Question

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 13B-1 and Exhibit 13B-2. (Use appropriate factor(s) from the tables provided.) 1 Chapter 13: Applying Excel Data 4 $ 5 6 7 $ 340,000 20,000 40,000 25,000 $ $ 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 $ $ 415,000 245,000 75,000 13 13%

Answer 1

a) NPV @ 13%

Year 0 Year 1 4,15,000 -2,45,000 -75,000 Year 2 4,15,000 -2,45,000 -75,000 Particulars Sales Revenue Cost of Goods Sold Cash Operating costs Overhaul of Equipment Cost of Equipment Working Capital Salvage Value Year3 Year 4 Year 5 4,15,000 4,15,000 4,15,000 -2,45,000 -2,45,000 -2,45,000 -75,000 -75,000 -75,000 -40,000 -3,40,000 -20,000 25,000 95,000 0.88 Net Flow of Funds PVAF @ 13% Present Value of Funds Net Present Value @ 13% -3,60,000 1.00 -3,60,000 -36,827 95,000 0.78 74,399 95,000 0.69 65,840 55,000 0.61 33,733 1,20,000 0.54 65,131

Net Present value of the project @ 13% will be -36,827

b) The Internal Rate of Return lies between 8% and 9% as is shown in the below calculations:-

It can be clearly seen that when the discount rate is 8%, the NPV is Positive and when the discount rate is 9%, the NPV is negative, which means that the IRR will be in between 8% and 9%.

NPV @ 8%

Year O Particulars Sales Revenue Cost of Goods Sold Cash Operating costs Overhaul of Equipment Cost of Equipment Working Capital Salvage Value Year 1 Year 2 Year3 Year 4 Year 5 4,15,000 4,15,000 4,15,000 4,15,000 4,15,000 -2,45,000 -2,45,000 -2,45,000 -2,45,000 -2,45,000 -75,000 -75,000 -75,000 -75,000 -75,000 -40,000 -3,40,000 -20,000 25,000 Net Flow of Funds PVAF @ 8% Present Value of Funds Net Present Value @ 8% -3,60,000 1.00 -3,60,000 16,921 95,000 0.93 87,963 95,000 0.86 81,447 95,000 0.79 75,414 55,000 0.74 40,427 1,20,000 0.68 81,670

NPV @ 9%

Year 0 Year 1 Year 2 Year3 4,15,000 4,15,000 4,15,000 -2,45,000 -2,45,000 -2,45,000 -75,000 -75,000 -75,000 Particulars Sales Revenue Cost of Goods Sold Cash Operating costs Overhaul of Equipment Cost of Equipment Working Capital Salvage Value Year 4 4,15,000 -2,45,000 -75,000 -40,000 Year 5 4,15,000 -2,45,000 -75,000 -3,40,000 -20,000 25,000 Net Flow of Funds PVAF @ 9% Present Value of Funds Net Present Value @ 9% -3,60,000 1.00 -3,60,000 -2,572 95,000 0.92 87,156 95,000 0.84 79,960 95,000 0.77 1 73,357 55,000 0 .71 38,963 1,20,000 0.65 77,992

c) As can be seen from the part a that the NPV @ 13% was -36,827. So, to get an positive NPV of 1, the Present value of last year should be $ 101,959 (65,131 + 36,828).

Now to get the Present value of $ 101,959, the net cash flow for last year should be $ 187,853 ( $ 101,959 / 0.54)

Now, the amount of salvage value will be $ 187,853 - Sales Revenue - Cost of Goods sold - Cash operating expenses

i.e $ 187,853 - $ 415,000 + $ 245,000 + $ 75,000 = $ 92,853