Take a Look Behind the Scenes

While powerful results are displayed in this example with virtually no effort, obviously there must be some drivers in the process. Crystal Ball cannot generate the same results for any typical spreadsheet without some help.

The key is using Crystal Ball to define certain input cells of the spreadsheet as assumptions and certain output cells of interest as forecasts.

After these cells are defined, Crystal Ball uses Monte Carlo simulation to model the complexity of a real-world scenario.

For each trial of a simulation, Crystal Ball repeats the following three steps:

Crystal Ball sequence for each trial of a simulation: generate a random number, recalculate the spreadsheet and retrieve a value from every forecast cell, and add to the forecast chart.

  1. For every assumption cell, a random number is generated according to the range you defined and then is placed into the spreadsheet.

  2. The spreadsheet is recalculated.

  3. A value is retrieved from every forecast cell and added to the chart in the forecast windows.

This is an iterative process that continues until either:

The final forecast chart reflects the combined uncertainty of the assumption cells on the model’s output. Keep in mind that Monte Carlo simulation can only approximate a real-world situation. When you build and simulate spreadsheet models, you need to carefully examine the nature of the problem and continually refine the models until they approximate the situation as closely as possible. See Crystal Ball Cells in the Example Model for an example.