DOC PREVIEW
MIT ESD 70J - Study Guide

This preview shows page 1-2-23-24 out of 24 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 24 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 24 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 24 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 24 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 24 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

ESD.70J Engineering EconomyFall 2010ESD.70J Engineering Economy Module - Session 2 1Fall 2010Session TwoXin Zhang – [email protected]. Richard de Neufville – [email protected] two – Simulation• Objectives: – Generate random numbers– Get familiar with Monte Carlo simulation–Set up simulation using Data TableESD.70J Engineering Economy Module - Session 2 2–Set up simulation using Data Table– Generate statistics from simulation – Draw histogram and cumulative distribution function (CDF)• Also called “target curve”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 ESD.70J Engineering Economy Module - Session 2 3simplifying 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 E[NPV]!Set up random generator Open ESD70session2-1.xls ESD.70J Engineering Economy Module - Session 2 4Open ESD70session2-1.xlsExcel’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)+aESD.70J Engineering Economy Module - Session 2 5and 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 C5Random 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-ESD.70J Engineering Economy Module - Session 2 63.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 happens6. 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 Random number generatorESD.70J Engineering Economy Module - Session 2 79.“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 yearGive it a try!Check with your neighbors+ESD.70J Engineering Economy Module - Session 2 8Check the solution sheet+Ask me questions+How Monte Carlo Simulation worksCalculate two NPVAs corresponding to the two random demand simulationsDemand in Year 1Demand in Year 2Demand in Year 3NPVAESD.70J Engineering Economy Module - Session 2 9Year 1Year 2Year 3345 678 1001 ?189 579 690 ?How about generating many sets of random demands, and get the corresponding NPVAs automatically?Monte Carlo SimulationGenerate many sets of random demands for thethree-year spanCalculate corresponding NPVsESD.70J Engineering Economy Module - Session 2 10Generate Distribution of NPVsStatistical AnalysisSetup 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)ESD.70J Engineering Economy Module - Session 2 11output 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 already 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?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 NPVAprojection•We have 2,000 rows in the Data Table, so we ESD.70J Engineering Economy Module - Session 2 12•We have 2,000 rows in the Data Table, so we have simulated 2,000 times• Click “command =” or “F9” to try another simulation runGive it a try!Check with your neighbors+ESD.70J Engineering Economy Module - Session 2 13Check the solution sheet+Ask me questions+Calculating descriptive statistics• Useful to know E[NPV], maximum, and minimum values for the simulated resultsESD.70J Engineering Economy Module - Session 2 14resultsFollow 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)”Give it a try!Check with your neighbors+ESD.70J Engineering Economy Module - Session 2 15Check the solution sheet+Ask me questions+Deterministic vs. dynamic results• From the base case spreadsheet, we learn NPVA= $162.1M and NPVB= $156.5M • What is your result for the E[NPVA] and E[NPVB] when considering demand uncertainty?ESD.70J Engineering Economy Module - Session 2 16when considering demand uncertainty?• Jensen’s inequality and the Flaw of Averages:)]([)]([ xfExEf≠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 thatESD.70J Engineering Economy Module - Session 2 17to 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!Target curveFollow 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 G272.Set Cell H7 “=COUNTIF($B$9:$B$2008,"<="&G7)”, ESD.70J Engineering Economy Module - Session 2 182.Set Cell H7 “=COUNTIF($B$9:$B$2008,"<="&G7)”, and drag the formula down to H273. Set Cell I7 “=H7/2000”, and drag down to cell I274. Same is already done for Plan B6. Right-click the chart on the right, select “Source Data”7. Select


View Full Document

MIT ESD 70J - Study Guide

Download Study Guide
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Study Guide and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Study Guide 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?