Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Using the chart below: What is the total cumulative amount that the company will have to borrow between years 0 and 5? 0 actual 1
Using the chart below: What is the total cumulative amount that the company will have to borrow between years 0 and 5?
0 actual | 1 assumption | 2 assumption | 3 assumption | 4 assumption | 5 assumption | |||
Sales growth | 10% | 10% | 10% | 10% | 10% | |||
Costs of goods sold/Sales | 50% | 50% | 50% | 50% | 50% | 50% | ||
Interest rate on debt | 10% | 10% | 10% | 10% | 10% | 10% | ||
Annual growth rate in SG&A | 3% | 3% | 3% | 3% | 3% | |||
Current assets/Sales | 15% | 15% | 15% | 15% | 15% | 15% | ||
Current liabilities/Sales | 8% | 8% | 8% | 8% | 8% | 8% | ||
Net fixed assets/Sales | 77% | 77% | 77% | 77% | 77% | 77% | ||
Depreciation rate | 13% | 13% | 13% | 13% | 13% | 13% | ||
Interest earned on cash and marketable securities | 8% | 8% | 8% | 8% | 8% | 8% | ||
Tax rate | 35% | 35% | 35% | 35% | 35% | 35% | ||
Dividend payout ratio |
| 35% | 35% | 35% | 35% | 35% | ||
D/E ratio | 53% | 45% | 40% | 30% | 30% | 30% | ||
Initial (year 0) debt/equity ratio: =B43/SUM(B44:B45) | ||||||||
Year | 0 | 1 | 2 | 3 | 4 | 5 | ||
Income statement | ||||||||
Sales | 1,000 | 1,100 | 1,210 | 1,331 | 1,464 | 1,611 | <--=F18*(1+G3) | |
Costs of goods sold | (500) | (550) | (605) | (666) | (732) | (805) | <--=-G18*G4 | |
Gross profit | 400 | 550 | 605 | 666 | 732 | 805 | <--=SUM(G18:G19) | |
Gross profit % | 40% | 50% | 50% | 50% | 50% | 50% | <--=G20/G18 | |
Selling, general and administrative | (100) | (103) | (106) | (109) | (113) | (116) | <--=F22*(1+G6) | |
Interest payments on debt | (32) | (32) | (31) | (29) | (29) | (32) | <--=-G5*AVERAGE(F43,G43) | |
Interest earned on cash and marketable securities | 6.4 | 6.4 | 6.4 | 6.4 | 6.4 | 6.4 | <--=G11*AVERAGE(F34,G34) | |
Depreciation | (100) | (110) | (121) | (133) | (146) | (161) | <--=-G10*G39 | |
Profit before tax | 174 | 312 | 353 | 400 | 451 | 503 | <--=G20+SUM(G22:G25) | |
Taxes | (61) | (109) | (124) | (140) | (158) | (176) | <--=-G26*G12 | |
Profit after tax (net profit) | 113 | 203 | 229 | 260 | 293 | 327 | <--=G27+G26 | |
Net profit % | 11% | 18% | 19% | 20% | 20% | 20% | <--=G28/G18 | |
Dividends | (40) | (72) | (81) | (92) | (103) | (115) | <--=-G13*G28 | |
Retained earnings | 73 | 131 | 149 | 168 | 190 | 212 | <--=G30+G28 | |
Balance sheet | ||||||||
Cash and marketable securities | 80 | 80 | 80 | 80 | 80 | 80 | <--=F34 | |
Current assets | 150 | 165 | 182 | 200 | 220 | 242 | <--=G18*G7 | |
Fixed assets | ||||||||
At cost | 1,070 | 1,257 | 1,463 | 1,689 | 1,938 | 2,212 | <--=G39-G38 | |
Accumulated depreciation | (300) | (410) | (531) | (664) | (811) | (972) | <--=F38+G25 | |
Net fixed assets | 770 | 847 | 932 | 1,025 | 1,127 | 1,240 | <--=G9*G18 | |
Total assets | 1,000 | 1,092 | 1,193 | 1,305 | 1,427 | 1,562 | <--=G39+G35+G34 | |
Current liabilities | 80 | 88 | 97 | 106 | 117 | 129 | <--=G18*G8 | |
Debt | 320 | 312 | 313 | 276 | 302 | 331 | <--=G14*SUM(G44:G45) | |
Stock | 450 | 411 | 353 | 324 | 220 | 103 | <--=G40-G42-G43-G45 | |
Accumulated retained earnings | 150 | 281 | 430 | 598 | 787 | 999 | <--=F45+G31 | |
Total liabilities and equity | 1,000 | 1,092 | 1,193 | 1,305 | 1,427 | 1,562 | <--=SUM(G42:G45) | |
Calculating the return on invested capital (ROIC) | ||||||||
0 actual | 1 assumption | 2 assumption | 3 assumption | 4 assumption | 5 assumption | |||
Net operating profit after tax (NOPAT) | 130 | 219 | 246 | 275 | 308 | 343 | <--=G28-SUM(G23:G24)*(1-G12) | |
Invested capital | 840 | 924 | 1,016 | 1,118 | 1,230 | 1,353 | <--=G39+G35-G42 | |
Return on invested capital (ROIC) | 26.08% | 26.58% | 27.06% | 27.50% | 27.92% | <--=G50/F51 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started