Question

Please show the formulas in each highlighted cell. Need to show work, thanks!

J 4 A B C D E F G H I 1 Fill in all Yellow highlighted cells. You are recreating the statement of Cash Flows and calculating the FCF. 2 Tax rate 40% 40% 3 BALANCE SHEETS - Specta Products - Decem 2014 2013 4 fin millions of dollars) 5 | Arrer Free Cash Flow 2014 6 Cash and equivalents 10 $ 80 7 Accounts receivable 375 315 8 Inventories 415 Total current assets 1,000 $ 810 10 Net plant and equipment 1,000 870 11 Other assets expected to last more than 1 year STATEMENT OF CF 2014 12 Total assets $ 2,000 $ 1,680 Operating CF 615 9 $ $ $ 14 Liabilities and Equity 15 Accounts payable 16 Accruals 17 Notes payable Total current liabilities 19 Long-term bonds 20 Total liabilities 21 Common stock (50,000,000 shares) 22 Retained earnings 23 Total common equity 24 Total liabilities and equity $ 60 140 110 310 750 1,060 130 810 940 2,000 $ 30 130 60 220 580 800 130 750 880 1,680 Investing CF $ $ $ 25 Financing CF Net CF 27 INCOME STATEMENTS - Specta Products - Years Ending December 31 28 (in millions of dollars) 2014 2013 29 30 Net sales $ 3,000.0 $ 2,850.0 31 Operating costs except depreciation and amorti 2.616.2 2,497.0 32 Depreciation and amortization 100.0 90.0 33 Earnings before interest and taxes (EBIT) $ 283.8 $ 263.0 34 Less interest 88.0 60.0 35 Earnings before taxes (EBT) 195.8 $ 203.0 36 Taxes 78.3 81.2 37 Net income $ 117.5 $ 121.8 38 39 Common dividends $ 57.5 $ 53.0 40 Addition to retained earnings $ 60.0 $ 68.8 41 INFORMATION SHEET FCF and ST of CF RATIOS

Answer 1

calculation of free cash flow
EBIT	284
tax rate	40%
EBIT X (1-tax rate)		170
add: non-cash expenses		100
less: change in (current assets-current liabilities)		100
less: capital expenditure		130
free cash flow		40

investing cash flow
purchase of plant and equipment	-130
net investing cash flow		-130

financing cash flow
increase in notes payable	50
increase in long-term bonds	170
dividend paid	-58
net financing cash flow		162

free cash flow	40
net investing activity	-130
net financing activity	162
net cash flow	72