You
can use cell references in many assumption dialog fields. The advantages
of using cell references are:
-
Saving time and reducing errors by building models to point to parameter
values in your spreadsheet.
-
Hiding details of the assumptions, so users can change the value of
a specific cell and the associated distribution changes automatically.
-
In the correlation dialog, developing a string of interconnected assumptions
to simulation the observation of a variable over time (a type of auto-correlation
or serial correlation).
Here
is an example using cell references as assumption parameters. Below
is a spreadsheet model with a column of variables with similar, but
not identical, characteristics.
Assume
that each inventory part has the same distribution type, but a different
set of parameters. Defining all these assumptions usually requires clicking
on each cell and stepping through the Distribution Gallery and Define
Assumption dialogs. This is somewhat tedious, depending on how many
assumptions you have to define.
A significant shortcut
Crystal Ball's assumption cell reference feature lets you specify
cell references instead of actual parameter values in the Define Assumption
dialog. The cell references point to cells in your spreadsheet with
the distribution parameters.
After you specify a cell reference for a parameter, you can set up
a whole series of similar assumptions. Just define one assumption,
and then copy it into the rest of your variables.
For example, in the inventory model above, the assumption cells are
in column B. For the first cell, B2, you assign a normal distribution,
but instead of entering a fixed value for the mean, you enter a cell
reference pointing to the mean value in the spreadsheet (in this case,
the same cell, B2). When you click on Enter, Crystal Ball uses the
value from the spreadsheet and uses it for the parameter of the selected
distribution.
Details
You can specify cell references for any distribution parameter field,
except for the Custom distribution parameter fields (which has a data
button to directly link information).
You must prefix each cell reference with an equals sign to tell Crystal
Ball that you are not entering a number. You can also use absolute
cell references by placing dollar signs before the column and row
designator (e.g., =$A$1), but then you lose the ability to paste the
data without changing it for each assumption.
When you enter a cell reference in any parameter field, two radio
buttons appear: Static and Dynamic. When you select Static, Crystal
Ball resolves all the cell references for the distribution when the
simulation starts, freezing the shape of the distribution for the
entire simulation.When you select Dynamic, Crystal Ball resolves cell
references each time the spreadsheet is recalculated (for each trial).
For this example, you would use Static.
Accelerated performance
Once you define the first assumption cell, you can copy it using the
Cell > Copy Data (Copy Assumptions for older versions) command
and paste it to other cells by selecting them and then using the Cell
> Paste Data (Paste Assumptions for older versions) command.
When you use the Paste Data command, Crystal Ball copies the assumption
from the clipboard into each selected value cell. The relative cell
reference in the copied assumption adjust to an equal relative position
to the pasted cell.
In the previous example, the mean parameter of each assumption will
point to the initial cell value. When running that simulation, Crystal
Ball will resolve all the cell references, giving each assumption
its unique distribution, using the cell value as the distribution
mean.
