巧用EXCEL函数加权平均法核算物料成本

摘要：核算企业物料成本是企业财务管理中一项重要工作。该文试图通过EXCEL函数来自动完成企业物料发出成本和结存成本的核算，给出了加权平均法在EXCEL表格中计算物料发出和结存成本的设计思路和具体步骤。
中国论文网 http://www.xzbu.com/8/view-1604676.htm
　　关键词：加权平均法；物料成本；EXCEL函数
　　中图分类号：TP393文献标识码：A文章编号：1009-3044(2012)03-0604-04
　　Using the EXCEL Function Weighted Average Method to Calculate Material CostSunshine
　　GENG Yong
　　(International Algeria Limited Company,Algiers 999103, Algeria)
　　Abstract: In a company calculating inventories’cost is the most important part of finance management. This essay aims at calculating the cost of inventories’outputting and inventories’in stock by EXCEL functions automatically, It explains the designing concept and concrete procedures of the method of weighted average in EXCEL sheet.
　　Key words: weighted average method; material cost; EXCEL function
　　库存原材料是企业流动性比较强的资产，为了正确核算材料成本，必须加强库存材料进销（耗）存的管理，很多公司为了加强原材料的管理购买了相关软件来完成这一工作，但软件的应用都会产生较大的成本，相比利用专用软件进行物料管理而言，运用EX? CEL表格中强大的函数功能来实现库存材料进销（耗）存的管理，也不需要在EXCEL表中去编制一般使用者所不熟悉的比较复杂的EXCEL宏去实现计算，则要经济实惠得多，对诸多中小企业来说尤其如此。在这里我就加权平均法核算物料成本在EXCEL里的应用作下详细的介绍，希望对广大数据处理人士有所裨益。
　　 1加权平均法应用的大体思路
　　1.1加权平均法与物料编码的基本要求
　　在介绍利用EXCEL函数进行加权平均法计算物料成本前我们首先来简略介绍下加权平均法和物料编码的基本要求。
　　加权平均法也叫全月一次加权平均法，指以本月收入全部物料数量加月初物料数量作为权数，去除本月收入全部物料成本加月初物料成本的和，计算出物料的加权平均单位成本，从而确定物料的发出成本和库存成本的方法。计算公式如下：
　　加权平均单价=（本月收入每种物料金额+月初每种物料金额）/（本月收入每种物料数量+月初每种物料数量）本月发出物料成本=本月发出物料数量*加权平均单价月末结存物料成本=月末库存物料数量*加权平均单价
　　为了在EXCEL中利用函数实现自动化计算物料成本，我必须将每一种具体的物料进行编码化，用编码来对应每一种物料，物料编码的条件必须遵循以下三大原则：唯一性、分类别、长度统一
　　1)唯一性：指在通常情况下如果物料的物理形态和化学形态不发生改变同一种物料自始至终使用统一代码，如果发生了改变就必须编制不同的物料编码。
　　2)分类别：在编码时一般会按一定的方式对物料编码进行分类，物料编码时通常按照大类―中类―小类―流水号的形式进行编码，这样在日常统计分析时比较方便。
　　3)长度统一：一般情况下编码长度统一可以使我们阅读或者录入时很容易发现物料编码长度不对（漏输入或多输入），也有利于物料进行排序和分类汇总。
　　现以超市库存为例进行介绍物料代码的编制，物料代码中第一位为1，利用EXCEL的数据有效性将物料类别分成调料、干货、酒、副食、烟、粮食等类别，其代码分别对应01、02、03、04……99，对于比较复杂的生产型公司来讲物料的编码除遵循上述基本规则外还必须考虑公司物料的分类等各种物料属性，在此就不在赘述，为了简化我没有进行物料中小类别的分类，物料编码中后五位为流水号，在EXCEL中物料代码必须设置成文本格式。
　　1.2加权平均法实施的大体思路
　　计算本月发出物料成本、月末结存物料成本应该具备以下几张基础的报表：期初库存报表、本月入库日报报表、本月销售（耗 用）发出日报报表，其中仓库管理员物料入库日报和物料出库日报中的出入库的每一笔记录必须严格按照日期叙时逐条进行登记，可以将入库单和出库单的数量、规格型号等物料信息定期传递到财务部门。我们知道：月末结存物料数量=月初物料结存数量+本月物料入库数量-本月物料出库数量，根据库存物料数量这一个基础公式我们可以计算每一种物料的月末结存数量，由于入库是按日期叙时登记的，所以针对每一种物料的数量可以用SUMIF函数来汇总计算其入库数量；财务部门根据发票的物料开票金额或者发票所附的物料清单的未含税价格、仓库管理人员的入库单数量就可以确定每一种物料的入库单价，同样地物料一个月内多次入库也可以利用SUMIF函数来汇总计算其入库金额，这样就确定了加权平均法公式中本月入库的数量及金额，再结合已经存在的期初库存物料的数量和单价，每种物料的加权平均单价就可以确定了，所以为了计算物料成本就必须加上一张汇总期初库存、本月入库日报、本月出库日报的报表用来自动计算结存的报表即月末库存物料汇总表。我将期初库存报表、本月入库日报报表、本月销售（耗用）出库日报报表、月末库存物料明细表放在同一工作簿里，见下面的截图（1），前三张表是基础的表格，月末库存物料明细表中是汇总本月入库出库结存的数据。
　　
　　图1
　　 2具体实施步骤
　　2.1基础表格的设计
　　期初库存物料表中应具备如下要素：物料编码、序号、类别、物料名称、品牌、规格型号、包装率、最小单位、数量、单价、金额、备注等要素，下图2为期初库存物料表的表头：
　　
　　图2
　　本月入库报表中应具备如下要素：物料编码、序号、类别、物料名称、品牌、规格型号、包装率、最小单位、整装数量、零散数量、单价、金额、入库单号、备注等要素，见下图3。
　　
　　图3
　　在做本月入库日报表时仓库管理人员去期库存表中选择物料代码然后根据手工入库单进行填报入库数量、入库单号等信息，如果期初库存物料表中没有相应的物料可以直接添加新的物料代码，在备注后表明本月新增。财务人员可以根据仓库管理人员传递到财务部门的入库单和采购发票或者发票所附的采购清单在入库日报表中填报其入库单价，这样就确定了入库物料的成本。本月入库日报表中标有黄色的部分是可以进行手工输入的，在第三行类别、物料名称、品牌、规格型号、单位这些单元格中可以定义如下公式：=IF(A3="","",VLOOKUP(A3,期初库存!A:H,3,FALSE))、=IF(A3="","",VLOOKUP(A3,期初库存!A:H,4,FALSE))、=IF(A3="","", VLOOKUP(A3,期初库存!A:H,5,FALSE))……就可以将这些所需的物料信息自动从期初库存表中引用过来。因为有些物料是按整装购入、零星发出的，所以这些物料有其包装率，因此最小单位数量单元格可以定义如下公式：=G3*I3+J3,即使没有包装率也可应用此公式，本表的单价按照最小计量单位的价格进行计量，本月入库金额的单元格可以定义如下公式：=round（M3*K3,2），上述公式定义完毕可以将上述公式向下拖动以便复制这些公式到该表格的合计行的上一行即可。本月入库日报中入库单号可以录入仓管部门提供的入库单编号，备注列中可以注上凭证号或者发票号码，可以和财务系统金额核对一致，在这些有计算公式的区域中不允许手工收入（即表中表头没有填充黄色部分的所在列的区域），为了防止错误操作破坏表中自动取数的公式我们还可以将这些有计算公式的单元格区域保护起来。
　　本月发出日报报表中应具备如下要素：物料编码、序号、类别、物料名称、品牌、规格型号、包装率、最小单位、整装数量、零散数量、单价、金额、备注等要素，截图如图4。
　　
　　图4
　　上面标黄色的部分是需要进行手工输入的，这张表中出库类型根据公司的业务类型分零售、赊销、配送、内部领用；类别、物料名称、品牌、规格型号、包装率、最小单位数量等没有填充黄色的表头单元格所在列的区域的取数公式和上述本月入库日报表中取数公式一样。
　　2.2月末结存报表的设计
　　月末结存明细汇总表这张表中物料的相关信息可以利用VLOOKUP函数、SUMIF函数将期初库存、本月入库、本月出库报表的
　　相关信息从这些表格中引用过来，下面分别进行介绍。
　　
　　图5
　　汇总表中类别、物料名称、品牌、规格型号、包装率、最小单位、月初结存数量、月初结存物料单价等信息（见图5）运用VLOOKUP函数都可以将这些信息自动从期初库存报表中引用过来，在这张表格没有填充黄色表头的下一行即第三行的单元格中（即单元格C3：K3这个区域）分别定义如下公式：=IF(A3="","",VLOOKUP(A3,期初库存2011.1!A:I,3,FALSE))、=IF(A3="","",VLOOKUP(A3,期初库存2011.1!A:I,4,FALSE))、=IF(A3="","",VLOOKUP(A3,期初库存2011.1!A:I,5,FALSE)……。
　　因为本月入库报表中是以日报的形式进行按物料明细进行录入入库信息的，所以同一种物料就有可能多次采购入库，在汇总这些物料入库数量时可以考虑利用SUMIF函数从本月入库日报表中将其累计数量汇总过来，因此在汇总表中本月入库栏中数量这一列的单元格中即L3单元格中定义如下公式=SUMIF(入库日报2011.1!$A$3:$A$20000,A3,入库日报2011.1!$K$3:$K$20000)，考虑到公司的业务量，也可以将汇总数量的行号最大值修改成大于20000或者小于20000的任何值，反正要将本月入库日报中全部入库记录给涵盖进去即可;同理在在汇总表中本月入库栏中金额这一列的单元格中即N3单元格中定义如下公式：=SUMIF(入库日报2011.1!$A$3:$A$20000,A3,入库日报2011.1!$N$3:$N$20000),接下来定义入库单价这一列，考虑到有些物料可能本月没有购入，因此可以利用IF函数进行判断本月入库数量是否等于0，因此在汇总表中本月入库栏中单价这一列的单元格中即单元格M3单元格中定义如下公式：=IF(L3<>0,round（N3/L3,2）,""),利用这三个公式就可以完整地将入库日报中数量、金额、单价汇总起来。相关截图见图6；如果上月有暂估物料的情况发生本月发票账单已到能够确定单价时就在本月入库报表中做红字入库和按正确的单价做蓝字入库，这样会产生入库金额的差额，入库金额的确认仍然和前述公式一样。
　　
　　图6
　　因为出库报表中是以日报的形式进行按物料明细进行录入出库信息记录的，所以同一种物料就有可能发生多次出库记录，在汇总这些物料出库数量时同样可以考虑利用SUMIF函数来计算，在O3单元格中定义的公式如下：=SUMIF(出库日报2011.1!$A$3:$A$40000,A3,出库日报2011.1!$P$3:$P$40000)，P列即为销售出库日报中最小单位数量这一列，考虑到公司出库的业务量，也可以将汇总数量的行号最大值修改成大于40000或者小于40000的任何值，反正要将本月出库日报中全部入库记录给涵盖进去即可，接下来就需要在P3单元格定义出库单价，定义单价可以采用月末一次加权平均法计算单价，具体计算公式为：=IF(O3<>0,ROUND((K3+ N3)/(I3+L3),2),0)，这个公式就是应用前面所述的月末加权平均单价的文字公式的具体表达式，出库金额定义公式为：= ROUND(O3*P3,2)，利用这三个公式这样就可以计算出本月的出库成本金额。
　　
　　图7
　　我们知道月末物料结存数量=期初库存数量+本月入库数量-本月出库数量因此汇总表里面月末结存的数量的单元格即R3中可以定义公式：= I3+L3-O3，为了避免出现月末结存数量为0但月末结存余额不为0的情况，我们可以利用IF函数来排除金额出现尾差的情况，直接将尾差计入出库成本中，尾差对出库成本的影响极小，所以月末结存数量的单元格R3、结存金额的单元格T3的公式定义分别为：=IF(I3+L3-O3=0,0,I3+L3-O3)、=IF（I3+L3-O3<>0,K3+N3-Q3,0）,月末结存单价的单元格S3中公式定义：=IF(Q3<>0, round（S3/Q3，2）,"")截图如下图8。
　　
　　图8
　　为了正确结出每月出库成本金额可以在这张表中增加一列：出库金额尾差调整，在这列中T3单元格可定义如下公式：= IF(AND(R3=0,K3+N3-Q3<>0),K3+N3-Q3,0)这个公式就是判断出现月末结存数量为0但月末结存余额不为0的情况，如果出现这种情况就一律将这个尾差计入出库成本之中，所以还应添加最后一列出库成本合计即U列，U3单元格中定义公式如下：= U3+Q3，见下图9。
　　
　　图9
　　上述公式定义完毕可以将上述公式向下拖动以便复制这些公式到该表格的合计行的上一行即可，经过以上步骤就可以将月末一次加权平均法计算物料成本在EXCEL中实现了。
　　对于物料的暂估入库而言，我们仍然可以在这个表格中进行计算，对于货物本月已经入库但供应商发票还未到达的情况下（即货到票未到），到下月该物料的发票已经到达的情况下可以按照上月暂估入库的数量在入库日报中做一笔红字入库记录，据此冲回暂估入库，然后按照正确的单价和数量做一笔入库记录。如果上月有物料的暂估出库情况发生在计算当月发出库存商品成本时，上月仍然应当按照规定的方法正常计算确定发出成本，到下月时如果正式发票已到红字冲回入库，并且取得发票正式入账，两者相抵，余额为暂估成本与实际成本之间的差额，该差额就由确认发票当月发出和月末结存的库存物料承担。如果该物料上月恰好已经全部发出，该物料本月再无购进与结存，本月发票已到实际成本与暂估成本之间的差异按照前面所述月末结存为零但结存金额不为零的情形进行处理，例如香脆椒上月购进20袋已经全部发出，本月发票已经到达单价已经确定，在月末结存物料明细表中本月入库中就反映为只有金额而数量为零，在出库成本尾差中就作了调整处理，该差额进入冲回暂估的当月成本中，不会还留在月末结存的金额中（见下图10）。
　　
　　
　　图10
　　2003版EXCEL最大行数只能是65536行，考虑到业务量的变化，对于有些中型企业不够用，可以考虑安装2007版EXCEL或者2010版EXCEL，其最大行数可达到1048576行，对于中小型企业来讲，每个月的业务量这么多行是够使用的了。从设计的角度来看，利用EXCEL函数省去了数据库方面的大量工作，也不需要在EXCEL中去设计复杂的宏，提高了设计效率。而从中小型企事业用户的角度来考虑，因其对电脑要求不高、对于一般EXCEL操作者来讲简单易懂也便于操作，从而很受中小企业的欢迎。本设计方案已经在阳光国际阿尔及利亚超市应用近2年，取得了明显的成效。存在的不足之处是没有将本月期末库存自动结转到下月的功能，每月初需要将上月末结存的相关数据复制过来然后使用或者用VLOOKUP函数引用过来然后将有关函数修改下拖动复制下来，显得不太方便。
　　参考文献：
　　[1]中电电气集团物料编码规范[DB/OL].http://www.省略/p-118961650.html.