Risk Analysis - Tutorial

http://www.solver.com/risk-analysis/tutorial.htm

Risk Analysis - Overview

Risk analysis is the systematic study of uncertainties and risks we encounter in business, engineering, public policy, and many other areas. Risk analysts seek to identify the risks faced by an institution or business unit, understand how and when they arise, and estimate the impact (financial or otherwise) of adverse outcomes. Risk managers start with risk analysis, then seek to take actions that will mitigate or hedge these risks.

Some institutions, such as banks and investment management firms, are in the business of taking risks every day. Risk analysis and management is clearly crucial for these institutions. One of the roles of risk management in these firms is to quantify the financial risks involved in each investment, trading, or other business activity, and allocate a risk budget across these activities. Banks in particular are required by their regulators to identify and quantify their risks, often computing measures such as Value at Risk (VaR), and ensure that they have adequate capital to maintain solvency should the worst (or near-worst) outcomes occur.

Quantitative Risk Analysis

Models and Simulation

Monte Carlo Simulation

Quantitative Risk Analysis

Quantitative risk analysis is the practice of creating a mathematical model of a project or process that explicitly includes uncertain parameters that we cannot control, and also decision variables or parameters that we can control. A quantitative risk model calculates the impact of the uncertain parameters and the decisions we make on outcomes that we care about -- such as profit and loss, investment returns, environmental consequences, and the like. Such a model can help business decision makers and public policy makers understand the impact of uncertainty and the consequences of different decisions. Consult our risk analysis tutorial for more information about quantitative risk analysis.

Models and Simulation

One way to learn how to deal with uncertainty is to perform an experiment. But often, it is too dangerous or expensive to perform an experiment in the "real world" -- so we resort to a model, such as a scale model of an airplane in a wind tunnel. With a model, we can simulate what would happen in the real world, and perform many experiments -- for example, subjecting our model airplane to various air currents and forces -- and learn how it behaves. We can introduce uncertainty into our experiments using devices such as a coin toss, dice roll, or roulette wheel. A single experiment that involves a coin toss may not tell us very much, but if we perform a simulation that consists of many experiments or trials, and collect statistics about the results, we can learn quite a lot.

If we have the skills and software tools needed to create a mathematical model of a project or process on a computer, we can perform a simulation with many trials in a very short time, and at very low cost. With such advantages over experiments in the real world, it's no wonder that computer-based simulation has become so popular. For business models, Microsoft Excel is an ideal tool for creating such a model -- and simulation software such as Frontline Systems' Risk Solver can be used to get maximum insight from the model.

Monte Carlo Simulation

Monte Carlo simulation -- named after the city in Monaco famed for its casinos and games of chance -- is a powerful mathematical method for conducting quantitative risk analysis. Monte Carlo methods rely on random sampling -- the computer-based equivalent of a coin toss, dice roll, or roulette wheel. The numbers from random sampling are "plugged into" a mathematical model and used to calculate outcomes of interest. This process is repeated many -- typically thousands of -- times. With the aid of software, we can obtain statistics and view charts and graphs of the results. To learn more, consult our Monte Carlo simulation tutorial.

Monte Carlo simulation is especially helpful when there are several different sources of uncertainty that interact to produce an outcome. For example, if we're dealing with uncertain market demand, competitors' pricing, and variable production and raw materials costs at the same time, it can be very difficult to estimate the impacts of these factors -- in combination -- on Net Profit. Monte Carlo simulation can quickly analyze thousands of 'what-if' scenarios, often yielding surprising insights into what can go right, what can go wrong, and what we can do about it.

What is Risk, and Why Do We Care?

What are Sources of Uncertainty?

How are Uncertainty and Risk Different?

How Can We Best Deal with Risk?

Risk Analysis - Tutorial

What is Risk?

Uncertainty, which is constantly present in our daily lives, frequently impacts our decisions and actions. When we talk about risk, we normally mean the chance that some undesirable impact will occur. Hence, we normally seek to avoid or minimize risk. If there is a chance of rain, and we don't want to get wet, we may choose to stay indoors -- avoiding that risk -- or we may take an umbrella to minimize the impact of rain upon us.

Uncertainty can impact our decisions and actions in desirable as well as undesirable ways. If we own shares of stock, the future price is uncertain -- it may go higher, which is desirable, or it may go lower, which is undesirable. When contemplating large payoffs or penalties, most people are risk averse. For example, if we can choose between a coin toss that gains $50 or breaks even, and a coin toss that gains $150 or loses $100, most people would choose the first coin toss, even though the average or 'expected' outcome of both tosses is $25. Hence, in risk analysis we usually focus on what can go wrong -- the outcomes that represent loss or damage -- although an effective analysis will also help us understand what can go right as well.

What are Sources of Uncertainty?

Uncertainty can arise in several ways:

If the quantity we'd like to know is a competing firm's planned product price, uncertainty arises from our lack of knowledge: The price may be well known to that firm's employees, but it's unknown to us.

If the quantity is market demand for products like ours, uncertainty arises from the complexity of the process: Demand depends on economic factors, fashions and preferences, and our and other firms' actions -- and even if we knew all of these, we couldn't fully calculate their net impact on final demand.

If the quantity is a material thickness in nanometers, uncertainty may arise from limits on our ability to measure this physical quantity. We may also have limits on our ability to control fabrication of the material.

Many processes that we want to model -- from the failure rate of an electronic component to the behavior of a macromolecule -- have inherent randomness for all intents and purposes.

Uncertainty that is inherent in nature is sometimes called irreducible uncertainty. You may be able to reduce the effect of the random variation on your model, or reduce your model's sensitivity to this variation, but it will always be there. In other situations you may be dealing with reducible uncertainty -- through market research, physical tests, better calibration, or other means you may be able to reduce the uncertainty itself.

How are Uncertainty and Risk Different?

Uncertainty is normally an intrinsic feature of some part of nature -- it is the same for all observers. But risk is specific to a person or company -- it is not the same for all observers. The possibility of rain tomorrow is uncertain for everyone; but the risk of getting wet is specific to me, if (i) I intend to go outdoors and (ii) I view getting wet as undesirable. The possibility that stock A will decline in price tomorrow is an uncertainty for both you and me; but if you own the stock long and I do not, it is a risk only for you. If I have sold the stock short, a decline in price is a desirable outcome for me.

Many, though certainly not all, risks involve choices. By taking some action, we may deliberately expose ourselves to risk -- normally because we expect a gain that more than compensates us for bearing the risk. If you and I come to a bridge across a canyon that we want to cross, and we notice signs of weakness in its structure, there is uncertainty about whether the bridge can hold our weight, independent of our actions. If I choose to walk across the bridge to reach the other side, and you choose to stay where you are, I will bear the risk that the bridge will not hold my weight, but you will not. Most business and investment decisions are choices that involve "taking a calculated risk" -- and risk analysis can give us better ways to make the calculation.

How Can We Best Deal with Risk?

If the stakes are high enough, we can and should deal with risk explicitly, with the aid of a quantitative model. As humans, we have heuristics or "rules of thumb" for dealing with risk, but these don't serve us very well in many business and public policy situations. In fact, much research shows that we have cognitive biases, such as over-weighting the most recent adverse event and projecting current good or bad outcomes too far into the future, that work against our desire to make the best decisions. Quantitative risk analysis can help us escape these biases, and make better decisions.

It helps to recognize up front that, when uncertainty is a large factor, the best decision does not always lead to the best outcome. The "luck of the draw" may still go against us. Risk analysis can help us analyze, document, and communicate to senior decision makers and stakeholders the extent of uncertainty, the limits of our knowledge, and the reasons for taking a course of action.Risk Analysis - The Process

