320 CHAPTER 8 Cash Flow Estimation and Risk Analysis
spreadsheet, Ch 08 Tool Kit Simulation.xls.^9 All three files are included on the text-
book’s web site. Using this model, we simulated 1,000 outcomes for the capital bud-
geting project. Table 8-5 presents selected results from the simulation.
After running the simulation, the first thing to do is to ensure that the results
are consistent with our assumptions. The resulting mean and standard deviation of
sales price are $3.01 and $0.35, respectively, which are virtually identical to our
assumptions. Similarly, the resulting mean of 0.4 percent and standard deviation of
14.8 percent for growth are very close to our assumed distribution. The maximum for
variable cost is $2.47, which is just under our specified maximum of $2.50, and the
minimum is $1.40, which is equal to our specified minimum. Unit sales have a maxi-
mum of 29,741 and a minimum of 15,149, both of which are consistent with our as-
sumptions. Finally, the resulting correlation between unit sales and growth is 0.664,
which is very close to our assumed correlation of 0.65. Therefore, the results of the
simulation are consistent with our assumptions.
Table 8-5 also reports summary statistics for the project’s NPV. The mean
is $13,867, which suggests that the project should be accepted. However, the range
of outcomes is quite large, from a loss of $49,550 to a gain of $124,091, so the proj-
ect is clearly risky. The standard deviation of $22,643 indicates that losses could
easily occur, and this is consistent with this wide range of possible outcomes.^10
The coefficient of variation is 1.63, which is large compared with most of RIC’sTABLE 8-5 Summary of Simulation Results (Thousands of Dollars)Risky Inputs Output
Sales Variable Unit
Price Cost Sales Growth NPV
Mean $3.01 $2.00 21,662 0.4% $13,867
Standard deviation 0.35 0.23 3,201 14.8 22,643
Maximum 4.00 2.47 29,741 42.7 124,091
Minimum 1.9 21.40 15,149 51.5 49,550
Median 10,607Probability of NPV 0 72.8%
Coefficient of variation 1.63(^9) We are grateful to Professor Roger Myerson of Northwestern University for making Simtool.xlaavailable
to us.
Note too that there are a number of commercially available simulation programs that can be used with
Excel, including @Riskand Crystal Ball. Many universities and companies have such a program installed on
their networks, and they can also be installed on PCs.
(^10) Note that the standard deviation of NPV in the simulation is much smaller than the standard deviation in
the scenario analysis. In the scenario analysis, we assumed that all of the poor outcomes would occur to-
gether in the worst-case scenario, and all of the positive outcomes would occur together in the best-case
scenario. In other words, we implicitly assumed that all of the risky variables were perfectly positively cor-
related. In the simulation, we assumed that the variables were independent, with the exception of the corre-
lation between unit sales and growth. The independence of variables in the simulation reduces the range of
outcomes. For example, in the simulation, sometimes the sales price is high, but the sales growth is low. In
the scenario analysis, a high sales price is always coupled with high growth. Because the scenario analysis’s
assumption of perfect correlation is unlikely, simulation may provide a better estimate of project risk. How-
ever, if the standard deviations and correlations used as inputs in the simulation are not estimated accurately,
then the simulation output will likewise be inaccurate. Remember the terms GIGO, or “garbage in, garbage
out,” and SWAG, or “scientific wild a guess”!