KHIZHNYAKOVCómo hacer un análisis ABC-XYZ del surtido en Excel
Análisis ABC-XYZ del surtido — plantilla de Excel — Ver la plantilla →
En casi cualquier tienda pasa lo mismo: unos pocos productos generan la mayor parte de las ventas, y muchos otros ocupan espacio en el almacén sin aportar casi nada. El problema es que, mirando la lista de productos, no siempre es evidente cuáles son unos y cuáles otros.
El análisis ABC-XYZ responde a dos preguntas a la vez: qué productos importan más por ingresos (ABC) y qué tan predecible es su demanda (XYZ). Combinados, dan nueve grupos con decisiones claras para compras y surtido. En esta guía verás cómo hacerlo en Excel con un ejemplo, y al final te presentamos una plantilla que automatiza todo el proceso.
Qué datos necesitas
Las ventas de cada producto por mes durante los últimos 12 meses, en dinero o en unidades (pero la misma unidad para todos). Para el ABC, normalmente se usan ingresos o margen; para el XYZ, unidades vendidas o ingresos mensuales. Con seis meses se puede empezar, pero doce capturan mejor la estacionalidad.
Parte 1: el análisis ABC
Paso 1: total y participación
Suma las ventas anuales de cada producto (=SUMA(B2:M2)) y calcula su participación en el total:
=N2/SUMA($N$2:$N$301)
Paso 2: ordena y acumula
Ordena los productos de mayor a menor por ventas y calcula la participación acumulada: en la primera fila es la propia participación; en las siguientes, la anterior más la actual (=P2+O3).
Paso 3: asigna los grupos
Con los límites clásicos de 80 % y 95 %:
=SI(P2<=80%;"A";SI(P2<=95%;"B";"C"))
Ejemplo con seis productos:
| Producto | Ventas anuales | Participación | Acumulado | Grupo |
|---|---|---|---|---|
| P1 | 40 000 € | 40 % | 40 % | A |
| P2 | 25 000 € | 25 % | 65 % | A |
| P3 | 15 000 € | 15 % | 80 % | A |
| P4 | 10 000 € | 10 % | 90 % | B |
| P5 | 6000 € | 6 % | 96 % | C |
| P6 | 4000 € | 4 % | 100 % | C |
La mitad del surtido genera el 80 % de los ingresos. Con cientos de productos la proporción suele ser aún más marcada.
Parte 2: el análisis XYZ
El XYZ mide la estabilidad de la demanda con el coeficiente de variación: la desviación estándar de las ventas mensuales dividida entre su promedio.
=DESVEST.P(B2:M2)/PROMEDIO(B2:M2)
(En Excel 2007 la función se llama DESVESTP.)
Con los límites habituales: hasta 10 % → X (demanda estable), hasta 25 % → Y (fluctuante), más de 25 % → Z (irregular).
Ejemplos con seis meses de ventas:
- 100, 110, 90, 105, 95, 100 → coeficiente 6,5 % → X
- 50, 70, 40, 65, 45, 60 → coeficiente 19,6 % → Y
- 20, 80, 10, 150, 40, 0 → coeficiente 103 % → Z
Parte 3: la matriz de nueve grupos
Concatena ambos resultados (=Q2&R2) y obtendrás grupos como AX, BY o CZ. Cada uno sugiere una política distinta:
- AX, AY: el núcleo del negocio. Stock siempre disponible, reposición regular, nunca dejar que se agoten.
- AZ: generan mucho, pero de forma irregular. Requieren previsión cuidadosa y quizá stock de seguridad mayor.
- BX, BY: importantes y razonablemente predecibles; reposición automática sin demasiado stock.
- CX: poco relevantes pero estables; pedidos poco frecuentes y en lote.
- CZ: candidatos a revisar o retirar del surtido, o a vender solo bajo pedido.
Errores frecuentes
- Mezclar unidades y dinero. Si un producto está en unidades y otro en euros, el análisis no tiene sentido.
- Incluir productos nuevos. Un artículo lanzado hace dos meses saldrá como C y Z por definición. Analízalo aparte.
- Ignorar la estacionalidad. Un producto de verano tendrá un coeficiente de variación alto aunque su demanda sea muy predecible año tras año. Interprétalo con criterio.
- Hacer el análisis una vez y olvidarlo. Repítelo cada trimestre o semestre; los grupos cambian.
Plantilla de pago: análisis ABC-XYZ del surtido
La plantilla Análisis ABC-XYZ del surtido es de pago y te ahorra montar y revisar todas estas fórmulas. En «Parámetros» defines los límites de los grupos A, B y X, Y; por defecto, 80 % y 95 %, 10 % y 25 %. En «Datos» introduces hasta 300 productos con sus ventas de 12 meses, y a la derecha se calculan solos el total, la participación, la participación acumulada y los grupos ABC y XYZ.
La hoja «Ranking» ordena los productos por ingresos de mayor a menor con sus grupos, sin que tengas que ordenar nada a mano. La «Matriz» muestra cuántos productos e ingresos hay en cada uno de los nueve grupos, una breve recomendación de qué hacer con cada combinación y un gráfico.
Funciona en Excel 2007 o posterior y en LibreOffice, sin macros. El cálculo es orientativo.
Preguntas frecuentes
¿Es mejor hacer el ABC por ingresos o por margen?
Por ingresos es lo más común y fácil de obtener. Por margen es más exigente, pero refleja mejor qué productos te dejan dinero de verdad. Si tienes los datos, haz los dos y compara.
¿Qué hago si un producto C es complementario de uno A?
No lo retires sin pensar. Hay productos que venden poco pero acompañan a los grandes (pilas para un juguete, una salsa para un plato). El análisis da una primera clasificación; la decisión final es tuya.
¿Cada cuánto conviene repetir el análisis?
Cada tres o seis meses, con los últimos 12 meses móviles. Así detectas productos que suben de grupo o que empiezan a caer.
¿Sirve para una cafetería o un restaurante?
Sí, aplicado a los platos o productos de la carta. Te dirá qué platos sostienen las ventas y cuáles tienen una demanda tan irregular que generan merma.
