工硕数据模型与决策案例研究文档格式.docx
《工硕数据模型与决策案例研究文档格式.docx》由会员分享，可在线阅读，更多相关《工硕数据模型与决策案例研究文档格式.docx（70页珍藏版）》请在冰豆网上搜索。
完成本次学习后，你应该能够：
1、解释什么是线性规划。
2、明确在建立电子表格模型时必须回答的三个关键问题。
3、知道用电子表格为线型规划问题建立模型时的四类单元格，并知道它们各自的作用。
4、能够为一个问题建立基本的线性规划电子表格模型。
5、能够根据线性规划电子表格写出它的代数形式。
6、运用Excel求解线性规划电子表格问题。
伟恩德玻璃制品公司产品组合问题
吉姆·
贝克很是兴奋，这次他所领导的小组获得了真正的成功。
过去他们已取得了一些显著的成功，但是这次他感到尤为特别。
把备忘录递交给高层管理层后，他迫不及待地等待着高层的反馈。
在作为伟恩德（Wyndor）玻璃制品公司新产品发展部经理的7年中，吉姆已经有一项出色的记录。
尽管是小公司，但是凭着吉姆小组开发的创新产品，公司已经取得了相当大的增长。
伟恩德公司总裁约翰·
希尔已公开承认吉姆在公司近来的成功中所起的关键作用。
因此，6个月之前，在要求吉姆小组开发下列新产品时，约翰感到很有信心：
·
8英尺的铝框玻璃门。
4英尺×
6英尺的双把木框窗。
尽管这些规格的门窗产品其他几家公司已经生产过，约翰还是认为吉姆能施展他的魔法在产品中引入使人兴奋异常的新特征，而这些新特征将会建立新的工业标准。
现在吉姆真是喜不自禁，因为他们已经开发出新产品了。
背景
伟恩德玻璃制品公司生产高质量的玻璃制品，包括工艺精湛的窗和玻璃门。
尽管这些产品价格昂贵，但它们是为挑剔的客户提供的行业中质量最高的产品。
公司有三个厂：
工厂1：
生产铝框和五金件。
工厂2：
生产木框。
工厂3：
生产玻璃并组装窗和门。
由于某些产品销售量下降，高层管理部门决定调整公司的产品线。
如果管理部门同意，不盈利的产品就会停产，并腾出生产能力来生产吉姆小组开发的两种新产品。
8英尺玻璃门需要工厂1和工厂3的一些生产能力，但不需要工厂2的生产能力。
6英尺的双把窗需要工厂2和工厂3的生产能力。
现在管理部门要考虑下列两个问题：
1.公司是否应该生产这两个新产品？
2.如果生产，两种新产品的产品生产组合如何？
——每周分别生产多少？
管理层对问题的讨论
收到吉姆·
贝克提交的两种新产品的备忘录后，约翰·
希尔召集了一次会议来讨论当前的问题。
除约翰和吉姆外，制造副总裁比尔·
塔斯托和营销副总裁安·
莱斯特也参加了会议。
让我们偷听一下会议。
约翰·
希尔（总裁）：
比尔，我们希望尽快开始生产这些产品。
你认为我们能够达到多大的产量？
比尔·
塔斯托（制造副总裁）：
由于我们停掉了一些产品的生产，所以确实有一些空闲的生产能力，但不是很多。
我们应该能够做到每种产品每周都各生产几个。
约翰：
就这么多吗？
比尔：
是的，这些都是需要仔细制作的复杂产品，而且正如我说过的，我们没有很多空闲的生产能力。
安，我们每周能卖掉这几个产品吗？
安·
莱斯特：
那容易。
好，我希望六周内确定这些产品的开工日期，比尔、安，这可行吗？
可以。
安：
我们必须马上抓紧时间为这些产品制定恰当的营销计划。
我们能够做到。
好。
现在还有一个问题有待解决，由于生产能力有限，因此我们要决定如何在两种产品间分配这些生产能力。
我们是希望两种产品的产量相等，还是较多地生产其中一个，甚至尽可能多地生产一种产品而推迟另一种产品的生产？
贝克（新产品开发经理）：
推迟一种产品的生产并为竞争对手提供一个比我们抢钱赚取利润的机会是很危险的。
我同意。
另外，从营销的角度看，同时开始生产这两种产品有一些好处，因为两种产品具有许多相同的特性，所以我们能为两种产品做组合广告，这会使效果更好。
但是，这两种产品怎样组合对公司来说最有利可图呢？
我有一个建议。
什么建议？
过去一段时间，我们的管理科学小组已帮助我们制定了这类产品的产品组合决策，他们做得很好。
他们收集了所有相关数据，然后对问题进行了详细分析。
我们发现他们提供的信息很有帮助，而这正是他们的所长。
是的，你说的对。
这是个好主意。
就让管理科学小组来解决这个问题吧。
比尔，你会和他们协调一下吗？
会议结束。
管理科学小组开始工作
项目开始时，管理科学小组与比尔·
塔斯托一起花了大量的时间澄清了一般问题和管理层想要研究的特定问题。
一个特别的考虑是要从管理层视角出发为这一问题设定恰当的目标。
比尔指出，约翰希尔提出的问题是要确定对公司而言盈利最大的两种产品的生产组合。
因此，经比尔同意，管理科学小组对关键问题定义如下：
问题：
两种新产品的生产率（每周生产数量）的哪种组合能最大化两种产品的总利润？
小组还认为，应该考虑三个工厂可用生产能力允许的两种新产品生产率的所有可能组合。
例如，一个方案（尽管吉姆-贝克和安-莱斯勒反对）是现在先不生产其中一种产品（因此生产率设置为零），而尽可能多的生产另一种产品（我们不能忽略这种可能性：
最大化两种产品利润或许是通过不生产某一种产品尽可能多的生产另一种产品得到的）。
下一步，管理科学小组要确定为了进行这项研究而需要收集的信息：
1.每家工厂的可用生产能力。
2.生产每一产品各需要每家工厂多少生产能力。
3.每一产品的单位利润。
这些数量的具体数据无法获得，因而必须做出估计。
估计这些数量需要得到公司其他部门关键职员的帮助。
比尔-塔斯托所负责的部门中的工作人员做出了涉及生产能力的估计。
具体讲，工作人员估计，生产新型门每周约使用工厂1的生产设备4个小时（在其他时间工厂1要继续生产当前的产品）。
生产新型窗每周约使用工厂2的生产设备12个小时。
生产两种产品每周约使用工厂3的生产设备18个小时。
每一产品实际使用各家工厂生产能力的数量取决于产品的生产率。
据估计，每扇门需要使用1个小时的工厂1生产时间和3个小时的工厂3生产时间，每扇窗各需要2个小时的工厂2和工厂3生产时间。
通过对成本数据和产品定价进行分析，会计部门估计出了生产两种产品的利润。
预测门的单位利润为300美元、窗的单位利润为500美元。
表1.1总结了现在收集到的数据。
表1.1伟恩德玻璃制品公司产品组合问题的数据
工厂
单位产品的生产时间
每周可用时间
门
窗
1
1小时
4小时
2
2小时
12小时
3
3小时
18小时
单位利润（美元）
300
500
管理科学小组清楚地认识到这是一个典型的产品组合问题（product-mixproblem）。
因此，下一步要开发一个数学模型——一个线性规划模型——来对问题进行表述以实现数学化求解。
假定表1.1中数据的估计是准确的，下面四节将着重讲解在开发出使两种产品利润最大的产品组合模型后，如何对其进行求解。
1、伟恩德玻璃制品公司满足的细分市场是什么？
2、管理层提出的两个问题是什么？
3、管理科学小组需要分析哪些问题？
4、这一小组对提出的关键问题是怎样定义的？
5、为了展开研究，小组需要收集哪些信息？
问题回顾
1.2在电子表格上建立伟恩德公司问题的模型
电子表格为展示和分析许多管理问题提供了一个功能强大且直观的工具。
现在我们将着重介绍如何利用微软的Excel电子表格软件包对伟恩德公司问题进行展示和分析。
建立伟恩德公司问题的电子表格模型
通过将表1.1中的数据输入电子表格，图1.1同样显示出了伟恩德公司问题（E列和F列是为以后输入数据而保留的）。
我们把显示数据的单元格称为数据单元格（datecell）。
为了将数据单元格与表格中的其他单元格区别开来，我们将它们标注成浅蓝色。
为单元格命名能使表格更容易理解。
我们对伟恩德公司问题电子表格的数据单元格进行了如下命名：
单位利润（C4:
D4）；
每单位产品所用时间（C7:
D9）；
可用时间（G7:
G9）。
要为单元格命名，首先选中单元格，然后点击公式条左边的名称框并输入一个名称。
A
B
C
D
E
F
G
产品组合问题
4
单位利润
5
6
每单位产品所用时间
可用时间
7
工厂1
8
工厂2
12
9
工厂3
18
图1.1把表1.1的数据转送到数据单元格之后的伟恩德公司问题的初始电子表格
建立任何电子表格模型时都要回答三个关键问题。
在开始利用电子表格对一个问题建立数学模型（此处是线性规划模型）之前，必须回答三个问题：
1.要制定的决策（decision）是什么？
2.这些决策有哪些约束条件（constrain）?
3.这些决策的总体绩效测度指标（measureofperformance）是什么？
前面描述了管理科学小组如何花费大量时间与制造副总裁比尔-塔斯托一起来澄清管理层对问题的看法，他们的讨论对上面这些问题给出了如下答案：
在建立电子表格模型前写下对三个关键问题的答案有助于理清思路。
1.要制定的决策是两种新产品的生产率（productionrate）（每周生产量）。
2.决策的约束条件是两种产品在相应工厂里每周生产时间不能超过每个工厂的可用生产时间。
可变单元格包含要制定的决策。
3.对这些决策的总体绩效测度指标是这两种新产品的总利润。
图1.2显示出了如何将上面这些答案编入电子表格。
基于第一个回答，两种产品的生产率（每周生产量）放在单元格C12和D12中，位于这些产品所在列的数据单元格的下面。
既然我们还不知道生产率是多少，所以在图1.2中将其设置为0（实际上，任何一个试验解都可以，负的生产率应被排除在外，因此生产率不可能为负）。
以后在寻找生产率最佳组合时，这些数值会被改变。
因此，含有需要制定决策的单元格被称为可变单元格（changingcell）[或称可调整单元格（adjustablecell）]。
为了突出可变单元格，我们为其配上了浅灰色背景并标有边框。
可变单元格被命名为产量（C12:
D12）。
利用时间
0
≤
10
11
总利润
产量
0
13
14
图1.2伟恩德公司问题完整的电子表格
利用对第二个问题的回答，将两种产品在相应工厂每周使用的生产时间总数输入单元格E7、E8和E9中，正好位于相应数据单元格的右边。
生产时间的总数取决于两种产品的生产率，因此当生产率为零时，生产时间的总数也为零。
在生产率为正的情况下，一个工厂每周的生产时间总数等于每种产品每周用去的生产时间的总和。
一种产品用去的生产时间等于每单位产品用去的生产时间乘以生产的产品数量。
因此，当每周门和窗的生产数量（正数）输入单元格C12和D12时，单元格C7：
D9中的数据就被用来计算每周的总生产时间，计算如下：
工厂1的生产时间=1（门的生产量）+0（窗的生产量）
工厂2的生产时间=0（门的生产量）+2（窗的生产量）
工厂3的生产时间=3（门的生产量）+2（窗的生产量）
（C7：
D9中间的冒号是Excel对C7到D9这一区域的简略表达方式，也就是C、D列和7、8、9行组成的所有单元格区域。
）因而，E列中的前三个单元格的Excel公式为：
E7=C7*C12+D7*D12
E8=C8*C12+D8*D12
E9=C9*C12+D9*D12
这里的星号表示乘号。
由于所有这些单元格中都给出了根据可变单元格（C12和D12）计算出的输出结果，因此它们被称为输出单元格（outputcell）。
输出单元格显示依赖可变单元格的输出结果。
注意，输出单元格中的公式包含了两个乘积之和。
Excel中有一个函数SUMPRODUCT，它会对拥有相同行数和相同列数的单元格区域中的各项之积进行加总。
需要加总的每一个乘积都是第一个区域中的一项与第二个区域中相应位置的项的乘积。
例如，考虑两个区域C7：
D7和C12：
D12，每个区域都包含一行两列。
在这种情况下，SUMPRODUCT（C7：
D7，C12：
D12）就会将C7：
D7区域中的每一项乘以C12：
D12区域中的相应项，然后将这些乘积加在一起，就像上面的第一个公式显示的那样。
使用C12：
D12的区域名称UnitsProduced（生产数量），公式就为成了SUMPRODUCT（C7：
D7，产量）。
尽管我们并不强制使用这种简短的公式，但对于输入较长的公式来说，这一函数特别方便。
SUMPRODUCT函数在线性规划的电子表格模型中被广泛使用。
输出单元格E7：
E9中的公式非常相似。
我们不必将这些公式分别输入到3个单元格里，而是可以只在E7中输入一次，然后将公式复制到E8和E9中，这样更快一些（还可以少打一些字）。
为了做到这一点，我们先在E7单元格中输入公式“=SUMPRODUCT（C7：
D7，单位利润）”，然后选择E7单元格，向下拖填充柄（fillhandle），就可以将公式复制到E8和E9单元格中。
在使用填充柄的时候，理解相对引用（relativereference）和绝对引用（absolutereference）之间的差别是非常重要的。
在单元格E7的公式中，对C7：
D7单元格的引用以包含公式的单元格的相对位置为基础。
在这个例子中，这意味着两个单元格在一列上，并紧邻其左侧。
这被称为相对引用。
当利用填充柄将这个公式复制到其他单元格中时，其引用地址会自动调整。
E8单元格中的公式变成了=SUMPRODUCT（C8：
D8，产量），E9单元格中的公式变成了=SUMPRODUCT（C9：
D9，产量）。
这正是我们想要的，因为我们总是希望特定工厂的工作时间以该工厂的单位生产时间为基础（这两个单元格位于同一列，并紧挨在左边）。
相比之下，对E7单元格中产量的引用被称为绝对引用（absolutereference）。
当它们被复制到其他单元格时，这些引用不会改变，而是指向同样的绝对单元格位置。
要想进行相对引用，只需要输入单元格地址（如C7：
D7）。
通过单元格名称进行引用被当成绝对引用。
对一系列单元格进行绝对引用的另一种方法是在代表单元格地址的字母和数字前加上“$”符号（如$C$12：
$D$12）。
在计算机上，≤（或≥）通常用<
=（或>
=）来表示，因为键盘上没有≤（或≥）。
在电子表格中输入≤（或≥）的简便方法是输入<
（或>
），然后加上下划线。
接下来，在单元格F7、F8、F9中输入≤符号，表示它们左边的总值不允许超过G列中对应的数值。
电子表格仍然允许你输入违反≤符号的试验解，但是，这些≤符号会提醒你，如果G列中的数值没有变化，应该拒绝接受这些试算解。
最后，因为第三个问题的答案是用两种新产品的总利润作为总体绩效测度指标，因此这一利润（每周）被输入单元格G12。
与E列中的其他数值类似，它也是一些乘积的加总。
因为单元格C4和D4给出了生产一扇门和一扇窗的利润，所以每周生产这些产品的总利润为：
利润=300美元（门的产量）+500美元（窗的产量）
因此，单元格G12的公式为：
G12=SUMPRODUCT（C4：
D4，C12：
D12）
利用区域名称—总利润（G12）、单位利润（C4：
D4）、产量（C12：
D12），公式变成了：
总利润=SUMPRODUCT（单位利润，产量）
这是个很好的例子，表明运用单元格名字可以使公式更容易理解。
目标单元格包括了对所有可变单元格所做决策的评估。
总利润（G12）是一个特殊的输出单元格，在制定与生产率相关的决策时，该单元格中的值应尽可能大。
所以，单元格总利润（G12）被称为目标单元格（targetcell）[或目的单元格（objectivecell）]。
目标单元格用比可变单元格更深的灰色表示，并且边框更粗。
图1.3的底部对需要在利用时间列和总利润单元格中输入的公式进行了归纳，同时总结了区域名称以及相应的单元格地址。
范围
单元格
公式
G7:
G9
E7
=SUMPRODUCT（B14:
C14,产量）
E7:
E9
E8
=SUMPRODUCT（B15:
C15,产量）
C7:
D9
=SUMPRODUCT（B16:
C16,产量）
G12
C4:
D4
=SUMPRODUCT（单位利润,产量）
C12:
D12
图1.3伟恩德问题的电子表格，包含目标单元格（G12）和E列输出单元格的公式，其目标是似的目标单元格最大化
这就完成了伟恩德公司问题建立电子表格模型的工作。
利用这个模型，对生产率的任何一个试验解进行分析就变得非常容易。
每次在单元格C12和D12中输入生产率数值，Excel就会立即计算出所用工时和总利润的数值。
例如，图1.4显示了当生产率设置为每周4扇门和3扇窗时的电子表格，单元格G12显示出每周产生的总利润为2700美元。
同时，由于E7=G7、E8＜G8和E9=G9，所以F列中的≤符号都得到了满足。
因而，这个试验解是可行的（feasible）。
但进一步提高两种产品的生产率，试验解将不再可行，因为这会导致E7＞G7和E9＞G9。
2700
图1.4在韦恩德问题的可变单元格产量（C12:
D12）中输入一个新的试算解的点在表格
这个试算解给出了生产率的最佳组合了吗？
不一定。
通过增加一个的产品生产率同时降低另一个产品的生产率可能会进一步增加总利润。
然而，没有必要继续用试算法来寻找，我们会在下面介绍如何利用Excel“规划求解”快速找到最优解。
这个电子表格模型是一个线性规划模型
图1.3显示的电子表格是线性规划模型的一个例子，原因是它具有所有下列特征。
电子表格中线性规划模型的特征：
1.需要制定一系列关于活动水平的决策，因此可变单元格被用来显示这些水平（伟恩德问题的两个活动是生产两种新产品，所以可变单元格显示了每种产品的周产量）。
2.这些活动的水平可以取能够取满足多个约束条件的任何数值（包括小数值）（伟恩德新产品的生产率仅受3个工厂的可用工时限制）。
3.每一个约束条件都描述了一种对活动水平可行值的限制，约束条件的左边往往是一个输出单元格，中间是一个数学符号（≤、≥或=），右边是数据单元格（伟恩德问题的3个工厂可用工时的约束条件如图1.2至图1.4中所示，E列为输出单元格，F列为≤符号，G列为数据单元格）。
4.活动水平决策以输入到目标单元格的一个总体绩效测度指标为基础，其目标是最大化或最小化目标单元格，这由绩效测度指标的性质决定（伟恩德问题的绩效测度指标为两个新产品所能提供的周总利润，因此这一指标被输入目标单元格G12，其目标是最大化目标单元格）。
5.每个输出单元格（包括目标单元格）的Excel公式可以表达为一个SUMPRODUCT函数，其中加总的每一项都是一个数据单元格与一个可变单元格的乘积（图1.3的底部显示了再伟恩德问题中SUMPRODUCT函数是如何被运用到每个输出单元格中的）。
特征2和特征5是区分线性规划模型和其他可在电子表格上建模的数学模型的关键。
特征2取消了活动水平必须是整数变量的条件。
例如，如果伟恩德公司要制定的决策是门和窗的总数量（必须是整数），而不是每周的产量可以是小数值，因为一扇门或窗可以在本周开始生产而在下一周完成生产），就会出现这种情况。
如果活动水平必须是整数，我们可以对电子表格进行一个小调整从而采用一种类似的模型（被称为整数规划模型），我们将在后面讨论这一问题。
特征5禁止了输出单元格的Excel公式不能表达为一个SUMPRODUCT函数的情况。
为了阐明这一情况，假设由于营销规模经济，伟恩德公司的新窗生产率增加1倍后每周得到的利润增加超过1倍，这意味着目标单元格的Excel公式将要比SUMPRODUCT函数更加复杂。
建模步骤小结
为伟恩德公司问题在电子表格上建立线性规划模型的过程同样适用于许多其他问题。
下面是对这一过程包含步骤的小结：
1.收集问题的数据（如表1.1为伟恩德公司问题总结的那样）。
2.在电子表格的数据单元格中输入数据。
3.确定需要的活动水平决策并指定可变单元显示这些决策。
4.确定这些决策的约束条件并引入设定这些约束条件的
升级会员 