Business Analytics

Steve Powell is a Professor at the Tuck School of Business at Dartmouth College. His primary research interest lies in modeling production and service processes, but he has also been active in research in energy economics, marketing, and operations. At Tuck, he has developed a variety of courses in management science, including the core Decision Science course and electives in the Art of Modeling, Business Analytics, and Simulation. He originated the Teacher's Forum column in Interfaces, and he has written a number of articles on teaching modeling to practitioners. He was the Academic Director of the annual INFORMS Teaching of Management Science Workshops. In 2001, he was awarded the INFORMS Prize for the Teaching of Operations Research/Management Science Practice. Along with Ken Baker, he has directed the Spreadsheet Engineering Research Project. In 2008, he co-authored Modeling for Insight: A Master Class for Business Analysts with Robert J. Batt.Ken Baker is a faculty member at Dartmouth College. He is currently the Nathaniel Leverone Professor of Management at the Tuck School of Business and Adjunct Professor at the Thayer School of Engineering. At Dartmouth, he has taught courses related to Management Science, Decision Support Systems, Manufacturing Management, and Environmental Management. Along with Steve Powell, he has directed the Spreadsheet Engineering Research Project. He is the author of two other textbooks, Optimization Modeling with Spreadsheets and Principles of Sequencing and Scheduling (with Dan Trietsch), in addition to a variety of technical articles. He has served as the Tuck School's Associate Dean and as the Co-Director of the Master's Program in Engineering Management.

