【作　　者】
陈 锋

【作者单位】
（山重建机〈济宁〉有限公司财务部 山东济宁 272003）

【摘　　要】

      【摘要】本文首先介绍存货发出成本计算过程中分解存货发出数量的思路，然后给出用EXCEL公式自动计算存货发出成本的方法，最后借助VBA编程设计出自定义求解函数。借助该函数，在已知各批入库数量、单价及出库数量的条件下，可快速实现存货发出成本的计算。
【关键词】先进先出法   存货发出成本   EXCEL   VBA

关于借助Excel自动计算先进先出存货发出成本的问题，何燕发表在《财会月刊》2011年11月上旬刊的《运用Excel制作先进先出法、移动加权平均法存货自动计价模型》一文给出了使用Excel函数自动计算存货发出成本的方法。然而，在该文所用的函数复杂，嵌套较多，且表格格式设计烦琐，不宜推广使用。据此，本文首先建立了简化的数学模型，对该问题进行了求解，而后根据求解结果，借助Excel设计了两种自动计算先进先出存货发出成本的方法，本文提供的方法原理清晰、实现简单且灵活性较强，适合推广。
一、库存发出队列的求解
用先进先出法计算存货发出成本时，先按期初单价计算发出存货的成本，领发完毕后，再按第一批入库的单价计算，依次从前向后类推。在已知入库数量、单价和出库数量的前提下，需先对出库数量进行分解，用分解后的数量分别乘以对应单价并加和，即可得到存货发出成本，因此，先进先出法计算存货发出成本的过程可用图1表示。

根据以上思路，使用先进先出法计算存货发出成本的关键是对发出数量的分解，即计算图1中的库存发出队列，下面建立求解库存发出队列的模型。

建立库存发出队列计算模型如表1，设[ai]、[bi]、[ci]（i≥1）分别表示第i期入库数量、出库数量、库存数量，[Xi,j]（i≥0，j≥0）表示第j期发出的[bj]件存货中来自于第i期入库存货[ai]的数量，对[ai]、[bi]、[ci]、[Xi,j]做如下假定：
（1）对任意i均有[ai]≥0，[bi]≥0，[ci]≥0；
（2）对任意i、j，若i∗j=0，则[Xi,j]=0。
以上假定，对于求解该模型提供了便利，假定（1）保证了给定的入库、出库数量是合理的，不会产生负库存，假定（2）设当i或j为0时，[Xi,j]=0，事实上，当i或者j为0时，[Xi,j]是没有任何含义的，将其值设置为0，有利于以下的归纳求解，用数学归纳法求解[Xi,j]如下：
当1≤i≤2，1≤j≤2时，有：
[X1,1=min(a1-X1,0,b1-X0,1)X1,2=min(a1-X1,0-X1,1,b2-X0,2)X2,1=min(a2-X2,0,b1-X0,1-X1,1)X2,2=min(a2-X2,0-X2,1,b2-X0,2-X1,2)]
当i=m，j=n时，有：

