Spreadsheet risk analysis uses spreadsheet models and simulation to analyze the effects of varying inputs on outputs of the modeled system.
Traditional methods of risk analysis have limitations:
Changing only one spreadsheet cell at a time makes it virtually impossible to explore the entire range of possible outcomes.
What-if analysis always results in single-point estimates that do not indicate the likelihood of achieving any particular outcome. While single-point estimates may tell you what is possible, they do not tell you what is probable.
Crystal Ball uses Monte Carlo simulation to overcome limitations encountered with traditional spreadsheet analysis:
You can describe a range of possible values for each uncertain cell in a spreadsheet. Everything you know about each assumption is expressed all at once. For example, you can define the business phone bill for future months as any value between $2500 and $3750, instead of using a single-point estimate of $3000. Crystal Ball then uses the defined range in a simulation.
With Monte Carlo simulation, Crystal Ball displays results in a forecast chart that shows the entire range of possible outcomes and the likelihood of achieving each of them. In addition, Crystal Ball keeps track of the results of each scenario for you.
Crystal Ball implements Monte Carlo simulation in a repetitive three-step process, described in Take a Look Behind the Scenes.
Monte Carlo simulation randomly generates a range of values for assumptions that you define. These inputs feed into formulas defined in forecast cells. You can use this process to explore ranges of outcomes, expressed as graphical forecasts. You can view and use forecast charts to estimate the probability, or certainty, of a particular outcome.
Monte Carlo simulation was named for Monte Carlo, Monaco, where the primary attractions are casinos containing games of chance. The random behavior in games of chance — roulette wheels, dice, and slot machines — 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 do not know which for any particular trial. It is the same with the variables that have a known range of values but an uncertain value for any particular time or event (for example, interest rates, staffing needs, stock prices, inventory, phone calls per minute).