当前位置：文档之家 › excel线性规划实验：奶制品厂生产(销售)的最优化决策实验指导书

excel线性规划实验：奶制品厂生产(销售)的最优化决策实验指导书

  • 格式：pdf
  • 大小：883.32 KB
  • 文档页数：21

下载文档原格式

  / 21

合集下载

实验1用Excel求解线性规划模型

实验1用Excel求解线性规划模型

实验一、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。

Excel最多可解200个变量、600个约束条件的问题。

下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。

一、实验目的1、掌握如何建立线性规划模型。

2、掌握用Excel求解线性规划模型的方法。

3、掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。

4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。

二、实验内容1、[工具][规划求解]命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。

在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。

使用[规划求解]命令的一般步骤为：第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。

图1“规划求解参数”对话框选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。

如利润问题，选取“最大值”可变单元格决策变量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。

图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。

图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。

运筹学实验报告 运用EXCEL解线性规划 报告范文 让利益最大化 生产规划

运筹学实验报告 运用EXCEL解线性规划 报告范文 让利益最大化 生产规划

让利益最大化——关于皇氏乳业加工奶制品的生产计划摘要：如今乳制品的市场竞争越来越强，原料成本正在增加，为了提高皇氏乳业的竞争力，提高公司的利润，公司决定开发新产品，原料奶油及中老年奶粉。

先对皇氏乳业的原料成本，生产时间，产品利润等做了一系列调查，建立了线性规划模型，在对模型求解并进行灵敏度分析后，给出具体的对策建议。

关键词：线性规划；生产成本；最优生产计划一、问题的提出经过调查，每一桶牛奶的生产成本和利润如下表：每天至多加工50桶牛奶，机器最多使用480小时，至多加工100kg奶油A1。

（一）如何制定生产计划，使每天获利最大？（二） 35元可以买到一桶牛奶，买吗？若买，每天最多买多少？（三）可聘用临时工人，付出的工资最多是每小时几元？（四）奶油A1的获利增加到30元/公斤，是否改变生产计划？1.问题分析首先，工厂的经济效益主要取决于原料，劳动时间，产品利润等，至于劳动机械磨损，工人熟练程度等，均不予考虑。

所以我们主要研究原料成本，劳动时间，产品利润与工厂经济效益的关系。

2.数据的收集整理对于奶油A1、奶粉A2的产量，询问工厂管理人员得知。

对于加工时间，可以通人力资源管理部门查询。

对于利润，通过近期一个月的销售成绩，综合分析得出。

二、运筹模型1、模型的建立设X1桶牛奶生产奶油A1，X2桶牛奶生产奶粉A2。

Maxz=72X1+64X2St. X1+X2<=5012X1+8X2<=4803X1<=100X1,X2>=02、模型的求解应用EXCEL软件进行求解。

3、灵敏度分析包括对于目标系数（桶数）变化的灵敏度分析结果表和对于约束条件，如原料供应，劳动时间，加工能力等变化的灵敏度分析结果表。

4、结果分析（一）当20桶牛奶生产奶油A1，30桶生产奶粉A2，利润达到3360元，是最大值。

（二）原料增加1单位，利润增加48。

35元<48元，应该买（三）时间增加1单位，利润增加2元，能力增减不影响，所以临时雇用临时工人每小时不超过2元。

excel线性规划实验：奶制品厂生产(销售)的最优化决策实验指导书

excel线性规划实验：奶制品厂生产(销售)的最优化决策实验指导书


例如， 我们使用第 10 行单元格进行时间约束表达。 实际量代表在目前决策变 量 x1 和 x 2 取值条件下，不同产品生产总共消耗的时间。其取值为 60，60 这个值 不能够直接输入数值，必须使用函数表达“=SUM（C10:D10）”，以表示总生产和 特定品种生产量之间的数学关系。该函数表达的含义是，生产需要消耗的时间为 A、B 两种奶制品生产加工时间之和。其中，奶制品 A 在现有产量下的生产时间 为 40，40 这个值同样不能够直接输入，需要使用函数表达“=C9/C3*C4”，其含义 表达为“生产 10 公斤奶制品 A 需要消耗的时间”。 再一次强调，在线性规划优化模块中，呈灰色的单元格中的数据全部都是使 用函数进行表达，不能过直接输入数据。 在图 1-1 中，决策变量 x1 和 x 2 所在单位格为 C9、D10，其初始值可以随意设 置，直接填写。在本例中我们直接填写为 10。在使用“规划求解”加载项进行求解 后，决策变量所在单元格的数值会自动发生变化。 步骤 3：参考图 1-2，检查模型中各单元格的计算公式。
图 1-5 运算结果报告
对于本例，可以发现所需工时和原材料牛奶的需求量都已达到限制值，而总 产量未达到限制值，说明生产能力还有剩余。因此，如果要提高总利润，必须增 加工时和原材料的可供量，若增加生产能力则对总利润无影响，相反生产能力可 以减少 40， 然能达到当前最大总利润。第 4、第 5 约束条件其实是两种产量的非 零约束，“状态”栏中“未到限制值”只说明两个决策变量的求解结果都满足大于 0 的条件。
因为每公斤 A 产品可获利 24 元，每公斤 B 产品可获利 16 元，那么目标函数 即为 y 24 x1 16 x2 。 由于 1 号生产线可用 12 小时加工成 3 公斤 A 产品，因此生产每公斤 A 产品 所需要的时间为 4 小时；同理，2 号生产线生产每公斤 B 产品所需要的时间为 2 小 时 ， 生 产 两 种 产 品 的 总 时 间 不 能 超 过 480 小 时 ， 工 时 的 约 束 条 件 为
《EXCEL规划求解》选修指导书

