Análisis ABC de inventarios con Excel: Clasificación por valor
Este instructivo técnico está diseñado para profesionales de la logística, ingeniería industrial y analistas de datos que buscan optimizar la gestión de inventarios utilizando la poderosa herramienta de Microsoft Excel. El Análisis ABC permite priorizar el control de los ítems que generan mayor valor o impacto en la operación.
Definición del problema
En entornos de manufactura o distribución, la gestión de inventarios puede volverse caótica si se trata a todos los productos por igual. Un almacén con cientos de referencias puede tener productos de bajo consumo y valor insignificante junto a componentes críticos y de alto costo. El reto operativo es la asignación ineficiente de recursos: ¿Deberíamos dedicar el mismo tiempo de auditoría y control a un tornillo de bajo costo que a un microchip especializado y costoso?
El Análisis ABC, basado en el Principio de Pareto (la regla 80/20), resuelve este problema. Su objetivo es clasificar los artículos de inventario en tres categorías (A, B y C) según su contribución al valor total del inventario (generalmente calculado como Cantidad x Costo Unitario). Esto permite enfocar los esfuerzos de control riguroso (inventarios cíclicos, seguridad, etc.) en el 20% de los ítems que representan el 80% del valor total.
Explicación técnica
La implementación de este análisis en Excel se basa en una secuencia lógica de manipulación de datos y cálculos estadísticos. No se requieren funciones complejas como Solver, sino más bien el uso eficiente de funciones de agregación y ordenamiento.
Los conceptos técnicos clave son:
- Valor Anualizado (o Valor de Consumo):
- Ordenamiento Descendente: Los datos deben ordenarse de mayor a menor valor para poder aplicar el criterio de Pareto.
- Cálculo de Porcentaje Acumulado: Se calcula el porcentaje que cada ítem representa sobre el valor total del inventario. Luego, se acumulan estos porcentajes para determinar en qué punto se alcanza el umbral del 80% (para la Clase A).
- Clasificación: Se asignan las clases basándose en los rangos estándar: Clase A (Top 10-20% de ítems, 70-80% del valor), Clase B (Siguiente 30% de ítems, 15-25% del valor), y Clase C (Resto, 50-60% de ítems, 5% del valor).
Guía paso a paso
Paso 1: Preparación y Carga de Datos Iniciales
Primero, debemos estructurar nuestros datos en una hoja de cálculo de Excel. Necesitamos al menos el código del producto, la cantidad consumida y el costo unitario.
Acción: Ingresa los datos en las columnas A, B y C. Asegúrate de que los costos y cantidades sean numéricos.
Resultado: Una tabla de datos crudos lista para el procesamiento.
| A | B | C | |
|---|---|---|---|
| 1 | SKU001 | 1500 | 12.50 |
| 2 | SKU002 | 450 | 85.00 |
| 3 | SKU003 | 3000 | 2.10 |
| 4 | SKU004 | 120 | 350.00 |
Paso 2: Cálculo del Valor de Consumo (Columna D)
Acción: En la celda D2, escribe la fórmula `=B2*C2`. Arrastra esta fórmula hacia abajo hasta el final de tus datos.
Resultado: Una nueva columna (D) que muestra el valor monetario total asociado a cada SKU.
| A | B | C | D (Valor) | |
|---|---|---|---|---|
| 1 | SKU001 | 1500 | 12.50 | =B2*C2 (18750) |
| 2 | SKU002 | 450 | 85.00 | =B3*C3 (38250) |
| 3 | SKU003 | 3000 | 2.10 | =B4*C4 (6300) |
| 4 | SKU004 | 120 | 350.00 | =B5*C5 (42000) |
Paso 3: Ordenamiento de Datos por Valor (Columna D)
Para aplicar Pareto, necesitamos que los ítems más valiosos aparezcan primero. El ordenamiento debe ser descendente.
Acción: Selecciona todo el rango de datos (A1 hasta D5, incluyendo encabezados). Ve a la pestaña ‘Datos’ y haz clic en ‘Ordenar’. Configura la ordenación para que la Columna D (Valor) sea el criterio principal y el orden sea ‘De Mayor a Menor’.
Resultado: La tabla completa se reordena, colocando el SKU con mayor valor en la primera fila.
Paso 4: Cálculo del Valor Total y Porcentaje Individual
Necesitamos saber el valor total del inventario para calcular la proporción de cada ítem.
Acción 1 (Total): En una celda vacía (ej. D6), calcula la suma total de la Columna D: `=SUMA(D2:D5)`. Llama a este valor «Valor Total».
Acción 2 (Porcentaje): En la celda E2, calcula el porcentaje de cada ítem sobre el total: `=D2/$D$6`. (Usar el signo $ para fijar la celda del total).
Resultado: La columna E muestra la contribución porcentual de cada producto al valor total.
| A | B | C | D (Valor) | E (% Individual) | |
|---|---|---|---|---|---|
| 1 | SKU004 | 120 | 350.00 | 42000 | =D2/D6 (0.48) |
| 2 | SKU002 | 450 | 85.00 | 38250 | =D3/D6 (0.44) |
| 3 | SKU001 | 1500 | 12.50 | 18750 | =D4/D6 (0.22) |
| 4 | SKU003 | 3000 | 2.10 | 6300 | =D5/D6 (0.07) |
| 5 | TOTAL | 105300 | 1.00 |
Paso 5: Cálculo del Porcentaje Acumulado y Clasificación ABC
Finalmente, acumulamos los porcentajes y definimos las clases.
Acción 1 (Acumulado): En la celda F2, calcula el porcentaje acumulado: `=E2`. En la celda F3, usa la fórmula `=F2+E3`. Arrastra esta fórmula hacia abajo.
Acción 2 (Clasificación): En la columna G, utiliza una función condicional (SI o SI.CONJUNTO) para asignar la clase. Por ejemplo, en G2, escribe: `=SI(F2<=0.2, «A», SI(F2<=0.5, «B», «C»))`. Ajusta los umbrales (0.2 y 0.5) según la política de tu empresa.
Resultado: La columna G contiene la clasificación final (A, B o C) para cada SKU, permitiendo la toma de decisiones de gestión.
Ejercicio propuesto
Una vez que domines el proceso con los datos de ejemplo, te proponemos una extensión: Análisis ABC por Volumen de Movimiento (Cantidad).
En lugar de usar el Valor de Consumo (Cantidad x Costo), repite los Pasos 2, 3, 4 y 5, pero utilizando únicamente la columna de Cantidad (Columna B) como métrica principal para el cálculo del porcentaje acumulado. Esto te permitirá identificar los ítems más vendidos (alto volumen) independientemente de su costo unitario. Compara los resultados: ¿Los ítems de mayor valor (Clase A) son necesariamente los de mayor volumen (Clase A por cantidad)?
Errores habituales
La implementación de este análisis es sencilla, pero los errores en la manipulación de referencias o en la lógica de Pareto son comunes. Presta atención a estos puntos:
- Error de Referencia Absoluta (El más común): Al arrastrar fórmulas (ej. en el Paso 4, al calcular el porcentaje), si no fijas la celda del «Valor Total» con el símbolo de dólar ($), la referencia cambiará a medida que arrastres, resultando en cálculos incorrectos. Solución: Siempre usa referencias absolutas ($D$6) para valores fijos.
- Error de Ordenamiento Incompleto: Si solo ordenas la Columna D (Valor) sin seleccionar y ordenar TODAS las columnas asociadas (A, B, C, D, E, F, G), los datos se desalinearán. Por ejemplo, el SKU004 podría terminar en la fila 3, pero su valor original seguiría en la fila 1. Solución: Siempre selecciona el rango completo de datos antes de ejecutar la función «Ordenar».
- Error de Umbrales de Pareto: Asumir que el 80% siempre corresponde a la Clase A. El principio de Pareto es una guía, no una ley inmutable. Si tu empresa maneja productos extremadamente homogéneos, podrías necesitar un umbral del 75% para la Clase A. Solución: Define los umbrales (ej. 80%/15%/5%) en la documentación de tu proceso y ajústalos en la función condicional (Paso 5) según la política de control de inventario de tu organización.
Deja una respuesta