You€™ve recently been hired by B2B Consultants to provide financial advisory services to small business managers. B2B€™s clients often need advice on how to improve their operating cash flows and, given your accounting background, you€™re frequently called upon to show them how operating cash flows would change if they were to speed up their sales of inventory and their collections of accounts receivable or delay their payment of accounts payable. Each time you€™re asked to show the effects of these business decisions on the cash flows from operating activities, you get the uneasy feeling that you might inadvertently miscalculate their effects. To deal with this once and for all, you e-mail your friend Owen and ask him to prepare a template that automatically calculates the net operating cash flows from a simple comparative balance sheet.

You received his reply today.
From: Owentheaccountant@yahoo.com
To: Helpme@hotmail.com
Cc:

Subject: Excel Help
Hey pal. I like your idea of working smarter, not harder. Too bad it involved me doing the thinking. Anyhow, I€™ve created a spreadsheet file that contains four worksheets. The first two tabs (labeled BS and IS) are the input sheets where you would enter the numbers from each client€™s comparative balance sheets and income statement. Your clients are small, so this template allows for only the usual accounts.
Also, I€™ve assumed that depreciation is the only reason for a change in accumulated depreciation. If your clients€™ business activities differ from these, you€™ll need to contact me for more complex templates. The third worksheet calculates the operating cash flows using the indirect method and the fourth does this calculation using the direct method. I€™ll attach the screenshots of each of the worksheets so you can create your own. To answer €œwhat if€ questions, all you€™ll need to do is change selected amounts in the balance sheet and income statement.

Required:
Copy the account balances from the worksheets for the balance sheet and income statement into a spreadsheet file. Enter formulas into the balance sheet worksheet to compute the change in each account balance, and then enter the formulas for the statement of cash flows (indirect method only) into a third worksheet. From this third worksheet, report the net cash flow provided by (used in) operatingactivities.

Transcribed Image Text:

Business De Bwce E Small Bueinese Clie ASSETS 930 1264000 858400 Incama Statement For the Year Ended December 31 102,000 5 Sales revenues 6 Cost of goods soid 7 400 1,800 2,800 Depreciation expense 8Othar oparating expenses 9 Operating income 10 Interest expense 11 Income bafore income taxes 12 Income tax expense 13 Net inccme 14 2Tos A ABILITIES 31 400 30622 ,1400 4108 9 430 เว 111 Totl Lil 6,500 10 111 1E15500 Rosdy 19 Sasment of Cash Flows For the Year Ended Dbr Small Buaineas Cliant Statement of CB5h F㎏ws For the Year Ended Decembar 31 Cash flows from operafing acthmes 81C13 B3011-881C11 6Cash collected fom customers Cash paid for other opersting expenses ISIC8-BSE17) Caah paid for interast 0 Cash paid for incoms taxas 11 Net cash Bow from operatingacivies SUMC C10 jSIC10-8SE15) EISC128SE15 83E Reaty