Excel数据分析与决策案例：制定满足条件的最优生产计划方案
数据分析与量化
某轴承公司生产两种不同的电子组件，即322A和802B，公司在接下来的3个月里都会通知该公司的销售人员每个星期的需求量，下图是刚刚接到的未来3个月的订单：
接到订单后需求报告将会送到生产布，该部门必须制定未来3个月的组件生产计划，需要考虑生产总成本、库存持有成本和改变生产负荷导致的费用。
根据以往数据，生产一个322A组件的成本为20，生产一个802B组件的成本为10.每月的基本库存持有成本占据总成本的1.5%。成本与生产负荷的水平有关系，生产负荷增加一个单位时新增的成本为0.5；生产负荷减少一个单位时成本减少0.2.假定3月份322A的生产量为1500，802B的生产量为1000。
322A组件的初期库存为500，6月最小安全库存为400；802B的初期库存为200，6月最小安全库存为200。
同时员工的工作时长、机器的最大生产能力和库容等因素均是限制生产的约束条件，如下图所示：
生产部门需要在以上条件下制定出满足客户需要的最佳生产计划方案。
在满足客户需求的基础上生产计划的安排会直接影响总成本的变化，成本最优是公式的目标，因此设置总成本为目标变量。
需要做的决策是如何来安排生产计划，因此决策变量是每月322A和802B两种产品在未来3个月的生产计划，共6个决策变量。
该模型中共有4个约束条件，即6月末最小安全库存限制、每月总库容限制、机器和人工能力限制。由于有些约束条件涉及每个月，因此每项约束条件展开可能有多项，如机器能力限制3个月就应是3个约束条件
由于案例中未来几个月的订单数量已知，因此模型暂时不需要假设变量。
总成本由3个方面组成：一是生产成本，即生产所产生的成本；二是库存成本，即存放在仓库里的成本；三是生产负荷变化成本，即每个月与上月的负荷变量导致的成本的变化。因此总成本与生产计划息息相关，逻辑关系如下：
生产成本=每月322A组件生产件数*单位成本+每月802B组件生产件数*单位成本。
库存成本= 每月322A组件生产件数*库存单位成本+每月802B组件生产件数*库存单位成本。
变化成本=（本月生产总量-上月生产总量）*变化单位成本。这里要注意的是，如果是减少，则总量为负，再乘以变化单位成本，即总量减少该项目成本对总成本由贡献，因此，需用if函数进行逻辑判断。
单元格H2:K4区域设置未来3个月的交货计划，即市场的需求量。
单元格H7:K9为未来3个月的生产计划，即决策变量区域。由于要考虑生产负荷变量对成本的影响，因此需要根据交货计划和生产计划计算出每月的变化量。在K9单元格输入公式=J8+J9-I8-I9，其他月份依此类推。
单元格H15:K19区域用于计算生产总成本，即预测变量区域；除此之外，还需设置约束条件，在单元格B3:F19为模型的约束条件。
模型的逻辑关系为：根据交货计划->确定生产计划->确定每月库存->确定总成本。
可以看出在满足所有约束条件下，预测变量“总成本”的最小值接近225300元。
我会在知识星球里面分享300个excel数据分析案例，目录：
数据分析与量化
商业分析案例、商业分析课程与咨询
