Cómo hacer un análisis ABC en Excel: 5 pasos sencillos

El análisis ABC clasifica los artículos de inventario en tres clases según el valor de su consumo anual. Los artículos de la clase A son los pocos que representan la mayor parte del dinero. Los artículos de la clase C son los muchos que representan una parte mínima, y la clase B se sitúa en el medio. En Excel, puede hacerlo con una sola tabla: valor anual, participación sobre el total, un total acumulado y una fórmula que asigne cada clase.

Esta guía explica qué significan las clases, los cinco pasos en Excel, un ejemplo práctico y cómo graficar el resultado. También cubre cómo elegir los límites de corte y qué hacer con cada clase una vez finalizado el análisis.

Qué es el análisis ABC

El análisis ABC es una forma de decidir qué artículos merecen la mayor atención. Se basa en un patrón sencillo: una pequeña proporción de artículos representa una gran parte del gasto.

Un capítulo de 2012 sobre el análisis y control de gastos farmacéuticos, de Management Sciences for Health (MSH), lo describe claramente. Señala que "un número relativamente pequeño de artículos representa la mayor parte del valor del consumo anual". Y añade: "El análisis de este fenómeno se conoce como análisis de Pareto o, más comúnmente, análisis ABC".

El mismo capítulo explica que los artículos "pueden clasificarse en tres categorías (A, B y C) según el valor de su consumo anual". El método es el mismo tanto si almacena medicamentos, piezas de repuesto o productos de venta al por menor.

Hay un punto que es fácil pasar por alto. Las clases no son etiquetas permanentes. MSH señala que "si los patrones de consumo cambian, el artículo puede caer en una categoría diferente la próxima vez que se realice el análisis ABC". Por lo tanto, el análisis ABC funciona mejor como un control rutinario y no como un proyecto de una sola vez.

Qué significan las clases A, B y C

El capítulo de MSH ofrece rangos típicos para cada clase:

Clase	Proporción de artículos	Proporción del valor anual	Lo que suele significar
A	10 al 20 por ciento	75 al 80 por ciento	Pocos artículos, la mayor parte del dinero
B	10 al 20 por ciento	15 al 20 por ciento	Un grupo intermedio
C	60 al 80 por ciento	5 al 10 por ciento	Muchos artículos, poco dinero

Estos son rangos típicos, no reglas. MSH afirma que "estos límites son algo flexibles". Su ejemplo establece la clase A en los artículos que suman el 70 por ciento de los fondos en su lugar.

El valor que define las clases es el valor de consumo anual: las unidades utilizadas en un año multiplicadas por el costo unitario. Un artículo barato utilizado en grandes volúmenes puede acabar en la clase A. Un artículo caro utilizado una vez al año puede acabar en la clase C.

Un artículo de 2014 en el American Journal of Business Education cuestiona el uso exclusivo del valor. Sostiene que los libros de texto "se centran en el volumen de dinero como único criterio" y recomienda añadir otros criterios. Para una primera aproximación, el valor es el método que utiliza el capítulo de MSH.

Qué necesita antes de empezar

El análisis ABC en Excel solo necesita unas pocas columnas por artículo:

• Nombre del artículo o SKU. Una fila por artículo.
• Unidades anuales utilizadas o compradas. Utilice el mismo período de 12 meses para cada artículo.
• Costo unitario. El costo de una unidad, en la misma unidad en la que realiza el conteo.

MSH hace hincapié en la coincidencia del período: "Asegúrese de utilizar el mismo período de revisión para todos los artículos para evitar comparaciones no válidas". También aconseja utilizar la misma unidad básica para el costo y la cantidad, como una tableta o una sola caja, en lugar de mezclar tamaños de envase.

Si sus datos provienen de un sistema de inventario o compras, expórtelos como un archivo CSV o Excel. Elimine los artículos sin actividad en el período, o consérvelos sabiendo que probablemente caerán en la clase C.

Cómo hacer un análisis ABC en Excel

Los cinco pasos siguientes siguen el método del capítulo de MSH, adaptado a fórmulas de Excel. El ejemplo coloca un título en la fila 1, encabezados en la fila 2 y 10 artículos en las filas 3 a 12. Las columnas A, B y C contienen el nombre del artículo, las unidades anuales y el costo unitario.

