作为曾在快消食品饮料供应链计划领域奋斗多年的计划老兵，我们在日常工作中有一个绕不开也躲不开的痛点是临期库存预估。因计算复杂，在缺乏专业计划系统算法加持的情况下，每次临期预估的手工计算背后都是加班到深夜辛酸史，除了计算耗时长，无法快速响应业务诉求，计算结果也可能因手算失误出错。
为了高效响应业务诉求，更好在计划层面起到”防火”的作用，同时将计划团队从加班加点的工作状态释放出来，我们探索了基于Excel实现临期库存预估仿真推演的解决方案。
考虑临期日期等名词在不同公司的定义不一致，我们首先来明确本文涉及效期管理的相关名词含义：①到期日期：生产日期 + 保质期②临期日期：到期日期 – 客户最晚收货效期要求；比如某啤酒品牌330ml听装的保质期是12个月，批次A的到期日期是2023/11/25，客户收货效期要求是剩余保质期天数大于6个月，则批次A的临期日期是2023年5月26日：即批次A的库存如果不能在2023年5月26日前全部消耗，则剩余的库存因为不满足客户收货效期要求，从供应角度，不能视为有效供应。
接下来我们来说说为什么临期预估是一个绕不开也躲不掉的计算点：不同于其他行业，导致快消食品行业的订单缺货除了数量短缺外，还有可能是因为效期不满足。以下图为例，客户订单数量是30 Pcs，在库数量是60 Pcs，但符合效期要求的库存只有10 Pcs，订单缺口 20 Pcs。
从订单满足的角度考虑，如果库存效期不满足，将产生缺货，从库存管理的角度，如果在超过客户收货效期外还有剩余库存，则此部分库存大概率将计入报废。因此在快消食品的供应计划层面，必须考虑库存是否符合效期要求，尤其是对于采购周期长的进口食品：不少公司的供应模式是M+2，M+3，甚至是M+4，受疫情和国际环境影响，需求端和供应端不确定性加大：低卖或预测下调，海运到货或进口报关延迟等均可能导致库存无法在有限的效期要求内完成消耗，这就要求供应链计划管理具备快速响应能力，能够及时推算和预警临期库存，以及制定相应的解决方案。
关于临期库存的预估，大致逻辑是将某个时间节点的总供应，包含在库库存，在途库存，以及已锁定的采购PO等，基于未来的销售预测和效期约束等条件，按先进先出等逻辑，推算各个批次库存在客户收货效期要求前可消化的数量，以及剩余的临期数量。
临期预估计算的复杂性主要体现在：①效期要求：大多数的公司采取的是多渠道运作模式，比如B2B，B2C等，而不同渠道对于收货效期要求不一样，一般而言B2B渠道的效期要求高于B2C渠道，因此对于同一个SKU在效期计算上要考虑多个效期的要求；②剩余效期：同一个SKU包含有多个批次，不同批次对应不同的生产日期、到期日期，以及剩余效期。
对于同一个SKU，临期预估是在先进先出逻辑下，多个批次和多个渠道在效期和数量判断上的多对多运算，此外还要考虑不同批次在途库存的预计可售日期等因素。
接下来我们结合实际案例来看看如何通过Excel预估临期库存：
Step 1 明确业务诉求和主要输入业务诉求：基于2022年3月1日的时间节点对应的供需数据，估算330听装的临期数量主要输入：①By SKU + By 销售渠道 + By 月份的预测量；②By SKU + By 销售渠道的效期要求(天数)；③By SKU + By 批次 + By 可用日期的库存量
Step 2 梳理临期预估的计算逻辑临期预估的计算逻辑切换到仿真业务场景，可以理解为每日的进销存推演，首先将月预测按一定的拆分规则转换为单日订单，然后从计划展期的第一天开始，判断在库的库存数量和剩余效期天数是否符合渠道A的发货效期和数量要求，如果不符合，则按先进先出原则，判断下一个批次，如果符合，则扣减相应批次的库存数量，并作为第一天推演过程中渠道B的期初库存，然后采用相同逻辑进行渠道B的进销存推演；在完成第一天所有渠道的计算后，更新库存数量和剩余效期天数，作为第二天的期初库存，然后依次对渠道A，渠道B进行进销存推演。By Day的循环计算结束后，如果某个批次的期末库存量大于0，同时预测覆盖的时间长度已超过该批次的临期日期，则可将期末库存量视为临期数量。
Step 3 构建临期预估的仿真模型①化繁为简，通过Excel的If等公式搭建单日单渠道的进销存模型，判断期初库存的效期和数量是否满足需求，以及输出预估的消耗量。
②在搭建完单日单渠道的进销存模型后，下一步就是借助Excel VBA的for循环语法，以及自动写入公式等基础功能，实现By Day + By Channel进销存的滚动循环计算。
Step 4 实现临期预估的仿真推演通过点击按键，执行VBA代码，程序即可按设定的临期预估推演逻辑，自动计算出临期预估量；不论涉及渠道有多少个，滚动计划周期是一年还是两年，均是一键生成计算结果，并实现和专业计划系统一样的精算。
Excel仿真推演解决方案的落地应用，帮忙计划团队强化了在复杂业务下快速响应业务需求，高效输出临期预估分析，并制定解决方案的能力。