EXCEL在复杂存货订购批量决策中的应用.docVIP

EXCEL在复杂存货订购批量决策中的应用-会计 EXCEL在复杂存货订购批量决策中的应用 从亮 摘要：在企业采购的存货陆续到达、供应商提供数量折扣的情况下，传统通过手工计算来确定存货的经济订购批 量计算过程相当繁琐。本文借助通用财务软件EXCEL，利用其规划求解功能，设计了相关的具体方案，并解决了存在数 量折扣、陆续到货、多品种同时采购的情况下，各种材料经济订货批量的确定难题，改进了传统存货模型下的经济订货 批量确定方法，提高了企业存货采购决策的效率。 关键词 ：陆续到货；数量折扣；规划求解；存货订购批量 企业存货决策所要解决的主要问题是：企业应该怎 样采购存货？这包含两方面的内容，首先是应当订购多少 存货；其次是应当在何时开始订货。进行有效的存货订购 批量决策对于企业维持正常生产经营，合理控制资金占 用水平，降低营运成本具有重要意义。本文主要解决第一 个问题即企业应当采购多少存货，主要利用excel的规划 求解功能，阐明存在数量折扣、陆续到货的情况下，如何 决定存货的订购批量。 一、存在数量折扣、陆续到货条件下的基本模型 存货的最优经济批量（EOQ）是指企业选择合理的采 购批量与采购时间，使存货相关总成本最低时的采购量， 与存货相关成本主要包括采购成本、储存成本、订货成本 和缺货成本。基本模型只考虑了其中的储存成本和订货 成本，与现实情况大多不相符合。在这里在放宽基本模型 的两项假定：第一，企业采购的全部存货没有一次到位 （存货陆续到达）；第二，不同的采购数量有不同的采购单 价（存在数量折扣）；同时假定不存在缺货。由于供应商实 行了数量折扣，陆续到货条件下的决策模型中涉及的成 本除了订货成本和储存成本之外，采购成本也成了决策 中的相关成本。 考虑数量折扣、陆续到货等条件的经济订货批量模 型为：T= 采购成本+ 订货成本+ 储存成本=D×U× （1-S）+D/Q×K+ Q /2× (1-d/P) ×C。式中：U 为采购单 价，D为全年存货需要量，Q 为订货批量，S 为数量折扣， K为每次订货成本，d为每日消耗量，P每日送货量。决策 目标是找到使总成本T最低时的采购批量。 二、存在数量折扣、陆续到货条件下的规划求解模型 的建立 例：大华公司拟采购甲、乙、丙三种存货，存货将陆续 到达。另外供应商提出的数量折扣的条件是：甲材料订货 数量大于或等于400 千克，折扣2%；乙材料订货数量大 于或等于450 千克，折扣4%；丙材料订货数量大于或等 于500 千克，折扣3%；丁材料订货数量大于或等于500 千克，折扣6%；企业存货基本数据情况见下表： 要求：确定甲材料、乙材料、丙材料、丁材料的最佳订 购批量。 首先，设定目标函数。设四种材料相关的总成本为 T，Di为某种材料的全年总需要量，Qi为某种材料的订货 量，Ki为某种材料的一次订货成本，Ui 为某种材料的单 价，Si为某种材料的数量折扣，di为某种材料的每日消耗 量，P每种材料的每日送货量。则目标函数总成本：T= Σ [Di×Ui×（1-Si）+Di/Qi×Ki+ Qi /2× (1-di/Pi) ×Ci],（i=1, 2,3，4）。 其次，明确约束条件。条件一：甲材料订货数量大于 或等于400 千克，Q1=400 千克；条件二：乙材料订货数 量大于或等于450 千克，Q2=450 千克；条件三：丙材料 订货数量大于或等于500千克，Q3=500千克；条件四： 丙材料订货数量大于或等于500千克，Q4=500千克。 最后，求最优解。这时决策就转化为：去找到相关总 成本T最低时的Qi（i=1,2,3，4）。 三、存货订购批量决策在EXCEL中的实现 下面借助于excel2003的规划求解功能，举例说明该 模型下多种产品存货订购批量的决策实现刚过程。 1.基本数据区的设计 把上表中的四种材料基本数据输入到B5:E11 单元 格中，见下图。 2.最优订货批量规划求解分析区域的设计 单元格A16:E24 是四种材料的规划求解分析区域， A24:B24为目标函数的设计区域。采购成本、储存成本、 订货成本的公式见下图；目标相关总成本为B24 单元 格，公式为：=B5*B11*(1-B10)+B5/B17*B6+0.5*(B17-B17/ B8*B9)*B7+C5*C11*(1-C10)+C5/C17*C6+0.5*(C17-C17/ C8*C9)*C7+D5*D11*(1-D10)+D5/D17*D6+0.5