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.

#### You've reached the end of your free preview.

Want to read all 446 pages?

- Fall '08
- Standard Deviation, probability density function