5.3. Same Correlation Coefficients for Several Groups of Inputs

If the correlated groups aren't too large and there aren't too many of them, you can easily correlate separate groups of inputs through menu selections. For simplicity we'll show two groups of three inputs each. Within the attached example CorrelationGroups.xls, the two worksheets ModelA and @RISK Correlations were created by this method.

In @RISK 5.x–7.x:

You can now run the simulation. Inputs within each group will be correlated, but inputs in different groups will not be correlated. The worksheet CorrelationAudit_Report, which is created automatically within the workbook, shows that the actual correlations match the requested correlations quite well.

1. Highlight the first group of inputs you want to correlate, using Shift-click for a continuous range and Ctrl-click for non-adjacent cells.
2. Right-click and select @RISK » Define Correlations, or just click the Define Correlations icon in the ribbon.
3. A window opens into a new correlation matrix, with your selected inputs listed. Set your correlation coefficients, either above or below the diagonal.
4. Click the icon at the bottom of the window to check matrix consistency, and correct any problems. See How @RISK Tests a Correlation Matrix for Validity.
5. Near the top of the window, set the matrix location. If you wish, also give the matrix a name and description.
6. Just above the matrix, click the first icon, "Rename Instance", and enter a unique identifier for this group of inputs. It can be text or numeric, such as a year number.
7. In the same row of icons, click "Create New instance".  When prompted, enter a unique identifier for the second group of inputs that will use this correlation matrix. Click the Add Inputs button at the bottom and select the second group of inputs.
8. Repeat step 7 for each group of inputs that will use this correlation matrix.  After entering the last group, click OK.

Special case: If the groups of inputs are in a contiguous rectangular array, either as rows or as columns, you can short-cut the above process:

1. Click Define Correlations in the the ribbon. In the dialog box, click the Create Correlated Time Series icon near the top. (Despite the name of the icon, the groups of inputs don't actually have to be a time series.)
2. With your mouse, select the rectangle that contains all the groups of inputs that you want to correlate. Select correlation by rows or by columns.
3. Set your correlation coefficients, either above or below the diagonal.
4. Click the icon at the bottom of the window to check matrix consistency, and correct any problems. See How @RISK Tests a Correlation Matrix for Validity.
5. Near the top of the window, set the matrix location. If you wish, also give the matrix a name and description. Click OK.

In @RISK 4.x:

1. Open the Model window by clicking the icon "Display List of Outputs and Inputs". (Alternative: menu selections @RISK, Model, List Outputs and Inputs.)
2. In the Explorer-style list at the left, click the first correlated input in the first group, then Ctrl-click the other correlated inputs in the first group. Click the icon "Define Correlation". (Alternative: menu selections Model, Correlate Distributions.)
3. Enter your correlation coefficients, change the matrix name if you wish, and click Apply. You'll see a new Correlations category in the Explorer-style list at the left with the name of your correlation matrix, and @RISK creates a new "@RISK Correlations" worksheet in your workbook.
4. Right-click on the name of the correlation matrix in the Explorer-style list, and select Edit Correlation Matrix. In the menu line of the Model window, select Correlation, Instance, Create New instance. Give the instance a name.
5. Drag each input of the second group into the correlation matrix and when prompted select Replace. When you've done this with all the inputs of the second group, click Apply.
6. Repeat steps 4 and 5 for each additional group of correlated inputs.

You can now run the simulation. Inputs within each group will be correlated, but inputs in different groups will not be correlated. The worksheet CorrelationAudit_Report, which is created automatically within the workbook, shows that the actual correlations match the requested correlations quite well.

As an alternative to point-and-click, you can take advantage of Excel's ability to replicate formulas by dragging the fill handle. (Search Excel help for "fill handle" if this is unfamiliar to you.) This method scales well to larger groups of correlated inputs, or greater numbers of groups.

For this example we'll show ten groups of four inputs each, representing growth in the value of stocks and a bank account over ten years. Performances of stocks in a given year are positively correlated to each other but negatively correlated to interest rates. Within the attached example CorrelationGroups.xls, worksheet ModelB was created by this method.

1. Create your correlation matrix; row and column heads are optional but help to document the model. Highlight just the actual coefficients and define a name for them (menu selection Insert, Name, Define). In our example the correlation matrix including headings is C18:G23, and SecondCorr is the name of the 4×4 array of coefficients in D20:G23.

2. Set up your first group of correlated inputs as one row or one column. Create the distribution in the usual way, but add a RiskCorrmat function as an additional argument within the distribution function. The three arguments to RiskCorrmat are the name you assigned to the correlation matrix, the input number, and the instance. For reasons that will become clear in the next step, the instance argument should be a reference to the column header.

   In our example, the first group is Year 1, in column E. Growth factors are the @RISK distributions in cells E9, E11, E13, and E15; look at the formulas for those cells and see how the RiskCorrmat function is used. The new values at year end are in cells E10, E12, E14, and E16. Notice that the growth factors are correlated, but the year-end values are not.

3. Highlight the cells of the first year, E8:E16, and drag the fill handle to create the additional groups through year 10 in column N. Notice how the instance argument changes in each group, but is the same for all the inputs within a group; this was the reason for the cell reference in step 2. Note also that the named correlation matrix does not change from one column to the next.

You can now run the simulation. Inputs within each group will be correlated, but inputs in different groups will not be correlated. The worksheet CorrelationAudit_Report, which is created automatically within the workbook, shows that the actual correlations match the requested correlations quite well.