The process of risk analysis includes identifying and quantifying uncertainties, estimating their impact on outcomes that we care about, building a risk analysis model that expresses these elements in quantitative form, exploring the model through simulation and sensitivity analysis, and making risk management decisions that can help us avoid, mitigate, or otherwise deal with risk.

Identify and Quantify Uncertainty

Compute the Impact of Uncertainty

Complete a Risk Analysis Model

Explore the Model with Simulation

Analyze the Model Results

Make Decisions to Better Manage Risk

Identify and Quantify Uncertainty

In risk analysis, our goal is to identify each important source of uncertainty, and quantify its magnitude as well as we can. For example, we may not know our competitor's exact price, but we can place bounds on it, based on known production and marketing costs. While we can't predict the exact number of people shopping at a store each day, we can examine past data for the frequency of days when (say) 10, 20, 30, ..., 100 people shopped, and use this to estimate a distribution of shoppers on future days. This process of identifying and quantifying uncertainties is a key step in risk analysis.

Compute the Impact of Uncertainty

Our next step is to accurately estimate the impact of the uncertainties on the outcomes we care about. For example, we may not be able to predict demand for our product exactly; but given a number for demand, since we know our costs and margins, we can often calculate the impact on our Net Profit. We may not know the exact number of shoppers on any future day; but given a number of shoppers, we can calculate how many store salespeople we need to service them, and estimate the sales we're likely to generate. In doing this, we build a model that allows us to compute "outputs" -- outcomes such as Net Profit -- for any given "inputs".

Complete a Risk Analysis Model

If we can complete these steps, we'll have a risk analysis model (or simply risk model). The model has inputs which are uncertain -- these may be called uncertain variables, random variables, assumptions, or simply inputs. For any given set of input values, the model calculates outputs -- outcomes such as Net Profit.

Unlike other kinds of models, a risk analysis model requires us to think in ranges: Because the inputs are uncertain and may take on many different values, the outputs are also uncertain and may take on a range of values. If management asks, "Give me a number for next year's sales", a risk analyst must respond that a single number is not going to be meaningful -- it will defeat the purpose of risk analysis.

Explore the Model with Simulation

We can use our risk model in several ways -- but one effective way is to explore the possible outcomes using simulation. For a model in Excel, we can use software, such as Frontline's Risk Solver, to perform a Monte Carlo simulation on our model. Simulation performs many (thousands of) experiments or trials -- each one samples possible values for the uncertain inputs, and calculates the corresponding output values for that trial.

The first run of a simulation model can often yield results that are surprising to the modelers or to management -- especially when there are several different sources of uncertainty that interact to produce an outcome. Even before an in-depth analysis of the results, simply seeing the range of outcomes -- for example, how low and how high Net Profit can be, given our model and sources of uncertainty -- can encourage a re-thinking of the risks we face, and the actions we can take.

Analyze the Model Results

Because a simulation yields many possible values for the outcomes we care about -- from Net Profit to environmental impact -- some work is needed to analyze the results. It is very useful to create charts to help us visualize the results -- such as frequency histogram charts and cumulative frequency charts. We can summarize the range of outcomes using various kinds of statistics, such as the mean or median, the standard deviation and variance, or the 5th and 95th percentile or Value at Risk.

Another powerful tool for assessing model results is sensitivity analysis, which can help us identify the uncertain inputs with the biggest impact on our key outcomes. For example, a tornado chart can give us a quick visual summary of the uncertainties with the greatest positive and negative impact on Net Profit. Using software, we can also run multiple simulations, with an input we choose taking a different value on each simulation, and assess the results. Analyzing the model can give us more information, but also insight about our real-world problem.

Make Decisions to Better Manage Risk

The payoff comes when we use our risk analysis model and simulation results to make choices or decisions, that may help us avoid or mitigate risk -- or perhaps earn greater returns that help compensate us for taking these risks. We can also compare the risk and return of different projects or investments, and we can seek to diversify our position so that no single risk can do too much harm. By doing this, we can practice risk management.

While we can't avoid uncertainty and risk altogether, there are often many steps we can take to better cope with risk. Risk analysis helps us determine the right steps to take. Our next step in this Tutorial is to take a closer look at a risk analysis model.Risk Analysis - Models

A risk analysis model could be a physical scale model, but it is most often a mathematical model. The model can be created by writing code in a programming language, statements in a simulation modeling language, or formulas in a Microsoft Excel spreadsheet. Regardless of how it is expressed, a risk analysis model will include:

Model inputs that are uncertain numbers -- we'll call these uncertain variables

Intermediate calculations as required

Model outputs that depend on the inputs -- we'll call these uncertain functions

It is essential to realize that model outputs that depend on uncertain inputs are uncertain themselves -- hence we talk about uncertain variables and uncertain functions. To make use of a risk analysis model, we will test many different numeric values for the uncertain variables, and we'll obtain many different numeric values for the uncertain functions. We'll use statistics to analyze and summarize all the values for the uncertain functions (and, if we wish, the uncertain variables).

Creating the Model

Model Simplification

Next: Running the ModelCreating the Model

Since a risk analysis model will be subject to intensive computations, you'll generally want to create the model using available risk analysis tools. An Excel spreadsheet can be a simple, yet powerful tool for creating your model -- especially when paired with Monte Carlo simulation software such as Risk Solver. If your model is written in a programming language, Monte Carlo simulation toolkits like the one in Frontline's Solver Platform SDK provide powerful aids.

An example model in Excel might look like this, where cell B6 contains a formula =PsiTriangular(E9,G9,F9) to sample values for the uncertain variable Unit Cost, and cell B10 contains a formula =PsiMean(B9) to obtain the mean value of Net Profit across all trials of the simulation.

A portion of an example model in the C# programming language might look like this, where the array Var[] receives sample values for the two uncertain variables X and Y, and the uncertain function values are computed and assigned to the Problem's FcnUncertain object Value property:

Model Simplification

Like all models, a risk analysis model is a simplification and approximation of reality. The art of modeling involves choices of what essential factors must be included, and what factors may be ignored or safely excluded from the model. As Albert Einstein suggested, a model should be "as simple as possible, but no simpler."

We must also choose what sample values to test for the uncertain variables. Simulation software such as Risk Solver lets us draw sample values from scores of different probability distributions. While we should do our best to choose the right sample values, we derive a great benefit simply by moving from fixed values to almost any reasonable sample of values for an uncertain quantity.

Dr. Sam Savage likes to use the analogy of shaking a ladder before you use it to climb up on a roof. When you do this, you subject the ladder to a random set of forces, to see how it behaves. Even though the forces when you are shaking are not distributed in the same way as the forces when you are climbing, shaking a ladder is still a good stress test in advance.

Risk Analysis - Tools

Risk analysis tools are used to create a risk model, perform simulations using the model, and analyze the results. An Excel spreadsheet can be a simple, yet powerful tool for creating your model -- especially when paired with Monte Carlo simulation software such as Risk Solver. If your application calls for a programming language, Monte Carlo simulation toolkits like the one in Frontline's Solver Platform SDK provide powerful aids.

A special-purpose simulation modeling language can be a productive tool, but we must consider its non-simulation-related capabilities: For example, how easy is it to access external databases in the simulation language, or embed a model in this language in a larger application program? Such a language is most useful if your application calls for discrete event simulation, as opposed to Monte Carlo simulation. For example, a manufacturing process with many different "flows" of materials, and many steps taking place over time could be a candidate for a simulation modeling language. Running a Risk Analysis Model

