ESD.70J Engineering EconomyFall 2010ESD.70J Engineering Economy Module - Session 3 1Fall 2010Session ThreeXin Zhang – [email protected]. Richard de Neufville – [email protected] from Session TwoYesterday we used uniformly distributed random variables to model uncertain demandThis implies identical probability of median as well ESD.70J Engineering Economy Module - Session 3 2This implies identical probability of median as well as extreme high and low outcomes. This is may not be appropriate'⇒ What alternative probability distributions should we use to sample demand?Session three – Modeling Uncertainty• Objectives:– Generate random numbers from various distributions (Normal, Lognormal, etc)• So you can incorporate in your model as you wishESD.70J Engineering Economy Module - Session 3 3– Generate and understand random variables that evolve through time (stochastic processes)• Geometric Brownian Motion, Mean Reversion, S-curveOpen ESD70session3-1Part1.xlsAbout random number generationESD.70J Engineering Economy Module - Session 3 4(Two parts because RAND() calls and graphs take long to compute and update for every Data Table iteration)About random number generation• Generate normally distributed random numbers:– Use NORMINV(RAND(), µ, σ) NORMINV stands for “the inverse of the normal cumulative distribution”) – µ is the mean–σis the standard deviationESD.70J Engineering Economy Module - Session 3 5–σis the standard deviation• In cell B1 in “Sim” sheet, type in “=NORMINV(RAND(), 5, 1)”• Create the Data Table for 2,000 samples• Press “command =“ or “F9”, see what happensRandom numbers from triangular distribution• Triangular distribution could work as an approximation of other distribution (e.g. normal, Weibull, and Beta)–Faster computationally ESD.70J Engineering Economy Module - Session 3 6–Faster computationally • Try “=RAND()+RAND()” in the Data Table output formula cell B1• Press “command =“ or “F9”, see what happensRandom numbers from lognormal distribution• A random variable X has a lognormal distribution if its natural logarithm has a normal distribution•Using LOGINV(RAND(), ln_µ, ln_σ)ESD.70J Engineering Economy Module - Session 3 7•Using LOGINV(RAND(), ln_µ, ln_σ)– ln_µ is the mean of ln(X)– ln_σ is the standard deviation of ln(X)• In the Data Table output formula cell B1, type “=LOGINV(RAND(), 2, 0.3)”• Press “command =“ or “F9”, see what happensGive it a try!Check with your neighbors'ESD.70J Engineering Economy Module - Session 3 8Check the solution sheet'Ask me questions'• We have just described the probability density function (PDF) of random variable x, or f(x)•We can now study the time function of From probability to stochastic processesESD.70J Engineering Economy Module - Session 3 9•We can now study the time function of distribution of random variable x across time, or f(x,t)• That is a stochastic process, or in plain English language: TREND + UNCERTAINTYThree stochastic models• Geometric Brownian Motion•Mean-reversionESD.70J Engineering Economy Module - Session 3 10•Mean-reversion• S-CurveGeometric Brownian Motion• Brownian motion (also called random walk)– The motion of a pollen in water– A drunk walk in Boston Common–S&P500 returnESD.70J Engineering Economy Module - Session 3 11–S&P500 return• Rate of change of the geometric mean is Brownian, not the underlying observations– Stock prices do not necessarily follow Brownian motion, but their returns do!• This is the standard model for modeling stock price behavior in finance theory, and lots of other uncertainties•Mathematic form for Geometric Brownian Motion:Brownian motion theoryESD.70J Engineering Economy Module - Session 3 12•Mathematic form for Geometric Brownian Motion:SdzSdtdSσµ+=where S is the stock price, µ is the annual return trend on the stock, σ is the volatility of the stock price, and dz is the basic Wiener process giving a “random shock” to µtrend uncertaintyOpen ESD70session3-1Part2.xls Simulate a stock priceESD.70J Engineering Economy Module - Session 3 13Open ESD70session3-1Part2.xlsSimulate a stock price• Google’s common stock price as of 8/31/09 was $461.67 (see “GOOG” tab)• Using regression analysis on historical price data, we calculated monthly growth rate (drift) ESD.70J Engineering Economy Module - Session 3 14data, we calculated monthly growth rate (drift) of µ = 1.4% and volatility σ = 31.3%• These two values are key inputs into any forward-looking simulation models. We will be using them repeatedly, so lets define their names'Defining Excel variable names1. Select cell with the historical mean value (1.4%) and go to: “Insert” ⇒ “Name” ⇒“Define”• Formulas ⇒ Name Manager in Excel 2007ESD.70J Engineering Economy Module - Session 3 152. Enter field name “drift” and hit “OK”3. Repeat the same for historical standard deviation and call that variable “vol”Simulate a stock price (Cont)Time Stock PriceRandom Draw from standardized normal distribution1Expected Return + random draw * volatilityComplete the following table for Google stock in tab “GOOG forecast”:ESD.70J Engineering Economy Module - Session 3 16September$461.67=NORMINV(RAND(),0,1)=drift+vol*C2October=B2*(1+D2)NovemberDecember1) Standard normal distribution with mean 0 and standard deviation 1Simulating Google returns in Excel1. In worksheet “GOOG forecast”, type “=NORMINV(RAND(),0,1)” in cell C2, and drag down to cell C132.Type “=drift+vol*C2” in cell D2, and drag down to ESD.70J Engineering Economy Module - Session 3 172.Type “=drift+vol*C2” in cell D2, and drag down to cell D133. Type “=B2*(1+D2)” in cell B3, and drag down to cell B134. Create a “Line Chart” under “Insert” menuGive it a try!Check with your neighbors'ESD.70J Engineering Economy Module - Session 3 18Check the solution sheet'Ask me questions'Mean reversion• Unlike Geometric Brownian Motion that grows at the “drift” rate, some processes have the tendency to –Fluctuate around a meanESD.70J Engineering Economy Module - Session 3 19–Fluctuate around a mean– The farther away from the mean, the higher the probability of reversion to the mean– The speed of mean reversion can be measured by a parameter ηMean reversion theory• Mean reversion has many applications besides modeling interest rate behavior in finance