在使用Excel进行库存管理时,我们需要计算库存成本(包括当前库存成本以及每次出库的成本)。当涉及到同一物品多次出库入库时,这种计算需要按照预先指定的规则进行,常用的规则有:
FIFO(先入先出法):即每次出库都从库存中最早的入库物资开始,如果最早的入库数量不足以保证出库数量,那么剩余数量的物资依次从以后的每条中进行出库。
LIFO(后入先出法):与FIFO相反,每次出库从最近的入库开始。
平均法
不管是FIFO,还是LIFO,实际计算的时候都比较复杂。主要是很难利用Excel公式进行自动化的计算工作。
用Excel进行库存管理时,我们建议维护两张表:
入库表
出库表
其中,入库表的数据是手工输入的。出库表的前4列是手工输入的,出库单件是需要计算的(即出库成本)。
很明显,要计算入库后的平均单价很容易。比如,要计算2022/2/10的库存成本(在这一天,所有的入库已经完成,并且没有发生任何出库操作)
只要将入库量和价格做加权平均就好了:
=SUMPRODUCT(入库量, 价格)/SUM(入库量)
但是一旦发生一次出库,比如要计算2022/3/2的库存成本,就需要知道2022/3/1这次出库是从哪些入库记录中进行的。尤其是,如果出库涉及多条入库时,这个计算并不容易。
注:这两个表是简化后的表,只有必要的字段,实际场景可能需要更多的字段。不过不影响我们对这个问题的理解。
注:很多使用Excel管理库存的朋友喜欢在一张表上同时维护入库和出库信息,实际上也没有问题。本文介绍的方法很容易就可以移植到这种场景中。
注:还有很多朋友喜欢在一张表上同时维护入库、出库和当前库存信息。强烈不建议这么做。在Excel中这么做会带来很多不利的影响。在“Excel工作的标准模式”课程中,我们详细介绍过,这里就不重复了。实际上,当前库存可以很容易通过函数得到。
注:本文案例中入库表和出库表只有一种物品,并且是按照日期顺序进行升序排列。实际情况并不如此,但是我们仍然可以这么假设,因为我们可以通过FILTER函数和SORT函数轻松达到这样的要求。
下面我们先以手工的方式分析第一次出库时FIFO的操作。
在计算库存成本时,其实最重要的就是要知道每次出库需要从哪些入库记录中进行操作,所以下面的分析就集中在这个环节。
最左边是入库记录,按照入库日期升序。最右边是出库数量:100。
首先去到第一条入库记录,即“15”这一条,因为15<100,所以全部的数量用来出库,即左边第二列中的第一个数据。同时,需要出库的数量变成了100-15=85,即右边倒数第二列中的第一个数据。
然后去到第二条入库记录,即“20”这一条,因为20<85,所以全部的数量用来出库,得到第二列中的第二个数据,同时,需要出库的数量变成了85-20=65。
依次类推,直到第五次操作结束,即需要出库的数量为16。
此时,需要对第六条入库记录进行出库操作,即“22”这一条,因为22>16,所以,只需要出库16个物品就可以了。而需要出库的数量变成了16-16=0。这就意味着出库任务已经完成,不再需要后续的操作了。
此时,左侧第二列就是本次出库的明细,而用入库(左侧第一列)减去这个明细,即得到出库后的库存明细。
这是非常清晰的一个过程。如果用VBA写程序,只需要一个循环即可。可惜,VBA的门槛稍高,而且在很多公司,VBA是禁用的。
如果改用传统的Excel函数来做这件事情,会发现非常困难,几乎没有办法做到自动化的处理,需要依赖中间表和手工的干预,很不理想。
但是借助LAMBDA函数,我们可以轻松实现库存成本的计算。
我们要借助于递归,不熟悉这个概念的朋友可以参见Excel这个函数功能竟然暗合孙子兵法 - 详说递归函数:什么是递归?递归能干什么?递归怎么做?。
我们的目标是计算需要出库的数量(OUT_QTY)在入库记录表S中的分布情况,假设这个计算可以通过函数:
F(out_qty, S)
来完成。
我们将S分成两部分:q和Q,
其中q就是S的第一条记录,Q是剩余的记录。
根据FIFO的原则,我们首先处理q,使用函数:
F(out_qty, q)
由于q只有一条记录,因此它很简单就可以解决:
IF(out_qty <= q, out_qty, q)
处理完q后,剩余需要出库的数量变成了:
out_qty_rest = out_qty - F(out_qty, q)
这些数量就需要在Q中进行出库:
F(out_qty_rest, Q)
我们需要的结果就是:
VSTACK(F(out_qty, q), F(out_qty_rest, Q))
根据上面的分析,我们可以实现下面的自定义函数:
可以直接调用这个函数:
=FIFO(D4:D11,G2)
得到这样的结果:
出库明细有了,我们是本次出库的平均单价,还是出库后的库存平均单价都很容易计算了。
我们这里只是处理了一次出库的情况。要处理后续出库的情况,还需要进行多一些的处理。传统的函数方案在进行到这一步时,也会遇到比较大的问题。不过使用我们这里的方案,就很简单了,只要计算出在本次出库前库存明细,并且将这个库存明细作为当前出库的依据,从而使用FIFO进行出库即可。
这个自定义函数计算给定日期之前的库存明细。其中,
1. in_qty_col,为入库记录的数量列
2. qty_out,为给定日期之前所有出库的数量总和
3. 通过FIFO函数计算历史上所有出库数量总和在入库记录上的明细,并用入库记录减去这个出库明细,即得到给定日期时的库存分布。
通过下面的自定义函数即可计算本次出库的平均单价:
其中,
计算本次出库日期之前的库存明细
取出库存明细中的数量列
用库存明细数量列作为出库依据,进行当前要求的出库,并计算出出库明细。
最后,用这个出库明细,结合入库时每次记录的单价,加权平均即可得到本次出库的平均单价了。这个公式在本文开头就介绍过了。
详细解释请看视频
加入E学会,永久免费学习更多Excel应用技巧
http://www.tropic.com.cn/portal/learn/class_list
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
Excel企业应用 底部菜单:企业应用
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服