Optimization Modeling with Spreadsheets

Preface ix

1 Introduction to Spreadsheet Models for Optimization 1
1.1 Elements of a Model 2
1.2 Spreadsheet Models 4
1.3 A Hierarchy for Analysis 7
1.4 Optimization Software 8
1.5 Using Solver 10

2 Linear Programming: Allocation, Covering, and Blending Models 21
2.1 Linear Models 22
2.2 Allocation Models 29
2.3 Covering Models 38
2.4 Blending Models 47
2.5 Modeling Errors in Linear Programming 52

3 Linear Programming: Network Models 65
3.1 The Transportation Model 66
3.2 The Assignment Model 71
3.3 The Transshipment Model 75
3.4 Features of Special Network Models 78
3.5 Building Network Models with Balance Equations 79
3.6 General Network Models with Yields 84
3.7 General Network Models with Transformed Flows 91

4 Sensitivity Analysis in Linear Programs 108
4.1 Parameter Analysis in the Transportation Example 109
4.2 Parameter Analysis in the Allocation Example 116
4.3 The Sensitivity Report and the Transportation Example 123
4.4 The Sensitivity Report and the Allocation Example 127
4.5 Degeneracy and Alternative Optima 129
4.6 Patterns in Linear Programming Solutions 133

5 Linear Programming: Data Envelopment Analysis 160
5.1 A Graphical Perspective on DEA 162
5.2 An Algebraic Perspective on DEA 166
5.3 A Spreadsheet Model for DEA 168
5.4 Indexing 173
5.5 Reference Sets and HCUs 174
5.6 Assumptions and Limitations of DEA 178

6 Integer Programming: Binary-Choice Models 191
6.1 Using Solver with Integer Requirements 193
6.2 The Capital Budgeting Problem 198
6.3 Set Covering 202
6.4 Set Packing 205
6.5 Set Partitioning 208
6.6 Playoff Scheduling 211
6.7 The Algorithm for Solving Integer Programs 215

7 Integer Programming: Logical Constraints 227
7.1 Simple Logical Constraints: Exclusivity 229
7.2 Linking Constraints: The Fixed Cost Problem 231
7.3 Linking Constraints: The Threshold Level Problem 237
7.4 Linking Constraints: The Facility Location Model 238
7.5 Disjunctive Constraints: The Machine-Sequencing Problem 246
7.6 Tour Constraints: The Traveling Salesperson Problem 251

8 Nonlinear Programming 270
8.1 One-Variable Models 271
8.2 Local Optima and the Search for an Optimum 277
8.3 Two-Variable Models 280
8.4 Nonlinear Models with Constraints 285
8.5 Linearizations 293

9 Heuristic Solutions with the Evolutionary Solver 307
9.1 Features of the Evolutionary Solver 308
9.2 An Illustrative Example: Nonlinear Regression 309
9.3 The Machine-Sequencing Problem Revisited 317
9.4 The Traveling Salesperson Problem Revisited 319
9.5 Budget Allocation 322
9.6 Two-Dimensional Location 324
9.7 Line Balancing 327
9.8 Group Assignment 331

Appendices

1 Supplemental Files and Software 348
A1.1 Supplemental Microsoft Office Excel Files 348
A1.2 Analytic Solver Platform for Education Software 348
A1.3 Opensolver Software 349

2 Graphical Methods for Linear Programming 350
A2.1 An Example 350
A2.2 Generalities 355

3 The Simplex Method 357
A3.1 An Example 357
A3.2 Variations of the Algorithm 362

Index 366