基于Excel的存货管理模型设计

1、基于Excel的存货管理模型设计    摘要:存货是企业在生产经营中为生产或销售而储备的物资,企业持有足够的存货,不仅可以节约采购成本与生产时间,而且能够迅速地满足客户各种订货的需要,从而为企业生产与销售提供较大的机动性。然而,存货增加必然要占用更多的资金,将使企业付出更大的持有成本,即存货的机会成本。因此发挥存货功能,在存货收益与成本之间进行权衡,在充分发挥存货功能的同是降低存货成本,增加收益,实现它们的最佳组合,成为存货管理的基本目标。 关键词:Excel;存货;模型设计 中图分类号:F72文献标志码:A文章编号:1673-291X(2010)30-

2、0199-03 存货是指在日常经营过程中为了销售而持有的、为了销售而处于生产过程中的、为用于生产销售的商品或劳务而持有的资产。存货在资产负债表中列为流动资产,包括原材料、在产品、半成品、产成品、低值易耗品、委托加工材料、包装物等。企业持有足够的存货,不仅有利于生产过程的顺利进行,节约采购成本与生产时间,而且能够迅速地满足客户各种订货的需要,从而为企业生产与销售提供较大的机动性,避免因存货不足带来的机会损失。 一、存货成本及其经济订货批量 1.存货成本 企业为存货所发生的一切支出,称之为存货成本。主要包括采购成本、定货成本、存储成本、缺货成本等部分。存货成本的构成从理论上讲,应该包括从购入到使商

3、品处于可供销售的地点和状态的一切直接和间接的支出。 2.经济订货批量 经济进货批量,是指能够使一定时期存货的总成本达到最低点的进货数量。决定存货经济进货批量的成本因素主要包括变动性进货费用(简称进货费用)、变动性储存成本(简称储存成本)以及允许缺货时的缺货成本。不同的成本项目与进货批量呈现不同的变动关系,因此存在一个最佳的进货批量,使成本总和保持最低水平。 3.存货ABC分类管理 对存货的日常管理,根据存货的重要程度,将其分为ABC三种类型。A类存货品种占全部存货的10% 15%,资金占存货总额的80%左右,实行重点管理,如大型备品备件等。B类存货为一般存货,品种占全部存货的20%30%,资金

4、占全部存货总额的 15%左右,适当控制,实行日常管理,如日常生产消耗用材料等。C类存货品种占全部存货的60%65%,资金占存货总额的5%左右,进行一般管理,如办公用品、劳保用品等随时都可以采购。通过ABC分类后,抓住重点存货,控制一般存货,制定出较为合理的存货采购计划,从而有效地控制存货库存,减少储备资金占用,加速资金周转。 二、相关函数介绍 1.SQRT 函数 SQRT 函数的功能是返回正平方根。 函数语法: SQRT(number) Number要计算平方根的数。 函数说明: 如果参数 Number 为负值,函数 SQRT 返回错误值 #NUM!。 2.IF函数 IF函数是较为常用的逻辑函

5、数之一,它执行真假值判断,根据逻辑计算的真假值,返回不同结果。可以使用函数IF对数值和公式进行条件检测。 函数语法: IF(logical_test,value_if_true,value_if_false) Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。 Value_if_truelogical_test为TRUE 时返回的值。 Value_if_falselogical_test为FALSE时返回的值。 函数说明: 函数IF可以嵌套七层,用value_if_false及value_if_true 参数可以构造复杂的检测条件。 在计算参数value_if_tru

6、e和value_if_false后,函数I 返回相应语句执行后的返回值。 如果函数IF的参数包含数组 (数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式;数组常量是用作参数的一组常量),则在执行IF语句时,数组中的每一个元素都将计算。 3.INT函数 INT函数的功能是将数字向下舍入到最接近的整数。 函数语法: INT(number) Number 需要进行向下舍入取整的实数。 三、利用Excel建立经济订货批量模型与存货ABC模型 1.经济订货批量综合模型 (1)新建工作簿与工作表。启动Excel电子表格,在BOOK1工作簿中新建一工作表,命

