Wednesday, March 13, 2013

Displaying statistic in assumption cells

This question often comes up in group discussions and support questions: although the assumptions and forecast cells in an Oracle Crystal Ball spreadsheet model change values while running a simulation, they revert back to the original values at the end of simulation. This often confuses new users. In this post, I will discuss the reasoning and some options to change this behavior (partially). For an example of such discussion, check this question in our LinkedIn group.

First off, this is the default behavior of the software, and there is an explanation for this behavior. We can think about it this way: a simulation is a series of automated what-if analysis. We are trying out various input values in the assumption cells and then recording the output values from the forecast cell(s). The objective of the simulation is to get some statistical understanding of the output values (your forecasts) for a range of input values (your assumptions).

At the end of simulation, we are done with the series of what-if analysis, and we just return back to the original value that was there in the cells before we started the analysis. We consider these original/starting values as base-case scenario. Since the modeler (you, in this case) have started off with these values, that is where we return to.

But one can choose to see a different statistic in the assumption cells, extracted from the simulation results, after the simulation is complete. To do so, open the "Cell Preferences" dialog from the ribbon (it is the last one in the column left to the "Start" button, see Figure 1 below). There, choose your preferred statistic you wish to see after a simulation run for the assumptions in your model and apply this setting to your model using the 'Apply To..' button. Since the forecast cells are linked to one or more assumption cells by a formula, after the simulation they will automatically reflect the calculated values for the chosen statistic displayed in the assumption cells. 
Figure 1: Cell Preferences
For example, if you have chosen to show the mean statistic in the assumption cells after the simulation, the forecast cells will show the calculated value using the assumption means. Note that, depending on the formula, this final value shown in the forecast cell may or may not match with the corresponding statistic shown in  the assumption cell (mean in this case). If the forecast is non-linearly related to the assumption cells (i.e., the formula used in the forecast cell has nonlinear components like square, square root, log etc.), then the forecast mean would be different from the displayed value.

Note, it is not possible to show certain statistic for a few assumption cells and certain other statistic or none for a few other ones, as that would lead to display problems. The same display preference has to be applied to all assumptions in the model.

Update (3/15/2013): Corrected statement.