excel在存货管理决策中的应用docWord文件下载.docx

1、方法和模型。、基本的经济订货批量模型存货的功能是满足生产经营的需要， 而存货必然发生相应的成本。经济订货批量是存货的相关总成本最低的一次订货批量。 经济订货批量应根据实际情况， 分不同类型来确定。基本的经济订货批量模型建立在以下的假定条件之上：（1） 订购的存货瞬时到货；（2） 不允许缺货；（3） 全年的存货需求是确定的；（4） 存货的价格稳定，没有数量折扣；（5） 存货的耗用比较均匀。在上述假定条件下，存货的相关成本包括以下两项：（1） 订货费用。订货费用是指为组织进货所发生的各种费用， 包括采购人员的差旅费、 通讯费、运输费、检验费等。这些费用一般与订货的次数有关。在存货的全年需求量一定的

2、 情况下，一次订货量越多，全年的订货次数越少，订货费用越低。（2） 存储费用。存储费用是指企业为持有存货而发生的费用，包括存货资金占用费用 或机会成本、仓储费用、 存货保险费用等。这些费用一般与平均存货水平的高低成正比。在存货的全年需求量一定的情况下， 一次订货量越多，全年的平均存货水平越高， 存储费用越 高。基本的经济订货批量有关的计算公式如下:经济订货批H:最佳的订货次数代夕最低总订储费用：T*= V2K5CK其中:Q* :经济订货批量R: 定时期存货的需求量S: 次采购费用C：存货单价K :存货的存储费率，CK为单位存储费用料的年存储成本为 2元。当一次订货批量分别为 200、400、6

3、00、800、1 000、1 200、1 400千克时，试计算其存货成本和经济订货批量指标。k ； B C D I F GS 巨矽件 _2桝I全年爲更畫 3200JL 一次订赏咸本 吸4型位痔货年存貨成車2j vtMiieT 一次讥货量200400 6003001000120014008年订货脱本32001600 1066.640533.33331ST. 1429W 600,12WK年订倔碾U34（）Q.2000 NGt.1600uno1733. 3331S5T.143图1甲材料经济订货批的计鼻（1） 在单元格B8中输入公式 =$B$2/B7*$B$3,计算一次订货量 200千克时的年订 货成

4、本。（2） 在单元格B9中输入公式 =B7/2*$B$4”，计算一次订货量200千克时的年存储成 本。（3） 在单元格B10中输入公式 =B8 + B9,计算一次订货量200千克时的年订货存储 总成本。（4） 选取单元格区域 B8:B10,采用填充复制的方法一直向右复制到 H8:H10，得到其 他采购批量之下的年成本。（5）选取单元格区域 A7:H10，单击工具栏的 【图表向导】 按钮，在打开的【图表类型】 对话框中选择【XY散点图】，并依次按有关的提示逐步操作，即可得到订货批量与年成本 的关系图。一一年订货成本 年存flljfi举 年订彊总族枣（6） 在单元格B12中输入公式 =SQRT（2

5、*B2*B3/B4）” ，计算经济订货批量。（7） 在单元格B13中输入公式 =B2/B12”，计算年最优订货次数。（8）在单元格B14中输入公式=SQRT（2*B2*B3*B4” ，计算年最低订储总成本。由计算结果可知，当年订货成本与年存储成本相等时， 全年的订货总成本最低，这种情况对应的订货批量为经济订货批量。 甲材料的经济订货批量是 800千克，年最优订货次数为4次，年最低订储总成本为 1 600元。三、扩展的经济订货批量模型当基本的经济订货批量模型的某些假定条件改变以后， 即可得到扩展的经济订货批量模型。扩展的经济订货批量模型主要有以下 3种形式。（一）存货陆续供应和耗用且不允许缺货的

6、经济订货批量模型在订购的存货不能瞬时到货而是陆续供应、 且边送边用的情况下， 经济订货批量有关的计算公式如下：经济订货批2RSycJi d Y g /最佳的订货汝数：/屮二昙最低订储费用（订货费用和储存费用合计）其中，g为送货期内每日平均送货量， d为每日平均消耗量。（二）存货瞬时到货且允许缺货的经济订货批量模型在订购的存货瞬时到达但允许缺货的情况下，持有存货的相关总费用包括了订货费用、储存费用和缺货费用，经济订货批量有关的计算公式如下：最佳订货次数：屮=臺V*n祗订储费用:严二讥施亦磊丘其+.C为单位峡贷成本，三）存货茯续供应和耗用且允许缺货的经济订 货批蛋模型经济订贷批量:V篆令号f战佳订

7、货次散2触低订储如竹；化;屮2 卜?阳:h例如，某企业需要的几种存货具有不同的要求， 可能遇到几种不同的情况， 如可能缺货、存货陆续供应和陆续耗用等。下面建立一般情况下通用的经济订货批量计算模型。（1）设计存货是否陆续供应和耗用的控件以及是否允许缺货的控件。其中是否陆续供应和耗用的控件的【设计控件格式】中【数据源区域】为 “ $D$4:$D$5,【单元格连接】为“ B10”【下拉显示项数】为 2,而在单元格D4中填入瞬时到货”，在单元格D5中填入陆续到货”；是否允许缺货的控件的【设计控件格式】中【数据源区域】为 “$D$7:$D$8,【单 元格链接】为“B11”，【下拉显示项数】为 2,主单元

