Excel在存货管理决策中的应用

Ｅｘｃｅｌ在存货管理决策中的应用(2008-07-0810:05:28)转载标签：存货管理财务管理作者：杨鉴淞中国管理信息化年10期字数：2113[摘要]经济订货量法是现代库存优化的方法之一。本文以Excel为工具，分别就库存管理决策基本的经济订货批量模型、扩展的经济订货批量模型和有数量折扣不允许缺货的经济订货模型进行了分析设计。透过实例表明，这些模型简单、实用，可以作为存货管理决策的有效工具。[关键词]存货管理；经济订货批量；订储费用[中图分类号]F275;F270.7[文献标识码]A[文章编号]1673-0194(2006)10-0041-03一、引言存货是企业在生产经营过程中，为销售或者耗用而储备的物资，包括原材料、在产品、产成品等。企业的存货一般在流动资产中占有较大的比重，存货管理水平的高低会对企业的财务状况和经营成果产生巨大的影响，因此，加强存货管理具有重要意义。经济订货量法（EconomicOrderQuantity，EQQ）也称经济批量法，是指企业每次订货的数量使得存货费用最低的方法，是现代库存优化方法之一。本文以Excel为工具，探讨库存管理决策的分析方法和模型。二、基本的经济订货批量模型存货的功能是满足生产经营的需要，而存货必然发生相应的成本。经济订货批量是存货的相关总成本最低的一次订货批量。经济订货批量应根据实际情况，分不同类型来确定。基本的经济订货批量模型建立在以下的假定条件之上：（1）订购的存货瞬时到货；（2）不允许缺货；（3）全年的存货需求是确定的；（4）存货的价格稳定，没有数量折扣；（5）存货的耗用比较均匀。在上述假定条件下，存货的相关成本包括以下两项：（1）订货费用。订货费用是指为组织进货所发生的各种费用，包括采购人员的差旅费、通讯费、运输费、检验费等。这些费用一般与订货的次数有关。在存货的全年需求量一定的情况下，一次订货量越多，全年的订货次数越少，订货费用越低。（2）存储费用。存储费用是指企业为持有存货而发生的费用，包括存货资金占用费用或机会成本、仓储费用、存货保险费用等。这些费用一般与平均存货水平的高低成正比。在存货的全年需求量一定的情况下，一次订货量越多，全年的平均存货水平越高，存储费用越高。基本的经济订货批量有关的计算公式如下：其中：Q*：经济订货批量R：一定时期存货的需求量S：一次采购费用C：存货单价K：存货的存储费率，CK为单位存储费用例如，某企业全年需要甲材料3200千克，每订购一次的订货成本为200元，每千克材料的年存储成本为2元。当一次订货批量分别为200、400、600、800、1000、1200、1400千克时，试计算其存货成本和经济订货批量指标。（1）在单元格B8中输入公式“＝$B$2/B7*$B$3”，计算一次订货量200千克时的年订货成本。（2）在单元格B9中输入公式“＝B7/2*$B$4”，计算一次订货量200千克时的年存储成本。（3）在单元格B10中输入公式“＝B8＋B9”，计算一次订货量200千克时的年订货存储总成本。（4）选取单元格区域B8:B10，采用填充复制的方法一直向右复制到H8:H10，得到其他采购批量之下的年成本。（5）选取单元格区域A7:H10，单击工具栏的【图表向导】按钮，在打开的【图表类型】对话框中选择【XY散点图】，并依次按有关的提示逐步操作，即可得到订货批量与年成本的关系图。（6）在单元格B12中输入公式“＝SQRT(2*B2*B3/B4)”，计算经济订货批量。（7）在单元格B13中输入公式“＝B2/B12”，计算年最优订货次数。(8)在单元格B14中输入公式“＝SQRT(2*B2*B3*B4)”，计算年最低订储总成本。由计算结果可知，当年订货成本与年存储成本相等时，全年的订货总成本最低，这种情况对应的订货批量为经济订货批量。甲材料的经济订货批量是800千克，年最优订货次数为4次，年最低订储总成本为1600元。三、扩展的经济订货批量模型当基本的经济订货批量模型的某些假定条件改变以后，即可得到扩展的经济订货批量模型。扩展的经济订货批量模型主要有以下3种形式。（一）存货陆续供应和耗用且不允许缺货的经济订货批量模型在订购的存货不能瞬时到货而是陆续供应、且边送边用的情况下，经济订货批量有关的计算公式如下：其中，g为送货期内每日平均送货量，d为每日平均消耗量。（二）存货瞬时到货且允许缺货的经济订货批量模型在订购的存货瞬时到达但允许缺货的情况下，持有存货的相关总费用包括了订货费用、储存费用和缺货费用，经济订货批量有关的计算公式如下：例如，某企业需要的几种存货具有不同的要求，可能遇到几种不同的情况，如可能缺货、存货陆续供应和陆续耗用等。下面建立一般情况下通用的经济订货批量计算模型。（1）设计存货是否陆续供应和耗用的控件以及是否允许缺货的控件。其中是否陆续供应和耗用的控件的【设计控件格式】中【数据源区域】为“$D$4:$D$5”，【单元格连接】为“B10”，【下拉显示项数】为2,而在单元格D4中填入“瞬时到货”，在单元格D5中填入“陆续到货”；是否允许缺货的控件的【设计控件格式】中【数据源区域】为“$D$7:$D$8”，【单元格链接】为“B11”，【下拉显示项数】为2,主单元格D7中填入“不允许缺货”，在单元格D8中填入“允许缺货”。（2）在单元格B13中输入经济订货批量计算公式“＝IF(AND(B10=1,B11=1),SQRT(2*B4*B5/B8),IF(AND(B10=1,B11=2),SQRT(2*B4*B5/B8*(B9+B8)/B9),IF(AND(B10=2,B11=1),SQRT(2*B4*B5/B8*B6(B6-B7)),IF(AND(B10=2,B11=2),SQRT(2*B4*B5/B8*B6/(B6-B7)*(B9+B8)/B9)))))”。（3）在单元格B14中输入年最佳订货次数计算公式“＝INT(B4/B13+0.5)”。（4）在单元格B15中输入年最低总订储费用计算公式“＝IF(AND(B10=1,B11=1),SQRT(2*B4*B5*B8),IF(AND(B10=1,B11=2),SQRT(2*B4*B5*B8*B9/(B9+B8)),IF(AND(B10=2,B11=1),SQRT(2*B4*B5*B8*(1-B7/B6)),IF(AND(B10=2,B11=2),SQRT(2*B4*B5*B8*(1-B7/B6)*B9/(B9+B8))))))”这样通用的经济订货批量计算模型建立完毕。例如，某企业对甲零件的年需要量为6000件，每次订货费用为250元，单位保管费用15元/年，若允许缺货，每缺货1件的年成本为10元。零件每日到货量25件，零件每日消耗量20件。则：（1）假设零件陆续供应和耗用，且允许缺货，则计算出经济订货批量为1581件，年订货次数4次，最低订储费用1397.37元。（2）若不允许缺货，但零件仍为陆续到货和消耗，则经济订货批量为1000件，年订货次数6次，最低订储费用3000元。（3）若不允许缺货，但零件为瞬时到达，则经济订货批量为447件，年订货次数13次，最低订储费用6708.20元。（4）若允许缺货，且零件为瞬时到达，则经济订货批量为707件，年订货次数8次，最低订储费用4242.64元。四、有数量折扣，不允许缺货的经济订货批量模型以上的经济订货批量都是假定采购数量不论多少，价格是一样的。如果销售企业为了鼓励客户购买更多的商品，采用“采购数量增加，可以享受折扣”这样的销售政策，那么客户一次购买量越多，所获得的价格优惠往往越大。因此，存货的相关总费用不仅包括订货费用和储存费用，还包括存货的采购费用。计算经济订货批量的基本思路是按打折后的价格计算，如果计算出的经济批量大于折扣点，则此经济批量即为最优解。反之，必须比较以经济批量进行采购和以折扣点进行采购两者的总费用。具体步骤为：（1）分别计算不同折扣价格下的经济订货批量。（2）判断这些经济订货批量是否有效（计算出的某经济订货批量是否与该折扣区间的价格相符）。（3）计算有效的经济订货批量下的总存货费用，并同时计算不同折扣起点批量下的总存货费用。（4）在这些总存货费用中找出最低费用对应的经济订货批量或折扣起点批量。（5）比较经济订货批量或折扣起点批量与需求量的关系，若小于需求量，则该经济订货批量或折扣起点批量就是最优解，否则，需求量是最优解。例如，某企业乙零件的年需要量为35000件，每次订购费用为30元，存储费率是零件单价的20%，该零件的供应商为争取业务，特制定台阶式折扣价格，以鼓励买方每批多购，其价格表如表1所示。订货决策的具体步骤如下：（1）在单元格C8、D8、E8、G8和H8中分别输入以下公式：单元格C8:＝SQRT(2*$B$3*($B$4)/B8/$B$5)单元格D8:＝IF(AND(C8=A8,C8单元格E8:＝IF(D8=有效,B8*$B$3+$B$3*$B$4/C8+$B$5*B8*C8/2,无效)单元格F8:＝B8*$B$3+$B$3*$B$4/A8+$B$5*B8*A8/2单元格G8:＝IF(D8=有效,MIN(E8:F8),无意义)单元格H8:＝IF(G8=F8,A8,C8)（2）选取单元格区域C8:H8,将其向下复制到单元格区域C13:H13。（3）将单元格D13中的公式修改为：“＝IF(C13=A13,有效,无效)”。（4）在单元格B14中输入公式“＝INDEX(H8:H13,MATCH(MIN(G8:G13),G8:G13,0),0)”。（5）在单元格B15中输入公式“＝IF(B3B14,B14,B3)”。（6）在单元格E15中输入公式“＝B3/B15”。由此可见，以经济批量8101件订货时的存货总费用最低，故企业的经济订货批量为8101件，年订货次数4次。五、结论由此可以看出，利用Excel强大的功能可以设计出日常库存管理决策中所需要的各种类型的数学模型。这对加强库存管理，提高物资供应的经济效益具有重要的意义。本文从基本的经济订货批量模型出发，设计的扩展的经济订购批量模型和有数量折扣、不允许缺货情况下经济订货批量模型有一定的实际应用参考价值。主要参考文献[1]王建成.管理会计学[M].北京：中国矿业大学出版社，2001.[2]张瑞君.计算机财务管理[M].北京：中国人民大学出版社，1996.[3]唐五湘，程桂枝.Excel在管理决策中的应用[M].北京：电子工业出版社，2001.