21. Investment data
3 Initial investment -$300,000
4 After-tax receipt from sale of equipment $0
5 Sales $1,000,000 $2,000,000 $1,800,000
11 2. Expected net operating profit after tax (NOPAT)
12 Depreciation expense $100,000 $100,000 $100,000
13 Operating expenses $1,000,000 $1,900,000 $1,720,000
17 3. Expected cash flow from the investment and net present value (NPV)
18 Working capital requirement $100,000 $200,000 $180,000 $0
19 Change in working capital requirement -$100,000 -$100,000 $20,000 $180,000
23 4. Economic value added (EVA) and market value added (MVA)
24 Accumulated depreciation of the equipment $100,000 $200,000 $300,000
25 Net book value of the equipment $300,000 $200,000 $100,000 $0
26 Invested capital $400,000 $400,000 $280,000 $0
27 Capital charge $29,280 $29,280 $20,496
28 EVA -$29,280 $40,720 $35,504
29 MVA $36,795
30
31 Rows 3 to 10 are data.
32 The formula in cell C12 is: =SLN(-$B$3,E4,3). Then copy formula in cell C12 to cells D12 and E12.
33 The formula in cell C13 is: =C6*C5+C12. Then copy formula in cell C13 to cells D13 and E13.
34 The formula in cell C14 is: =C5-C13. Then copy formula in cell C14 to cells D14 and E14.
35 The formula in cell C15 is: =C9*C14. Then copy formula in cell C15 to cells D15 and E15.
36 The formula in cell C16 is: =C14-C15. Then copy formula in cell C16 to cells D16 and E16.
37 The formula in cell B18 is: = B7*C5. Then copy formula in cell B18 to cells C18, D18, and E18.
38
39 The formula in cell B20 is: =B3+B19+B16+B12. Then copy formula in cell B20 to cells C20, D20, and E20.
40 The formula in cell B21 is: =B20+NPV(B10,C20:E20).
41 The formula in cell B22 is: =IRR(B20:E20).