《EXCEL规划求解》选修指导书

《EXCELL辅助决策实验》选修指导书本章以举例的方式介绍用Spreadsheet方法解决各种管理问题。

第一节线性规划问题建模和求解例雅致家具厂生产计划优化问题雅致家具厂生产4种小型家具，由于该四种家具具有不同的大小、形状、重量和风格，所以它们所需要的主要原料（木材和玻璃）、制作时间、最大销售量与利润均不相同。

该厂每天可提供的木材、玻璃和工人劳动时间分别为600单位、1000单位与400小时，详细的数据资料见下表。

问：（1）应如何安排这四种家具的日产量，使得该厂的日利润最大？（2）家具厂是否愿意出10元的加班费，让某工人加班1小时？（3）如果可提供的工人劳动时间变为398小时，该厂的日利润有何变化？（4）该厂应优先考虑购买何种资源？（5）若因市场变化，第一种家具的单位利润从60元下降到55元，问该厂的生产计划及日利润将如何变化？表1 雅致家具厂基本数据解：依题意，设置四种家具的日产量分别为决策变量x1,x2,x3,x4,目标要求是日利润最大化，约束条件为三种资源的供应量限制和产品销售量限制。

博客地址：/ 1博客地址：/2据此，列出下面的线性规划模型：其中X1,X2,X3,X4分别为四种家具的日产量。

下面介绍用Excel 中的“规划求解”功能求此题。

第一步 在Excel 中描述问题、建立模型，如下图所示。

①②③④⑤⑥⑦⑧ ⎪⎪⎪⎪⎪⎩⎪⎪⎪⎪⎪⎨⎧≥≤≤≤≤≤+++≤+++≤++++++=（非负约束）需求量约束）（家具需求量约束）（家具需求量约束）（家具需求量约束）（家具（劳动时间约束）（玻璃约束）（木材约束）0,,,41003502200110040023121000226600224..30402060432143214321432143214321x x x x x x x x x x x x x x x x x x x x t s x x x x MaxZ第二步在“工具”菜单中选择“规划求解”。

Excel求解线性规划问题实验教程要点

Excel求解线性规划问题实验教程要点

数学与信息科学学院Excel求解线性规划问题实验教程二零一三零八月目录1.关于“规划求解” (1)2.如何加载“规划求解” (2)3.“规划求解”各参数解释和设置 (3)4.“规划求解”的步骤 (6)5.Excel求解线性规划问题 (8)6.Excel求解运输问题 (14)7.Excel求解目标规划问题 (18)8.Excel求解整数规划问题 (22)1.关于“规划求解”“规划求解”是Excel中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式（公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。

公式总是以等号（=）开始）的最优值。

“规划求解”将对直接或间接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。

“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。

在创建模型过程中，可以对“规划求解”中的可变单元格数值应用约束条件（约束条件：“规划求解”中设置的限制条件。

可以将约束条件应用于可变单元格、目标单元格或其它与目标单元格直接或间接相关的单元格。

而且约束条件可以引用其它影响目标单元格公式的单元格。

使用“规划求解”可通过更改其它单元格来确定某个单元格的最大值或最小值。

）Microsoft Excel的“规划求解”工具取自德克萨斯大学奥斯汀分校的Leon Lasdon 和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码。

线性和整数规划问题取自Frontline Systems公司的John Watson 和Dan Fylstra提供的有界变量单纯形法和分支边界法。

2.如何加载“规划求解”安装office的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。

下面是加载“规划求解”宏的步骤：（1）在“工具”菜单上，单击“加载宏”。

利用EXCEL进行线性规划

利用EXCEL进行线性规划


解：依题意，设置四种家具的日产量分别为决策变量
x1, x2 , x3, x4 ，目标要求是日利润最大化，
约束条件为三种资源的供应量限制和产品销售量限制。 据此，列出下面的线性规划模型：
MaxZ 60x1 20x2 40x3 30x4
4x1 2x2 x3 2x4 600
x1, x2 , x3 , x4 0 （非负约束）
其中 x1, x2 , x3, x4 分别为四种家具的日产量。
下面介绍用Excel中的“规划求解”功能求此题。 第一步 在Excel中描述问题、建立模型，如下图所示。
=SUMPRODUCT(B6:E6,$B$15:$E$15)
第二步 在“工具”菜单中选择“规划求解”。
第三步 在“规划求解参数”对话框进行选择如下图。
第四步 点击“选项”按钮，弹出“规划求解选项”对话框
第五步 选择“采用线性模型”和“假定非负”， 单击“确定”，返回下图。单击“求解”，即可解 决此题。
最后结果如下页图所示。
应如何安排这四种家具的日产量，使得该厂的日利 润最大？
表1 雅致家具厂基本数据
12
3
4
家具类型
劳动时间
21 3
2
（小时/件）
木材
42 1
2
（单位/件）
玻璃（单位/ 6 2
1
2
件）
单位利润
60 20 40
30
（元/件）
最大销量 100 200 50
100
（件）
可提供两 400小时 600单位 1000单位
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可 以加载或保存规划求解模型,并对规划求解过 程的高级属性进行控制
线性规划作业

