优化建模与电子表格：肯尼斯·R·贝克尔第二版

需积分: 9 36 浏览量更新于2024-07-18 收藏 11.14MB PDF 举报

"Kenneth R. Baker的《Optimization Modeling with Spreadsheets》第二版，PDF格式，由John Wiley & Sons, Inc.于2011年出版。这本书主要探讨了如何利用电子表格进行优化建模，是Tuck School of Business at Dartmouth College的作者Kenneth R. Baker的作品。" 《Optimization Modeling with Spreadsheets》第二版是一本深入浅出的指南，专为那些希望通过电子表格工具（如Microsoft Excel）解决实际优化问题的读者设计。该书涵盖了广泛的主题，包括线性规划、整数编程、动态规划以及非线性优化等基本概念。这些方法在决策分析、运营管理、财务规划、项目管理等多个领域有着广泛应用。书中首先介绍了优化的基本原理，包括目标函数和约束条件的设置，以及如何构建有效的数学模型来描述复杂的问题。接着，它详细解释了如何在电子表格环境中设定这些模型，并利用内置的求解器（如Excel的Solver）进行求解。作者通过大量实例和练习，帮助读者掌握模型建立和求解的过程，强调实践操作和问题解决能力的培养。此外，书中还涵盖了敏感性分析、参数估计、风险分析和多目标优化等内容，这些都是在实际应用中至关重要的考虑因素。读者将学习如何处理不确定性和不完整信息，以及如何在各种约束条件下寻找最佳决策。对于那些对运筹学和优化技术感兴趣的学生、教师以及专业人士来说，这本书提供了一个实用且易于理解的平台，将理论知识与实际操作相结合。同时，书中包含的案例研究和练习有助于巩固学习，提升读者解决实际优化问题的能力。《Optimization Modeling with Spreadsheets》第二版是优化领域的一本经典教材，它不仅提供了丰富的理论知识，还通过电子表格工具使抽象的优化模型变得直观易用，是学习和应用优化技术的理想资源。

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页未读，继续阅读

tangmiaomiaowu

粉丝: 1
资源: 7

优化建模与电子表格：肯尼斯·R·贝克尔第二版

particle-swarm-optimiztion.rar_swarm

texttohtml

综合能源系统自由度分析相关数据

PSOplane_matlab_

WinAVR+AVR Studio 新手入门必读.docx

unitcom.rar_commitment_old6bd_unit commitment_whoseriy

matlab下的MVO飞蛾优化算法

IMG_20241018_191757.jpg

ECharts饼图-饼图纹理.rar

《ESP32从0到1》收官篇：wifi版温湿度蓝牙网关 源码

最新资源

《ESP32从0到1》收官篇：wifi版温湿度蓝牙网关源码