电子表格在优化建模中的应用（第二版）

需积分: 10 125 浏览量更新于2024-07-26 收藏 10.6MB PDF 举报

"《优化建模与电子表格》第二版是Kenneth R. Baker撰写的一本针对电子表格在优化建模中的应用教材。本书旨在帮助读者掌握如何利用Excel、Google Sheets或其他商业智能工具进行有效的决策支持和问题求解。作者肯尼斯·贝克（Kenneth R. Baker）来自达特茅斯学院塔克商学院，他以其深入浅出的方式讲解了优化理论在实践中的运用，包括线性规划、整数编程、动态规划等核心概念。在本书中，读者可以学习到以下关键知识点： 1. 优化理论基础：首先，作者会介绍优化的基本原理，如目标函数、约束条件和可行域，让读者理解优化问题的结构和解决策略。 2. 电子表格工具的运用：作者会详细展示如何在Excel环境中构建和编辑模型，包括设置单元格公式、数据输入、数据验证以及图形化表示。 3. 具体方法和技术：涉及了诸如线性规划法（LP）、单纯形法、内点法等优化算法，以及解决带有整数变量的整数规划（IP）问题的方法。 4. 动态规划与滚动规划：这部分内容展示了如何处理具有时间依赖性的决策问题，通过动态规划求解最优化策略。 5. 案例研究与实战演练：书中包含丰富的实际案例，引导读者将理论知识应用于各种业务场景，如生产计划、投资决策、物流管理等。 6. 高级主题：对于对优化有更高追求的读者，本书还可能涵盖模拟退火、遗传算法、神经网络优化等更复杂的技术。 7. 版权声明：最后，本书强调版权保护，告知读者关于复制、传播或使用材料的规定，确保尊重知识产权。《优化建模与电子表格》是一本实用的教程，适合学生、研究人员以及任何希望提升决策分析技能的专业人士。通过阅读这本书，读者不仅能够掌握优化建模的基本方法，还能提升利用电子表格进行高效解决问题的能力。"

Chapter 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 deﬁning the emphasis of our coverage.

A model is a simpliﬁed 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 math-

ematics. 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 speciﬁc logical information. Electronic spreadsheets, like those

built using Microsoft Excel

, have become familiar tools in the business, engineering,

and scientiﬁc 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 ﬁrst work through some introductory materials, such

as the books by McFedries (1) and Reding and Wermers (2).

Optimization is the process of ﬁnding the best values of the variables for a particu-

lar criterion or, in our context, the best decisions for a particular measure of perform-

ance. The elements of an optimization problem are a set of decisions, a criterion, and

Optimization Modeling with Spreadsheets, Second Edition. Kenneth R. Baker

# 2011 John Wiley & Sons, Inc. Published 2011 by John Wiley & Sons, Inc.

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 speciﬁcally to a procedure that is implemented by soft-

ware. However, in this book, we expand that perspective to include the model-building

process as well as the process of ﬁnding 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 ﬁnd 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 ﬁnd ourselves seeking

solutions to those problems. Business ﬁrms are very interested in optimization because

making good decisions helps a ﬁrm run efﬁciently, perform proﬁtably, 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. Speciﬁcally, 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 con-

struct 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.

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; ﬁlling 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.

2 Chapter 1 Introduction to Spreadsheet Models for Optimization

EXAMPLE 1.1

Price, Demand, and Proﬁt

Our ﬁrm’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 demand curve for the product) as

follows:

y = 800 − 5x (1.1)

where y represents quarterly demand and x represents the selling price per unit. We wish to deter-

mine a selling price for this product, given the information available. B

In Example 1.1, the decision is the unit price, and the consequence of that

decision is the level of demand. The demand curve in Equation 1.1 expresses the

relationship of demand and price in algebraic terms. Another equation expresses the

calculation of proﬁt contribution, by multiplying the demand y by the unit proﬁt con-

tribution (x – 40) on each item

z = (x − 40)y (1.2)

where z represents our product’s quarterly proﬁt contribution.

We can substitute Equation 1.1 into 1.2 if we want to write z algebraically as a

function of x alone. As a result, we can express the proﬁt contribution as

z = 1000x − 5x

− 32,000 (1.3)

This step embodies the algebraic principle that simpliﬁcation is always desirable. Here,

simpliﬁcation reduces the number of variables in the expression for proﬁt contribution.

Simpliﬁcation, however, is not necessarily a virtue when we use a spreadsheet model.

