J33 fr H 1 J K L M A B B D E F G 1 Chapter 14: Applying Excel 2 3 Data 4 Example E 5 Cost of equipment needed $240,000 6 Working capital needed $40,000 7 Overhaul of equipment in four years $15,000 8 Salvage value of the equipment in five year: $25,000 9 Annual revenues and costs: 10 Sales revenues $430,000 11 Cost of goods sold $275,000 12 Out-of-pocket operating costs $85,000 13 Discount rate 16% 14 15 Enter a formula into each of the cells marked with a ? below 16 Exhibit 14-8 17 Years 18 Now 1 2 3 4 5 19 Purchase of equipment $ 240,000 20 Investment in working capital $ 40,000 21 Sales $430,000 $430,000 $430,000 $430,000 $ 430,000 22 Cost of goods sold $ 275,000 $275,000 $275,000 $275,000 $275,000 23 Out-of-pocket operating costs $ 85,000 $ 85,000 $ 85,000 $ 85,000 $ 85,000 24 Overhaul of equipment $ 15,000 25 Salvage value of the equipment $ 25,000 26 Working capital released $ 40,000 27 Total cash flows $(280,000) $ 70,000 $ 70,000 $ 70,000 $ 55,000 $135,000 28 Discount factor (14%) (b) 1.000 0.862 0.743 0.641 0.552 0.476 29 Present value of cash flows (a) (b) $(280,000) $ 60,340 $ 52,010 $ 44,870 $ 30,360 $ 64,260 30 Net present value $ (28,160) 31 32 *Use the formulas from Appendix 13B: 33 Present value of $1 = 1/(1+r)^n 34 Present value of an annuity of $1 = (1/1)*(1-(1/(1+r)^n)) 35 where n is the number of years and r is the discount rate 36 37 38 Part 2 39 a) The net present value of the project is: $ (28,160) 40 b) The IRR value is between 11% and 12% (check the Discount rate changes and sign of NPV) 41 C) If the discount rate is 16%, then the salvage value should be at least $84,160 to make the NPV positive 42 43 44 2 The Chapter 14 Form worksheet is to be used to create your own worksheet version of Example E and Exhibit 14-8 in the text. art 2 of 2 2. The company is considering a project involving the purchase of new equipment Change the data area of your worksheet to match the following: Use Exhibit 14B-1 and Exhibit 14B-2. (Use appropriate factor(s) from the tables provided.) pints A B 1 Chapter 14: Applying Excel eBook 2 Print References $ $ 3 Data 4 Example E 5 Cost of equipment needed 6 Working capital needed 7 Overhaul of equipment in four years 8 Salvage value of the equipment in five years 9 Annual revenues and costs: 10 Sales revenues 11 Cost of goods sold 12 Out-of-pocket operating costs 13 Discount rate 240,000 40,000 15,000 25,000 $ $ $ $ ? 430,000 275,000 85,000 16 % $ a. What is the net present value of the project? (Negative amount should be indicated by a minus sign. Round your present value factor to 3 decimals and round all other intermediate calculations to nearest whole dollar.) Net present value c. The internal rate of return is between what two whole discount rates (e.g., between 10% and 11%, between 11% and 12%, between 12% and 13%, between 13% and 14%, etc.)? The internal rate of return is between % and % d. Reset the discount rate to 16%. Suppose the salvage value is uncertain. How large would the salvage value have to be to result in a positive net present value? Minimum salvage value required to generate a positive present value