Knight 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. Knights cost of capital is 12 percent.

Required

Prepare a spreadsheet similar to the one following to calculate net present value, the profitability index, and the internal rate of return. The numbers should be formatted as they are below.

Spreadsheet Tips

Spreadsheets have built-in financial functions that make present value & internal rate of return calculations very easy. The formats of these formulas are as follows:

Net Present Value: =NPV(rate, value1,value2,value3 value29) where up to 29 values are allowed. The value must be at the end of the period, and each period must be equal in time (one year, for example). The formula is =NPV(D3,D7,D8,D9,D10,D11,D12)+D6.

Internal Rate of Return: =IRR(values,guess) where values is the range that includes the cash flows (D6 to D12) and guess is an estimate of the rate. Use the cost of capital as the guess.

Percentage: Rather than entering 12% in the formulas, refer to cell D3. This will allow you to change the rate and see the effect on the NPV and profitability index.

Profitability Index: You must construct a formula because no built-in function calculates it.

please give all formulas

Bookl Microsoft Excel Home Insert Page Lay Formula Data Review View Acrobat o x File Arial I U A A Alignment Numbe Styles Cells Paste Clipboard Font Editing 220 A B 1 Chapter 26-Working with Excel Name: 12% Net Present Value at 5 Year Cash flow 0 Initial investment (150,000) 1 Net cash flow 42,500 2 Net cash flow 42,500 3 Net cash flow 42,500 17,500 10 4 Net cash flow 42,500 5 Net cash flow 50.000 12 6 Net cash flow Net Present Value (NPV) 2647 13 1.084 Profitability Index 16 14.95% Internal Rate of Return Sheet 1 Sheet2 Sheet3 100%