7、名为“经济订货批量”,建立“经济订货批量综合决策模型表”(如图1所示)。 图1经济订货批量决策模型 (2)计算“经济订货批量”。单击B13单元格在编辑栏中输入“=IF(A11=1,SQRT(2*B4*B5/B6),IF(A11=2,SQRT(2*B4*B5/B6*(B7+B6)/B7),SQRT(2*B4*B5*B8)/(B8-B9)*B6)”按【回车】键确认,即可求出在不允许缺货、允许缺货以及陆续到货三种不同情况下的经济订货批量。 其中,IF 是逻辑函数、SQRT是平方根函数。当A11单元格等于1时,计算的是不允许缺货的经济订货批量;当A11单元格等于2时,计算的是允许缺货的经济订货批量;当

8、A11单元格不等于1或2时,计算的是陆续到货的经济订货批量。 (3)计算“年订货次数”。单击B14单元格在编辑栏中输入“=INT(B4/B13+0.5)”按【回车】键确认,即可求出三种不同条件年订货次数。 (4)计算“年最低订货费用”。单击B15单元格在编辑栏中输入“=IF(A11=1,SQRT(2*B4*B5*B6),IF(A11=2,SQRT(2*B4*B5*B6*B7/(B7+B6),SQRT(2*B4*B5*B6*(1-B9/B8)”按【回车】键确认,即可求出三种不同条件年最低订货费用。 (5)添加控件。调出窗体工具栏,在B10单元格添加一组合框,将其数据源链接到D1:D3单元格区域,

9、条件单元格链接到A11单元格;最后结果(如图2所示)。 图2计算结果 (6)建立动态图表。单击A19单元格在编辑栏中输入” =B4/B13*B5” ;单击A20单元格,在编辑栏中输入” =B13/2*B6” ;单击A21单元格在编辑栏中输入” =SUM(A19:A20) 按【回车】键确认,即计算出不允许缺货情况下最低总成本。 单击C21单元格在编辑栏中输入“=A21”,选取B29:B34单元格区域,分别输入不同的订货量;选取B28:B34单元格区域,单击“数据”菜单,选择“模拟运算表”,在打开的“模拟运算表”对话框“输入引用列的单元格”中输入“B13”,单击“确定”后,即形成了总成本与经济订货

10、批量模拟运算表(如图3所示)。 图3模拟运算表 选取A36:A40单元格区域,在编辑栏中以数组方式输入公式“=B13”,选取B36:B40单元格区域输入相关数值(如图4所示)。 图4参考线表 分别选取上述两个区域,利用图表向导,建立一XY散点图。调出窗体工具栏,在图表区添加一“微调控件”,右击该“微调控件”,选择“设置控件格式”在“单元格链接”文本框中输入“B4”,这样就形成了一个动态图表,反映在不同需求量情况下,订货量与总成本之间的变动情况(如图5所示)。 图5不同需求量下的经济订货批量 2.存货ABC管理模型 (1)新建表。在“BOOK1.xls”工作簿中另建一工作表,命名为“库存ABC”

11、,建立“库存ABC管理模型表”,并将库存金额降序排列(如图6所示)。 图6库存ABC管理模型 (2)计算“库存金额所占比重”。单击D3单元格在编辑栏中输 “=C3/$C$24”按【回车】键确认,复制公式至D23单元格,这样就计算出了各库存金额所占的比重。 (3)计算“累计比重”。单击E4单元格在编辑栏中输入“=D4+E3”按【回车】键确认,复制公式至E23单元格,这样就计算出了各库存金额的累计比重。 (4)库存分类。单击A3单元格在编辑栏中输入“=IF(E3>=95%,“C”,IF(E3>=80%,“B”,“A”)”按【回车】键确认,复制公式至A23单元格即可,这样就将其库存商品进行了A、B、C分类(如图7所示)。 (5)品种分析。单击I3单元格在编辑栏中输入“=COUNTIF($A$3:$A$23,“A”)”按【回车】键确认,复制公式至I5单元格,这样就求出了不同分类的品种个数。 图7ABC分类 单击J3单元格在编辑栏中输入“=I3/$I$6”按【回车】键确认,复制公式至J5单元格,这样即可求出各品