线性规划作业

线性规划作业（数学规划作业一）1、用两种编程方式求解下列问题2、将下述问题化成标准线性规划问题3、奶制品的生产销售计划一奶制品加工厂用牛奶生产A 1、A 2两种奶制品，1桶牛奶可以在甲类生产设备上用12h 加工成3kg A 1种奶制品，或在在乙类生产设备上用8h 加工成4kg A 2种奶制品.若A 1、A 2两种奶制品全部能售出,且甲种奶制品售价24元/kg, 乙种奶制品售价16元/kg 。

现在工厂每天能得到50桶牛奶,每天正式工人总的劳动时间为480h,且甲类生产设备每天至多加工100kg 甲种奶制品, 乙类生产设备每天加工乙种奶制品没有限制.为了增加工厂的获利，开发了奶制品的深加工技术，用2h 和3元加工费，可将1kg A 1加工成0.8kg 高级奶制品B 1;也可将1kg A 2加工成0.75kg 高级奶制品B 2，B 1与B 2售价分别为44元与32元，试为该工厂制订一个生产计划,使每天获利最大.并进一步讨论以下3 个问题:（1）、若用30元买一桶牛奶，投资3元可以增加1h 劳动时间，是否投资？若每天投资150元，可获利多少？（2）、每kg 高级奶制品B 1与B 2的获利经常有10%的波动，对制订生产销售计划有影响？若B 2的获利下降10%，计划是否变化？（3）、若工厂已签订了每天销售10kg A 1的合同并且必须满足，该合同对工厂的获利有什么影响？4、供水问题某市从A 、B 、C 三个水库向甲、乙、丙、丁四个生活区供应自来水，C 不能向丁区供水.四个生活区每天的基本生活用水分别为30，70，10，10（单位103t ），并且每天申请了额外的用水量分别为50,70,20,40（单位103t ）;三个水库每天最多只能供应50,60,50（单位103t ）.由于地理位置不同,向各区送水所需的引水管理费不同(表1),其他管理费每单位(103t)450元,但向各区都统一收取每单位(103t)900元.问怎样制定供水方案,才能使获利最大?为了增加供水量,拟对水库进行改造,使各水库的最大供水量增加1倍,问怎样制定供水方案,才能使获利最大?表1 引水管理费(元/103t)⎪⎩⎪⎨⎧-≤+---≤-+--≤+--2143214321432132132..x x x x x x x x x x x x t s 4321432min x x x x z +++=),,,{min(max 11211i mi im i mi i i mi i x x a x a x a i∑∑∑=== ⎩⎨⎧=≥=+++m i x x x x t s im ,,2,1,01.215、货物装运问题某架货机有前、中、后三个货舱,所能装载的货物的最大重量和体积都有限制(见表1),且三个货舱实载货物的重量与其最大限载重量成比例.表1 最大限载量现有四类货物由本机装载,信息如表2:表2 四类货物装载信息问应如何装运,使本架货机获利最大?。

加工奶制品的生产计划

加工奶制品的生产计划


OBJ COEFFICIENT RANGES
(约束条件不变)
VARIABLE CURRENT ALLOWABLE ALLOWABLE
COEF INCREASE DECREASE
X1 X2
ROW
72.000000 24.000000
8.000000 x1系数范围(64,96)
64.000000 8.000000
LINGO︱Options︱General Solver(通用求解程序)选项卡
ቤተ መጻሕፍቲ ባይዱ对偶计算，包括对偶 价格和敏感性分析
要使用敏感性分析 必须要在这选择 使用敏感性分析
结果解释
LINGO︱Range 最优解不变时目标
RANGES IN WHICH THE BASIS IS UNCHANGED: 系数允许变化范围
Ite
扩展求解器
使用的特殊求解程序 到目前的最佳目标值
特殊求解程序 当前运行步数
有效步数
Type 可能显示 Obj B-and-B
Global Multistart
T N
In
T N
T N
变量数量 约束数量 非零系数数量
可直接求 解的变量 不作为决 策变量。
内存使用数量 求解花费时间
知识回顾 Knowledge Review
第二行起为约束条件,约束行名字被放“〔 〕”中。 • 行中注有“!”符号的后面部分为注释。
使用LINGO的一些注意事项
• 在模型的开头可以用“TITLE” 对模型命名， • 变量可以放在约束右端 • 每行（目标，约束，说明语句）后增加 “；” • ＠开头都是函数调用； • 上下界限定用@BND,不计入模型的约束，也不能给出
80.000000 围内
奶制品的生产与销售

奶制品的生产与销售


模型求解
OBJECTIVE FUNCTION VALUE 1) 3460.800 VARIABLE VALUE REDUCED COST X1 0.000000 1.680000 X2 168.000000 0.000000 X3 19.200001 0.000000 X4 0.000000 0.000000 X5 24.000000 0.000000 X6 0.000000 1.520000 ROW SLACK OR SURPLUS DUAL PRICES 2) 0.000000 3.160000 3) 0.000000 3.260000 4) 76.000000 0.000000 5) 0.000000 44.000000 6) 0.000000 32.000000 NO. ITERATIONS= 2
• 35元可买到1桶牛奶，要买吗？
35 <48, 应该买！
• 聘用临时工人付出的工资最多每小时几元？ 2元！
DO RANGE(SENSITIVITY) ANALYSIS?
Yes
(C,A,B中任一元素改变，讨论次数改变对最优解影响时，要做灵敏度分析)
最优解不变时目标函 数系数允许变化范围
Ranges in which the basis is unchanged: (约束条件不变) Objective Coefficient Ranges：（价值系数范围） Current Allowable Allowable Variable Coefficient Increase Decrease X1 72.00000 24.00000 8.000000 x1系数范围(64,96) X2 64.00000 8.000000 16.00000 x 系数范围(48,72) 2 Righthand Side Ranges：（右端项范围） Current Allowable Allowable Row RHS Increase Decrease x1系数由24 3=72 2 50.00000 10.00000 6.666667 增加为303=90， 3 480.0000 53.33333 80.00000 在允许范围内 4 100.0000 INFINITY 40.00000
附1：用LINGO求解线性规划的例子 一奶制品加工厂用牛奶生产A1、A2

