1ESD.70J Engineering Economy Module - Session 2 1ESD.70J Engineering EconomyFall 2006Session TwoAlex Fadeev - [email protected] for this PPT: http://ardent.mit.edu/real_options/ROcse_Excel_latest/ExcelSession2.pdfESD.70J Engineering Economy Module - Session 2 2Session two – Simulation• Objective: – Generate random numbers– Set up simulation by Data Table– Generate statistics for simulation – Draw histogram and cumulative distribution function (CDF)2ESD.70J Engineering Economy Module - Session 2 3Questions for “Big vs. small”From the base case spreadsheet, we’ve calculated NPV’s. However, we assumed deterministic demand forecasts for years 1, 2 and 3. It is an over-simplifying assumption since actual demand will vary. We need to evaluate the range of NPV outcomes, their Min, Max, distributions and the expected NPV values!ESD.70J Engineering Economy Module - Session 2 4Outline• Set up random number generator• How does Monte Carlo simulation work• Set up simulation by Data Table• Get descriptive statistics from the simulation• Draw cumulative distribution function (CDF)3ESD.70J Engineering Economy Module - Session 2 5Excel’s RAND() f’n• Returns an evenly distributed random number greater than or equal to 0 and less than 1• To generate a random real number between a and b, use: =RAND()*(b-a)+a• Formula in cell C3: “=Entries!C9*((1-Entries!C25)+2*Entries!C25*RAND())”Returns a uniformly distributed random demand for year 1 around 300, but may differ by plus or minus 50%.[or just: “=Entries!C9*(RAND()+(1-Entries!C25))”]The Same logic for cell C4 and C5ESD.70J Engineering Economy Module - Session 2 6Random number generatorFollow the instructions, step by step1. Click “Worksheet” under “Insert” to add a new sheet, name it “Rand”2. Type in “Year” in cell B2, “Random demand” in cell B3, type “1”, “2”, “3” in cell C2, D2, E2 respectively3. Type “=Entries!C9*((1-Entries!C25)+2*Entries!C25*RAND())” in cell C34. Type “=Entries!C10*((1-Entries!C25)+2*Entries!C25*RAND())” in cell D35. Type “=Entries!C11*((1-Entries!C25)+2*Entries!C25*RAND())” in cell E3Link for Excel: http://ardent.mit.edu/real_options/ROcse_Excel_latest/Session2-2.xls4ESD.70J Engineering Economy Module - Session 2 76. Click “Chart” under “Insert” menu7. “Standard types” select “XY(Scatter)”, “Chart sub-type” select any one with lines, click “Next”8. “Data range” select B2:E3, click Next9. “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 generator (cont)ESD.70J Engineering Economy Module - Session 2 8Check the solution sheet.Please ask questions now…5ESD.70J Engineering Economy Module - Session 2 9How Monte Carlo Simulation worksCalculate two NPVA ’s corresponding to the two random demand simulations6905791891001678345NPVADemand in Year 3Demand in Year 2Demand in Year 1How about generating many sets of random demands, and get the corresponding NPVA’sESD.70J Engineering Economy Module - Session 2 10Monte Carlo Simulation (Cont)Generate many sets of random demands for the three-year spanCalculate corresponding big number of NPVA'sStatistical analysisGenerate distribution of NPVA6ESD.70J Engineering Economy Module - Session 2 11Set up simulation by Data TableFollow these instructions, step by step:1. Link demand in sheet for Plan A to the random demand generated, specifically, Plan A!E5 = Rand!C3; Plan A!G5 = Rand!D3; Plan A!I5 = Rand!E52. Click “Worksheet” under “Insert” menu to add a new sheet, and name the new sheet “Simulation”3. In “Simulation” sheet, Type “NPVA” in cell A1, type “=‘Plan A’!C16” in cell B1 (“=‘Plan A’!C16” is the output of result for NPVA)4. Select “A1:B2001”, click “Table” under “Data” menu, in “column input cell” put “A2002”, leave “row input cell”blankESD.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 (for example A2:B2) calls rand() and generates an NPVAprojection• We have 2000 rows in the Data Table, so we have simulated 2000 times• Click “F9” to try another simulation run7ESD.70J Engineering Economy Module - Session 2 13Excel crashing noteIf you Excel crashes during simulation runs, input some numbers (0’s or whatever) into the dummy input column to the left of the data series. Do not leave the area of input values blank in the data table. You can hide the dummy values by setting their font value to “white” color.ESD.70J Engineering Economy Module - Session 2 14Check the solution sheet.Please ask questions now…8ESD.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 “Simulation” sheet, type “Mean” in cell D1, “Max”in cell D2, “Min” in Cell D32. Cell E1 type in “=AVERAGE(B$9:B$2008)”, Cell E2 type in “=MAX(B$9:B$2008)”, cell E3 type in “=MIN(B$9:B$2008)”ESD.70J Engineering Economy Module - Session 2 16Deterministic vs. dynamic results• From the base case spreadsheet, we learn NPVAis $162.1 million• What is your result for the expected NPVAwhen considering demand uncertainty?• Jensen’s inequality:)]([)]([ xfExEf≠9ESD.70J Engineering Economy Module - Session 2 17Cumulative Distribution FunctionFollow the instructions, step by step:1. In sheet “Simulation”, type “Bound” in G6, “Count”in H6, and “CDF” in I62. Enter “0, 1, 2, …, 20” in cells F7 to F273. Set Cell G7 “=$D$3+($D$2-$D$3)/20*F7”, and drag the formula down to G274. Set Cell H7 “=COUNTIF($B$9:$B$2008,"<="&G7)”, and drag the formula down to H275. Set Cell I7 “=H7/2000”, and drag down to cell I27ESD.70J Engineering Economy Module - Session 2 186. Click “Chart” under “Insert” menu (or click )7. “Standard types” select “XY(Scatter)”, “Chart sub-type” select anything with lines, click “Next”8. “Data range” select =Simulation!$G$7:$L$27”, click <Next>9. Go to <Series> tab and define the two series in which we are interested (bound vs. CDF).