ESD.70J Engineering Economy ModuleOne note on Session TwoQuestion from Session TwoSession three – Modeling UncertaintiesGenerate random numbers from various distributionsHow to generate random numbers from triangular distributionHow to generate random numbers from lognormal distributionFrom probability to stochastic processesFrom probability to stochastic processes (Cont)Three stochastic modelsGeometric Brownian MotionSimulate a stock priceSimulate a stock price (Cont)Using Spreadsheet to simulate Google stockUsing Spreadsheet to simulate Google stock (Cont)Brownian Motion (Again)Mean-reversionSimulate interest rateSimulate interest rate (Cont)Using Spreadsheet to simulate interest rateUsing Spreadsheet to simulate interest rate (Cont)Mean reversion (Again)S-curveModeling S-curve DeterministicallyModeling S-curve dynamicallyS-curve exampleBig vs. small?Obtaining key parameters from data setExampleExample (Cont)Issues in modelingSummaryNext class…ESD.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.ESD.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 setESD.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.ESD.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.985Histogram0501001502002503003503.734 4.835 5.935 7.036 8.136 9.237 10.34 11.44 12.54 13.64 14.74Histogram01002003004005006007000.739 6.969 13.2 19.43 25.66 31.89 38.12 44.35 50.57 56.8 63.03Year 1 Year 2Year 3ESD.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-CurveESD.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%ESD.70J Engineering Economy Module - Session 3 13Simulate a stock price (Cont)Time Stock Price Random Draw from standardized normal distribution1)Realized return (expected return + random draw * volatility)Sep 05 $288.45Dec 05Mar 06Jun 06Sep 06Complete 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” menuESD.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