Software Review
Crystal Ball v7.0.1 Professional
Intuitive, well-constructed Excel add-in offers aid to anyone modeling uncertainty or risk, or working with forecasting or optimization problems.
By Wayne Holland
"The best way to predict the future is to invent it."
Gypsy Rose-Lee might take issue with the words of Alan Kay and instead invite us to look into her crystal ball (which many would think was much the same thing). A more profitable option might be to exchange the glass ball for a CD-ROM and install Decisioneering's® Crystal Ball® software onto your PC. It might not predict the future, but it can certainly help you to understand the possibilities.
Crystal Ball is an add-in to Microsoft Excel that provides facilities to perform standard-level Monte Carlo simulation and, in other versions, time-series analysis and forecasting, optimization and real-options analysis.
Monte Carlo simulation is a well-established method for handling risks and associated uncertainties in computer simulation models. It essentially involves generating hundreds (or maybe thousands) of replications of a model where uncertain variables are replaced with numbers generated from an appropriate distribution to represent the form of uncertainty of the particular variable. Crystal Ball provides you with assistance in performing Monte Carlo simulations by dividing the process into three stages:
Assumptions: Choose from 21 predefined probability distributions for your uncertain variables, or define your own probability distribution. Define your output variable.
Run: Use the various simulation control options (such as specification of number of runs or types of random variables) to perform the simulation experiment in the form of your choosing.
Forecast: Use Crystal Ball's summary chart and statistics option to analyze the output and extract the information required for managerial decision-making.
Crystal Ball has a very natural feel. Though ample documentation is provided, most users will have models up and running within minutes.
Let us consider the construction of a simple model in a little more detail to illustrate these points. Before doing so, a word on my installation battle.
Installation Trauma
To put this review in context, allow me to share a short story of installation woes caused by an accumulation of my working practices, IT support at my institution and Crystal Ball's rigorous registration procedure and technical support limitations. I'm sure most readers of this article are far more organized in their work and would never leave the production of a report until the last week before the deadline, as I did.
My IT department had installed Crystal Ball on my machine the previous week, without incident, and I did some experiments to find out its capability. However, in terms of production of screen captures and working with one or two advanced facilities, I had made little progress. With a week to go, disaster struck: a virus attacked my PC, necessitating the rebuilding of the operating system and reinstallation of all software.
At my institution, this cannot be done instantaneously, so I made appointments with IT to do it in stages over three days. In the meantime, I thought I would try to install Crystal Ball on my laptop. Unfortunately, the licensing is such that it can only be installed on one PC, and this is registered by the software with a server at Decisioneering's headquarters. So it was not possible for me to install it again on another machine, even though I was the only user.
When my IT support officer came to reinstall Crystal Ball on my PC, it failed. Some calls to technical support eventually produced the solution that the company's server was down and registration could not be completed until it was restored. This was one of the very few points in my life where being British was a distinct disadvantage. London is seven hours ahead of Colorado, and so as we were coming to the end of a working day (a Friday), Colorado hadn't started work, and there was no indication of how long it would take to sort out the server problem.
My IT support left me with temporary administration rights to complete installation on Saturday. However, when I tried to register, it was only partially successful. There were some error messages about incomplete registration. I phoned Decisioneering technical support, but was put through to an answering machine with a message that my call would be returned on the next business day — Monday. Too late for the completion of this review!
The lessons of my little tale:
1) Don't leave completion of reports close to deadlines (but hand on heart, how many people can claim never to have done this?);
2) This wouldn't happen if you worked at an institution that had open administration rights for its PC network (but given the proliferation of viruses, Trojan horses, etc., any sensible organization would want to protect itself); and
3) Rigor developments in protection of licenses and prevention of multiple installations is commercially understandable but should never hinder the installation process.
Basic Use of Crystal Ball
To illustrate the use of Crystal Ball, consider one of the numerous, well-documented examples installed with the Crystal Ball software.
An investor has $100,000 to invest in four assets. The rate of return of each asset is uncertain but believed to follow a particular probability distribution to reflect the conservative/volatile nature of each asset. The spreadsheet setting up the problem is shown in Figure 1.
Figure 1: Allocating $100,000 over four assets.
Assumptions. Assumptions is the name Crystal Ball gives to the choice of probability distribution for each variable in the problem. These can be selected from predefined or user-defined distributions. There is also the opportunity to modify a predefined distribution. For example, Figure 2 shows the normal distribution selected for one of the assets. It is possible to edit this so that the lower limit is set at zero (or any other value), rather than minus infinity. Once you have defined an assumption, the cell turns green. It does not contain any code.
Figure 2: Normal distribution for one of the assets.
This procedure is repeated for each assumption you wish to set. There is also a feature that allows you to correlate the various assumptions (not required in this case).
Run. The Run option allows the user to control a range of simulation parameters including the number of iterations (trials), random number seed (so that the same set of random numbers can be used each time the simulation is run — very useful for debugging the model) and type of sampling to be performed (Monte Carlo or Latin hypercube). Figure 3 shows the Run Preferences window.
Figure 3: Run Preference window.
Once you are satisfied with the setting up of your model, the simulation is performed by pressing the Run button. Whilst the simulation is running, you can either see the results being calculated or suppress output until the runs are finished for maximum speed.
Forecast. Before the simulation is run, the output variable needs to be defined. The output from the simulation is referred to by Crystal Ball as the forecast. Figure 4 shows the forecast definition form. The user can select what information is to be displayed for the forecast variable. As can be seen, the output can be presented in various forms.
Figure 4: The forecast definition form.
Once the simulation has run, information relating to the forecast can be displayed. Figure 5 shows the most basic information collected, the empirical distribution function for the forecast variable.
Figure 5: The empirical distribution function.
Crystal Ball has statistical goodness of fit tests built in to allow the user to identify the best probability distribution to fit the empirical. For this example, Figure 6 shows the result. It is satisfying, from a statistical perspective, to see that the software does not rely solely on the chi-squared test, widely used but generally considered inferior form of goodness of fit. It also runs the Kolmogornov-Smirnoff and, what many statisticians think of as superior, the Anderson-Darling test. This gave me a reassuring sense of the software having been constructed well and thoughtfully.
Figure 6: Result of statistical goodness of fit.
Additional Features of Crystal Ball
The Professional version of Crystal Ball comes with additional features. First, it provides CB Predictor for time-series analysis and forecasting. This facility essentially provides access to a wide range of standard forecasting techniques including Holt-Winters exponential smoothing, etc., and a multiple regression option. Whilst Excel Analysis Toolpak provides this facility, CB Predictor provides a much more user-friendly interface with a significantly greater range of output, essentially to properly analyze the appropriateness of any constructed regression model. Second, OptQuest provides an optimization package. In the example illustrated above, the amount invested into each asset could be defined to be a decision variable, and OptQuest could be used to find the optimal amount to invest to maximize the overall return. Unfortunately, I could get neither of these features to work in my reinstallation of Crystal Ball.
The Premium edition is the Professional edition plus a real-options analysis toolkit. This should prove a very useful tool for those working in this area.
New Features in Crystal Ball 7
For users of older versions of Crystal Ball, a summary of the new features include:
- improved quality charting and assumptions chart interface;
- ability to fit distributions to forecasts during simulation;
- a new outlook-like interface for the distributions gallery;
- a publish and subscribe facility for user-defined distributions;
- auto extract feature for writing forecast information to Excel after simulation;
- enhanced features for dealing with correlated assumptions;
- great flexibility in using predefined or custom-built report structures;
- enhanced distribution fitting to forecasts;
- new select menu format for picking which charts to show;
- changes to the way macros are handled. The preference tab has been replaced with the ability to search for macros of a certain name and execute them accordingly;
- a new control panel to provide a direct user interface to control simulations, instead of using the menu or toolbar;
- enhanced online help documentation;
- new probability distributions — Student's t, Discrete Uniform, Maximum and Minimum Extreme and Yes-No. Also parameter changes to the Beta and Hyper geometric distribution; and
- enhanced handling of distribution parameters.
For complete information, see www.crystalball.com/cb_features.html.
Forthcoming Feature of Crystal Ball 7.1
The day before this review was to be submitted, Decisioneering contacted me to inform me that they were about to announce the release of Crystal Ball 7.1. The major new feature in this update is "Extreme Speed," which, according to Decisioneering, can increase the simulation speed by up to 100 times. I cannot comment further on this development, as it was not part of the software I was reviewing.
Potential for Use in the OR/MS Community
For those who use Monte Carlo simulation, which has wide applications across the OR/MS world, Crystal Ball provides very useful functionality in building user-friendly models more easily. Firstly, the automatic handling of simulation repetition and output presentation means that the focus can be kept on the logic of model building. Secondly, most business students are familiar with Excel, and so the environment of the software feels very familiar to them. This means the learning curve is much shorter than it would be to use specialist simulation software.
Likewise, Crystal Ball has important application in the business world. Anyone who works in modeling uncertainty in a spreadsheet format could make use of, and benefit from, Crystal Ball. I make no case here that all simulation users should be working in Excel and Crystal Ball. Clearly, there are many practical cases with large, complex models where it would not be sensible to transfer these types of problems from programming languages or specialist software environment to spreadsheet. But for those workers currently building their models in spreadsheets, Crystal Ball provides a very convenient and helpful simulation tool.
If the above two paragraphs look familiar, they are essentially what I wrote in my review of @Risk for OR/MS Today [Holland, 2003]. The two packages are direct competitors, providing substantially equivalent software. The main differences are in feel and implementation, and thus which you prefer will be a very subjective judgment. For me, Crystal Ball was probably slightly more intuitive to pick up, with its Assumptions — Run — Forecast terminology and structure. On the other hand, for old-fashioned programmers like me, it was slightly disconcerting to only see numbers in cells that had been defined as probability distributions. To see statements like =RISKNORMAL(...) has a curious gratification for "old-school" modellers. As I said, this is extremely subjective, and to many people will be completely unimportant.
Quality of Documentation
The documentation for Crystal Ball is superb. The "Getting Started" book will get you setting up example models within 15 minutes of opening Crystal Ball. The main user guides for Crystal Ball, "OptQuest" and "CB Predictor," are all written in plain English, with plenty of examples to any sense of intimidation or information overload. I personally loved the reference card "which distribution should I use," which gives a layman's guide to features of probability distributions and examples of where you might find them used. I might use some of these examples (with appropriate credit) for some of my undergraduate classes.
The example spreadsheets that are installed with Crystal Ball are also all well-documented, explaining the problem being modelled, how it is being modelled in Crystal Ball, and how to get it to run.
Conclusion
Crystal Ball is a well-constructed piece of software to aid anyone modeling uncertainty or risk, or working with forecasting or optimization problems. The software is intuitive to pick up and well-supported with documentation and example when it is not. The main downside was the installation problems (maybe unique to me).
|
Wayne Holland is a lecturer in quantitative methods at Cass Business School, City University, London, UK. He teaches quantitative methods and management science to undergraduate and MBA students. His research interests are in design and analysis of simulation models, with particular application to transportation and supply chain networks.
References
- Holland, W., 2003, "Software Review: @RISK Version 4.5 Pro," OR/MS Today, Vol. 30, No. 1, pgs. 52-55.
OR/MS Today copyright © 2005 by the Institute for Operations Research and the Management Sciences. All rights reserved.
Lionheart Publishing, Inc.
506 Roswell Rd., Suite 220, Marietta, GA 30060 USA
Phone: 770-431-0867 | Fax: 770-432-6969
E-mail: lpi@lionhrtpub.com
URL: http://www.lionhrtpub.com
Web Site © Copyright 2005 by Lionheart Publishing, Inc. All rights reserved.