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 XI

ABOUT THE AUTHORS XV

CHAPTER 1 INTRODUCTION 1

1.1 Models and Modeling 1

1.1.1 Why Study Modeling? 2

1.1.2 Models in Business 2

1.1.3 Models in Business Education 3

1.1.4 Benefits of Business Models 3

1.2 The Role of Spreadsheets 4

1.2.1 Risks of Spreadsheet Use 5

1.2.2 Challenges for Spreadsheet Users 6

1.2.3 Background Knowledge for Spreadsheet Modeling 7

1.3 The Real World and the Model World 7

1.4 Lessons from Expert and Novice Modelers 9

1.4.1 Expert Modelers 9

1.4.2 Novice Modelers 11

1.5 Organization of the Book 12

1.6 Summary 13

Suggested Readings 14

CHAPTER 2 MODELING IN A PROBLEM-SOLVING FRAMEWORK 15

2.1 Introduction 15

2.2 The Problem-Solving Process 16

2.2.1 Some Key Terms 16

2.2.2 The Six-Stage Problem-Solving Process 18

2.2.3 Mental Models and Formal Models 23

2.3 Influence Charts 24

2.3.1 A First Example 25

2.3.2 An Income Statement as an Influence Chart 27

2.3.3 Principles for Building Influence Charts 27

2.3.4 Two Additional Examples 28

2.4 Craft Skills for Modeling 31

2.4.1 Simplify the Problem 33

2.4.2 Break the Problem into Modules 34

2.4.3 Build a Prototype and Refine It 35

2.4.4 Sketch Graphs of Key Relationships 38

2.4.5 Identify Parameters and Perform Sensitivity Analysis 39

2.4.6 Separate the Creation of Ideas from Their Evaluation 41

2.4.7 Work Backward from the Desired Answer 42

2.4.8 Focus on Model Structure, not on Data Collection 43

2.5 Summary 45

Suggested Readings 46

Exercises 46

CHAPTER 3 SPREADSHEET ENGINEERING 49

3.1 Introduction 49

3.2 Designing a Spreadsheet 51

3.2.1 Sketch the Spreadsheet 51

3.2.2 Organize the Spreadsheet into Modules 52

3.2.3 Start Small 53

3.2.4 Isolate Input Parameters 54

3.2.5 Design for Use 54

3.2.6 Keep It Simple 54

3.2.7 Design for Communication 55

3.2.8 Document Important Data and Formulas 55

3.3 Designing a Workbook 57

3.3.1 Use Separate Worksheets to Group Similar Kinds of Information 58

3.3.2 Design Workbooks for Ease of Navigation and Use 59

3.3.3 Design a Workbook as a Decision-Support System 60

3.4 Building a Workbook 62

3.4.1 Follow a Plan 62

3.4.2 Build One Worksheet or Module at a Time 62

3.4.3 Predict the Outcome of Each Formula 62

3.4.4 Copy and Paste Formulas Carefully 62

3.4.5 Use Relative and Absolute Addressing to Simplify Copying 62

3.4.6 Use the Function Wizard to Ensure Correct Syntax 63

3.4.7 Use Range Names to Make Formulas Easy to Read 63

3.4.8 Choose Input Data to Make Errors Stand Out 64

3.5 Testing a Workbook 64

3.5.1 Check That Numerical Results Look Plausible 64

3.5.2 Check That Formulas Are Correct 65

3.5.3 Test That Model Performance Is Plausible 68

3.6 Summary 68

Suggested Readings 69

Exercises 69

CHAPTER 4 ANALYSIS USING SPREADSHEETS 71

4.1 Introduction 71

4.2 Base-case Analysis 72

4.3 What-if Analysis 72

4.3.1 Benchmarking 73

4.3.2 Scenarios 74

4.3.3 Parametric Sensitivity 77

4.3.4 Tornado Charts 79

4.4 Breakeven Analysis 81

4.5 Optimization Analysis 83

4.6 Simulation and Risk Analysis 84

