Risk Analysis

1
Risk Analysis

• Simulate a scenario of possible input values that
  could occur and observe key impacts
• Pick many input scenarios according to their
  likelihood of occurring
• Record and summarize the key impacts observed to
  measure risks

2
5 Steps of Risk Analysis

• Build a spreadsheet model that has dynamic
  relationships between input assumptions and key
  outputs
• Perform sensitivity analysis to identify the key
  inputs that have the most potential impact on the
  key outputs
• Quantify the possible values for the key
  uncertain inputs by specifying probability
  distributions
• Run a simulation to pick scenarios from the input
  probability distributions and record observed
  output results
• Summarize recorded output results to measure
  risks and likelihood of different outcomes

3
Random Number Generator (RNG)

• Rand() function in Excel
• Randomly generates a number between 0 and 1 (0
  and 100). Think of this number as the
  cumulative probability of seeing a value for the
  input assumption that would be smaller than the
  number associated with this probability.
• For example, a formula that uses Rand().5 would
  tell you the input assumption value where 50 of
  the input assumption values would be smaller than
  the number calculated. A formula with Rand().9
  would tell you the input assumption value where
  90 of the input assumption values would be
  smaller than the number calculated.

4
Normal (u, s)
Prob
P(Xu).5 P(u- s x u s).65
s
u- s u u s x
5
Normal Input Distributions

• Norminv(rand(), mean, standard deviation)
• For a specified mean and standard deviation, this
  formula will look up the value for the input
  distribution that will result in rand() of the
  assumption values being smaller than the returned
  value x.

6
Uniform (a, b)
Prob
P(X u). 5
a ua(b-a) b
X 2
7
Uniform Distribution

• a (b-a)rand()
• Where a is the smallest value that could occur, b
  is the largest value
• Values that could occur are assumed to be equally
  likely to occur between a and b
• Values are assumed to be continuous and not
  discrete

8
Discrete Distribution
P(x)

• X P(x)
• .2
• .3
• .4
• .1

10 11 12 13 x
9
Cumulative Probability Distribution
.2

• X P(x)
• .2
• .3
• .4
• .1

P(Xx) .2 .5 .9 1.0
11
10
1.0/0
.5
13
.9
12
10
Discrete Distributions

• Set up a table where the first column contains
  the cumulative probability for a value, the
  second column contains the discrete values that
  could occur.
• Use the Excel function
• vlookup(rand(),table,2)
• This function will look up the rand() number in
  column 1 of the table, identify the row that
  represents the correct cumulative probability,
  and look up the value associated with that
  probability in column 2.

11
Alternative Histogram Approach

• Set up the bins you would like in the first
  column. Make sure that the first bin value is
  lower than the minimum result and the last bin
  value is greater than the maximum result
• Format column 2 to display frequency results for
  the bins in column 1. Highlight the array of
  cells in column 2 that should hold the results.
• Use the array function
• frequency(column with simulation output data,
  column with bin values)
• Enter function by pressing CtrlShiftEnter
  instead of the Enter key