APPLICATIONS OF PARAMETERIZATION OF VARIABLES FOR MONTE CARLO RISK ANALYSIS Teaching Note MS Excel 597 WHY • M by stk10617

VIEWS: 0 PAGES: 34

More Info
									   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.    Identify the risk variable and nature of risk
2.    Obtain historical data on the variable
3.    Transfer raw data into spreadsheet
4.    Convert nominal values into real values
5.    Calculate correlations among variables, if needed
6.    Run a regression to identify a trend over years
7.    Obtain residuals from regression
8.    Express residuals as a percentage deviation from the trend
9.    Rank the percentage deviations
10.   Group percentage deviations into ranges
11.   Specify frequency of occurrence for each range
12.   Calculate the expected value
13.   Make adjustments to frequencies, so that the expected value equals to the
      deterministic value of risk variable (check for the adjusted expected value)
14.   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
                                                Nominal Oil
                                         Year   Price, $/barrel
                                        1976
• All data records must be              1977
                                                    11.5
                                                    12.4
                                        1978        12.7
  transferred into an electronic form   1979        17.3
• Data is on the crude oil prices in    1980
                                        1981
                                                    28.6
                                                    32.5
                                        1982
  nominal terms, 1976–1999              1983
                                                    32.4
                                                    29.0
  ($/barrel)                            1984
                                        1985
                                                    28.2
                                                    27.0
• There are 24 observations             1986
                                        1987
                                                    13.5
                                                    17.7
• Prices are annual averages            1988
                                        1989
                                                    14.2
                                                    17.3
• The prices are nominal, inclusive     1990
                                        1991
                                                    22.3
                                                    18.6
  of inflation                          1992        18.4
                                        1993        16.3
• The relevant inflation is the us      1994
                                        1995
                                                    15.5
                                                    16.9
  dollar inflation                      1996        20.3
                                        1997        18.7
• Inflation effect must be removed      1998        12.3
                                        1999        17.5     605
       4. CONVERT NOMINAL VALUES INTO
                  REAL VALUES      Producer Price Index,
                                                    USA,1995=100
                                             Year
                                             1976      49.0
                                             1977      52.0
• Since the oil prices are quoted in us      1978
                                             1979
                                                       56.0
                                                       63.1
                                             1980      72.0
  dollar, use the us inflation index         1981      78.6
                                             1982      80.1
• The relevant inflation measure is the us   1983
                                             1984
                                                       81.1
                                                       83.1
                                             1985      82.7
  producer price index, base 1995=100        1986      80.3
                                             1987      82.4

• Data on the US producer price index        1988
                                             1989
                                                       85.7
                                                       90.0
                                             1990      93.2
  were obtained from “IMF Financial          1991      93.4
                                             1992      93.9
  Statistics Yearbook 2000”.                 1993
                                             1994
                                                       95.3
                                                       96.5
                                             1995     100.0
                                             1996     102.3
                                             1997     102.3
                                             1998      99.7
                                             1999     100.6        606
        Nominal      Producer Price
        Oil Price,   Index, USA,                               Real Oil Price,
 Year   $/barrel     1995=100                                  $/barrel
1976      11.5          49.0                                       23.5
1977      12.4          52.0                                       23.8
1978      12.7          56.0                                       22.7
1979      17.3          63.1                                       27.3
1980      28.6          72.0                                       39.8
1981      32.5          78.6                                       41.4
1982      32.4          80.1                                       40.4
1983      29.0          81.1                                       35.8
1984      28.2          83.1                                       33.9
1985      27.0          82.7     REAL    NOMINAL PRICE             32.7
1986      13.5                         =               x 100       16.8
                        80.3     PRICE    PRICE INDEX