Paso 1: Listar los artículos, las unidades y el costo unitario

Introduzca o pegue una fila por artículo con su nombre, unidades anuales y costo unitario. Añada encabezados en la fila 2 para que la tabla sea fácil de ordenar más adelante.

Revise los datos antes de continuar. Busque costos en blanco, cantidades negativas y SKU duplicados, ya que cada uno de ellos distorsionará los totales. Un filtro rápido en cada columna suele ser suficiente para encontrarlos.

Si se realizaron varias compras del mismo artículo a precios diferentes, utilice un costo único y coherente. MSH señala que "un promedio ponderado o un promedio FIFO" son las alternativas más precisas cuando el costo unitario real es difícil de rastrear.

Paso 2: Calcular el valor anual y su proporción sobre el total

En la columna D, multiplique las unidades por el costo para obtener el valor anual de cada artículo. En D3, introduzca =B3*C3 y arrastre la fórmula hacia abajo.

En la columna E, divida cada valor por el total de todos los valores para obtener su proporción. En E3, introduzca =D3/SUM($D$3:$D$12) y arrastre hacia abajo. Los signos de dólar mantienen fijo el rango total a medida que se copia la fórmula. Formatee la columna E como porcentaje con dos decimales.

MSH recomienda esa precisión por una razón. En sus palabras, "varios artículos pueden tener un valor muy cercano y muchos pueden representar menos del 1 por ciento del valor total".

Paso 3: Ordenar los artículos por valor, de mayor a menor

Seleccione toda la tabla, incluidos los encabezados, y ordénela por la columna D de mayor a menor. En Excel, esto se hace en Datos, luego Ordenar, seleccionando la columna D y estableciendo el orden de Mayor a Menor.

Si prefiere una fórmula, la función SORT devuelve una copia ordenada. La sintaxis de Microsoft es =SORT(array,[sort_index],[sort_order],[by_col]), donde un orden de clasificación de -1 significa descendente. Para esta tabla, =SORT(A3:E12,4,-1) ordena por la cuarta columna, con el valor más alto primero.

Después de este paso, el artículo con el valor anual más alto se sitúa en la parte superior. Ese orden es lo que hace que el total acumulado del siguiente paso tenga sentido.

Paso 4: Añadir el porcentaje acumulado

En la columna F, añada un total acumulado de las proporciones. En F3, introduzca =SUM($E$3:E3) y arrastre hacia abajo. La primera parte del rango permanece fija y la segunda crece una fila cada vez.

La última fila debería mostrar el 100 por ciento. Si no es así, compruebe si hay celdas vacías o valores de texto en las columnas D y E.

Esta columna es el núcleo del análisis ABC. Muestra qué parte del valor total representan conjuntamente los artículos situados por encima de cada fila.

Paso 5: Asignar las clases A, B y C

En la columna G, utilice una fórmula para etiquetar cada artículo. Con límites de corte de 80 y 95 por ciento, introduzca esto en G3 y arrastre hacia abajo:

=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")

La función IFS comprueba cada condición en orden y devuelve la primera coincidencia. El propio ejemplo de Microsoft utiliza el mismo patrón, con TRUE como el valor final para todo lo demás. Los artículos con un acumulado de hasta el 80 por ciento se convierten en A, los artículos de hasta el 95 por ciento se convierten en B, y el resto se convierte en C.

Por último, cuente cada clase con =COUNTIF(G3:G12,"A") y haga lo mismo para B y C. Compare los conteos con los rangos típicos anteriores. Ajuste los límites de corte si la clase A es demasiado grande o pequeña para que su equipo pueda gestionarla.

Un ejemplo práctico

Aquí tiene una tabla ilustrativa de 10 artículos, ya ordenados por valor anual. Los números son ejemplos, no datos de una empresa real.

