Capital Budget

Land O'Lakes' is considering purchasing a small factory building to increase their manufacturing potential. They plan to sell the building in $5$ years to upgrade. Information necessary to analyze this capital budgeting decision is provided in the spreadsheet.

Please use the template provided to complete the capital budgeting analysis of the decision to purchase the factory. You will use Excel to compute the Net Present Value $($NPV$)$ and Intemal Rate of Retum $($IRR$)$ using the function wizard. You will also compute the Simple Rate of Return and Profitability Index. Each cell must be formula driven.

HINTS:

To compute the Net Present Value, use the function wizard. Select Financial Functions and then NPV$.$ Excel will ask you for a rate, the cost of capital. Reference this from the data box to the left. Excel will also ask you for a range of values. This should consist of your cash flows for years $1-5.$ DO NOT PUT IN YOUR INITIAL CASH OUTFLOW $($YEAR O CASH FLOW$).$ YOU will need to separately add in the initial cash outflo w outside of the NPV formula in your formula bar at the top of the page. MAKE SURE THAT THE INITIAL CASH FLOW IS REPRESENTED AS NEGATIVE SINCE IT IS A CASH OUTFLOW.

To compute the Profitability Index, divide NPV by the initial investment which consists of the amounts paid for the factory building and working capital.

To compute the Internal Rate of Return, again use the function wizard. Select Financial Functions and then IRR. It will ask you for a range of cash flo ws$.$ THIS TIME BE SURE TO INCLUDE THE YEAR O CASH FLOW AND MAKE SURE THAT IT IS NEGATIVE.

To calculate the Simple Rate of Return, average the net income across all years. and divide the average net income by the initial investment. You can use a function to average the net incomes of years $1-5.$ Select Statistical Functions and then AVERAGE. Once you have found your average you will need to go back up to the formula bar and divide by the initial investment. Do not include the working capital here. just the factory building. If you get $$0$ as your answer, right click and select format cells. Click on numbers tab. percentage, and $2$ decimal places. Once you do this, you should see a percentage in the cell.

CHECK FIGURES:

Gain on Sale of Building $=$$$395,000$

Net Income $($Year $1)=$$$24,700$

Sales Amount Less Gain $=$$$600,000$

Cash Flow $($Year $0)=($$$1\mathrm{.}450\mathrm{.}000)$

Cash Flow $($Year $1)=$$$144\mathrm{.}700$

Cash Flow $($Year $5)=$$$1,649\mathrm{.}400$