Trials, Sampling, and Accuracy

Analyzing Model Results

Running a Risk Analysis Model

Once we have a complete model in a form appropriate for our chosen risk analysis tool, we can execute or "run" it, performing one or more simulations, to get results. We want the software to do the work, since it would take many hours to run the model manually, thousands of times.

The basic step in a simulation run, called a trial, is very simple:

1. Choose sample values for the uncertain variables, and "plug them into" the model.

2. Evaluate the model: Run the program, or recalculate the spreadsheet.

3. Observe and record the values of the uncertain functions.

For models with few uncertain variables, where the possible values of these variables cover a limited range, it may be possible to run a series of trials, where we systematically "step through" the range of each variable, in all combinations. For example, if we had two variables, X ranging from 1 to 100 and Y ranging from 10 to 50 in steps of 5, we'd perform a trial with X = 1 and Y = 10, then X = 1 and Y = 15, and so on for a total of 900 trials. But for most models of any size, this approach is impractical: We would need millions or even billions of trials, and running them all might not actually tell us very much.

Hence, simulation normally relies on random sampling of values for the uncertain variables: In step 1 above, we draw one or more random numbers -- analogous to flipping a coin or rolling dice on the computer -- and we use these numbers to randomly select sample values from the range of possible values (the distribution) of each uncertain variable. If we do this effectively (using high-quality random number generation and sampling methods), we can obtain excellent "coverage" of the possible values and model outcomes -- even if the model has many uncertain variables.

Trials, Sampling, and Accuracy

A simulation can be run for as many trials as you specify. To obtain more accurate results, you must run more trials -- so there is a tradeoff between accuracy of the results, and the time taken to run the simulation. But there are several ways you can improve this tradeoff, and obtain good accuracy in a limited amount of time: Ensure that each trial runs as fast as possible. If you create your model in a compiled programming language, it can execute trials at the fastest possible speed -- but this usually requires more development time. Running a model in Excel is slower, since your Excel formulas must be interpreted on each simulation run. But you can take advantage of PSI Technology in Frontline's Risk Solver software to run trials hundreds of times faster than using Excel alone.

Use a sampling method that provides better coverage of the possible values of variables, and lower variance (higher accuracy) for the outcomes, than standard Monte Carlo sampling for the same number of trials. Two advanced methods are Latin Hypercube sampling and Sobol numbers, which are an alternative to simple random numbers. Frontline's Risk Solver and Solver Platform SDK support both of these advanced sampling methods.

For demanding applications, such as those found in quantitative finance, use multiple streams of random numbers in combination with a high quality, long period random number generator to minimize any dependencies between samples for your uncertain variables. Again these capabilities are available in Frontline's Risk Solver and Solver Platform SDK.

Analyzing Model Results

Because a simulation yields many possible values for the outcomes we care about -- from Net Profit to environmental impact -- some work is needed to analyze the results. It is very useful to create charts to help us visualize the results -- such as frequency histogram charts and cumulative frequency charts. We can summarize the range of outcomes using various kinds of statistics, such as the mean or median, the standard deviation and variance, or the 5th and 95th percentile or Value at Risk.

Another powerful tool for assessing model results is sensitivity analysis, which can help us identify the uncertain inputs with the biggest impact on our key outcomes. For example, a tornado chart can give us a quick visual summary of the uncertainties with the greatest positive and negative impact on Net Profit. Using software, we can also run multiple simulations, with an input we choose taking a different value on each simulation, and assess the results. Analyzing the model can give us more information, but also insight about our real-world problem.Risk Analysis - Software

Risk analysis software covers a wide spectrum of capabilities and price points, from under-$1,000 general-purpose packages to $100,000 and much higher priced packages tailored for banks, insurance companies and enterprise risk managers in large corporations. Our focus here is on lower-cost general-purpose packages that offer great flexibility, but require that you "roll your own" risk analysis model. The most popular packages perform Monte Carlo simulation of models created in Microsoft Excel, with a surprising range of technical capabilities.

Some capabilities to look for in a general-purpose package include: High-quality (long-period and well-equidistributed) random number generation

Latin Hypercube and/or Quasi Monte Carlo based sampling for variance reduction

A wide range of analytic probability distributions -- leading packages have 40 or more

Flexible creation of custom distributions, both continuous and discrete

Ability to correlate dissimilar distributions, typically via rank order correlation

Ability to fit analytic distributions to user-supplied data or to simulation results

Statistics to measure central tendency and variation, quantile measures, and confidence intervals

Easily created but customizable charts and graphs of PDFs, CDFs, frequencies and cumulative frequencies

Sensitivity analysis of outputs against uncertain inputs

Ability to run multiple simulations, varying parameters across the simulations

Some advanced capabilities available in the best packages include:

Shifting and truncation of analytic distributions

Independent streams of random numbers for different distributions

Ability to run "trace simulations" using pre-generated simulation trials

Ability to model conditional distributions in simulation results

Ability to find optimal or near-optimal decisions using simulation optimization

Ability to create (analytic or custom) distributions and share them among modelers

Ability to create custom risk analysis applications, and distribute them to end users

And of course, the fastest possible execution of simulation trials

Finally, technical support and upgrades are a very important part of any software product that will be used for high-stakes risk analysis projects. The leading companies in this area have been selling and supporting software for more than 15 years; all of them offer, and charge for, "annual support" which includes software updates and upgrades, and ongoing hotline access.

It should come as no surprise that Frontline Systems' new Risk Solver product has been engineered to include all of the standard and advanced capabilities mentioned here. But there's no need to "take our word for it" -- you can download and install Risk Solver and use it, free of charge, for a 15-day evaluation period.

Monte Carlo Simulation Tutorial - Introduction

Welcome to our tutorial on Monte Carlo simulation -- from Frontline Systems, developers of the Excel Solver and Risk Solver software. In the next few pages, we'll show how you can convert a conventional spreadsheet model of a business plan sales forecast -- one that yields a flawed "average" Net Profit forecast based on average inputs -- into a far more realistic and useful simulation model that reveals the full range of Net Profit outcomes. And we'll show the power of Risk Solver software with Interactive Simulation -- which Dr. Sam Savage calls "a cure for the Flaw of Averages."

Wait! What is Monte Carlo simulation? Consult our Monte Carlo Simulation Introduction page for answers to:

What is Monte Carlo Simulation?

Why Should I Use Monte Carlo Simulation?

What Knowledge Do I Need to Use It?

How Will This Help Me in My Work or Career?

For background on simulation analysis and simulation models, consult our Simulation Introduction. For background on risk analysis, consult our Risk Analysis Overview. Our Risk Analysis Tutorial is designed to sharpen your thinking about uncertainty and risk, and how to identify and quantify the uncertainties you face.A Business Planning Example

Imagine you are the marketing manager for a firm that is planning to introduce a new product. You need to estimate the first year profit from this product, which will depend on:

Sales in units

Price per unit

Unit cost

Fixed costs

Profit will be calculated as Profit = Sales * (Price - Unit cost) - Fixed costs. Fixed costs (for overhead, advertising, etc.) are known to be $120,000. But the other factors all involve some uncertainty. Sales in units can cover quite a range, and the selling price per unit will depend on competitor actions. Unit costs will also vary depending on vendor prices and production experience.

Uncertain Variables

To build a risk analysis model, we must first identify the uncertain variables -- also called random variables. While there's some uncertainty in almost all variables in a business model, we want to focus on variables where the range of values is significant.

Sales and Price

Based on your market research, you believe that there are equal chances that the market will be Slow, OK, or Hot.

