Excel Inventario LIFO/FIFO Técnica Avanzada con LAMBDA

Excel Inventario LIFO/FIFO Técnica Avanzada con LAMBDA

Cómo aplicar la función recursiva en Excel para transformar los tediosos procesos de valoración de inventario, mediante los métodos LIFO y FIFO

La gestión de inventarios siempre genera cierta complejidad en Excel por los distintos métodos de valoración que existen: precio medio ponderado, FIFO y LIFO. En lugar de llenar la hoja de cálculos intermedios y columnas auxiliares, en este tutorial construimos una única función personalizada que nos da la respuesta directamente. Aprovechamos la recursividad de las funciones LAMBDA para resolver un caso práctico real y obtener, para cada movimiento, el estado exacto de nuestro almacén.

El planteamiento del caso

Partimos de una tabla con una serie de movimientos de almacén. Los valores positivos son entradas (compras) y los negativos son salidas. Cuando hay una entrada, esta lleva asociado un precio, que es el precio de compra de ese paquete de unidades. El reto habitual con este tipo de ejercicios es saber cómo valorar el stock que tenemos o cómo valorar las salidas realizadas durante un periodo.

El objetivo es crear una función, que hemos llamado inventario, que para cada movimiento nos devuelva el estado del almacén en ese momento. Como salida obtenemos un rango que representa, literalmente, las piezas que tenemos almacenadas con su precio asociado.

Cómo se comporta la función paso a paso

Si arrastramos la función con la ayuda de DESREF para ir avanzando movimiento a movimiento, vemos cómo evoluciona el almacén:

  • Compramos 10 unidades: cargamos 10 piezas a precio 10.
  • Sale una salida de 5: nos quedan 5 piezas.
  • Sale otra de 4: nos queda 1 pieza.
  • Entran 20 unidades valoradas a 8: ahora conviven el saldo anterior con las 20 nuevas filas.
  • En las siguientes salidas se aplica el método FIFO (primera entrada, primera salida): cuando sale una cantidad, se eliminan primero las unidades más antiguas del almacén.

Tras varias compras y salidas, el rango final refleja el almacén con sus distintos tramos de precio. Lo más útil es que, una vez tenemos esa salida en un rango, calcular el valor del inventario es tan sencillo como hacer una SUMA del resultado de la función. Esa suma nos da la evolución del valor del almacén en cada uno de los periodos.

La estructura interna de la función LAMBDA

La función inventario tiene tres argumentos:

  1. matriz: el rango con todos los movimientos de inicio.
  2. ronda: opcional. Indica en qué recursión estamos, es decir, qué número de fila estamos procesando.
  3. output: opcional. Es el resultado acumulado que se va arrastrando de una recursión a la siguiente.

Los argumentos ronda y output van entre corchetes porque son parámetros opcionales: no son obligatorios, pero si vienen informados la función los aprovecha. La clave de todo es la recursividad: la función se llama a sí misma una y otra vez para ir acumulando, quitando y poniendo unidades en la celda resultado.

La condición de parada

Toda recursión necesita una condición que la detenga. Aquí indicamos que cuando la ronda supera el número de filas de la matriz, la función deja de ejecutarse y devuelve el resultado, excluyendo la primera fila (un pequeño juego necesario porque en la primera ronda todavía no existe una matriz resultado).

El cálculo en cada ronda

En cada llamada, con la función LET, se preparan las variables intermedias:

  • Se detecta si llega un output precargado. En la primera ronda viene vacío, así que se le asigna un valor vacío (""); en las siguientes, se usa el rango acumulado.
  • Se comprueba en qué ronda estamos. Si no existe, es la primera (valor 1); si existe, se le suma un paso para seguir contando las recursiones.
  • Con la función INDICE se buscan las unidades y el precio del movimiento que corresponde a la ronda actual sobre la matriz inicial (la columna 1 son las unidades y la columna 2 el precio).
  • Con la función SECUENCIA se genera el tramo de unidades que entran, repitiendo el precio en cada fila (con paso 0 para que el valor no se incremente).

El núcleo de la lógica FIFO / LIFO

El resultado de cada ronda se decide con una condición sobre las unidades:

  • Si las unidades son mayores que cero (es una compra), se apila el rango acumulado con el nuevo tramo generado.
  • Si las unidades son negativas (es una salida), se usa la función EXCLUIR para quitar filas del rango acumulado. Como las salidas vienen en negativo, se les cambia el signo para indicar a EXCLUIR cuántas filas eliminar desde el principio.

Aquí está el secreto para cambiar de método. Quitando primero las filas iniciales aplicamos FIFO (lo primero que entró es lo primero que sale). Si quitamos ese tratamiento de la primera fila y eliminamos en el otro extremo, aplicamos LIFO (la última entrada se convierte en la primera salida): las unidades antiguas se mantienen y lo que sale corresponde siempre a lo más reciente.

Por qué merece la pena

Estos algoritmos de selección no se limitan al inventario de materiales. La misma lógica recursiva sirve para gestión de flujos, asignación de tareas y muchos otros casos. Las fórmulas avanzadas y los arrays dinámicos pueden parecer difíciles al principio, pero practicando paso a paso se convierten en una herramienta que agiliza enormemente el trabajo diario.

En definitiva, con una sola función LAMBDA recursiva apoyada en INDICE, SECUENCIA y EXCLUIR resolvemos la valoración de inventario por FIFO o LIFO sin saturar la hoja de cálculos auxiliares. Te animamos a descargar el fichero del ejercicio y adaptarlo a tu propia gestión.

Más casos con estas funciones

Más contenido de Excel en InflueXcel