Introduction to Risk Analysis

1
Introduction to Risk Analysis

• Using Excel

2
Learning Objective
3
Time management
4
Methods

• (1) the analytical, mathematical approach and
• (2) the Monte Carlo simulation technique.

5
Warm-up

• The president of the small Pharmaceutical company
  must make the final decision about whether to
  market a new kind of cough drop. The yearly
  forecast for this venture is as follows

6
Warm-up

• Considering a five year product live and 20
  discount of the profit stream, analyze this
  venture using present value method and present
  some considerations about the decision based in
  your calcules.(10 minutes)

7
Present Value
8
Uncertainties
9
Additional Information

• Calculate the base-case, best-case and worst-case
  scenarios, through excel using these additional
  information (10 minutes)
• Total industry sales of cough drops will be
  between 10 million and 20 million.
• The company's market share will be between 20
  and 50.
• The price will be between 10 and 20 cents per
  ounce.
• The fixed cost of manufacturing will be between
  100,000 and 300,000.
• The variable cost rate of production will be
  between 5 and 10 cents.

10
Scenarios
11
Uncertainties

• There is a great deal of uncertainty in this
  venture. The board of directors is quite
  risk-averse, so they want to know
• How good is the base-case estimate?
• What is the variability in the profit function?
• What are the chances of making a profit?
• What are the chances of making a profit of
  500,000 or more?
• What is the probability of a loss?

12
What is Risk Analysis?

• Consider now that
• Marketing believes, based on past records,
  competitive products, and intuition, that total
  industry sales will be around 15 million.
• They believe it is very unlikely that sales will
  be less than 10 million or more than 20
  million, but they are unable to decide the
  likelihood of any particular sales figure within
  that range.
• Any value is equally likely. In other words,
  marketing feels that the probability distribution
  of total industry sales is a flat-line segment
  between 10 million and 20 million, as shown in
  the next slide.

13
Probability Distribution
Probability Distribution of total industry sales
14
The cumulative probability Distribution

• The probability that random variable is up to a
  certain value is represented by the area under
  the probability distribution.

Cumulative Probability
Cumulative Probability Distribution
1.0
0.5
15
Risk simulation process
16
Risk analysis

• Risk analysis calculates measures of uncertainty
  of the output variables, such as sales, profit,
  labor required, and so on. These measures include
  expected value, variance, standard deviation,
  median, mode, the complete output probability
  distribution, and the cumulative probability
  distribution.

17
Risk analysis scenarios

• The scenarios include not just single estimates
  of the variables but also the calculated
  probability values associated with critical
  factors, and answers to such questions as
• What is the probability that there will be no
  profit?
• What is the probability that the profit will be
  over 1,000,000?
• What is the probability that the project will be
  late by 20?
• What is the probability that the break-even point
  will be under 1,000 units?
• What is the probability that lost sales will be
  under 1,500 units?

18
Using Excel step 1

• The first thing you need to learn is how to
  generate random numbers using the Excel function
  RAND().
• When you enter this function, a random number
  between 0 and 1 appears.
• This is an unusual function, for two reasons.
  First, it has no argument that is, nothing goes
  inside the parentheses. However, the parentheses
  are required. Second, each time the worksheet is
  recalculated, a new random number appears
  automatically. You should play with the RAND( )
  function to understand how it works.

19
Uniform random number

• Uniform random distribution between the lower
  limit L and upper limit U
• "L((U-L) RAND())".
• For whole numbers
• "RANDBETWEEN(100,150)"

20
The model
Open the model
21
The model
22
Runs
In order to repeat the simulation, a table was
built in cells B25 to C124 based on the results
in cells B11 and B12.
23
Results
24
Results

• Cells B15 to B21 summarize the results of the
  table. These of runs.
• The average will hover around 190,000.
• The values of sigma and CF are warnings of the
  uncertainty involved. Indeed, the probability of
  loss hovers around 22.

25
Probability Distribution function
26
Making Decision

• The expected yearly profit is 193,402.
• The standard deviation is 224,911.
• There is approximately a 20 chance of a loss.
• There is approximately an 18 chance that the
  yearly profit will be greater than 400,000.

27
Reference

• Operations Analysis Using Excel. Weida
  Richardson and Vazsony, Duxbury, 2001, Chapter 12.