50.1K

Verified Solution

Link Copied!

Answer yellow boxes but show work and what you used to get then answer example (B4+B5)

317 A B C D G H K L Kilby Company is considering the purchase of new automated manufacturing equipment that would cost $150,000. The equipment would save $42,500 in labor costs per year over its six-year life. At the end of the fourth year, the equipment would require an overhaul that would cost $25,000. The equipment would have a $7,500 salvage value at the end of its life. Kilby's cost of capital is 12 percent. \begin{tabular}{|r|r|r|} $150,000 & $42,500 & $25,000 \\ \hline$7,500 & 12% & \\ \hline \end{tabular} Required a. Complete the spreadsheet to calculate net present value, the present value index, and the internal rate of return. Note: Negative amounts or amounts to be deducted should be input and displayed as negative values. All other answers should be input and displayed as positive values. Spreadsheet Tips Spreadsheets have built-in financial functions that make net present value and internal rate of return calculations very easy. The formats of these formulas are as follows: 1. Net Present Value: = NPV(rate, value1,value2, value 3. . value29) where up to 29 values are allowed. 12 The values must be at the end of the period, and each period must be equal in time (one year, for example). The formula is =NPV(B17,C21,C22,C23,C24,C25,C26) + C20. 2. Internal Rate of Return: =IRR(values,guess) where values is the range that includes the cash flows (C20 to C26 ) and guess is an estimate of the rate. Use the cost of capital as the guess. 3. Percentage: Rather than entering 12% in the formulas, refer to cell B17. This will allow you to change the rate and see the effect on the NPV and present value index. 4. Present Value Index: You must construct a formula because no built-in function calculates it. A B C D G E F. H K Spreadsheet Tips Spreadsheets have built-in financial functions that make net present value and internal rate of return calculations very easy. The formats of these formulas are as follows: 1. Net Present Value: = NPV(rate,value1,value2,value3 . . . value29) where up to 29 values are allowed. 12 The values must be at the end of the period, and each period must be equal in time (one year, for example). The formula is =NPV(B17,C21,C22,C23,C24,C25,C26)+C20. 13 2. Internal Rate of Return: =IRR(values,guess) where values is the range that includes the cash flows (C20 to C26 ) and guess is an estimate of the rate. Use the cost of capital as the guess. 3. Percentage: Rather than entering 12% in the formulas, refer to cell B17. This will allow you to change the rate and see the effect on the NPV and present value index. 4. Present Value Index: You must construct a formula because no built-in function calculates it. Net Present Value at: 18 \begin{tabular}{|lc|l|l|} \hline 19 & Year & & Cash flow \\ \hline 20 & 0 & Initial investment \\ 21 & 1 & Net cash flow \\ 22 & 2 & Net cash flow \\ 23 & 3 & Net cash flow \\ 24 & 4 & Net cash flow \\ 25 & 5 & Net cash flow \\ 26 & 6 & Net cash flow \\ 27 & & Net present value (NPV) \\ \hline \end{tabular} 28 29 Present value index 30 Internal rate of return 31 32

Answer & Explanation Solved by verified expert

See Answer