Answered step by step
Verified Expert Solution
Link Copied!

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% 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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Management For Technology Start Ups

Authors: Alnoor Bhimani

2nd Edition

1398603082, 978-1398603080

More Books

Students also viewed these Finance questions