Question

Excel Online Structured Activity: Statement of cash flows You have just been hired as a financial analyst for Barrington Industries. Unfortunately, company headquarters (where all of the firm's records are kept) has been destroyed by fire. So, your first job will be to recreate the firm's cash flow statement for the year just ended. The firm had $100,000 in the bank at the end of the prior year, and its working capital accounts except cash remained constant during the year. It earned $5 million in net income during the year but paid $700,000 in dividends to common shareholders. Throughout the year, the firm purchased $5.4 million of machinery that was needed for a new project. You have just spoken to the firm's accountants and learned that annual depreciation expense for the year is $450,000; however, the purchase price for the machinery represents additions to property, plant, and equipment before depreciation. Finally, you have determined that the only financing done by the firm was to issue long-term debt of $1 million at a 6% interest rate. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below. X Open spreadsheet What was the firm's end-of-year cash balance? Recreate the firm's cash flow statement to arrive at your answer. Write out your answer completely. For example, 5 million should be entered as 5,000,000. Round your answer to the nearest dollar, if necessary. $

Answer 1

SEE THE IMAGE. ANY DOUBTS, FEEL FREE TO ASK. THUMBS UP PLEASE

cash flow [Compatibility Mode] - Microsoft Excel (Product Activation Failed) Page Layout Formulas File Home Insert Data Review View Add-Ins E AutoSum - Cut AC Wrap Text Calibri 12 General Copy Fill Merge & Center Insert Delete Format Paste в IU* Conditional Format Cell Sort & Find & % Format Painter Clear Filter Select Formatting as Table Styles Styles Clipboard Font Alignment Number Cells Editing fox B1 D. А C E G H J K 3 cash at the beginning of the year 100000 changes in net operating working capital except cash 4 0 5 Net income 5000000 6 common dividends 700000 5400000 7 machinery equipment purchases 8 depreciation expenses 450000 9 long term debt 1000000 10 interest rate on long term debt 6.00% 11 12 statement of cash flows 13 I.Operating activities formulas (=B5] 14 net income 5000000 15 depreciation expenses [B8] 450000 16 changes in net operating working capital except cash [=B4] 0 17 net cash provided by (used in) operations [=SUM(B14:B16)] 5450000 18 19 II. Long term investing activities 20 Additions to PP&E -5400000 (-B7] Sheet5 MIX Sheet3 Sheet6 Sheet7 Sheet& Sheet1 Sheet2 Sheet4 Sheet9 erences 120% WA 06:49 10 ENG 01-12-2019

cash flow [Compatibility Mode] - Microsoft Excel (Product Activation Failed) Formulas File Home Insert Page Layout Data Review View Add-Ins Z AutoSum- Cut AC Wrap Text Calibri 12 General Copy Fill Merge & Center Insert Delete Format Paste в IU* Conditional Format Cell Sort & Find & % Format Painter Clear Filter Select Formatting as Table Styles Clipboard Font Alignment Number Styles Cells Editing fox H27 A C E G Н J K 12 statement of cash flows 13 I.Operating activities formulas =B5] 14 net income 5000000 15 depreciation expenses [B8] 450000 16 changes in net operating working capital except cash [=B4] 0 17 net cash provided by (used in) operations [=SUM(B14:B16)] 5450000 18 19 I. Long term investing activities |(-B7] 20 Additions to PP&E -5400000 21 net cash used in investing activities [=SUM(B20)] -5400000 22 23 I. Financing activities 24 Increase in long term debt [=B9] -B6] 1000000 25 Payment of common dividends -700000 26 net cash used by financing activities 300000 [=SUM(B24:B25)] 27 28 IV. Summary 29 Net increase(decrease) in cash 30 cash at the beginning of the year [-B17+B21+B26] B3 [-SUM(B29:B30) 350000 100000 31 cash at the end of year 450000 Sheet1 Sheet2/MIX Sheet5 Sheet6 Sheet3 Sheet4 Sheet7 Sheet8 Sheet9 erences 120% WA 06:50 10 ENG 01-12-2019