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.
Updated
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 group | Correlation matrix | |
|---|---|---|
| Coefficients | One rank correlation for every pair of assumptions in the group. | A rank correlation for each pair. |
| In the workbook | The 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. |
| Default | Default group correlation on the Run page: 0.6. | None. |
| Defined with | Define 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
| MonteGrid | Crystal Ball | @RISK | |
|---|---|---|---|
| Correlation | Rank (Spearman), Iman–Conover. | Rank (Spearman), per Oracle. | Rank correlation coefficients in RiskCorrmat, per Lumivero. |
| Defined in | Cells: 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) | Independent | Correlated |
|---|---|---|
| P10 | -36.88 | -42.76 |
| P50 | 5.15 | 5.11 |
| P90 | 50.61 | 59.30 |
| SD | 34.67 | 40.47 |