1987      17.7          82.4                                       21.5
1988      14.2          85.7                                       16.6
1989      17.3          90.0                                       19.2
1990      22.3          93.2                                       23.9
1991      18.6          93.4                                       19.9
1992      18.4          93.9                                       19.6
1993      16.3          95.3                                       17.1
1994      15.5          96.5                                       16.1
1995      16.9         100.0                                       16.9
1996      20.3         102.3                                       19.8
1997      18.7         102.3                                       18.3
1998      12.3          99.7                                       12.3
1999      17.5         100.6                                       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,                                           Real Urea
   $/barrel
                  PRICE OF CRUDE OIL AND PRICE            Price, $/Mt
   23.5
                       OF UREA FERTILIZER                    234.7
   23.8                                                      269.2
   22.7                                                      267.9
   27.3                                                      296.4
   39.8                                                      326.4
   41.4                                                      225.2
   40.4           Use ms-excel formula “CORREL“              177.9
   35.8                                                      172.6
   33.9           to estimate the correlation                219.6
   32.7                                                      129.4
   16.8
                  coefficient between two sets of data:       87.5
   21.5                                                      120.2
   16.6                                                      153.4
   19.2                                                      101.4
   23.9                                                      167.7
   19.9                                                      154.2
   19.6                                                      122.3
   17.1                                                      115.4
   16.1                                                      180.6
   16.9                                                      207.2
   19.8
   18.3
                  =CORREL(OIL,UREA)                          164.6
                                                              91.8
   12.3
   17.4
                       = 0.544                                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
45
     US$/Mt




                                                                           Real Price of Oil (1976-99)
40



     y = -0.7859x + 33.859
35
          R2 = 0.4159


30                                             RESIDUAL                           RANDOM FACTORS

25




20

              ACTUAL REAL
              PRICE IN 1984
15
                                            PREDICTED
                                                                                  TREND
10




5


                                                                                                                                                                             Year
0
      1976


                1977


                       1978


                              1979


                                     1980


                                              1981


                                                     1982


                                                             1983


                                                                    1984


                                                                           1985


                                                                                  1986


                                                                                         1987


                                                                                                1988


                                                                                                       1989


                                                                                                              1990


                                                                                                                     1991


                                                                                                                            1992


                                                                                                                                   1993


                                                                                                                                          1994


                                                                                                                                                 1995


                                                                                                                                                        1996


                                                                                                                                                               1997


                                                                                                                                                                      1998


                                                                                                                                                                              1999
                              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   Residuals
          1      33.1       -9.6
          2      32.3       -8.5      • New worksheet “oil” will contain
          3      31.5       -8.8        the regression statistics and
          4      30.7       -3.4
          5      29.9        9.8        residual output
          6      29.1       12.2
          7      28.4       12.1
          8      27.6        8.2
          9      26.8        7.1      • Residuals are estimated in the
        10       26.0        6.7
        11       25.2       -8.4        units of variable, $/barrel
        12       24.4       -2.9
        13       23.6       -7.0
        14       22.9       -3.6
        15       22.1        1.8      • Need to express residuals as a
        16       21.3       -1.3
        17       20.5       -0.9        percentage deviation from the
        18       19.7       -2.6        trend (from predicted value)
        19       18.9       -2.8
        20       18.1       -1.3
        21       17.4        2.5
        22       16.6        1.7
        23       15.8       -3.5
        24       15.0        2.4                                         615
         8.    EXPRESS RESIDUALS AS A PERCENTAGE
                   DEVIATION FROM THE TREND
Predicted Y Residuals                                        % Deviation from Trend
                                                                     -28.98%
     33.1
     32.3
             -9.6
             -8.5
                        • USE A SIMPLE FORMULA:                      -26.20%
     31.5    -8.8                                                    -28.01%
     30.7    -3.4                                                    -11.00%
     29.9     9.8       =RESIDUAL/(PREDICTED/100)/100                 32.91%
     29.1    12.2                                                     41.92%
     28.4    12.1                                                     42.55%
     27.6     8.2           For example (1  st observation):          29.87%
     26.8     7.1                                                     26.69%
     26.0     6.7                     = -9.6/33.1                     25.62%
     25.2    -8.4                                                    -33.17%
     24.4    -2.9                     = -0.2898                      -11.92%
     23.6    -7.0                                                    -29.72%
     22.9    -3.6                                                    -15.85%
     22.1     1.8                                                      8.22%
     21.3    -1.3                                                     -6.34%
     20.5    -0.9                                                     -4.20%
     19.7    -2.6                                                    -13.07%
     18.9    -2.8       • Express the result as a                    -14.97%
     18.1    -1.3         percentage                                  -7.06%
     17.4     2.5                                                     14.29%
     16.6     1.7       • Percentage represents a                     10.21%
     15.8    -3.5                                                    -21.96%
     15.0     2.4         deviation from the trend                    15.80%     616
            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%      -35% to -30%   • Ranked percentage
        -29.72%
        -28.98%                       deviations show the
        -28.01%      -30% to -20%
        -26.20%                       minimum and maximum
        -21.96%
        -15.85%                       deviations from trend
        -14.97%
        -13.07%      -20% to -10%     over the years
        -11.92%
        -11.00%
         -7.06%
         -6.34%      -10% to 0%     • They can be grouped into
         -4.20%
          8.22%      0% to 10%        ranges, for simplicity
         10.21%
         14.29%      10% to 20%
         15.80%
         25.62%
                     20% to 30%     • In each range, there will
         26.69%
         29.87%                       be a few observations
         32.91%      30% to 40%
         41.92%
         42.55%      40% to 45%                               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
