1 / 23

ESD.70J Engineering Economy Module

ESD.70J Engineering Economy Module. Fall 2005 Session Four Alex Fadeev - afadeev@mit.edu Link for this PPT: http://ardent.mit.edu/real_options/ROcse_Excel_latest/ExcelSession4.pdf. Questions for “Big vs. small”.

shelby
Download Presentation

ESD.70J Engineering Economy Module

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ESD.70J Engineering Economy Module Fall 2005 Session Four Alex Fadeev - afadeev@mit.edu Link for this PPT: http://ardent.mit.edu/real_options/ROcse_Excel_latest/ExcelSession4.pdf ESD.70J Engineering Economy Module - Session 4

  2. Questions for “Big vs. small” The past three sessions have covered ways to model uncertainty. It seems like the big plant is better… Does it feel right? Note: we had assumed a commitment to building one small plant each year regardless of what demand reality turns out to be. So much for flexibility and common sense. Lets correct that oversight! ESD.70J Engineering Economy Module - Session 4

  3. Outline • Building flexibility into the model • VAR chart • Breakeven analysis - Goal Seek/Spinner • Excel self-study references ESD.70J Engineering Economy Module - Session 4

  4. Decide whether to build a plant Decide whether to build a plant Decide whether to build a plant A key inadequacy of traditional NPV analysis • It assumes that future decisions are made today, for example for small plants construction Year 1 Year 2 Year 3 Decide whether to build a plant • But the decisions are actually made each year Year 1 Year 2 Year 3 ESD.70J Engineering Economy Module - Session 4

  5. A key inadequacy of traditional NPV analysis (Cont) • There is a LOT of value in delaying decisions until: • More information becomes available • Forecast uncertainty decreases with time and additional data • Ability to delay decisions into the future is flexibility • Flexibility is the magic bullet against uncertainty ESD.70J Engineering Economy Module - Session 4

  6. Modeling contingency decisions (flexibility) in spreadsheet • Check the spreadsheet we built for Session Two (http://ardent.mit.edu/real_options/ROcse_Excel_latest/Session2-1.xls) • Press “F9” and check which plan is better? • Think about the following decision rule: • After the first plant is built in year 1, we build an additional small plant only if we observe a bigger demand than capacity • How do we model that? ESD.70J Engineering Economy Module - Session 4

  7. Modeling contingency decisions (flexibility) in spreadsheet (Cont) • In “Plan B” sheet, type • “=IF(E5>E4,E3+1,E3)” in Cell G3, and • “=IF(G5>G4,G3+1,G3)” in Cell I3 • Press “F9” • Now which plan is better? • How easily can the traditional analysis be misleading, despite properly simulate the uncertainties! ESD.70J Engineering Economy Module - Session 4

  8. Other decision rule to suggest? • Let us think how to model this rules? ESD.70J Engineering Economy Module - Session 4

  9. Functions can be used for modeling flexibility • IF(logical_test,value_if_true,value_if_false): Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. • MAX(number1,number2,...): Returns the largest value in a set of values. • Often our decisions are to maximize among different alternatives • MIN(number1,number2, ...): Returns the smallest number in a set of values. • etc. ESD.70J Engineering Economy Module - Session 4

  10. Value at Risk (VAR) Chart • Value at Risk (VAR): • A loss that will not be exceeded at some specified confidence level. • The VAR chart is aimed at making a representation of • “We are X percent certain that we will not lose more than Y dollars for this project.” • VAR is a common language on Wall Street, It stresses downside risk, though we should also look at CDF for upside potential of a project ESD.70J Engineering Economy Module - Session 4

  11. Draw VAR Chart Follow the instructions, step by step • Open our latest spreadsheet, add a new sheet, and name it “VAR” • Type “NPVA” in Cell A1, “Rank” in Cell B1, “NPVB” in Cell E1, “Rank” in Cell F1 • Type “=Simulation!B8” in cell A2, drag down to A2001; Type” =Simulation!D8” in cell E2, drag down to E2001 • Type “=RANK(A2,$A$2:$A$2001,1)” in cell B2, and drag down to B2001; Type “=RANK(E2,$E$2:$E$2001,1)” in cell F2, and drag down to F2001 ESD.70J Engineering Economy Module - Session 4

  12. Type “98%” in both cell J1 and J2 • Type “=IF(B2=(1-$J$1)*2000,A2,0)” in cell C2, and drag down to C2001; type “=IF(F2=(1-$J$2)*2000,E2,0)” in cell G2, and drag down to G2001 • Type “=IF(C2=0,0,1)” in cell D2, and drag down to D2001; type “=IF(G2=0,0,1)” in cell H2, and drag down to H2001 • Type “VAR for NPVA is” in cell K1; type “VAR for NPVB is” in cell K2 • Type “=SUM(C2:C2001)/SUM(D2:D2001)” in cell L1; type “=SUM(G2:G2001)/SUM(H2:H2001)” in cell L2 ESD.70J Engineering Economy Module - Session 4

  13. Copy the CDF chart from the “Simulation” sheet to “VAR” sheet • Type “=L1” in K5, type “=L1” in L5, type “1” in K6, and type “0” in L6 • Type “=L2” in K8, type “=L2” in L8, type “1” in K9, and type “0” in L9 • Type "=TEXT(J1,"?%")&" VAR for NPVA"" in M5, type "=TEXT(J2,"?%")&" VAR for NPVB"" in M8 • Right click the CDF chart, click “Chart Options”, change “Chart title:” from “CDF” to “VAR” • Right click the VAR chart, click “Source Data”, click “Series”. • Click “NPVA Mean” in the left hand window, change “Name:” to “=VAR!$M$5”, change “X Values:” to “=VAR!$K$5:$L$5”, change “Y Values:” to “=VAR!$K$6:$L$6”, • Click “NPVB Mean” in the left hand window, change “Name:” to “=VAR!$M$8”, change “X Values:” to “=VAR!$K$8:$L$8”, change “Y Values:” to “=VAR!$K$9:$L$9”, • click “OK”. ESD.70J Engineering Economy Module - Session 4

  14. Explanation • RANK(number,ref,order): Returns the rank of a number in a list of numbers. • Formulas in column C and G find out the value for the rank corresponding to the specified confidence interval • Formulas in column D and H deal with the exception event when rank ties • If the rank corresponding to the specified confidence interval ties, it returns the correct result • If the rank corresponding to the specified confidence interval is jumped by because of other ties, it returns error message ESD.70J Engineering Economy Module - Session 4

  15. Try different confidence intervals by changing values in J1 and J2… • Compare 98% VAR value for • Plan A • Plan B without flexibility • Plan B with flexibility ESD.70J Engineering Economy Module - Session 4

  16. Question for “Big vs. small” Since Plan B with flexibility is better than Plan A, the manager are about to go with small plant. But the Chief Operations Officer reports the variable cost for the big plant can be further cut (the variable cost for a small plant remains the same). What is the maximum variable cost such that Plan A is still favored? ESD.70J Engineering Economy Module - Session 4

  17. Breakeven analysis • A breakeven level for a parameter – a target value of the parameter at which some particularly interesting event occurs • In a deterministic case, a breakeven point can be determined by “Goal Seek” • In a simulation case, we have to do trial-and-error search, “spinner” is a useful tool to help Excel trial-and-error ESD.70J Engineering Economy Module - Session 4

  18. Goal seek Open the deterministic model from http://ardent.mit.edu/real_options/ROcse_Excel_latest/Session4-2.xls • Follow the instructions, step by step • In sheet “Goal Seek”, type “='Plan A'!C16-'Plan B'!C16” in cell C2 • Click “Goal Seek” under “Tools” menu • Set cell: C2 • To value: 0 • By changing cell: Entries!$C$18 • Click “OK” • Check the value in cell C18 of “Entries” sheet ESD.70J Engineering Economy Module - Session 4

  19. Spinner Follow the instructions, step by step: • In 1.xls, click “Worksheet” under “Insert” menu, name it “Spinner” • Choose View -> Toolbar -> Forms • Click the spinner button and draw a spinner from D3 to D4 • Right click the establishes spinner, click “Format Control” • Change “Current value:” to “128”, put “C4” in “Cell link:” • Type “Variable cost for big plant” in cell B7, type “=C4/100” in B8, type “NPVA – NPVB” in B9, type “=Simulation!$O$2-Simulation!$O$3” in B10 • In “Entries” sheet, type “=Spinner!B8” in C18 • Go to “Spinner” sheet, click spinner and press “F9” to find the value such that NPVA – NPVB is 0 ESD.70J Engineering Economy Module - Session 4

  20. Excel self-study references • MS Support center http://support.microsoft.com/?scid=ph;en-us;2512 • “Advanced Excel for Scientific Data Analysis” by Robert De Levie • “Advanced modeling in finance using Excel and VBA” by Mary Jackson, Mike Staunton • www.amazon.com ESD.70J Engineering Economy Module - Session 4

  21. Big picture of Excel Database functions Date and time functions Engineering functions Financial functions Information functions Logical functions Lookup and reference functions Math and trigonometry functions Text functions Macros • Basics • Names • References • Formula • Functions • Charts • Sensitivity analysis • Statistical analysis • Optimization (Solver) • Simulation • Database Advanced Excel manipulation: Visual Basic ESD.70J Engineering Economy Module - Session 4

  22. Using Excel Help • Ways to search • Contents • Answer wizard • Index • Online at http://support.microsoft.com • Google • Explore links to related topics ESD.70J Engineering Economy Module - Session 4

  23. Summary • Incorporating flexibility into the models • VAR Chart • Breakeven analysis • Spreadsheet as the second best way to do many kinds of modeling… (after specialized $oftware). • Arguably, it is the best tool for modeling, at least the initial pass. Like a Swiss Army knife, it will accompany you throughout your career. • We hope this short course increases your awareness of some of Excel’s intermediate functionality • Uncertainty management is cool ! ESD.70J Engineering Economy Module - Session 4

More Related