基于Excel的最优化敏感性分析——现代管理中的基本方法

背景

G宝石加工工具公司是一家私营公司，参与宝石加工工具的消费与行业市场的竞争。除了位于中国四川的主要制造设备外，G公司还经营着位于东南亚的其他几个制造工厂。这些工厂生产的整套产品范围包括电钻、电锯、钉子枪以及手持工具，如锤子、螺丝刀、扳手和钳子。为了简单起见，让我们假设越南地区的工厂仅生产扳手和钳子。扳手和钳子是由钢铁制造的，并且制造过程包含在浇铸机上浇铸工具，然后在装配机上装配工具。用于生产扳手和钳子的钢铁数量和每天可以得到的钢铁数量见下表。下两行是生产扳手和钳子所需要的机器使用率以及这些机器的生产量。最后，表的最后两行说明每天这些工具的需求量和这些变量(每单位)对盈利的贡献。

扳手	钳子	可获得的资源数量
钢铁(千克)	0.75	0.5	每天13000千克
浇铸机(小时)	1	1	每天21000小时
装配机(小时)	0.3	0.5	每天9000小时
需求限制(件/天)	15000	16000
盈利贡献(人民币/千件)	910	700

问题：

• 为了使对盈利的贡献最大化，应该计划每天生产多少件扳手和钳子?
• 根据这个计划对盈利的总贡献将是多少?
• 这个计划中，哪些资源将是最关键的?

最优化求解

根据方程，确立基本条件

然后建立约束条件

列出算式，结果如下所示：

其中绿色部分是自由变量；黄色模板是目标函数，蓝色部分是约束条件。使用Excel进行求解：

因为是利润问题，所以是最大化。遵守约束，见约束条件部分。为了便于进行敏感性分析，使用单纯线性规划法进行求解。如果采用非线性GRG，会出现拉格朗日乘数，从更好理解的角度分析，单纯线性规划法更加恰当。

灵敏性分析

一个约束的影子价格(shadow price)是当该约束的RHS值增加一个单位，而所有其他约束保持不变时的优化目标函数值发生变化的数量。有时，影子价格被称为双重价格、边际价格，或者有时称为边际成本或边际值，

控制影子价格的一般原则：

• 每个约束的一个影子价格，每一个合理约束都有一个影子价格
• 影子价格的单位。影子价格的单位是目标函数的单位除以约束的单位。
• 影子价格的经济信息。对于给定约束的影子价格可用数学的方法得到它的数值，并具有特定的经济意义。
• 用微观经济学理论叙述影子价格。根据微观经济学的理论，给定约束的影子价格是资源的“边际值”，它的单位用约束来表示。
• 用微积分学描述影子价格。给定约束的影子价格从微积分学的传统意义上来说可以看做是一个导数。影子价格f（x）的导数=△最优目标函数值/△(RHS)值
• 用拉格朗日乘数描述影子价格。在经济学中，拉格朗日乘数被解释为资源的影子价格，即资源增加一个单位时，目标函数值会改变多少。

使用Excel进行敏感性分析

Excel会自动生成敏感性分析结果，效果如下图所示：

对于上半部分，意思是，当扳手的生产最优为10000，系数为利润，每一个利润为0.91元。【10000-0.21，10000+0.14】为利润基本线，在该区间变动扳手，模型不用改变，利润不用重新计算。

对于下半部分，其中阴影价格钢铁和浇注机大于0，所以这两个条件属于硬性约束，而装配件和需求限制则没有得到充分利用。而阴影价格0.84，意思是当钢铁（千克）条件限制增加或减少1千克，公司愿意为这一千克所付出或增加的成本，或者称为拉格朗日乘数。

增加1kg，刚好产生0.84元的利润，不高。

知识点

• 使用Excel进行规划求解
• 使用Excel规划工具生成敏感性分析报告
• 解读Excel生成的敏感性分析