VIEWS: 0 PAGES: 34 CATEGORY: Business POSTED ON: 2/3/2011 Public Domain
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