Palisade Knowledge Base

Home → Techniques and Tips → Correlation in @RISK → Create a Correlation Matrix from Historical Data

5.15. Create a Correlation Matrix from Historical Data

Disponible en español: Crear una matriz de correlación a partir de datos históricos

Overview:

With @RISK, you can use correlation coefficients reported from historical data to simulate distribution functions created from the data. This is an effective way to use past observations to predict future behavior.

For example, you may have data from several years representing mortgage interest rates, mortgages sold, housing starts, and inventory of existing homes for sale. Each of these variables bears a historical relationship to the others. For example, the data may show that a rise in inventory of existing homes on the market is typically accompanied by a decrease in housing construction starts. Mortgage interest rates may exhibit a similar inverse relationship to both housing starts and mortgages sold. This web of historical relationships can be captured in correlation coefficients identified with an Excel correlation matrix. Coefficients from the Excel matrix can then be copy/pasted into an @RISK correlation matrix to control sampling of distributions that represent these variables.

To identify the correlation coefficients of your data:

  1. Make sure that your data are located in adjacent columns or rows in Excel. The Excel correlation analysis tool will not report correlations for non-adjacent selections made with the Ctrl key.
  2. In Excel 2010 or 2007, chose Data » Data Analysis; in Excel 2003 or earlier it's Tools » Data Analysis. The Data Analysis dialog appears. (If the Data Analysis item does not appear in the Excel menu, then you need to install the Analysis ToolPak Add-In from your Microsoft Office CD, or you need to place a check mark in the box beside Analysis ToolPak in your Excel Add-Ins dialog.)
  3. Select Correlation from the list box in the Data Analysis dialog and click the OK button. The Correlation dialog appears.
    1. In the area labeled Input, click the appropriate button to indicate whether your data are arranged in columns or rows, and check the box beside Labels in First Row (or Labels in First Column) if applicable.
    2. To indicate the range of data for which you want correlations reported, click the Collapse/Expand Dialog button (small red and blue square) beside the Input Range text box. This shrinks the dialog temporarily so that you can select your data from the Excel spreadsheet.
    3. Select your data. The cell reference for the selected range appears in the Input Range text box.
    4. Click the Collapse/Expand Dialog button again to expand the dialog.
    5. In the area labeled Output options, click one of the option buttons to indicate whether you want the Excel correlation matrix to appear in an Output Range in the same worksheet, a New Worksheet Ply (tab) in the same workbook, or a New Workbook. If you choose Output Range, you can enter a single cell. This cell will be the upper left cell at the beginning of the matrix location. If you choose New Worksheet Ply, you must enter a name for the new worksheet.
    6. Click the OK button. A correlation matrix appears in Excel reporting the correlation coefficients of your data.
  4. Create an @RISK distribution function from each column (or row) of data. (See "Distribution Fitting" in the @RISK manual or on-line help.) To ensure that variable and coefficient positions in the @RISK correlation matrix match positions in the Excel matrix, create the distributions down an Excel row, or across an Excel column, in the same order as the data columns from which the Excel matrix was created. (Alternatively, you can create the distribution functions, and then move them so that are arranged in the order reflected in the Excel matrix.)
  5. Look at the Excel matrix again and note carefully the order of variables and coefficients in that matrix.
  6. Select all cells in the Excel correlation matrix that contain correlation coefficients. Be sure to exclude the variable labels from the selection.
  7. Open the @RISK Model Window. If you followed step 4, the Inputs to be correlated should appear in the same order in which they were arranged in the Excel worksheet.
  8. From the Explorer pane (left-hand side) of the @RISK Model Window, use the Shift key to select the group of Input distributions you just created from your data.
  9. Right click the selection of Inputs. A popup menu appears.
  10. Choose Correlate Distributions from the popup menu. The @RISK Correlation window appears. Verify that the order of variables and coefficients in the @RISK correlation matrix matches the order of variables and coefficients in the Excel matrix.
  11. In the @RISK Correlation window, select all cells that contain correlation coefficients. Be sure to exclude the variable labels from the selection.
  12. From the menu in the @RISK Model Window choose Edit > Paste. Verify that the coefficients appear in their expected positions within the @RISK correlation matrix. Rename the matrix as desired by entering a new name in the Name text box.
  13. Click the Apply button to enter the correlation matrix in @RISK. The name of your @RISK correlation matrix now appears beneath the list of Inputs in the Explorer pane of the @RISK Model Window. In addition, correlation icons appear beside each correlated Input in the grid to the right of the Explorer pane, and the RiskCorrmat function now appears as an argument of each of the correlated distribution functions. In Excel, a new worksheet containing your @RISK correlation matrix has been added to the workbook.

last edited: 2012-08-08

This page was: Helpful | Not Helpful