Correlation Matrix Example

In the Crystal Ball Examples folder, there is a Portfolio Allocation.xls workbook you can use to experiment with the Correlation Matrix tool. This spreadsheet calculates the total expected return for an investment model. In this example, you will run a simulation without correlations and then add the correlations and rerun the simulation for comparison.

  To start the Correlation Matrix tool:

  1. In Microsoft Excel with Crystal Ball loaded, open the workbook Portfolio Allocation.xls.

  2. Set the following options in the Run, then Run Preferences dialog:

    • On the Trials tab, set Maximum Number Of Trials to 500.

    • On the Sampling tab, set Random Number Generation to Use Same Sequence Of Random Numbers and set an Initial Seed Value of 999.

    • Also on the Sampling tab, set Sampling Method to Monte Carlo.

  3. Run a simulation by selecting Run, then Start Simulation.

    Choose View, then Statistics in the forecast chart. The forecast statistics for the simulation are similar to those shown in Figure 63, Uncorrelated Simulation Statistics, following.

    Figure 63. Uncorrelated Simulation Statistics

    This figure displays a split view showing uncorrelated simulation statistics.

  4. Choose Run, then Reset to reset the simulation.

  5. Select Run, then More Tools, then Correlation Matrix. (In Microsoft Excel 2007 or later, select More Tools in the Tools group.)

    The Select Assumptions panel opens.

  6. Include all the assumptions in the correlation matrix by moving all the assumptions from the Available Assumptions list to the Selected Assumptions list using one of these alternatives:

    • Double-click each assumption to move.

    • Select each assumption to move and click the Move Right arrowto move it.

    • Make an extended selection using the Shift or Ctrl keys and then click Move Right arrow.

  7. Click Next.

    The Specify Options panel opens.

  8. Select the following settings:

    • Create A Temporary Correlation Matrix On A New Worksheet

    • Upper Triangular Matrix

  9. Click Start.

    The tool creates a temporary matrix in a new workbook.

  10. Enter the following correlation coefficients into the matrix.

    This figure displays correlation coefficients to be entered into the matrix.

    Leaving a cell blank is not the same as entering a zero. Values that are not specified in the matrix will be filled in with estimates of appropriate values when the simulation runs.

  11. Click Load The Matrix.

    The tool loads the correlation coefficients from the matrix into the Crystal Ball model. Loading a full correlation matrix for all assumptions can take several minutes or even longer.

    If a Matrix Successfully Loaded message is not displayed, press Tab or Return to exit the current cell and then click Load The Matrix again.

  12. Reset the simulation.

  13. Rerun the simulation.

    The forecast statistics for the correlated simulation are shown in Figure 64, Correlated Simulation Statistics.

    Figure 64. Correlated Simulation Statistics

    This figure displays a split view showing correlated simulation statistics.

    The standard deviation is now much higher than the original simulation due to the correlations. The original model without the correlations ignored this risk factor and its effects.