8、格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） 在单元格

9、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） ”AB J11经济订货批畳决策模药己知数摇4年薦求量6000瞬时到货；5每次定购费用 250陆续到贞J每日到贷量251二_7每目消耗量 20不九许缺货8单件保管费用1 15允许缺货单件

10、缺货成本 10Lio.是否陆绞到货|瞬时到货匕|!罡否允许缺贷| 计轉结耒13经济订购批量（件）7Q714年最佳订购次数（:次）F15豪佳订储费用（元）4242. 64069图3经济订货批计算模箜这样通用的经济订货批量计算模型建立完毕。例如，某企业对甲零件的年需要量为 6 000件，每次订货费用为 250元，单位保管费用15元/年，若允许缺货，每缺货 1件的年成本为 10元。零件每日到货量 25 件，零件每日消 耗量 20 件。则：（1）假设零件陆续供应和耗用，且允许缺货，则计算出经济订货批量为 1 581 件，年订货次数 4 次，最低订储费用 1 397.37 元。（2）若不允许缺货，但零件

11、仍为陆续到货和消耗，则经济订货批量为 1 000 件，年订货次数 6 次，最低订储费用 3 000 元。（3）若不允许缺货，但零件为瞬时到达，则经济订货批量为 447 件，年订货次数 13次，最低订储费用 6 708.20 元。（4）若允许缺货，且零件为瞬时到达，则经济订货批量为 707件，年订货次数 8 次，最低订储费用 4 242.64 元。四、有数量折扣，不允许缺货的经济订货批量模型以上的经济订货批量都是假定采购数量不论多少， 价格是一样的。 如果销售企业为了鼓 励客户购买更多的商品， 采用 “采购数量增加， 可以享受折扣 ”这样的销售政策，那么客户一 次购买量越多， 所获得的价格优惠往

12、往越大。 因此， 存货的相关总费用不仅包括订货费用和 储存费用，还包括存货的采购费用。计算经济订货批量的基本思路是按打折后的价格计算， 如果计算出的经济批量大于折扣点， 则此经济批量即为最优解。 反之， 必须比较以经济批量 进行采购和以折扣点进行采购两者的总费用。具体步骤为：（1）分别计算不同折扣价格下的经济订货批量。（2）判断这些经济订货批量是否有效（计算出的某经济订货批量是否与该折扣区间的 价格相符）。（3）计算有效的经济订货批量下的总存货费用，并同时计算不同折扣起点批量下的总 存货费用。（4）在这些总存货费用中找出最低费用对应的经济订货批量或折扣起点批量。（ 5）比较经济订货批量或折扣起

13、点批量与需求量的关系，若小于需求量，则该经济订 货批量或折扣起点批量就是最优解，否则，需求量是最优解。例如，某企业乙零件的年需要量为 35 000 件，每次订购费用为 30元，存储费率是零件单价的 20%，该零件的供应商为争取业务，特制定台阶式折扣价格，以鼓励买方每批多购， 其价格表如表1所示。 1乙毒件台辭折扣价格験枷格折扣点（件价格（元）0.24,2 000-3 W0.224 000-5 9990.20:6 000-7 弼0,188 000-9 9990,16IO （XX）以上CJ2订货决策的具体步骤如下：（1） 在单元格C8、D8、E8、G8和H8中分别输入以下公式：单元格 C8: =

14、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:有效,MIN（E8:F8）,无意义单元格 H8: = IF（G8=F8,A8,C8）（2） 选取单元格区域 C8:H8,将其向下复制到单元格区域 C13:H13。（3）将单元格D13中的公式修改为： =IF（C13=A13,无效）”。（4）在单元格 B14 中输入公式=INDE

15、X（H8:H13,MATCH（MIN（G8:G13）,G8:G13,0）,0）”（5） 在单元格 B15中输入公式 =IF（B3B14,B14,B3） ”。（6） 在单元格 E15中输入公式 =B3/B15”。前鈿:诫破礙痈晶權卍車需欢H Cff） 3SWJ0 nrRn甲刘事 so4 肓數折扣的经济订购批模型由此可见，以经济批量 8 101件订货时的存货总费用最低，故企业的经济订货批量为 8101件，年订货次数 4次。五、结论由此可以看出，利用Excel强大的功能可以设计出日常库存管理决策中所需要的各种类型的数学模型。这对加强库存管理，提高物资供应的经济效益具有重要的意义。 本文从基本 的经济订货批量模型出发，设计的扩展的经济订购批量模型和有数量折扣、 不允许缺货情况 下经济订货批量模型有一定的实际应用参考价值。主要参考文献1王建成管理会计学M.北京：中国矿业大学出版社， 2001.2张瑞君计算机财务管理M.北京：中国人民大学出版社， 1996.3唐五湘，程桂枝.Excel在管理决策中的应用M.北京：电子工业出版社， 2001.