• PREFACE XIABOUT THE AUTHORS XVCHAPTER 1 INTRODUCTION 11.1 Models and Modeling 11.1.1 Why Study Modeling? 21.1.2 Models in Business 21.1.3 Models in Business Education 31.1.4 Benefits of Business Models 31.2 The Role of Spreadsheets 41.2.1 Risks of Spreadsheet Use 51.2.2 Challenges for Spreadsheet Users 61.2.3 Background Knowledge for Spreadsheet Modeling 71.3 The Real World and the Model World 71.4 Lessons from Expert and Novice Modelers 91.4.1 Expert Modelers 91.4.2 Novice Modelers 111.5 Organization of the Book 121.6 Summary 13Suggested Readings 14CHAPTER 2 MODELING IN A PROBLEM-SOLVING FRAMEWORK 152.1 Introduction 152.2 The Problem-Solving Process 162.2.1 Some Key Terms 162.2.2 The Six-Stage Problem-Solving Process 182.2.3 Mental Models and Formal Models 232.3 Influence Charts 242.3.1 A First Example 252.3.2 An Income Statement as an Influence Chart 272.3.3 Principles for Building Influence Charts 272.3.4 Two Additional Examples 282.4 Craft Skills for Modeling 312.4.1 Simplify the Problem 332.4.2 Break the Problem into Modules 342.4.3 Build a Prototype and Refine It 352.4.4 Sketch Graphs of Key Relationships 382.4.5 Identify Parameters and Perform Sensitivity Analysis 392.4.6 Separate the Creation of Ideas from Their Evaluation 412.4.7 Work Backward from the Desired Answer 422.4.8 Focus on Model Structure, not on Data Collection 432.5 Summary 45Suggested Readings 46Exercises 46CHAPTER 3 SPREADSHEET ENGINEERING 493.1 Introduction 493.2 Designing a Spreadsheet 513.2.1 Sketch the Spreadsheet 513.2.2 Organize the Spreadsheet into Modules 523.2.3 Start Small 533.2.4 Isolate Input Parameters 543.2.5 Design for Use 543.2.6 Keep It Simple 543.2.7 Design for Communication 553.2.8 Document Important Data and Formulas 553.3 Designing a Workbook 573.3.1 Use Separate Worksheets to Group Similar Kinds of Information 583.3.2 Design Workbooks for Ease of Navigation and Use 593.3.3 Design a Workbook as a Decision-Support System 603.4 Building a Workbook 623.4.1 Follow a Plan 623.4.2 Build One Worksheet or Module at a Time 623.4.3 Predict the Outcome of Each Formula 623.4.4 Copy and Paste Formulas Carefully 623.4.5 Use Relative and Absolute Addressing to Simplify Copying 623.4.6 Use the Function Wizard to Ensure Correct Syntax 633.4.7 Use Range Names to Make Formulas Easy to Read 633.4.8 Choose Input Data to Make Errors Stand Out 643.5 Testing a Workbook 643.5.1 Check That Numerical Results Look Plausible 643.5.2 Check That Formulas Are Correct 653.5.3 Test That Model Performance Is Plausible 683.6 Summary 68Suggested Readings 69Exercises 69CHAPTER 4 ANALYSIS USING SPREADSHEETS 714.1 Introduction 714.2 Base-case Analysis 724.3 What-if Analysis 724.3.1 Benchmarking 734.3.2 Scenarios 744.3.3 Parametric Sensitivity 774.3.4 Tornado Charts 794.4 Breakeven Analysis 814.5 Optimization Analysis 834.6 Simulation and Risk Analysis 844.7 Summary 85Exercises 85CHAPTER 5 DATA EXPLORATION AND PREPARATION 895.1 Introduction 895.2 Dataset Structure 905.3 Types of Data 935.4 Data Exploration 935.4.1 Understand the Data 945.4.2 Organize and Subset the Data 945.4.3 Examine Individual Variables Graphically 985.4.4 Calculate Summary Measures for Individual Variables 995.4.5 Examine Relationships among Variables Graphically 1015.4.6 Examine Relationships among Variables Numerically 1055.5 Data Preparation 1095.5.1 Handling Missing Data 1095.5.2 Handling Errors and Outliers 1115.5.3 Binning Continuous Data 1115.5.4 Transforming Categorical Data 1115.5.5 Functional Transformations 1125.5.6 Normalizations 1135.6 Summary 113Suggested Readings 114Exercises 114CHAPTER 6 CLASSIFICATION AND PREDICTION METHODS 1176.1 Introduction 1176.2 Preliminaries 1176.2.1 The Data-Mining Process 1186.2.2 The Problem of Overfitting 1186.2.3 Partitioning the Dataset 1206.2.4 Measures of Model Quality 1206.2.5 Variable Selection 1256.2.6 Setting the Cutoff in Classification 1266.3 Classification and Prediction Trees 1276.3.1 Classification Trees 1286.3.2 An Application of Classification Trees 1306.3.3 Prediction Trees 1376.3.4 An Application of Prediction Trees 1386.3.5 Ensembles of Trees 1416.4 Additional Algorithms for Classification 1436.4.1 Logistic Regression 1446.4.2 Naïve Bayes 1506.4.3 k-Nearest Neighbors 1586.4.4 Neural Networks 1626.5 Additional Algorithms for Prediction 1696.5.1 Multiple Linear Regression 1696.5.2 k-Nearest Neighbors 1776.5.3 Neural Networks 1786.6 Strengths and Weaknesses of Algorithms 1816.7 Practical Advice 1826.8 Summary 183Suggested Readings 184Exercises 184CHAPTER 7 SHORT-TERM FORECASTING 1877.1 Introduction 1877.2 Forecasting with Time-Series Models 1877.2.1 The Moving-Average Model 1887.2.2 Measures of Forecast Accuracy 1917.3 The Exponential Smoothing Model 1927.4 Exponential Smoothing with a Trend 1967.5 Exponential Smoothing with Trend and Cyclical Factors 1987.6 Using XLMiner for Short-Term Forecasting 2027.7 Summary 202Suggested Readings 203Exercises 203CHAPTER 8 NONLINEAR OPTIMIZATION 2078.1 Introduction 2078.2 An Optimization Example 2088.2.1 Optimizing Q1 2088.2.2 Optimization over All Four Quarters 2108.2.3 Incorporating the Budget Constraint 2118.3 Building Models for Solver 2138.3.1 Formulation 2138.3.2 Layout 2148.3.3 Interpreting Results 2158.4 Model Classification and the Nonlinear Solver 2158.5 Nonlinear Programming Examples 2178.5.1 Facility Location 2178.5.2 Revenue Maximization 2198.5.3 Curve Fitting 2218.5.4 Economic Order Quantity 2258.6 Sensitivity Analysis for Nonlinear Programs 2278.7 The Portfolio Optimization Model 2318.8 Summary 234Suggested Readings 234Exercises 234CHAPTER 9 LINEAR OPTIMIZATION 2399.1 Introduction 2399.1.1 Linearity 2399.1.2 Simplex Algorithm 2409.2 Allocation Models 2419.2.1 Formulation 2419.2.2 Spreadsheet Model 2429.2.3 Optimization 2449.3 Covering Models 2469.3.1 Formulation 2469.3.2 Spreadsheet Model 2479.3.3 Optimization 2479.4 Blending Models 2489.4.1 Blending Constraints 2499.4.2 Formulation 2519.4.3 Spreadsheet Model 2529.4.4 Optimization 2529.5 Sensitivity Analysis for Linear Programs 2539.5.1 Sensitivity to Objective Function Coefficients 2549.5.2 Sensitivity to Constraint Constants 2559.6 Patterns in Linear Programming Solutions 2589.6.1 Identifying Patterns 2589.6.2 Further Examples 2609.6.3 Review 2649.7 Data Envelopment Analysis 2659.8 Summary 269Suggested Readings 270Exercises 270Appendix 9.1 The Solver Sensitivity Report 274CHAPTER 10 OPTIMIZATION OF NETWORK MODELS 27710.1 Introduction 27710.2 The Transportation Model 27710.2.1 Flow Diagram 27810.2.2 Model Formulation 27810.2.3 Spreadsheet Model 27910.2.4 Optimization 28010.2.5 Modifications to the Model 28110.2.6 Sensitivity Analysis 28210.3 Assignment Model 28610.3.1 Model Formulation 28710.3.2 Spreadsheet Model 28710.3.3 Optimization 28810.3.4 Sensitivity Analysis 28810.4 The Transshipment Model 28910.4.1 Formulation 29010.4.2 Spreadsheet Model 29110.4.3 Optimization 29210.4.4 Sensitivity Analysis 29310.5 A Standard Form for Network Models 29310.6 Network Models with Yields 29510.6.1 Yields as Reductions in Flow 29510.6.2 Yields as Expansions in Flow 29710.6.3 Patterns in General Network Models 30010.7 Network Models for Process Technologies 30110.7.1 Formulation 30110.7.2 Spreadsheet Model 30310.7.3 Optimization 30410.8 Summary 304Exercises 305CHAPTER 11 INTEGER OPTIMIZATION 30911.1 Introduction 30911.2 Integer Variables and the Integer Solver 31011.3 Binary Variables and Binary Choice Models 31211.3.1 The Capital Budgeting Problem 31211.3.2 The Set Covering Problem 31511.4 Binary Variables and Logical Relationships 31611.4.1 Relationships among Projects 31711.4.2 Linking Constraints and Fixed Costs 31911.4.3 Threshold Levels and Quantity Discounts 32311.5 The Facility Location Model 32411.5.1 The Capacitated Problem 32511.5.2 The Uncapacitated Problem 32711.5.3 The Assortment Model 32911.6 Summary 330Suggested Readings 331Exercises 331CHAPTER 12 OPTIMIZATION OF NONSMOOTH MODELS 33512.1 Introduction 33512.2 Features of the Evolutionary Solver 33512.3 Curve Fitting (Revisited) 33812.4 The Advertising Budget Problem (Revisited) 33912.5 The Capital Budgeting Problem (Revisited) 34212.6 The Fixed Cost Problem (Revisited) 34412.7 The Machine-Sequencing Problem 34512.8 The Traveling Salesperson Problem 34712.9 Group Assignment 35012.10 Summary 352Exercises 352CHAPTER 13 DECISION ANALYSIS 35713.1 Introduction 35713.2 Payoff Tables and Decision Criteria 35813.2.1 Benchmark Criteria 35813.2.2 Incorporating Probabilities 35913.3 Using Trees to Model Decisions 36113.3.1 Decision Trees 36213.3.2 Decision Trees for a Series of Decisions 36413.3.3 Principles for Building and Analyzing Decision Trees 36713.3.4 The Cost of Uncertainty 36813.4 Using Decision Tree Software 36913.4.1 Solving a Simple Example with Decision Tree 37013.4.2 Sensitivity Analysis with Decision Tree 37113.4.3 Minimizing Expected Cost with Decision Tree 37313.5 Maximizing Expected Utility with Decision Tree 37513.6 Summary 378Suggested Readings 378Exercises 378CHAPTER 14 MONTE CARLO SIMULATION 38314.1 Introduction 38314.2 A Simple Illustration 38414.3 The Simulation Process 38614.3.1 Base-Case Model 38714.3.2 Sensitivity Analysis 38814.3.3 Specifying Probability Distributions 39014.3.4 Specifying Outputs 39114.3.5 Setting Simulation Parameters 39114.3.6 Analyzing Simulation Outputs 39114.4 Corporate Valuation Using Simulation 39514.4.1 Base-Case Model 39614.4.2 Sensitivity Analysis 39814.4.3 Selecting Probability Distributions 39914.4.4 Simulation Analysis 39914.4.5 Simulation Sensitivity 40214.5 Option Pricing Using Simulation 40414.5.1 The Logic of Options 40514.5.2 Modeling Stock Prices 40514.5.3 Pricing an Option 40814.5.4 Sensitivity to Volatility 41014.5.5 Simulation Precision 41014.6 Selecting Uncertain Parameters 41114.7 Selecting Probability Distributions 41314.7.1 Empirical Data and Judgmental Data 41314.7.2 Six Essential Distributions 41414.7.3 Fitting Distributions to Data 41814.8 Ensuring Precision in Outputs 42014.8.1 Illustrations of Simulation Error 42014.8.2 Precision versus Accuracy 42114.8.3 An Experimental Method 42214.8.4 Precision Using the MSE 42314.8.5 Simulation Error in a Decision Context 42314.9 Interpreting Simulation Outcomes 42414.9.1 Simulation Results 42414.9.2 Displaying Results on the Spreadsheet 42614.10 When to Simulate and When Not To Simulate 42614.11 Summary 428Suggested Readings 428Exercises 429CHAPTER 15 OPTIMIZATION IN SIMULATION 43515.1 Introduction 43515.2 Optimization with One or Two Decision Variables 43515.2.1 Base-case Model 43615.2.2 Grid Search 43815.2.3 Optimizing using Simulation Sensitivity 43915.2.4 Optimizing using Solver 44215.3 Stochastic Optimization 44215.3.1 Optimization of the Base-Case Model 44215.3.2 A Portfolio Optimization Problem 44515.4 Chance Constraints 44815.5 Two-Stage Problems with Recourse 45315.6 Summary 457Suggested Readings 458Exercises 458MODELING CASES 463APPENDIX 1 BASIC EXCEL SKILLS 479Introduction 479Excel Prerequisites 479The Excel Window 480Configuring Excel 482Manipulating Windows and Sheets 483Navigation 484Selecting Cells 485Entering Text and Data 485Editing Cells 486Formatting 487Basic Formulas 488Basic Functions 489Charting 493Printing 495Help Options 496Keyboard Shortcuts 497Cell Comments 497Naming Cells and Ranges 499Some Advanced Tools 502R1C1 Reference Style 502Mixed Addresses 503Advanced Functions 503APPENDIX 2 MACROS AND VBA 507Introduction 507Recording a Macro 507Editing a Macro 510Creating a User-Defined Function 512Suggested Readings 514APPENDIX 3 BASIC PROBABILITY CONCEPTS 515Introduction 515Probability Distributions 515Examples of Discrete Distributions 518Examples of Continuous Distributions 519Expected Values 521Cumulative Distribution Functions 522Tail Probabilities 523Variability 524Sampling 525INDEX 529