1ESD.70J Engineering Economy Module - Session 3 1ESD.70J Engineering Economy ModuleFall 2004Session ThreeLink for PPT: http://web.mit.edu/tao/www/ESD70/S3/p.pptESD.70J Engineering Economy Module - Session 3 2One note for Session TwoIf you Excel keeps crashing when simulating, 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. By doing that, crashes should be much less frequent.2ESD.70J Engineering Economy Module - Session 3 3Questions for “Big or small”From the simulation in the last session, we know the distribution of the NPV. We used evenly distribute random variables to model the demand uncertainty . Evenly distributed, however, means the probability of very high or low demand is the same as those near the expected demand. Arguably, it has obvious inadequacy for many real cases. What are other possible models for demand uncertainties?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 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• Open web.mit.edu/tao/www/ESD70/S3/1.xls, or establish a simulation spreadsheet by your self• 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) ESD.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.• Asymmetric triangular distribution more complex to generate (if interested, check link: http://www.sics.se/~ali/teaching/sysmod/e05.pdf)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 $105.33 per share on 9/10/04, 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 05Jun 05Mar 05Dec 04$105.33Sep 04Realized 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 –