Artículo	Unidades anuales	Costo unitario	Valor anual	Proporción	Acumulado	Clase
SKU-01	1,200	$45.00	$54,000	36.00%	36.00%	A
SKU-02	3,000	$12.00	$36,000	24.00%	60.00%	A
SKU-03	500	$40.00	$20,000	13.33%	73.33%	A
SKU-04	8,000	$1.50	$12,000	8.00%	81.33%	B
SKU-05	2,000	$4.00	$8,000	5.33%	86.67%	B
SKU-06	600	$10.00	$6,000	4.00%	90.67%	B
SKU-07	1,000	$5.00	$5,000	3.33%	94.00%	B
SKU-08	1,500	$3.00	$4,500	3.00%	97.00%	C
SKU-09	700	$5.00	$3,500	2.33%	99.33%	C
SKU-10	400	$2.50	$1,000	0.67%	100.00%	C

El valor anual total es de $150,000. Tres artículos, el 30 por ciento de la lista, representan el 73.33 por ciento del valor y se ubican en la clase A. Cuatro artículos caen en la clase B, y los tres últimos, que representan el 6 por ciento del valor, caen en la clase C.

Destacan dos detalles. El SKU-04 tiene, con diferencia, la mayor cantidad de unidades, pero su bajo costo lo sitúa en la clase B. Y con solo 10 artículos, las proporciones de las clases no coincidirán con los rangos típicos, lo cual es normal para una lista corta.

Cómo graficar el resultado

Un gráfico facilita la presentación del patrón en una reunión. MSH sugiere trazar el porcentaje acumulado frente al número de artículo, lo que genera la conocida curva ABC.

Excel tiene un gráfico integrado para esto. Microsoft describe un gráfico de Pareto como aquel que "contiene tanto columnas ordenadas en orden descendente como una línea que representa el porcentaje total acumulado". Para crear uno, seleccione los nombres de los artículos y los valores anuales, luego elija Insertar, Insertar gráfico estadístico y Pareto.

Añada dos líneas horizontales o etiquetas en sus límites de corte, como 80 y 95 por ciento, para que los espectadores puedan ver dónde empieza cada clase. Nuestra guía para crear un gráfico de Pareto con IA cubre el gráfico en sí con más detalle.

Elegir los límites de corte

No existe un único límite de corte correcto. MSH explica que la elección "depende de cómo se dispersen el volumen y el valor entre los artículos de la lista". También depende de "cómo se vayan a utilizar los resultados del análisis ABC".

La capacidad de gestión es el límite práctico. MSH lo expresa directamente: "la asignación de artículos a la clase A debe basarse en la capacidad de gestión". Si su equipo puede revisar de cerca 50 artículos al mes, una clase A de 300 artículos anula el propósito.

Algunos enfoques comunes:

• Límites por valor. A hasta el 80 por ciento del valor, B hasta el 95 por ciento, C para el resto. Este es el método utilizado anteriormente.
• Límites por conteo de artículos. El 20 por ciento superior de los artículos por valor se convierte en A, el siguiente 30 por ciento en B, y el resto en C.
• Listas fijas. Algunos equipos establecen la clase A como los 25 o 50 artículos principales, independientemente de su proporción de valor.

Cualquiera que elija, anótelo y utilícelo siempre. Comparar las clases de este trimestre con las del trimestre anterior solo funciona si los límites de corte se mantienen iguales.

Qué hacer con cada clase

El objetivo del análisis ABC es dedicar esfuerzos allí donde está el dinero. El capítulo de MSH enumera varias formas de utilizar los resultados:

• Pedir artículos de clase A con más frecuencia. MSH afirma que pedir artículos de clase A "con más frecuencia y en cantidades más pequeñas debería conducir a una reducción de los costos de mantenimiento de inventario".
• Negociar primero los precios de la clase A. "Las reducciones de precios para los artículos clasificados como productos A en el análisis pueden generar ahorros significativos", según el capítulo.
• Contar el stock de clase A con más frecuencia. MSH señala que "los conteos cíclicos de stock deben guiarse por el análisis ABC, con conteos más frecuentes para los artículos de clase A".
• Vigilar el estado de los pedidos de clase A. Una escasez inesperada de un artículo de clase A puede dar lugar a costosas compras de emergencia.

Los artículos de clase C pueden tener reglas más sencillas, como pedidos más grandes y menos frecuentes, y menos conteos. La clase B se sitúa en el medio. Si le preocupan los artículos de lento movimiento, nuestra guía sobre cómo detectar inventario de lento movimiento complementa muy bien este análisis.

Hacerlo más rápido con IA

