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

vol.8, no.3, january 2012tel:+86-551-5690963 5690964巧用 excel 函数加权平均法核算物料成本耿 勇（阳光国际阿尔及利亚有限公司，阿尔及利亚 阿尔及尔 999103）摘要：核算企业物料成本是企业财务管理中一项重要工作。该文试图通过 excel 函数来自动完成企业物料发出成本和结存成本 的核算，给出了加权平均法在 excel 表格中计算物料发出和结存成本的设计思路和具体步骤。 关键词：加权平均法；物料成本；excel 函数中图分类号：tp393文献标识码：a文章编号：1009-3044(2012)03-0604-04using the excel function weighted average method to calculate material costsunshinegeng yong(international algeria limited company,algiers 999103, algeria)abstract: in a company calculating inventoriescost is the most important part of finance management. this essay aims at calculating the cost of inventoriesoutputting and inventoriesin 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、0499，对于比较复杂的生产型公司来讲物料的编码除遵循上述基本规则 外还必须考虑公司物料的分类等各种物料属性，在此就不在赘述，为了简化我没有进行物料中小类别的分类，物料编码中后五位为 流水号，在 excel 中物料代码必须设置成文本格式。1.2 加权平均法实施的大体思路计算本月发出物料成本、月末结存物料成本应该具备以下几张基础的报表：期初库存报表、本月入库日报报表、本月销售（耗收稿日期：2011-12-12作者简介：耿勇（1974-）,男,湖北红安人，大专，中级会计师，研究方向为电子商务、办公自动化与财务会计。本栏目责任编辑：谢媛媛604软件设计开发第 8 卷第 3 期 (2012 年 1 月)computer knowledge and technology 电脑知识与技术用）发出日报报表，其中仓库管理员物料入库日报和物料出库日报中的出入库的每一笔记录必须严格按照日期叙时逐条进行登记，可以将入库单和出库单的数量、规格型号等物料信息定期传递到财务部门。我们知道：月末结存物料数量=月初物料结存数量+本 月物料入库数量-本月物料出库数量，根据库存物料数量这一个基础公式我们可以计算每一种物料的月末结存数量，由于入库是按 日期叙时登记的，所以针对每一种物料的数量可以用 sumif 函数来汇总计算其入库数量；财务部门根据发票的物料开票金额或者 发票所附的物料清单的未含税价格、仓库管理人员的入库单数量就可以确定每一种物料的入库单价，同样地物料一个月内多次入 库也可以利用 sumif 函数来汇总计算其入库金额，这样就确定了加权平均法公式中本月入库的数量及金额，再结合已经存在的期 初库存物料的数量和单价，每种物料的加权平均单价就可以确定了，所以为了计算物料成本就必须加上一张汇总期初库存、本月入 库日报、本月出库日报的报表用来自动计算结存的报表即月末库存物料汇总表。我将期初库存报表、本月入库日报报表、本月销售（耗用）出库日报报表、月末库存物料明细表放在同一工作簿里，见下面的截图（1），前三张表是基础的表格，月末库存物料明细表中 是汇总本月入库出库结存的数据。图 12 具体实施步骤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 函数将期初库存、本月入库、本月出库报表的本栏目责任编辑：谢媛媛软件设计开发605computer knowledge and technology 电脑知识与技术第 8 卷第 3 期 (2012 年 1 月)相关信息从这些表格中引用过来，下面分别进行介绍。图 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(l30,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(o30,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-o30,k3+n3-q3,0）,月末结存单价的单元格 s3 中公式定义：=if(q30, round（s3/q3，2）,) 截图如下图 8。图 8为了正确结出每月出库成本金额可以在这张表中增加一列：出库金额尾差调整 ，在这列中 t3 单元格可定义如下公式：= if(and(r3=0,k3+n3-q30),k3+n3-q3,0)这个公式就是判断出现月末结存数量为 0 但月末结存余额不为 0 的情况，如果出现这种情 况就一律将这个尾差计入出库成本之中，所以还应添加最后一列出库成本合计即 u 列，u3 单元格中定义公式如下：= u3+q3，见下图 9。图 9本栏目责任编辑：谢媛媛606软件设计开发第 8 卷第 3 期 (2012 年 1 月)computer knowledge and technology 电脑知识与技术上述公式定义完毕可以将上述公式向下拖动以便复制这些公式到该表格的合计行的上一行即可，经过以上步骤就可以将月末一次加权平均法计算物料成本在 excel 中实现了。 对于物料的暂估入库而言，我们仍然可以在这个表格中进行计算，对于货物本月已经入库但供应商发票还未到达的情况下（即货到票未到），到下月该物料的发票已经到达的情况下可以按照上月暂估入库的数量在入库日报中做一笔红字入库记录，据此冲回 暂估入库，然后按照正确的单价和数量做一笔入库记录。如果上月有物料的暂估出库情况发生在计算当月发出库存商品成本时， 上月仍然应当按照规定的方法正常计算确定发出成本，到下月时如果正式发票已到红字冲回入库，并且取得发票正式入账，两者相 抵，余额为暂估成本与实际成本之间的差额，该差额就由确认发票当月发出和月末结存的库存物料承担。如果该物料上月恰好已 经全部发出，该物料本月再无购进与结存，本月发票已到实际成本与暂估成本之间的差异按照前面所述月末结存为零但结存金额 不为零的情形进行处理，例如香脆椒上月购进 20 袋已经全部发出，本月发票已经到达单价已经确定，在月末结存物料明细表中本月 入库中就反映为只有金额而数量为零，在出库成本尾差中就作了调整处理，该差额进入冲回暂估的当月成本中，不会还留在月末结 存的金额中（见下图 10）。图 102003 版 excel 最大行数只能是 65536 行，考虑到业务量的变化，对于有些中型企业不够用，可以考虑安装 2007 版 excel 或者2010 版 excel，其最大行数可达到 1048576 行，对于中小型企业来讲，每个月的业务量这么多行是够使用的了。从设计的角度来 看