基于电子表格的企业配产最优化模型——从理论到实操分析——30分钟轻松掌握电子表格线性规划
背景
N钢铁厂钢铁生产中，炼焦煤是一种必备的原材料。现在正是为下一年生产制定计划的时候，因而N厂的煤炭供应经理张三已经征求接受来自8个可能的煤炭矿业公司下一年的订单出价。如下所示：
| A1 | A2 | A3 | A4 | A5 | A6 | A7 | A8 | |
|---|---|---|---|---|---|---|---|---|
| 价格(人民币/吨) | 346.5 | 350 | 427 | 444.5 | 465.5 | 497 | 507.5 | 560 |
| 联合/非联合 | 联合 | 联合 | 非联合 | 联合 | 非联合 | 联合 | 非联合 | 非联合 |
| 卡车/铁路 | 铁路 | 卡车 | 铁路 | 卡车 | 卡车 | 卡车 | 铁路 | 铁路 |
| 挥发性(%) | 15 | 16 | 18 | 20 | 21 | 22 | 23 | 25 |
| 生产能力(千吨/年) | 300 | 600 | 510 | 655 | 575 | 680 | 450 | 490 |
上表说明A1公司向N厂以346.5人民币/吨的价格供应炼焦煤，它与N厂存在联合关系，A1厂的运输为铁路，挥发性为15%，A1厂的生产能力为300千吨/年。
为了满足工艺要求，炼焦煤必须达到一个平均19%的挥发性。
根据市场预测和工艺要求特征，N厂预测下一年需要炼焦煤1225千吨。为照顾关联关系，N厂计划从关联公司获取至少50%的炼焦煤。另外因为运力限制，铁路运输的炼焦煤数量限制在每年 650千吨，用卡车运输的炼焦煤数量限制在每年 720千吨。
作为管理者的张三，需要回答下列三个问题:
-
为了使供应炼焦煤的成本最小化，N厂应该与每个供应商签订多少煤炭的供应量?
-
N厂的总供应成本是多少?
-
N厂的平均供应成本是多少?
案例分析
管理者张三必须思考N厂焦煤供应成本最小化的供应计划。其中，A1公司价格最便宜，但是其挥发率也最低，不能够满足要求。简单而言，可以要求每家公司供应的焦煤必须超过19%，也可以考虑通过混合来实现19%的挥发性，因为A1公司焦煤只用提升4%即可满足要求。
Excel求解
第一是项目背景，需要将题干意思进行转化，结果如下：
第二是决策变量的处理，分别A1-A8的产量定义为X1-X8，其中绿色部分标记为决策变量可以变动：
接着是目标函数，目标函数是产量乘以价格，具体公式为=SUMPRODUCT(C4:J4,C12:J12)，黄色部分代表关注的结果：
再次是约束条件转化，因为前面的挥发性、联合、卡车和铁路都存在约束，虽然在理论部分已经分析了，但是如果手输不太方便，最好采用表格将系数确定下来。在这里挥发约束部分是手动输入的，而联合约束、卡车铁路采用了if函数进行处理，分别是：=IF(C5=“联合”,1,0)和=IF(C6=“铁路”,1,0)，这样就可以快速将文本转化为数值，方便后续的求和计算。
第六是约束条件，将前面的约束条件全部转化为表格，得到结果如下。在这里需求约束采用的是SUM，挥发性、联合、铁路和卡车采用Sumproduct，比较方便快捷。
最终模型效果如下：
模型建立好后，需要进行求解处理，选择数据部分的规划求解，参数设定如下，记得要选最小值。
求解过程如下：
最终得到结果为A1工厂购买5.5万吨，A7工厂购买45万吨，A8工厂不购买，具体求解结果见绿色部分，此时满足每年1225千吨、挥发性、联合采购、交通运输的各项约束要求，而且成本最低为51287.25万元。其平均供应成本为：418.67元/吨。
知识点总结
-
构造线性规划模型的要点是确定决策变量、确定目标函数、确定约束。
-
要将除法问题转化为乘法问题降低出现0的可能。
-
采用电子表格规划求解模型进行参数求解。