Transcript Effective Use of @Risk for Building and Constructing Risk

Effective Use of @Risk for Building
and Constructing Risk Models
JIFSAN, University of Maryland
Univ. Of Maryland & US FDA
Joint Institute for Food safety and Applied Nutrition
1
Learning Objectives
• At the end of this session you will:
– learn how to build and construct models using @Risk
– use software features and avoid pitfalls
– learn how to analyze models and present results
– learn how to make user friendly models
– do some exercises
2
Running a Simple Risk Analysis
• Topics to discuss:
– @Risk functions
– Defining the outputs
– Listing the inputs and the outputs
3
@Risk…
• Is an add-in for Microsoft Excel that performs risk analysis on
any spreadsheet model by using Monte Carlo simulation
• When you launch @Risk, Microsoft Excel is opened
automatically
• @Risk makes a new toolbar in Excel
• The toolbar lets you run many commands that you will need to
run a risk analysis
@Risk Menu…
Have access to @Risk functions and results
windows through @Risk menu
Click on @Risk Menu
Newer versions of Excel may appear different and may have
additional options.
Let’s Look at an Available Example…
• Open the example file finance.xls (you can find it on the same
webpage with the link to this presentation
• This model calculates net cash flow and net present value for a
new product
• Although this example has financial nature, @Risk can be used
with any spreadsheet model you create
• We will build a few microbial risk assessment models later
Finance.xls Model…
Finance.xls Model…(continued)
• For each year, net income is affected by number of factors including:
– Number of competitors (C25:L25)
– Sales price (C31:L31)
– Sales volume (C32:L32)
– Capital Expenses (C36:L36)
– Product development (C35:L35)
– Overhead (C37:L37)
• Net income is calculated for each year (C22:L22)
• Net present value is calculated at 10% discount rate for the 10 year period (C10)
Let’s Take a Deeper Look at the Model…
• Let’s take a look at the content of Cell E32
[E32]=RiskNormal(3000,1000)/(E25+1)
• RiskNormal is an @Risk probability distribution function for a normal distribution
• @Risk has more than 3 dozen probability distribution functions
• If a cell has an @Risk function, we refer to it as an input variable. Thus, E32 is an input
variable!
• Each probability function represents a range of values along with their corresponding
probabilities that can occur
Let’s Take a Deeper Look at the Model…(continued)
• @Risk probability functions can be either directly written into a
cell or we can use the Define Distribution button on the @Risk
toolbar
Click the Define
Distribution button
when you selected
Cell E32.
What do you see?
Define Distribution Button
@Risk function for
the distribution
Distribution type, e.g.,
Normal
Mean value, e.g., 3000
Standard deviation, e.g.,
1000
You can shift the mean
value or trim the left
and/or right tails of the
distribution
Selected statistics of the distribution,
e.g., mean, median, mode, etc.
Define Distribution Button (continued)
• During the simulation, @Risk randomly samples values for Cell
E32 across the full range of the normal curve
• For each iteration, a new randomly generated value will be
placed in Cell E32
• Why the current value of Cell E32 is 3000?
• When the simulation is not running, @Risk returns the mean
of the distribution
Other Types of Distributions…
• Let’s look at Cell F25. What is the distribution function?
[F25]=RiskDiscrete({0,1,2},{1,1,1})
• This distribution is discrete, as opposed to being continuous
(previous example)
• The distribution holds only three values (i.e., 0,1, and 2) with
equal probabilities
Now Let’s Define a Distribution Ourselves…
• Select Cell E26 and click the button for defining a distribution
• There are two ways to select a distribution
– Using the drop-down list
– Using the distribution palette
Distribution palette
Drop-down list
Now Let’s Define a Distribution Ourselves…(continued)
• Let’s select the triangular distribution
@Risk function for triangular
distribution:
RiskTriang(min, most likely, max)
Minimum value
Most likely value
Maximum value
• Put 15 as the minimum value and truncate the function at 20
[E26]=RiskTriang(15,23.25,26,RiskTruncate(20,))
Now Let’s Define a Distribution Ourselves…(continued)
• Assign the distribution to the selected
variable by clicking on the Apply or OK button
Now the Output…
• In @Risk, the output is the value that we are interested in
studying, i.e., a bottom line value
• In this model, we are interested in net present value which is
calculated in Cell C10
• To add Cell C10 as an output, first select Cell C10 and then click
the Add Output button on the @Risk
toolbar
Add Output button
Now the Output…(continued)
• You will be given a dialogue to select a name for the output
• @Risk selects a name for the cell based on the neighboring
cells (e.g., NPV(10%))
Now the Output…(continued)
• We are also interested in Net Income as an output which is
calculated in Cells C22 to L22
• Highlight the whole row, then click on the Add Output button
• Select a name for the output (e.g., Net Income)
• Now we have defined everything!
• You can click on Show @Risk-Model Window or Model Window
button to check the list of inputs and outputs you have entered
into your model
Take Away Points
• We learned about @Risk functions
• We learned how to define the outputs
• We learned how to list the inputs and the outputs
Running a Simple Risk Analysis
• Topics to discuss:
– Simulation Settings
– Running a simulation
– The @Risk’s results window
– Sensitivity and scenario analysis in @Risk
– Generating reports in Excel
Simulation Setting Dialogue
• There are many aspects to a successful simulation
– Robust model
– Robust distribution functions that accurately
define variability and uncertainty in inputs
• Mechanical aspects
– How @Risk samples data from the
distributions
– How long we should simulate
• @Risk allows you to control the mechanical
aspects through Simulation Settings Dialogue
Click on the Iterations Button
• # Iterations: Number of iterations during a simulation
(more complex models may require larger number of
iterations)
•
There are options for
–
–
–
–
Updating the display
Pause on error in outputs
Using multiple CPUs
Minimizing @Risk and Excel when simulation starts
– In newer versions the options are directly embedded in the toolbar
– Settings in the “Simulation” toolbar box
Click on the Sampling Button
• Sampling type
– Latin Hypercube (for faster convergence)
– Monte Carlo
• Select the default value, i.e., Latin Hypercube
WARNING
• The following slides are for an older version of @RISK. They have not
been updated or adapted for the New Release of both Excel and @RISK.
If you have questions or are confused because of the layout we highly
recommend you view the following links:
• @RISK Quick Start - http://www.palisade.com/QuickStart/EN/RISK/
• @RISK Guided Tour - http://www.palisade.com/GuidedTour/EN/RISK/
25
We are Now Ready to Start the Simulation
• Click the Start Simulation button to start the analysis
• During the simulation @Risk recalculates the spreadsheet
using random values sampled from the input distribution
functions and record the output values
• When the simulation is completed, the results are shown
in the @Risk’s results window
• In the results window, we can interactively analyze the
statistics and graph the simulation results
Simulation Results
• @Risk provides a number of different methods for analyzing
and working with data
– Summary statistics window
– Detailed statistics window
– Data window
– Sensitivity window
– Scenario window
Summary Statistics Window
Summary Statistics Window (continued)
• Summary statistics window displays a summary of the
simulation results including minimum, mean, and
maximum values calculated for each output cell and
input distribution
• For example in our simulation, NVP has a minimum
value of -66813.52, mean value of 447635.7, and
maximum value of 1257205 (note the difference in
your numbers! What is the reason?)
• Is this a very risky venture due to wide range of
results?
• What is the risk of loosing money? You can answer
this question by adding a target value under x1
Detailed Statistics Window
Detailed Statistics Window (continued)
• The detailed statistics toolbar provides statistics for each of the
output cells and input distribution
• Additional statistics include variance, skewness, and
percentiles
Data Window
• The data command displays all the data generated and
collected during each simulation
Sensitivity Command
• Often you want to know
– how the input variables affected the output values
– Which inputs are the most critical ones
• The sensitivity command provides answers to the above
questions
• @Risk uses two techniques for sensitivity analysis
• Multivariate Stepwise regression analysis
• Spearman (rank) correlation analysis
Sensitivity Command (continued)
Output of interest
Method of sensitivity analysis
Sensitivity Command (continued)
• Click on the sensitivity command button
• You will see a list, ranking of inputs based on their effect on the
selected output
• For example, for NVP as the output of interest the number of
competitors is by far the most important input
• For either of two sensitivity methods used by @Risk, a value of
zero indicates the changes in the input variable have no effect
on the output
Sensitivity Command (continued)
• The greater the magnitude of value, the bigger the impact
• A positive value indicates as the input increases so does the
output
• A negative value indicates as the input increases the output
decreases and vice versa
Scenario Analysis
Scenario Analysis (continued)
• Scenario analysis allows you to determine which
input values contributes significantly toward reaching
a goal
• For example, which variables contribute to
exceptionally high or low NVP values?
– When NVP>90%, i.e., NVP is at or above its 90th
percentile value, sales volume and number of
competitors are the most important variables
– For exceptionally low NVPs, i.e., NVP<25%, the
number of competitors is the most important
input
Reporting Results Directly to Microsoft Excel
• @Risk can also generate
results and report
directly to Microsoft
Excel
• This gives you access to
all Excel’s capabilities for
formatting graphs and
reports
• For this purpose, you
should click on the
Report Settings button on
Reporting Results Directly to Microsoft Excel
(continued)
• Report Settings contains a list of all the
different types of Excel reports @Risk can
create
• First, select Generate Excel Reports
• Then, choose any or all the optional reports
• @Risk can also use pre-built Excel template
files that includes costume formatting,
titles, and logos
• Quick summaries and quick report can also
be generated
Take Away Points
•
•
•
•
•
We learned how to specify the simulation settings
We learned how to run a simulation
We looked into the @Risk’s results window
We practiced sensitivity and scenario analysis in @Risk
We learned how to generate reports in Excel
Graphing the Results
• Topics to discuss:
– Histograms and cumulative graphs
– Real time updating graphs
– Summary graphs
– Overlay graphs
– Tornado graphs
– Graphing in Excel
Graphs in @Risk
• One of the strength of Monte Carlo
simulation is that you generate enough
data to present your results using graphs
• @Risk has a number of presentation quality
graphs
• Graphs typically provide a very good
description of an output
• Using graphs in @Risk is very easy:
– select an input or an output of interest and
right click on it to bring the menu with
Let’s Create Graph for the NVP
Let’s Create a Histogram for NVP
Let’s Create a Histogram for NVP (continued)
• Histogram shows the
distribution of possible
values for the selected
output
• The height of each bar
represents the number of
samples fell in the range
defined by the width of each
bar
• In short, the histogram is the
graphic representation of the
uncertainty inherent in the
selected variable
Question: What is the probability of
having NVP between 1 million and
500,000$? Approx. 34%
Let’s Create a Histogram for NVP (continued)
• You can format the graph by right clicking on the
graph to bring up the options menu
• You can change
– The type
– The format
– Scaling
– Colors
– Titles
– etc
Real Time Results
• @Risk can generate graphs during the simulation
• Such graphs will be updated in a real time as the simulation
runs
• This can be done using real time toolbar
Real time toolbar
Summary Graphs
• What about the output range we
selected in our model, i.e., net
income?
• One advantage of selecting a range
as the model output is that we can
study the changes of the whole
group at once
• Right click on net income and select
the Summary Graph
• The Summary Graph summarizes
the distributions generated for the
cells selected in the output range
Summary Graphs (continued)
Outer green band
extends from 5th to the
95th percentiles
Red band represents
one standard
deviation above and
below the mean
Distribution of mean
over 10 years
You can change these values by right clicking on the graph, selecting
format, and changing values in the Type dialogue
Summary Graphs (continued)
• The summary graphs are very useful to look at how values are
changed across an output range
• In this example, the graph shows a negative income for the first
few years of the project with and expected positive cash flow
in the subsequent years
• The trend widened at year 6 indicating this year has the largest
risks
Overlay Graphs
• There may be times when you want compare two or
more variables on one graphs
• @Risk allows you to create overlay graphs easily
• For this purpose:
– First, you can make a graph of the first variable
you want to compare
– Second, right click on the second variable in
the inputs/outputs explore list and select
Overlay an Active Graph
• Overlays are specially useful when comparing output
distributions in cumulative format
Overlay Graphs (continued)
Tornado Graphs
• One of the easiest ways to understand the results of
sensitivity analysis is to use tornado graphs
• To create a tornado graph:
• Right click on an output you are interested in and
select Tornado Graph
• The most significant input is drawn on the top of the
tornado graph
• The X-axis represents the percent change in the
output value
• Each input variable is placed on the Y-axis
• Positive and negative impacts are shown on the right
and left sides, respectively
Tornado Graphs (continued)
Graphing Excel
• There maybe times when you want to bring your data to Excel
or a different program for graphing
• @Risk makes it easy to convert any graph to a native chart
format in Excel
• For this purpose right click on any graph in @Risk and select
Graph in Excel
• @Risk not only transfer the graph to an Excel worksheet, but
also transfer the source data
Take Away Points
• We learned how to draw histograms and cumulative graphs
•
•
•
•
•
We looked into real time updating graphs
We made summary graphs
We learned how to overlay graphs
We built tornado graphs
We transferred the graphs generated in @Risk to an Excel
worksheet
Advanced Features
• Topics to discuss:
– Fitting distributions to data
– Special Risk functions
– Correlating inputs
– Multiple simulations
Fitting Distributions to Data
• There maybe times when you have a set of collective data
which you want to use as the basis for an input distribution
• @Risk allows you to fit probability distributions to existing data
• You can use this feature by:
– Click on Fit Distributions to Data in @Risk toolbar
– Highlighting data, right clicking and selecting Fit Distributions to Data
from @Risk menu
Fitting Distributions to Data (continued)
Data type: continuous versus discrete
Filtering data
Fitting Distributions to Data (continued)
Alternative distributions fitted to data
and ranked based on goodness-of-fit
Selected dist. and
corresponding
parameters
Alternative goodnessof-fit tests available for
ranking: Chi-sq, A-D,
K-s
Results from
goodness-of-fit
Fitting Distributions to Data (continued)
• You can specify a distribution for fitting by clicking on Specify a
Distribution to Fit icon
• When analyzing the fit, you can look at four different graphs
– A comparison graph
– A difference graph
– P-P plot
– Q-Q plot
• Goodness-of-fit information based on different tests is also
available
Special Risk Functions
• @Risk includes a set of statistics functions that can be entered
directly into a spreadsheet model
• These functions include desired statistics that are updated in a
real time during a simulation
• This feature gives you the ability to build advance models
Special Risk Functions (continued)
• Suppose you want to include the mean of the sale volume in
Cell D32 as a part of your model
• You can simply enter “=RiskMean(E32)” in Cell D32
• This cell will simply include the mean of the equation in the
selected cell and will update as the simulation runs
• @Risk statistics functions include all the standard statistics plus
percentiles
Correlating Inputs
• In most real life situations variables are not independent
• In such cases, the value of one variable affects the value of
another one
• For example, when the temperature is high, air condition usage
is high
• When modeling you need to take these relationships into
account otherwise you will model scenarios that are do not
make sense in reality
Correlating Inputs (continued)
• @Risk allows you to take the relationships between inputs into
account by correlating input variables
• Open file “Corrmat.xls” from your example directory
• You have a model with 3 inputs in Cells D17 to D19
• We want to correlate these input variables according to
correlation coefficients shown in the matrix between Cells B12
and D14
Correlating Inputs (continued)
• The @Risk allows you to quickly create a correlation
matrix in defined values
– First, you select an input for which you want to
define a correlation value
– Click on the Define Correlation icon
– This gives us a blank correlation matrix to work
with
– You can enter correlation coefficients between
-1 and +1 into this matrix
– When adding a correlation coefficient, you can
see the relationship between the two inputs in
the provided scatter plot window
Correlating Inputs (continued)
Scatter
plot
window
Selected
inputs
Correlation
coefficient
matrix
Correlating Inputs (continued)
• Add the following correlation into the matrix
– IntRate & Pound/$: -0.7
– IntRate & Mark/$: -0.5
– Pound/$ & Mark/$: 0.6
• Check how scatter plots change when adding correlation
coefficients
• Click on Apply to assign the value
• Remember correlation matrix is a symmetric matrix. Thus, you
only need to enter half the values
Multiple Simulations or Sensitivity Simulations
• Some times you want to measure the
impact of particular value on the simulation
• For such cases you want to run a simulation
for each possible value you want to try
• In @Risk we call this as sensitivity
simulation
• We can use function
RiskSimtable({v1,v2,…,vn}) where vi is the ith
value we want to check in a separate
simulation
Multiple Simulations or Sensitivity Simulations
(continued)
• Open file “SenSim.xls”
• Use the RiskSimtable function for Cell D12 to evaluate
four values (e.g., 25, 50, 75, and 100) for price
• Specify the number of simulations (i.e., 4) in
Simulation Settings
• When starting the simulation, @Risk will run 4
simulations back-to-back using 4 different values for
the input in Cell D12
• @Risk will provide results for all four simulations
allowing you to compare results between different
values of the selected input
Multiple Simulations or Sensitivity Simulations
(continued)
• You can use overlay graphs or summary
graphs to compare results from different
simulations
Take Away Points
•
•
•
•
We learned how to fit a distribution to data
We looked into special Risk functions
We learned how to correlate inputs
We learned how to run multiple simulations
Enhanced Define Distribution Window
• A number of significant enhancements are made to the Define
Distribution window
– Distribution palette
– Easy definition of distribution cell references
– Easy access to overlay graphs for comparison with the primary
distribution
Enhanced Define Distribution Window (continued)
Definition of
distribution
cell references
Distribution
palette
Adding
overlay
Alternate Distribution Parameters and Quick Reports
• There may be instances where you wish to use
particular distribution but you don’t have enough
information to determine the distribution parameters
• For example, for experience you may know that the
Lognormal distribution best describes the initial
microbial contamination but you don’t have enough
information about the mean and standard deviation
• @Risk allows you to enter parameters as percentile
values giving you more modeling flexibility
Alternate Distribution Parameters and Quick Reports (continued)
• To do this:
– First click on the Define Distribution value
– Click on Alternate Parameters
– Click on Percentile to enter actual percentiles
you are using (e.g., 10th and 90th percentiles)
– Now you can enter values for selected
percentiles instead of actual mean and SD of
the distribution
• @Risk creates a distribution for you based
on the parameters you have entered
Alternate Distribution Parameters and Quick Reports (continued)
Alternate Distribution Parameters and Quick Reports (continued)
• You can have a one-step, pre-formatted
report that summarizes all critical risk
analysis results
• With a single click you can generate a onepage report containing a histogram,
cumulative curve, tornado diagram, and
summary statistics of simulation results
• In order to do so, after you are done with
the simulation, right click on any output
value in the @Risk results window and
select Quick Report
Alternate Distribution Parameters and Quick Reports (continued)
Histogram
Cumulative curve
Summary
statistics
Tornado graph
@Risk Goal Seek
• @Risk Goal Seek can find the value of an
input that needs to a desired simulation
result
• Unlike Excel goal seek function that is
deterministic, @Risk goal seek uses
multiple simulations to get results
• You can set the target value or the goal for
a simulation statistic and then tell @Risk
which input to adjust to achieve your goal
• @Risk uses results from multiple
@Risk Goal Seek (continued)
• As an example, open file “VarGoalSeek.xls”
• This example analyzes return on a portfolio investment
• You are interested in buying puts (Cell D29) to protect at least
some of the shares of your portfolio in order to reduce the risk
of loss
• You also want to make sure that the mean of your return is
23% (This is your goal!) (Cell C34)
@Risk Goal Seek (continued)
• Click the @Risk Goal Seek
Goal Seek button
button to invoke this feature
• Select the statistic you will be
setting your goal for (e.g.,
mean)
• Select the cell whose goal you
want to set (i.e., Cell C34)
• Enter the target value for the
simulating mean for this cell
 You can also set the goal
(e.g., 23%)
seek options such as
• Specify the cell to adjust (i.e.,
number of simulations
D29)
and accuracy level
Stress Analysis
• @Risk Stress Analysis allows you to analyze
the effect of stressing probability
distributions
• We may now control the values sample
from a distribution as you may stress a
portion of the distribution
• You can specify the percentile values
between which samples are drawn during a
simulation
• By specifying extreme ranges of a given
Stress Analysis (continued)
• Stress Analysis also allows you to stress
your model by substituting an entirely new
distribution for an existing distribution
• This way you can test various scenarios
without changing your model
Stress Analysis (continued)
• Open “ClamsStress.xls”
• Suppose that an insurance company is required by
law to have enough money on hand to pay all the
claims with the probability of 95% and that it can
only set aside $2000
• However, standard Monte Carlo simulations show
us the maximum payment is about $3500
• The company wants to see what the effect would be
on claims if it took out the insurance policy to cover
an extraordinary high number of claims?
Stress Analysis (continued)
• We would like to stress the distribution
representing the number of claims to
represent just the 90th percentile and
below
• This will allow us to see if the reserves on
hand are enough to cover 90% of possible
claims
• After completing the simulation, the stress
analysis provides you with a collection of
reports and graphs which you can use to
Stress Analysis (continued)
Stress Analysis Icon
Cell to monitor
Reference cell
for the input
distribution
Selecting the
range of an input
distribution to
stress
Advanced Sensitivity Analysis
• With advanced sensitivity analysis, inputs
can be any value or any @Risk distribution
function
• The advanced SA function lets you specify
any member of the distribution functions
or static worksheet cells to vary and let you
control how they vary
• @Risk then runs any number of
simulations, one on each possible input
value, and tracks how the output values
Advanced Sensitivity Analysis (continued)
• Open “HippoSensitivity.xls”
• We are simulating the profitability of the launch of a new drug
for hippos
• We want to know if any of the fixed inputs (listed in Cells
C16:C20 and Cells E16:E20) can be determined with greater
precision, which one we need to select
• Advanced SA can help us decide which input to focus on
Advanced Sensitivity Analysis (continued)
Advanced SA
icon
Output cell to
monitor
Range of fixed
input cells for
advanced SA
Max and min change
values around the fixed
initial value
Advanced Sensitivity Analysis (continued)
Specific statistic of
the output we want to
track
Options are:
variance, mean,
percentile, skewness,
etc.
Alternative
options to
include in the
report
Pause on Error
• After working with @Risk for a little bit, it will become very
easy to make complex models with nested cells and
distribution functions
• The logic of the complex models can be very difficult to track
specially given the variability of many probability distribution
functions
• As a result, this in-depth analysis can lead to violation of
conditions of some of the output cells
Pause on Error (continued)
• @Risk includes a Pause on Error feature
which pauses the simulation when an error
is recorded in an output cell
• For example
– Define a distribution in a cell as
[A1]=RiskNormal(2000,100)
– Define the output value in a cell as
[A2]=RiskTriang(0,2000,A1)
– Select Pause on Errors in Outputs in Simulation
Settings window
– Run the simulation
• What happened!?
Pause on Error (continued)
Name of the
output cell
with an error
Related input cell
that caused the
error
Value of the input cell that
caused the error