Optimization Modeling with Spreadsheets, Second Edition

Optimization Modeling with Spreadsheets, Second Edition

by Kenneth R. Baker
Released May 2011
Publisher(s): Wiley
ISBN: 9780470928639

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Reflects the latest applied research and features state-of-the-art software for building and solving spreadsheet optimization models

Thoroughly updated to reflect the latest topical and technical advances in the field, Optimization Modeling with Spreadsheets, Second Edition continues to focus on solving real-world optimization problems through the creation of mathematical models and the use of spreadsheets to represent and analyze those models. Developed and extensively classroom-tested by the author, the book features a systematic approach that equips readers with the skills to apply optimization tools effectively without the need to rely on specialized algorithms.

This new edition uses the powerful software package Risk Solver Platform (RSP) for optimization, including its Evolutionary Solver, which employs many recently developed ideas for heuristic programming. The author provides expanded coverage of integer programming and discusses linear and nonlinear programming using a systematic approach that emphasizes the use of spreadsheet-based optimization tools. The Second Edition also features:

  • Classifications for the various problem types, providing the reader with a broad framework for building and recognizing optimization models

  • Network models that allow for a more general form of mass balance

  • A systematic introduction to Data Envelopment Analysis (DEA)

  • The identification of qualitative patterns in order to meaningfully interpret linear programming solutions

  • An introduction to stochastic programming and the use of RSP to solve problems of this type

    • Additional examples, exercises, and cases have been included throughout, allowing readers to test their comprehension of the material. In addition, a related website features Microsoft Office Excel files to accompany the figures and data sets in the book.

    With its accessible and comprehensive presentation, Optimization Modeling with Spreadsheets, Second Edition is an excellent book for courses on deterministic models, optimization, and spreadsheet modeling at the upper-undergraduate and graduate levels. The book can also serve as a reference for researchers, practitioners, and consultants working in business, engineering, operations research, and management science.

    Table of contents

    1. Cover
    2. Title page
    3. Copyright page
    4. Preface
      1. Why Model Building?
      2. Why Spreadsheets?
      3. What’s Special?
      4. The Audience
      5. Acknowledgements
    5. Chapter 1: Introduction to Spreadsheet Models for Optimization
      1. 1.1 Elements of A Model
      2. 1.2 Spreadsheet Models
      3. 1.3 A Hierarchy for Analysis
      4. 1.4 Optimization Software
      5. 1.5 Using Solver
      6. Summary
      7. Exercises
      8. References
    6. Chapter 2: Linear Programming: Allocation, Covering, and Blending Models
      1. 2.1 Linear Models
      2. 2.2 Allocation Models
      3. 2.3 Covering Models
      4. 2.4 Blending Models
      5. 2.5 Modeling Errors in Linear Programming
      6. Summary
      7. Exercises
    7. Chapter 3: Linear Programming: Network Models
      1. 3.1 The Transportation Model
      2. 3.2 The Assignment Model
      3. 3.3 The Transshipment Model
      4. 3.4 Features of Special Network Models
      5. 3.5 Building Network Models with Balance Equations
      6. 3.6 General Network Models with Yields
      7. 3.7 General Network Models with Transformed Flows
      8. Summary
      9. Exercises
    8. Chapter 4: Sensitivity Analysis in Linear Programs
      1. 4.1 Parameter Analysis in The Transportation Example
      2. 4.2 Parameter Analysis in The Allocation Example
      3. 4.3 The Sensitivity Report And The Transportation Example
      4. 4.4 The Sensitivity Report and The Allocation Example
      5. 4.5 Degeneracy and Alternative Optima
      6. 4.6 Patterns in Linear Programming Solutions
      7. Summary
      8. Exercises
    9. Chapter 5: Linear Programming: Data Envelopment Analysis
      1. 5.1 A Graphical Perspective on Dea
      2. 5.2 An Algebraic Perspective on Dea
      3. 5.3 A Spreadsheet Model for Dea
      4. 5.4 Indexing
      5. 5.5 Finding Reference Sets and Hcus
      6. 5.6 Assumptions and Limitations of Dea
      7. Summary
      8. Exercises
    10. Chapter 6: Integer Programming: Binary Choice Models
      1. 6.1 Using Solver with Integer Requirements
      2. 6.2 The Capital Budgeting Problem
      3. 6.3 Set Covering
      4. 6.4 Set Packing
      5. 6.5 Set Partitioning
      6. 6.6 Playoff Scheduling
      7. 6.7 Solving A Large-Scale Set Partitioning Problem
      8. 6.8 The Algorithm for Solving Integer Programs
      9. Summary
      10. Exercises
    11. Chapter 7: Integer Programming: Logical Constraints
      1. 7.1 Simple Logical Constraints: Exclusivity and Contingency
      2. 7.2 Linking Constraints: The Fixed Cost Problem
      3. 7.3 Linking Constraints: The Threshold Level Problem
      4. 7.4 Linking Constraints: The Facility Location Model
      5. 7.5 Disjunctive Constraints: The Machine Sequencing Problem
      6. 7.6 Tour and Subset Constraints: The Traveling Salesperson Problem
      7. Summary
      8. Exercises
    12. Chapter 8: Nonlinear Programming
      1. 8.1 One-Variable Models
      2. 8.2 Local Optima and The Search for an Optimum
      3. 8.3 Two-Variable Models
      4. 8.4 Nonlinear Models with Constraints
      5. 8.5 Linearizations
      6. Summary
      7. Exercises
    13. Chapter 9: Heuristic Solutions with the Evolutionary Solver
      1. 9.1 Features of The Evolutionary Solver
      2. 9.2 An Illustrative Example: Nonlinear Regression
      3. 9.3 The Machine-Sequencing Problem Revisited
      4. 9.4 The Traveling Salesperson Problem Revisited
      5. 9.5 Two-Dimensional Location
      6. 9.6 Line Balancing
      7. 9.7 Group Assignment
      8. Summary
      9. Exercises
    14. Appendix 1: Optimization Software and Supplemental Files
      1. A1.1 Risk Solver Platform
      2. A1.2 Supplemental Excel Files
    15. Appendix 2: Graphical Methods in Linear Programming
      1. A2.1 An Example
      2. A2.2 Generalities
    16. Appendix 3: The Simplex Method
      1. A3.1 An Example
      2. A3.2 Variations of The Algorithm
      3. References
    17. Appendix 4: Stochastic Programming
      1. A4.1 One-Stage Decisions with Uncertainty
      2. A4.2 Two-Stage Decisions with Uncertainty
      3. A4.3 Using Solver
    18. Index

    Product information

    • Title: Optimization Modeling with Spreadsheets, Second Edition
    • Author(s): Kenneth R. Baker
    • Release date: May 2011
    • Publisher(s): Wiley
    • ISBN: 9780470928639