Guide to Using

1 Guide to Using @RISK Risk Analysis and Simulation Add-In for microsoft excel Version February, 2004 Palisade Corporation 31 Decker Road Newfield, NY USA 14867 (607) 277-8000 (607) 277-8001 (fax) (website) (e-mail) Copyright Notice Copyright 2004, 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 RISK view are registered trademarks of Palisade Corporation. RISK is a trademark of Parker Brothers, Division of Tonka Corporation and is used under license. Welcome i 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.

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

3 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. ii Welcome @RISK uses a technique called "simulation" to combine all the uncertainties you identify in your modeling situation.

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

5 So when should you use @RISK? Anytime you make an analysis in excel that could be affected by uncertainty, you can and should use 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 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.

6 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. @RISK functions Welcome iiiThe 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 Available distribution types include: Beta BetaGeneral Beta-Subjective Binomial Chi-Square Cumulative Discrete Discrete Uniform Error Function Erlang Exponential Extreme Value Gamma General Geometric Histogram Hypergeometric Inverse Gaussian IntUniform Logistic Log-Logistic Lognormal Lognormal2 Negative Binomial Normal Pareto Pareto2 Pearson V Pearson VI PERT Poisson Rayleigh Student's t Triangular Trigen Uniform Weibull All distributions may be truncated to allow only samples within a given ranges of values within the distribution.

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

8 Available Distribution Types @RISK Simulation Analysis Graphics iv Welcome 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 @RISK graphs a probability distribution of possible results for each output cell selected in @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 Execution time is of critical importance because simulation is extremely calculation intensive.

9 @RISK is designed for the fastest possible simulations through the use of advanced sampling techniques. Advanced Simulation Capabilities High Resolution Graphic Displays Product Execution Speed Welcome v Table of Contents Chapter 1: Getting Started 1 Introduction ..3 Installation Instructions ..7 Quick Chapter 2: An Overview to Risk Analysis 15 Introduction ..17 What Is Risk? ..19 What Is Risk Analysis? ..23 Developing an @RISK Analyzing a Model with Making a Decision: Interpreting the 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 vs @RISK vi Table of Contents Chapter 4: Getting to Know @RISK 57 A Quick Overview to @RISK .. 59 Setting Up and Simulating an @RISK 69 Chapter 5: @RISK Modeling Techniques 91 Introduction .. 93 Modeling Interest Rates and Other Trends .. 95 Projecting Known Values into the 97 Modeling Uncertain or "Chance" Events.

10 99 Oil Wells and Insurance Claims .. 101 Adding Uncertainty Around a Fixed 103 Dependency Relationships .. 105 Sensitivity Simulation .. 107 Simulating a New Product: The Hippo Example .. 109 Finding Value at Risk (VAR) of a 119 Simulating the NCAA Tournament .. 123 Chapter 6: Distribution Fitting 127 Overview .. 129 Define Input 131 Select Distributions To 135 Run The 139 Interpret the Results .. 143 Using the Results of a Fit .. 151 Table of Contents vii Chapter 7: @RISK Reference Guide 153 Introduction ..161 Reference: @RISK Reference: @RISK Add-In Menu Commands 173 File Menu ..175 Model Menu ..177 Simulate Menu ..189 Results Menu ..203 Options Menu ..207 Advanced Analyses Menu ..209 Goal Stress Analysis ..217 Advanced Sensitivity Reference: @RISK Model Window Commands 245 File Menu ..247 Edit View Menu ..251 Insert Simulation Menu ..261 Model Menu ..263 Correlation Menu ..273 Fitting Graph Menu ..307 viii Table of Contents Artist 315 Window Menu.