ESD.70J Engineering Economy Module - Session 3 1ESD.70J Engineering EconomyFall 2010Session ThreeMichel-Alexandre Cardin – [email protected]. Richard de Neufville – [email protected] Engineering Economy Module - Session 3 2Question from Session TwoYesterday we used uniformly distributed randomvariables to model uncertain demandThis implies identical probability of median as wellas extreme high and low outcomes. This is maynot be appropriate…⇒ What alternative probability distributions shouldwe use to sample demand?ESD.70J Engineering Economy Module - Session 3 3Session three – Modeling Uncertainty• Objectives:– Generate random numbers from variousdistributions (Normal, Lognormal, etc)• So you can incorporate in your model as you wish– Generate and understand random variables thatevolve through time (stochastic processes)• Geometric Brownian Motion, Mean Reversion, S-curveESD.70J Engineering Economy Module - Session 3 4Open ESD70session3-1Part1.xls(Two parts because RAND() calls and graphstake long to compute and update for everyData Table iteration…)About random number generationESD.70J Engineering Economy Module - Session 3 5About 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 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 happensESD.70J Engineering Economy Module - Session 3 6Random numbers from triangular distribution• Triangular distribution could work as anapproximation of other distribution (e.g.normal, Weibull, and Beta)– Faster computationally• Try “=RAND()+RAND()” in the DataTable output formula cell B1• Press “command =“ or “F9”, see whathappensESD.70J Engineering Economy Module - Session 3 7Random numbers from lognormal distribution• A random variable X has a lognormaldistribution if its natural logarithm has a normaldistribution• 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 happensESD.70J Engineering Economy Module - Session 3 8Using @Risk• Open “[email protected]”• In cell B2, type: “=RiskNormal(5,1)”• In cell B3, type: “=RiskTriang(0,1,2)”• In cell B4, type: “=RiskLognorm(E7,G7)”• Go to @Risk menu, use 2,000 iterations• Click on “Start Simulation”• Click on “Summary”, and browse thedistribution of interestESD.70J Engineering Economy Module - Session 3 9Give it a try!Check with your neighbors…Check the solution sheet…Ask me questions…ESD.70J Engineering Economy Module - Session 3 10• We have just described the probability densityfunction (PDF) of random variable x, or f(x)• We can now study the time function ofdistribution of random variable x across time,or f(x,t)• That is a stochastic process, or in plainEnglish language:TREND + UNCERTAINTYFrom probability to stochastic processesESD.70J Engineering Economy Module - Session 3 11Three stochastic models• Geometric Brownian Motion• Mean-reversion• S-CurveESD.70J Engineering Economy Module - Session 3 12Geometric Brownian Motion• Brownian motion (also called random walk)– The motion of a pollen in water– A drunk walk in Boston Common– S&P500 return• Rate of change of the geometric mean isBrownian, not the underlying observations– Stock prices do not necessarily follow Brownianmotion, but their returns do!ESD.70J Engineering Economy Module - Session 3 13• This is the standard model for modeling stockprice behavior in finance theory, and lots of otheruncertainties• Mathematic form for Geometric Brownian Motion:SdzSdtdS!µ+=where S is the stock price, µ is the annual return trend onthe stock, σ is the volatility of the stock price, and dz is thebasic Wiener process giving a “random shock” to µBrownian motion theorytrend uncertaintyESD.70J Engineering Economy Module - Session 3 14Open ESD70session3-1Part2.xlsSimulate a stock priceESD.70J Engineering Economy Module - Session 3 15Simulate a stock price• Google’s common stock price as of 8/31/09was $461.67 (see “GOOG” tab)• Using regression analysis on historical pricedata, we calculated monthly growth rate (drift)of µ = 1.4% and volatility σ = 31.3%• These two values are key inputs into anyforward-looking simulation models. We will beusing them repeatedly, so lets define theirnames…ESD.70J Engineering Economy Module - Session 3 16Defining Excel variable names1. Select cell with the historical mean value(1.4%) and go to: “Insert” ⇒ “Name” ⇒“Define”• Formulas ⇒ Name Manager in Excel 20072. Enter field name “drift” and hit “OK”3. Repeat the same for historical standarddeviation and call that variable “vol”ESD.70J Engineering Economy Module - Session 3 17Simulate a stock price (Cont)DecemberNovember=B2*(1+D2)October=drift+vol*C2=NORMINV(RAND(),0,1)$461.67SeptemberExpected Return +random draw *volatilityRandom Draw fromstandardized normaldistribution1StockPriceTimeComplete the following table for Google stock intab “GOOG forecast”:1) Standard normal distribution with mean 0 and standard deviation 1ESD.70J Engineering Economy Module - Session 3 18Simulating Google returns in Excel1. In worksheet “GOOG forecast”, type“=NORMINV(RAND(),0,1)” in cell C2, and drag downto cell C132. Type “=drift+vol*C2” in cell D2, and drag down tocell D133. Type “=B2*(1+D2)” in cell B3, and drag down to cellB134. Create a “Line Chart” under “Insert” menuESD.70J Engineering Economy Module - Session 3 19Give it a try!Check with your neighbors…Check the solution sheet…Ask me questions…ESD.70J Engineering Economy Module - Session 3 20Mean reversion• Unlike Geometric Brownian Motion thatgrows at the “drift” rate, someprocesses have the tendency to– Fluctuate around a mean– The farther away from the mean, the higherthe probability of reversion to the mean– The speed of mean reversion can bemeasured by a parameter ηESD.70J Engineering Economy Module - Session 3 21Mean reversion theory• Mean reversion has many applicationsbesides modeling interest rate behavior infinance theory• Mathematical