Correlations in Crystal Ball

2 Pages Posted: 21 Oct 2008

See all articles by Samuel E. Bodily

Samuel E. Bodily

University of Virginia - Darden School of Business

Miguel Palacios

University of Virginia - Darden School of Business

Abstract

The Crystal Ball add-in to Excel for Monte Carlo simulation has capabilities to correlate assumptions (uncertain quantities). This note describes the various ways that correlations may be used and issues a number of caveats.

Excerpt

UVA-QA-0593

Correlations in Crystal Ball

Crystal Ball has features that allow users to correlate the outputs from different assumptions. By using correlations, the output of a specific assumption variable becomes dependent upon another assumption variable. For example, a model might contain two assumption cells: the value of the S&P 500 and the value of the NASDAQ. If these assumptions were treated as independent (i.e., no correlation), the model would be missing the connection between the movements of the markets. If one were simulating the risk in a portfolio that has components tied to each market, the amount of portfolio risk would be wrong unless correlations were considered. Correlation would be useful in the study of diversification, hedging, and other risk-management activities.

There are two ways to introduce correlations between assumptions: (1) using the assumptions dialog box and (2) using the correlation matrix tool.

1. Correlations in the assumptions dialog box: The main dialog box that opens when you define a Crystal Ball assumption has a button called “correlate.” When you click the “correlate” button, a dialog appears in which all the assumptions of the model appear. When you select any of the other assumptions, you will be able to fill the correlation coefficient in the specified box. Notice that you can introduce the correlation coefficient directly, use a slide bar, or include a reference to a cell that contains the correlation. After setting the correlation coefficient (or referencing a cell that has the correlation number), push the “enter” button. This will instruct Crystal Ball to generate correlated trials for the assumptions when a simulation is run.

2. Correlation matrix tool: The previous method works when you are creating the correlation between a small number of assumptions, perhaps two or three. When the number of assumptions increases, manually inputting the correlation coefficients can become tedious. For such cases, Crystal Ball has a tool called “correlation matrix,” which you can find in the CBtools menu at the top of your screen (see Figure 1). When you use the correlation matrix tool, Crystal Ball automatically refers the correlation coefficients to a matrix that it creates in your spreadsheet. The numbers that appear in the matrix are actually linked to the assumptions.

. . .

Keywords: monte carlo simulation

Suggested Citation

Bodily, Samuel E. and Palacios, Miguel, Correlations in Crystal Ball. Darden Case No. UVA-QA-0593. Available at SSRN: https://ssrn.com/abstract=912021

Samuel E. Bodily (Contact Author)

University of Virginia - Darden School of Business ( email )

P.O. Box 6550
Charlottesville, VA 22906-6550
United States
434-924-4813 (Phone)
434-293-7677 (Fax)

HOME PAGE: http://www.darden.virginia.edu/faculty/bodily.htm

Miguel Palacios

University of Virginia - Darden School of Business

P.O. Box 6550
Charlottesville, VA 22906-6550
United States

Here is the Coronavirus
related research on SSRN

Paper statistics

Downloads
196
Abstract Views
1,228
rank
162,820
PlumX Metrics