Risk Analysis in Capital Investments 223On rolling a dice, we know that 1, 2, 3, 4, 5, or 6 will come up, but we do not know which one exactly will
come up for a particular roll. It is the same with the variables that have a known range of values but an
uncertain value for any particular time or event (e.g., interest rates, staffing needs, stock prices, inventory,
and phone calls per minute). For each uncertain variable (one that has a range of possible values), you define
the possible values with a probability distribution. The type of distribution we select is based on the conditions
surrounding that variable. Distribution types include:
Source: Decisioneering.
To add this kind of function to an MS Excel spreadsheet, we would need to know the equation that
represents this distribution. With Crystal Ball 2000, these equations are automatically calculated for us. It
can even fit a distribution to any historical data that we may have. A simulation calculates multiple scenarios
of a model by repeatedly sampling values from the probability distributions for the uncertain variables and
using those values for the cell. Crystal Ball 2000 simulations can consist of as many trials (or scenarios) as
we want—hundreds or even thousands—in just a few seconds. During a single trial, it randomly selects a
value from the defined possibilities (the range and shape of the distribution) for each uncertain variable and
then recalculates the spreadsheet. For every spreadsheet model, we have a set of important outputs, such
as totals, net profits, or gross expenses that you want to simulate and analyze. Crystal Ball 2000 lets us
define those cells as forecasts. A forecast is a formula or output cell that you want to simulate and analyze. We
can define as many forecasts as you need, and when you run a Monte Carlo simulation with Crystal Ball
2000 , the latter remembers the values for each forecast, for each trial. During the simulation you can watch
a histogram of the results referred to as a Frequency Chart develop for each forecast. While the simulation
runs, we can see how the forecasts stabilize toward a smooth frequency distribution. After hundreds or
thousands of trials, we can view the statistics of the results (such as the mean forecast value) and the certainty
of any outcome. The following example is a forecast for total expected return.
Source: Decisioneering.