Ranked % Deviation                   Frequency   % Occurrence
     -33.17%         -35% to -30%          1      4.17%
     -29.72%
     -28.98%
     -28.01%         -30% to -20%          5      20.83%
     -26.20%
     -21.96%
     -15.85%
     -14.97%
     -13.07%         -20% to -10%          5       20.83%
     -11.92%
     -11.00%
      -7.06%
      -6.34%         -10% to 0%            3      12.50%
      -4.20%
       8.22%         0% to 10%             1      4.17%
      10.21%
      14.29%         10% to 20%            3       12.50%
      15.80%
      25.62%
      26.69%         20% to 30%            3       12.50%
      29.87%
      32.91%         30% to 40%            1      4.17%
      41.92%
      42.55%         40% to 45%            2      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                 Mid-point X %
 From      To     Mid-point             % Occurrence    Occurrence
-35.0%   -30.0%    -32.5%            1      4.17%       -1.35%
-30.0%   -20.0%    -25.0%            5     20.83%       -5.21%
-20.0%   -10.0%    -15.0%            5     20.83%       -3.13%
-10.0%     0.0%     -5.0%            3     12.50%       -0.63%
  0.0%    10.0%      5.0%            1      4.17%        0.21%
 10.0%    20.0%     15.0%            3     12.50%        1.88%
 20.0%    30.0%     25.0%            3     12.50%        3.13%
 30.0%    40.0%     35.0%            1      4.17%        1.46%
 40.0%    45.0%     42.5%            2       8.3%        3.54%
                              Total: 24   100.00%

                   Expected Value (weighted average):    -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                    SET TARGET CELL = EXPECTED VALUE CELL
        5
        5
        3
        1
                                               EQUAL TO: VALUE OF 0
        3
        3
        1
        2
Total:   24




 Subject to constraints:
 press “ADD”
 And take cell with
 total frequencies and
 set this cell = 24
                           • When completed, press “SOLVE”
                                                                      625
                                                      Mid-point X %
 From      To     Mid-point Frequency    % Occurrence Occurrence
-35.0%   -30.0%    -32.5%       0.95       3.97%        -1.29%
-30.0%   -20.0%    -25.0%       5.00      20.84%        -5.21%
-20.0%   -10.0%    -15.0%       5.00      20.84%        -3.13%
-10.0%     0.0%     -5.0%       3.00      12.52%        -0.63%
  0.0%    10.0%      5.0%       1.01       4.19%         0.21%
 10.0%    20.0%     15.0%       3.01      12.53%         1.88%
 20.0%    30.0%     25.0%       3.01      12.53%         3.13%
 30.0%    40.0%     35.0%       1.01       4.21%         1.47%
 40.0%    45.0%     42.5%       2.01       8.38%         3.56%
                           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      To      % Occurrence
                        -35.0%   -30.0%      3.97%
                        -30.0%   -20.0%     20.84%
                        -20.0%   -10.0%     20.84%
                        -10.0%     0.0%     12.52%
                          0.0%    10.0%      4.19%
                         10.0%    20.0%     12.53%
                         20.0%    30.0%     12.53%
                         30.0%    40.0%      4.21%
                         40.0%    45.0%      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       Year 0     Year 1   Year 2   Year 3
                          Domestic Price Index      1.000     1.037    1.075    1.115
   = Link to Parameter (120D$/ton,
      assumed to remain constant)


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

                                    = Real PriceYearX (Unadj.) * (1+DisturbanceYearX)
                                                   = 120 * (1 + 0.0%)

     = Real PriceYearX (Adj.) * Domestic Inflation IndexYearX
                127 = 120 * 1.075        [for Year 2]                                    630

								
To top