ESD.70J Engineering EconomySession two – SimulationQuestions for “Big vs. Small”Set up random generatorExcel’s RAND() functionRandom number generatorSlide 7Give it a try!How Monte Carlo Simulation worksMonte Carlo SimulationSetup simulation by Data TableExplanationExcel crashing noteSlide 14Calculating descriptive statisticsSlide 16Deterministic vs. dynamic resultsValue at Risk and Gain (VARG)Cumulative Distribution FunctionSlide 20Slide 21Slide 22QuestionSlide 24SummaryNext class…ESD.70J Engineering Economy Module - Session 2 1ESD.70J Engineering EconomyFall 2007Session TwoMichel-Alexandre Cardin – [email protected]. Richard de Neufville – [email protected] Engineering Economy Module - Session 2 2Session two – Simulation•Objectives: –Generate random numbers–Get familiar with Monte Carlo simulation–Set up simulation using Data Table–Generate statistics for simulation –Draw histogram and cumulative distribution function (CDF)•Also called Value At Risk and Gain (VARG) curveESD.70J Engineering Economy Module - Session 2 3Questions for “Big vs. Small”From the base case spreadsheet, we’ve calculated NPVs However, we assumed deterministic demand forecasts for years 1, 2, and 3. This assumption is over-simplifying since actual demand will vary  Since life in uncertain, we’d like to simulate a range of possible NPV outcomes, the Min, Max, distributions, and the expected NPV values!ESD.70J Engineering Economy Module - Session 2 4Set up random generator Open ESD70session2-1.xls http://ardent.mit.edu/real_options/ROcse_Excel_latest/ESD 70 2007/ESD70session2-1.xlsESD.70J Engineering Economy Module - Session 2 5Excel’s RAND() function•Returns random number greater than or equal to 0 and less than 1, sampled from a uniform distribution•To generate a random real number between a and b, use: =RAND()*(b-a)+a•In tab “RAND”, the formula in cell C3: “=Entries!C9*((1-Entries!C25)+2*Entries!C25*RAND())”–Returns a uniformly distributed random demand for year 1 centered around 300, which may differ by plus or minus 50%•Same logic applies for cell C4 and C5ESD.70J Engineering Economy Module - Session 2 6Random number generatorFollow the instructions, step by step1. Go to tab “RAND”2. Type “=Entries!C9*((1-Entries!C25)+2*Entries!C25*RAND())” in cell C33. Type “=Entries!C10*((1-Entries!C25)+2*Entries!C25*RAND())” in cell D34. Type “=Entries!C11*((1-Entries!C25)+2*Entries!C25*RAND())” in cell E35. Press “F9” several times to see want happensESD.70J Engineering Economy Module - Session 2 76. Click “Chart” under “Insert” menu7. “Chart Type” select “XY(Scatter)”, “Chart sub-type” select any one with lines, click “Next”8. “Data Range” select B2:E3, 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 want happensWe have built a random demand generator for the 3 years that assumes independent demand (0 correlation) from year to yearRandom number generatorESD.70J Engineering Economy Module - Session 2 8Give it a try!Check with your neighbors…Check the solution sheet…Ask me questions…ESD.70J Engineering Economy Module - Session 2 9How Monte Carlo Simulation worksCalculate two NPVAs corresponding to the two random demand simulationsDemand in Year 1Demand in Year 2Demand in Year 3NPVA345 678 1001189 579 690How about generating many sets of random demands, and get the corresponding NPVAs automatically?ESD.70J Engineering Economy Module - Session 2 10Monte Carlo SimulationGenerate many sets of random demands for the three-year spanCalculate corresponding big number of NPVA'sStatistical analysisGenerate distribution of NPVAESD.70J Engineering Economy Module - Session 2 11Setup simulation by Data TableFollow these instructions, step by step:1. Link demand in sheet for Plan A to the random demand generator, specifically, Plan A!E5 = Rand!C3; Plan A!G5 = Rand!D3; Plan A!I5 = Rand!E52. In “Simulation” sheet, type “=‘Plan A’!C16” in cell B8 (“=‘Plan A’!C16” is the output of result for NPVA)3. Create the Data Table. Select “A8:B2008”, click “Table” under “Data” menu, in “column input cell” put “A7”, leave “row input cell” blank.4. Same thing done for Plan BNOTE: there is no input in the value column of the Data Table; an empty cell is selected as the “column input cell”. Why?ESD.70J Engineering Economy Module - Session 2 12Explanation•For the One-Way Data Table, there is no need to set up the input values in a list, since each row of the Data Table calls RAND() and generates an NPVA projection•We have 2000 rows in the Data Table, so we have simulated 2000 times•Click “F9” to try another simulation runESD.70J Engineering Economy Module - Session 2 13Excel crashing noteIf your Excel crashes during simulation runs, input some numbers (0’s or whatever) into the input value column to the left of the data series. Do not leave the area of input values blank in the Data TableYou can hide the dummy values by setting their font value to “white” colorESD.70J Engineering Economy Module - Session 2 14Give it a try!Check with your neighbors…Check the solution sheet…Ask me questions…ESD.70J Engineering Economy Module - Session 2 15Calculating descriptive statistics•Useful to know mean, maximum, and minimum values for the simulated resultsFollow step by step:1. In Cell D1 type “=AVERAGE(B$9:B$2008)”2. In Cell D2 type “=MAX(B$9:B$2008)”3. In Cell D3 type “=MIN(B$9:B$2008)”ESD.70J Engineering Economy Module - Session 2 16Give it a try!Check with your neighbors…Check the solution sheet…Ask me questions…ESD.70J Engineering Economy Module - Session 2 17Deterministic vs. dynamic results•From the base case spreadsheet, we learn NPVA is $162.1 million•What is your result for the expected NPVA and NPVB when considering demand uncertainty?•Jensen’s inequality and the Flaw of Averages:)]([)]([ xfExEf ESD.70J Engineering Economy Module - Session 2 18Value at Risk and Gain (VARG)•The VARG curve is another name for cumulative distribution function (CDF)•In our case, VARG curve aims at making a representation to managers that–“There is a probability X that NPV will be lower (higher) than Y dollars for this project”•Value At Risk is a common language on Wall Street. It stresses downside risk, though