Cómo calcular el punto de reorden y stock de seguridad en Excel
Como profesional en ingeniería industrial y analítica de datos, sabes que la gestión eficiente de inventarios es crítica para la rentabilidad. Un desabastecimiento (stockout) detiene la producción, mientras que el exceso de stock inmoviliza capital. Este instructivo te guiará paso a paso para automatizar el cálculo del Punto de Reorden (ROP) y el Stock de Seguridad (SS) utilizando las potentes herramientas de Microsoft Excel.
Definición del problema
Imaginemos que gestionamos la cadena de suministro de una empresa de manufactura de componentes electrónicos. Tenemos un producto clave, el «Microcontrolador X100», cuya demanda es variable y el tiempo de entrega (Lead Time) del proveedor no es perfectamente constante. Si no sabemos cuándo reordenar y cuánto tener de colchón, corremos el riesgo de paralizar la línea de ensamblaje.
El reto operativo es determinar dos métricas vitales:
- Punto de Reorden (ROP): El nivel de inventario en el que se debe emitir una nueva orden de compra para evitar quedarse sin stock antes de que llegue el pedido.
- Stock de Seguridad (SS): La cantidad extra de inventario que se mantiene para protegerse contra fluctuaciones inesperadas en la demanda o retrasos en el suministro.
Para resolver esto, necesitamos analizar datos históricos de consumo y variabilidad de la entrega.
Explicación técnica
El cálculo se basa en la gestión de inventarios y requiere entender la relación entre la demanda promedio, la variabilidad (desviación estándar) y el nivel de servicio deseado.
Conceptos clave y funciones de Excel:
- Se calcula usando la función
=PROMEDIO()sobre los datos históricos de consumo. - Tiempo de Espera (Lead Time, $L$): El tiempo que tarda el proveedor en entregar el material.
- Stock de Seguridad (SS): Se calcula mediante la fórmula:
.- Z (Factor de Servicio): Es el valor Z de la distribución normal asociado al nivel de servicio deseado (ej. 95% de servicio $rightarrow Z approx 1.645$). Este valor se obtiene de la función
INV.NORM.ESTANDAR()en Excel.
- Z (Factor de Servicio): Es el valor Z de la distribución normal asociado al nivel de servicio deseado (ej. 95% de servicio $rightarrow Z approx 1.645$). Este valor se obtiene de la función
- Punto de Reorden (ROP): Se calcula como:
ROP = (Demanda Promedio Diaria $times$ Lead Time) + Stock de Seguridad.
Guía paso a paso
Paso 1: Estructuración de los datos históricos de demanda
Debemos ingresar los datos de consumo diario del «Microcontrolador X100» durante los últimos 30 días para calcular la demanda promedio y su variabilidad.
Acción: Abre una hoja de cálculo y organiza los datos como se muestra a continuación.
Resultado: Una base de datos limpia lista para el análisis estadístico.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fecha | Demanda Diaria (Unidades) | Lead Time (Días) | Nivel de Servicio (%) |
| 2 | 01/05 | 120 | 10 | 95% |
| 3 | 02/05 | 135 | 10 | 95% |
| 4 | 03/05 | 110 | 12 | 95% |
| 5 | 04/05 | 140 | 10 | 95% |
| 31 | 31/05 | 125 | 10 | 95% |
Paso 2: Cálculo de la Demanda Promedio y Desviación Estándar
Usaremos las funciones estadísticas de Excel para cuantificar la demanda histórica.
Acción: En una celda vacía (ej. B33), calcula la media de la columna B (Demanda Diaria). En otra celda (ej. B34), calcula la desviación estándar muestral de la columna B.
Fórmulas a ingresar:
=PROMEDIO(B2:B32)- Desviación Estándar ($sigma_d$):
=DESVEST.M(B2:B32)
Resultado: Obtendremos valores numéricos que representan el consumo típico y su dispersión.
Paso 3: Determinación del Factor Z y Stock de Seguridad (SS)
El nivel de servicio (95%) define qué tan seguro queremos estar. Usamos la función inversa de la distribución normal.
Acción:
- En una celda (ej. B35), calcula el Factor Z para el 95% de servicio.
- En otra celda (ej. B36), calcula el Stock de Seguridad (SS).
Fórmulas a ingresar:
- Factor Z:
=INV.NORM.ESTANDAR(0.95)(El 0.95 representa el 95% de probabilidad acumulada). - Stock de Seguridad (SS):
=B35 * DESVEST.M(B2:B32) * RAIZ(C2)(Asumiendo que el Lead Time promedio es el valor en C2).
Resultado: El valor de SS es la cantidad de unidades que debemos tener «en reserva» para cubrir imprevistos.
Paso 4: Cálculo del Punto de Reorden (ROP)
Finalmente, combinamos la demanda esperada durante el tiempo de espera con el colchón de seguridad.
Acción: En la celda final (ej. B37), calcula el ROP.
Fórmula a ingresar:
- ROP:
=(PROMEDIO(B2:B32) * C2) + B36
Resultado: El valor en B37 es el umbral de inventario. Cuando el stock físico caiga a este nivel, se debe generar la orden de compra.
| B | C | D | |
|---|---|---|---|
| 32 | 125 | 10 | 95% |
| 33 | 128.5 | ||
| 34 | 15.2 | ||
| 35 | 1.645 | ||
| 36 | 25.0 | ||
| 37 | 1435.0 |
Ejercicio propuesto
Para consolidar el aprendizaje, modifica el escenario:
- Aumenta el Nivel de Servicio: Cambia el nivel de servicio del 95% al 99%. ¿Cómo afecta esto al Stock de Seguridad y al ROP? (Deberías notar un aumento significativo en el SS).
- Incrementa la Incertidumbre: Introduce un nuevo conjunto de datos históricos donde la desviación estándar ($sigma_d$) sea un 50% mayor que la inicial. ¿Cómo reacciona el ROP ante un proveedor más errático?
- Analiza el Costo: Si el costo de mantener un inventario extra (costo de almacenamiento) es de $0.50 por unidad/mes, calcula el costo adicional mensual que implica pasar de un nivel de servicio del 95% al 99%.
Este ejercicio te obliga a entender la compensación entre el riesgo de desabastecimiento y el costo de mantener inventario.
Errores habituales
Al implementar estos modelos, es común caer en trampas conceptuales o de implementación en Excel. Presta atención a estos puntos:
- Confundir Desviación Estándar Poblacional vs. Muestral: Si tus datos históricos son solo una muestra de la demanda real (lo más común), debes usar
=DESVEST.M()(Muestral). Usar=DESVEST.P()(Poblacional) subestimará la variabilidad real y resultará en un Stock de Seguridad insuficiente. - Ignorar el Lead Time en la Desviación Estándar:
- Usar el Promedio Simple en lugar de la Media Ponderada: Si los tiempos de entrega ($L$) varían mucho, usar el promedio simple de la demanda diaria puede ser engañoso. Para mayor precisión, se recomienda calcular la demanda promedio ponderada por el tiempo de entrega, aunque para este tutorial, el promedio simple es suficiente si asumimos un $L$ constante.
Deja una respuesta