附1：用LINGO求解线性规划的例子 一奶制品加工厂用牛奶生产A1、A2

附1：用LINGO求解线性规划的例子一奶制品加工厂用牛奶生产A1、A2附1:用LINGO求解线性规划的例子一奶制品加工厂用牛奶生产A、A两种奶制品，1桶牛奶可以在设备甲上用12小时加工成3公斤A，121或者在设备乙上用8小时加工成4公斤A。

根据市场需求，生产的A、A能全部售出，且每公斤A获利212124元，每公斤A获利16元。

现在加工厂每天能得到50桶牛奶的供应，每天正式工人总的劳动时间为4802 小时，并且设备甲每天至多能加工100公斤A，设备乙的加工能力没有限制。

试为该厂制定一个生产计划，1使每天获利最大，并进一步讨论以下3个附加问题:1)若用35元可以购买到1桶牛奶，应否作这项投资,若投资，每天最多购买多少桶牛奶,2)若可以聘用临时工人以增加劳动时间，付给临时工人的工资最多是每小时几元,3)由于市场需求变化，每公斤A的获利增加到30元，应否改变生产计划, 1数学模型:设每天用x桶牛奶生产A1 ，用x桶牛奶生产A2 12目标函数:设每天获利为z元。

x桶牛奶可生产3x公斤A1，获利24*3x，x桶牛奶可生产4*x公11122斤A2，获利16*4x，故z=72x+64x212约束条件:原料供应:生产A、A的原料(牛奶)总量不超过每天的供应50桶，即 12x+x?50 12劳动时间:生产A、A的总加工时间不超过每天正式工人总的劳动时间480小时，即 1212x+8x?480 12设备能力:A的产量不得超过设备甲每天的加工能力100小时，即 13x?100 1非负约束:x、x均不能为负值，即x?0，x?0 2121综上所述可得max z=72x+64x 12s.t.x+x?50 1212x+8x?480 123x?100 1x?0，x?0 21显然，目标函数和约束条件都是线性的，这是一个线性规划(LP)，求出的最优解将给出使净利润最大的生产计划，要讨论的问题需要考虑参数的变化对最优解和影响，一般称为敏感性(或灵敏度)分析。

129297688535312500excel实验指导书

129297688535312500excel实验指导书

经济管理中的计算机应用——Excel数据分析统计预测和决策模拟(财经管理与计算机应用)目录实验一网上书店的数据库创建及其查询 (2)实验1-1“响当当”网上书店的数据库创建 (2)实验1-3“响当当”网上书店会员分布和图书销售信息查询 (4)实验二企业销售数据的分类汇总分析 (4)实验2-1“北风”贸易公司客户特征分析 (4)实验2-2northwiwnd公司销售业绩观测板 (9)实验三餐饮公司经营数据时间序列预测 (11)实验3-1“美食佳”公司半成品年销售量预测 (11)实验3-2“美食佳”公司月管理费预测 (12)实验3-4“美食佳”公司会员卡发行量趋势预测 (14)实验3-5“美食佳”火锅连锁店原料年度采购成本预测 (15)实验四住房建筑许可证数量的回归分析 (17)实验4-1“家家有房”公司建筑许可证一元线性回归分析 (17)实验4-3“家家有房”公司建筑许可证多元线性回归分析 (19)实验4-4“家家有房”公司建筑许可证多元非线性回归分析 (20)实验五电信公司宽带上网资费与电缆订货决策 (22)实验5-1电信公司包年制资费方案决策 (22)实验5-2电信用户包年制与限时制资费方案的选择 (22)实验5-3电信公司电缆订货方案决策 (23)实验六奶制品厂生产/销售的最优化决策 (25)实验6-1“猛牛”奶制品厂生产计划制定 (25)实验6-2“猛牛”奶制品厂原料供应站的分布决策 (25)实验6-3“猛牛”奶制品厂产品销售方案决策 (26)实验七运动鞋公司经营投资决策 (27)实验7-1“太阳”运动鞋公司新产品开发的投资决策 (27)实验7-2“太阳”运动鞋公司扩大生产规模的投资决策 (29)实验7-3“太阳”运动鞋公司资金筹措的投资决策 (29)实验八项目风险模拟决策 (30)实验8-1“步步灵”公司项目投资风险分析 (30)实验8-2“步步灵”公司项目管理风险分析 (31)实验一网上书店的数据库创建及其查询实验1-1“响当当”网上书店的数据库创建实验目的•理解数据库的概念；•理解关系（二维表）的概念以及关系数据库中数据的组织方式；•了解数据库创建方法。

