Chapter 14: Applying Excel Data Example E Cost of equipment needed Working capital needed Overhaul of equipment in four years Salvage value of the equipment in five years Annual revenues and costs: Sales revenues Cost of goods sold Out-of-pocket operating costs Discount rate $100,000 $55,000 $10,000 $35,000 $370,000 $275,000 $65,000 18% a 5 Enter a formula into each of the cells marked with a ? below Exhibit 14-8 Years Now 1 2 3 4 Purchase of equipment $ (100,000) Investment in working capital $ (55,000) Sales $ 370,000 $ 370,000 $ 370,000 $ 370,000 $ 370,000 Cost of goods sold $ (275,000) $ (275,000) $ (275,000) $ (275,000) $ (275,000) Out-of-pocket operating costs $ (65,000) $ (65,000) $ (65,000) $ (65,000) $ (65,000) Overhaul of equipment $ (10,000) Salvage value of the equipment $ 35,000 Working capital released $ 55,000 Total cash flows (a) $ (155,000) $ 30,000 $ 30,000 $ 30,000 $ 20,000 $ 120,000 Discount factor (14%) (b) 1.000 0.8475 0.7182 0.6086 0.516 0.437 Present value of cash flows (a) (b) $ (155,000) $ 25,425 $ 21,546 $ 18,258 $ 10,320 $ 52,440 Net present value $ (27,011) *Use the formulas from Appendix 13B: Present value of $1 = 1/(1+r)^n Present value of an annuity of $1 = (1/r)*(1-(1/(1+r)^n)) where n is the number of years and r is the discount rate Capital Budgeting Excel Assignment i Saved 2 Part 2 of 2 Required information 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. 15 points 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.) eBook B Print References 100,000 55.000 1 Chapter 14: Applying Excel 2. 3 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 $ $ $ $ 10.000 35.000 $ 370.000 275.000 65,000 18 % $ 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 TS s (27.003) 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 intemal rate of retum is between % and % d. Reset the discount rate to 18%. 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