1ESD.70J Engineering Economy Module - Session 1 1ESD.70J Engineering Economy ModuleFall 2004Session OneLink for PPT: http://web.mit.edu/tao/www/ESD70/S1/p.pptESD.70J Engineering Economy Module - Session 1 2Introduction of Course• Decision-making based on forecast for future• Who will usually do better?– Deterministic mentality: Improving forecast (and making the best decision against the forecasted scenario) – Dynamic mentality: Believing forecast is “always wrong” (and willing to make a suboptimal decision for any specific scenarios)2ESD.70J Engineering Economy Module - Session 1 3Our ability to forecastHeavier-than-air flying machine are impossible.Lord Kelvin – British Mathematician, Physicist, and President of the British Royal Society, c. 1895They couldn’t hit an elephant at this dis___General John Sedgwick – Union Army Civil War officer’s last words, uttered during the Battle of Spotsylvania, 1864Reagan doesn’t have the presidential look.United Artists Executive – dismissing the idea that Ronald Reagan be offered the starring role in the movie of THE BEST MAN, 1964ESD.70J Engineering Economy Module - Session 1 4Modeling in dynamic mentality• We cannot ignore the intrinsic uncertainty of future• Dynamic mentality in decision-making is critical• Excel supports decisions• We teach in this course how to unleash the power of Excel to model in dynamic nature3ESD.70J Engineering Economy Module - Session 1 5• More about uncertainty and flexibility provided in ESD.71 – Engineering Systems Analysis for Design• ESD.70 provides – necessary knowledge on Excel modeling for ESD.71, and– Introduction of advanced Excel techniques for those interestedESD.70J Engineering Economy Module - Session 1 6But wait… Excel???• Remember here is MIT!• Excel? Toy gun?• Excel can do most common tasks Matlab or other packages can do in modeling for decision supporting• Excel is much more accessible to ultimate decision-makers• Excel is much more POWERFUL that people think, indeed!4ESD.70J Engineering Economy Module - Session 1 7Logic of material - Four blocks• NPV and Sensitivity Analysis - base case and analysis• Monte Carlo Simulation – key technique to crack uncertainties• Modeling Uncertainties – the common stochastic movements• Analyzing Flexibility and Further - flexibility changes risk profile ESD.70J Engineering Economy Module - Session 1 8Teaching methods• We hope you have grasped how to do it when leaving the classroom – Exercise in class– Interactive communication to help learning in class– Solutions provided to students via wireless Web in class• Exercises built around the case of “Big or Small?”• Laptop with wireless connection is essential5ESD.70J Engineering Economy Module - Session 1 9Session one – NPV and Sensitivity Analysis• Objective: – Proper set-up of spreadsheets to facilitate sensitivity analysis– One-way/two-way Data Tables– Goal Seek– ChartsESD.70J Engineering Economy Module - Session 1 10Proper Set-up of spreadsheet• Debugging is inevitable when programming• Sensitivity analysis require changes of a lot of entriesSo, a good habit helps!!• Entire entries in separate area or sheet• Set up the calculations by formulas linked to entries6ESD.70J Engineering Economy Module - Session 1 11Set up NPV base case Try first yourself…ESD.70J Engineering Economy Module - Session 1 12Big or small?• Building a computer plant• Demand are 300,000, 600, 000, and 900,000 respectively for year 1, 2, and 3• No sales in year 4 and after• Big plant capacity of 900,000 with capital cost of $900 million• Each small plant capacity of 300,000 with capital cost of $300 million• Plan A – a big plant; Plan B – one small plant each year• No salvage value for Plan A, $300 million salvage value for Plan B• Discount rate for plan A is 9%, Plan B 8%• The company will get $2,000 for each computer sold• Variable cost for Plan A is $1,280; Variable cost for Plan B is $1,5007ESD.70J Engineering Economy Module - Session 1 13Worksheet for Plan AYear 0123Number of plants111Capacity900 900 900Demand300 600 900Production300 600 900Unit Price (Thousand dollar)222Revenue (Million dollar)60012001800Unit Variable Cost (Thousand dollar)1.28 1.28 1.28Variable Cost (Million dollar)384 768 1152Investment (Million dollar)900000Salvage (Million dollar)0Net value (Million dollar)-900 216 432 648Discount Factor @ 9.0%1 0.917431 0.84168 0.772183Present Value (Million dollar)-900.0 198.2 363.6 500.4NPV (Million dollar) 162.1=1/((1+Entries!$C$3)^E2)Link for Excel: http://web.mit.edu/tao/www/ESD70/S1/1.xlsESD.70J Engineering Economy Module - Session 1 14Worksheet for Plan BYear 0123Number of plants123Capacity300 600 900Demand300 600 900Production300 600 900Unit Price (Thousand dollar)222Revenue (Million dollar)600 1200 1800Unit Variable Cost (Thousand dollar)1.5 1.5 1.5Variable Cost (Million dollar)450 900 1350Investment (Million dollar)300 300 300Salvage (Million dollar)300Net value (Million dollar)-300 -150 0 750Discount Factor @ 8.0%1 0.925926 0.857339 0.793832Present Value (Million dollar)-300.0 -138.9 0.0 595.4NPV (Million dollar) 156.5Link for Excel: http://web.mit.edu/tao/www/ESD70/S1/1.xls8ESD.70J Engineering Economy Module - Session 1 15See the separate sheet for entries…ESD.70J Engineering Economy Module - Session 1 16Check the solution sheet.Any questions?9ESD.70J Engineering Economy Module - Session 1 17Sensitivity Analysis• With the “Big or small” sheet, the manager want to know:– If discount rate for Plan A is fixed, what’s the impact of variation of discount rate for Plan B?– With technology advancement, variable costs may change, what is the impact of such changes?ESD.70J Engineering Economy Module - Session 1 18Tools related to sensitivity analysis• Data Tables• Charts• Goal seek10ESD.70J Engineering Economy Module - Session 1 19Data Tables• Data Tables are used to determine how some outputs vary in response to changes in input. • There are two types of Data Tables: – one-way data tables: determine how changing one input will change any number of outputs– two-way data tables: determine how changing two inputs would change a single outputESD.70J Engineering Economy Module - Session 1 20One-way Data Tables• Step 1: you should create a list of the various input values in a single column or row• Step 2: You must then enter the output formula– If the input values