Handout of Crystal Ball
Department of Finance
Fisher College of Business
The Ohio State University
Prof. George Pinteris
Handout on Crystal Ball
This handout supplements the lecture notes on Monte Carlo simulation techniques. In this handout, I will discuss how to use Crystal Ball to fit a distribution to historical data and how to produce tornado and sensitivity charts that allow the analyst to evaluate the impact of the model’s driver(s) on the model’s variable(s) of interest (such as firm value or NPV in the examples presented in the lectures).
Fitting Distributions to Historical Data
Unless we know from experience the type of probability distribution of the variables that are the model’s driver(s), this information needs to be discovered from historical data. Having obtained historical data for these variables, we can use Crystal Ball’s distribution fitting feature to select the appropriate distribution.
To evaluate the quality of the final selection, the analyst must use a goodness-offit criterion. Three popular criteria are available through Crystal Ball: the
Anderson-Darling test, the Chi-square test, and the Kolmogorov-Smirnov test.
Each has its advantages and disadvantages. The Chi-square test is most commonly used and, thus, it is recommended. The probability distribution with the lowest value for the criterion has the best fit and is the one selected.
To use Crystal Ball to fit a distribution to historical data, first select the cell where the value of the driver is and then follow these steps:
•
•
•
Select Define Assumption from the Define Menu
Select Fit o Insert the range of historical data if the data is in the current spreadsheet o Alternatively, select Text file and insert the name of the file where the data is stored o Specify whether Crystal Ball should attempt to fit all continuous distributions to the data or a subset that can be chosen by the analyst o Specify which is the primary criterion under which Crystal Ball ranks
the
Fisher College of Business
The Ohio State University
Prof. George Pinteris
Handout on Crystal Ball
This handout supplements the lecture notes on Monte Carlo simulation techniques. In this handout, I will discuss how to use Crystal Ball to fit a distribution to historical data and how to produce tornado and sensitivity charts that allow the analyst to evaluate the impact of the model’s driver(s) on the model’s variable(s) of interest (such as firm value or NPV in the examples presented in the lectures).
Fitting Distributions to Historical Data
Unless we know from experience the type of probability distribution of the variables that are the model’s driver(s), this information needs to be discovered from historical data. Having obtained historical data for these variables, we can use Crystal Ball’s distribution fitting feature to select the appropriate distribution.
To evaluate the quality of the final selection, the analyst must use a goodness-offit criterion. Three popular criteria are available through Crystal Ball: the
Anderson-Darling test, the Chi-square test, and the Kolmogorov-Smirnov test.
Each has its advantages and disadvantages. The Chi-square test is most commonly used and, thus, it is recommended. The probability distribution with the lowest value for the criterion has the best fit and is the one selected.
To use Crystal Ball to fit a distribution to historical data, first select the cell where the value of the driver is and then follow these steps:
•
•
•
Select Define Assumption from the Define Menu
Select Fit o Insert the range of historical data if the data is in the current spreadsheet o Alternatively, select Text file and insert the name of the file where the data is stored o Specify whether Crystal Ball should attempt to fit all continuous distributions to the data or a subset that can be chosen by the analyst o Specify which is the primary criterion under which Crystal Ball ranks
the