ESD.70J Engineering EconomyFall 2010ESD.70J Engineering Economy Module - Session 4 1Fall 2010Session FourXin Zhang – [email protected]. Richard de Neufville – [email protected] 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? ESD.70J Engineering Economy Module - Session 4 2We had assumed full commitment to building one small plant each year regardless of what demand reality turns out to beSo much for flexibility and common sense. Let’s correct that oversight!Session four – Flexibility • Objectives:– Build flexibility into the cash flow model– See how that impacts our decision-making–Perform simple breakeven analysis using ESD.70J Engineering Economy Module - Session 4 3–Perform simple breakeven analysis using Goal Seek– Introduce Solver– Excel self-study referencesKey limitation of NPV analysis• It assumes future decisions are made today, for example for constructing small plantsYear 1 Year 2 Year 3ESD.70J Engineering Economy Module - Session 4 4Decide 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 plant• There is a LOT of value in delaying decisions until:– More information becomes available– Forecast uncertainty decreases with time and collection of additional dataKey limitation of NPV analysisESD.70J Engineering Economy Module - Session 4 5collection of additional data• Ability to delay decisions into the future is an example of flexibility• Flexibility is the magic bullet against uncertainty!• Recall the spreadsheet we built for Session Two– ESD70session2-2.xls• Press “command =“ or “F9”: which plan is better?•Now think about the following decision rule:Modeling contingency decisionsESD.70J Engineering Economy Module - Session 4 6•Now think about the following decision rule:– In Plan B, after the first plant is built in year 1, we build an additional small plant only if we observed a bigger demand than capacity• How do we model that?Open ESD70session4-1.xls Modeling contingency decisionsESD.70J Engineering Economy Module - Session 4 7Open ESD70session4-1.xls• 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 “command =“ or “F9”Modeling contingency decisionsESD.70J Engineering Economy Module - Session 4 8•Press “command =“ or “F9”• Now which plan is better?• How easily can the traditional analysis be misleading, despite properly simulating the uncertainties!Logical 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 ESD.70J Engineering Economy Module - Session 4 9•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 valuesGive it a try!Check with your neighbors)ESD.70J Engineering Economy Module - Session 4 10Check the solution sheet)Ask me questions)Questions• How different is this kind of analysis from Sensitivity Analysis?ESD.70J Engineering Economy Module - Session 4 11• What is the effect on the target curve and histogram for Plan B?• What is the value of flexibility?Question for “Big vs. small”Since Plan B with flexibility is better than Plan A, the manager is tempted to go with small plants. Just then the Chief Operations Officer reports the variable cost for the big plant can ESD.70J Engineering Economy Module - Session 4 12reports 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?Breakeven analysis• A breakeven level for a parameter – a target value where some particularly interesting event occurs•In a deterministic case, a breakeven point can ESD.70J Engineering Economy Module - Session 4 13•In a deterministic case, a breakeven point can be determined using “Goal Seek”• We cannot use Goal Seek with Data Tables (sim on sim). We can still do trial-and-error search!Spinner1. In “Entries” tab, enter “=Simulation!D4-Simulation!D1” next to “mean NPVB– mean NPVA” in cell H32. Choose menu “View” ⇒ “Toolbar” ⇒ “Forms”– Menu “Developer” ⇒ “Insert” in Excel 20073.Click button and draw a Spinner from E18 to E19ESD.70J Engineering Economy Module - Session 4 143.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:”Spinner7. Hit “command =“ or “F9” and see how “E[NPVB] –E[NPVA]” values change8. Somewhere around 1.24 this value approaches 0ESD.70J Engineering Economy Module - Session 4 159. Note: what we are really trying to do is run a “simulation on simulation” to find Plan A’s variable cost where E[NPVA] = E[NPVB]– Excel does not support recursive simulations– Spinner gives an approximate answer, but requires manual inputGive it a try!Check with your neighbors)ESD.70J Engineering Economy Module - Session 4 16Check the solution sheet)Ask me questions)Goal 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 ESD.70J Engineering Economy Module - Session 4 17–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))”Solver• ⇒ use Solver to find optimum plant size• Go to “Tools” ⇒ “Solver”– Menu “Data” in Excel 2007, look to the right•In “Entries” tab, set target cell to “H5” “equal to” ESD.70J Engineering