Saturday, December 25, 2010

Using spreadsheet software like Crystal Ball for Monte Carlo simulation

One of the important aspect of risk analysis is selecting appropriate software. My tutorial paper introducing Monte Carlo Simulation ([1]) has a section titled Monte Carlo Simulation Software, which lists the ways Monte Carlo simulation can be implemented and used in practice. It is important from a practitioner’s perspective to use the software which will be most helpful to achieve the goal at hand: to analyze and make decisions on the problem s/he is working on.

Let us start off with a quick summary of this section from the paper. There are typically four options to choose from when performing risk analysis using Monte Carlo simulation:
a. High-level programming language like C/C++/Java/C#
b. General purpose simulation and modeling environment, like Matlab, R, Scilab, GNU Octave
c. Stand-alone simulation modeling software
d. Software add-in to spreadsheets like Oracle Crystal Ball

In this post, let us review some of the questions that one can ask when choosing the software to use. We will also comment on when a spreadsheet based software like Oracle Crystal Ball would be an appropriate choice.

1. Deterministic modeling: One of the first steps for performing risk analysis is developing a deterministic model of the situation. In this context, one can ask if the deterministic model can be developed in a spreadsheet environment? Some models like financial models are typically convenient to model on spreadsheet. On the other hand, modeling of physical systems are difficult on spreadsheet, since you would require other computationally intensive modules. Here is where the skillset of the analyst come in: if you are already familiar with Oracle Crystal Ball and would like to leverage the knowledge rather than learn new software, there are ways around this problem. One can use the macro hookup facility (macros to run before and after each trial) and run arbitrary code. If the modules were developed for running in *NIX platforms, one can use Cygwin to compile those for the Windows platform, and then call them from macros. There are a few other possibilities for other situations (like Matlab code etc.), but this is the general idea.

2. Access to historical data: Where does the data, that you want to use in modeling, reside? If they are in database servers, one can go either way on using Crystal Ball or using other software. On the other hand, if they are already in CSV format, or better yet, in spreadsheets like MS Excel, choosing CB would be easy.

3. Modeling uncertainty: Do you have historical data and want to use them to model uncertainty? In CB, you can use historical data to model either probability distributions, or use them to forecast future values which can be used as assumptions in your model. For a particular modeling exercise, you might want to do both: for some variables you would perform distribution fitting, for a few others, you need time series forecasting. Crystal Ball would be useful in this scenario: you can do both using the same software. Even if you want to model distributions from expert opinions, CB can still be used (alongwith appropriate distributions like triangular or betaPERT).

4. Distribution types: Does your model involve non-normal distributions? If your model involves just normal distributions for the uncertainties, it is easy to do in a regular programming language or even in environments like Matlab with a few lines of code. On the other hand, if your model involves non-normal distributions alongwith correlation structure between distributions, Crystal Ball would really be useful to model the situation easily.

5. Optimizing design parameters: Does your model involve optimizing design parameters? That would mean using a tool for performing simulation optimization, which can be done easily in Crystal Ball. Typically in design problems, different design objectives need to be balanced, which might be posed as an optimization problem. You might have to switch to other software for this part of the analysis if you choose other software.

6. Access to charts and reports: Finally, any professional analysis ends up with reporting, which needs access to quality charts and reports. Crystal Ball has inbuilt facility for generating charts and reports, whereas, if you use other general purpose software, you might have to generate these charts and reports manually. Depending on the volume and frequency of the analysis, this can consume considerable amount of time if done manually.

[1] Raychaudhuri, S. Introduction to Monte Carlo Simulation. In proceedings of Winter Simulation Conference 2008. Miami, FL. December 2008. Visit this link for access to this paper.