1ESD.70J Engineering Economy Module - Session 4 1ESD.70J Engineering EconomyFall 2006Session FourAlex Fadeev - [email protected] for this PPT: http://ardent.mit.edu/real_options/ROcse_Excel_latest/ExcelSession4.pdfESD.70J Engineering Economy Module - Session 4 2Questions 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 down the road. So much for flexibility and common sense. Lets correct that oversight!2ESD.70J Engineering Economy Module - Session 4 3Outline • Building flexibility into the model• VAR chart• Breakeven analysis - Goal Seek• Excel self-study referencesESD.70J Engineering Economy Module - Session 4 4Key limitation of NPV analysis• It assumes that future decisions are made today, for example for constructing small plantsDecide 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 plantYear 1 Year 2 Year 33ESD.70J Engineering Economy Module - Session 4 5• There is a LOT of value in delaying decisions until:– More information becomes available– Forecast uncertainty decreases with time and collection of additional data• Ability to delay decisions into the future is flexibility• Flexibility is the magic bullet against uncertaintyKey limitation of NPV analysisESD.70J Engineering Economy Module - Session 4 6Modeling contingency decisions• Recall the spreadsheet we built for Session Two• 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?4ESD.70J Engineering Economy Module - Session 4 7• In “Plan B” sheet:– in Cell G3 type: “=IF(E5>E4,E3+1,E3)”– In Cell I3 type: “=IF(G5>G4,G3+1,G3)”•Press “F9”• Now which plan is better now?• How easily can the traditional analysis be misleading, despite properly simulating the uncertainties!Modeling contingency decisionsESD.70J Engineering Economy Module - Session 4 8Check the solution sheet.Please ask questions now…5ESD.70J Engineering Economy Module - Session 4 9Logical Functions in Excel• IF(logical_test,value_if_true,value_if_false): Returns one value if the test evaluates to TRUE and another value if it evaluates to FALSE.• MAX(number1,number2,...): Returns the largest value in a set of values.– When maximizing among the alternatives• MIN(number1,number2, ...): Returns the smallest number in a set of values.• ...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 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 project6ESD.70J Engineering Economy Module - Session 4 11Building VAR Chart1. Add a new sheet to our latest file, and name it “VAR”2. Type “NPVA” in Cell A1, “Rank” in Cell B1, “NPVB” in Cell E1, “Rank” in Cell F13. Type “=Simulation!B8” in cell A2, drag down to A2001; Type” =Simulation!D8” in cell E2, drag down to E20014. 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 125. Type “98%” in cell J1 and link J2 to J16. In cell C2 type “=IF(B2=(1-$J$1)*2000,A2,0)”, and drag down to C2001; 7. In cell G2 type “=IF(F2=(1-$J$2)*2000,E2,0)”, and drag down to G20018. In cell D2 type “=IF(C2=0,0,1)”, and drag down to D20019. In cell H2 type “=IF(G2=0,0,1)”, and drag down to H200110. Type “VAR for NPVA is” in cell K1; type “VAR for NPVB is” in cell K211. In cell L1 type “=SUM(C2:C2001)/SUM(D2:D2001)”12. In cell L2 type “=SUM(G2:G2001)/SUM(H2:H2001)”13. Copy the CDF chart from the “Simulation” sheet to “VAR” sheet7ESD.70J Engineering Economy Module - Session 4 1314. Type “=L1” in K5, type “=L1” in L5, type “1” in K6, and type “0” in L615. Type “=L2” in K8, type “=L2” in L8, type “1” in K9, and type “0” in L916. Type "=TEXT(J1,"?%")&" VAR for NPVA"" in M5, type "=TEXT(J2,"?%")&" VAR for NPVB"" in M817. Right click the CDF chart, click “Chart Options”, change “Chart title:” from “CDF” to “VAR”18. 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”, 19. 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. • Columns C & G pick out N-th simulation value corresponding to the desired confidence interval. • Column D and H resolve ties and would support calculation of C-VAR– Right now we only look at what happens at xx-%, but that event may not be representative– Frequently want to know “how bad do things get below xx-%”.8ESD.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• Add C-VAR calculationESD.70J Engineering Economy Module - Session 4 16Check the solution sheet.Please ask questions now…9ESD.70J Engineering Economy Module - Session 4 17Question for “Big vs. small”Since Plan B with flexibility is better than Plan A, the manager is tempted to go with small plant. Just then 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 break-even variable cost point for Plan A where the two