Optimization Modeling with Spreadsheets

  • ISBN13:


  • ISBN10:


  • Edition: 2nd
  • Format: Hardcover
  • Copyright: 2011-05-24
  • Publisher: Wiley
  • Purchase Benefits
  • Free Shipping On Orders Over $35!
    Your order must be $35 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $129.00


This introductory book on optimization (mathematical programming) includes coverage on linear programming, nonlinear programming, integer programming and heuristic programming; as well as an emphasis on model building using Excel and Solver. The emphasis on model building (rather than algorithms) is one of the features that makes this book distinctive. Most books devote more space to algorithmic details than to formulation principles. These days, however, it is not necessary to know a great deal about algorithms in order to apply optimization tools, especially when relying on the spreadsheet as a solution platform. The emphasis on spreadsheets is another feature that makes this book distinctive. Few books devoted to optimization pay much attention to spreadsheet implementation of optimization principles, and most books that emphasize model building ignore spreadsheets entirely. Thus, someone looking for a spreadsheet-based treatment would otherwise need to use a book that was designed for some other purpose, like a survey of management science topics, rather than one devoted to optimization. The model building emphasis derives from an attempt to be realistic about what readers need most when learning about optimization. At an introductory level, the most practical and motivating theme is the wide applicability of optimization tools. To apply optimization effectively, readers needs more than a brief exposure to a series of numerical examples, which is the way that most mathematical programming books treat applications. With a systematic modeling emphasis, readers can begin to see the basic structures that appear in optimization models and as a result, develop an appreciation for potential applications well beyond the examples in the book. Formulating optimization models is both an art and a science, and this book pays attention to both. The art can be refined with practice, especially supervised practice, just the way a student would learn sculpture or painting. The science is reflected in the structure that organizes the topics in this book. For example, there are several distinct problem types that lend themselves to linear programming formulations, and it makes sense to study these types systematically. In that spirit, the book builds a library of templates against which new problems can be compared. Analogous structures are developed for the presentation of other topics as well.

Author Biography

Kenneth R. Baker, PHD, is Nathaniel Leverone Professor of Management at the Tuck School of Business and Adjunct Professor of Engineering at Dartmouth College. A Fellow of the Institute for Operations Research and the Management Sciences (INFORMS), Dr. Baker has published extensively in his areas of research interest, which include mathematical modeling, spreadsheet engineering, and scheduling. He is the coauthor of Principles of Sequencing and Scheduling and Management Science: The Art of Modeling with Spreadsheets, Third Edition, both published by Wiley.

Table of Contents

