April 1997 € Volume 24 € Number 2 ## @RISK, Riskview and BestFitFor 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. System RequirementsA 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@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: - 37 probability distributions from which to select.
- The ability to correlate the distributions, using the
nonparametric technique developed by Iman, et. al from Sandia
Labs.
- Latin Hypercube sampling is an option. Relative to simple
random sampling, Latin Hypercube sampling considerably reduces the
chance of underrepresentation of low probability outcomes. In our
opinion, this is the setting you always should use.
- RiskSimtable is a means by which to run the simulation under
identical conditions except for values of a select parameter, or
set of parameters. The number of alternative values for the
parameters is only limited by the system resources you have
available.
- Sensitivity analysis is available via (a) rank correlation or
(b) regression with normalized coefficients.
- Alternative Values for Policy Variables via RiskSimtable.
- Auto stop feature &endash; @RISK iterates (i.e., recalculates
the spreadsheet) until the difference between tested iterations is
within a pre-specified delta. The tests are on the mean, standard
deviation and every 5th percentile of the output distributions.
This procedure won't work if the true tested percentiles of the
distribution or the mean have a zero value.
- The ability to run macros between iterations. There are also
@RISK macros available to control the simulation and/or extract
selected information from the simulation.
- Includes an interactive tutorial that takes you systematically
through an example @RISK model.
- Standard output tables are accessible that provide most of the
results a user will need in order to assess a simulation and make
decisions. These can all be easily transferred from @RISK to the
spreadsheet. These are:
a) cumulative distribution functions for results at 5% intervals;
b) the ability to examine any percentile, or given a value, be provided the percentile;
c) graph of estimated density function and their associated ordered pairs;
d) graph of estimated cumulative distribution function and their associated ordered pairs;
e) tornado diagram showing the influence of each random variable on the result variable. The measures used are the absolute value of the rank correlation (or normalized regression coefficient) ordered from largest to smallest.
The user's guide is clearly set
up and walks the new user through simple examples to gain a
familiarity with @RISK. In addition, the on-line tutorial does the
same. All you are required to do is continue the tutorial at the
various pauses. The @RISK user's guide includes:
@RISK User's Guide. - detailed installation instructions for a manual installation;
- simple examples so that you can see a constructed @RISK model
and how to simulate, then view results;
- full documentation on how to use each feature;
- a thumbnail sketch of the conceptual basis for each feature;
- a listing of each density function and cumulative distribution function, and associated parameters;
@RISK can be used to incorporate
uncertainty into analysis that can be constructed on a spreadsheet,
assuming the system resources available are sufficient to the task.
Economic & Engineering Consultants (E&EC) have developed risk
assessment for clients in the following areas:
Applications. - Evaluating investment in new equipment or in a new process.
- Quality control problems.
- Queuing problems.
- Assessment of derivative securities (options, futures, etc.).
- Forecasting long-term and short-term electric energy
uncertainty.
- Evaluation of the value of mineral resources in the ground,
based on geologic and economic risk factors.
- Workman's compensation risk.
The advantages are easy to list; many analyses
do not require using an elaborate programming model, making a
spreadsheet model a feasible alternative. Also, the spreadsheet
interface is familiar, making models straightforward to create.
Finally, the spreadsheet environment makes it easy to create and
communicate results to management.Advantages and disadvantages of simulation on a
spreadsheet. 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. I like the following
features:Conclusions Regarding @RISK.(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.) BestFitBestFit 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. 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.Procedure.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. Price and ease of use make
BestFit an attractive product. Expected improvements in tailoring the
fitting process should resolve its major limitation.Conclusions on BestFit.RISKviewRISKview 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. 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.Applications. 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.Conclusion on RISKview.Closing ThoughtsThe 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.
For more information, put the number 4 in the
appropriate space on theReader Service Form E-mail to the Editorial Department of : orms@lionhrtpub.comOR/MS Today copyright © 1997, 1998 by the Institute for Operations Research and the Management Sciences. All rights reserved.OR/MS TodayLionheart Publishing, Inc.2555 Cumberland Parkway, Suite 299, Atlanta, GA 30339 USA Phone: 770-431-0867 | Fax: 770-432-6969 E-mail: lpi@lionhrtpub.com Web Site © Copyright 1997, 1998 by Lionheart Publishing, Inc. All rights reserved. Web Design by Premier Web Designs, e-mail lionwebmaster@preweb.com |