DOC PREVIEW
MIT ESD 70J - Excel Session

This preview shows page 1-2-3 out of 10 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 10 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 10 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 10 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 10 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

1ESD.70J Engineering Economy Module - Session 2 1ESD.70J Engineering Economy ModuleFall 2004Session TwoLink for PPT: http://web.mit.edu/tao/www/ESD70/S2/p.pptESD.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 or small”From the base case spreadsheet, we know the NPV’s. We assume deterministic demands in year 1, 2 and 3, however. It is not a valid assumption since actual demands must vary. How can we study the minimum, maximum, and expected NPV’s? And their distributions?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 statistics for simulation results• Draw cumulative distribution function (CDF)3ESD.70J Engineering Economy Module - Session 2 5Random number generatorFollow the instructions, step by step1. Download http://web.mit.edu/tao/www/ESD70/S2/1.xls2. Click “Worksheet” under “Insert” to add a new sheet, name it “Rand”3. Type in “Year” in cell B2, “Random demand” in cell B3, type “1”, “2”, “3” in cell C2, D2, E2 respectively4. Type “=Entries!C9*((1-Entries!C25)+2*Entries!C25*RAND())” in cell C35. Type “=Entries!C10*((1-Entries!C25)+2*Entries!C25*RAND())” in cell D36. Type “=Entries!C11*((1-Entries!C25)+2*Entries!C25*RAND())” in cell E3ESD.70J Engineering Economy Module - Session 2 66. 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 demands generator for the 3 years4ESD.70J Engineering Economy Module - Session 2 7Explanation• Rand() function Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated. • 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%.Same logic for cell C4 and C5ESD.70J Engineering Economy Module - Session 2 8How Monte Carlo Simulation worksCalculate two NPVA ’s corresponding to two random demand realizations6905791891001678345NPVADemand 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 “Simu”3. In “Simu” 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, we do not bother to set up the input values in a list, since each row of the Data Table (for example A2:B2) stimulates a run of rand() and generates a realization of NPVA• 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 statistics for the simulation results• We want to get the mean, maximum, and minimum value for the simulated results of 2000 NPVAFollow the following instructions, step by step:1. In “Simu” 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 “Simu”, 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 “=Simu!$E$7:$E$27,Simu!$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 get the CDF for the simulation9ESD.70J Engineering Economy Module - Session 2 17Explanation• We set up 20 equal-width intervals to study how many data points sitting in each interval• “countif” function counts the number of cells within a range that meet the given criteria.• “text” function converts a value to


View Full Document

MIT ESD 70J - Excel Session

Download Excel Session
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 Excel Session 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 Excel Session 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?