excel在存货管理决策中的应用docWord文件下载.docx
《excel在存货管理决策中的应用docWord文件下载.docx》由会员分享，可在线阅读，更多相关《excel在存货管理决策中的应用docWord文件下载.docx（10页珍藏版）》请在冰豆网上搜索。
方法和模型。
、基本的经济订货批量模型
存货的功能是满足生产经营的需要，而存货必然发生相应的成本。
经济订货批量是存货
的相关总成本最低的一次订货批量。
经济订货批量应根据实际情况，分不同类型来确定。
基
本的经济订货批量模型建立在以下的假定条件之上：
（1）订购的存货瞬时到货；
（2）不允许缺货；
（3）全年的存货需求是确定的；
（4）存货的价格稳定，没有数量折扣；
（5）存货的耗用比较均匀。
在上述假定条件下，存货的相关成本包括以下两项：
（1）订货费用。
订货费用是指为组织进货所发生的各种费用，包括采购人员的差旅费、通讯费、运输费、检验费等。
这些费用一般与订货的次数有关。
在存货的全年需求量一定的情况下，一次订货量越多，全年的订货次数越少，订货费用越低。
（2）存储费用。
存储费用是指企业为持有存货而发生的费用，包括存货资金占用费用或机会成本、仓储费用、存货保险费用等。
这些费用一般与平均存货水平的高低成正比。
在
存货的全年需求量一定的情况下，一次订货量越多，全年的平均存货水平越高，存储费用越高。
基本的经济订货批量有关的计算公式如下:
经济订货批H:
最佳的订货次数代夕
最低总订储费用：
T*=V2K5CK
其中:
Q*:
经济订货批量
R:
—定时期存货的需求量
S:
—次采购费用
C：
存货单价
K:
存货的存储费率，CK为单位存储费用
料的年存储成本为2元。
当一次订货批量分别为200、400、600、800、1000、
1200、1400千克时，试计算其存货成本和经济订货批量指标。
k；
BCDIFG
S巨矽件_"
2桝I全年爲更畫3200
JL]一次订赏咸本吸
4型位痔货年存貨成車2
j—■
vtMiie
T一次讥货量
200
400600
300
1000
1200
1400
8年订货脱本
3200
16001066.
640
533.3333
1ST.1429
W600,
12W
K年订倔碾U
34（）Q.
2000NGt.
1600
uno
1733.333
1S5T.143
图1甲材料经济订货批■的计鼻
（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散点图】，并依次按有关的提示逐步操作，即可得到订货批量与年成本的关系图。
一一年订货成本■年存flljfi举年订彊总族枣
（6）在单元格B12中输入公式=SQRT（2*B2*B3/B4）”，计算经济订货批量。
（7）在单元格B13中输入公式=B2/B12”，计算年最优订货次数。
（8）在单元格B14中输入公式=SQRT（2*B2*B3*B4”，计算年最低订储总成本。
由计算结果可知，当年订货成本与年存储成本相等时，全年的订货总成本最低，这种情
况对应的订货批量为经济订货批量。
甲材料的经济订货批量是800千克，年最优订货次数为
4次，年最低订储总成本为1600元。
三、扩展的经济订货批量模型
当基本的经济订货批量模型的某些假定条件改变以后，即可得到扩展的经济订货批量模
型。
扩展的经济订货批量模型主要有以下3种形式。
（一）存货陆续供应和耗用且不允许缺货的经济订货批量模型
在订购的存货不能瞬时到货而是陆续供应、且边送边用的情况下，经济订货批量有关的
计算公式如下：
经济订货批
2RS
ycJid]Yg/
最佳的订货汝数：
/屮二昙
最低订储费用（订货费用和储存费用合计）
其中，g为送货期内每日平均送货量，d为每日平均消耗量。
（二）存货瞬时到货且允许缺货的经济订货批量模型
在订购的存货瞬时到达但允许缺货的情况下，持有存货的相关总费用包括了订货费用、
储存费用和缺货费用，经济订货批量有关的计算公式如下：
最佳订货次数：
屮=臺
V*
n祗订储费用:
严二讥施亦磊丘
其+.C为单位峡贷成本，
｛三）存货茯续供应和耗用且允许缺货的经济订货批蛋模型
经济订贷批量:
—V篆•令号f
战佳订货次散2'
触低订储如竹；
化\;
屮"
2卜?
…阳:
h
例如，某企业需要的几种存货具有不同的要求，可能遇到几种不同的情况，如可能缺货、
存货陆续供应和陆续耗用等。
下面建立一般情况下通用的经济订货批量计算模型。
（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）,SQR
T（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）,SQ
RT（2*B4*B5*B8*（1-B7/B6）*B9/（B9+B8））））））”
A
BJ
£
11
经济订货批畳决策模药
己知数摇
4
年薦求量
6000
瞬时到
货；
5
每次定购费用
[250
陆续到
贞J
每日到贷量
25
1~二_
7
每目消耗量
20
不九许缺货
8
单件保管费用
115
允许缺货
单件缺货成本10
Lio...
是否陆绞到货
|瞬时到货匕|!
罡否允许缺贷
|计轉结耒
13
经济订购批量（件）
7Q7
14
年最佳订购次数（:
次）
F15
'
豪佳订储费用（元）
4242.64069
图3经济订货批■计算模箜
这样通用的经济订货批量计算模型建立完毕。
例如，某企业对甲零件的年需要量为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乙毒件台辭折扣价格験
枷格折扣点（件》
价格（元）
0.24
2000-3W
0.22
4000-5999
0.20
:
6000-7弼
0,18
8000-9999
0,16
IO（XX）以上
CJ2
订货决策的具体步骤如下：
（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:
有效"
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（B3>
B14,B14,B3）”。
（6）在单元格E15中输入公式=B3/B15”。
前鈿:
诫破礙痈晶權卍
車需欢HCff）3SWJ0•nrRn甲\刘事so
4肓數■折扣的经济订购批■模型
由此可见，以经济批量8101件订货时的存货总费用最低，故企业的经济订货批量为8
101件，年订货次数4次。
五、结论
由此可以看出，利用Excel强大的功能可以设计出日常库存管理决策中所需要的各种类
型的数学模型。
这对加强库存管理，提高物资供应的经济效益具有重要的意义。
本文从基本的经济订货批量模型出发，设计的扩展的经济订购批量模型和有数量折扣、不允许缺货情况下经济订货批量模型有一定的实际应用参考价值。
主要参考文献
[1]王建成•管理会计学[M].北京：
中国矿业大学出版社，2001.
[2]张瑞君•计算机财务管理[M].北京：
中国人民大学出版社，1996.
[3]唐五湘，程桂枝.Excel在管理决策中的应用[M].北京：
电子工业出版社，2001.
升级会员 