4.7 Summary 85

Exercises 85

CHAPTER 5 DATA EXPLORATION AND PREPARATION 89

5.1 Introduction 89

5.2 Dataset Structure 90

5.3 Types of Data 93

5.4 Data Exploration 93

5.4.1 Understand the Data 94

5.4.2 Organize and Subset the Data 94

5.4.3 Examine Individual Variables Graphically 98

5.4.4 Calculate Summary Measures for Individual Variables 99

5.4.5 Examine Relationships among Variables Graphically 101

5.4.6 Examine Relationships among Variables Numerically 105

5.5 Data Preparation 109

5.5.1 Handling Missing Data 109

5.5.2 Handling Errors and Outliers 111

5.5.3 Binning Continuous Data 111

5.5.4 Transforming Categorical Data 111

5.5.5 Functional Transformations 112

5.5.6 Normalizations 113

5.6 Summary 113

Suggested Readings 114

Exercises 114

CHAPTER 6 CLASSIFICATION AND PREDICTION METHODS 117

6.1 Introduction 117

6.2 Preliminaries 117

6.2.1 The Data-Mining Process 118

6.2.2 The Problem of Overfitting 118

6.2.3 Partitioning the Dataset 120

6.2.4 Measures of Model Quality 120

6.2.5 Variable Selection 125

6.2.6 Setting the Cutoff in Classification 126

6.3 Classification and Prediction Trees 127

6.3.1 Classification Trees 128

6.3.2 An Application of Classification Trees 130

6.3.3 Prediction Trees 137

6.3.4 An Application of Prediction Trees 138

6.3.5 Ensembles of Trees 141

6.4 Additional Algorithms for Classification 143

6.4.1 Logistic Regression 144

6.4.2 Naïve Bayes 150

6.4.3 k-Nearest Neighbors 158

6.4.4 Neural Networks 162

6.5 Additional Algorithms for Prediction 169

6.5.1 Multiple Linear Regression 169

6.5.2 k-Nearest Neighbors 177

6.5.3 Neural Networks 178

6.6 Strengths and Weaknesses of Algorithms 181

6.7 Practical Advice 182

6.8 Summary 183

Suggested Readings 184

Exercises 184

CHAPTER 7 SHORT-TERM FORECASTING 187

7.1 Introduction 187

7.2 Forecasting with Time-Series Models 187

7.2.1 The Moving-Average Model 188

7.2.2 Measures of Forecast Accuracy 191

7.3 The Exponential Smoothing Model 192

7.4 Exponential Smoothing with a Trend 196

7.5 Exponential Smoothing with Trend and Cyclical Factors 198

7.6 Using XLMiner for Short-Term Forecasting 202

7.7 Summary 202

Suggested Readings 203

Exercises 203

CHAPTER 8 NONLINEAR OPTIMIZATION 207

8.1 Introduction 207

8.2 An Optimization Example 208

8.2.1 Optimizing Q1 208

8.2.2 Optimization over All Four Quarters 210

8.2.3 Incorporating the Budget Constraint 211

8.3 Building Models for Solver 213

8.3.1 Formulation 213

8.3.2 Layout 214

8.3.3 Interpreting Results 215

8.4 Model Classification and the Nonlinear Solver 215

8.5 Nonlinear Programming Examples 217

8.5.1 Facility Location 217

8.5.2 Revenue Maximization 219

8.5.3 Curve Fitting 221

8.5.4 Economic Order Quantity 225

8.6 Sensitivity Analysis for Nonlinear Programs 227

8.7 The Portfolio Optimization Model 231

8.8 Summary 234

Suggested Readings 234

Exercises 234

CHAPTER 9 LINEAR OPTIMIZATION 239

9.1 Introduction 239

9.1.1 Linearity 239

9.1.2 Simplex Algorithm 240

9.2 Allocation Models 241

9.2.1 Formulation 241

9.2.2 Spreadsheet Model 242

9.2.3 Optimization 244

