Question: 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
Get step-by-step solutions from verified subject matter experts