In the "Slow market" scenario, you expect to sell 50,000 units at an average selling price of $11.00 per unit.

In the "OK market" scenario, you expect to sell 75,000 units, but you'll likely realize a lower average selling price of $10.00 per unit.

In the "Hot market" scenario, you expect to sell 100,000 units, but this will bring in competitors who will drive down the average selling price to $8.00 per unit.

Unit Cost

Another uncertain variable is Unit Cost. Your firms production manager advises you that unit costs may be anywhere from $5.50 to $7.50, with a most likely cost of $6.50. The most likely cost is also the average cost.

Uncertain Functions

Net Profit

Our next step is to identify uncertain functions -- also called functions of a random variable. Net Profit is calculated as Profit = Sales * (Price - Unit cost) -Fixed costs. Sales Volume, Selling Price and Unit Cost are all uncertain variables, so Net Profit is an uncertain function.

The Flawed Average Model

At this point, we can summarize the problem in the Excel model pictured below, which calculates Net Profit based on average sales, price, and unit cost.

The Net Profit figure of $117,750 calculated by this model, based on average values for the uncertain factors, is quite misleading, as well see in a moment. The true average Net Profit is closer to $93,000! As Dr. Sam Savage warns, "Plans based on average assumptions will be wrong on average."Monte Carlo Simulation Tutorial - Introducing Uncertainty

To turn the spreadsheet model on the previous page into a risk analysis model, we need to replace the fixed Sales Volume, Selling Price, and Unit Cost amounts with variable amounts that reflect their uncertainty.

Sales and Price

Since there are equal chances that the market will be will be Slow, OK, or Hot, we want to create an uncertain variable that selects among these three possibilities, by drawing a random number -- say 1, 2 or 3 -- with equal probability. We can do this easily in Risk Solver using an integer uniform probability distribution. We'll then base on Sales Volume and Selling Price on this uncertain variable.

With cell B9 selected, we click the Discrete button on the Risk Solver Ribbon. This displays a dropdown gallery of discrete probability distributions. (A sample drawn from a discrete distribution is always one of a set of discrete values, such as integer numbers.) We click to choose "IntUniform" from the gallery.

Risk Solver displays the Uncertain Variable dialog with a chart of the integer uniform distribution -- initially with parameters lower 0 and upper 10. We edit these parameters to read lower 1 and upper 3. This means that on each trial, we'll draw a number 1, 2 or 3 from this distribution.

When we click the Save icon in the dialog toolbar, a formula =PsiIntUniform(1,3) is written to B9. B9 is now an uncertain variable. If we now press F9 to recalculate the spreadsheet, a different value -- either 1, 2 or 3 -- appears each time in cell B9.

Now, we need to select one of the three sales scenarios in formulas for Sales Volume and Selling Price. With cell B4 selected, we enter the formula:

=CHOOSE(B9,E5,E6,E7) for Sales Volume

This will cause B4 to return 100,000, 75,000, or 50,000, depending on the value in B9. Next, with cell B5 selected, we enter the formula:

=CHOOSE(B9,F5,F6,F7) for Selling Price

This will cause B5 to return $8, $10 or $11, depending on the value in B9.

Notice that the values returned by B4 and B5 are related, or correlated: Higher sales volume is accompanied by lower selling prices, and vice versa. If we allowed scenarios with 100,000 units sold at $11 each, our model would be unrealistic. Risk Solver supports more versatile ways to specify correlation between uncertain variables, but this approach is easy to understand in this example.

Monte Carlo Simulation Tutorial - Introducing Uncertainty Continued

So far, we've modified our spreadsheet model to introduce uncertainty for Sales Volume at cell B4 and Selling Price at B5. Now we'll deal with Unit Cost. We have not just three, but many possible values for this variable: It can be anywhere from $5.50 to $7.50, with a most likely cost of $6.50. A crude but effective way to model this is to use a triangular distribution. Risk Solver provides a function called PsiTriangular() for this distribution.

With cell B6 selected, from the Risk Solver Ribbon we click the Continuous button to display a dropdown gallery of continuous probability distributions. (Unlike a discrete distribution, a sample drawn from a continuous distribution can be any numeric value, such as 5.8 or 6.01, in a range.) We choose "Triangular" from the gallery.

Risk Solver displays the Uncertain Variable dialog with a chart of the triangular distribution -- initially with parameters min 0, likely 1 and max 3. We want to edit these parameters -- but instead of entering fixed numbers, we'll use the range selector icon at the right of each field to select cells containing the parameters: min E11 ($5.50), likely E12 ($6.50) and max E13 ($7.50).

This means that on each trial, we'll draw a number between $5.50 and $7.50, where $6.50 is the most likely value to be drawn -- as shown in the chart of the triangular distribution below. Hence $6.45 and $6.55 are more likely than $5.55 or $7.45 -- but any of these and other numbers has a chance of being drawn on each trial. When we click the Save icon in the dialog toolbar, a formula =PsiTriangular(E11,E12,E13) is written to B6. We now have a second uncertain variable in our model.

Next, we'll move on to define an Uncertain Function. But we can do much more than shown here with the Uncertain Variable dialog: The view above shows all of its panes open (click the image to see it full size). You can browse different distributions, shift and truncate a distribution, see each distribution's Probability Density Function (PDF), Cumulative Density Function (CDF) or Reverse CDF, see statistics and percentiles for the distribution, automatically fit a distribution to user-supplied data, and customize the chart.Monte Carlo Simulation Tutorial - Uncertain Functions and Statistics

Weve now defined the uncertain variables in our risk analysis model. Anything calculated from these uncertain variables is an uncertain function, but usually were interested only in specific results such as Net Profit at cell B11. When we turn on Interactive Simulation, B11 will effectively hold an array of values, each one calculated from different values sampled for B4, B5, B6 and B7.

What would we like to know about the array of values for Net Profit at cell B11? The simplest summary result is the average (or mean) Net Profit. Note that this will be the true average of Net Profit across 1,000 or more scenarios or trials -- not a single calculation from average values of the inputs. With cell B11 highlighted, we click the Statistics button on the Risk Solver Ribbon. A dropdown gallery shows us the available statistics functions, which we can "drag and drop" into a worksheet cell.

When we click the Mean button, a small balloon like the one below appears and follows the mouse pointer as we move to a worksheet cell, in this case B12. When we click to select the cell, the formula =PsiMean(B11) appears in the cell.

When we define a summary statistic, such as PsiMean(B11), weve implicitly designated cell B11 as an uncertain function. Risk Solver will keep track of the full range of trial values for B11 during a simulation, and will display frequency and sensitivity charts, statistics and percentiles for it on demand. As noted above, in principle anything calculated from the uncertain variables is an uncertain function -- but to save time and memory, Risk Solver keeps track of trial values only for the formula cells that we designate. There are several ways (besides using a statistic such as PsiMean) to designate a cell as an uncertain function.Monte Carlo Simulation Tutorial - Using Interactive Simulation

So far, we've modified an ordinary spreadsheet model by defining selected cells as uncertain variables, and one cell (Net Profit) as an uncertain function. That's it! -- we have a risk analysis model in the form required by Risk Solver software. We're now ready to run a Monte Carlo simulation, and see how uncertainty affects our spreadsheet model.

With an old-fashioned simulation software package, you'd press a button to start a simulation, then perhaps get a cup of coffee. Because simulations ran slowly, software packages were designed for "batch" operation: You'd spend time getting everything set up just right, run a simulation and wait (sometimes quite a while), then spend time analyzing the results. But with Risk Solver, simulations run so fast that fully Interactive Simulation is practical.

