Optimization Modeling with Spreadsheets, 3rd Edition

Optimization Modeling with Spreadsheets, 3rd Edition

by Kenneth R. Baker
Released July 2015
Publisher(s): Wiley
ISBN: 9781118937693

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

An accessible introduction to optimization analysis using spreadsheets

Updated and revised, Optimization Modeling with Spreadsheets, Third Edition emphasizes model building skills in optimization analysis. By emphasizing both spreadsheet modeling and optimization tools in the freely available Microsoft Office Excel Solver, the book illustrates how to find solutions to real-world optimization problems without needing additional specialized software.

The Third Edition includes many practical applications of optimization models as well as a systematic framework that illuminates the common structures found in many successful models. With focused coverage on linear programming, nonlinear programming, integer programming, and heuristic programming, Optimization Modeling with Spreadsheets, Third Edition features:

  • An emphasis on model building using Excel Solver as well as appendices with additional instructions on more advanced packages such as Analytic Solver Platform and OpenSolver

  • Additional space devoted to formulation principles and model building as opposed to algorithms

  • New end-of-chapter homework exercises specifically for novice model builders

  • Presentation of the Sensitivity Toolkit for sensitivity analysis with Excel Solver

  • Classification of problem types to help readers see the broader possibilities for application

  • Specific chapters devoted to network models and data envelopment analysis

  • A companion website with interactive spreadsheets and supplementary homework exercises for additional practice

    • Optimization Modeling with Spreadsheets, Third Edition is an excellent textbook for upper-undergraduate and graduate-level courses that include deterministic models, optimization, spreadsheet modeling, quantitative methods, engineering management, engineering modeling, operations research, and management science. The book is an ideal reference for readers wishing to advance their knowledge of Excel and modeling and is also a useful guide for MBA students and modeling practitioners in business and non-profit sectors interested in spreadsheet optimization.

    Table of contents

    1. COVER
    2. TITLE PAGE
    3. PREFACE
      1. WHY MODEL BUILDING?
      2. WHY SPREADSHEETS?
      3. WHAT’S SPECIAL?
      4. WHAT’S NEW?
      5. THE AUDIENCE
      6. ACKNOWLEDGMENTS
    4. 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
    5. 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
    6. 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
    7. 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
    8. 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 REFERENCE SETS AND HCUs
      6. 5.6 ASSUMPTIONS AND LIMITATIONS OF DEA
      7. SUMMARY
      8. EXERCISES
    9. 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 THE ALGORITHM FOR SOLVING INTEGER PROGRAMS
      8. SUMMARY
      9. EXERCISES
    10. 7 INTEGER PROGRAMMING: LOGICAL CONSTRAINTS
      1. 7.1 SIMPLE LOGICAL CONSTRAINTS: EXCLUSIVITY
      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 CONSTRAINTS: THE TRAVELING SALESPERSON PROBLEM
      7. SUMMARY
      8. EXERCISES
    11. 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
    12. 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 BUDGET ALLOCATION
      6. 9.6 TWO-DIMENSIONAL LOCATION
      7. 9.7 LINE BALANCING
      8. 9.8 GROUP ASSIGNMENT
      9. SUMMARY
      10. EXERCISES
    13. Appendix 1: SUPPLEMENTAL FILES AND SOFTWARE
      1. A1.1 SUPPLEMENTAL Microsoft® Office Excel® FILES
      2. A1.2 ANALYTIC SOLVER PLATFORM FOR EDUCATION SOFTWARE
      3. A1.3 OPENSOLVER SOFTWARE
    14. Appendix 2: GRAPHICAL METHODS FOR LINEAR PROGRAMMING
      1. A2.1 AN EXAMPLE
      2. A2.2 GENERALITIES
    15. Appendix 3: THE SIMPLEX METHOD
      1. A3.1 AN EXAMPLE
      2. A3.2 VARIATIONS OF THE ALGORITHM
      3. REFERENCES
    16. INDEX
    17. END USER LICENSE AGREEMENT

    Product information

    • Title: Optimization Modeling with Spreadsheets, 3rd Edition
    • Author(s): Kenneth R. Baker
    • Release date: July 2015
    • Publisher(s): Wiley
    • ISBN: 9781118937693