Sensitivity and what-if analysis Your instructor has an Excel spreadsheet for LO 4, 6 the Shirley's Doughnut Hole example used in the chapter. You will need it to answer this question. The file shows how easily capital budgeting calculations can be done on a computer. (Knowledge and judgment are required to per¬ form capital budgeting analysis; however, the computer makes the necessary calculations easy.) This exercise shows you how quickly you can answer what-if or sensitivity analysis questions after you have set up the spreadsheet.

Do not be misled by the simple nature of the problem. The procedure is the same for more complex problems.

After you retrieve this file, look at the layout of the spreadsheet. The key problem parameters are the initial investment amount in cell D4, the annual benefits in cell D5, the salvage value in cell D6, the cost of capital in cell D3, and the tax rate in cell D9. The project's net present value is shown in cell D10.

(a) Move to cell D5 and adjust the annual benefits up or down until the amount in cell DIO is zero. (This will be about $17,556.) This is the annual benefit that just makes the project desirable. Note that $17,556 is about 88% of $20,000, the estimated value of the benefit. Therefore, the decision of whether to invest in this project is quite sensitive to our estimate of the cost savings. This causes us to focus our attention on the estimate of cost savings.

(b) Put the value of $20,000 in cell D5. The net present value shown in cell D10 should be $6,013. Now move to cell D3 and experiment with the cost of capital until the net present value in cell D10 is zero. (This will be about 9.94%.) This is 42% more than the estimated required return of 7%, so we would consider the decision to invest in the cooker relatively in¬ sensitive to the estimate of the required return.

(c) Put the value of 7% (0.07) in cell D3. Again the net present value shown in cell D10 should be $6,013. Now look at the project life estimate. This simple spreadsheet is not set up in a way that allows us to vary the project life easily, although if you want to, it can be done fairly easily with spreadsheet macros.

(d) Suppose that you want to know if the doughnut cooker investment would be justified if the cooker lasted only four years. Delete row 28. Move to the new cell A28 and enter 4. This terminates the project after four years. However, we must adjust the depreciation so that it is taken over four years instead of five. Move to cell D8 and enter 4. This will adjust the de¬ preciation amount. You can see that the project now has a present value of $1401, which means that the project is undesirable. The decision to buy the cooker is very sensitive to the estimate of the cooker's life.

This simple example gives you the idea of how to use sensitivity analysis to identify what estimates are critical to the project's acceptance or rejection and where to spend more time or money improving the accuracy of estimates used in the analysis.

Suppose that the required return is 9%. If everything else in the problem remains the same, what is the minimum amount of the annual benefits that would make the project desirable?

(LO 1)