Risk in capital budgeting really means the probability that the actual outcome will be worse than the ex outcome. For example, if there were a high probability that the expected NPV as calculated above will a turn out to be negative, then the project would be classified as relatively risky. The reason for a worse- expected outcome is, typically, because sales were lower than expected, costs were higher than expect project turned out to have a higher than expected initial cost. In other words, if the assumed inputs turn
Instructions for Constructing Data Tables: Step 1: Deviation Sales NPV from Base Price/unit -30% 0% $1.50 30% Step 2: Deviation Sales NPV from Base Price/unit -30% $1.05 0% $1.50 30% $1.95 Step 3: Deviation Sales NPV from Base Price/unit $1,048.16 -30% $1.05 0% $1.50 30% $1.95 Deviation Sales NPV from Base Price/unit $1,048.16 -30% $1.05 0% $1.50 30% $1.95 project turned out to have a higher than expected initial cost. In other words, if the assumed inputs turn worse than expected, then the output will likewise be worse than expected. We use data tables below to the project's sensitivity to changes in the input variables. Following is a tutorial for constructing a Data Table to be used in sensitivity analysis. This sec be skipped if you already know how to construct data tables. Set up the Data Table by typing in the labels and numbers shown column for sales price/unit is the input range and the column for N output range. Data Tables take each input value and then automatic calculate a new output based on the input. Be sure to type in the ac price of $1.50 and not a formula. Every year we have students who mistake! Don't be one of them! Enter the formula =$B$117*(1+A116) into the light green cell and it into the light blue cell. This sets up the input range's values of sa for which you want new NPV's to be calculated. It is ok to have a fo the input range, but be sure that none of these inputs is a formula t back to the actual value of sales in the input section of the workshe Enter into the tan cell a formula that refers to the cell in the resul which shows the NPV for the given set of inputs. In this example, th =$I$15 . Notice that the tan cell will show the current value of NPV. Now use your cursor to hightlight the range we show in gray (this i the Data Table range); notice that this highlighted range includes th the new inputs for price and the cell for the reference to NPV. With the range still highlighted, open the Table dialog b Excel 2003, you go the Main Menu, select Data, then Ta Excel 2007 and 2010, select Data, What-If-Analysis, the Table.
Deviation Price NPV from Base $1,048.16 -30% $1.05 -$9,852.23 0% $1.50 $1,048.16 30% $1.95 $11,948.54 Deviation Equipment NPV Deviation Unit Sales NPV Deviation from Base $1,048 from Base $1,048 from Base -30% $5,425 $2,599 -30% 7,000 -$1,999 -30% $1.05 0% 7,750 1,048 0% 10,000 1,048 0% 1.50 30% 10,075 -503 30% 13,000 4,096 30% 1.95 Deviation VC/Unit NPV Deviation Non-VC NPV Deviation from Base $1,048 from Base $1,048 from Base -30% $0.75 $8,901 -30% $1,484 $2,309 -30% 7.00% This next step is a bit tricky, so be careful. The cursor in the dialog box will be blinking in the "Row inpu box. Here you have to tell Excel if the inputs in your Data Table are arranged in a row or a column. Exc assumes a row, but this is not correct in our example--your inputs are in a column, Column B.
- Fall '08
- Standard Deviation, probability density function