Prefacep. xi
Introduction to Spreadsheet Models for Optimizationp. 1
Elements of a Modelp. 2
Spreadsheet Modelsp. 4
A Hierarchy for Analysisp. 7
Optimization Softwarep. 8
Using Solverp. 10
Summaryp. 17
Exercisesp. 18
Referencesp. 20
Linear Programming: Allocation, Covering, and Blending Modelsp. 21
Linear Modelsp. 22
Linear Constraintsp. 24
Formulationp. 25
Layoutp. 26
Resultsp. 28
Allocation Modelsp. 29
The Product Mix Problemp. 35
Covering Modelsp. 39
The Staff-Scheduling Problemp. 43
Blending Modelsp. 47
Modeling Errors in Linear Programmingp. 53
Exceptionsp. 53
Debuggingp. 55
Logicp. 56
Summaryp. 57
Exercisesp. 57
Case: JetGreenp. 68
Linear Programming: Network Modelsp. 71
The Transportation Modelp. 72
The Assignment Modelp. 77
The Transshipment Modelp. 81
Features of Special Network Modelsp. 85
Building Network Models with Balance Equationsp. 86
General Network Models with Yieldsp. 91
Models with Yield Lossesp. 91
Models with Yield Gainsp. 93
General Network Models with Transformed Flowsp. 98
Summaryp. 103
Exercisesp. 103
Case: Casey's Famous Roast Beefp. 113
Case: Hollingsworth Paper Companyp. 114
Production and Distribution Facilitiesp. 115
Patterns of Distributionp. 115
Expansion Proposalsp. 116
Sensitivity Analysis in Linear Programsp. 119
Parameter Analysis in the Transportation Examplep. 120
Parameter Analysis in the Allocation Examplep. 127
The Sensitivity Report and the Transportation Examplep. 135
The Sensitivity Report and the Allocation Examplep. 138
Degeneracy and Alternative Optimap. 140
Patterns in Linear Programming Solutionsp. 144
The Transportation Modelp. 145
The Product Portfolio Modelp. 149
The Investment Modelp. 152
The Allocation Modelp. 154
The Refinery Modelp. 155
Summaryp. 159
Exercisesp. 160
Case: Cox Cable and Wire Companyp. 171
Backgroundp. 171
The Contractp. 172
The Analysisp. 173
Linear Programming: Data Envelopment Analysisp. 175
A Graphical Perspective on DEAp. 177
An Algebraic Perspective on DEAp. 181
A Spreadsheet Model for DEAp. 183
Indexingp. 188
Finding Reference Sets and HCUsp. 190
Assumptions and Limitations of DEAp. 193
Summaryp. 196
Exercisesp. 196
Case: Branch Performance at Nashville National Bankp. 205
Branch Growth at Nashville National Bankp. 205
Assessing Branch Productivityp. 206
Branch Managers Revoltp. 206
Measuring Branches: Available Techniquesp. 207
The DEA Studyp. 207
Integer Programming: Binary Choice Modelsp. 211
Using Solver with Integer Requirementsp. 213
The Capital Budgeting Problemp. 217
Set Coveringp. 221
Set Packingp. 224
Set Partitioningp. 227
Playoff Schedulingp. 229
Solving a Large-Scale Set Partitioning Problemp. 234
The Algorithm for Solving Integer Programsp. 237
Summaryp. 243
Exercisesp. 243
Case: Motel Location for Nature's Innp. 249
Integer Programming: Logical Constraintsp. 251
Simple Logical Constraints: Exclusivity and Contingencyp. 253
Linking Constraints: The Fixed Cost Problemp. 255
Linking Constraints: The Threshold Level Problemp. 260
Linking Constraints: The Facility Location Modelp. 261
Capacitated Versionp. 263
Uncapacilated Versionp. 267
Disjunctive Constraints: The Machine Sequencing Problemp. 270
Tour and Subset Constraints: The Traveling Salesperson Problemp. 274
Summaryp. 282
Exercisesp. 283
Case: Hornby Products Companyp. 291
Historyp. 291
Alternativesp. 293
Nonlinear Programmingp. 297
One-variable Modelsp. 298
An Inventory Examplep. 300
A Quantity Discount Examplep. 302
Local Optima and the Search for an Optimump. 304
Two-Variable Modelsp. 307
Curve Fittingp. 307
Two-dimensional Locationp. 310
Nonlinear Models with Constraintsp. 312
A Pricing Examplep. 313
Sensitivity Analysis for Nonlinear Programsp. 315
The Portfolio Optimization Modelp. 316
Linearizationsp. 320
Linearizing the Maximump. 320
Linearizing the Absolute Valuep. 324
Summaryp. 327
Exercisesp. 328
Case: Delhi Foodsp. 335
Heuristic Solutions with the Evolutionary Solverp. 337
Features of the Evolutionary Solverp. 338
An Illustrative Example: Nonlinear Regressionp. 339
The Machine-Sequencing Problem Revisitedp. 346
The Traveling Salesperson Problem Revisitedp. 349
Two-dimensional Locationp. 352
Line Balancingp. 354
Group Assignmentp. 358
Summaryp. 362
Exercisesp. 362
Case: Colgate Wave (Abridged)p. 370
Introductionp. 370
The Studyp. 370
Case Appendix: Market Share Simulation Model (CoIgate.xls)p. 373
Datap. 373
Calculationsp. 373
Simulationp. 374
Optimization Software and Supplemental Filesp. 375
Risk Solver Platformp. 375
Supplemental Excel Filesp. 376
Graphical Methods in Linear Programmingp. 377
An Examplep. 377
Generalitiesp. 382
The Simplex Methodp. 385
An Examplep. 385
Variations of the Algorithmp. 390
Referencesp. 393
Stochastic Programmingp. 395
One-Stage Decisions with Uncertaintyp. 395
Two-Stage Decisions with Uncertaintyp. 399
Using Solverp. 402
Indexp. 407
Table of Contents provided by Ingram. All Rights Reserved.

Rewards Program

Write a Review