To turn on Interactive Simulation, we simply click the light bulb on the Ribbon. It will light up, as shown below. In the blink of an eye, your first Monte Carlo simulation is complete!

From now on (until we click the light bulb again to turn it off), 1,000 Monte Carlo simulation trials (the default number) will be run each time you change the spreadsheet, and cell B12 will display the true average for Net Profit across these 1,000 trials:

The result of shaking the ladder is striking: Our true average Net Profit for these 1,000 trials is only $93,493 quite a bit less than the Flawed Average Model figure of $142,000! And we also see that we can lose money -- the last of the 1,000 trials, which appears on the worksheet, shows a loss of $21,153.

Try pressing F9 (the Excel recalculate key) on this model: Each time you do, another 1,000 Monte Carlo trials are run, and a slightly different true average Net Profit figure will be displayed -- but nearly always much less than $142,000.

Monte Carlo Simulation Tutorial - Viewing the Full Range of Profit Outcomes

We've turned our spreadsheet model into a risk analysis model, and we've turned on Interactive Simulation. One immediate insight is that the mean or true average Net Profit, over 1,000 different simulated outcomes, is much less than we expected from our nave Flawed Average model. Weve also seen that in some outcomes, our Net Profit is actually a loss.

A quick look at the dropdown galleries for Statistic, Risk Measure and Range on the Risk Solver Ribbon suggests that we can easily compute and view many other statistics about Net Profit. But wed really like to see the full range of outcomes in this model. This is very easy to do in Risk Solver.

With Interactive Simulation turned on, simply move the mouse pointer to B11 and wait about 1 second. A miniature, live frequency distribution chart of the simulation trial values for cell B11 appears automatically:

To see and do more, just double-click on B11, the cell calculating Net Profit, to display Risk Solver's Uncertain Function dialog, with a customizable frequency chart of these outcomes, as shown below:

We see immediately that in some outcomes, we can lose of lot of money -- more than $50,000! We also see that we make a profit in most outcomes -- but how many exactly? With Risk Solver, we can point and click on the chart to find out, as shown on the next page.Monte Carlo Simulation Tutorial - Focusing on Profitable Outcomes

Our risk analysis model is now showing us the full range of outcomes for Net Profit. We see that in some outcomes, we can lose of lot of money -- more than $50,000! We also see that we make a profit in most outcomes -- but how many exactly? In Risk Solver, by just right-clicking over the chart and selecting Add Lower, we can quickly put a vertical bar at 0 on the horizontal axis:

When we do this, the Certainty box at the bottom is updated to reflect the percentage of outcomes still in the blue region. (You can also place a vertical bar on the upper part of the horizontal axis, if you wish.) We see that we make a profit in about 92% of the outcomes.

Another view of the full range of outcomes is shown on the Cumulative Frequency tab. Each bar on this tab shows the frequency with which Net Profit was less than or equal to the value on the horizontal axis. We can also look at this information numerically with percentiles, as shown on the next page.

Monte Carlo Simulation Tutorial - Statistics and Percentiles

So far in our business forecast risk model, we've looked at charts of the full range of Net Profit outcomes, in the form of frequency and cumulative frequency charts. The Percentiles tab in the Risk Solver Uncertain Function dialog shows the same information as the Cumulative Frequency tab, but in numeric form. In the chart below, weve scrolled down to show the 50th through 64th percentile values:

While we're looking at numbers, let's click on the Statistics tab, which displays summary statistics for the full range of Net Profit outcomes. We can see that the worst case outcome of this simulation was -$62,446, and the best case outcome was +$210,012. Value at Risk 95% shows that we have a 5% chance of losing $11,348 or more, and Conditional Value at Risk 95% shows that the average of all the possible losses we might realize beyond the VaR 95% level is $33,298.

Now we have a pretty good idea of the full range of outcomes for Net Profit. In a conventional what-if spreadsheet model, we'd now be asking "How can we increase Net Profit?" But with a risk analysis model, we can also ask and answer questions like "How can we reduce the chance of a loss?" and "How can we reduce the variability of Net Profit?" Sensitivity analysis can help us answer all these questions, as discussed on the next page.Monte Carlo Simulation Tutorial - Sensitivity Analysis

We've converted our spreadsheet model to a risk analysis model, run a Monte Carlo simulation with Risk Solver, and examined the full range of outcomes for Net Profit through statistics and percentiles, charts and graphs. Now we can begin to take steps towards risk management: Using the model to determine how we can reduce the chance of a loss -- and increase the chance of a (larger) profit.

The Sensitivity tab in the Uncertain Function displays a Tornado chart that shows you how much Net Profit changes with a change in the uncertain variables -- our integer uniform distribution at cell B9, and the triangular distribution for Unit Cost at cell B6. In this model there are only two uncertain variables, but in a large model with many such variables, its usually not obvious which ones have the greatest impact on outcomes such as Net Profit. A Tornado chart highlights the key variables, as shown below:

Our Unit Cost at cell B6 is negatively correlated with Net Profit, as expected: Higher Unit Costs leads to lower Net Profits. Notice that our integer uniform distribution at cell B9 is positively correlated with Net Profit: What does this mean?

Since we used =CHOOSE(B9,E5,E6,E7) for Sales Volume, on each trial where B9 is 1 we use 100,000 units sold; when B9 is 2 we use 75,000 units sold; and when B9 is 3 we use 50,000 units sold. The fact the B9 is positively correlated with Net Profit is telling us that we make higher profits when the market is slow, not when it's hot. Our typical Selling Price is lower when the market is hot, and the increased Sales Volume doesn't make up for this.

The state of the market is outside of our company and our direct control. But our production costs, while variable and uncertain, are more subject to our control. Having seen that our Net Profit suffers in a hot market because of the narrow margin we have between our typical Selling Price and our Unit Cost, we're motivated to try to improve this situation.

We want to ask "what if our (uncertain) Unit Costs could be reduced?" -- in the presence of our uncertain Sales Volume and Selling Price. Risk Solver empowers us to ask and answer exactly this question, as shown on the next page.

Monte Carlo Simulation Tutorial - Interactive Simulation with Charts and Graphs

Interactive Simulation makes Risk Solver fundamentally different from other Monte Carlo simulation tools for Excel. The kinds of charts weve just seen can be produced by other tools, but only at the end of a simulation run. In contrast, Risk Solver makes these charts live as you play what-if with your model.

After seeing this model, your production manager might think of a way to reduce the maximum Unit Cost to $7.00 instead of $7.50. What would be the impact of this change on Net Profit, over the full range outcomes? With Risk Solver, this is as easy as any other what-if question in Excel.