9.3 Covering Models 246

9.3.1 Formulation 246

9.3.2 Spreadsheet Model 247

9.3.3 Optimization 247

9.4 Blending Models 248

9.4.1 Blending Constraints 249

9.4.2 Formulation 251

9.4.3 Spreadsheet Model 252

9.4.4 Optimization 252

9.5 Sensitivity Analysis for Linear Programs 253

9.5.1 Sensitivity to Objective Function Coefficients 254

9.5.2 Sensitivity to Constraint Constants 255

9.6 Patterns in Linear Programming Solutions 258

9.6.1 Identifying Patterns 258

9.6.2 Further Examples 260

9.6.3 Review 264

9.7 Data Envelopment Analysis 265

9.8 Summary 269

Suggested Readings 270

Exercises 270

Appendix 9.1 The Solver Sensitivity Report 274

CHAPTER 10 OPTIMIZATION OF NETWORK MODELS 277

10.1 Introduction 277

10.2 The Transportation Model 277

10.2.1 Flow Diagram 278

10.2.2 Model Formulation 278

10.2.3 Spreadsheet Model 279

10.2.4 Optimization 280

10.2.5 Modifications to the Model 281

10.2.6 Sensitivity Analysis 282

10.3 Assignment Model 286

10.3.1 Model Formulation 287

10.3.2 Spreadsheet Model 287

10.3.3 Optimization 288

10.3.4 Sensitivity Analysis 288

10.4 The Transshipment Model 289

10.4.1 Formulation 290

10.4.2 Spreadsheet Model 291

10.4.3 Optimization 292

10.4.4 Sensitivity Analysis 293

10.5 A Standard Form for Network Models 293

10.6 Network Models with Yields 295

10.6.1 Yields as Reductions in Flow 295

10.6.2 Yields as Expansions in Flow 297

10.6.3 Patterns in General Network Models 300

10.7 Network Models for Process Technologies 301

10.7.1 Formulation 301

10.7.2 Spreadsheet Model 303

10.7.3 Optimization 304

10.8 Summary 304

Exercises 305

CHAPTER 11 INTEGER OPTIMIZATION 309

11.1 Introduction 309

11.2 Integer Variables and the Integer Solver 310

11.3 Binary Variables and Binary Choice Models 312

11.3.1 The Capital Budgeting Problem 312

11.3.2 The Set Covering Problem 315

11.4 Binary Variables and Logical Relationships 316

11.4.1 Relationships among Projects 317

11.4.2 Linking Constraints and Fixed Costs 319

11.4.3 Threshold Levels and Quantity Discounts 323

11.5 The Facility Location Model 324

11.5.1 The Capacitated Problem 325

11.5.2 The Uncapacitated Problem 327

11.5.3 The Assortment Model 329

11.6 Summary 330

Suggested Readings 331

Exercises 331

CHAPTER 12 OPTIMIZATION OF NONSMOOTH MODELS 335

12.1 Introduction 335

12.2 Features of the Evolutionary Solver 335

12.3 Curve Fitting (Revisited) 338

12.4 The Advertising Budget Problem (Revisited) 339

12.5 The Capital Budgeting Problem (Revisited) 342

12.6 The Fixed Cost Problem (Revisited) 344

12.7 The Machine-Sequencing Problem 345

12.8 The Traveling Salesperson Problem 347

12.9 Group Assignment 350

12.10 Summary 352

Exercises 352

CHAPTER 13 DECISION ANALYSIS 357

13.1 Introduction 357

13.2 Payoff Tables and Decision Criteria 358

13.2.1 Benchmark Criteria 358

13.2.2 Incorporating Probabilities 359

13.3 Using Trees to Model Decisions 361

13.3.1 Decision Trees 362

13.3.2 Decision Trees for a Series of Decisions 364

13.3.3 Principles for Building and Analyzing Decision Trees 367

13.3.4 The Cost of Uncertainty 368

13.4 Using Decision Tree Software 369

