石河子大学：《运筹学》课程教学资源（实验指导）使用Excel进行灵敏度分析

（三)使用Excel进行灵敏度分析 实验目的：掌握使用Excl软件进行灵敏度分析的操作方法。 实验内容： 用Exce1软件完成本章P67-P72页例题例6-例9，教材P79习题2.8 操作步躁： (1)建立电子表格模型： (2)使用Exce1规划求解功能求解问题并生成“敏感性报告”； (3)结果分析。 作业练习 用Excel软件完成P82页案例2.1： 经理会议建议的分析 某公司生产三种产品A1、A2、A3,它们在B1、B2两种设备上加工，并耗用C1、C2 两种原材料，已知生产单位产品耗用的工时和原材料以及设备和原材料的最多可使用量如表 2-21所示。 表221生产三种产品的有关数据 资源 产品A1产品A2产品A3每天最多可使用量 设备B1(min) 2 430 设备B2(min) 0 460 原料C1(kg) 0 420 原料C2(kg) 1 1 300 每件利润（元） 30 20 50 已知对产品A2的需求每天不低于70件，A3不超过240件。经理会议讨论如何增加公 司收入，提出了以下建议

（三） 使用 Excel 进行灵敏度分析 实验目的: 掌握使用 Excel 软件进行灵敏度分析的操作方法。 实验内容: 用 Excel 软件完成本章 P67-P72 页例题例 6-例 9，教材 P79 习题 2.8 操作步骤： （1）建立电子表格模型； （2）使用 Excel 规划求解功能求解问题并生成“敏感性报告”； （3）结果分析。 作业练习 用 Excel 软件完成 P82 页案例 2.1： 经理会议建议的分析 某公司生产三种产品 A1、A2、A3，它们在 B1、B2 两种设备上加工，并耗用 C1、C2 两种原材料，已知生产单位产品耗用的工时和原材料以及设备和原材料的最多可使用量如表 2－21 所示。 表 2－21 生产三种产品的有关数据 资 源 产品 A1 产品 A2 产品 A3 每天最多可使用量 设备 B1（min） 1 2 1 430 设备 B2（min） 3 0 2 460 原料 C1（kg） 1 4 0 420 原料 C2（kg） 1 1 1 300 每件利润（元） 30 20 50 已知对产品 A2 的需求每天不低于 70 件，A3 不超过 240 件。经理会议讨论如何增加公 司收入，提出了以下建议：

(a)产品A3提价，使每件利润增至60元，但市场销量将下降为每天不超过210件 (b)原材料C2是限制产量增加的因素之一，如果通过别的供应商提供补充，每千克价格 将比原供应商高20元： (c)设备B1和B2每天可各增加40min的使用时间，但相应需支付额外费用各350元 (d)产品A2的需求增加到每天100件： (e)产品A1在设备B2上的加工时间可缩短到每件2min,但每天需额外支出40元。 分别讨论上述各条建议的可行性，哪些可直接利用“敏感性报告“中的信息，哪些需要重 新规划求解。 操作步骤 (1)建立电子表格模型： 解：假设三种产品A1、A2、A3每天产量为x1,x2,x3件，则原问题的线性 规划模型为： H 产品A1产品A2产品A3 每件利面30 20 50 单位产品耗用的工时和原材料 实际使用 可使用量 43 s30 产晶A1产品42产品A3 每天产量 (2)使用Excl规划求解功能求解问题并生成“敏感性报告”：

（a）产品 A3 提价，使每件利润增至 60 元，但市场销量将下降为每天不超过 210 件； （b）原材料 C2 是限制产量增加的因素之一，如果通过别的供应商提供补充，每千克价格 将比原供应商高 20 元； （c）设备 B1 和 B2 每天可各增加 40 min 的使用时间，但相应需支付额外费用各 350 元； （d）产品 A2 的需求增加到每天 100 件； （e）产品 A1 在设备 B2 上的加工时间可缩短到每件 2 min，但每天需额外支出 40 元。 分别讨论上述各条建议的可行性，哪些可直接利用“敏感性报告”中的信息，哪些需要重 新规划求解。 操作步骤： （1）建立电子表格模型； 解：假设三种产品 A1、A2、A3 每天产量为 x1,x2,x3 件，则原问题的线性 规划模型为： （2）使用 Excel 规划求解功能求解问题并生成“敏感性报告”；

案例2 产品A1产品A2产品A3 每件利润 20 50 位产品耗用的工时和原材：实际使用 可使用量 设备B1 370 430 沿各B2 3 02 460 460 原科C1 1 0 280 420 原C2 1 1 1300 300 产品A1产品A2产品A3 总利润 每天产量 70 20 12900 ,11.0敏感性报告 E F I G IH II 离竖：0品14：49：40 可变单元格 单元格 名子 簷来的的 每天产 501E+30 约束 单元格 名字 瘙界装的的 1E+30 30 21 百品A3 230 2401E+30 10 (3)结果分析： a.由于产品A3的产量为230件，大于提价后的市场销量210件，所以不可以直 接利用“敏感性报告”中的信息，需要重新规划求解。产品A1每天生产13.33 件，产品2每天生产76.67件，产品A3每天生产210件，总利润最大为14533 元，因此可采用这条建议。 b.可直接利用“敏感性报告”中的信息，原材料C2的影子价格为50，但其允许 的增量为0，因此，通过别的供应商提供补充原材料的C2的可使用量，不可能 增加公司收入，因此不要采用这条建议。 c.可直接利用“敏感性报告”中的信息，但设备B1,B2的影子价格为O,每 天增加4OMin的使用时间，对利润没有贡献，不要采用建议

（3）结果分析： a.由于产品 A3 的产量为 230 件，大于提价后的市场销量 210 件，所以不可以直 接利用“敏感性报告”中的信息，需要重新规划求解。产品 A1 每天生产 13.33 件，产品 2 每天生产 76.67 件，产品 A3 每天生产 210 件，总利润最大为 14533 元，因此可采用这条建议。 b.可直接利用“敏感性报告”中的信息，原材料 C2 的影子价格为 50，但其允许 的增量为 0，因此，通过别的供应商提供补充原材料的 C2 的可使用量，不可能 增加公司收入，因此不要采用这条建议。 c．可直接利用“敏感性报告”中的信息，但设备 B1，B2 的影子价格为 0， 每 天增加 40Min 的使用时间，对利润没有贡献，不要采用建议

d.可直接利用“敏感性报告”中的信息，但产品A2产量的影子价格为-30，允 许的增量为35，所以总利润变化量为(100-70)*(-30)=-900，因此，不能增 加公司收入，反而减少，不建议采用。 。.对于工艺系数改变，“敏感性报告”中没有相关信息，所以不可以直接利用“敏 感性报告”中的信息，需要重新规划求解。重新求解结果不变，因此不建议采用 此建议。 因此，能采纳的建议只有a

d. 可直接利用“敏感性报告”中的信息，但产品 A2 产量的影子价格为-30，允 许的增量为 35，所以总利润变化量为（100-70）*（-30）=-900，因此，不能增 加公司收入，反而减少，不建议采用。 e. 对于工艺系数改变，“敏感性报告”中没有相关信息，所以不可以直接利用“敏 感性报告”中的信息，需要重新规划求解。重新求解结果不变，因此不建议采用 此建议。 因此，能采纳的建议只有 a