Los pasos en Excel toman unos minutos una vez que los datos están limpios. Limpiar la exportación y repetir el trabajo cada trimestre lleva más tiempo.

Un espacio de trabajo de IA puede realizar la aritmética y la ordenación en una sola solicitud. Suba la exportación de inventario o compras a Powerdrill Bloom y solicite en lenguaje natural un análisis ABC con sus límites de corte. Pida el valor anual, la proporción, el porcentaje acumulado y la clase para cada artículo, además de un gráfico de Pareto.

Luego, revíselo como cualquier hoja de cálculo. Confirme el valor anual total con su propia suma y verifique aleatoriamente dos artículos en cada clase. Nuestra página del asistente de IA para Excel cubre ese tipo de trabajo con hojas de cálculo con más detalle. Para una visión más amplia de las herramientas de previsión, consulte esta recopilación de herramientas de IA para la previsión de inventario y demanda.

Errores comunes que se deben evitar

• Mezclar períodos de tiempo. Doce meses para un artículo y seis para otro hace que las proporciones carezcan de sentido.
• Utilizar unidades en lugar de valor. Las clases dependen de las unidades multiplicadas por el costo, no solo de las unidades.
• Olvidar ordenar antes del total acumulado. Un porcentaje acumulado en una lista no ordenada coloca los artículos en la clase incorrecta.
• Tratar las clases como permanentes. Vuelva a ejecutar el análisis cada trimestre o año, ya que los artículos se mueven entre clases.
• Límites de corte que ignoran la capacidad. Una lista de clase A demasiado larga para gestionarla de cerca no recibirá mayor atención que la clase B.
• Ignorar artículos baratos críticos. Un artículo de bajo valor aún puede detener el trabajo si se agota. El capítulo de MSH combina el análisis ABC con una clasificación independiente de artículos vitales, esenciales y no esenciales.

Cuando su lista de artículos provenga de una exportación desordenada, puede probar Powerdrill Bloom para crear la primera tabla y gráfico ABC.

Preguntas frecuentes

¿Qué es el análisis ABC en la gestión de inventarios?

El análisis ABC clasifica los artículos en tres clases según el valor de consumo anual. Los artículos de la clase A son los pocos que representan la mayor parte del valor. Los artículos de la clase C son los muchos que representan una parte mínima, y la clase B se sitúa en el medio. Ayuda a los equipos a centrar los esfuerzos de control allí donde está el dinero.

¿Cómo se calcula el análisis ABC en Excel?

Multiplique las unidades anuales por el costo unitario de cada artículo, luego divídalo por el total para obtener la proporción de cada uno. Ordene por valor de mayor a menor, añada un total acumulado de las proporciones y asigne las clases con una fórmula como IFS. Los límites de corte del 80 y 95 por ciento se sitúan dentro de los rangos típicos del capítulo de MSH.

¿Cuáles son los porcentajes para el análisis ABC?

Una pauta común es que la clase A contiene del 10 al 20 por ciento de los artículos y del 75 al 80 por ciento del valor. La clase B contiene otro 10 al 20 por ciento de los artículos y del 15 al 20 por ciento del valor. La clase C contiene del 60 al 80 por ciento de los artículos y del 5 al 10 por ciento del valor.

¿Cuál es la fórmula para la clasificación ABC en Excel?

Con el porcentaje acumulado en la columna F y los datos comenzando en la fila 3, utilice =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Cambie 0.8 y 0.95 para que coincidan con sus propios límites de corte. Las fórmulas IF anidadas pueden hacer el mismo trabajo.

¿Por qué es importante el análisis ABC?

Muestra a dónde va la mayor parte del dinero del inventario, para que los equipos puedan gestionar esos artículos más de cerca. Los usos típicos incluyen pedir artículos de clase A con más frecuencia, negociar sus precios primero y contarlos más a menudo. También señala los gastos que no coinciden con los planes.

Fuentes: Management Sciences for Health, MDS-3 Capítulo 40: Análisis y control de gastos farmacéuticos · Ravinder y Misra, Análisis ABC para la gestión de inventarios (2014) · Soporte de Microsoft, función SORT · Soporte de Microsoft, función IFS · Soporte de Microsoft, Crear un gráfico de Pareto.