13.4.1 Solving a Simple Example with Decision Tree 370

13.4.2 Sensitivity Analysis with Decision Tree 371

13.4.3 Minimizing Expected Cost with Decision Tree 373

13.5 Maximizing Expected Utility with Decision Tree 375

13.6 Summary 378

Suggested Readings 378

Exercises 378

CHAPTER 14 MONTE CARLO SIMULATION 383

14.1 Introduction 383

14.2 A Simple Illustration 384

14.3 The Simulation Process 386

14.3.1 Base-Case Model 387

14.3.2 Sensitivity Analysis 388

14.3.3 Specifying Probability Distributions 390

14.3.4 Specifying Outputs 391

14.3.5 Setting Simulation Parameters 391

14.3.6 Analyzing Simulation Outputs 391

14.4 Corporate Valuation Using Simulation 395

14.4.1 Base-Case Model 396

14.4.2 Sensitivity Analysis 398

14.4.3 Selecting Probability Distributions 399

14.4.4 Simulation Analysis 399

14.4.5 Simulation Sensitivity 402

14.5 Option Pricing Using Simulation 404

14.5.1 The Logic of Options 405

14.5.2 Modeling Stock Prices 405

14.5.3 Pricing an Option 408

14.5.4 Sensitivity to Volatility 410

14.5.5 Simulation Precision 410

14.6 Selecting Uncertain Parameters 411

14.7 Selecting Probability Distributions 413

14.7.1 Empirical Data and Judgmental Data 413

14.7.2 Six Essential Distributions 414

14.7.3 Fitting Distributions to Data 418

14.8 Ensuring Precision in Outputs 420

14.8.1 Illustrations of Simulation Error 420

14.8.2 Precision versus Accuracy 421

14.8.3 An Experimental Method 422

14.8.4 Precision Using the MSE 423

14.8.5 Simulation Error in a Decision Context 423

14.9 Interpreting Simulation Outcomes 424

14.9.1 Simulation Results 424

14.9.2 Displaying Results on the Spreadsheet 426

14.10 When to Simulate and When Not To Simulate 426

14.11 Summary 428

Suggested Readings 428

Exercises 429

CHAPTER 15 OPTIMIZATION IN SIMULATION 435

15.1 Introduction 435

15.2 Optimization with One or Two Decision Variables 435

15.2.1 Base-case Model 436

15.2.2 Grid Search 438

15.2.3 Optimizing using Simulation Sensitivity 439

15.2.4 Optimizing using Solver 442

15.3 Stochastic Optimization 442

15.3.1 Optimization of the Base-Case Model 442

15.3.2 A Portfolio Optimization Problem 445

15.4 Chance Constraints 448

15.5 Two-Stage Problems with Recourse 453

15.6 Summary 457

Suggested Readings 458

Exercises 458

MODELING CASES 463

APPENDIX 1 BASIC EXCEL SKILLS 479

Introduction 479

Excel Prerequisites 479

The Excel Window 480

Configuring Excel 482

Manipulating Windows and Sheets 483

Navigation 484

Selecting Cells 485

Entering Text and Data 485

Editing Cells 486

Formatting 487

Basic Formulas 488

Basic Functions 489

Charting 493

Printing 495

Help Options 496

Keyboard Shortcuts 497

Cell Comments 497

Naming Cells and Ranges 499

Some Advanced Tools 502

R1C1 Reference Style 502

Mixed Addresses 503

Advanced Functions 503

APPENDIX 2 MACROS AND VBA 507

Introduction 507

Recording a Macro 507

Editing a Macro 510

Creating a User-Defined Function 512

Suggested Readings 514

APPENDIX 3 BASIC PROBABILITY CONCEPTS 515

Introduction 515

Probability Distributions 515

Examples of Discrete Distributions 518

Examples of Continuous Distributions 519

Expected Values 521

Cumulative Distribution Functions 522

Tail Probabilities 523

Variability 524

Sampling 525

INDEX 529