Cómo hacer Curva ABC en Excel

18
36827
Aprenda a hacer curva ABC en Excel
Hojas de trabajo en Excel

La hoja de cálculo en Excel con la llamada curva ABC permite al usuario realizar la control de inventario tu compañía. A través de los datos de esta hoja de cálculo, el gerente puede hacer una comparación entre los elementos y determinar cuáles son relevantes y cuáles están disponibles para aumentar los ingresos de la empresa.

¿No conoces la curva ABC? ¿No realizas un comparativo para controlar tu inventario? Sepa que utilizar esta herramienta es muy importante para reducir los costos de su negocio.

El origen de la curva ABC es un método sedimentado en teorías del economista italiano Vilfredo Pareto. Se trata de dividir los elementos de su stock en 3 grupos de acuerdo a su importancia: A, B o C. En el grupo A están los elementos más importantes. En el grupo B están los elementos de nivel intermedio. En el grupo C, los elementos de baja relevancia.

Gestión de inventario con la curva ABC en Excel

Elaboramos paso a paso cómo hacer una hoja de cálculo de curva ABC en Excel. Seguir:

Creando las 4 primeras columnas

Aprenda a hacer curva ABC en ExcelPaso 1: Inicialmente vamos a crear cuatro columnas e insertar los valores. Son ellas:

  • Primera Columna: A1-A11: Es el código de cada producto en su stock.
  • Segunda columna: B1-B11: Cantidad de producto en stock.
  • Tercera columna: C1-C11: Valor unitario de cada producto.
  • Cuarta Columna: D1-D11: Valor total de los productos.

Paso 2: Para completar los datos de la cuarta columna, multiplique el valor de la columna B con el valor de la columna C para su producto. Por ejemplo: para obtener el valor de la columna D2, utilice la fórmula = B2 * C2. Por lo tanto, la cantidad de productos multiplicada por el valor unitario resultará en el importe gastado en su empresa para ese elemento de stock.

Paso 3: Hecho esto, coloque los valores de la columna D2 en orden descendente.

Paso 4: En secuencia, en la celda D12 utilice la fórmula = SOMA (D2: D11). El valor representará la cantidad del valor gastado en el inventario actual de su empresa.

Inserción de la columna%

Aprenda a hacer curva ABC en ExcelPaso 5: Cree la columna% en la celda E1. Los valores de las celdas E2 a E11 representan el porcentaje que cada valor de la Columna D (celdas D1 a D11) en relación con el valor de la celda D12.

Utilice sus fórmulas en cada celda abajo desglosada:

  • Célula E2: = D2 / D12
  • Célula E3: = D3 / D12
  • Célula E4: = D4 / D12
  • Célula E5: = D5 / D12
  • Célula E6: = D6 / D12
  • Célula E7: = D7 / D12
  • Célula E8: = D8 / D12
  • Célula E9: = D9 / D12
  • Célula E10: = D10 / D12
  • Célula E11: = D11 / D12

Paso 6: Hecho esto, seleccione las celdas E2 hasta E11 y haga clic en Inicio / Número /%.

Paso 7: En secuencia, en la celda E12 utilice la fórmula = SUMA (E2: E11). Si el resultado es 100% la hoja de cálculo es correcta hasta el momento. De lo contrario, vuelva a comprobar el paso a paso para notar el error y corregir el mismo.

Inserción de la columna% Acumulada

Aprenda a hacer curva ABC en ExcelPaso 8: Cree la columna% Acumulada en la celda F1. Los valores de las celdas F2 hasta F11 representan la suma de los porcentajes de la columna%.

Utilice sus fórmulas en cada celda abajo desglosada:

  • Célula F2: = E2
  • Célula F3: = F2 + E3
  • Célula F4: = F3 + E4
  • Célula F5: = F4 + E5
  • Célula F6: = F5 + E6
  • Célula F7: = F6 + E7
  • Célula F8: = F7 + E8
  • Célula F9: = F8 + E9
  • Célula F10: = F9 + E10
  • Célula F11: = F10 + E11

Paso 9: Una vez hecho esto, seleccione las celdas F2 a F11 y haga clic en Inicio / Número /%.

Insertar el gráfico con la curva ABC

Aprenda a hacer curva ABC en ExcelPaso 10: Seleccione los valores de las celdas A2 hasta A11.

Paso 11: Manteniendo presionada la tecla CTRL, también seleccione los valores de las celdas F2 hasta F11.

Paso 12: Seleccione el gráfico: Insertar / Gráfico / Líneas / Línea 2d / Líneas.

Aprenda a hacer curva ABC en ExcelPaso 13: Seleccione el título de serie 1 y, a continuación, haga clic en DELETE.

