I\'m having trouble finishing the bottome part of this table. Need formulas for

Question

I'm having trouble finishing the bottome part of this table. Need formulas for Excel.

BASE CASE

I'm having trouble finishing the bottome part of this table. Need formulas for Excel.

BASE CASE

Year 0 1 2 3 4 5 Initial FA Investment 9,000,000 Net Working Capital Rqt 800,000 Unit Sales 100,000 99,000 98,000 97,000 96,000 Price/unit $200 $195 $190 $185 $180 VarCost/unit $150 $151 $152 $153 $154 Revenue $       20,000,000 $       19,305,000 $       18,620,000 $       17,945,000 $       17,280,000 Variable costs $       15,000,000 $       14,949,000 $       14,896,000 $       14,841,000 $       14,784,000 Fixed costs year $               30,000 $               30,000 $               30,000 $               30,000 $               30,000 Depreciation $         1,800,000 $         1,800,000 $         1,800,000 $         1,800,000 $         1,800,000 EBIT $         3,170,000 $         2,526,000 $         1,894,000 $         1,274,000 $             666,000 Tax $         1,109,500 $             884,100 $             662,900 $             445,900 $             233,100 Net Income $         2,060,500 $         1,641,900 $         1,231,100 $             828,100 $             432,900 Depreciation $         1,800,000 $         1,800,000 $         1,800,000 $         1,800,000 $         1,800,000 After tax salvage 65,000 Return of Working Capital Cash Flow Accumulated cash flow Discounted Cash flow Net Present value 10% Internal Rate of Return Profitability Index Payback Discounted Payback Average Accounting Return

Explanation / Answer

Description 1 2 3 4 5 Total

Return of Working Capital:

EBIT/Working Capital 3.96 3.16 2.37 1.59 0.83

Cash Flows

(Net income + Depreciation) 3860500 3441900 3031100 2628100 2232900 15194500

Accumulated Cash flows 3806500 7302400 10333500 12961600 15194500

Net Present value 10% factors 1/1.10 1/(1.10)^2 1/(1.10)^3 1/(1.10)^4 1/(1.10)^5

Discounted Cash flows=

Cash flows*NPV factor     3590545 2844545 2277310 1795028 1386455 11812884

Internal rate or return= Interest rate at which future value of discounted cash flows equal to initial outlay

1/1.228 1/(1.228)^2 1/(1.228)^3 1/(1.228)^4 1/(1.228)^5

3143730 2282450 1636835 1155709 799609 9018334

Profitability Index:

1+NPV/Initial investment 1+ (11812884/9000000) = 2.31

Pay Back 3860500 3441900 2162900 = 2years 9 months

Discounted Payback 3590545 2844545 2277310 368599 = 3 years 3 months

Average Accounting Return = Average Accounting income / Investment

Average Accounting income = Cumulative Income after taxes/ 5 years

= 61945000 / 5 = 1238900

= 1238900 / 9000000 X 100 = 13.76%

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.