注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

零售创新,创新那些事儿,SPSS,VBA

零售创新

 
 
 

日志

 
 
关于我

新浪微博,零售创新 研究经理,数据分析师 希望和市场研究和零售业的同事共同进步! 本博客发表的都是免费或试用的资料,如果有版权问题请发邮件wangli12a@163.com联系删除。 spss excel vba blog

网易考拉推荐

使用规划求解确定最佳产品组合  

2010-12-02 17:38:51|  分类: VB和VBA知识 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

使用规划求解确定最佳产品组合

适用于: Microsoft Office Excel 2003

适用于

Microsoft Office Excel 2003
Microsoft Excel 2000 和 2002

clip_image003本文改编自《Microsoft Excel Data Analysis and Business Modeling》(Wayne L. Winston 著)。

这本具有教学风格的图书是 Wayne Winston 根据一系列演示文稿编写而成。Wayne Winston 是一位知名的统计学家和贸易学教授,专门研究 Excel 具有创新性的实际应用。所以这本书对您开启思路可能会有所帮助。

请访问 Microsoft 学习工具(英文)购买此书。

本文内容

· 什么是 Excel 规划求解工具?

· 如何确定哪种产品组合可以使利润最大化?

· 如何将此模型输入到规划求解中?

· 规划求解模型总是有解决方案吗?

· 如果设置目标单元格的值未收敛,意味着什么?

· 自我测试

示例文件 您可以从 Microsoft Office Online 中示例文本(英文)与 Microsoft Excel Data Analysis and Business Modeling 中的摘录有关的示例文件。本文使用了 prodmix.xls 和 s25_1.xls 至 s25_5.xls 文件。

什么是 Excel 规划求解工具?

当您想要寻找做某件事的最佳方法时,使用的就是规划求解。或者,更正规的说法就是,当您想要在电子表格的某些单元格中得到优化(最大化或最小化)某个目标的值时,使用的就是规划求解。

优化模型包括三部分:目标单元格、可变单元格和约束。

· 目标单元格代表目的或目标。例如,最大化每月利润。

· 可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格。例如,每月每种产品的产量。

· 约束是您置于可变单元格中的限制条件。例如,使用的资源不能超标,并且不能生产过剩的产品。

如何确定哪种产品组合可以使利润最大化?

公司通常需要确定每月(或每周)生产计划,列出每种产品必须生产的数量。具体来说就是,产品组合问题涉及如何确定在每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束:

· 产品组合使用的资源不能超标。

· 对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量,因为生产过剩就是浪费(例如,易变质的药品)。

让我们来解决以下产品组合示例问题。您可以在 prodmix.xls 文件中找到该问题的解决方案(该文件包含在示例文件下载中),如图 1 所示。

clip_image004

图 1:产品组合示例。

假定我们在一家医药公司工作,这家公司可以在他们的工厂生产六种产品。生产每种产品都需要人工和原材料。

· 图 1 的第 4 行显示了生产一磅的每种产品所需的人工小时数,第 5 行显示了生产一磅的每种产品所需的原材料的磅数。例如,生产一磅的产品 1 需要 6 小时人工和 3.2 磅原材料。

· 第 6 行显示了每种药品每磅的价格,第 7 行显示了每磅的成本,第 9 行显示每磅可带来的利润。例如,产品 2 的价格是每磅 11.00 美元,每磅的单位成本是 5.70 美元,每磅的利润就是 5.30 美元。

· 第 8 行显示了该月对每种药品的需求。例如,对产品 3 的需求为 1041 磅。

该月可提供 4500 人工工时和 1600 磅的原材料。该公司如何最大化它每月的利润?

如果我们对规划求解一无所知,我们会通过构建一个电子表格,然后在其中跟踪每种产品组合以及与该产品组合相关联的资源用量来处理这一问题。然后我们会反复试验、不断地变化产品组合以优化利润,同时确保使用的人工或原材料不会超标,并确保不会生产出过剩药品。在此过程中,我们只在反复试验阶段中使用了规划求解。从根本上来说,规划求解是一个可以完美地执行反复试验搜索的优化引擎。

解决产品组合问题的关键是有效地计算与任一给定产品组合相关联的资源用量和利润。SUMPRODUCT 函数是我们可以用来执行此计算的一个重要工具。SUMPRODUCT 函数将单元格区域中相应的值相乘并返回这些值的总和。SUMPRODUCT 评估中使用的每个单元格区域都必须具有相同的维度,这意味着您可以对两行或两列使用 SUMPRODUCT,而不是对一列或一行。

