1ESD.70J Engineering Economy Module - Session 2 1ESD.70J Engineering Economy ModuleFall 2005Session OneAlex 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 an evenly 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 yearESD.70J Engineering Economy Module - Session 2 8How 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’s5ESD.70J Engineering Economy Module - Session 2 9Monte Carlo Simulation (Cont)Generate 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 10Set up simulation by Data TableFollow the 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” blank6ESD.70J Engineering Economy Module - Session 2 11Explanation• 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 runESD.70J Engineering Economy Module - Session 2 12Check this againGenerate many sets of random demands for the three-year spanCalculate corresponding big number of NPVA'sStatistical analysisGenerate distribution of NPVA7ESD.70J Engineering Economy Module - Session 2 13Get descriptive statistics for the simulation results• We want to get the mean (median will be the same), maximum, and minimum value for the simulated results of 2000 NPVAFollow the following instructions, step by step:1. In “Simulation” sheet, type “Mean” in cell D1, “Maximum”in cell D2, “Minimum” in Cell D32. Cell E1 type in “=AVERAGE(B2:B2001)”, Cell E2 type in “=MAX(B2:B2001)”, cell E3 type in “=MIN(B2:B2001)”ESD.70J Engineering Economy Module - Session 2 14Comparison of deterministic and 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≠8ESD.70J Engineering Economy Module - Session 2 15Draw cumulative distribution function (CDF)Follow the instructions, step by step:1. In sheet “Simulation”, type “Bound” in E6, “Count” in F6, and “CDF” in G62. Type in “0, 1, 2, …, 20” in cell D7 to D273. Set Cell E7 “=$E$3+D7*($E$2-$E$3)/20”, and drag the formula down to E274. Set Cell F7 “=COUNTIF($B$2:$B$2001,"<"&TEXT(E7,“#.00000"))”, and drag the formula down to F275. Set Cell G7 “=F7/2000”, and drag down to cell G27ESD.70J Engineering Economy Module - Session 2 166. Click “Chart” under “Insert” menu7. “Standard types” select “XY(Scatter)”, “Chart sub-type” select anyone with lines, click “Next”8. “Data range” select “= Simulation!$E$7:$E$27, Simulation!$G$7:$G$27”, click Next9. “Chart options” select whatever pleases you, click “Next”10. Choose “As object in” and click “Finish”11. In the chart, double click Value (x) axis, “Format axis” window popped out, go to “scale” menu, change “Value (Y) axis crosses at” into “-400”12. Click “F9”, see the CDF movesNow you