Excel新技能 -- 产能与销售策略最优决策分析

特别声明：本文版权归原创作者所有

说起线性规划，对于那些没有接触过的人来说，似乎总带着一股神秘感。而对于那些大学里深受运筹学之苦的人来说，一提起线性规划求解，仿佛一瞬间就回到了那个痛苦的夏天。

然而，线性规划很神秘吗？求解线性规划真的有那么痛苦吗？其实，自打我们有了Excel这柄利剑以后，求解线性规划简直就和切瓜吃瓜一样的简单啊！

在供应链优化领域中，线性规划应用之广简直超出我们的想象。无论是运输路径优化、调度问题还是仓库选址，线性规划简直是物流规划中老干妈一样的存在！只要你想的到的，基本都可以用线性规划解决。更别提用它解决那长年高悬我们头顶的两大目标（流程优化、降低成本）了。

今天，我们就来介绍一款可以提升你影响力，直接从苦逼的运营biu变为金光闪闪战略BP的线性规划应用。

对于企业来说，只要有生产，只要有销售，就一定有其相应的制约因素。所以线性规划的要点就在于，搞清楚你要什么以及那些限制你达成目标的条件！

回到我们的标题，我们要影响销售策略的调整，我们要优化生产资源的配置，那么我们的目标是什么？

对！很对！我们要追求利润最大化！我们要用有限的生产资源生产那些好卖又赚钱的产品！

首先，我们先搞明白，哪些产品比较赚钱。这个简单，把所有SKU的毛利率拉一边瞅瞅。

下一步！

目标：利润最大化 。

将目标转化为数学式：

P_i：产品单件利润    X_i：产品销售量

知道了利润率，也搞清楚了目标，那么下一步就是明确实现这个目标的约束条件。

对于一个生产型企业来说，一定有一个环节是限制其产能增长的因素，找出这个因素，并把它数学化。

某企业的生产线一共有1条生产线，该生产线有23个加工设备，这23个设备均可以生产加工以上6个SKU的产品，只是每个产品的加工时间不一样。于是我们根据每个SKU产品每件加工时间推算，这个产品对于产线的占用率进行计算，得出以下数据：

如果我们的优化结果是以半年为区间，那么对于这个6个SKU的产品来说，生产的限制约束则为：6月*30天*24小时= 4320小时。那么结果就是：

除了生产制约因素，我们还需要考虑每个产品在市场的最大销售量。由于不同产品处于不同的产品周期，故其在市场上的接纳度是完全不同的。有的产品已经处于稳定期，必须确保每月一定的供给量，而有的产品作为新品，尚未完全打开市场，近半年的需求会有一定限制。

如是，得出以下市场相关的制约条件：

现在，我们只需要用Excel对这道线性规划进行求解就大功告成了。

打开Excel选择，在加载项最下方的管理处，点击“转向”（英文版：“GO”）

选择“规划求解加载项”（英文“Solver Add-in”）

然后你就会在你的Excel的数据模块，找到线性规划求解的按钮

好啦，我们开始解题吧！轻轻松松2步解完全题！

首先，先把各数据码好，把求解公式一起各限制条件的值放上。

然后，点击线性规划求解，填入求解内容。

在1之处，输入我们的求解目标：利润

在2之处，选择Max，因为我们是要最大化利润哦

在3之处，选择可以变动的值，把范围选在各产品最大件数之处

在4之处，根据限制条件，将限制调节输入

在5之处，打钩，使无约束变动为非负数

在6之处，选择简单线性规划

在7之处，点击求解！

Duangduang，优化结果已出。

产品5，产品6没有市场最低供给的限制且利润较低，所以不建议生产。而产品1虽然利润高，但由于需求有限，则按照其最大需求进行生产。产品2与产品3，虽然利润还不错，但是占用生产资源也较多故仅满足其最低供给需求即可，剩余的产能全力生产产品4，则可以最大化公司利润。

好啦！结果已出，带着它好好和老板谈谈去吧！

相关好文如下，点击即可阅读ABC分类库存控制法：不手动排序，如何用Excel进行ABC分类如何通过EXCEL建立采购成本的分析表物料计划中如何使用Excel实现一对多反向查询功能有了ERP 软件，供应链人为什么还依赖 Excel 表格？供应链神人：不用系统也能处理海量订单如何用Excel做供应链网络优化（通熟易懂）用Excel进行供应链数据分析：Data菜单用Excel进行供应链数据分析：多图形动态选择