MonteGrid | Articles | Correlated assumptions in MonteGrid

Correlated assumptions in MonteGrid

MonteGrid correlates assumptions by rank, as Crystal Ball and @RISK do. A correlation is a group or a matrix, stored in the workbook cells.

Method

MonteGrid draws the samples of each assumption, then reorders them so that their rank (Spearman) correlations match the targets. This is the Iman–Conover method. Each assumption keeps its distribution exactly: only the pairing of samples across assumptions changes. Rank correlation applies to any mix of distributions.

Iman–Conover imposes the target on normal scores as a linear correlation. That delivers a slightly lower rank correlation: a target of 0.8 arrives as about 0.786. MonteGrid converts each target to the linear correlation that delivers it, so the rank correlation in the trials is the one requested.

Groups and matrices

Correlation groupCorrelation matrix
CoefficientsOne rank correlation for every pair of assumptions in the group.A rank correlation for each pair.
In the workbookThe group name in each member's MC_STATIC, such as MC_STATIC(0.03,"Volume Growth","Demand").A block of cells: the name in the corner (MC_CORRMAT), the assumptions across and down, the coefficients between them.
DefaultDefault group correlation on the Run page: 0.6.None.
Defined withDefine Assumption, or the Model page.Define Correlation.

An assumption is in one group or one matrix, not both. MonteGrid reads the upper half of a matrix. If the lower half holds numbers, they must mirror the upper half. If the coefficients cannot all hold at once, MonteGrid refuses the matrix by name, and Define Correlation offers Adjust to nearest.

The header cells of a matrix reference the assumptions. When rows are inserted or an assumption moves, Excel updates the references, and the matrix follows the model.

The procedure is in Correlate assumptions.

Crystal Ball and @RISK

MonteGridCrystal Ball@RISK
CorrelationRank (Spearman), Iman–Conover.Rank (Spearman), per Oracle.Rank correlation coefficients in RiskCorrmat, per Lumivero.
Defined inCells: MC_STATIC and correlation matrix blocks.Crystal Ball's definitions, outside the cell formulas.Cells: RiskCorrmat and a matrix range.
Into MonteGrid—Not imported. The import gives the number of correlations to define.RiskCorrmat matrices convert to MonteGrid matrices. Copulas do not convert.

Details of each conversion are in Oracle Crystal Ball on a Mac and Lumivero @RISK on a Mac.

Effect on the results

In the capital budgeting model of the tutorials, two groups correlate the demand inputs and the cost inputs at 0.6. With the same 5,000 trials and seed, the NPV spread widens:

NPV ($M)IndependentCorrelated
P10-36.88-42.76
P505.155.11
P9050.6159.30
SD34.6740.47

Sources

  1. Oracle: Specifying Correlations Between Assumptions (Crystal Ball User's Guide)
  2. Lumivero: RiskCorrmat (@RISK help)

All articles