show excel work please

1 I 1 - vierge e Center % 9 60.00 00 20 Insert Delet Clipboard Font Conditional Format as Cell Formatting Table Styles Styles Alignment Number C39 DK Cells F G H 1 A C E 1 ABC Company, Financial Model 2 Value Drivers 3 Sales growth 12% 4 Costs of goods sold/Sales 45% 5 Depreciation rate (of the averag 10% 6 Interest rate on debt 8% 7 Interest earned on cash balances 4% 8 Tax rate 36% 9 Dividend payout ratio 100% 10 Current assets/Sales 22% 11 Current liabilities/Sales 20% 12 Net fixed assets growth rate 5% 13 Cash balance every year $500,000 14 Debt repayment amount $750,000 15 Common Stock is the PLUG variable 16 17 Part A. Construct pro forma income statements and pro forma balance sheets for the next 5 years 18 Year 0 1 2 19 Income statement 20 Sales 2,000,000 21 Costs of goods sold (900,000) 22 Depreciation (250,000) 23 Interest payments on debt (300.000) 24 Interest earned on cash 18.400 25 Profit before tax 568,400 26 Taxes (204,624) 27 Profit after tax 363.776 28 Dividends (363.776) 29 30 Balance sheet Q6 02 Q7 0.8 Q9 Q10 3 4 5 OOO os Ready Type here to search 39 X for A B C D E F G Balance sheet -Cash Current assets 460,000 440,000 3 Fixed assets 4 At cost 5 Accumulate Depreciation 6 Net fixed assets 7 Total assets 4,000,000 (500,000) 3,500,000 4,400,000 59 Current liabilities 400.000 10 Debt 3,000,000 41 Stock (1,500,000 shares) 750.000 42 Total liabilities and equity 4,400,000 43 44 45 Part B. Calculate Free Cash Flow 46 Year 0 47 Profit after tax 48 Add back depreciation 49 Subtract increase in current assets 50 Add back increase in current liabilities 51 Subtract increase in fixed assets at cost 52 Add back after-tax interest on debt 53 Subtract after-tax interest on cash & mkt. securities 54 Free cash flow 1 2 3 4 5 55 56 02 Q3 Q4 05 06 07 Q8 Q9 Q10 Enter Tyne here