作为如何在产品组合示例中使用 SUMPRODUCT 函数的示例,让我们尝试计算一下我们的资源用量。通过以下计算方式可以得出人工用量:

(每磅药品 1 使用的人工)*

(生产的药品 1 的磅数)+

(每磅药品 2 使用的人工)*

(生产的药品 2 的磅数)+

...

(每磅药品 6 使用的人工)*

(生产的药品 6 的磅数)

在我们的电子表格中,我们可能会通过 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 来计算人工用量(非常繁锁)。类似地,原材料用量可以通过 D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5 计算。在电子表格中对六种产品分别输入这些公式是很浪费时间的。想像一下,如果您正在对一家其工厂生产 50 种产品的公司执行这样的计算,会花费多长时间?

计算人工和原材料用量的一种更为简单的方法是将 D14 中的公式复制到 D15 中:

SUMPRODUCT($D$2:$I$2,D4:I4)

该公式会计算 D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4(这是我们的人工用量),这要比手动输入简单得多!

请注意,我对区域 D2:I2 使用了 $ 符号,以便在我复制公式时,我仍然可以从第 2 行中取下产品组合。单元格 D15 中的公式用于计算原材料用量。

类似地,通过以下计算方式可以得出我们的利润:

(每磅药品 1 的利润)*

(生产的药品 1 的磅数)+

(每磅药品 2 的利润)*

(生产的药品 2 的磅数)+

...

(每磅药品 6 的利润)*

(生产的药品 6 的磅数)。

在单元格 D12 中使用以下公式可以很容易计算出利润:

SUMPRODUCT(D9:I9,$D$2:$I$2)

现在我们可以标识出产品组合规划求解模型的三个组成部分:

目标单元格

可变单元格

约束

我们的目标是使利润(在单元格 D12 中计算)最大化。

生产的每种产品的磅数(在单元格区域 D2:I2 中列出)。

· 使用的人工和原材料不能超标。也就是说,单元格 D14:D15(所用资源)必须小于或等于单元格 F14:F15 中的值(可用资源)。

· 生产的药品不能超过需求数量。也就是说,单元格 D2:I2(生产的每种药品的磅数)必须小于或等于对每种药品的需求(在单元格 D8:I8 中列出)。

· 我们不能生产任何产量为负的药品。

何将此模型输入到规划求解中?

现在,我将向你们演示如何将目标单元格、可变单元格和约束输入规划求解。然后,你们只需单击求解按钮即可,规划求解将会找出可使利润最大化的产品组合。

1. 要开始操作,请选择工具菜单上的规划求解。(有关安装规划求解的说明,请参阅使用 Excel 规划求解工具进行优化的说明。)

即会出现规划求解参数 对话框。

clip_image005

2. 要输入目标单元格,请在设置目标单元格框中单击,然后选择利润单元格(单元格 D12)。要输入可变单元格,请在可变单元格框中单击,然后指向区域 D2:I2,该区域包含生产的每种药品的磅数。该对话框现在看起来应如下图所示。

clip_image006

3. 现在我们已经可以向模型中添加约束了。单击添加按钮,您可以看到添加约束对话框。

clip_image007

4. 要添加资源用量约束,请在标记为单元格引用位置的框中单击,然后选择区域 D14:D15。从对话框中部的列表中选择“<=”。在标记为约束值的框中单击,然后选择单元格区域 F14:F15。

clip_image008

现在我们已经确保当规划求解尝试对可变单元格使用不同的值时,规划求解将只考虑同时满足 D14 <= F14(所用人工小于或等于可用人工)和 D15 <= F15(所用原材料小于或等于可用原材料)的组合。

5. 现在,在添加约束对话框中单击添加,以输入需求约束。只需如下图所示填充添加约束对话框即可。

clip_image009

添加这些约束可以确保当规划求解尝试对可变单元格值使用不同的组合时,规划求解将只考虑满足以下条件的组合:

· D2 <= D8(药品 1 的产量小于或等于对药品 1 的需求量)

· E2 <= E8(药品 2 的产量小于或等于对药品 2 的需求量)

· F2 <= F8(药品 3 的产量小于或等于对药品 3 的需求量)

· G2 <= G8(药品 4 的产量小于或等于对药品 4 的需求量)

· H2 <= H8(药品 5 的产量小于或等于对药品 5 的需求量)

