CHAPTER6 Selecting Run Preferences
ow that we have covered the basics of setting up a Crystal Ball model and using its forecasts to help you make decisions, we will take a closer look at the options
available to you through the Run Preferences menu to control the execution of your simulation models.
Figure 6.1 shows the Trials tab of the Run Preferences dialog. A Crystal Ball trial is the process of generating random variates from the stochastic assumptions you have deﬁned for your model, evaluating the formulas that depend on these values, then calculating and storing the forecast values.
When you click the Run button, Crystal Ball begins executing the steps depicted in Figure 6.2. The actions taken by Crystal Ball at each step are described as follows:
Start is where Crystal Ball prepares itself to run a simulation by looking for the assumption and forecast cells in your spreadsheet, and getting ready to store forecast values in your computer’s memory.
Set Values is where each trial begins by Crystal Ball generating a random value for each stochastic assumption and placing it in the corresponding assumption cell.
Recalculate is where Crystal Ball instructs Excel to use the values that were just placed in the assumption cells to update each cell in the spreadsheet that depends on the assumptions.
Get Results is where Crystal Ball takes the updated value from each of the forecast cells and stores it in memory. If you have enabled sensitivity analysis, Crystal Ball also stores the current value of each assumption cell during this step for possible analysis later with sensitivity charts.
Stop marks the completion of each simulation trial. If none of the stopping criteria described below have been satisﬁed, Crystal Ball returns to the Set Values step as indicated in Figure 6.2.
End is where Crystal Ball returns control to Excel after one of the stopping criteria is met, or after you have clicked on the Stop button in the Crystal Ball toolbar.
96 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL
FIGURE 6.1 Run Preferences Trials tab.
Crystal Ball executes the simulation cycle automatically, but it also gives you some control over selected aspects of the execution. This section describes those aspects.
Number of Trials to Run
This is the maximum number of trials you want Crystal Ball to run before it stops the simulation. There are other stopping criteria, so sometimes the simulation will stop before the maximum number of trials is reached. In general, the more trials you run, the better (more precise) will be your solution in a statistical sense. A rule of thumb is to use no fewer than 2,000 trials (see Figure 2.7). With Crystal Ball’s Extreme Speed feature, 10,000 trials will execute quickly for moderately complex models, so that is the number of trials speciﬁed in most of the examples shown in this book.
Stop on Calculation Errors
When this box is checked, Crystal Ball will stop the simulation when a numerical error occurs in an Excel calculation. Numerical errors are often caused by dividing by some zero somewhere. For example, for arrays of values that have different algebraic signs, using Excel’s =IRR(values,guess) formula in a forecast cell can sometimes cause a numerical error.
Selecting Run Preferences 97
FIGURE 6.2 Simulation cycle.
It is good practice always to have this box checked, as numerical errors are usually the result of errors in model logic and this feature of Crystal Ball will alert you if any such errors are present. However, if you are using =IRR(values,guess) in your model and have difﬁculty getting Crystal Ball to run to completion, try unchecking this box.
Stop When Precision Control Limits Are Reached
Checking this box enables Crystal Ball’s statistical precision-checking feature. The precision of an estimate is determined by the half-width of a (1 −α)100% conﬁdence interval, which is computed by Crystal Ball for the mean as zα/2s/ n, where zα/2 is the (1 −α/2)100 percentile of the standard normal distribution, s is the Standard Deviation, and n is the number of trials from which the standard error is computed. As discussed in Chapter 2, the value of s/ n is reported in the Statistics View of the Forecast window as Mean Standard Error. Smaller values of s yield smaller half-widths, which yields more precise estimates. Each of the three terms in the half-width calculation (zα/2, s, and n) can be used to increase the precision of the estimate of the mean.
98 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL
The value of zα/2 is affected by the number you enter in the Conﬁdence level ﬁeld in Figure 6.1, which is your speciﬁcation of 1 − α. For example, when you specify a 95 percent conﬁdence level, then zα/2 = z.975 = 1.96, which means that the precision is measured as roughly two standard errors of the mean. While there may sometimes be good reason to change the value of 1 −α in particular situations, leaving the conﬁdence level at 95% will sufﬁce for most ﬁnancial models.
Crystal Ball calculates the standard deviation of the forecast values, s, using Expression 2.1 as the simulation progresses. While s cannot be affected by options speciﬁed in the dialog shown in Figure 6.1, it is possible to increase precision by reducing s (or equivalently, the variance, s2) through so-called variance reduction techniques, which require structural changes to the model and are described in Appendix C.
The precision of the estimate increases at a rate inversely proportional to the square root of the number of trials. During the simulation, Crystal Ball recalculates the half-width periodically, then stops the simulation when either the maximum number of trials or speciﬁed precision is reached, whichever comes ﬁrst.
As an example of how to use the precision control feature, open the ﬁle Accumulate.xls, and click on the Forecast cell D4. Then select Deﬁne→Deﬁne Forecast... to get the Deﬁne Forecast dialog shown in Figure 6.3. Select the Precisiontab. Note that you might have to click on the More icon—two arrowheads pointing down ( ) in the upper right portion of the dialog to see the tabs. Check the two check boxes and punch the radio button indicated in Figure 6.3, and specify 50000 as the absolute units of precision as shown. Click OK.
In Run Preferences, change the Number of trials to run to one million (enter 1000000 with no commas), and make sure that Stop when precision control limits are reached is checked. Then click OK. Press the Run button on the Crystal Ball toolbar. Your simulation model should stop running well before 1 million trials have run, and you should see a Statistics View for Year 30 Wealth that looks like that shown in Figure 6.4.
It is also possible to specify precision for the median, the standard deviation, or a selected percentile. This is done by following steps similar to those above for speci-fying the precision of the mean. See the Crystal Ball User Manual for speciﬁc details.
Figure 6.5 shows the Sampling tab of the Run Preferences dialog, which has options related to the algorithms for generating the random numbers Crystal Ball uses to drive the simulation. This section gives a high-level overview of these options. For a more technical discussion, see Appendix B.
Random Number Generation
Random numbers are values between 0 and 1 that Crystal Ball generates to drive all the randomness in your models. The algorithm that Crystal Ball uses to generate
Selecting Run Preferences 99
FIGURE 6.3 Deﬁne Forecast dialog showing the Precision tab.
FIGURE 6.4 Statistics View for Year 30 Wealth forecast showing that a precision of $46,587 was reached in 40,000 trials.