Optimization Modeling with Spreadsheets (eBook)

Kenneth R. Baker (Autor)

eBook Download: EPUB

2015 | 3. Auflage
John Wiley & Sons (Verlag)
978-1-118-93773-0 (ISBN)

Infos

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.

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 also coauthor of Principles of Sequencing and Scheduling and Management Science: The Art of Modeling with Spreadsheets, Fourth Edition, both published by Wiley.

Inhalt

Chapter 1 Introduction to Spreadsheet Models for Optimization

1.1 Elements of a Model

1.2 Spreadsheet Models

1.3 A Hierarchy for Analysis

1.4 Optimization Software

1.5 Using Solver

Chapter 2 Linear Programming: Allocation, Covering and Blending Models

2.1 Linear Models

2.2 Allocation Models

2.3 Covering Models

2.4 Blending Models

2.5 Modeling Errors in Linear Programming

Chapter 3 Linear Programming: Network Models

3.1 The Transportation Model

3.2 The Assignment Model

3.3 The Transshipment Model

3.4 Features of Special Network Models

3.5 Building Network Models with Balance Equations

3.6 General Network Models with Yields

3.7 General Network Models with Transformed Flows

Chapter 4 Sensitivity Analysis in Linear Programs

4.1 Parameter Analysis in the Transportation Example

4.2 Parameter Analysis in the Allocation Example

4.3 The Sensitivity Report and the Transportation Example

4.4 The Sensitivity Report and the Allocation Example

4.5 Degeneracy and Alternative Optima

4.6 Patterns in Linear Programming Solutions

Chapter 5 Linear Programming: Data Envelopment Analysis

5.1 A Graphical Perspective on DEA

5.2 An Algebraic Perspective on DEA

5.3 A Spreadsheet Model for DEA

5.4 Indexing

5.5 Finding Reference Sets and HCUs

5.6 Assumptions and Limitations of DEA

Chapter 6 Integer Programming: Binary Choice Models

6.1 Using Solver with Integer Requirements

6.2 The Capital Budgeting Problem

6.3 Set Covering

6.4 Set Packing

6.5 Set Partitioning

6.6 Playoff Scheduling

6.7 The Algorithm for Solving Integer Programs

Chapter 7 Integer Programming: Logical Constraints

7.1 Simple Logical Constraints: Contingency and Exclusivity

7.2 Linking Constraints: The Fixed Cost Problem

7.3 Linking Constraints: The Threshold Level Problem

7.4 Linking Constraints: The Facility Location Model

7.5 Disjunctive Constraints: The Machine Sequencing Problem

7.6 Tour and Subset Constraints: The Traveling Salesperson Problem

Chapter 8 Nonlinear Programming

8.1 One-Variable Models

8.2 Local Optima and the Search for an Optimum

8.3 Two-Variable Models

8.4 Nonlinear Models with Constraints

8.5 Linearizations

Chapter 9 Heuristic Solutions with the Evolutionary Solver

9.1 Features of the Evolutionary Solver

9.2 An Illustrative Example: Nonlinear Regression

9.3 The Machine-Sequencing Problem Revisited

9.4 The Traveling Salesperson Problem Revisited

9.5 Budget Allocation

9.6 Two-Dimensional Location

9.7 Line Balancing

9.8 Group Assignment

Appendices

1. Supplemental Files and Software

2. Graphical Methods for Linear Programming

3. The Simplex Method

4. Using Analytic Solver Platform for Education (Online)

5. Using OpenSolver (Online)

Textauszug

PREFACE

This is an introductory textbook on optimization—that is, on mathematical programming—intended for undergraduates and graduate students in management or in engineering. The principal coverage includes linear programming, nonlinear programming, integer programming, and heuristic programming; and the emphasis is on model building using Microsoft® Office Excel® and Solver.

The emphasis on model building (rather than algorithms) is one of the features that make this book distinctive. Most textbooks 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 textbooks devoted to optimization pay much attention to spreadsheet implementation of optimization principles, and many books that emphasize model building ignore spreadsheets entirely. Thus, someone looking for a spreadsheet-based treatment would otherwise have to use a textbook that was designed for some other purpose, such as a survey of management science topics, rather than one devoted to optimization.

WHY MODEL BUILDING?

The model building emphasis derives from an attempt to be realistic about what management and engineering students 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, the student 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, the student 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 text.

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.

WHY SPREADSHEETS?

Now that optimization tools have been made available with spreadsheets (i.e., with Excel), every spreadsheet user is potentially a practitioner of optimization techniques. No longer do practitioners of optimization constitute an elite, highly trained group of quantitative specialists who are well versed in computer software. Now, anyone who builds a spreadsheet model can call on optimization techniques and can do so without any need to learn about specialized software. The basic optimization tool, in the form of Excel’s Standard Solver, is now as readily available as the spell-checker. So why not raise modeling ability up to the level of software access? Let’s not pretend that most users of optimization tools will be inclined to shop around for algebraic modeling languages and industrial-strength “solvers” if they want to produce numbers. More likely, they will be drawn to Excel.

Students using this book can take advantage of even more powerful software packages (Analytic Solver Platform and OpenSolver) by using the material in the online appendices. For the instructor who wants students to be working on one of these platforms, the book provides sufficient information to get started and to learn the user interface.