· I2 <= I8(药品 6 的产量小于或等于对药品 6 的需求量)

6. 单击添加约束对话框中的确定规划求解参数对话框应如下图所示。

clip_image010

7. 在规划求解选项对话框中输入所有可变单元格都为非负值的约束,通过单击规划求解参数对话框中的选项按钮可打开该对话框。

clip_image011

选择采用线性模型假定非负选项,然后单击“确定”。

clip_image012为什么要选择这些选项?

选择假定非负选项可确保规划求解只考虑每个可变单元格都采用非负值的可变单元格组合。

选择采用线性模型的原因是产品组合问题是一种称为线性模型的特殊规划求解问题。基本上,在以下情况下,规划求解模型都是线性模型:

· 目标单元格是通过将表单的条件(可变单元格)*(约束)相加进行计算的。

· 每种约束都满足线性模型要求。这意味着每种约束都是通过将表单的条件(可变单元格)*(约束)相加,然后将这些总和与某个常量进行比较来评估的。

这个规划求解问题为什么是线性的?

我们的目标单元格(利润)计算方式为:

(每磅药品 1 的利润)*

(生产的药品 1 的磅数)+

(每磅药品 2 的利润)*

(生产的药品 2 的磅数)+

...

(每磅药品 6 的利润)*

(生产的药品 6 的磅数)

这种计算方式遵循一种模式,即目标单元格的值是通过将表单的各个条件(可变单元格)*(约束)相加得出的.

我们的人工约束是通过将可用人工与通过以下公式得出的值进行比较来评估的:

(每磅药品 1 使用的人工)*

(生产的药品 1 的磅数)+

(每磅药品 2 使用的人工)*

(生产的药品 2 的磅数)+

...

(每磅药品 6 使用的人工)*

(生产的药品 6 的磅数)

因此,人工约束是通过将表格的各个条件(可变单元格)*(约束)相加,然后将这类总和与某个常量进行比较来评估的。人工约束和原材料约束都满足线性模型要求。

我们的需求约束采用以下形式:

(药品 1 的产量)<=(药品 1 的需求量)

(药品 2 的产量)<=(药品 2 的需求量)

...

(药品 6 的产量)<=(药品 6 的需求量)

每种需求约束还都符合线性模型要求,因为每种约束都是通过将表单的各个条件(可变单元格)*(约束)相加,然后将这些总和与某个常量进行比较来评估的。

已经表明我们的产品组合模型是线性模型,我们还要关心什么?

· 如果规划求解模型是线性的,并且我们选择采用线性模型,则规划求解保证可以找到规划求解模型的最佳解决方案。如果规划求解模型不是线性的,则规划求解可能可以找到最佳解决方案,也可能找不到。

· 如果规划求解模型是线性的,并且我们选择采用线性模型,规划求解将使用一种非常高效的算法(单工方法)来找到该模型的最佳解决方案。如果规划求解模型是线性的,并且我们不选择采用线性模型,规划求解将使用一种非常低效的算法(GRG2 方法),并且可能很难找到该模型的最佳解决方案。

8. 单击规划求解选项对话框中的确定后,我们将返回到主规划求解对话框。当我们单击求解时,规划求解将为我们的产品组合模型计算出一个最佳解决方案(如果有)。

产品组合模型问题的最佳解决方案是所有可行解决方案集中可使利润最大化的一组可变单元格值(生产的每种药品的磅数)。同样,可行解决方案是一组满足所有约束的可变单元格值。图 2 中显示的可变单元格值就是一个可行解决方案,因为所有产品级别都是非负值,产品级别都没有超出需求,而且资源用量也没有超出可用资源。

clip_image013

图 2:符合约束的可行的产品组合问题解决方案。

由于以下原因,图 3 中显示的可变单元格值代表一个不可行的解决方案

· 生产的产品 5 的数量大于需求数量。

· 使用的人工大于可用人工。

· 使用的原材料大于可用原材料。

clip_image014

图 3:不符合我们定义的约束的不可行的产品组合问题解决方案。

单击求解后,规划求解会迅速找出最佳解决方案,如图 4 所示。您需要选择保存规划求解解决方案以将最佳解决方案值保留在电子表格中。

clip_image015

图 4:产品组合问题的最佳解决方案。

通过生产 596.67 磅的药品 4、1084 磅的药品 5 而不生产任何其他药品,我们的医药公司每月可获得最高利润 6,625.20 美元!我们无法确定通过其他方法是否可以获得 6,625.20 美元的最高利润。但我们可以确定,在我们有限的资源和需求条件下,这个月的利润根本不可能超出 6,625.20 美元。

