Guide to Using @RISK Risk Analysis and Simulation Add-In for Microsoft Excel ® Version 4.5 June, 2005 Palisade Corporation 798 Cascadilla St. Ithaca, NY USA 14850 (607) 277-8000 (607) 277-8001 (fax) http://www.palisade.com (website) sales@palisade.com (e-mail) Copyright Notice Copyright © 2005, Palisade Corporation. Trademark Acknowledgments Microsoft, Excel and Windows are registered trademarks of Microsoft, Inc. IBM is a registered trademark of International Business Machines, Inc. Palisade, TopRank, BestFit and RISKview are registered trademarks of Palisade Corporation. RISK is a trademark of Parker Brothers, Division of Tonka Corporation and is used under license. Welcome @RISK for Microsoft Excel Welcome to @RISK, the revolutionary software system for the analysis of business and technical situations impacted by risk! The techniques of Risk Analysis have long been recognized as powerful tools to help decision-makers successfully manage situations subject to uncertainty. Their use has been limited because they have been expensive, cumbersome to use, and have substantial computational requirements. However, the growing use of computers in business and science has offered the promise that these techniques can be commonly available to all decision-makers. That promise has been finally realized with @RISK (pronounced "at risk") -- a system which brings these techniques to the industry standard spreadsheet package, Microsoft Excel. With @RISK and Excel any risky situation can be modeled, from business to science and engineering. You are the best judge of what your analysis needs require, and @RISK, combined with the modeling capabilities of Excel, allows you to design a model which best satisfies those needs. Anytime you face a decision or analysis under uncertainty, you can use @RISK to improve your picture of what the future could hold. Why You Need Risk Analysis and @RISK Traditionally, analyses combine single "point" estimates of a model's variables to predict a single result. This is the standard Excel model -- a spreadsheet with a single estimate of results. Estimates of model variables must be used because the values which actually will occur are not known with certainty. In reality, however, many things just don't turn out the way that you have planned. Maybe you were too conservative with some estimates and too optimistic with others. The combined errors in each estimate often lead to a real-life result that is significantly different from the estimated result. The decision you made based on your "expected" result might be the wrong decision, and a decision you never would have made if you had a more complete picture of all possible outcomes. Business decisions, technical decisions, scientific decisions ... all use estimates and assumptions. With @RISK, you can explicitly include the uncertainty present in your estimates to generate results that show all possible outcomes. Welcome i @RISK uses a technique called "simulation" to combine all the uncertainties you identify in your modeling situation. You no longer are forced to reduce what you know about a variable to a single number. Instead, you include all you know about the variable, including its full range of possible values and some measure of likelihood of occurrence for each possible value. @RISK uses all this information, along with your Excel model, to analyze every possible outcome. It's just as if you ran hundreds or thousands of "what-if" scenarios all at once! In effect, @RISK lets you see the full range of what could happen in your situation. It's as if you could "live" through your situation over and over again, each time under a different set of conditions, with a different set of results occurring. All this added information sounds like it might complicate your decisions, but in fact, one of simulation's greatest strengths is its power of communication. @RISK gives you results that graphically illustrate the risks you face. This graphical presentation is easily understood by you, and easily explained to others. So when should you use @RISK? Anytime you make an analysis in Excel that could be affected by uncertainty, you can and should use @RISK. The applications in business, science and engineering are practically unlimited and you can use your existing base of Excel models. An @RISK analysis can stand alone, or be used to supply results to other analyses. Consider the decisions and analyses you make every day! If you've ever been concerned with the impact of risk in these situations, you've just found a good use for @RISK! Modeling Features As an "add-in" to Microsoft Excel, @RISK "links" directly to Excel to add Risk Analysis capabilities. The @RISK system provides all the necessary tools for setting up, executing and viewing the results of Risk Analyses. And @RISK works in a style you are familiar with -- Excel style menus and functions. @RISK Functions @RISK allows you to define uncertain cell values in Excel as probability distributions using functions. @RISK adds a set of new functions to the Excel function set, each of which allows you to specify a different distribution type for cell values. Distribution functions can be added to any number of cells and formulas throughout your worksheets and can include arguments which are cell references and expressions -- allowing extremely sophisticated specification of uncertainty. To help you assign distributions to uncertain values, @RISK includes a graphical pop-up window where distributions can be previewed and added to formulas. Welcome ii Available Distribution Types The probability distributions provided by @RISK allow the specification of nearly any type of uncertainty in cell values in your spreadsheet. A cell containing the distribution function NORMAL(10,10), for example, would return samples during a simulation drawn from a normal distribution (mean = 10, standard deviation = 10). Distribution functions are only invoked during a simulation -- in normal Excel operations, they show a single cell value -- just the same as Excel before @RISK. Available distribution types include: Beta Binomial Discrete Erlang Gamma Histogram IntUniform Lognormal Normal Pearson V Poisson Triangular Weibull BetaGeneral Chi-Square Discrete Uniform Exponential General Hypergeometric Logistic Lognormal2 Pareto Pearson VI Rayleigh Trigen Beta-Subjective Cumulative Error Function Extreme Value Geometric Inverse Gaussian Log-Logistic Negative Binomial Pareto2 PERT Student's t Uniform All distributions may be truncated to allow only samples within a given ranges of values within the distribution. Also, many distributions can also use alternate percentile parameters. This allows you to specify values for specific percentile locations of an input distribution as opposed to the traditional arguments used by the distribution. @RISK Simulation Analysis @RISK has sophisticated capabilities for specifying and executing simulations of Excel models. Both Monte Carlo and Latin Hypercube sampling techniques are supported, and distributions of possible results may be generated for any cell or range of cells in your spreadsheet model. Both simulation options and the selection of model outputs are entered with Windows style menus, dialog boxes and use of the mouse. High resolution graphics are used to present the output distributions from your @RISK simulations. Histograms, cumulative curves and summary graphs for cell ranges all lead to a powerful presentation of results. And all graphs may be displayed in Excel for further enhancement and hard copy. An essentially unlimited number of output distributions may be generated from a single simulation -- allowing for the analysis of even the largest and most complex spreadsheets! iii Graphics Welcome Advanced Simulation Capabilities The options available for controlling and executing a simulation in @RISK are among the most powerful ever available. They include: · · · · · · Latin Hypercube or Monte Carlo sampling Any number of iterations per simulation Any number of simulations in a single analysis Animation of sampling and recalculation of the spreadsheet Seeding the random number generator Real time results and statistics during a simulation High Resolution Graphic Displays @RISK graphs a probability distribution of possible results for each output cell selected in @RISK. @RISK graphics include: · · · · · Relative frequency distributions and cumulative probability curves Summary graphs for multiple distributions across cell ranges (for example, a worksheet row or column) Statistical reports on generated distributions Probability of occurrence for target values in a distribution Export of graphics as Windows metafiles for further enhancement Product Execution Speed Execution time is of critical importance because simulation is extremely calculation intensive. @RISK is designed for the fastest possible simulations through the use of advanced sampling techniques. iv Welcome Table of Contents Chapter 1: Getting Started 1 Introduction .........................................................................................3 Installation Instructions .....................................................................7 Quick Start.........................................................................................11 Chapter 2: An Overview to Risk Analysis 15 Introduction .......................................................................................17 What Is Risk? ....................................................................................19 What Is Risk Analysis? ....................................................................23 Developing an @RISK Model...........................................................25 Analyzing a Model with Simulation.................................................27 Making a Decision: Interpreting the Results.................................29 What Risk Analysis Can (Cannot) Do .............................................33 Chapter 3: Upgrade Guide 35 Introduction .......................................................................................37 New @RISK Model Window .............................................................39 New @RISK Add-in Features...........................................................43 New @RISK Results Window ..........................................................45 New Features in @RISK 4.5 vs @RISK 4.0.....................................49 Table of Contents v Chapter 4: Getting to Know @RISK 57 A Quick Overview to @RISK ........................................................... 59 Setting Up and Simulating an @RISK Model................................. 69 Chapter 5: @RISK Modeling Techniques 91 Introduction ...................................................................................... 93 Modeling Interest Rates and Other Trends ................................... 95 Projecting Known Values into the Future...................................... 97 Modeling Uncertain or "Chance" Events ....................................... 99 Oil Wells and Insurance Claims.................................................... 101 Adding Uncertainty Around a Fixed Trend.................................. 103 Dependency Relationships ........................................................... 105 Sensitivity Simulation .................................................................... 107 Simulating a New Product: The Hippo Example ......................... 109 Finding Value at Risk (VAR) of a Portfolio .................................. 119 Simulating the NCAA Tournament ............................................... 123 Chapter 6: Distribution Fitting 127 Overview ......................................................................................... 129 Define Input Data............................................................................ 131 Select Distributions To Fit............................................................. 135 Run The Fit...................................................................................... 139 Interpret the Results ...................................................................... 143 Using the Results of a Fit .............................................................. 151 vi Table of Contents Chapter 7: @RISK Reference Guide 153 Introduction .....................................................................................161 Reference: @RISK Icons................................................................163 Reference: @RISK Add-In Menu Commands 173 File Menu .........................................................................................175 Model Menu .....................................................................................177 Simulate Menu ................................................................................189 Results Menu ................................................ ...