ESD.70J Engineering EconomyToday’s class outlineSlide 3Beyond this classCourse OutlineCourse MaterialsCourse PhilosophyModeling with dynamic mentalitySession one – Big vs. SmallProper spreadsheet setupNet Present Value (NPV) primerSet up NPV base caseBig vs. Small setupWorksheet for Plan ASlide 15Give it a try!Worksheet for Plan BConclusion?Sensitivity AnalysisSlide 20Data TablesOne-way Data TableSlide 23Slide 24One-way Data Table (Cont)More Excel tricksModifying Data TablesSlide 28Two-way Data TablesSlide 30Slide 31Slide 32Conditional formattingSlide 34Excel chartsThe Chart WizardSlide 37Chart exampleSlide 39Goal SeekGoal Seek ExampleAdditional note: NPV functionAdditional note: Text functionSlide 44Questions? Comments? Suggestions?SummaryNext class…ESD.70J Engineering Economy Module - Session 1 1ESD.70J Engineering EconomyFall 2010Session OneXin Zhang – [email protected]. Richard de Neufville – [email protected]://ardent.mit.edu/real_options/ROcse_Excel_latest/Excel_Class.htmlESD.70J Engineering Economy Module - Session 1 2Today’s class outline1. Introduction2. Course outline and philosophy3. Session 1 – base case construction and sensitivity analysisESD.70J Engineering Economy Module - Session 1 3•Informal:–Please ask questions at any time–Compare notes/help each other •Theoretically easy going:–Covering a few relevant theories – you DON’T have to learn theory to benefit from this class–Just learn how to use them in Excel•Take it for credit, or attend to learn/have fun•For credit – no exam – one final assignment!About this classESD.70J Engineering Economy Module - Session 1 4•More about uncertainty and flexibility in 1.146/3.56/16.861/ESD.71/ESD.710 – Risk and Decision Analysis / Engineering Systems Analysis for Design–ESD.70 provides necessary knowledge of Excel for ESD.710/ESD.71–Introduction of advanced Excel techniques•Want more?–ESD.712: Tools for Analysis: Design for Real Estate and Infrastructure Development–ESD.344: Real Options for Product and Systems Design–ESD.77: Multi-Disciplinary Systems Design OptimizationBeyond this classESD.70J Engineering Economy Module - Session 1 5Course OutlineFour (4) recitation-style sessions: 1. NPV, Data Tables, and Sensitivity Analysis 2. Monte Carlo simulations3. Modeling uncertainty using different stochastic models and probability distributions4. Analyzing the system in the context of flexibilityESD.70J Engineering Economy Module - Session 1 6Course Materials•Excel spreadsheets–ESD70session# –1.xls : setup before the class–ESD70session# –2.xls : reflects all the work done in class–Do the exercises with me  best way to learn•Cells marked as are for you to fill–Refer to the ESD70session# –2.xls to validate your work•Lectures in PDF on the webESD.70J Engineering Economy Module - Session 1 7Course Philosophy•Decision-making with an eye towards the future can be approached from two strategic directions:–Deterministic – improving forecast (and making the best decision based on the probabilities of the expected outcomes) –Dynamic – admitting that the forecast is likely to be wrong (recognizing uncertainty explicitly and the capacity to makes decisions along the way)•Exercises built around analyzing the case of “Big vs. Small”ESD.70J Engineering Economy Module - Session 1 8Modeling with dynamic mentality•Cannot ignore the intrinsic uncertainty of the future  DYNAMIC mentality in decision-making to the rescue•Excel is a decent tool for decision analysis •We teach you how to unleash the power of Excel to model in a dynamic settingESD.70J Engineering Economy Module - Session 1 9Session one – Big vs. Small•Objectives: –Good spreadsheet setup habits to facilitate Sensitivity Analysis (examples anyone?)–Charts–One-way/Two-way Data Tables–Goal SeekESD.70J Engineering Economy Module - Session 1 10Proper spreadsheet setup•Programming/modeling  debugging•Sensitivity Analysis requires input changesGood habits that will make your life easier:•Enter inputs in a separate area or a sheet•Set up the calculations by formulas linked to entries => NEVER HARD CODE INPUTSESD.70J Engineering Economy Module - Session 1 11Net Present Value (NPV) primer•NPV = PV (Revenues) - PV (Costs)NPV ≥ 0  valuable project‘r’: reflects risk of project, expressed as required rate of return. Also called discount rate or opportunity cost of capital. It is a “translation factor” between present and future moneyNPV= Rt Ct(1+r)tt =0TESD.70J Engineering Economy Module - Session 1 12Set up NPV base case Open ESD70session1-1.xls (we have saved you the joy of manually entering the data)ESD.70J Engineering Economy Module - Session 1 13Big vs. Small setup•Building a computer plant•Deterministic demand projections for years 1, 2 and 3 are 300,000, 600,000, and 900,000 respectively•No sales in year 4 or thereafter•Plan A – a big plant; Plan B – one small plant each year; •Plants take a few months to construct•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•No salvage value for Plan A; $300 million salvage value for Plan B•Discount rate for Plan A is 9%, and 8% for Plan B•The company will sell each computer for $2,000 •Variable cost for Plan A is $1,280 due to economies of scale; Variable cost for Plan B is $1,500•See “Entries” Worksheet…ESD.70J Engineering Economy Module - Session 1 14Worksheet for Plan AYear 0 1 2 3Number of plants 1 1 1Capacity 900 900 900Demand 300 600 900Production 300 600 900Unit Price (Thousand dollar) 2 2 2Revenue (Million dollar) 600 1200 1800Unit Variable Cost (Thousand dollar) 1.28 1.28 1.28Variable Cost (Million dollar) 384 768 1152Investment (Million dollar) 900 0 0 0Salvage (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.1ESD.70J Engineering Economy Module - Session 1 15Worksheet for Plan AYear 0 1 2 3Number of plants 1 1 1Capacity 900 900 900Demand 300 600 900Production 300 600 900Unit Price (Thousand dollar) 2 2 2Revenue (Million dollar) 600 1200 1800Unit Variable Cost (Thousand dollar) 1.28 1.28 1.28Variable Cost (Million dollar) 384 768 1152Investment (Million dollar) 900 0 0 0Salvage (Million dollar) 0Net value (Million dollar)