规划求解模型总是有解决方案吗?

假定必须满足对每种产品的需求,那么我们就必须将我们的需求约束从 D2:I2 <= D8:I8 更改为 D2:I2 >= D8:I8。要更改此约束,请

1. 打开规划求解。

2. 单击“D2:I2 <= D8:I8”约束,然后单击更改

改变约束对话框随即出现。

clip_image016

3. 在中部的框中,选择“>=”,然后单击确定

现在我们可以确保规划求解将只考虑符合所有需求的可变单元格值。

当您单击求解时,您将会看到规划求解找不到可行的解决方案消息。该消息意味着使用我们有限的资源,无法满足对所有产品的需求。我们的模型并没有错!规划求解只是要告诉我们,如果我们想要满足对每种产品的需求,我们就需要增加更多的人工、更多的原材料或两者都要增加。

如果设置目标单元格的值未收敛,意味着什么?

让我们看看如果我们允许对每种药品无限制的需求,并且允许每种药品的产量为负,会发生什么情况。要找出针对这种情况的最佳解决方案,请执行以下操作:

1. 打开规划求解。

2. 单击选项按钮,然后清除假定非负复选框。

3. 在规划求解参数对话框中,单击需求约束“D2:I2 <= D8:I8”,然后单击删除以删除该约束。

当您单击求解时,规划求解将返回“‘设置目标单元格的值未收敛消息。该消息意味着如果要最大化目标单元格(像我们的示例中一样),会存在具有任意大的目标单元格值的可行解决方案。(如果要最小化目标单元格,该消息则意味着存在具有任意小的目标单元格值的可行解决方案。)

在这种情况下,通过允许药品的产量为负,我们实际上“创造”了可用于生产任意大数量的其他药品的资源。假设我们的需求没有限制,这就使得我们可以创造无限的利润。而现实中,我们是不可能创造无限利润的。简而言之,如果您看到了“‘设置目标单元格的值未收敛,就表示您的模型有错误。

自我测试

s25_1.xls 至 s25_5.xls 文件中提供了这些问题的解决方案,这些文件包含在示例文件下载中。

1. 假定我们的医药公司可以按每小时 1 美元的价格购买了 500 小时的人工。它们将如何利用这次机会?

2. 在一家芯片制造厂,有四位技术员(A、B、C 和 D)生产三种产品(产品 1、2 和 3)。芯片制造商每月可以销售 80 件产品 1,50 件产品 2,产品 3 最多可销售 50 件。技术员 A 只能生产产品 1 和 3。技术员 B 只能生产产品 1 和 2。技术员 C 只能生产产品 3。技术员 D 只能生产产品 2。对于生产的每件产品,产品 1、2 和 3 的利润分别为 6 美元、7 美元和 10 美元。下表显示了制造每件产品每个技术员需要花费的时间(小时)。

产品

技术员 A

技术员 B

技术员 C

技术员 D

1

2

2.5

不能做

不能做

2

不能做

3

不能做

3.5

3

3

不能做

4

不能做

3. 每名技术人员每月最大工作时间为 120 小时。芯片制造商如何最大化它每月的利润?

4. 一家计算机制造工厂生产鼠标、键盘和视频游戏操纵杠。下表给出了该工厂的每件利润、每件人工工时、每月需求及每件占用的机器时间:

 

鼠标

键盘

操纵杆

利润/件

$8

$11

$9

人工使用/件

.2 小时

.3 小时

.24 小时

机器时间/件

.04 小时

.055 小时

.04 小时

每月需求

15,000

25,000

11,000

6. 每月共提供了 13,000 个人工工时和 3,000 小时的机器使用时间。制造商如何最大化工厂每月的利润?

7. 解析我们的药品示例,假定必须满足每种药品的最低需求 200 件。

8. 张森是制作钻石手镯、项链和耳环的宝石商。他每个月最多需要 160 人工工时。他有 800 盎司的钻石。下面给出了每种产品的利润、生产每种产品所需的人工工时和钻石盎司量。如果对每种产品的需求是无限量的,张森如何最大化他的利润?

产品

单件利润

每件人工工时

每件钻石盎司量

手镯

$300

.35

1.2

项链

$200

.15

.75

耳环

$100

.05

.5

  评论这张
 
阅读(1246)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017