224 Corporate Finance
Certainty is the percent chance that a particular forecast value will fall within a specified range. For
example, in the given chart, we can see the certainty of breaking even (results better than $ 0) by entering the
$ 0 amount as the lower limit. Of the 5,000 trials that were run, 4,408 (or 88.16 percent) of those had a posi-
tive total expected return, so our certainty of breaking even is 88.16 percent. Therefore, the forecast results
not only show us the different result values for each forecast, but also the probability of any value. Other
charts allow us to examine different facets of our model.
The Sensitivity Chart lets us analyze the contribution of the assumptions (the uncertain variables) to a
forecast, showing us which assumptions have the greatest impact on that forecast. What factor is most
responsible for the uncertainty surrounding our net profit? Which geological assumptions are most important
when calculating oil reserves? Sensitivity analysis lets us focus on the variables that matter most.
The Overlay Chart lets us display multiple forecasts on the same axis, even when the forecasts are from
separate spreadsheet models. Which of six potential new projects has the highest expected return with the
least variability (smallest range of values) surrounding the mean? With the Overlay Chart, we can compare
and select the best alternatives.
The Trend Chart lets us stack forecasts so that we can examine trends and changes in a series. How do
our risks change over time?
Crystal Ball 2000 Standard is an easy-to-use simulation program that helps us analyze the risks and
uncertainties associated with MS Excel spreadsheet models that are deterministic, which means that the
inputs are fixed (one value to one cell). We can only see one solution at a time. If we want to view alternative
results, we need to manually change the inputs in the model. Simulation is a way to quickly generate and
analyze many possible results. Excel by itself cannot run simulations, so we need an add-in program like
Crystal Ball to make MS Excel do this. Because Crystal Ball is an analysis tool, we can use it to simulate
existing or new spreadsheets in any industry and for any application. For example, if a researcher in
the pharmaceutical industry develops the given spreadsheet to analyze the financial success of our project.
Without simulation, we calculate a net profit of $9,200,000. Not bad, but we have no sense of the likelihood
of this result. Are we 75 percent sure this will happen? How about being 100 percent sure? A simulation can
show us the probability of a given outcome. The first step to using Crystal Ball is to determine which model
inputs are uncertain. Which values are estimates? Which are averages? Once these are identified, we use
knowledge of the uncertainty around the input to create a probability distribution for that cell (what we call
an assumption). Crystal Ball lets us define these distributions.
Source: Decisioneering.