Chapter 16, Problem 4BPSB

To determine

Spreadsheet for Cash Flow Statement:

Spreadsheet for cash flow statement is another technique of analyzing the cash flows from various activities in which changes in each item of Balance sheet (i.e. of assets or liabilities) will be reconciled with the items listed in cash flow statement. The normal cash flow statement is prepared and it is then reconciled through the spreadsheet to reflect the changes in the Balance sheet items.

To Determine: The Spreadsheet for Cash flows Statement analysis to be prepared.

Answer to Problem 4BPSB

Solution: TheSpreadsheet for Cash flow Statement analysis has been prepared as follows:

<

Spreadsheet for Statement of Cash Flows-Indirect Method
for the year ending December31, 2015
			31-Dec	Analysis of Changes				31-Dec
			2014		Debit		Credit	2015
Balance Sheet-Debit Bal. Accounts
Cash			61550					123450
Accounts receivable			80750			d.	3650	77100
Inventory			250700			e.	10100	240600
Prepaid expense			17000			f.	1900	15100
Equipment			200000	h.	113250	c.	51000	262250
			610000					718500
Balance Sheet-Credit Bal. Accounts
Accumulated depreciation			95000	c.	22850	b.	38600	110750
Accounts payable			102000	g.	84250
Short term Notes payable			10000			i.	5000	15000
Long term Notes payable			77500	j.	47500	m.	70000	100000
Common Stockk			200000			k.	15000	215000
Paid in capital in excess of par			0			k.	30000	30000
Retained earnings			125500	l.	53600	a.	158100	230000
			610000					718500
Statement of Cash flows:
Operating Activities:
Net income				a.	158100
Depreciation				b.	38600
Loss on Sale of equipment				c.	2100
Decrease in Accounts receivable				d.	3650
Decrease in Inventory				e.	10100
Decrease in prepaid insurance				f.	1900
Decrease in Accounts payable						g.	84250
Investing Activities
Sale of Equipment				c.	26050
Purchase of Equipment						h.	43250
Financing Activities
Borrowings under Short term notes				i.	5000
Repayment of Long term notes						j.	47500
Issue of Common Stock				k.	45000
Dividend paid						l.	53600
Non Cash investing and Financing Activities
Purchase of Equipment by issuing Long term notes				m.	70000	h.	70000
					543860		543860

Explanation of Solution

The Analyses of Cash flow Statement has been done for each item activity wise as under:

Cash-flows from Operating Activities: The Various items reflecting in the cash flows from operating activities have been reconciled with the balance sheet as under:

*Net income taken in cash flows is from the changes in retained earnings in the balance sheet (other part of change in retained earnings is due to dividend paid which will be reconciled in financing activities)
*Depreciation is reconciled with the change in Accumulated depreciation Account.
*Loss on sale of equipment along with accumulated depreciation on sold assets and cash received on sold assets is reconciled with change in equipment account balance.
*Changes in Accounts receivable, Inventory, Prepaid expense and Accounts payable has been properly reconciled with balances in balance sheet.

Cash-flows from Investing Activities: The Various items of cash flows from investing activities have been reconciled withbalance sheet items as under:

*Total change in balance of equipment has been divided in two parts, one debit change due to purchase (which has been categorized as cash purchase and non-cash purchase through issue of long term notes.
*Other change is credit change due to assets sold(which is further divided in to accumulated depreciation on sold assets, cash received on sold assets and loss on sale of asset).

Cash-flows from financing Activities: The Various items in financing activities has been reconciled as under:

*Short term borrowings is reconciled with the change in balance in balance sheet.
*Repayment of long term notes has been made and this change along with non-cash acquisition of assets has been reconciled with the total change in long term notes payable.
*Issue of shares at above premium reflecting the changes in two accounts of balance sheet i.e. common stock and paid in capital above par.
*Dividend paid is second change in retained earnings as discussed initially along with the net income.

Non-Cash Investing and Financing activities: The one transaction of assets acquired by issuing long term notes payable has been reconciled as already been discussed.

Conclusion

To conclude, it can be said that cash flows statement shall be prepared taking in to consideration principles of each activities for division of transactions among activities.