In Crystal Ball, assumption values are usually calculated independently of each other. Crystal Ball generates random numbers for each assumption without regard to how random numbers are generated for other assumptions. However, dependencies often do exist between variables in a system being modeled.
You can define correlations between pairs of assumptions. These relationships are described in mathematical terms using a correlation coefficient, a number between -1.0 and +1.0 that measures the strength of the relationship. A positive value means that when one assumption is high, the other is likely to be high. A negative value means that the assumptions are inversely related; when one is high, the other is likely to be low.
Crystal Ball uses rank correlation (Spearman) for all correlation computations to relate assumptions with different distribution types. For more information, see the "Statistical Definitions" chapter of the Oracle Crystal Ball Statistical Guide.
To correlate an assumption to one or more assumptions:
Click the More button beside the Assumption name in the Define Assumption dialog.
In the Define Correlation dialog, click Choose to select the assumption to correlate from the Choose Assumptions dialog. (You can only correlate assumptions in the same workbook.)
The Choose Assumptions dialog provides a list of the names of all the assumptions defined in the workbook.
By default, the dialog opens in a hierarchical Tree view. If you prefer, you can click the List icon,
, to change it to List view.Choose one or more of the assumption names on the list in the Define Correlation dialog and click OK (Figure 17, Define Correlation Dialog with Second Assumption).
After you select the second assumption, the cursor moves to the Coefficient Entry field (below the Choose button).
The chosen assumptions are displayed in the list of correlations. The currently selected assumption is also displayed immediately next to the Choose button.
Enter a correlation coefficient using one of the following methods and then click OK to accept the correlation and close the dialog box:
Enter a value between -1 and 1 (inclusive) in the Coefficient Entry field.
After you type the number, the slider control on the correlation coefficient scale moves to the selected value.
Choose a cell that contains the correlation coefficient.
If you choose a cell with values that change during the simulation, it is the initial value of the cell that is used for the coefficient.
Drag the slider control along the correlation coefficient scale.
The value you select is displayed in the Coefficient Entry field.
Type the desired correlation coefficient in the Coefficient field in the correlation list.
A small dialog opens at the bottom of the first dialog. Enter the range of cells on the spreadsheet that contains the empirical values that Crystal Ball should use to calculate a correlation coefficient.
Enter the range of cells in the standard A1:A2 format, where A designates the column and 1 and 2 designate the first and last cell rows, respectively. For example, if one set of values is in column Q, rows 10 through 15 and the second set of values is in column R, rows 10 through 15, enter the range in the first field as Q10:Q15 and the range in the second field as R10:R15.
Crystal Ball calculates the correlation coefficient, enters it in Coefficient Entry field, and moves the slider control to the correct position.
.
The two cell ranges do not necessarily have to have the same dimensions, but they must contain the same number of value cells and must be in the same workbook. The cell ranges are read in a row-by-row fashion.
Each time you select a new assumption or correlation coefficient, Crystal Ball displays a sample correlation of the correlated assumption values in the chart.
The points on the chart represent the pairing of assumption values as they would actually occur when running a simulation. The solid line running through the middle of the chart indicates the location where values of a perfect correlation (+1.0 or -1.0) would fall. The closer the points are to the solid line, the stronger the correlation.
In the example, an Inflation Rate assumption and an Oil Price/Barrel assumption have been correlated using a coefficient of 0.8, a strong positive correlation. As the points on the chart show, higher inflation values tend to be associated with higher oil prices and vice versa. This chart can help you begin to understand how the two assumptions are related.
You can specify as many of these paired correlations as you want for each assumption, up to the total number of assumptions defined in a workbook.
You can generally ignore correlations between variables if one or both variables do not impact the output or are not highly correlated.