ESD.70J Engineering EconomyQuestions for “Big vs. small”Session four – FlexibilityKey limitation of NPV analysisSlide 5Modeling contingency decisionsSlide 7Slide 8Logical Functions in ExcelGive it a try!QuestionsQuestion for “Big vs. small”Breakeven analysisSpinnerSlide 15Slide 16Goal Seek SolverSolverSlide 19SummaryExcel self-study referencesBig picture of ExcelUsing Excel HelpESD.70J Engineering Economy Module - Session 4 1ESD.70J Engineering EconomyFall 2007Session FourMichel-Alexandre Cardin – [email protected]. Richard de Neufville – [email protected] 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. Let’s correct that oversight!ESD.70J Engineering Economy Module - Session 4 3Session four – Flexibility •Objectives:–Build flexibility into the model–See how that impacts our decision-making–Perform simple breakeven analysis using 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 3ESD.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 uncertainty!Key limitation of NPV analysisESD.70J Engineering Economy Module - Session 4 6•Recall the spreadsheet we built for Session Two–ESD70session2-2.xls•Press “F9”: which plan is better?•Now think about the following decision rule:–After the first plant is built in year 1 for Plan B, we build an additional small plant only if we observe a bigger demand than capacity•How do we model that?Modeling contingency decisionsESD.70J Engineering Economy Module - Session 4 7Open ESD70session4-1.xls http://ardent.mit. edu/real_options/ROcse_Excel_latest/ESD70session4-1.xlsModeling contingency decisionsESD.70J Engineering Economy Module - Session 4 8•In “Plan B RAND with Flexibility” tab:–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?•How easily can the traditional analysis be misleading, despite properly simulating the uncertainties!Modeling contingency decisionsESD.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 valuesESD.70J Engineering Economy Module - Session 4 10Give it a try!Check with your neighbors…Check the solution sheet…Ask me questions…ESD.70J Engineering Economy Module - Session 4 11Questions•How different is this kind of analysis from sensitivity analysis?•What is the effect on VARG curve and histogram for Plan B?ESD.70J Engineering Economy Module - Session 4 12Question 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 breakeven variable cost point for Plan A where the two plans are equivalent?ESD.70J Engineering Economy Module - Session 4 13Breakeven analysis•A breakeven level for a parameter – a target value where some particularly interesting event occurs•In a deterministic case, a breakeven point can be determined using “Goal Seek”•We cannot use Goal Seek with Data Tables (sim on sim). However, we still can do trial-and-error searchESD.70J Engineering Economy Module - Session 4 14Spinner1. In “Entries” tab, enter “=Simulation!D4-Simulation!D1” next to “mean NPVB – mean NPVA” in cell H32. Choose menu “View” “Toolbar” “Forms”3. Click button and draw a Spinner from E18 to E194. Spinner works with integers, so modify Plan A variable cost in cell C18 to equal = C17/100. Set C17 to 1285. Right click the spinner and click “Format Control”6. Change “Current value:” to “128”, put “C17” in “Cell link:”ESD.70J Engineering Economy Module - Session 4 15Spinner7. Hit F9 and see how “mean NPVB – mean NPVA” values change8. Somewhere around 1.24 this value approaches 09. Note: what we are really trying to do is run a “simulation on simulation” to find Plan A’s variable cost where mean NPVs for both plans are equal–Excel does not support recursive simulations–Spinner gives an approximate answer, but requires manual inputESD.70J Engineering Economy Module - Session 4 16Give it a try!Check with your neighbors…Check the solution sheet…Ask me questions…ESD.70J Engineering Economy Module - Session 4 17Goal Seek Solver•In Session 1 we used Goal Seek to get the exact breakeven point•Solver supports constrained optimization –Now maximize NPV for Plan B by varying size of constructed plant–Assume 1:1 correlation between small plant’s cost and manufacturing capacity ($300M 300K units)–Set C23 = C15 on the “Entries” sheet–Change demand expectations to {200, 600, 800}–Erase Data Tables to speed up analysis–In “Plan B - Solver”, set Salvage value cell I12 to “=MIN(Entries!C29, MAX(C11,E11,G11))”ESD.70J Engineering Economy Module - Session 4 18Solver use Solver to find optimum plant size•Go to “Tools” “Solver”•In “Entries” tab, set target cell to “H5” “equal to” “MAX”•Set “By changing cells” to “C15”•“Subject to the Constraints:” $C$15 => 0•Hit “Solve”… •Optimal small plant size is?ESD.70J
View Full Document