1ESD.70J Engineering Economy Module - Session 3 1ESD.70J Engineering Economy ModuleFall 2005Session ThreeAlex Fadeev - [email protected] for this PPT: http://ardent.mit.edu/real_options/ROcse_Excel_latest/ExcelSession3.pdfESD.70J Engineering Economy Module - Session 3 2One note on Session TwoIf you Excel crashes during simulation runs, try to input numbers (0’s or whatever) into the dummy input values in a column (or row). Do not leave the area of input values blank in the data table.2ESD.70J Engineering Economy Module - Session 3 3Question from Session TwoLast time we used evenly distributed random variables to model the demand uncertainty. This implies equal probability of median as well as extreme high and low outcomes. It’s not too hard to imagine why this is not very realistic. What alternative models for demand uncertainties should we try?ESD.70J Engineering Economy Module - Session 3 4Session three – Modeling Uncertainties• Generate random numbers from various distributions• Random variables as time function (stochastic processes)– Geometric Brownian Motion– Mean Reversion– S-curve• Statistical analysis to obtain key parameters from a data set3ESD.70J Engineering Economy Module - Session 3 5Generate random numbers from various distributions• How to generate random numbers from normal distribution?– Using norminv(rand(), µ, σ) (norminv stands for “the inverse of the normal cumulative distribution”) – µ is the mean– σ is the standard deviation• In the data table output formula cell (B1 in “Simu”sheet of 1.xls) type in “=norminv(rand(), 5, 1)”. Press “F9”, see what happens) Link for Excel: http://ardent.mit.edu/real_options/ROcse_Excel_latest/Session3-1.xlsESD.70J Engineering Economy Module - Session 3 6How to generate random numbers from triangular distribution• Triangular distribution could work as an approximation of other distribution (e.g. normal, Weibull, and Beta) • Try “=rand()+rand()” in the data table output formula cell (B1 in “Simu” sheet of 1.xls), press “F9”, see what happens.4ESD.70J Engineering Economy Module - Session 3 7How to generate random numbers from lognormal distribution• A random variable X has a lognormal distribution if its natural logarithm has a normal distribution• Using loginv(rand(), log_µ, log_σ)– log_µ is the log mean–log_σ is the log standard deviation• In the data table output formula cell (B1 in “Simu” sheet of 1.xls) type in “=loginv(rand(), 2, 0.3)”. Press “F9”, see what happens) ESD.70J Engineering Economy Module - Session 3 8From probability to stochastic processes• We can describe the probability density function (PDF) of random variable x, or f(x)• Apparently, the distribution of a random variable in the future is not independent from what happens now• Life is random in a non-random way…TimeHistogram0501001502002503001.02 1.216 1.413 1.609 1.806 2.003 2 .199 2.396 2.592 2.789 2.985Hist ogram0501001502002503003503.734 4.835 5.935 7.036 8.136 9.237 10.34 11.44 12.54 13.64 14.74Hist ogram01002003004005006007000.739 6.969 13.2 19.43 25.66 31.89 38.12 44.35 50.57 56.8 63.03Year 1 Year 2Year 35ESD.70J Engineering Economy Module - Session 3 9From probability to stochastic processes (Cont)• We have to study the time function of distribution of random variable x, or f(x,t)• That is a stochastic process, or in language other than mathematics jargon: TREND + UNCERTAINTYESD.70J Engineering Economy Module - Session 3 10Three stochastic models• Geometric Brownian Motion• Mean-reversion•S-Curve6ESD.70J Engineering Economy Module - Session 3 11Geometric Brownian Motion• Brownian motion is a random walk– the motion of a pollen in water– a drunk walks in Boston Common• Geometric means the change rate is Brownian, not the subject itself– For example, in Geometric Brownian Motion model, the stock price itself is not a random work, but the return on the stock isESD.70J Engineering Economy Module - Session 3 12Simulate a stock price• Google’s stock price is $288.45 per class A common share on 9/2/05, assuming volatility of the stock price is 20% per quarter• Volatility can be approximately taken as the standard deviation of quarterly return on stock• Assume quarterly expected return of Google stock is 4%7ESD.70J Engineering Economy Module - Session 3 13Simulate a stock price (Cont)Sep 06Jun 06Mar 06Dec 05$288.45Sep 05Realized return (expected return + random draw * volatility)Random Draw from standardized normal distribution1)Stock PriceTimeComplete the following table for Google stock:1). Standardized normal distribution with mean 0 and standard deviation 1ESD.70J Engineering Economy Module - Session 3 14Using Spreadsheet to simulate Google stockFollow the instructions, step by step:1. Open a new worksheet, name it “GBM”2. Copy or input the table in the previous slide into Excel, with “Time” as cell A13. Type “=norminv(rand(),0,1)” in cell C2, and drag down to cell C64. Type “=0.04+0.20*C2” in cell D2, and drag down to cell D65. Type “=B2*(1+D2)” in cell B3, and drag down to cell B66. Click “Chart” under “Insert” menu8ESD.70J Engineering Economy Module - Session 3 15Using Spreadsheet to simulate Google stock (Cont)7. “Standard types” select “Line”, “Chart sub-type” select whichever you like, click “Next”8. “Data range” select “=GBM!$A$1:$B$6”, click “Next”9. “Chart options” select whatever pleases you, click “Next”10. Choose “As object in” and click “Finish”11. Press “F9” several times to see what happens.ESD.70J Engineering Economy Module - Session 3 16Brownian Motion (Again)• This is the standard model for modeling stock price behavior in finance theory, and lots of other uncertainties (because of the Central Limit Theorem)• Mathematic form for Geometric Brownian Motion (you do not have to know)SdzSdtdSσµ+=where S is the stock price, μis the expected return on the stock, σis the volatility of the stock price, and dz is the basic Wiener process9ESD.70J Engineering Economy Module - Session 3 17Mean-reversion• Unlike Geometric Brownian Motion that grows forever, some processes have the tendency to – fluctuate around a mean– the farther away from the mean, the better the possibility to revert to the mean– the speed of mean reversion can be measured by a parameter ηESD.70J Engineering Economy Module - Session 3 18Simulate interest