Análisis ABC en Excel con ejemplo y plantilla

Para el análisis del surtido de bienes, «clientes potenciales» de clientes, proveedores, deudores se utilizan métodos ABC y XYZ (rara vez).

Análisis ABC basado: es el famoso principio de Pareto, que establece que el 20% de los esfuerzos dan el 80% del resultado. Transformada y detallada, esta ley ha sido aplicada en el desarrollo de los métodos discutidos.

Análisis ABC en Excel

El método ABC le permite clasificar una lista de valores en tres grupos, que tienen un impacto diferente en el resultado final.

• resaltar los de mayor «peso» en el resultado total;
• analizar los grupos de posiciones en lugar de una lista extensa;
• para trabajar en un algoritmo con las posiciones de un grupo.

Los significados en la lista después de la aplicación del método ABC se dividen en tres grupos:

1. A – el más importante para el total de (20% da 80% de los resultados).
2. B – promedio en importancia (30% – 15%).
3. C – el menos importante (50% – 5%).

Estos valores no son obligatorios. Los métodos para determinar los límites de los grupos ABC diferirán en el análisis de varios indicadores. Pero si se detectan desviaciones significativas, vale la pena pensar qué está mal.

Condiciones para el uso del análisis ABC:

• los objetos analizados tienen características numéricas;
• la lista del análisis consta de posiciones homogéneas (no se pueden comparar lavadoras y bombillas, porque estos bienes están ocupados en rangos de precios tan diferentes);
• se seleccionó el significado objetivo máximo (calificar las opciones sobre el ingreso mensual más correctas que sobre los recibos diarios).

Para algunos valores, puede utilizar la metodología de análisis ABC:

• la gama comercial de mercancías (analizando al beneficio);
• la cartera de clientes (analizando el volumen de pedidos);
• la base de proveedores (analizando a los envíos);
• los deudores (analizando a la suma de la deuda).

El método de clasificación es muy simple. Pero manejar grandes volúmenes de datos sin programas especiales es problemático. El procesador tabular Excel simplifica enormemente el análisis ABC.

El esquema general:

1. Identificar para el propósito del análisis. Determine el objeto (que analiza) y el parámetro (sobre qué principio se clasificará por grupos).
2. Realice los parámetros de clasificación en orden descendente.
3. Resumir en datos numéricos (parámetros: ingresos, cantidad de deuda, volumen de pedidos, etc.).
4. Encuentra la proporción de cada parámetro en el total.
5. Calcule la parte del total acumulativo para cada valor de lista.
6. Encuentre el valor en la lista, en el que la participación del total acumulado se acerca al 80%. Este es el límite inferior del grupo A. El superior es el primero de la lista.
7. Encuentre el valor en la lista, en el que la participación del total acumulado se acerca al 95% (+ 15%). Este es el límite inferior del grupo B.
8. Para C – todo a continuación.
9. Calcule el número de valores para cada categoría y el número total de posiciones en la lista.
10. Encuentre las acciones de cada categoría en total.

Prenda

Análisis ABC de la gama comercial de productos en Excel

Formamos la mesa de estudio con 2 columnas y 15 filas. Insertamos los nombres de los bienes condicionales y las fechas de venta del año (en el valor monetario). Es necesario clasificar el rango de ingresos (qué productos proporcionan más ganancias).

1. 1. Ordena las fechas en la tabla. Excretar a todo el rango (excepto la parte superior) y pulsar «Ordenar» en la pestaña «Datos». En el cuadro de diálogo que se ha abierto, en «Ordenar por» seleccione «Ingresos». En la columna «Orden» – «De mayor a menor».

1. 1. Agregue la tabla a la línea final. Necesitamos encontrar la suma total de los valores en la columna «Ingresos». Vaya a la celda B17 y presione la combinación de teclas de acceso directo ALT + «=» para acceder rápidamente a las funciones con parámetros completos: =SUMA(B2:B16).

1. 1. Calcular la proporción de cada elemento en el importe total. Cree la tercera columna «Compartir» y asigne a las celdas un formato de porcentaje. Introduce la fórmula en la primera celda: =B2/$B$17 (el enlace a la «suma» lo debemos hacer al absoluto). «Estirar» hasta la última celda de la columna. Además, haga que el «Porcentaje» de las celdas tenga formato CTRL+MAYÚS+5.

1. 1. Calcular la participación por devengo. Agregue en la tabla la columna 4-th «Participación acumulada». Para el primer puesto, será igual a la cuota individual. Para este propósito, en la celda D2 ingrese: =C2. Para la segunda posición – es la participación individual + participación de base devengada para la posición anterior. Ingrese en la segunda celda la fórmula: =C3+D2. «Estirar» hasta el final de la columna. Para las últimas posiciones debe ser del 100%.

1. 1. Asignar por posiciones a uno u otro grupo. Menos del 80% – está en el grupo A. Menos del 95% – está en el grupo B. Otros – en el grupo S.

1. Para que le resulte cómodo utilizar los resultados del análisis, adjunte delante de cada elemento las letras correspondientes.

Así que hemos terminado el análisis ABC utilizando las instalaciones de Excel. Las acciones adicionales del usuario son el uso de las fechas de los hallazgos en la práctica.

Análisis XYZ: el ejemplo de cálculo en Excel

Este método se usa a menudo además del análisis ABC. El término combinado análisis ABC-XYZ incluso se encuentra en la literatura.

El acrónimo XYZ esconde el nivel de previsibilidad de la previsibilidad del objeto que se analiza. Este índice está hecho a medida por el coeficiente de variación que caracteriza la medida de las fechas de dispersión alrededor del valor medio.

El coeficiente de variación – es una medida relativa, que no dispone de las unidades de medida específicas. Es bastante informativo. Incluso per se. ¡PERO! La tendencia, la dinámica de la estacionalidad aumentan significativamente la previsibilidad de la tasa. Como resultado se reduce la previsibilidad de la tasa. Este error puede implicar decisiones equivocadas. Esta es una gran desventaja del método XYZ. Sin embargo, es…

Hay posibles objetos para el análisis: volumen de ventas, número de proveedores, ingresos, etc. Más a menudo, este método se usa para determinar los bienes para los que existe una fuerte demanda.

Algoritmo de análisis XYZ:

1. El cálculo del nivel del coeficiente de variación de la demanda para cada categoría de producto. El analista estima la desviación porcentual del volumen de ventas de la media.
2. Ordenar gama de productos por el coeficiente de variación.
3. Posición de clasificación en los tres grupos – X, Y o Z.

Los criterios para la clasificación y características de los grupos:

1. «Х» – 0 – 10% (el coeficiente de variación) – bienes con la demanda más fuerte.
2. «Y» – 10 – 25% – bienes con ventas volátiles.
3. «Z» – 25% – bienes que tienen demanda aleatoria.

Componga la tabla de entrenamiento para el análisis XYZ.

1. 1. Calcule el coeficiente de variación para cada grupo de productos básicos. La fórmula de cálculo de la variabilidad del volumen de ventas: =STDEVP(B8:H8)/AVERAGE(B8:H8).

1. Clasificar significados – definir a los productos del grupo «X», «Y» o «Z». Usamos la función integrada «SI»: =SI(I3

Descargar ejemplo de inventario ABC en Excel

En el grupo «X» se encuentran los productos que tienen la demanda más estable. El volumen de ventas mensual promedio rechaza solo un 7% (el producto 1) y un 9% (el producto 8). Si tiene las existencias de estos artículos en stock, la empresa debe poner los productos en el mostrador.

Se pueden reducir las existencias de mercancías del grupo «Z». O incluso vaya a estos nombres en un pedido anticipado.