奶制品生产和销售

实验名称：奶制品的生产与销售
实验日期：2015年 5月 12日星期二,
周次：11
课节：5-6
实验目的：通过本实验掌握数学规划模型的数学思想，设计算法，使用Matlab 编程解决奶制品的生产与销售问题。

实验内容：1.一奶制品加工厂用牛奶生产21,A A 两种奶制品，1桶牛奶可以在甲类设备上用12小时加工成3公斤1A ，或者在乙类设备上用8小时加工成4公斤2A 。

根据市场需求，生产的21,A A 全部能售出，且每公斤1A 获利24元，每公斤2A 获利16元。

现在加工厂每天能得到50桶牛奶的供应，每天正式工人总的劳动时间为480小时，并且甲类设备每天至多能加工100公斤1A ，乙类设备的加工能力没有限制。

试为该厂制订一个生产计划，使每天获利最大。

2.为增加工厂的获利，开发了奶制品的深加工技术：用2小时和3元加工费，可将1公斤1A 加工成0.8公斤高级奶制品1B ，也可将1公斤2A 加工成0.75公斤高级奶制品2B ，每公斤1B 能获利44元，每公斤2B 能获利32元，。

试为该厂制订一个生产销售计划，使每天的净利润最大。

数值算法：
1.求问题1最优解[x]=linprog(f,A,b,[],[],lb,[]);计算最大利润z =g*x;
2.求问题2最优解[x]=linprog(f,A,b,Aeq,beq,lb,[]); 计算最大利润z =g*x;
3.输出结果。

1实验51 加工奶制品的生产计划 实验52 奶制品的生产销售计划

河北大学《数学模型》实验实验报告一、实验目的学会利用LＩNGO进行实验,熟练掌握用LＩNＧO求解简单的线性规划问题以及能够完成对其灵敏度的分析。