Example 1.1, simple as it is, has some important features. First, our model con-

tains three numerical inputs: 40 (the unit cost), –5 (the marginal effect of price on

demand) and 800 (the maximum demand). Numerical inputs such as these are

called parameters. In some models, parameters correspond to raw data, but in many

cases, parameters are summaries drawn from a more primitive data set. They may

also be estimates made by a knowledgeable party, forecasts derived from statistical

analyses, or predictions chosen to reﬂect a future scenario.

Our model also contains a decision—an unknown quantity yet to be determined.

In traditional algebraic formulations, unknowns are represented as variables.

Quantitative representations of decisions are therefore called decision variables.

The decision variable in our model is the unit price x.

Our model contains the equation that relates demand to price. We can think of

this relationship as part of the model’s logic. In that role, the demand curve prescribes

a relationship between two variables—price and demand—that must always hold.

Thus, in our model, the only admissible values of x and y are those that satisfy

Equation 1.1.

Finally, our model contains a calculation of quarterly proﬁt contribution, which is

the performance measure of interest and a quantity that we wish to maximize. This

output variable measures the consequence of selecting any particular price decision

1.1. Elements of a Model 3

in the model. In optimization models, we are concerned with maximizing or minimiz-

ing some measure of performance, expressed as a mathematical function, and we refer

to it as the objective function, or simply the objective.

1.2. SPREADSHEET MODELS

Algebra is an established language that works well for describing problems, but not

always for obtaining solutions. Algebraic solutions tend to occur in formulas, not num-

bers, but numbers most often represent decisions in the practical world. By contrast,

spreadsheets represent a practical language—one that works very effectively with

numbers. Like algebraic models, spreadsheets can be precise and compact, but there

are also complications that are unique to spreadsheets. For example, there is a differ-

ence between form and content in a spreadsheet. Two spreadsheets may look the same

in terms of layout and the numbers displayed on a computer screen, but the underlying

formulas in corresponding cells could differ. Because the information behind the dis-

play can be different even when two spreadsheets have the same on-screen appear-

ance, we can’t always tell the logical content from the form of the display. Another

complication is the lack of a single, well accepted way to build a spreadsheet represen-

tation of a given model. In an optimization model, we want to represent decision vari-

ables, an objective function, and constraints. However, that still leaves a lot of

ﬂexibility in choosing how the logic of a particular model is incorporated into a

spreadsheet. Such ﬂexibility would ordinarily be advantageous if the only use of a

spreadsheet were to help individuals solve problems. However, spreadsheets are per-

haps even more important as vehicles for communication. When we use spreadsheets

in this role, ﬂexibility can sometimes lead to confusion and disrupt the intended

communication.

We will try to mitigate these complications with some design guidelines. For

example, it is helpful to create separate modules in the spreadsheet for decision vari-

ables, objective function, and constraints. To the extent that we follow such guidelines,

we may lose some ﬂexibility in building a spreadsheet model. Moving the design pro-

cess toward standardization will, however, make the content of a spreadsheet more

understandable from its form, so differences between form and content become less

problematic.

With optimization, a spreadsheet model contains the analysis that ultimately pro-

vides decision support. For this reason, the spreadsheet model should be intelligible to

its users, not just to its developer. On some occasions, a spreadsheet might come into

routine use in an organization, even when the developer moves on. New analysts may

inherit the responsibilities associated with the model, so it is vital that they, too, under-

stand how the spreadsheet works. For that matter, the decision maker may also move

on. For the organization to retain the learning that has taken place, successive decision

makers must also understand the spreadsheet. In yet another scenario, the analyst

develops a model for one-time use but then discovers a need to reuse it several

months later in a different context. In such a situation, it’s important that the analyst

understands the original model, lest the passage of time obscure its purpose and

Chapter 1 Introduction to Spreadsheet Models for Optimization

剩余429页未读，继续阅读

u010034382

粉丝: 0
资源: 1

电子表格在优化建模中的应用（第二版）

Optimization Modeling with Lingo 6th

lingo教程Optimization Modeling with LINGO

AIMMS optimization modeling

Power system optimization modeling in GAMS

Pyomo—Optimization Modeling in Python

MATLAB toolbox for optimization modeling.zip

Pyomo - Optimization Modeling in Python Pyomo - Python中的优化建模

Bidirectional DC-DC Power Converter Design Optimization Modeling and Control.pdf

Antenna and EM Modeling with MATLAB

Whale Optimization Algorithm with Simulated Annealing

最新资源