Método FIFO con fórmulas-Excel

Hoy toca un artículo clásico de una hoja de cálculo (no olvides qué es Excel).
Hablaremos del cálculo de las unidades y valoración por el método FIFO multiartículo (First In - First Out, esto es, primero sacamos lo más antigüo).

Nos centraremos en la composición del stock/inventario y su valoración, y no del análisis de coste de ventas...
Un par de aspectos o condiciones importantes a tener en cuenta:
Premisa 1: Entradas de un mismo artículo en un mismo día se registran en un solo movimiento.
Premisa 2: Los movimientos de entrada-salida deben ser lógicos... esto es, no puedo vender/dar salida a artículos que no tengo!!

Empecemos con un modelo que registre nuestros movimientos de 'Entrada' y 'Salida', donde anotamos obviamente Fechas del movimiento, las unidades y su precio unitario...

Nos centraremos en primer lugar en obtener, en la columna 'Stock vivo' el dato de unidades acumuladas por artículo (un 'running total' integrado que tenga en cuenta entradas y salidas para artículo).
Así pues la fórmula que integraremos será:
=SUMAR.SI.CONJUNTO([Cantidad];[Tipo Mov];"Entrada";[Cód art];[@[Cód art]];[Fecha];"<="&[@Fecha])
-SUMAR.SI.CONJUNTO([Cantidad];[Tipo Mov];"<>Entrada";[Cód art];[@[Cód art]];[Fecha];"<="&[@Fecha])
Vemos que la fórmula responde a la diferencia entre dos simples funciones SUMAR.SI.CONJUNTO, jugando con los rangos de fechas y con los tipos de movimientos.

En la siguiente columna/campo (lo he llamado 'Calc Aux') añado un cálculo intermedio donde obtenemos, de alguna manera, el número de unidades consumidas para ese registro de Entrada...
OJO!!, es un cálculo no directo y no válido por sí mismo... solo tiene sentido junto a la siguiente columna!!
La fórmula sería: =SI([@[Tipo Mov]]<>"Entrada";
0;
MIN( SUMAR.SI.CONJUNTO([Cantidad];[Tipo Mov];"Entrada";[Cód art];[@[Cód art]];[Fecha];"<="&[@Fecha]);SUMAR.SI.CONJUNTO([Cantidad];[Tipo Mov];"<>Entrada";[Cód art];[@[Cód art]];[Fecha];">="&[@Fecha]) ))
Importante jugar con la función MIN para evitar 'consumos' negativos... Además nos centramos únicamente en los registros de 'Entrada', ya que los consumos se deben producir minorando estas entradas precisamente.

Finalmente aplicamos en una nueva columna 'Unidades pendientes' donde la fórmula será:
=SI([@[Tipo Mov]]<>"Entrada";0;MIN([@[Stock vivo]]-[@[Calc Aux]];[@Cantidad]))
Aquí obtenemos por diferencias el número de unidades restantes en cada momento de cada movimiento de 'Entrada'... tal como queríamos.

Adicionalmente podemos añadir un último campo 'Valoración FIFO' como producto de:
=[@[Unidades pendientes]]*[@[Precio Unitario]]
que nos devuelve la valoración en euros, según el método FIFO, de las unidades en Stock.

Comprobemos alguno de los movimientos para un artículo...

Veamos el histórico de movimientos para el artículo '111':
13/01/2021 - Entrada = 22
26/02/2021 - Entrada = 15
18/07/2021 - Salida = -3
11/08/2021 - Salida = -9
26/09/2021 - Salida = -11
05/10/2021 - Entrada = 10
21/10/2021 - Salida = -16

La secuencia temporal sería que a 26/02/2021 tras las dos primeras compras disponemos de 37 uds (22 uds + 15 uds):

En el siguiente movimiento recogemos una primera salida de 3 unidades, la cual minorará la primera de las compras (FIFO), por tanto el total de unidaes ahora será de 34 (22 uds + 15 uds - 3 uds).
Quedando de la primera compra 22 uds - 3 uds = 19 uds.
Y la totalidad de la segunda compra = 15 uds

El siguiente movimiento es una salida de 9 uds... Nuevamente salen de la primera compra.
Por tanto del artículo '111' tenemos en este instante 25 uds (22 uds + 15 uds - 3 uds - 9 uds).
Quedando de la primera compra 22 uds - 3 uds - 9 uds = 10 uds.
Y la totalidad de la segunda compra = 15 uds

Otro movimiento, en la fecha siguiente, será en este caso nuevamente de 'Salidda' por un total de 11 uds.
Esto nos dice que en el artículo '111' tenemos 14 uds (22 uds + 15 uds - 3 uds - 9 uds - 11 uds).
Quedando de la primera compra 22 uds - 3 uds - 9 uds - 10 uds = 0 uds.
Y la totalidad de la segunda compra con 14 uds = 15 uds - 1 ud (una unidad menos de la última salida..)

Vamos con el siguiente movimiento... en este caso representamos una 'Entrada' de 10 uds.
Fácil de interpretar.. aumenta nuestro total en 10 hasta las 24 uds (22 uds + 15 uds - 3 uds - 9 uds - 11 uds + 10 uds).
Quedando de la primera compra 22 uds - 3 uds - 9 uds - 10 uds = 0 uds.
La segunda compra con 14 uds = 15 uds - 1 ud
La tercera compra con 10 uds

Por último nos fijamos en la última salida de 16 uds.
Nos deja con un acumulado de 8 uds (22 uds + 15 uds - 3 uds - 9 uds - 11 uds + 10 uds - 16 uds).
Quedando de la primera compra 22 uds - 3 uds - 9 uds - 10 uds = 0 uds.
La segunda compra con 15 uds - 1 uds - 14 uds (última salida) = 0 uds
La tercera compra con 8 uds = 10 uds - 2 uds (última salida)

Concluimos entonces nuestra comprobación, bajo las premisas iniciales... donde parece que se cumplen las condiciones del método FIFO para todos los casos...
¿Conoces alguna fórmula más directa???