HomeTechniques and TipsCorrelation in @RISKHow and Why to Switch from RiskDepC to RiskCorrmat

5.14. How and Why to Switch from RiskDepC to RiskCorrmat

Applies to: @RISK 5.5 and newer

I'm using RiskIndepC and RiskDepC functions to correlate my inputs, but the manual says that's the old way, and I should use RiskCorrmat. Does it really matter?

If you have only one RiskDepC for each RiskIndepC, it really doesn't matter. @RISK creates a 2 × 2 matrix for each DepC/IndepC pair.

But multiple RiskDepC functions associated with any particular RiskIndepC can cause problems. To understand the issue, you need to know that @RISK creates a separate correlation matrix for each RiskIndepC function, using the RiskDepC functions associated with that RiskIndepC. The top row of the matrix is assigned to the variable designated with RiskIndepC, and the other rows to the RiskDepC variables with the same string identifier. The correlations you specify go in the first column of that matrix. The other columns of the matrix represent correlations among the RiskDepC variables, and since the DepC/IndepC scheme gives no way to assign those correlations, @RISK uses zeroes. (See Correlation Matrix Equivalent to RiskIndepC and RiskDepC in the attached workbook.)

Now, why is this bad? First, you may unwittingly create a matrix that is not self-consistent. For example, if you have two RiskDepC functions specifying correlations of 0.9 and 0.5 with your RiskIndepC, it's not mathematically possible for those two RiskDepC variables to be correlated to each other with a coefficient of 0.0, yet that's what @RISK uses, so you get the message that the matrix is not self-consistent. If you let the simulation proceed, @RISK will adjust the matrix to be self-consistent, changing not only the zeroes but the correlations you specified, and the simulated correlations may be very different from what you specified. (See Simulated Correlations if You Click OK in the attached workbook.)

Even if your matrix is self-consistent, for example two RiskDepC functions specifying 0.8 and 0.5, while it's mathematically possible for those two dependent variables to have a correlation coefficient of zero with each other, it's not very likely. Thus, your model may not be representing the real-world situation as accurately as possible.

What can I do to prevent these problems?

Switch to RiskCorrmat. Starting with @RISK 5.5, you can specify an adjustment weights matrix to tell @RISK to come up with a self-consistent matrix that preserves your desired correlations as far as possible, while assigning valid values to the correlations between the RiskDepC variables. The attached workbook gives an example of how to make the conversion:

  1. Construct the correlation matrix for each of your RiskIndepC variables, as described. Also construct an adjustment weights matrix with 100's in the first column and zeroes elsewhere.
  2. Use a RiskCorrectCorrmat array function to compute a self-consistent adjusted correlation matrix. (You may wish to give the new matrix a name in Excel, for convenience in editing formulas.)
  3. Change RiskIndepC and RiskDepC to RiskCorrmat. RiskCorrmat takes two arguments, the matrix and a variable number. Use 1 for the variable that used to have RiskIndepC, and 2 through n for the rest.

The end result is that the simulated correlations will match the ones you originally assigned in RiskDepC, as closely as mathematically possible.

If you wish, you can highlight the adjusted matrix and select first Copy, then Paste Special Values to replace the formulas with numbers. Then the original matrix and the adjustment weights matrix can be deleted.

Last edited: 2017-08-08

Downloads

This page was: Helpful | Not Helpful