其中，                                        ，表示在第m期入库的[am]件存货中，前n-1期累计发出的数量；[am-i=0n-1Xm,i]表示第m期入库的[am]件存货中，可供第n期发出的数量；[j=0m-1Xj,n=X0,n+X1,n+…+Xm-1,n]表示第n期发出的[bn]件存货中，前m-1期累计提供的数量；[bn-j=0m-1Xj,n]表示第n期发出的[bn]件存货中，需从第m期（[am]）中获取的数量。[am-i=0n-1Xm,i]、[bn-j=0m-1Xj,n]分别表示确定[Xm,n]时的供给和需求，取二者中的较小者即为[Xm,n]，至此，完成了库存发出队列的求解。
二、借助Excel公式实现先进先出存货发出成本计算
用Excel实现先进先出存货发出成本计算所用到的函数有Sum函数、Min函数和Sumproduct函数。Sum函数和Min函数此处不做介绍，Sumproduct函数用于求给定的几组数的对应乘积的和，例如，在A1：A2区域分别输入“1，2”，在B1：B2区域内分别输入“3，4”，在B3单元格内输入“=SUMPRODUCT（A1：A2，B1：B2）”，B3单元格的计算实质为“=A1∗B1+A2∗B2”，结果值为“11”，在已知库存发出队列和入库单价时，Sumproduct函数用于计算存货发出成本。
在用Excel实现过程中还用到了单元格绝对引用与相对引用。绝对引用是引用指定位置的单元格，当公式所在单元格的位置发生改变时，绝对引用保持不变；相对引用是包含公式和引用单元格相对位置的引用，当公式所在单元格的位置改变时，引用也随之改变。
例如，在B3单元格内输入“=SUM（B$1：B2）”，表示求B1到B2的和，当拖动B3单元格向下填充至B4时，B4单元格公式变成“=SUM（B$1：B3）”，表示求B1到B3的和，绝对引用和相对引用用于方便地求解先进先出队列。