We click the Frequency tab in the Uncertain Function to re-display the frequency chart of outcomes for cell B9. Then simply change the number in cell E13 -- the high end of the Unit Cost distribution -- from 7.50 to 7.00. (The other parameters of our triangular distribution -- low $5.50, most likely $6.50 -- haven't changed.)

Immediately, a thousand Monte Carlo trials are performed, and the chart is updated. The effect is striking: We have better than a 98% chance of making a profit, and -- checking the Statistics tab -- we see that instead of a worst-case loss of -$62,446, we have a worst-case loss of only -$14,421!

The original spreadsheet 'Flawed Average' model presented a limited and misleading picture of this business situation. In contrast, the Risk Solver model has illuminated the situation considerably. We can see what can go right, and what can go wrong. We can make an informed decision about whether the reward is worth the risk. And -- most important -- we can interactively explore ways to improve the reward and reduce the risk. This is risk analysis and risk management at work.

We've now progressed all the way through the process of building a simulation model and using Monte Carlo simulation for risk analysis and decision-making. But most often, completing the analysis doesn't mean we are finished -- we must present our results to others. And this example model was very small -- most realistic models will contain many more uncertain variables and functions, statistics, correlations and other features. We'll explore how Risk Solver can help us with these issues in two concluding pages.

Monte Carlo Simulation Tutorial - Charts and Graphs for Presentations

Often, you may be called up to present your results to others. With Risk Solver, one great way to do this is in Excel itself, live! But you can quickly create high-quality charts and graphs of your results, print them, or copy and paste them into PowerPoint or any Windows application. You can control chart color, dimensionality and transparency, bin density, titles and legends, axis labels and number formats, horizontal axis scaling, and more.

Just as Risk Solver charts update instantly when you change numbers on the spreadsheet, they also update instantly when you change colors, titles, gridlines, legends and other options. Once you have the chart formatted just the way you want, you can use the toolbar buttons in the title bar of the dialog to "export" the chart.

Click the Clipboard icon to copy the currently displayed chart to the Windows Clipboard. You can then choose Edit Paste in Word, Excel, PowerPoint and many other applications to paste the chart image into your document. Click the Print icon to immediately print the currently displayed chart on your default printer, or click the down arrow next to this icon to display the menu choices shown above. You can choose a printer and set printer options, set page margins, and preview your output using these menu choices.Monte Carlo Simulation Tutorial - Viewing a Summary of the Model

In this tutorial, weve used Risk Solver to define two uncertain variables (our integer uniform distribution and our Unit Cost), one uncertain function (Net Profit), and one summary statistic (the Mean or True Average). In a larger model, we might define a great many uncertain variables and functions, statistics, and correlation matrices on a worksheet. They might even be spread across multiple worksheets in a workbook. How do we find them? Since all of Risk Solvers function start with Psi, we could use the Excel Find function to search for formulas containing this string. But Risk Solver provides an easier way.

Click the Model button on the Risk Solver Ribbon to display a summary of your entire risk analysis model in outline form, as shown below. You can expand and collapse outline groups by clicking the + or - icons that appear in this display. You can quickly locate cells containing uncertain variables, uncertain functions, statistics, or correlation matrices. You can even step through trials of the most recent simulation, displaying them on the spreadsheet.

When Interactive Simulation is on, moving the mouse pointer over one of the uncertain variable or uncertain function cell addresses will display a miniature PDF or frequency chart for that variable or function -- giving you an instant summary of the behavior of your model. A single-click on any of the cells displayed here will move the Excel selection to that cell. A double-click will open the Risk Solver dialog -- Uncertain Variable, Uncertain Function, or Correlation -- that you use to work with that cell.

This concludes our Monte Carlo Simulation Tutorial -- we hope you've enjoyed it! If you haven't done so already, consult our Risk Analysis Tutorial -- it's designed to sharpen your thinking about uncertainty and risk, and how to identify and quantify the uncertainties you face. For background on simulation analysis and simulation models, consult our Simulation Introduction.

Simulation - Introduction

Simulation is a flexible methodology we can use to analyze the behavior of a present or proposed business activity, new product, manufacturing line or plant expansion, and so on (analysts call this the 'system' under study). By performing simulations and analyzing the results, we can gain an understanding of how a present system operates, and what would happen if we changed it -- or we can estimate how a proposed new system would behave. Often -- but not always -- a simulation deals with uncertainty, in the system itself, or in the world around it.

Simulation Applications

Simulation Models

Simulation Methods

Monte Carlo Simulation

Simulation Applications

Simulation is one of the most widely used quantitative methods -- because it is so flexible and can yield so many useful results. Here's just a sample of the applications where simulation is used:

Choosing drilling projects for oil and natural gas

Evaluating environmental impacts of a new highway or industrial plant

Setting stock levels to meet fluctuating demand at retail stores

Forecasting sales and production requirements for a new drug

Planning aircraft sorties and ship movements in the military

Planning for retirement, given expenses and investment performance

Deciding on reservations and overbooking policies for an airline

Selecting projects with uncertain payoffs in capital budgeting

Simulation Models

In a simulation, we perform experiments on a model of the real system, rather than the real system itself. We do this because it is faster, cheaper, or safer to perform experiments on the model. While simulations can be performed using physical models -- such as a scale model of an airplane -- our focus here is on simulations carried out on a computer. Such simulations use a mathematical model of the real system. In such a model we use variables to represent key numerical measures of the inputs and outputs of the system, and we use formulas, programming statements, or other means to express mathematical relationships between the inputs and outputs. Each experiment is called a trial, and a simulation run includes many -- often thousands of -- such trials.

When the simulation deals with uncertainty, the model will include uncertain variables -- whose values are not under our control -- as well as decision variables or parameters that we can control. Our simulation model -- often called a risk model -- will calculate the impact of the uncertain variables and the decisions we make on outcomes that we care about, such as profit and loss, investment returns, environmental consequences, and the like. As part of our model design, we must choose how numerical values for the uncertain variables will be sampled on each trial. To learn more, consult Simulation Models.

Simulation Methods

Complex manufacturing and logistics systems often call for discrete event simulation, where there are "flows" of materials or parts, people, etc. through the system, and many steps or stages with complex interrelationships. Special simulation modeling languages are often used for these applications.

But a great many situations -- including almost all of the examples above -- have been successfully handled with simulation models created in a spreadsheet using Microsoft Excel. This minimizes the learning curve, since you can apply your spreadsheet skills to create the model. Simple steps or stages, such as inventory levels in different periods, are easy to represent in columns of a spreadsheet model. You can solve a wide range of problems with Monte Carlo simulation of models created in Excel, or in a programming language such as Visual Basic, C++ or C#.

Running a simulation generates a great deal of statistical data, that must be analyzed with appropriate tools. Professional simulation software, such as Frontline Systems' Risk Solver, allows you to easily create charts and graphs, a wide range of statistics and risk measures, perform sensitivity analysis and parameterized simulations, and use advanced methods for simulation optimization. To learn more, consult Simulation Analysis.

Monte Carlo Simulation

Monte Carlo simulation -- named after the city in Monaco famed for its casinos and games of chance -- is a powerful method for studying the behavior of a system, as expressed in a mathematical model on a computer. As the name implies, Monte Carlo methods rely on random sampling of values for uncertain variables, that are "plugged into" the simulation model and used to calculate outcomes of interest. With the aid of software, we can obtain statistics and view charts and graphs of the results. To learn more, consult our Monte Carlo simulation tutorial.

Monte Carlo simulation is especially helpful when there are several different sources of uncertainty that interact to produce an outcome. For example, if we're dealing with uncertain market demand, competitors' pricing, and variable production and raw materials costs at the same time, it can be very difficult to estimate the impacts of these factors -- in combination -- on Net Profit. Monte Carlo simulation can quickly analyze thousands of 'what-if' scenarios, often yielding surprising insights into what can go right, what can go wrong, and what we can do about it.Simulation Model - Introduction

A simulation model is a mathematical model that calculates the impact of uncertain inputs and decisions we make on outcomes that we care about, such as profit and loss, investment returns, environmental consequences, and the like. Such a model can be created by writing code in a programming language, statements in a simulation modeling language, or formulas in a Microsoft Excel spreadsheet. Regardless of how it is expressed, a simulation model will include:

Model inputs that are uncertain numbers -- we'll call these uncertain variables

Intermediate calculations as required

Model outputs that depend on the inputs -- we'll call these uncertain functions

It's essential to realize that model outputs that depend on uncertain inputs are uncertain themselves -- hence we talk about uncertain variables and uncertain functions. When we perform a simulation with this model, we will test many different numeric values for the uncertain variables, and we'll obtain many different numeric values for the uncertain functions. We'll use statistics to analyze and summarize all the values for the uncertain functions (and, if we wish, the uncertain variables).

Creating Models in Excel or Custom Programs

Choosing Samples for Uncertain Variables

Creating Models in Excel or Custom Programs

An Excel spreadsheet can be a simple, yet powerful tool for creating your model -- especially when paired with Monte Carlo simulation software such as Risk Solver. If your model is written in a programming language, Monte Carlo simulation toolkits like the one in Frontline's Solver Platform SDK provide powerful aids.

An example model in Excel might look like this, where cell B6 contains a formula =PsiTriangular(E9,G9,F9) to sample values for the uncertain variable Unit Cost, and cell B10 contains a formula =PsiMean(B9) to obtain the mean value of Net Profit across all trials of the simulation.

A portion of an example model in the C# programming language might look like this, where the array Var[] receives sample values for the two uncertain variables X and Y, and the uncertain function values are computed and assigned to the Problem's FcnUncertain object Value property:

Choosing Samples for Uncertain Variables

We must also choose what random sample values to use for the uncertain variables. During a simulation, a new sample value will be drawn on each trial. In the simplest case, we might generate random numbers between 0 and 1, and use these as sample values. But in most cases, the range of values, and chance that different values in the range will be drawn on each trial, must be tailored to the uncertain variable. To do this, we normally choose a probability distribution and appropriate parameters for the uncertain variable.

This is a key step in building a simulation model. To learn more about it, consult Probability Distributions for Simulation. We can choose:

An analytic distribution, such as a Uniform or Normal distribution

A custom distribution, where we specify its form in detail

A distribution fitted to past data on the behavior of the variable

A smaller data set that is resampled for values on each simulation trial

A larger data set, called a Stochastic Library, that supplies all of the samples

A Certified Distribution that has been prepared and "vetted" by an expert

Frontline's Risk Solver supports all of these options for obtaining sample values for the uncertain variables in a simulation model.

Probability Distributions for Simulation

For experienced modelers, the most challenging task in creating a simulation model is usually not identifying the key inputs and outputs, but selecting an appropriate probability distribution and parameters to model the uncertainty of each input variable. For example, Risk Solver software provides over 40 analytic probability distributions -- which one should you use? The answer depends on your application, but some general guidelines can be given.

Discrete Vs. Continuous Distributions

Bounded Vs. Unbounded Distributions

Analytic Vs. Custom Distributions

More Hints and Warnings

Choosing a Distribution

Discrete Vs. Continuous Distributions

If you must choose or create your own distribution, the first step is to determine whether to use a discrete or continuous form.

If there are a small number of possible values for the uncertain variable, you may be able to use a discrete analytic distribution, or construct a discrete custom distribution. If the underlying physical process involves discrete, countable entities -- such as the number of customers arriving at a service window -- you can use a discrete distribution.

If the possible values are highly divisible -- such as most prices, volumes, interest rates, exchange rates, weights, distances, etc. -- you will likely use a continuous distribution. In some cases, you may use a continuous distribution to approximate a discrete distribution.

Bounded Vs. Unbounded Distributions

Another characteristic that distinguishes probability distributions is the range of sample values they can generate.

Some distributions are intrinsically bounded -- samples are guaranteed to lie between a known minimum and maximum value. Examples are the Uniform, Triangular, Beta, and Binomial distributions.

Other analytic distributions are unbounded -- sample values may cluster around the distributions mean, but may sometimes have extreme negative or positive values. Examples are the Normal, Logistic, and Extreme Value distributions.

Still other distributions are partially bounded, with a known minimum such as zero, but no maximum value. Examples are the Exponential, Poisson, and Weibull distributions.

At times, you may find that the most appropriate distribution (say the Normal) is unbounded, but you know that the realistic values of the physical process are bounded, or your model is designed to handle values only up to some realistic limit. Your software may allow you to truncate an unbounded distribution. For example, in Risk Solver you can impose bounds on any distribution by passing the PsiTruncate property function as an argument to the distribution function.

Analytic Vs. Custom Distributions

A third characteristic of probability distributions is whether they are analytic (also called parametric) or custom (sometimes called non-parametric) distributions.

An analytic distribution has a form derived from certain theoretical assumptions about the problem. For example, a Poisson distribution is derived from an assumption that events are independent and occur at a known average rate, and an Exponential distribution is derived from an assumption of a constant rate of decay in some process.

A custom distribution has a form dictated by either past data or expert opinion about the range and frequency of sample values. Risk Solver software provides five general-purpose functions -- PsiCumul, PsiDiscrete, PsiDisUniform, PsiGeneral and PsiHistogram -- to help you model custom distributions.

Generally speaking, you should choose an analytic distribution if -- and only if -- the theoretical assumptions truly apply in your situation.

More Hints and Warnings

Using a Triangular Distribution. If you have only estimates of the minimum, maximum, and most likely values of an uncertain variable -- and no other past data or literature references -- a popular approach is to create a Triangular distribution from these three numbers. This is unlikely to be a highly accurate representation of the uncertainty, but it will allow you to get started, and it is far better than a single average that is subject to the Flaw of Averages. If your minimum and maximum values are really low- and high-percentile estimates rather than the absolute lowest and highest values that can occur, consider using a 'generalized Triangular' distribution (PsiTriangGen in Risk Solver) instead.

Define Each Uncertain Variable Only Once. Often, youll need to use the same uncertain variable in several different formulas in your model. A very common error is to enter the same distribution function, with the same parameters (say PsiNormal(100, 10) in Risk Solver), several times in a model -- in a belief that these instances will yield the same results on each trial. This is incorrect -- by doing this, youve actually defined several independent uncertain variables that may well sample different values on each trial. You should instead define =PsiNormal(100, 10) only once (for example in a cell such as A1), and use A1 in every formula where the uncertain variable is needed.Choosing a Probability Distribution

For experienced modelers, the most challenging task in creating a simulation model is usually not identifying the key inputs and outputs, but selecting an appropriate probability distribution and parameters to model the uncertainty of each input variable. For example, Risk Solver software provides over 40 analytic probability distributions -- which one should you use?

When Past Data is Available

When Past Data is Not Available

When Past Data is Available

If you have, or you can collect data on the past performance of the uncertain variable -- and if you believe that past performance is likely to be representative of future performance -- you have three options:

If you have a reasonably large number of observations of past performance of the variable, compared to the number of simulation trials you want to run, you can use the past data itself for simulation trials. This is sometimes called a trace-driven simulation. In Risk Solver, you can use past data in the form of a SIP (Stochastic Information Packet), and use the PsiSip() or PsiSlurp() distribution function for the uncertain variable.

If -- as is often the case -- you have a relatively small number of observations of past performance compared to the number of trials you want to run, you may be better off resampling the past performance data. Instead of using all of the past observations (one per simulation trial), you randomly sample the past observations on each trial. In Risk Solver, you can place past data in a cell range or SIP, and use the PsiDisUniform() function (single values) or the PsiResample() function (multiple values) for the uncertain variable.

If you can fit the data (past observations) to a specific type of analytic distribution and its parameters, and if there is reason to believe that the underlying process that the uncertain variable is measuring is consistent with the assumptions from which the analytic distribution is derived, you can use this distribution (for example PsiNormal, PsiWeibull, etc.) for the variable. You can use Risk Solver to automatically find the best-fitting analytic distribution.

When Past Data is Not Available

If you dont have, and you cannot easily collect data on the past performance of the uncertain variable -- or if past performance is not likely to be representative of future performance -- you must tackle the problem in a different way:

Consult the literature for your industry, if available, to find examples of applications like yours where simulation models were built. Find out -- by contacting the authors if necessary -- what kinds of distributions were used for the uncertain variables, and the rationale for choosing them.

If you cannot find reports on industry-specific applications like yours, consult the publications of professional societies like INFORMS, where simulation applications are reported. One rich source is the past proceedings of the Winter Simulation Conference (www.wintersim.org).

The Risk Solver User Guide has a chapter PSI Function Reference, with descriptions of 40 different analytic and custom distribution functions, including brief comments on the types of applications where each distribution has been used in the past.

To learn more about analytic distributions, consult textbooks such as Simulation Modeling and Analysis, 4th Ed. by Averill Law, Statistical Distributions, 3rd Ed. by Evans, Hastings and Peacock, Univariate Discrete Distributions, 3rd Ed. by Johnson, Kemp and Kotz, or Continuous Univariate Distributions, Vol. 1 & 2, 2nd Ed. by Johnson, Kotz and Balakrishnan.

You are well-advised to keep it simple! Many physical, social and biological phenomena are well described by the Normal distribution, or -- if the possible values are equally likely to occur, as in a coin flip or single die -- the Uniform distribution. Bear in mind that when any set of distributions are summed, the result (quickly) tends towards the Normal distribution.

Applications that involve queuing -- customers arriving or departing, parts awaiting assembly, etc. -- have been well studied, so you can often find appropriate distributions in the literature. Applications that use the Project Evaluation and Review Technique (PERT) can often use the PsiPert() function is Risk Solver to model uncertainty.

Simulation Analysis

In simulation analysis, we create a mathematical model or a system or process, usually on a computer, and we explore the behavior of the model by running a simulation. A simulation consists of many -- often thousands of -- trials. Each trial is an experiment where we supply numerical values for input variables, evaluate the model to compute numerical values for outcomes of interest, and collect these values for later analysis.

Charts and Graphs

Statistical Measures

Sensitivity Analysis

Parameterized Simulation

Next: Simulation Optimization

Charts and Graphs

A simulation yields many possible values for the outcomes we care about -- from Net Profit to environmental impact. The role of simulation analysis is to summarize and analyze the results, in a way that will yield maximum insight and help with decision-making. It is very useful to create charts to help us visualize the results -- such as frequency histogram charts and cumulative frequency charts.

Statistical Measures

Statistics often play a key role in summarizing the range of values for each outcome of interest in a simulation analysis. When the outcome is important to us, statistics come to life! A good simulation software package, such as Frontline's Risk Solver, provides a variety of statistics:

Measures of central tendency such as the mean, median and mode

Measures of variation such as the variance or standard deviation, skewness, and kurtosis

Risk measures such as mean absolute deviation, semivariance or lower partial moment, and semideviation

Quantile measures such as percentiles, cumulative targets, Value at Risk, and Conditional Value at Risk

Confidence intervals that tell us how close our computed sample mean or standard deviation is to the true value

It's important to look at quantile measures, such as percentiles and Value at Risk, in addition to measures of central tendency and variation. Quantile measures help you answer questions such as "How much money might we lose, with 5% or 10% probability? or What are the chances that well make at least $100,000? based on your simulation model.

A simulation uses a sample of the possible values of your uncertain variables; hence any statistic resulting from the simulation involves some degree of sampling error. Confidence intervals help you assess this error, and estimate the range or interval in which you can be confident that the true statistic lies, at a confidence level that you specify.

Sensitivity Analysis

A powerful tool for assessing model results is sensitivity analysis, which can help us identify the uncertain inputs with the biggest impact on our key outcomes. For example, a tornado chart can give us a quick visual summary of the uncertainties with the greatest positive and negative impact on Net Profit. Using software, we can also run multiple simulations, with an input we choose taking a different value on each simulation, and assess the results. Analyzing the model can give us more information, but also insight about our real-world problem.

Parameterized Simulation

Another powerful method for simulation analysis is running a parameterized simulation. In this method, we run a series of simulations, where we vary the value of one or more variable(s) that we can control -- such as our offering price, our inventory restocking level, or our allocation of investment funds to different asset classes. Each simulation run tests a wide range of values for the uncertain variables in our model, collects results for our outcomes of interest, and produces summary statistics, charts and graphs. We can then compare the different simulations to each other, to better understand how varying the decision variable(s) affects our outcomes, in the presence of uncertainty.

Simulation Optimization

An even more powerful method for simulation analysis, beyond parameterized simulation, is to use simulation optimization to automatically find the best value of one or more variables that we can control. We can put the computer to work, in effect performing parameterized simulations for many different combinations of values for our decision variables, and seeking the best combination of values for criteria that we specify.

Frontline's Premium Solver or Premium Solver Platform -- the leading optimization software for Microsoft Excel models -- works closely in concert with Risk Solver to find solutions to simulation optimization problems -- at speeds up to 100 times faster than other software!

How Simulation Optimization Works

Defining a Simulation Optimization Model

Solving a Simulation Optimization Model

How Simulation Optimization Works

The overall process works like this: Your simulation model includes uncertain variables whose values are defined by probability distributions. You can use the cells containing these uncertain variables in any formula in your model. Risk Solver performs a simulation with thousands of trials, where a different value is sampled for each uncertain variable on each trial, and your model is recalculated with these values.

Statistics across all the trials are accumulated for any formula cell you designate. You can access these statistics in regular Excel formulas. And with Premium Solver or Premium Solver Platform, you can use these formulas in the objective and constraints of your optimization model. For example, you could make average delivery time (computed by your model, based on decisions and uncertainties) an objective to be minimized, subject to a constraint that the cost of inventory should, with 95% probability, be less than a dollar threshold you specify.

Defining a Simulation Optimization Model

Creating a simulation optimization model using Premium Solver and Risk Solver is straightforward. You follow these steps:

1. Define decision variable cells (such as A1), using either the Solver Parameters dialog or the PsiVar() function. These are factors that are under your control you (or the Solver) will decide what values they should have.

2. Define uncertain variable cells (such as A2), that contain formulas calling the PSI Distribution functions supplied by Risk Solver for example PsiUniform() and PsiNormal(). These are factors that are not under your control.

3. Build your model, using cell formulas that may depend on the decision variables, uncertain variables, or both.

4. Each cell (such as B1) containing a formula that depends on uncertain variables (say =A1+2*A2) represents thousands of trial values, generated during each Monte Carlo simulation by sampling different values for A2 and computing =A1+2*A2.

5. In other cells (such as C1), define the summary statistics you want, using functions such as PsiMean(B1) or PsiStdDev(B1). You may use formulas to compute further values based on these summary statistics.

6. Define your objective and constraints for optimization. These may be cell formulas that depend only on the decision variables, depend on the uncertain variables through PSI Statistics functions, or depend on both.

Solving a Simulation Optimization Model

Solving a simulation optimization model using Premium Solver and Risk Solver is also straightforward. Follow these steps:

1. Activate Interactive Simulation by clicking the light bulb button on the Risk Solver Ribbon or toolbar.

2. Select Tools Premium Solver to display the Solver Parameters dialog, and click the Solve button.

Simulation optimization is a powerful, general framework for finding "best solutions," but it is computationally very expensive, and there are limits on the size and complexity of your model (number of decision variables, uncertain variables, constraints, and total formulas) if you want to find a solution in a reasonable amount of time. Frontline Systems is already delivering the highest performance software for simulation optimization in Excel available today -- but we're taking this capability much further in the future! Contact us if you'd like to learn more.