Aprenda a hacer curva ABC en ExcelPaso 14: El eje de porcentaje está entre 0 y 120%. Para dejar entre los valores de 0 y 100% seleccione el eje y haga clic en Formatear eje.

Paso 15: En las opciones de eje, seleccione FIJO para mínimo y máximo. Déjelos respectivamente en 0,0 y 1,0. Haga clic en CERRAR.

Aprenda a hacer curva ABC en Excel

A través de estos pasos la hoja de cálculo estará lista. Usted puede cambiar los colores y dejarla de la manera que más le guste.

Analizando los datos de la hoja de cálculo con curva ABC

Aprenda a hacer curva ABC en Excel

Teniendo en cuenta los 3 grupos A, B y C, elegimos la referencia 70-20-10.

De esta forma el grupo A representa 70% de los costos totales, el grupo B 20% y el grupo C 10%.

Aprenda a hacer curva ABC en Excel

Teniendo en cuenta los datos de esa hoja de trabajo, los grupos están representados:

  • GRUPO A: 3 productos (códigos 9, 4 y 10);
  • GRUPO B: 2 productos (códigos 5 y 8);
  • GRUPO C: 5 productos (códigos 1, 3, 6, 7 y 2).

Por lo tanto:

  • 30% de los productos representan 70% de los costes totales;
  • 20% de los productos representan 20% de los costes totales;
  • 50% de los productos representan 10% de los costos totales.

Estos datos son de fundamental importancia para que usted mantenga un inventario controlado y realice una gestión adecuada para inversiones perfectas.

¿Te gustó la hoja de cálculo? ¿El análisis de una hoja de cálculo con curva ABC en Excel ya te ha ayudado a controlar tu stock y realizar las inversiones correctas? Deje sus comentarios sobre la experiencia con esta hoja de cálculo.

Véase también la Planilla de control de inventario ¡de la luz!

Hojas de trabajo en Excel

18 COMENTARIOS

  1. Está bien que sean valores aproximados no. Al definir los cortes, tendrá que tomar una decisión, si desea tomar el último valor antes de los valores de corte (70 - 20 - 10) o el siguiente valor.

  2. ¿Habla César, todo bueno? Vamos por partes:

    1 - Para hacer la tercera columna basta con tomar los valores de los porcentajes acumulados (columna F), ver que en el tercer producto (cantidad de tipo A) tenemos aproximadamente el 70%. Para encontrar este valor puedes hacer una BUSCARV (yo crearía una columna auxiliar inicial con la numeración de la cantidad de productos (1, 2, 3, 4, etc.) y usaría la columna PRODUCTOS de la tabla auxiliar para encontrar el porcentaje correspondiente.

    2 - El% del total de productos es aún más simple, simplemente haga un CONT de la cantidad total de productos y haga una división simple de la cantidad de productos en un determinado grupo (A, B o C) por este total.

    3 - Los colores de las líneas son un poco más complicados. Realmente no recuerdo cómo se hizo, pero creo que insertó tres series de datos en el gráfico, cada serie buscando los valores de los porcentajes acumulados para cada grupo y luego cambió el color de cada una de las líneas, particularmente, no creo que hacer que el gráfico sea más explicativo ...

  3. faltó colocar la región debajo de la curva rellenada para quedar 10 = v

  4. Estoy haciendo una curva ABC utilizando una tabla con autofiltro, me gustaría saber si conoce alguna fórmula que calcule el porcentaje acumulado cuando se aplica el filtro.

  5. ¿Podría explicar cómo hace esa segunda tabla y cómo determina esas líneas coloreadas del ABC?
    ¡Gracias

  6. Basta con hacer un CONT.SE acoplado al grupo de la curva ABC que está enganchado

  7. buenas tardes,
    ¿cómo obtengo los valores de la última columna de la segunda tabla,% productos totales?

  8. Hola Bruno, para hacer ese cálculo es necesario colocar los valores en orden decreciente en una columna y hacer el acumulado de cada uno sucesivamente en otra.

    Así que usted va a tener cuánto representa en% y cuánto el acumulado representa. A partir de ahí es sólo pensar en los porcentajes y establecer los criterios de cuáles son los grupos A, B y C

  9. ¿Cómo se hace el cálculo de la última tabla para hacer la separación del ABC ???????

  10. ¿Hola Mineirinho, todo bueno?

    Si desea mejorar sus habilidades en Excel, ¡tenemos dos cursos en línea muy buenos que pueden ayudarlo mucho! Uno es principiante / intermedio y el otro avanzado. Puede obtener más información sobre ellos (e incluso ver algunas clases gratuitas) en este enlace: http://cursos.luz.vc/

DEJA UNA RESPUESTA

Por favor, escriba su comentario
Por favor, introduzca su nombre