所设计模型的Excel表格格式设置如图2，图2中的表格可分为两部分，A到J列为第一部分，它详细列示了商品收发存的信息，称作收发存明细表， K到N列为第二部分，它详细列示了存货发出成本的计算，称作辅助计算表，现分别对其格式和公式设置说明如下：
在收发存明细表中，A列为入库时间，可根据需要自行增减；B到D列为入库信息，B列为入库数量，C列为入库单价，D列为入库金额，入库金额等于入库数量乘以入库单价；E到G列为出库信息，E列为出库数量，F列为出库单价，G列为出库金额，出库金额区域（G4：G6）依次引用区域L7：N7，出库单价等于出库金额除以出库数量；H到J列为库存信息，H列为库存数量，I列为库存单价，J列为库存金额，库存数量等于上期库存数加上本期入库数减去本期出库数，库存金额等于上期库存金额加上本期入库金额减去本期出库金额，库存单价等于库存金额除以库存数量，收发存明细表中公式的设置比较简单，此处不做介绍。
在辅助计算表中，K4：K6是辅助区域，即表1中的[X1，0]，[X2，0]，[X3，0]，值均设置为0；L2：N2为出库数量，依次引用区域E4：E6；L3：N3是辅助区域，即表1中的[X0,1]，[X0,2]，[X0,3]，值均设置为0；L4：N6是库存发出队列区域，L4单元格公式为“=MIN（L$2-SUM（L$3:L3）,$B4-SUM（$K4:K4））”，由于L4单元格的公式已包含了绝对引用和相对引用，拖动L4单元格向下填充至L6，再拖动L6单元格的向右填充至N6，即可成功设置L4：N6其余单元格的公式；L7：N7是库存发出金额计算区域，L7单元格公式为“=SUMPRODUCT（$C4:$C6，L4:L6）”，拖动L7单元格的向右填充至N7，即可成功设置M7：N7单元格的公式。
至此，用Excel实现了先进先出法存货发出成本的自动计算。可以看出，使用该方法用到的关键公式只有两个，而通过精准地设置绝对引用和相对引用，这两个公式又可以实现快速复制填充，应用到其他单元格，因此，在存货种类不多时，用该方法计算先进先出存货发出成本是具有一定的可操作性的。
三、借助VBA设计先进先出存货发出成本计算函数
用EXCEL已有函数固然可以实现先进先出法存货发出成本的自动计算，然而对于存货进出量大且频繁的企业，对每种存货都设置辅助计算表，将会是一项巨大的工程，因此如果能像使用Sum函数一样，仅仅通过输入必要的参数即可实现先进先出存货发出成本的计算，将会大大提高工作效率，庆幸的是，VBA自定义函数为此提供了可能。
自定义函数是Excel中一个强大的扩展功能，它可以实现不同用户的特定计算要求，编写自定义函数需要一定的VB或VBA基础，此处不做过多介绍，以下仅对该函数的关键点进行说明并给出完整的代码，具体语法请参考相关书籍。
所编写自定义函数名称为FIFO，函数参数为3个对象变量IQ、IP和OQ，其中，IQ为入库数量，IP为入库单价，OQ为出库数量；函数返回值为一组数据，即出库金额序列。该函数设计要点是，将用户选定区域的入库数量、入库单价和出库数量读入三个数组中，然后再设置辅助数组模拟“辅助计算表”进行计算，并将计算的结果返回给函数输出，具体代码如下：
Public Function FIFO（IQ As Range, IP As Range, OQ As Range） As Variant
Dim aIQ（）
Dim aIP（）
Dim aOQ（）
Dim aOM（）
Dim aOQS1（）
Dim aOQS2（）
Dim aOQS（）
ReDim aIQ（1 To IQ.Count）
ReDim aIP（0 To IQ.Count）
ReDim aOQ（1 To OQ.Count）
ReDim aOQS1（0 To IQ.Count, 0 To OQ.Count）
ReDim aOQS2（0 To IQ.Count, 1 To OQ.Count）
ReDim aOQS（0 To IQ.Count, 1 To OQ.Count）
ReDim aOM（1 To OQ.Count）
i = 1
For Each cell In IQ
aIQ（i） = IQ（i）
i = i + 1
Next cell
i = 1
For Each cell In IP
aIP（i） = IP（i）
i = i + 1
Next cell
i = 1
For Each cell In OQ
aOQ（i） = OQ（i）
i = i + 1
Next cell
For i = 0 To OQ.Count
aOQS1（0, i） = 0
Next
For i = 0 To IQ.Count
aOQS1（i, 0） = 0
Next
For i = 1 To OQ.Count
aOQS2（0, i） = 0
Next
For i = 1 To OQ.Count
aOQS（0, i） = 0
Next
For i = 1 To OQ.Count
aOM（i） = 0
Next
For i = 1 To IQ.Count
For j = 1 To OQ.Count
aOQS（i, j） =Min（aIQ（i） - aOQS1（i, j - 1）, aOQ（j） - aOQS2（i - 1, j））
aOQS1（i，j）=aOQS1（i，j-1）+aOQS（i，j）
aOQS2（i，j）=aOQS2（i-1，j） + aOQS（i，j）
Next j
Next i
For j = 1 To OQ.Count
For i = 1 To IQ.Count
aOM（j） = aOM（j） + aIP（i）∗aOQS（i, j）
Next i
Next j
FIFO =Transpose（aOM）
End Function
以图2中的数据为例，说明自定义函数的使用。打开图2数据所在Excel表格，使用“Alt+F11”快捷键，进入VBA界面，点击“插入\模块”子菜单，在插入的代码输入窗口中输入以上代码，点击保存按钮，关闭VBE界面。选中“G4：G6”单元格区域，删除其中的数据，在编辑栏中输入“=FIFO（B4:B6,C4:C6,E4:E6）”，同时按下Ctrl键、Shift键和Enter键，即可看到处理后的结果。利用VBA的封装技术可以将以上代码封装成加载宏，加载后即可使用，在此不做详述。
四、小结
本文首先建立简化数学模型，给出了先进先出法存货发出成本计算过程中分解存货发出数量的思路，然后借助Excel，设计了两种自动计算先进先出存货发出成本的方法。
方法1虽然相比方法2略显复杂，但其所使用的公式却是基础公式，更不需要编制程序，且在其计算过程中产生的辅助计算表也具有一定的分析价值，当发出成本数据异常变动时，可以从分析发出产品的结构入手，清晰明了地找出问题所在。方法2操作简便，灵活性强，计算结果直观，在存货种类众多、进出量频繁时更能显现其优越性。
财务人员可根据企业的实际情况，选择适合的方法，实现先进先出存货发出成本的自动计算，提高工作效率。
主要参考文献
何燕.运用Excel制作先进先出法、移动加权平均法存货自动计价模型.财会月刊，2011；11