MIT OpenCourseWare http ocw mit edu ESD 70J 1 145J Engineering Economy Module Fall 2009 For information about citing these materials or our Terms of Use visit http ocw mit edu terms ESD 70J Engineering Economy Fall 2009 Session Two Michel Alexandre Cardin Prof Richard de Neufville ESD 70J Engineering Economy Module Session 2 1 Session 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 Target curve ESD 70J Engineering Economy Module Session 2 2 Questions 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 want to simulate a range of possible NPV outcomes the Min Max distributions and the expected NPV values ESD 70J Engineering Economy Module Session 2 3 Set up random generator Open ESD70session2 1 xls ESD 70J Engineering Economy Module Session 2 4 Excel 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 1Entries 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 C5 ESD 70J Engineering Economy Module Session 2 5 Random number generator Follow the instructions step by step 1 Go to tab RAND 2 Type Entries C9 1Entries C25 2 Entries C25 RAND in cell C3 3 Type Entries C10 1Entries C25 2 Entries C25 RAND in cell D3 4 Type Entries C11 1Entries C25 2 Entries C25 RAND in cell E3 5 Press F9 several times to see what happens ESD 70J Engineering Economy Module Session 2 6 Random number generator 6 Click Chart under Insert menu 7 Chart Type select XY Scatter Chart subtype 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 happens We have built a random demand generator for the 3 years that assumes independent demand 0 correlation from year to year ESD 70J Engineering Economy Module Session 2 7 Give it a try Check with your neighbors Check the solution sheet Ask me questions ESD 70J Engineering Economy Module Session 2 8 How Monte Carlo Simulation works Calculate two NPVAs corresponding to the two random demand simulations Demand in Demand in Demand in Year 1 Year 2 Year 3 345 678 1001 189 579 690 NPVA How about generating many sets of random demands and get the corresponding NPVAs automatically ESD 70J Engineering Economy Module Session 2 9 Monte Carlo Simulation Generate many sets of random price for the three phase span Calculate corresponding NPVs Generate Distribution of NPVs Statistical Analysis ESD 70J Engineering Economy Module Session 2 10 Setup simulation by Data Table Follow these instructions step by step 1 2 3 4 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 E5 In Simulation sheet type Plan A C16 in cell B8 Plan A C16 is the output of result for NPVA Create the Data Table Select A8 B2008 click Table under Data menu in column input cell put A7 leave row input cell blank Same thing already done for Plan B NOTE 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 11 Explanation 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 2 000 rows in the Data Table so we have simulated 2000 times Click command or F9 to try another simulation run ESD 70J Engineering Economy Module Session 2 12 Excel crashing note If 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 Table You can hide the dummy values by setting their font value to white color ESD 70J Engineering Economy Module Session 2 13 Give it a try Check with your neighbors Check the solution sheet Ask me questions ESD 70J Engineering Economy Module Session 2 14 Calculating descriptive statistics Useful to know mean maximum and minimum values for the simulated results Follow 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 15 Give it a try Check with your neighbors Check the solution sheet Ask me questions ESD 70J Engineering Economy Module Session 2 16 Deterministic 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 f E x E f x ESD 70J Engineering Economy Module Session 2 17 Target curve The target curve is another name for cumulative distribution function CDF In our case a target curve aims at making a representation to managers that There is a probability X that NPV will be lower higher than a targeted Y dollars for this project Value At Risk is a common language on Wall Street It stresses downside risk though we should also look at CDF for upside potential of a project or Value At Gain ESD 70J Engineering Economy Module Session 2 18 Target curve Follow the instructions step by step 1 In sheet Simulation set Cell G7 D 3 D 2 D 3 20 F7 and drag the formula down to G27 2 Set Cell H7 COUNTIF B 9 B 2008 G7 and drag the formula down to H27 3 Set Cell I7 H7 2000 and drag down to cell I27 4 Same is already done for Plan B ESD 70J Engineering Economy Module Session 2 19 Target curve 6 Right click the chart on the right select Source Data 7 Select Series and press Add This adds a new data series to the graph Call it NPVA 8 Select the range Simulation G 7 G 27 for X values and the range Simulation I 7 I 27 for Y values Click OK 9 Right click the curve and change Weight to 3 10 Hit command or F9 and watch the target curve move ESD 70J Engineering Economy Module Session 2 20 Explanation We set up 20 data buckets and count …