Transcript Risk Analysis - Foundation Coalition

Introduction to Risk
Analysis
Using Excel
Learning Objective
Time management
Methods

(1) the analytical, mathematical
approach and

(2) the Monte Carlo simulation
technique.
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:
Value
Total industry sales
Market share
Quantity sold
Price per ounce
Revenue
Fixed cost
Variable cost rate
Variable cost
Total cost
Profit
Forecast
$15 million
35%
5.25 million
15 cents
$787,000
$200,000
7.5 cents
$393,750
$593,750
$193,750
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)
Value
Total industry sales
Market share
Quantity sold
Price per ounce
Revenue
Fixed cost
Variable cost rate
Variable cost
Total cost
Profit
Forecast
$15 million
35%
5.25 million
15 cents
$787,000
$200,000
7.5 cents
$393,750
$593,750
$193,750
Uncertainties
Additional Information

Calculate the base-case, best-case and worstcase 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.
Scenarios
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?
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.
Probability Distribution
Probability Distribution of total industry sales
P
1
1,000,000
10
20
30
Total industry sales in millions of dollars
The cumulative probability Distribution

Cumulative
Probability
1.0
The probability that random variable is
“up to” a certain value is represented by
the area under the probability
distribution.
Cumulative
Probability
Distribution
0.5
10
20
30
Total industry sales in millions of dollars
Risk simulation process
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.
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?
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.
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)"
Open the
model
The model
A
1
2
3
4
5
6
7
8
9
10
11
12
B
The model
Total industry sales
Market share
Quantity sold
Price per ounce
Revenue
Fixed cost
Variable cost rate
Variable cost
Total cost
Profit
Loss (1) or profit (0)
=RANDBETWEEN(10000000,20000000)/1000000
=0.3*RAND()+0.2
=B2*B3
=0.1*RAND()+0.1
=B4*B5
=RANDBETWEEN(100000,300000)/1000000
=0.05*RAND()+0.05
=B4*B8
=B7+B9
=B6-B10
=IF(B11<0,1,0)
The model
A
1
2
3
4
5
6
7
8
9
10
11
12
B
The model
Total industry sales
Market share
Quantity sold
Price per ounce
Revenue
Fixed cost
Variable cost rate
Variable cost
Total cost
Profit
Loss (1) or profit (0)
=RANDBETWEEN(10000000,20000000)/1000000
=0.3*RAND()+0.2
=B2*B3
=0.1*RAND()+0.1
=B4*B5
=RANDBETWEEN(100000,300000)/1000000
=0.05*RAND()+0.05
=B4*B8
=B7+B9
=B6-B10
=IF(B11<0,1,0)
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.
A
23
24
25
26
27
28
29
30
31
32
33
34
B
C
Table of 100 Runs
1
2
3
4
5
6
7
8
9
10
Profit
Loss - Yes?
-0.2237708
1
0.195107
0
0.0324161
0
0.4500064
0
0.0759415
0
0.3426906
0
0.2254467
0
-0.0563199
1
-0.0890889
1
0.0803718
0
Results
A
14
15
16
17
18
19
20
21
B
Results
Number of losses (out of 100)
Probablity of loss
Average profit
SumSquares
Variance
Standard deviation
CF
A
14
15
16
17
18
19
20
21
22
22%
0.189
0.087
0.051
0.226
1.195
B
Results
Number of losses (out of 100)
Probablity of loss
Average profit
SumSquares
Variance
Standard deviation
CF
=SUM(C25:C124)
=B15/100
=AVERAGE(B25:B124)
=SUMSQ(B25:B124)/100
=B18-B17^2
=B19^0.5
=B20/B17
Results
A
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%.

14
15
16
17
18
19
20
21
B
Results
Number of losses (out of 100)
Probablity of loss
Average profit
SumSquares
Variance
Standard deviation
CF
22
22%
0.189
0.087
0.051
0.226
1.195
Probability Distribution function
Taunton Pharmaceuticals
20%
Probability
15%
10%
5%
0%
-0.3
-0.1
0.1
0.3
0.5
0.7
0.9
Profits in millions of dollars
1.1
1.3
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.
Reference

Operations Analysis Using Excel. Weida;
Richardson and Vazsony, Duxbury,
2001, Chapter 12.