|
What do we mean by "simulation?"
When we use the word simulation, we refer to any
analytical method meant to imitate a real-life system, especially
when other analyses are too mathematically complex or too difficult
to reproduce.
Without the aid of simulation, a spreadsheet model will only reveal
a single outcome, generally the most likely or average scenario.
Spreadsheet risk analysis uses both a spreadsheet model and simulation
to automatically analyze the effect of varying inputs on outputs
of the modeled system.
| One type of spreadsheet simulation is Monte Carlo simulation,
which randomly generates values for uncertain variables over
and over to simulate a model. |
How did Monte Carlo simulation get its name?
Monte Carlo simulation was named for Monte Carlo, Monaco, where
the primary attractions are casinos containing games of chance.
Games of chance such as roulette wheels, dice, and slot machines,
exhibit random behavior.
The random behavior in games of chance is similar to how Monte
Carlo simulation selects variable values at random to simulate a
model. When you roll a die, you know that either a 1, 2, 3, 4, 5,
or 6 will come up, but you don't know which for any particular roll.
It's 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, phone calls per
minute).
What do you do with uncertain variables in your spreadsheet?
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 you select is based on the conditions surrounding
that variable. Distribution types include:
  
and  .
To add this sort of function to an Excel spreadsheet,
you would need to know the equation that represents this distribution.
With Crystal Ball, these equations are automatically calculated
for you. Crystal Ball can even fit a distribution to any historical
data that you might have.
What happens during a simulation?
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
simulations can consist of as many trials (or scenarios) as you
want - hundreds or even thousands - in just a few seconds.
During a single trial, Crystal Ball 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 more information about how Crystal Ball uses Monte Carlo simulation,
see the One-Minute Spotlight below:
|
