Guía
Predictor de stock en Excel: cinco columnas que te dicen qué se va a acabar y cuánto pedir
Se puede montar un predictor de stock básico en Excel, sin pagar nada, con funciones que ya trae el programa. No será el de una multinacional, pero calcula lo mismo que uno de pago en su versión más sencilla: venta prevista, riesgo de rotura y cantidad a pedir. Aquí tienes las fórmulas exactas, en español y en inglés, y un ejemplo resuelto para que compruebes que te salen los mismos números.
La respuesta corta
Para hacer un predictor de stock gratis en Excel necesitas una fila por producto con las ventas de las últimas 8 semanas, el stock, lo pendiente de recibir, el plazo en semanas y la caja de compra. Con PROMEDIO y DESVEST.M sacas venta media y variación; con DISTR.NORM.N, el riesgo de rotura en el plazo; con INV.NORM.ESTAND, el colchón para tu nivel de servicio; y con REDONDEAR.MAS, la cantidad a pedir en cajas.
Qué vas a montar
Una hoja con una fila por producto. A la izquierda, lo que escribes tú: las ventas de las últimas ocho semanas, el stock de hoy, lo que ya has pedido y no ha llegado, el plazo del proveedor en semanas, cada cuántas semanas revisas y cuántas unidades trae la caja. A la derecha, cinco columnas calculadas: venta media semanal, variación, riesgo de rotura, objetivo de stock y cantidad a pedir.
Es la versión de hoja de cálculo de lo que explicamos en cómo funciona un predictor de stock. Usa la media de las últimas semanas como previsión y supone que la venta se reparte como una normal. Para productos que se venden todas las semanas en cantidades parecidas funciona bien; más abajo están los casos en que no.
Las columnas que rellenas
Pon una cabecera en la fila 1 y empieza los productos en la fila 2. Esta es la disposición que usan todas las fórmulas de la guía:
- A: referencia del producto.
- B a I: ventas de las semanas 1 a 8, de la más antigua a la más reciente, en unidades.
- J: stock de hoy.
- K: pendiente de recibir (pedido hecho que aún no ha llegado).
- L: plazo del proveedor en semanas (2 si tarda 14 días).
- M: cada cuántas semanas haces pedido (1 si pides todos los lunes).
- N: unidades por caja o múltiplo de compra.
- O: nivel de servicio que quieres, como decimal (0,95 para un 95 %).
Las cinco fórmulas
Van en la fila 2 y se copian hacia abajo. Las escribimos con los nombres de Excel en español y el separador de punto y coma; entre paréntesis, el nombre en inglés por si tu Excel está en ese idioma (allí el separador es la coma).
- P, venta media semanal: =PROMEDIO(B2:I2) (AVERAGE).
- Q, desviación semanal: =DESVEST.M(B2:I2) (STDEV.S). Mide cuánto se mueve la venta de una semana a otra.
- R, riesgo de rotura antes de que llegue lo que pidas hoy: =1-DISTR.NORM.N(J2+K2;P2*L2;Q2*RAIZ(L2);VERDADERO) (NORM.DIST, SQRT). Es la probabilidad de vender en el plazo más de lo que tienes y lo que viene. Dale formato de porcentaje.
- S, objetivo de stock: =P2*(L2+M2)+INV.NORM.ESTAND(O2)*Q2*RAIZ(L2+M2) (NORM.S.INV). La venta esperada hasta el siguiente pedido que podrías recibir, más el colchón para tu nivel de servicio.
- T, cantidad a pedir: =REDONDEAR.MAS(MAX(0;S2-J2-K2)/N2;0)*N2 (ROUNDUP). Lo que falta para llegar al objetivo, redondeado hacia arriba a cajas completas.
Un ejemplo resuelto
Datos inventados. Un almacén de material de oficina en Valencia vende paquetes de folios A4. Las últimas ocho semanas vendió 42, 38, 51, 45, 40, 47, 39 y 48. Tiene 95 paquetes, nada pendiente, el proveedor tarda dos semanas, pide cada lunes, el proveedor sirve en cajas de 5 y quiere un 95 % de nivel de servicio.
La venta media sale 43,75 y la desviación, 4,71. En dos semanas de plazo se esperan 87,5 paquetes, con una desviación de 4,71 por la raíz de 2, unos 6,67. La probabilidad de vender más de 95 en ese plazo es del 13 %: ese es el riesgo de rotura de la columna R.
El objetivo cubre tres semanas (dos de plazo y una hasta el siguiente pedido): 43,75 por 3 son 131,25, más 1,645 por 4,71 por la raíz de 3, que son 13,43. Total, 144,7 paquetes. Con 95 en el almacén faltan 49,7, que redondeados a cajas de 5 son 50. Si copias los datos y no te sale 13 % y 50, revisa los separadores o el idioma de las funciones.
Cómo usarlo cada semana
El día que haces pedido, desplaza las ventas: lo de la semana 2 pasa a la 1, y así hasta la 8, donde escribes la semana que acaba de cerrar. Actualiza el stock y lo pendiente. Ordena la hoja por la columna R, de mayor a menor riesgo, y mira primero las filas de arriba. La columna T es la propuesta; cámbiala cuando sepas algo que la hoja no sabe.
Si prefieres no tocar fórmulas, la plantilla de pedido sugerido y la plantilla de punto de pedido ya traen una parte de esto montada, gratis y sin registro.
Y si quieres una previsión con temporada: PRONOSTICO.ETS
La media de ocho semanas no ve la temporada. Si vendes el doble en verano, en mayo te quedarás corto. Excel tiene una función para eso: PRONOSTICO.ETS (FORECAST.ETS), que según la documentación de Microsoft usa la versión AAA del suavizado exponencial y detecta la estacionalidad sola si la dejas en 1. Su sintaxis es PRONOSTICO.ETS(fecha_objetivo; valores; escala_de_tiempo; [estacionalidad]; [finalización_de_datos]; [agregación]), y la escala de tiempo tiene que tener un paso constante (semanas o meses seguidos).
Necesita bastante historia para ver la temporada: con datos semanales y un ciclo anual, al menos dos años. También existe la Hoja de previsión (Datos, Previsión), que hace lo mismo para una serie y dibuja el intervalo de confianza, al 95 % por defecto. Para pasar de ahí al riesgo y a la cantidad, sustituye la venta media de la columna P por la previsión de las semanas del plazo y mantén el resto de fórmulas. Lo contamos con más calma en previsión de demanda en Excel.
Dónde se queda corto un predictor en Excel
La hoja no sabe qué semanas estuvo el producto a cero. Si una semana vendiste 3 porque no había, esa semana baja la media y sube la desviación. Corrígelo a mano: borra esa semana o ponle la venta normal y anótalo.
Los productos que se venden a rachas rompen la fórmula. Con semanas de 0, 0, 12, 0, 1, la normal no describe nada y el riesgo que sale es engañoso. Para esos productos mira la guía de demanda intermitente.
Y no escala. Con 80 productos en un almacén es manejable. Con 400 productos en 25 tiendas son 10.000 filas que alguien tiene que actualizar cada semana sin equivocarse. Ese es el punto en que conviene mirar si seguir con Excel o pasar a un programa, o un predictor que lea tu Excel y haga las cuentas por ti.
Preguntas frecuentes
¿Hay un predictor de stock gratis de verdad?
La hoja de esta guía lo es: usa funciones que ya trae Excel y no necesita complementos. Los programas especializados, en cambio, son de pago.
¿Por qué ocho semanas y no el año entero?
Porque la media de un año mezcla temporadas y reacciona tarde. Ocho semanas es un compromiso entre tener datos suficientes y reflejar el ritmo reciente. Si tu venta es muy estable, puedes usar doce; si tiene temporada, pasa a PRONOSTICO.ETS.
¿Qué hago si la fórmula del riesgo da error?
Suele ser una desviación de cero (vendiste lo mismo las ocho semanas) o un plazo en blanco. Rodea la fórmula con SI.ERROR para que devuelva 0 en esos casos, o pon un plazo mínimo de 1.
¿Funciona en Google Sheets?
Las cinco fórmulas básicas usan funciones que Google Sheets también tiene (AVERAGE, STDEV.S, NORM.DIST, NORM.S.INV y ROUNDUP, con sus nombres en inglés). Nosotros las hemos probado en Excel. Si tu hoja no tiene PRONOSTICO.ETS, quédate con la media de ocho semanas.
¿Puedo usarlo con varias tiendas?
Sí, con una fila por producto y tienda. Pero el número de filas crece rápido, y repartir el stock del almacén entre tiendas cuando no llega para todas ya no cabe en una fórmula sencilla.