二、实验要求１.实验５-1 加工奶制品的生产计划按如下步骤操作:(1)打开lingo(2）修改“选项…”(Ｏptions…）LINGO/Ｏptｉons…在出现的选项框架中，选择Generaｌ Solveｒ(通用求解器)选项卡,修改2个参数： Dｕa ｌ Coｍpｕtatｉonｓ(对偶计算）设置为:Prｉces ａnd Raｎges(计算对偶价格并分析敏感性）Model Ｒｅgeneｒatｉｏｎ（模型的重新生成)设置为：Alwaｙs(每当有需要时）点击OＫ退出。

（３)在模型窗口输入模型Modｅl:ｍax =72*x１+64*x２;［milk] x1＋ｘ2<5０;[ｔiｍｅ] 12*ｘ1+8＊ｘ2＜４８0;[cｐct] 3*x１<100;Eｎd保存为：ｓy4-1.lg4ＬINGO语法:1. 程序以“model:”开始，每行最后加“；”,并以“enｄ”结束;2. 非负约束可以省略;3. 乘号＊不能省略;4. 式中可有括号;5. 右端可有数学符号。

（４）求解模型运行菜单LＩNGO/Sｏlｖe。

选择LIＮGO/Solｖｅ求解结果的报告窗口检查输出结果与教材p８9的标准答案是否相同。

(5）灵敏性分析点击模型窗口。

选择LＩNGＯ/Ｒanｇes模型的灵敏性分析报告检查输出结果与教材p90的标准答案是否相同。

结果分析可参阅教材p90－91。

2.实验5-2 奶制品的生产销售计划按以下步骤操作:（1)打开菜单“File”/“New”,新建模型文件。

（２)在模型编辑窗口输入模型（利用Linｇｏ编程语言完成）: (３）将文件存储并命名为sy4-2.lg４（记住所在文件夹）。

（４)求解模型。

(５）灵敏性分析。

检查输出结果与教材p９2-9４的标准答案是否相同。

结果分析可参阅教材p９4。

奶制品最优定价问题


E =需求降低百分数/价格提高百分数 EAB =A需求提高百分数/B 价格提高百分数
4 4.1
模型建立
问题一
1.根据模型假设1与假设2， 价格变化对消费需求产生反向单一影响， 同时本文中各类
产品的价格伸缩性及交叉伸缩性存在且稳定可计量，
E = ∆q/∆x EAB = ∆qA /∆xB (1)
2.同时消费者的消费需求可以完全满足及需求量等于消费量，因此我们可以得到文
奶制品的消费量即为其消费需求， 并等同于销售量；
6. 本题中将销售产品收入视为销售总收入的唯一来源，不涉及营业外收入及营业税金
扣除。其表达式为销售总收入=产品销售价格×销售量；
7. 本题中将产品购买支出视为消费总费用的唯一构成部分， 不涉及其他支出项。 其表达
式为消费总费用=产品消费价格×消费量；
8. 本题中消费总费用视为等同于销售总价格； 9. 本题中任何生产组合产生的余料剩余视为可利用资源， 不计入生产消耗与浪费； 10. 本题中经济代价表现形式主要为两项：销售总收入的减少额与生产耗费的增加额。 3.2
q 1x1 + q 2x2 + q 3x3 + q 4x4 ≤ 1939490000 xi <= xi0 3.同第一问， 第二问的最终目标为使销售总收入达到最大， 即存在如下目标函数： M ax = q 1x1 + q 2x2 + q 3x3 + q 4x4 4.模型简化
固定生产原料， 消费总费用不增加， 优化销售总收入 目标函数： maxY =
4 ∑
qi · xi
E1 (x1 − x10 ) q1 = q10 (1 − ) x10 E2 (x2 − x20 ) q = q (1 − ) 2 20 x20 E34 (x4 − x40 ) E3 (x3 − x30 ) q3 = q30 (1 + − ) x x 40 30 s.t. q4 = q40 (1 + E43 (x3 − x30 ) − E4 (x4 − x40 ) ) x30 x40 4 ∑ qi · xi ≤ 1939490000 i =1 xi <= xi0 x , p > 0, i = 1, 2, 3, 4

EXCEL用于生产决策的线性规划法

EXCEL用于生产决策的线性规划法朱建国会计系上海理工大学商学院上海市复兴中路1195号 64337978e-mail: zhjg@摘要：本文将介绍运用微软公司的Excel软件，来解决成本会计中生产决策的线性规划问题。

企业生产两种或两种以上产品时，如果这些产品在企业生产能力、经济资源利用上存在着相互影响的关系，如何确定生产产量一般可以使用先行规划方法予以解决。

一般在解线性规划问题时，多采用单纯形法，计算过程比较繁琐。

微软公司的Excel软件有专用的规划求解加载宏可以利用，本文就此问题予以探讨。

关键词：Excel 线性规划生产决策壹、引言线性规划是运筹学的一个重要组成部分，专门用来对具有线性联系的极值问题进行求解的一种现代数学方法。

所谓“线性”，是指所有变动因素的相互影响是直线关系。

即约束条件和目标函数都是呈线性关系的。

在成本管理会计中线性规划研究的问题主要有以下两类：对于已拥有一定数量的人力、物力和财力资源，研究如何合理使用，才能发挥它们的最大经济效益。

对于已确定的一项任务，研究如何统筹安排，才能以最少的人力、物力和财力资源去完成该项任务。

线性规划及其解法—单纯形法的出现，对运筹学的发展起了重大的推动作用。

许多实际问题都可以化成线性规划来解决，而单纯形法有是一个行之有效的算法，但是，单纯形法的手工计算对于会计人员来讲，仍然是一项比较繁杂的工作。

计算机的出现，为会计人员带来了一项非常有用的工具，使一些大型复杂的实际问题的解决成为现实。

微软公司的电子表格软件Excel提供的规划求解加载宏，“加载宏”是为EXCEL提供附加命令和函数的VBA模块。

可以非常方便的完成线性规划的求解任务（事实上该工具也可以处理其他规划问题）。

贰、提出问题企业在资源合理配置，产品品种决策等方面经常要使用线性规划法来求解，在产品生产安排的决策中，如果企业生产的产品品种有两种以上，且产品之间在资源需求、加工能力、市场需要等方面有一定的相互影响关系，就可以使用线性规划法进行决策，获得最优的产品生产数量，取得最大的边际贡献。

EXCEL求解线性规划问题


2) 在弹出的对话框中的“可用加载宏”列表框 中,选定待添加的加载宏“规划求解”选项旁 的复选框,然后单击“确定”.单击“确定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
单击“规划求解”按钮,将会出现以下规划求 解参数设置对话框
? 单击“添加”,显示添加约束对话框
或 （3）Format / sheet Rename 显示Rename sheet 对话框
（4）键入工作表名
2、工作表 Excel-2003 256列 65535 行 Excel-2007 16384列 1048576行 3、向单元格中输入数据 常数：不能改变的文字或其它数据
公式：输入到单元格中的数字值，或返回数字值的表达式。
延伸
下面对目标系数同时变动以及约束右端值同时变动的情况分别作延伸。 （1）目标系数 c同时变动的百分之百法则 ： 如果目标函数系数同时变动，计算出每一系数变动量占该系数同方向 可容许变动范围的百分比，而后将各个系数的变动百分比相加， 如果 所得的和不超过百分之一百，最优解不会改变 ；如果超过百分之一百， 则不能确定最优解是否改变。
（4）混合引用
格式： $A3 B$ 3
列是绝对的，行是相对的 列是相对的，行是绝对的
使用Excel 进行求解
1.关于“规划求解” 2.如何加载“规划求解” 3. “规划求解”各参数设置 4. “规划求解”步骤 5. 利用“规划求解”解线性规划问题
2.如何加载“规划求解”
1) 在“工具”菜单上,单击“加载宏”
到目标函数的值 ? 确定约束单元格输入公式，
计算每个约束条件左边的 值 ? 确定约束单元格输入公式， 计算每个约束条件右边的 值
在约束单元格中，需要填入计算约束函数值的公式。
  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

