April 1997 Volume 24 Number 2
@RISK, Riskview and BestFit
For the competent risk analyst, suite of risk assessment products offers a powerful extension to spreadsheets
By Sam O. Sugiyama and Jeffrey W. ChowPalisade Corporation's DecisionTools -- @RISK, BestFit and RISKview -- offer the user a powerful set of software whose primary purpose is to develop risk assessment models using a spreadsheet. The mechanism is simulation, accomplished via recalculating the spreadsheet many times, each time drawing randomly from selected probability distributions. The results are saved and allow the user to access a variety of data/reports/charts in order to evaluate the risks in the analysis.
Each tool offers a user-friendly icon-oriented interface. Toolbars for each tool make access to their capability very easy. To use the tools correctly, however, requires the user to be a competent risk analyst.
@RISK has been in existence since 1987, BestFit since 1993 and RISKview since 1994. @RISK is the main risk assessment tool. It is an add-in to either Microsoft Excel or Lotus 1-2-3. @RISK allows you to add uncertainty to your spreadsheet model via @RISK functions. It comes in four versions: (1) for Lotus 1-2-3 for Windows, version 4 or later; (2) for Microsoft Excel 4.0 or 5.0 for Windows (works in Windows 95 and Windows NT); (3) for Microsoft Excel for Windows 95/Windows NT; and (4) for Lotus 1-2-3 for DOS. This review will focus on the Windows 95/Windows NT version, which has the latest features. The other Windows versions have most of these features. We will note the exceptions where applicable.
BestFit and RISKview are 16-bit applications that work in Windows, Windows 95 and Windows NT. BestFit is the tool that allows you to fit one of many probability distributions to data. The data can be either in the form of a sample, a sample probability function, or a sample empirical distribution function.
RISKview is the tool that allows you to graphically and statistically view a variety of distributions based on parameters you specify. This is an excellent tool for calibrating an expert's professional judgment. This can also be used to judgmentally fit data to a distribution.
Two help files -- @RISK and PDF (description of probability distribution functions available in @RISK) -- are very useful on-line technical resources. For ease of access you can place them as icons on your Microsoft Office Toolbar.
A practical configuration for @RISK requires that you have at least a 486 DX33 with at least 8MB RAM. To run large models you need much more RAM and a much faster processor. For models that are 1MB in size or are complex in structure, a Pentium 90 with 32 MB RAM is recommended. For very large models, I recommend using @RISK 3.5-the Windows 95 Microsoft Excel version-in Windows 95 or Windows NT.
As a practical configuration for BestFit, I would recommend a configuration consistent with @RISK as mentioned above. However, if your data set is small, then you will be able to operate with 8MB RAM.
RISKview is not very demanding on system resources. If your system is set up to operate BestFit or @Risk, then it will easily accommodate RISKview.
It is possible during setup to have BestFit and RISKview as add-ins to @RISK, but we recommend that you not do this. Using them as add-ins may create a system resource problem. A better research design would have you using BestFit and RISKview first to identify distributions for use in @RISK, rather than using them concurrently.
@RISK is the core model containing the @RISK functions that allow you to easily add probability distributions as easily as adding any function (for example, the SUM() function) to your spreadsheet. These probability distribution functions are called input distributions. The user then selects output cells (output distributions) for which @RISK will collect data for later processing. The process is one where @RISK recalculates the spreadsheet many times, taking random draws on the input distributions, and collecting the calculated results on the output distributions.
The virtue of this approach is that simulation may be the only way to approximate the solution to the result distribution, because the result distribution may not be a closed form. The simulation is usually started from the spreadsheet. Once the simulation has completed, @RISK brings up the @RISK results, which are not in the spreadsheet. These results can be easily transferred to the spreadsheet, however.
The main features of @RISK are:
The disadvantages are important to consider, because not all risk simulations should be performed on a spreadsheet, even if you have Windows 95/NT, an abundance of RAM and a powerful processor. Very large, complex problems are not meant to be spreadsheet models. Simulations for these models can take a considerable amount of time to run.
I have run complex 4-6 MB models for clients, containing 70 spreadsheets and 490+ @RISK input distributions. (This size may be approaching the upper limit, of @RISK models that can practically be created and run on a PC. I have yet to attempt a model larger in size.) The run time of 1,000-iteration simulations for these models has been (a) DX4-100: 5+ hours; (b) Pentium 150 system with 64 MB RAM: <2.0 hours; (c) Pentium 90 notebook with 40 MB RAM: 4-5 hours.
Another disadvantage, relative to a formal programming language, is that the flexibility of the spreadsheet comes at a cost -- although easy to develop and use, it is easy to write over formulas in cells. In contrast, once you have programmed a model and debugged it, you can be fairly certain that the code will perform the same calculations repeatedly.
Conclusions Regarding @RISK. I like the following features:
(a) @RISK has a very smooth, automated installation procedure; (b) it is powerful, yet has a user-friendly interface; (c) the spreadsheet model format can readily be revised to incorporate one's judgments on risk; (d) correlating uncertain variables is easy to implement; (e) companion products, BestFit and RISKview make a powerful complementary software risk assessment suite.
There are features that are lacking in @RISK:
(a) It does not provide a warning before running the simulation to indicate the simulation might not run. I believe there may be available tools that can survey the hardware and provide a sense of danger that the model may not run, based on size of model, number of input distributions and number of output distributions; (b) Palisade does not provide a means by which to calculate Spearman's rank correlation coefficients, which are the appropriate correlations to use for the correlated random variable approach. This capability is not available in either Excel or Lotus.
@RISK is an important addition to a decision-maker's toolkit of software. In today's world where decisions must be made without the luxury of the time it takes to build risk programming models, @RISK is the answer, because it is a powerful extension of a spreadsheet's capabilities.
The only caution is one I mentioned earlier: Although it is easy to use, to use it properly requires a competent risk analyst. (Palisade Corporation does sell a variety of texts to assist the user in model development.)
BestFit is a curve-fitting software that can be used to fit distributions to data. The data can be in the form of a sample of values, points on a probability function, or points on an empirical distribution function (cumulative probability distribution). In each case, BestFit first converts the data to a sample density function, then finds the density function from its library of functions, that best fits the sample density function.
BestFit is very easy to use. Once you've entered the data, results can be generated by simply clicking on icons. The disadvantage of this ease-of-use is that the ability to tailor the fit is limited. In particular, you cannot specify your own starting parameter values, which we consider to be a major limitation.
Procedure. As stated above, BestFit can accept data in one of three forms. You can type the data in directly, and copy/paste from a spreadsheet. BestFit also allows you to paste-link using OLE, but we recommend not doing so because this may tax system resources.
The fitting procedure is maximum likelihood (MLE), which can be further refined with the full optimization option (BestFit uses the Levenberg-Marquardt method to further improve upon the MLE method.) These methods do not guarantee a global optimum. Three goodness of fit tests are available -- chi square, Kolmogorov-Smirnov and Anderson-Darling -- each with their advantages and disadvantages.
Conclusions on BestFit. Price and ease of use make BestFit an attractive product. Expected improvements in tailoring the fitting process should resolve its major limitation.
RISKview is a distribution viewing tool. It comes in two versions, RISKview and RISKview Pro. RISKview Pro incorporates technology from BestFit to allow distribution fitting to hand-drawn curves. Select from one of 37 distributions, and select parameters to examine the particular shape generated by the parameters selected. RISKview is very easy to use. The challenge for many may be to put it to productive use. I have found this to be an invaluable tool for (a) eliciting probability judgments from experts; (b) creating a distribution based on your professional judgment; and (c) approximating a fit using a truncated distribution when BestFit could not adequately fit the data to a distribution.
Applications. RISKview can be used (a) to visually demonstrate skewness for a given density function (say the beta density function); (b) to examine the particular shape of one of the truncated distributions; (c) to query experts, then approximate their professional judgment.
Conclusion on RISKview. I recommend RISKview if (a) professional judgment is the only means available for assessing uncertainty, or (b) visual display is an important means of communication.
The available interactive on-line tutorials make learning the mechanics of @RISK, BestFit and RISKview relatively painless. If you are patient enough to read the User's Guides, you can learn the basics of operation with little assistance.
Converting the mechanics to a correct risk analysis is another matter.
Sam O. Sugiyama is president of Economic & Engineering Consultants in Portland, Ore. He received his Ph.D. in Economics from the University of Utah in 1981. Jeffrey Chow is a financial analyst with Economic & Engineering Consultants. He earned his master's degree in decision science/statistics, with a minor in finance, from the University of Oregon in 1993.
Reader Service Form
E-mail to the Editorial Department of OR/MS Today: firstname.lastname@example.org
OR/MS Today copyright © 1997, 1998 by the Institute for Operations Research and the Management Sciences. All rights reserved.
Lionheart Publishing, Inc.
2555 Cumberland Parkway, Suite 299, Atlanta, GA 30339 USA
Phone: 770-431-0867 | Fax: 770-432-6969
Web Site © Copyright 1997, 1998 by Lionheart Publishing, Inc. All rights reserved.
Web Design by Premier Web Designs, e-mail email@example.com