第三部分 Spreadsheet建模与求解
Spreadsheet方法是近年来美国各大学乃至企业推广的一种管理科学教学与应用的有效方法。Spreadsheet提供了一种描述问题、处理数据、建立模型与求解的有效工具，使得管理科学的理论和方法易于被理解与掌握，大大推动了管理科学方法与技术在企业中的实际应用。
Spreadsheet是在Excel或者Lotus 1-2-3等其他背景下将所需求解的问题进行描述与展开，然后建立数学模型，并使用Excel（或者Lotus 1-2-3）的命令和功能进行预测、决策、模拟、优化等运算与分析。Excel（或者Lotus 1-2-3）的工作表用作描述问题与建立模型时，就被称做Spreadsheet。
本实验指导书旨在帮助学生在运筹学课程中，学习如何运用Excel对复杂的实际系统进行描述与建模，并用计算机求解。由于避免了大量繁琐的数学公式，使得运筹学的理论方法简明直观，容易理解与应用，因此，掌握它有利于运筹学理论的学习，也特别有利于那些注重应用的企业管理人员的学习，为企业决策人员与管理人员掌握与应用运筹学理论提供一个有益的工具。
第一章 Spreadsheet建模
第一节 模型的概念与建立
一、 模型的概念
用管理科学方法求解问题，一般需要建立模型，用定量化方法来描述与分析所研究的问题。模型是对现实系统或情景的一种描述，同时又是对现实系统的一种抽象。
二、建立模型的一般步骤
① 定义问题。定义问题包括确定系统的目标和边界。 ② 调查研究，收集数据。 ③ 建立数学模型。
④ 模型的验证。为检验模型的有效性，需在使用前进行模型的验证。一般可用模型预测近
期变量值，并将该预测值与实际值相比较，以确定模型的有效性。 ⑤ 选择可行方案。
⑥ 模型运行求解，提出推荐的方案。 ⑦ 履行所推荐的方案，并进行评价。
三、模型的特点
① 模型通常是现实的抽象与简化；
② 模型是由与分析问题有关的主要要素构成，并表明这些主要要素之间的关系； ⑧ 模型的精细程度与所要决策问题的需要有关。
第二节 Spreadsheet方法的应用
一、建模与求解过程
下面用一个盈亏平衡分析的例子说明Excel求解的应用。盈亏平衡分析是通过分析产品产量、成本与盈利之间的关系，找出各投资方案在产量、产品价格、单位产品成本等方面的临界值，
41
以判断投资方案在各种不确定因素作用下的盈亏状况，从而为决策提供依据。 例： 盈亏平衡分析
华丽床垫厂生产一种床垫，年固定费用为90000元，生产一个床垫的可变费用为50元，床垫的销售单价为100元。假定市场条件不变，产品价格稳定，所有的产品均能被销售。确定该产品在盈亏平衡点的产量。如果该工厂生产2400个床垫，盈亏情况如何?
解：假设床垫产量用X来表示。
则可建立如下模型：
(1)成本－产量模型
总成本为： C(X)＝90000+50X
上式中，C为生产X个床垫的总成本，它是产量X的函数。
(2)收益－销售量模型
收益为： R(X)＝1OOX
上式中，X为床垫的销售量(在本例中，床垫的销售量等于床垫的生产量)； R(X)为销售X个床垫的总收益，它是产量X的函数。 (3)利润－产量模型
总利润为： P(X)＝R(X)一C(X) ＝1OOX一(90000＋50X) ＝－90000＋50X
上式中，P(X)为总利润，它是X的函数。 (4)盈亏平衡分析
当总利润为零时，达到盈亏平衡。 即有：
P(X)＝一90000+50X＝0
计算可得这时的产量为：
X=1800(个)
(5)若生产2400个床垫，则其利润为：
P(2400)＝-90000+50X2400＝30000(元)。
下面以Microsoft Excel为背景，用Spreadsheet方法描述和求解该例。
打开Excel后，出现工作表。该工作表用作描述问题与建立模型时，称为Spreadsheet。在Spreadsheet上进行盈亏分析的基本步骤如下：
首先在Spreadsheet中进行问题描述。
用地址为B4、B5、B6的单元格分别表示固定费用、单位产品可变费用和产品单价，在这些单元格中分别输入已知数据，见下图2—1。
42
图2—1 已知数据的输入
然后在Spreadsheet中建立模型。可在单元格A9处键入“模型”两个字，以表示以下为模型。用单元格B10表示产品产量(相当于上述X)，它是一个有待于确定的决策变量。由于总成本、总收益与总利润均与该决策变量有关，所以可将单元格B10用一个框围起来以示该决策变量的重要性。
单元格B12、B14、B16分别表示总成本、总收益与总利润。总成本(单元格B12)等于年固定费用与年可变费用之和，其中年可变费用等于单位产品可变费用与产品的产量之积，所以在单元格B12中输入下述公式：
＝B4+B5*Bl0 即 90000＋50X
总收益(单元格B14)等于产品价格与产品产量之积，在单元格B14中输入下述公式： ＝B6*Bl0 即 100X
总利润(单元格B16)等于总收益与总成本之差，在单元格B16中输入公式： ＝B14-B12 即 （90000＋50X）－100X
运用上述模型即可计算出不同产品产量下的盈亏情况。
例如，当产品的产量为2400个时，可在单元格B10中输入2400，即得到此时的总成本、总收益与总利润分别为210000元，240000元与30000元，如上图所示。
最后，我们来确定盈亏均衡点：
盈亏均衡点是总成本等于总收益的点，或总利润等于零的点。前面已经算出，当产量为2400个时，总利润为30000元，所以该点不是盈亏均衡点。可在单元格B10中继续输入其他产量值进行试算，直到总利润为零。
下面介绍两种使用Excel中的命令迅速求出盈亏均衡点产量的方法，第一种方法使用数据表命令，第二种方法使用单变量求解命令。 方法一：数据表命令方法
Excel中的数据表命令可用来计算不同输入下的输出值。
在本例中，可用数据表命令计算不同产量下的盈利值或亏损值，其中，盈利值(或亏损值)为零时所对应的那一个产量，即为盈亏均衡点下的产量。
43
用数据表命令求盈亏点下产量的步骤如下：
第一步：确定输入的决策变量值(即床垫的产量)的范围与计算步长。
前面已计算得到，当床垫的产量为2400个时，总利润为正值，即盈利；在上表的模型中，若在单元格B10中试输入1400，得到总利润为负值，即亏损。
因此，在产量在l400与2400之间，必有一个值使得总利润为零，这个值即为盈亏均衡点的产量。
所以，可将输入范围定为（1400，2400），假设计算步长为200。
第二步：在单元格A22：A27中分别输入从1400至2400、步长为200的产量值。 第三步：在单元格B21中输入计算总利润的公式，即：＝B16，如图2—2所示。
图2—2 数据表计算
第四步：用Excel中的数据表命令计算不同产量下的利润值： ① 用鼠标选择单元格A21：B27的区域；
② 在Excel工作表的菜单栏中，选择“数据(Data)”，如图2—2所示； ③ 选择“模拟运算表”（table）；
④ 出现模拟运算表对话框，在“输入引用列的单元格”一栏中输入“B10”，B10是表示产
量的单元格，这表示模拟运算表要计算的不同产量下的利润。
⑤ 选择“确定”。
这时，表内将出现不同产量所对应的利润值。
从表中数据可见，当产量为1800个时，总利润为零，即盈亏均衡点的产量为1800个。
方法二：单变量求解命令方法
用excel的单变量求解命令可以直接求出利润为零所对应的产量。 第一步：在Excel的菜单栏选择“工具（tool）”；
44
图2—3 单变量求解 第二步：选择“单变量求解(Goal Seek)”；
第三步：这时，出现“单变量求解”对话框。在“目标单元格”一栏中输入地址“B16”(总利润值)，在“目标值”一栏中输入“0”(表示总利润为零)，在“可变单元格”一栏中输入地址“B10”(表示产量)，如图2—4所示。
该对话框的输入表明，下面要寻找的是当总利润为零时对应的产量值，选择“确定”。
图2—4 单变量求解
这时，出现“单变量求解状态”表，如图2—5所示。它表示已经求得了一个解,选择“确定”。这时，在单元格B10中即得到盈亏均衡点的产品产量，为1800个。
图2—5 单变量求解状态
第二章 应用Spreadsheet方法建立运筹学模型与求解
本章以举例的方式介绍用Spreadsheet方法如何建立运筹学模型，并进一步求出最优解。
第一节 线性规划问题建模和求解
例 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具，由于该四种家具具有不同的大小、形状、重量和风格，所以它们所需要的主要原料（木材和玻璃）、制作时间、最大销售量与利润均不相同。该厂每天可提
45