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. Chow

Palisade 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.

Product Information

@RISK, RISKview and BestFit are distributed by Palisade Corporation, 31 Decker Road Newfield, NY 14867.

Phone: 1-800-432-7475 or (607) 277-8000; Fax: (607) 277-8001; E-mail: info@palisade.com; Web site: http://www.palisade.com

Pricing is as follows: @RISK, $395; BestFit, $299; RISKview, $79 and $149 (Pro).

For site, academic or other special pricing, contact Palisade.


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 Requirements
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
@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:
  1. 37 probability distributions from which to select.

  2. The ability to correlate the distributions, using the nonparametric technique developed by Iman, et. al from Sandia Labs.

  3. 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.

  4. 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.

  5. Sensitivity analysis is available via (a) rank correlation or (b) regression with normalized coefficients.

  6. Alternative Values for Policy Variables via RiskSimtable.

  7. 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.

  8. 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.

  9. Includes an interactive tutorial that takes you systematically through an example @RISK model.

  10. 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.
@RISK User's Guide. 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:
  1. detailed installation instructions for a manual installation;

  2. simple examples so that you can see a constructed @RISK model and how to simulate, then view results;

  3. full documentation on how to use each feature;

  4. a thumbnail sketch of the conceptual basis for each feature;

  5. a listing of each density function and cumulative distribution function, and associated parameters;
Applications. @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:
  1. Evaluating investment in new equipment or in a new process.

  2. Quality control problems.

  3. Queuing problems.

  4. Assessment of derivative securities (options, futures, etc.).

  5. Forecasting long-term and short-term electric energy uncertainty.

  6. Evaluation of the value of mineral resources in the ground, based on geologic and economic risk factors.

  7. Workman's compensation risk.
Advantages and disadvantages of simulation on a spreadsheet. 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.

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
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
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.


Closing Thoughts
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.

Vendor's Comments

Editor's Note: It is the policy of OR/MS Today to allow software developers an opportunity to clarify and/or comment on the review article. Following are comments from Joseph Prisco, technical director at Palisade.

Users running Windows 95 with sufficient amounts of RAM should not have difficulties running @RISK concurrently with BestFit® or RISKview™. In fact, RISKview was designed to be used while building a model with @RISK.

We have recently released the Decision Tools™ Suite which combines the three products the authors reviewed -- @RISK, BestFit and RISKview -- with TopRank® and Precision Tree™. TopRank performs what-if analyses on spreadsheets, ranking values in order of effect on the bottom line. TopRank identifies the critical inputs and allows the user to focus on them in a risk analysis with @RISK. PrecisionTree adds decision analysis to the user's spreadsheet. Decision trees and influence diagrams can be built directly in spreadsheet models. PrecisionTree can be combined with @RISK to run simulations on decision trees. All programs are designed to work together and are accessed via a common toolbar in MS Excel for Windows.

@RISK Developers Kit (RDK) addresses the reviewers' comment that "not all risk simulations should be performed in a spreadsheet" by allowing the user to develop simulation models using standard programming languages. The RDK allows users to add Monte Carlo simulation capabilities to any Windows application using standard programming languages such as C or Visual Basic. Simulation results can be viewed using the @RISK interface, or developers can create a custom interface themselves.

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 the
Reader Service Form



E-mail to the Editorial Department of OR/MS Today: orms@lionhrtpub.com


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
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