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
