最优订货批量决策模型.docVIP

excel应用技巧——最优订货批量决策模型 (2009-05-14 21:41:07) 转载 标签： 订货成本 储存成本 材料成本 采购成本 规划求解 分类： 专业写作 美林公司每年需要甲乙丙丁四种材料，相关资料如下所示，问最优经济订货批量是多少？ ? ? （附：若想进货时获得折扣机会，必须满足甲材料每次订货数量》=400，乙》=450，丙》=500.丁》=500） ?当我们在工作当中真正遇到这样一个问题时，我们的计算方法可能会有很多，但大多数都会很麻烦，这时候我们可能会需要一种很简便的方法，设计一种模型，不论数据如何变化我们都能以不变应万变，那么现在我就给大家介绍一种模型： ? 要想做这个模型首先我们必须了解几个非常关键的点： 1、采购这些材料我们需要计算那些成本？ ?? 答案:采购成本、储存成本、订货成本 2、这些成本是如何计算的？ ?? 答案：采购成本=材料总需求量*单价*（1-折扣率）? 如果没有折扣率的话则用前两项直接相乘 ?????????储存成本=（最优订货批量/2）*（1-每日耗用量/每日送货量）*单位储存成本 ???????? 订货成本=（材料总需求量/最优订货批量）*每次订货成本 ???????? 总成本=采购成本+储存成本+订货成本 ???????? 综合成本=A材料成本+B材料成本+C材料成本+D材料成本 3、如何将这些公式合理设计人模型中？ ?? 答案：（如图所示） ? 我们可以先做这么一张表? 把要写的填上 表格做好了? 如何编辑这些公式呢？ 其中紫色区域和绿色区域是我们所需要求解的区域，其他地方都是需要编写公式的 具体公式我们在上面已经讲到了，现在我们具体讲一下： B17=采购成本=材料总需求量*单价*（1-折扣率)=B4*B10*(1-B9) ?? 选中向后填充至E17 B18=（最优订货批量/2）*（1-每日耗用量/每日送货量）*单位储存成本=(B16/2)*(1-B8/B7)*B6 ?? 选中向后填充至E18 B19=（材料总需求量/最优订货批量）*每次订货成本=B4/B16*B5 ?? 选中向后填充至E19 B20=总成本=采购成本+储存成本+订货成本= B17+B18+B19 ? ? 选中向后填充至E20 B21=综合成本=SUM(B20:E20) B22=最佳订货次数=总需求量/最优订货批量=B4/B16 ?? 选中向后填充至E22 B23=最佳订货周期(月)=12/最佳订货次数=12/B22 B24=经济订货量占用资金=（最佳订货批量/2）*单价=B16/2*B10 ? 到这里我们已经把全部要写的公式都填写完毕了 ? 最后进行规划求解： 1、选中目标单元格C21 2、执行工具---规划求解---弹出 目标单元格就是要求综合成本----C21 可变单元格就是要求的最优订货批量B17---E17 约束条件见上（填入） ? 到这里我们所有工作都做好了?点击求解? 点确定即可最终完成。 ? ?