WHAT’S SPECIAL?

Mathematical programming techniques have been invented and applied for more than half a century, so by now they represent a relatively mature area of applied mathematics. There is not much new that can be said in an introductory textbook regarding the underlying concepts. The innovations in this book can instead be found in the delivery and elaboration of certain topics, making them accessible and understandable to the novice. The most distinctive of these features are as follows:

• The major topics are not illustrated merely with a series of numerical examples. Instead, the chapters introduce a classification for the problem types. An early example is the organization of basic linear programming models in Chapter 2 along the lines of allocation, covering, and blending models. This classification strategy, which extends throughout the book, helps the student to see beyond the particular examples to the breadth of possible applications.
• Network models are a special case of linear programming models. If they are singled out for special treatment at all in optimization books, they are defined by a strict requirement for mass balance. Here, in Chapter 3, network models are presented in a broader framework, which allows for a more general form of mass balance, thereby extending the reader’s capability for recognizing and analyzing network problems.
• Interest has been growing in data envelopment analysis (DEA), a special kind of linear programming application. Although some books illustrate DEA with a single example, this book provides a systematic introduction to the topic by providing a patient, comprehensive treatment in Chapter 5.
• Analysis of an optimization problem does not end when the computer displays the numbers in an optimal solution. Finding a solution must be followed with a meaningful interpretation of the results, especially if the optimization model was built to serve a client. An important framework for interpreting linear programming solutions is the identification of patterns, which is discussed in detail in Chapter 4.
• The topic of heuristic programming has developed somewhat outside the field of optimization. Although various specialized heuristic approaches have been developed, generic software has seldom been available. Now, however, the advent of the evolutionary solver brings heuristic programming alongside linear and nonlinear programming as a generic software tool for pursuing optimal decisions. The evolutionary solver is covered in Chapter 9.

Beyond these specific innovations, as this book goes to print, there is no optimization textbook exclusively devoted to model building rather than algorithms that relies on the spreadsheet platform. The reliance on spreadsheets and on a model building emphasis is the most effective way to bring optimization capability to the many users of Excel.

WHAT’S NEW?

The Third Edition largely follows the topic coverage of the previous edition, with one important change. In the new edition, the presentation is organized around the use of Excel’s Solver. More advanced software, such as Analytic Solver Platform or OpenSolver, might be preferred by some instructors, so the Third Edition provides support for both of these in online appendices. However, students need access to no software other than Excel in order to follow the coverage in the book’s nine chapters.

The set of homework exercises has been expanded in the Third Edition. Each chapter now contains about ten homework exercises, most of which appeared in the previous edition. In addition, a supplementary set of homework exercises can be found online for instructors who are looking for a broader set of exercises or for students who want additional practice.

THE AUDIENCE

This book is aimed at management students and secondarily to engineering students. In business curricula, a course focused on optimization is viable in two situations. If there is no required introduction to management science at all, then the treatment of management science at the elective level is probably best done with specialized courses on deterministic and probabilistic models. This book is an ideal text for a first course dedicated to deterministic models. If instead there is a required introduction to management science, chances are that the coverage of optimization glides by so quickly that even the motivated student is left wanting more detail, more concepts, and more practice. This book is also well suited to a second-level course that delves specifically into mathematical programming applications.

In engineering curricula, it is still typical to find a full course on optimization, usually as the first course on (deterministic) modeling. Even in this setting, though, traditional textbooks tend to leave it to the student to seek out spreadsheet approaches to the topic, while covering the theory and perhaps encouraging students to write code for algorithms. This book can capture the energies of students by covering what they would be spending most of their time doing in the real world—building and solving optimization problems on spreadsheets.

This book has been developed around the syllabi of two courses at Dartmouth College that have been delivered for several years. One course is a second-year elective for MBA students who have had a brief, previous exposure to optimization during a required core course that surveyed other analytic topics. A second course is a required course for engineering management students in a graduate program at the interface between business and engineering. These students have had no formal exposure to spreadsheet modeling,...

Produkt-Details

• Erscheint lt. Verlag: 15.6.2015
• Sprache: englisch
• Themenwelt: Mathematik / Informatik ► Informatik ► Theorie / Studium
• Themenwelt: Technik ► Maschinenbau
• Themenwelt: Wirtschaft ► Betriebswirtschaft / Management ► Unternehmensführung / Management
• Schlagworte: Betriebswirtschaft u. Operationsforschung • Business & Management • deterministic models • Electrical & Electronics Engineering • Elektrotechnik u. Elektronik • Engineering • Excel solver • Industrial Engineering • Industrial Engineering / Project Management • Industrielle Verfahrenstechnik • Intro • Management Science • Management Science/Operational Research • Mathematical Modeling • Mathematical Programming • Modeling • Optimization • Program & Project Management • Programm- u. Projektmanagement • Projektmanagement i. d. Industriellen Verfahrenstechnik • Research • research tools • Spreadsheet Engineering • spreadsheet optimization • Wirtschaft u. Management
• ISBN-10: 1-118-93773-2 / 1118937732
• ISBN-13: 978-1-118-93773-0 / 9781118937730