Transcript Lecture 11

APPLICATIONS OF
PARAMETERIZATION OF
VARIABLES
FOR
MONTE-CARLO RISK
ANALYSIS
Teaching Note
(MS-Excel)
597
WHY ?
• Monte-Carlo risk analysis requires having a
defined probability distribution for each risk
variable
• In most cases the probability distribution is
not readily available
• Need to derive an appropriate distribution
from raw data
598
STEPS TO FOLLOW:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Identify the risk variable and nature of risk
Obtain historical data on the variable
Transfer raw data into spreadsheet
Convert nominal values into real values
Calculate correlations among variables, if needed
Run a regression to identify a trend over years
Obtain residuals from regression
Express residuals as a percentage deviation from the trend
Rank the percentage deviations
Group percentage deviations into ranges
Specify frequency of occurrence for each range
Calculate the expected value
Make adjustments to frequencies, so that the expected value equals to the
deterministic value of risk variable (check for the adjusted expected value)
Transfer the derived probability distribution into risk analysis software
599
1. IDENTIFY THE RISK VARIABLE AND
NATURE OF RISK
• A financial/economic model of the project has to be complete
• Sensitivity analysis suggests candidates to be included as “risk
variables”
• A “risk variable” must be both risky (have a great impact on the
project) and uncertain (not predictable)
• Sensitivity analysis helps to identify the risky variables
• It is the task of analyst to understand the underlying reasons for
uncertainty of variable
600
QUESTIONS TO UNDERSTAND RISK
• What are the fundamental reasons for
movements of the variable over time?
• Can the causes of risk be predicted?
• Are there any related variables, which move in
the same or opposite direction at the same time?
• Is it possible to avoid the risk or reduce it
somehow?
601
2. OBTAIN HISTORICAL DATA ON THE
VARIABLE
• Once the risk variable is identified and justified to be
included into risk analysis
• Need to obtain a reliable set of data on the variable over
time
• As many observations as possible
• If data on the variable itself is not available – use data
on a related variable (fluctuations in the price of natural
gas can be reasonably approximated by movements of
the oil prices)
602
EXAMPLE: DERIVATION OF A PROBABILITY
DISTRIBUTION FOR NATURAL GAS
PRICE
• Natural gas is the major input for production of urea in a
fertilizer plant project
• Price of input was identified as a very risky variable, having a
strong impact on the project’s returns
• Project purchases natural gas as a price-taker
• Natural gas prices follow the international gas prices
• Prices can not be fully predicted – risk analysis is needed
603
• Data on the domestic and international gas prices were
not available
• It is believed that the crude oil prices can be used as a
proxy for fluctuations in the prices of natural gas
• Historic records of the crude oil prices supplied
by the OPEC were obtained from “OPEC Annual
Statistical Bulletin 2000” {www.opec.org}
• Crude oil prices are expressed in nominal US
dollar
604
3. TRANSFER RAW DATA INTO SPREADSHEET
• All data records must be
transferred into an electronic form
• Data is on the crude oil prices in
nominal terms, 1976–1999
($/barrel)
• There are 24 observations
• Prices are annual averages
• The prices are nominal, inclusive
of inflation
• The relevant inflation is the us
dollar inflation
• Inflation effect must be removed
Year
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
Nominal Oil
Price, $/barrel
11.5
12.4
12.7
17.3
28.6
32.5
32.4
29.0
28.2
27.0
13.5
17.7
14.2
17.3
22.3
18.6
18.4
16.3
15.5
16.9
20.3
18.7
12.3
17.5
605
4. CONVERT NOMINAL VALUES INTO
REAL VALUES
Producer Price Index,
USA,1995=100
Year
• Since the oil prices are quoted in us
dollar, use the us inflation index
• The relevant inflation measure is the us
producer price index, base 1995=100
• Data on the US producer price index
were obtained from “IMF Financial
Statistics Yearbook 2000”.
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
49.0
52.0
56.0
63.1
72.0
78.6
80.1
81.1
83.1
82.7
80.3
82.4
85.7
90.0
93.2
93.4
93.9
95.3
96.5
100.0
102.3
102.3
99.7
100.6
606
Year
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
Nominal
Oil Price,
$/barrel
11.5
12.4
12.7
17.3
28.6
32.5
32.4
29.0
28.2
27.0
13.5
17.7
14.2
17.3
22.3
18.6
18.4
16.3
15.5
16.9
20.3
18.7
12.3
17.5
Producer Price
Index, USA,
1995=100
49.0
52.0
56.0
63.1
72.0
78.6
80.1
81.1
83.1
82.7
80.3
82.4
85.7
90.0
93.2
93.4
93.9
95.3
96.5
100.0
102.3
102.3
99.7
100.6
NOMINAL PRICE
REAL
x 100
=
PRICE
PRICE INDEX
Real Oil Price,
$/barrel
23.5
23.8
22.7
27.3
39.8
41.4
40.4
35.8
33.9
32.7
16.8
21.5
16.6
19.2
23.9
19.9
19.6
17.1
16.1
16.9
19.8
18.3
12.3
17.4
607
5. CALCULATE CORRELATIONS
BETWEEN VARIABLES
• If variables tend to move together over time – there is a
correlation
• Coefficient of correlation can be easily estimated from two
sets of data
• Both data sets must be expressed in real terms
• Example: correlation between the price of crude oil (input)
and price of urea fertilizer (output)
• Real price of urea was obtained from nominal price in the
same manner as real oil price
608
CORRELATION BETWEEN THE
Real Oil Price,
PRICE OF CRUDE OIL AND PRICE OF
$/barrel
UREA FERTILIZER
23.5
23.8
22.7
27.3
39.8
41.4
40.4
35.8
33.9
32.7
16.8
21.5
16.6
19.2
23.9
19.9
19.6
17.1
16.1
16.9
19.8
18.3
12.3
17.4
Use ms-excel formula “CORREL“
to estimate the correlation
coefficient between two sets of data:
=CORREL(OIL,UREA)
= 0.544
Real Urea
Price, $/Mt
234.7
269.2
267.9
296.4
326.4
225.2
177.9
172.6
219.6
129.4
87.5
120.2
153.4
101.4
167.7
154.2
122.3
115.4
180.6
207.2
164.6
91.8
67.9
68.8
609
6. RUN A REGRESSION TO IDENTIFY A
TREND OVER YEARS
• There is a trend in the real price of oil
• Generally, trend can be increasing, decreasing or
constant over years
• If plotted, the trend can be seen visually on the chart
• Trend represents “predicted” values
• The difference between the actual price and predicted
price is called “residual” value, which is not
explained by trend
• Residuals represent the random factors affecting the
real price of oil
• Residuals represent the risk
610
REAL PRICE OF CRUDE OIL: ACTUAL VS. PREDICTED
40
35
US$/Mt
45
Real Price of Oil (1976-99)
y = -0.7859x + 33.859
R2 = 0.4159
RANDOM FACTORS
RESIDUAL
30
25
20
ACTUAL REAL
PRICE IN 1984
15
PREDICTED
TREND
10
5
Year
1999
1998
1997
1996
1995
1994
1993
1992
1991
1990
1989
1988
1987
1986
1985
1984
1983
1982
1981
1980
1979
1978
1977
1976
0
RESIDUAL = ACTUAL – PREDICTED
CALCULATED FOR EVERY YEAR
611
• Regression is needed
• Running a regression is
easy
• Use an “add-in” in
excel, called “data
analysis”
• To start:
TOOLS=>
DATA ANALYSIS =>
REGRESSION
612
• SELECT “REGRESSION” AND PRESS “OK”
• Fill in the required fields in the regression box and press “OK”
• The regression will estimate the predicted values and residuals
for every year
613
REAL PRICE OF OIL, 1976-99
YEARS, 1976-99
NEW WORKSHEET PLY [OIL]
RESIDUALS
• Fill-in the regression box as shown above
• Do not change other settings
• When done, a new worksheet called “oil” will appear
614
7.
OBTAIN RESIDUALS FROM REGRESSION
RESIDUAL OUTPUT
Observation Predicted Y
1
33.1
2
32.3
3
31.5
4
30.7
5
29.9
6
29.1
7
28.4
8
27.6
9
26.8
10
26.0
11
25.2
12
24.4
13
23.6
14
22.9
15
22.1
16
21.3
17
20.5
18
19.7
19
18.9
20
18.1
21
17.4
22
16.6
23
15.8
24
15.0
Residuals
-9.6
-8.5
-8.8
-3.4
9.8
12.2
12.1
8.2
7.1
6.7
-8.4
-2.9
-7.0
-3.6
1.8
-1.3
-0.9
-2.6
-2.8
-1.3
2.5
1.7
-3.5
2.4
• New worksheet “oil” will contain
the regression statistics and
residual output
• Residuals are estimated in the
units of variable, $/barrel
• Need to express residuals as a
percentage deviation from the
trend (from predicted value)
615
8.
EXPRESS RESIDUALS AS A PERCENTAGE
DEVIATION FROM THE TREND
Predicted Y Residuals
33.1
-9.6
32.3
-8.5
31.5
-8.8
30.7
-3.4
29.9
9.8
29.1
12.2
28.4
12.1
27.6
8.2
26.8
7.1
26.0
6.7
25.2
-8.4
24.4
-2.9
23.6
-7.0
22.9
-3.6
22.1
1.8
21.3
-1.3
20.5
-0.9
19.7
-2.6
18.9
-2.8
18.1
-1.3
17.4
2.5
16.6
1.7
15.8
-3.5
15.0
2.4
% Deviation from Trend
-28.98%
• USE A SIMPLE FORMULA:
-26.20%
-28.01%
-11.00%
32.91%
=RESIDUAL/(PREDICTED/100)/100
41.92%
42.55%
st
29.87%
For example (1 observation):
26.69%
= -9.6/33.1
25.62%
-33.17%
= -0.2898
-11.92%
-29.72%
-15.85%
8.22%
-6.34%
-4.20%
-13.07%
• Express the result as a
-14.97%
-7.06%
percentage
14.29%
10.21%
• Percentage represents a
-21.96%
deviation from the trend
616
15.80%
9. RANK THE PERCENTAGE
DEVIATIONS
•
Residuals in percentage form represent the
deviations from the trend
•
The percentage deviations must be ranked from
the lowest to highest
•
Use a built-in “sort” function in excel:
1. Highlight all percentage deviations
2. Open “DATA” => “SORT…”
3. Fill-in the sorting box
617
• Fill-in as follows:
SORT BY: % DEVIATION FROM TREND
ASCENDING
HEADER ROW
• When done, press “OK”
618
10. GROUP PERCENTAGE DEVIATIONS INTO RANGES
Ranked % Deviation
-33.17%
-29.72%
-28.98%
-28.01%
-26.20%
-21.96%
-15.85%
-14.97%
-13.07%
-11.92%
-11.00%
-7.06%
-6.34%
-4.20%
8.22%
10.21%
14.29%
15.80%
25.62%
26.69%
29.87%
32.91%
41.92%
42.55%
-35% to -30%
-30% to -20%
-20% to -10%
-10% to 0%
0% to 10%
• Ranked percentage
deviations show the
minimum and maximum
deviations from trend
over the years
• They can be grouped into
ranges, for simplicity
10% to 20%
20% to 30%
30% to 40%
40% to 45%
• In each range, there will
be a few observations
619
11. SPECIFY FREQUENCY OF OCCURRENCE FOR
EACH RANGE
• Frequency of occurrence is the number of observations
in each range
• Total number of observations must be 24
• Express frequencies as probability of occurrence
• Total probability must be always 100%
• Probability of occurrence – is really the derived
probability distribution
• If the expected value of this distribution is equal zero –
then, probability distribution is ready for use
• If the expected value of this distribution is equal zero –
then, further adjustments must be made
620
Frequency
Ranked % Deviation
-33.17%
-29.72%
-28.98%
-28.01%
-26.20%
-21.96%
-15.85%
-14.97%
-13.07%
-11.92%
-11.00%
-7.06%
-6.34%
-4.20%
8.22%
10.21%
14.29%
15.80%
25.62%
26.69%
29.87%
32.91%
41.92%
42.55%
% Occurrence
-35% to -30%
1
4.17%
-30% to -20%
5
20.83%
-20% to -10%
5
20.83%
-10% to 0%
3
12.50%
0% to 10%
1
4.17%
10% to 20%
3
12.50%
20% to 30%
3
12.50%
30% to 40%
40% to 45%
1
2
4.17%
8.33%
Total: 24
100%
621
12. CALCULATE THE EXPECTED VALUE
•
Expected value is a weighted average of mid-point of all
ranges and their probability of occurrence
•
To calculate:
1. Find the mid-point of each range
2. Multiply each mid-point by its probability of occurrence
3. Sum up the results
•
The expected value of probability distribution must be equal
zero, to remain unbiased
•
If the estimated expected value is not zero, further adjustments
are needed
622
Frequency
From
-35.0%
-30.0%
-20.0%
-10.0%
0.0%
10.0%
20.0%
30.0%
40.0%
To
-30.0%
-20.0%
-10.0%
0.0%
10.0%
20.0%
30.0%
40.0%
45.0%
Mid-point
-32.5%
-25.0%
-15.0%
-5.0%
5.0%
15.0%
25.0%
35.0%
42.5%
% Occurrence
1
4.17%
5
20.83%
5
20.83%
3
12.50%
1
4.17%
3
12.50%
3
12.50%
1
4.17%
2
8.3%
Total: 24
100.00%
Expected Value (weighted average):
Mid-point X %
Occurrence
-1.35%
-5.21%
-3.13%
-0.63%
0.21%
1.88%
3.13%
1.46%
3.54%
-0.1042%
• Expected value is simply a weighted average of mid-point
of all ranges and their probability of occurrence
• Expected value here is not equal to zero
623
13. MAKE ADJUSTMENTS TO FREQUENCIES
• To adjust the expected value of probability distribution to zero,
use Excel’s “SOLVER” add-in
To start:
“TOOLS” =>
“SOLVER…”
624
BY CHANGING CELLS: (ALL FREQUENCIES)
Frequency
1
5
5
3
1
3
3
1
2
Total:
SET TARGET CELL = EXPECTED VALUE CELL
EQUAL TO: VALUE OF 0
24
Subject to constraints:
press “ADD”
And take cell with
total frequencies and
set this cell = 24
• When completed, press “SOLVE”
625
From
-35.0%
-30.0%
-20.0%
-10.0%
0.0%
10.0%
20.0%
30.0%
40.0%
To
-30.0%
-20.0%
-10.0%
0.0%
10.0%
20.0%
30.0%
40.0%
45.0%
Mid-point X %
% Occurrence Occurrence
3.97%
-1.29%
20.84%
-5.21%
20.84%
-3.13%
12.52%
-0.63%
4.19%
0.21%
12.53%
1.88%
12.53%
3.13%
4.21%
1.47%
8.38%
3.56%
Mid-point Frequency
0.95
-32.5%
5.00
-25.0%
5.00
-15.0%
3.00
-5.0%
1.01
5.0%
3.01
15.0%
3.01
25.0%
1.01
35.0%
2.01
42.5%
Total: 24
100.0%
Expected Value (weighted average): 0.0%
• Expected value is equal to zero
• Probability distribution is ready
626
14. Transfer the derived probability distribution into
risk analysis software
• We have obtained the following “step” distribution for the
disturbance to the real price of crude oil:
From
-35.0%
-30.0%
-20.0%
-10.0%
0.0%
10.0%
20.0%
30.0%
40.0%
To
-30.0%
-20.0%
-10.0%
0.0%
10.0%
20.0%
30.0%
40.0%
45.0%
% Occurrence
3.97%
20.84%
20.84%
12.52%
4.19%
12.53%
12.53%
4.21%
8.38%
100.0%
627
• Using the “Crystal Ball” risk analysis software will depict this
probability distribution as:
628
FINAL NOTE
• In most cases, probability distribution is applied not on the
value of a variable itself
• Probability distribution is applied on the disturbance to this
variable
• Disturbance, on the average, is expected to be zero
• Spreadsheet may need to be modified to include the
disturbance
629
CORRECT WAY TO MODEL ANNUAL DISTURBANCE:
Year 0
Year 1
Year 2
Year 3
1.000
1.037
1.075
1.115
Disturbance to REAL Price of urea EXPORTS
0.0%
0.0%
0.0%
0.0%
REAL Price of urea EXPORTS (D$/ton) Unadjusted
120
120
120
120
REAL Price of urea EXPORTS (D$/ton) Adjusted
120
120
120
120
NOMINAL Price of urea EXPORTS (D$/ton)
120
123
127
130
YEAR
Domestic Price Index
= Link to Parameter (120D$/ton,
assumed to remain constant)
= Real PriceYearX (Unadj.) * (1+DisturbanceYearX)
= 120 * (1 + 0.0%)
= Real PriceYearX (Adj.) * Domestic Inflation IndexYearX
127 = 120 * 1.075
[for Year 2]
630