1ESD.70J Engineering Economy Module - Session 4 1ESD.70J Engineering Economy ModuleFall 2004Session FourLink for PPT: http://web.mit.edu/tao/www/ESD70/S4/p.pptESD.70J Engineering Economy Module - Session 4 2Questions for “Big or small”From the past sessions, we know how to set up models to incorporate uncertainties. It seems big plant is better… Is it right? Note we assumed we are building a small plant each year regardless what reality turns out to be. This assumption is surely a big mistake. How to correct this?2ESD.70J Engineering Economy Module - Session 4 3Outline • Building flexibility into the model• VAR chart• Breakeven analysis - Goal Seek/Spinner• How to study Excel more by yourselfESD.70J Engineering Economy Module - Session 4 4A key inadequacy of traditional NPV analysis• It assumes the decisions for future are made today, for example for small plants planDecide whether to build a plantDecide whether to build a plantDecide whether to build a plantYear 1 Year 2 Year 3• But the decisions are actually made each yearDecide whether to build a plantDecide whether to build a plantDecide whether to build a plantYear 1 Year 2 Year 33ESD.70J Engineering Economy Module - Session 4 5A key inadequacy of traditional NPV analysis (Cont)• People will make the contingency decision later (when have to) to maximize the value– The simple fact of waiting for more information to come has a lot of value– This inadequacy extorts the value of projects• Ability to make contingency decision late is flexibility• Flexibility is the sword against the enemy of uncertaintyESD.70J Engineering Economy Module - Session 4 6Modeling contingency decisions (flexibility) in spreadsheet• Check the spreadsheet we built for Session 2 at web.mit.edu/tao/www/ESD70/S4/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 to do that?4ESD.70J Engineering Economy Module - Session 4 7Modeling 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 the traditional analysis can be misleading, even when we are aware of uncertainties and do simulationESD.70J Engineering Economy Module - Session 4 8• Other decision rule to suggest?• Let us think how to model this rules?5ESD.70J Engineering Economy Module - Session 4 9Functions 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 10Value 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 V 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 project6ESD.70J Engineering Economy Module - Session 4 11Draw VAR ChartFollow the instructions, step by step1. Go to 1.xls2. Click “Worksheet” under “Insert” menu to add a new sheet, name it “VAR”3. Type “NPVA” in Cell A1, “Rank” in Cell B1, “NPVB” in Cell E1, “Rank” in Cell F14. Type “=Simulation!B8” in cell A2, drag down to A2001; Type” =Simulation!D8” in cell E2, drag down to E20015. 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 F2001ESD.70J Engineering Economy Module - Session 4 126. Type “98%” in both cell J1 and J27. 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 G20018. 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 H20019. Type “VAR for NPVA is” in cell K1; type “VAR for NPVB is” in cell K210. Type “=SUM(C2:C2001)/SUM(D2:D2001)” in cell L1; type “=SUM(G2:G2001)/SUM(H2:H2001)” in cell L27ESD.70J Engineering Economy Module - Session 4 1311. Copy the CDF chart from the “Simulation” sheet to “VAR” sheet12. Type “=L1” in K5, type “=L1” in L5, type “1” in K6, and type “0” in L613. Type “=L2” in K8, type “=L2” in L8, type “1” in K9, and type “0” in L914. Type "=TEXT(J1,"?%")&" VAR for NPVA"" in M5, type "=TEXT(J2,"?%")&" VAR for NPVB"" in M815. Right click the CDF chart, click “Chart Options”, change “Chart title:” from “CDF” to “VAR”16. 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 14Explanation• 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 message8ESD.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 flexibilityESD.70J Engineering Economy Module - Session 4 16Question for “Big or small”Since Plan B with flexibility is better than Plan A, the