Software Review
@Risk Version 4.5 Pro
Excel add-in is a valuable tool for modeling uncertain situations using spreadsheets.
By Wayne Holland
@RISK is a Microsoft Excel add-in to assist in the building and analysis of simulation models. It would be of interest to anyone building Monte-Carlo or discrete-event type simulation models in Excel for risk analysis or other applications. @RISK provides the user with assistance with simulation modeling in three ways.
First, it provides a range of 37 pre-defined probability distributions for generating random numbers to represent uncertain quantities in the user's model. Many of these distributions are set up so that the user can either enter traditional parameters to fit the distributions (for example, mean and variance), or the user can enter percentiles of the distribution.
Second, the output variables from the model are handled by @RISK procedures. Once the user has built a simulation template specifying which cells are random variables and which are output measures, @RISK will deal with the simulation replications — the user tells it how many iterations are required, and it will produce and store the resulting calculations.
Third, it summarizes the simulation output into user-friendly tables and graphs. For users of Excel, @RISK has quite a natural feel. Most of the options are intuitive and easy to investigate.
Features of @RISK
To illustrate the features of @RISK in action, I use the example taken from the @RISK manual on understanding the risks associated with introducing a new product. The situation: a company is planning to market a new product. There is uncertain growth in the market for the product. There is also uncertainty as to when or whether competitors will enter the market in future years, and if they do, how much of the market share they will take.
Input features. The user can select from 37 probability distributions. Ranging from the Beta to the Weibull, these distributions are intended to be used in a form analogous to Excel built-in functions that make their use natural to someone familiar with Excel. For example, the user enters RiskNormal(530, 101) in a cell to generate a sample of values during the simulation run from a normal distribution with a mean of 530 and standard deviation of 101. Similarly, cell entries of RiskGeometric(0.5) and RiskBinomial(100,0.1) would generate geometric and binomial variates, respectively, during simulation from distributions with indicated parameters. However, starting with version 4.5, many of the distributions may be entered with alternate parameter specifications. For example, RiskNormalAlt(5%, 40, 95%, 150) would fit a normal distribution with the specified 5th and 95th percentiles. There are also functions such as RiskDiscrete, RiskCumul and RiskGeneral that allow the user to specify their own distributions.
Figure 1 shows the example simulation model built in Excel. The important thing to note here is that we have a mixture of known quantities (for example, unit variable cost) and uncertain quantities (such as market size). The first table gives the known parameters for the model. The second table describes the logical relationship between the quantities and incorporates generation of values to represent the uncertain quantities. This example is forecasting market size over a five-year period. In this case market size is forecast using a normally distributed percentage annual increase that is generated with the function RiskNormal(1.05,0.01). Having established the relationships between the various quantities in the spreadsheet, the output from the model will be net present value (NPV), and the user informs @RISK that this is the output with the use of the RiskOutput function in cell B27.
Figure 1: Excel input worksheet
By clicking on input distribution, the user can investigate in more detail the probability distribution he or she intends to use. Figure 2 demonstrates the input distribution selection window. Here, we have a gamma distribution.
Figure 2: Input distribution window
Once the user has selected, or fitted, appropriate input distributions, the problem may be such that the user requires some of these distributions to be correlated. A user-friendly correlation distribution tool is provided that presents the user with a matrix into which correlation coefficients may be entered. An example of this matrix input window is shown in Figure 3.
Figure 3: Correlation matrix
Simulation features. For the basic user, the Simulation Settings Command allows the user to control a range of simulation parameters including the number of iterations, number of simulations and type of sampling to be performed. The number of iterations is the number of repetitions of a particular simulation experiment; the number of simulation parameters allows the user to conduct multiple experiments on the model using different input parameters, as specified in the SIMTABLE function. Figure 4 shows the Simulation Settings Command window.
Figure 4: Simulation Settings Command
For the advanced user seeking greater control, @RISK offers choice of standard Monte-Carlo random sampling or Latin Hypercube for stratified sampling, using the Sampling tab in the Figure 4. The user can specify the random number seed or allow @RISK to generate one randomly. There is also an option to allow the multiple simulation experiments either to use the same random number seeds or to generate different ones.
Once the user is satisfied with the setting up of the model, the simulation is performed by pressing the Start Simulation button (indicated on Figure 1).
Output features. @RISK handles the presentation of simulation output in a very satisfactory format. The user can direct output either to a results window or to Excel worksheet. The standard output table from @RISK is shown in Figure 5. It provides most of the results a typical user would be interested in.
Figure 5: Output window
For more sophisticated output analysis, @RISK provides cumulative distribution functions, percentile information, graphs of estimated density or cumulative distribution function and tornado diagrams for sensitivity analysis of the influence of each input random variable on the result variable. Some sample output is shown in Figure 6. It gives the distribution of NPV.
Figure 6: NPV graph
Comparison of @RISK4.5 with @RISK 4.0
For users of @RISK 4.0, the main changes in version 4.5 are:
- additional analyses including Advanced Sensitivity Analysis (described below), Stress Analysis and Goal Seek;
- "alternate" parameters for probability distributions allowing percentile parameters for many of the probability distributions;
- cumulative descending percentiles, where percentile probabilities can be optionally reported as cumulative descending values, as well as cumulative ascending values;
- Quick Reports, one-page reports in Excel containing statistics and graphs for a simulation result, formatted for printing;
- enhanced "define distribution" window, with point-and-click selection of references from Excel; and
- improved error reporting during simulation, where the "Pause on Error" option now identifies the output with errors and the cells in the user's model that caused the error for each.
Unfortunately, upon attempting to run ASA (or either of the two other advanced analyses, Stress Analysis or Goal Seek), I got a Visual Basic error ("Compile error in hidden module: Entry points.") and could not run the analysis at all. I suspect this could be an installation problem because, unlike as shown in the toolbar picture in the manual, I did not see the ASA icon on my toolbar.
Use in the OR/MS Community
Simulation is a core technique utilized by the OR/MS community. As a business academic, I find @RISK an excellent tool for introducing students to simulation modeling for two reasons. First, the automatic handling of simulation repetition and output presentation means that the focus can be kept on the logic of model building. Second, most business students are familiar with Excel, therefore 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, @RISK should be quite useful in the business world. Anyone who works in modeling uncertainty in a spreadsheet format could make use of, and benefit from, @RISK. I make no case here that all simulation users should be working in Excel and @RISK. 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 environments to spreadsheet. But for those workers building their models in spreadsheets, @RISK provides a very convenient and helpful simulation tool.
Quality of Documentation
Documentation is done well for @RISK. Help is available in three formats: 1) online help and reference facilities built into @RISK; 2) online tutorials provided with the software; and 3) the printed manual. The online tutorials include multimedia presentations by @RISK experts in .WMV format. This requires the user to have Internet Explorer and Windows Media Player available.
My personal preference when learning new software is to have a printed manual next to my keyboard that I can flick through as I work. @RISK's 499-page volume is a wonderful tool for learning about not only @RISK and its various features, but also about the subject of risk analysis. It is very easy for users inexperienced in risk analysis to build models without appreciating whether their approach is the most appropriate for their particular circumstances. The sections on "Overview of Risk Analysis" and "@RISK Modeling Techniques" provide a clear and concise tutorial in the use of risk analysis. The remaining sections provide installation instructions for a manual installation; full documentation on how to use each feature; a listing of each density function and cumulative distribution function and associated parameters. Dipping into the remaining sections will allow most users to work out how to do the things that might not be so intuitive, such as linking with VBA. So, full marks to Palisade on the quality of their documentation.
Installation Blues
My PC operating system is Windows NT 4.0. The IT policy of my system administrators is to partition hard disks into two drives; My C: is write-protected, while D: is a work drive that I am free to use as I wish. The CD-ROM I was provided would not work with Windows NT 4.0. On contacting Palisade technical support, it was confirmed that this was a defect, and I was given an FTP address to download an updated version that did work under the NT operating system.
However, my installation problems were not at an end. To install @RISK on my C: drive, I required system administrator access. Once the installation was complete (and the procedure is very simple; just click on the setup icon, choose the relevant directory information, and it should complete by itself), I found I could not run it when I logged back on as a user (with a write-protected C: drive). The only way I could run it was to log on as system administrator whenever I wished to use it. Now, if you are the administrator for your own PC then the above will be irrelevant. Indeed, an installation on Windows 2000 with the computer hooked on to the Internet where the user had full administrative rights went off without a hitch and the installation was completed in only a few minutes.
Conclusion
@RISK is a valuable tool for those working in the area of modeling uncertain situations using spreadsheets. Its flexibility, comprehensiveness and ease of use make it software suitable for serious business use as well as academic demonstration. Some of the output intended for an advanced or academic user may look intimidating to a beginning user; for example, kurtosis or sensitivity analysis using regression. However, the support provided should be sufficient for any non-expert who appreciates the need for risk analysis to make headway quickly.
|
|
References
-
@RISK Advanced Risk Analysis for Spreadsheets, Version 4.5, February 2002, Palisade Corporation, Newfield, N.Y.
Wayne Holland is a lecturer in Quantitative Methods at Cass Business School, City University, London, United Kingdom.
OR/MS Today copyright © 2003 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 2003 by Lionheart Publishing, Inc. All rights reserved.