Chapter 03 - A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
SETTING UP THE FINANCIAL STATEMENT MODEL Sales growth 10% Current assets/Sales 15% Current liabilities/Sales 8% Net fixed assets/Sales 77% Costs of goods sold/Sales 50% Depreciation rate 10% Interest rate on debt 10.00% Interest paid on cash and marketable securities 8.00% Tax rate 40% Dividend payout ratio 40% Year 0 1 Income statement Sales 1,000 1,100 <-- =B16*(1+$B$3) Costs of goods sold (500) (550) <-- =-C16*$B$7 Interest payments on debt (32) (32) <-- =-$B$9*(B37+C37)/2 Interest earned on cash and marketable securities 6 9 <-- =$B$10*(B28+C28)/2 Depreciation (100) (117) <-- =-$B$8*(C31+B31)/2 Profit before tax 374 410 <-- =SUM(C16:C20) Taxes (150) (164) <-- =-C21*$B$11 Profit after tax 225 246 <-- =C22+C21 Dividends (90) (98) <-- =-$B$12*C23 Retained earnings 135 148 <-- =C24+C23 Balance sheet Cash and marketable securities 80 144 <-- =C40-C29-C33 Current assets 150 165 <-- =C16*$B$4 Fixed assets At cost 1,070 1,264 <-- =C33-C32 Depreciation (300) (417) <-- =B32-$B$8*(C31+B31)/2 Net fixed assets 770 847 <-- =C16*$B$6 Total assets 1,000 1,156 <-- =C33+C29+C28 Current liabilities 80 88 <-- =C16*$B$5 Debt 320 320 <-- =B37 Stock 450 450 <-- =B38 Accumulated retained earnings 150 298 <-- =B39+C25 Total liabilities and equity 1,000 1,156 <-- =SUM(C36:C39) A B C D E F 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
FIRST FINANCIAL MODEL Sales growth 10% Current assets/Sales 15% Current liabilities/Sales 8% Net fixed assets/Sales 77% Costs of goods sold/Sales 50% Depreciation rate 10% Interest rate on debt 10.00% Interest paid on cash and marketable securities 8.00% Tax rate 40% Dividend payout ratio 40% Year 0 1 2 3 4 5 Income statement Sales 1,000 1,100 1,100 1,210 1,331 1,464 Costs of goods sold (500) (550) (550) (605) (666) (732) Interest payments on debt (32) (32) (32) (32) (32) (32) Interest earned on cash and marketable securities 6 12 20 26 33 41 Depreciation (100) (113) (129) (152) (179) (209) Profit before tax 374 367 408 447 488 532 Taxes (150) (144) (179) (196) (215) (234) Profit after tax 225 223 229 251 274 298 Dividends (90) (86) (108) (118) (129) (140) Retained earnings 135 130 161 177 193 211 Balance sheet Cash and marketable securities 80 210 287 371 463 562 Current assets 150 165 165 182 200 220 Fixed assets At cost 1,070 1,187 1,401 1,647 1,929 2,251 Depreciation (300) (417) (554) (715) (904) (1,124) Net fixed assets 770 770 847 932 1,025 1,127 Total assets 1,000 1,145 1,299 1,485 1,687 1,909 Current liabilities 80 88 88 97 106 117 Debt 320 320 320 320 320 320 Stock 450 450 450 450 450 450 Accumulated retained earnings 150 280 441 618 811 1,021 Total liabilities and equity 1,000 1,130 1,299 1,485 1,687 1,909 Year 0 1 2 3 4 5 Free cash flow calculation Profit after tax 223 229 251 274 298 Add back depreciation 117 137 161 189 220 Subtract increase in current assets 0 (15) (17) (18) (20) Add back increase in current liabilities 0 8 9 10 11 Subtract increase in fixed assets at cost
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 10

Chapter 03 - A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online