选择“规划求解加载项”，点击 “确定”。
“规划求解”加载项将出现在“数据”工具栏中。
实验要求 “猛牛”奶制品厂用牛奶为原料生产 A 和 B 两种奶制品。 一桶牛奶可以在 1 号 生产线上用 12 小时加工成 3 公斤 A 产品， 或者在 2 号生产线上用 8 小时加工成 4 公斤 B 产品。由于市场对 A、B 两种奶制品的需求非常旺盛，因此每天所生产的 A、B 两种奶制品都能全部出售。根据目前的市场情况，销售每公斤 A 产品可获 利 24 元，销售每公斤 B 产品可获利 16 元。“猛牛”奶制品厂每天能得到 50 桶牛 奶的供应量，每天工人的总劳动时间为 480 小时，并且 1 号生产线每天至多能够 加工 100 公斤 A 产品，2 号生产线的加工能力没有限制。请为该厂制定一个生产 计划。如何安排 A、B 两种奶制品的生产，能使每天获利最大？
“敏感性报告”(见图 1-6)提供关于求解结果对目标函数和约束条件微小变化 的敏感性信息。本实验是线性模型，在规划求解时必须将“规划求解选项”中的“采 用线性规划”选项打上钩，那么“敏感性报告”中将包含缩减成本、阴影价格、目标 系数以及右侧约束区域。如果采用非线性规划，则“敏感性报告”中将包含缩减梯 度和拉格朗日乘数。
4 x1 2 x2 480 。
因为一桶牛奶可加工成 3 公斤 A 产品或 4 公斤 B 产品，每天只能得到 50 桶 牛奶，所以原材的约束条件为：
x1 x 2 50 。又因为 1 号生产线每天至多只能加 3 4
工 100 公斤 A 产品，2 号生产线的加工能力没有限制，所以生产能力约束条件为
供应站 1 2 3 4 5 2 1.3 3 2.1 0.9 4 0.9 1.8 2.6 5 0.7 1.2 1.0 0.8 6 1.8 2.6 2.5 1.6 0.9
实验步骤 步骤 1：分析问题，确定解决方案。 本问题中，线路的走向必须在满足每个供应站都连上的基础上，使得所选的
线路的总距离最短，这种要求在保证满足目标函数最优的条件下，选中几个变量 的问题属于“选址问题”，可用“0-1”规划的方法来求解。 步骤 2：在 Excel 中建立模型框架。 建立如图 1-8 所示的参考模型。该模型分为 4 部分：第 1 部分为各供应站之 间的距离 (系数矩阵)；第 2 部分为与系数矩阵相似的规划矩阵，该矩阵的取值只 有 0 和 1 两种可能；第 3 部分是单位“人工费”、“材料费”等成本要素的单位值计 算出的每公里成本（180=30 人工费+50 材料费+100 其他费用） ；第 4 部分为由系 数矩阵和规划矩阵运算后得到的所选线路的总距离，以及由每公里成本与总距离 的乘积，即铺设管道的总成本。
例如， 我们使用第 10 行单元格进行时间约束表达。 实际量代表在目前决策变 量 x1 和 x 2 取值条件下，不同产品生产总共消耗的时间。其取值为 60，60 这个值 不能够直接输入数值，必须使用函数表达“=SUM（C10:D10）”，以表示总生产和 特定品种生产量之间的数学关系。该函数表达的含义是，生产需要消耗的时间为 A、B 两种奶制品生产加工时间之和。其中，奶制品 A 在现有产量下的生产时间 为 40，40 这个值同样不能够直接输入，需要使用函数表达“=C9/C3*C4”，其含义 表达为“生产 10 公斤奶制品 A 需要消耗的时间”。 再一次强调，在线性规划优化模块中，呈灰色的单元格中的数据全部都是使 用函数进行表达，不能过直接输入数据。 在图 1-1 中，决策变量 x1 和 x 2 所在单位格为 C9、D10，其初始值可以随意设 置，直接填写。在本例中我们直接填写为 10。在使用“规划求解”加载项进行求解 后，决策变量所在单元格的数值会自动发生变化。 步骤 3：参考图 1-2，检查模型中各单元格的计算公式。
实验步骤 步骤 1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条件 函数。 本生产计划是要解决如何安排 A、B 两种奶制品生产数量，才能获利最大， 因此目标变量为总利润，设为 y。决策变量为 A、B 两种奶制品生产数量，设 x1 和
x 2 分别为 A、B 奶制品的产量(公斤) ，显然 x1 和 x 2 必须大于等于 0。
图 1-6 敏感性报告
可变单元格$C$9 和$D$9 允许的增量为 8 和 2，允许的减量为 2. 667 和 4，说 明决策 变量可允许的变化范围分别为(60-2.667，60+8)和(120-4，120+2)。约束变 量单元格$E$9、$E$10 和$E$11 允许的增量和减量说明生产能力、工时和原材料 供应量可允许的变化范围分别是（ 60-40 ， 60+1030 ） 、 (480-80 ， 480+53.333) 和 (50-6.667，50+10)。 总产量的影子价格为 0，说明增加生产能力对总利润无影响；工时的阴影价 格为 2，说明每增加一个单位的工时，可以使总利润增加 2；原材料的阴影价格为 48，说明每增加一个单位的原材料供应量，可以使总利润增加 48。由此可看出原 材料供应量对总利润的影响较大。如果现在有多余资金可以增加人力和购买原材 料，这时应该优先考虑人力还是原材料，除了考虑阴影价格外，还应该考虑各自 的单位成本。阴影价格与单位成本的比值较大的资源就是应该优先考虑购买的资 源。另外，当资源限制发生改变，求解出新的最优解后，各资源的阴影价格也可 能会有相应的变化，这时应该重新生成规划求解报告进行分析。 “极限值报告”（见图 1-7）列出目标单元格$E$12 最大值为 3360，可变单元
B 8 9 10 11 12 13 C 奶制品 A（1 号生产线） 10 安排生产数量（公斤） =C9/C3*C4 所需要工时（小时） 折算为需要的牛奶 （桶） =C9/C3 =C9 *C5 所获利润（元） D E F 限额 100 480 50 奶制品 B （2 实际量 号生产线） 10 =D9/D3*D4 =D9/D3 =D9 *D5 =C9 =SUM （C10:D10） =SUM （C11:D11） =SUM （C12:D12）
图 1-2 参考模型中的公式 步骤 4：设置规划求解的各项参数并求解。 在步骤 2 中完成的工作主要是表达各个变量之间的函数关系，构造线性方程 中左侧的函数表达式。 而方程右侧的约束关系则使用“规划求解”加载项进行表达， 并进行决策变量的最优解求解。 选择“工具”中的“规划求解”选项，参考图 1-3，设置求解此问题的规划求解的 各项参数。其中$E$12 为目标函数的因变量，$C$9：$D$9 为决策变量，$E$9： $E$11<= $F$9: $F$11 为约束条件。
图 1-3
规划求解参数对话框
图 1-4 பைடு நூலகம்划求解后的结果
步骤 5：分析求解结果，仔细阅读"运算结果报告"、"敏感性报告"与"极限 值报告"，并理解报告中的各项内容。 求解后发现：当 1 号生产线安排 60 公斤的生产量且 2 号生产线上安排 120 公斤的生产量时，“猛牛”公司的获利最大，达到 3360 元，见图 1-5。 在单击"确定"规划求解按钮前，可以选择 “运算结果报告”、“敏感性报告”与 “极限值报告”进行保存。报告中包含了许多对管理决策有用的信息，基于这些信 息，可以帮助管理人员挖掘资源利用潜力，达到节能增效的目的。 “运行结果报告”列出目标单元格$E$12 在规划求解之前的初始值是 3360；可 变单元格$C$9 和$D$9 在规划求解之前的初值都是 10，规划求解以后的终值分别 为 60 和 120。在该报告的约束区域中显示每个约束条件的公式，当前值和是否达 到限制值。
因为每公斤 A 产品可获利 24 元，每公斤 B 产品可获利 16 元，那么目标函数 即为 y 24 x1 16 x2 。 由于 1 号生产线可用 12 小时加工成 3 公斤 A 产品，因此生产每公斤 A 产品 所需要的时间为 4 小时；同理，2 号生产线生产每公斤 B 产品所需要的时间为 2 小 时 ， 生 产 两 种 产 品 的 总 时 间 不 能 超 过 480 小 时 ， 工 时 的 约 束 条 件 为
实验目的 理解"选址问题"的规划模型； 掌握在 Excel 中构造 0-1 规划模型的方法； 掌握用“规划求解”工具正确求解选址问题的步骤。
实验环境 Microsoft Office Excel 2003（2007） ； 通过 Excel 中的“加载宏”加载“规划求解”工具。
实验要求 由于“猛牛”奶制品厂的产品在市场上畅销，为了有利于原料的及时获得和质 量控制，工厂决定对其 6 个原料供应站铺设管道输送牛奶，6 个供应站相互间的 距离如表所示。已知：1 号供应站离工厂的距离为 5 公里，每铺设 1 公里管道的 成本为人工费 30 万元、材料费 50 万元、其他费用 100 万元。请设计从 1 号供应 站开始铺设管道，把各供应站连接起来的铺设方案，使建设总成本最低。 表 1-1 各供应站间距离
图 1-5 运算结果报告
对于本例，可以发现所需工时和原材料牛奶的需求量都已达到限制值，而总 产量未达到限制值，说明生产能力还有剩余。因此，如果要提高总利润，必须增 加工时和原材料的可供量，若增加生产能力则对总利润无影响，相反生产能力可 以减少 40， 然能达到当前最大总利润。第 4、第 5 约束条件其实是两种产量的非 零约束，“状态”栏中“未到限制值”只说明两个决策变量的求解结果都满足大于 0 的条件。
格$C$9 和$D$9 的最优解分别为 60 和 1200。$C$9 的上、下限(60 和 0)分别表示 在满足约束条件和保持其他可变单元格 $D$9 的值不变的情况下，可变单元格 $C$9 可以取到的最大、最小值。同样，$D$9 的上、下限(120 和 0)分别表示在满 足约束条件和保持其他可变单元格 $C$9 的值不变的情况下，可变单元格$D$9 可以取到的最大、最小值。
实验 1 奶制品厂生产/销售的最优化决策
实验 1-1
“猛牛”奶制品厂生产计划制定