Optimization Modeling with Spreadsheets

Kenneth R. Baker

• Publisher: Wiley
• Year: 2015
• ISBN:
  9781118937730
• Edition: 3
• Topic: Business
• Subtopic: Management
• Index: Business

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.

1
INTRODUCTION TO SPREADSHEET MODELS FOR OPTIMIZATION

This is a book about optimization with an emphasis on building models and using spreadsheets. Each facet of this theme—models, spreadsheets, and optimization—has a role in defining the emphasis of our coverage.

A model is a simplified representation of a situation or problem. Models attempt to capture the essential features of a complicated situation so that it can be studied and understood more completely. In the worlds of business, engineering, and science, models aim to improve our understanding of practical situations. Models can be built with tangible materials, or words, or mathematical symbols and expressions. A mathematical model is a model that is constructed—and also analyzed—using mathematics. In this book, we focus on mathematical models. Moreover, we work with decision models, or models that contain representations of decisions. The term also refers to models that support decision-making activities.

A spreadsheet is a row-and-column layout of text, numerical data, and logical information. The spreadsheet version of a model contains the model’s elements, linked together by specific logical information. Electronic spreadsheets, like those built using Microsoft^® Office Excel^®, have become familiar tools in the business, engineering, and scientific worlds. Spreadsheets are relatively easy to understand, and people often rely on spreadsheets to communicate their analyses. In this book, we focus on the use of spreadsheets to represent and analyze mathematical models.

This text is written for an audience that already has some familiarity with Excel. Our coverage assumes a level of facility with Excel comparable to a beginner’s level. Someone who has used other people’s spreadsheets and built simple spreadsheets for some purpose—either personal or organizational—has probably developed this skill level. Box 1.1 describes the Excel skill level assumed. Readers without this level of background are encouraged to first work through some introductory materials, such as the books by McFedries (1) and Walkenbach (2).

---

BOX 1.1 Excel Skills Assumed as Background for This Book

Navigating in workbooks, worksheets, and windows

Using the cursor to select cells, rows, columns, and noncontiguous cell ranges

Entering text and data; copying and pasting; filling down or across

Formatting cells (number display, alignment, font, border, and protection)

Editing cells (using the formula bar and cell edit capability [F2])

Entering formulas and using the function wizard

Using relative and absolute addresses

Using range names

Creating charts and graphs

---

Optimization is the process of finding the best values of the variables for a particular criterion or, in our context, the best decisions for a particular measure of performance. The elements of an optimization problem are a set of decisions, a criterion, and perhaps a set of required conditions, or constraints, that the decisions must satisfy. These elements lend themselves to description in a mathematical model. The term optimization sometimes refers specifically to a procedure that is implemented by software. However, in this book, we expand that perspective to include the model-building process as well as the process of finding the best decisions.

Not all mathematical models are optimization models. Some models merely describe the logical relationship between inputs and outputs. Optimization models are a special kind of model in which the purpose is to find the best value of a particular output measure and the choices that produce it. Optimization problems abound in the real world, and if we’re at all ambitious or curious, we often find ourselves seeking solutions to those problems. Business firms are very interested in optimization because making good decisions helps a firm run efficiently, perform profitably, and compete effectively. In this book, we focus on optimization problems expressed in the form of spreadsheet models and solved using a spreadsheet-based approach.

1.1 ELEMENTS OF A MODEL

To restate our premise, we are interested in mathematical models. Specifically, we are interested in two forms—algebraic and spreadsheet models. In the former, we use algebraic notation to represent elements and relationships, and in the latter, we use spreadsheet entries and structure. For example, in an algebraic statement, we might use the variable x to represent a quantitative decision, and we might use some function f(x) to represent the measure of performance that results from choosing decision x. Then, we might adopt the letter z to represent a criterion for decision making and construct the equation z = f(x) to guide the choice of a decision. Algebra is the basic language of analysis largely because it is precise and compact.

As an introductory modeling example, let’s consider the price decision in the scenario of Example 1.1.

---

EXAMPLE 1.1 Price, Demand, and Profit

Our firm’s production department has carried out a cost accounting study and found that the unit cost for one of its main products is $40. Meanwhile, the marketing department has estimated the relationship between price and sales volume (the so-called d...