Como Fazer Curva ABC no Excel

  18 Comentários   Excel  
Compartilhe com amigos:

A planilha no Excel com a chamada curva ABC permite ao usuário uma gestão de qualidade do estoque de sua empresa. Através dos dados dessa planilha, o gestor pode fazer uma comparação entre os itens e determinar quais são relevantes e os que são descartáveis para o aumento da receita da empresa.

Você não conhece a curva ABC? Você não realiza um comparativo para controle do seu estoque? Saiba que utilizar essa ferramenta é muito importante para reduzir os custos do seu negócio.

A origem da curva ABC é um método sedimentado em teorias do economista italiano Vilfredo Pareto. Trata-se de dividir os itens do seu estoque em 3 grupos de acordo com a sua importância: A, B ou C. No grupo A estão os itens mais importantes. Já no grupo B estão os itens de nível intermediário. No grupo C estão os itens com relevância baixa.

Gestão do estoque com a curva ABC no Excel

Elaboramos um passo a passo de como fazer uma planilha de curva ABC no Excel. Acompanhe:

Criando as 4 primeiras colunas

Aprenda a fazer curva ABC no Excel

Passo 1: Inicialmente vamos criar quatro colunas e inserir os valores. São elas:

  • Primeira Coluna: A1-A11: É o código de cada produto do seu estoque.
  • Segunda Coluna: B1-B11: Quantidade do produto em estoque.
  • Terceira Coluna: C1-C11: Valor unitário de cada produto.
  • Quarta Coluna: D1-D11:Valor total dos produtos.

Passo 2: Para completar os dados da quarta coluna, multiplique o valor da coluna B com o valor da coluna C para o respectivo produto. Por exemplo: para obter o valor da coluna D2, utilize a fórmula =B2*C2. Logo, a quantidade de produtos multiplicada pelo valor unitário resultará no montante gasto na sua empresa para aquele item de estoque.

Passo 3: Feito isso, coloque os valores da coluna D2 em ordem decrescente.

Passo 4: Em sequência, na célula D12 utilize a fórmula =SOMA(D2:D11). O valor representará a quantidade do valor gasto no estoque atual da sua empresa.

Inserindo a coluna %

Aprenda a fazer curva ABC no Excel

Passo 5: Crie a coluna % na célula E1. Os valores das células E2 até E11 representam a porcentagem que cada valor da Coluna D (células D1 até D11) em relação ao valor da célula D12.

Utilize as respectivas fórmulas em cada célula abaixo discriminada:

  • 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

Passo 6: Feito isso, selecione as células E2 até E11 e clique em Página Inicial/Número/%.

Passo 7: Em sequência, na célula E12 utilize a fórmula =SOMA(E2:E11). Se o resultado for 100% a planilha está corretíssima até o presente momento. Do contrário, confira novamente o passo-a-passo para notar o erro e corrigir o mesmo.

Inserindo a coluna % Acumulada

Aprenda a fazer curva ABC no Excel

Passo 8: Crie a coluna % Acumulada na célula F1. Os valores das células F2 até F11 representam a soma das porcentagens da coluna %.

Utilize as respectivas fórmulas em cada célula abaixo discriminada:

  • 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

Passo 9: Feito isso, selecione as células F2 até F11 e clique em Página Inicial/Número/%.

Inserindo o gráfico com a curva ABC

Aprenda a fazer curva ABC no Excel

Passo 10: Selecione os valores das células A2 até A11.

Passo 11: Mantendo a tecla CTRL pressionada selecione também os valores das células F2 até F11.

Passo 12: Selecione o gráfico: Inserir/Gráfico/Linhas/Linha 2d/Linhas.

Aprenda a fazer curva ABC no Excel

Passo 13: Selecione a legenda Série 1 e após clique em DELETE.

Aprenda a fazer curva ABC no Excel

Passo 14: O eixo da porcentagem está nos valores entre 0 e 120%. Para deixarmos entre os valores de 0 e 100% selecione o eixo e clique em Formatar eixo.

Passo 15: Em opções do eixo, selecione FIXO para mínimo e máximo. Respectivamente deixe-os nos valores 0,0 e 1,0. Clique em FECHAR.

Aprenda a fazer curva ABC no Excel

Através desses passos a planilha estará pronta. Você pode alterar cores e deixá-la do jeito que mais lhe agrade.

Analisando os dados da planilha com curva ABC

Aprenda a fazer curva ABC no Excel

Levando em consideração os 3 grupos A, B e C, escolhemos a referência 70-20-10.

Dessa forma o grupo A representa 70% dos custos totais, o grupo B 20% e o grupo C 10%.

Aprenda a fazer curva ABC no Excel

Levando em conta os dados dessa planilha, os grupos estão representados:

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

Portanto:

  • 30% dos produtos representam 70% dos custos totais;
  • 20% dos produtos representam 20% dos custos totais;
  • 50% dos produtos representam 10% dos custos totais.

Esses dados são de fundamental importância para você manter um estoque controlado e realizar uma gestão adequada para investimentos perfeitos.

Gostou da planilha? A análise de uma planilha com curva ABC no excel já ajudou você a controlar seu estoque e realizar investimentos corretos? Deixe seus comentários a respeito da experiência com essa planilha.

Compartilhe com amigos:

Inscreva-se e receba o conteúdo semanal
que realmente importa

Experimente! É grátis e, se não gostar, basta cancelar o envio com 1 clique.
 

Deixe seu comentário aqui

  • Muito legal as planilhas, e o modo de fazer…pena q não sou tão bom com excel…

    • Rafael

      Oi Mineirinho, tudo bom?

      Se quiser melhorar suas habilidades no Excel, temos dois cursos online muito bons que podem te ajudar bastante! Um é iniciante/intermediário e o outro avançado. Você pode conhecer um pouco mais deles (e inclusive ver algumas aulas gratuitas) nesse link – http://cursos.luz.vc/

  • Bruno Amorim

    Como se faz o calculo da ultima tabela para fazer a separação do ABC???????

    • Rafael

      Oi Bruno, para fazer esse cálculo é necessário colocar os valores em ordem decrescente em uma coluna e fazer o acumulado de cada um sucessivamente em outra.

      Assim você vai ter o quanto ele representa em % e o quanto o acumulado representa. A partir daí é só pensar nas porcentagens e estabelecer os critérios de quais são os grupos A, B e C

  • Maysa Biazon

    boa tarde,
    como obtenho os valores da ultima coluna da segunda tabela, % produtos totais ?

    • Rafael

      Basta fazer um CONT.SE atrelado ao grupo da curva ABC que ele está atrelado

  • Giovanna Fin

    Poderia explicar como faz essa segunda tabela e como determina essas linhas coloridas do ABC?
    Obrigada

  • Madson Braz

    Estou fazendo uma curva ABC utilizando uma tabela com autofiltro, gostaria de saber se conhece alguma formula que calcule o percentual acumulado quando o filtro é aplicado.

    • Rafael

      Oi Madson, normalmente a função SUBTOTAL funciona para esses casos

  • Thiago Miranda

    faltou colocar a região em baixo da curva preenchida para ficar 10 =v

    • Rafael

      Justo, boa dica Thiago!

  • Cláudio César Prates Junior

    Olá rafael , muito bom esse post, eu consegui fazer tudo normal, só não entendi como é feita a tabela de referência 70-20-10, eu não entendi muito bem como funciona essas referências e como fazer essa 2ª tabela mais especificamente as duas ultimas colunas, a primeira usei a função Cont.se e deu certo,ja as outras colunas nem tenho ideiá, e também como pintar as linhas do grafico do mesmo jeito que no artigo?

    https://uploads.disquscdn.com/images/fa01de66f01c002ae9ac3e0c5f4a1a1ce780c16cffce7331d40266a2457907c5.jpg

    Desde já Agradeço!

    • Rafael

      Fala César, tudo bom? Vamos por partes:

      1 – Para fazer a terceira coluna, basta você pegar os valores das porcentagens acumuladas (coluna F), veja que no terceiro produto (quantidade do tipo A) temos aproximadamente 70%. Para achar esse valor você pode fazer um PROCV (eu criaria uma coluna inicial auxiliar com a numeração da quantidade de produtos (1, 2, 3, 4, etc) e usaria a coluna de PRODUTOS da tabela auxiliar para encontrar a porcentagem correspondente.

      2 – A % de produtos totais é ainda mais simples, basta fazer um CONT da quantidade total de produtos e fazer uma divisão simples da quantidade de produtos em um determinado grupo (A, B ou C) por esse total.

      3 – As cores das linhas é um pouco mais complicado. Não lembro muito bem como foi feito, mas acredito que ele tenha inserido três séries de dados no gráfico, cada série buscando os valores das porcentagens acumuladas referente a cada grupo e depois modificou a cor de cada uma das linhas – particularmente eu não acho que isso deixe o gráfico mais explicativo…

      • Cláudio César Prates Junior

        hum, entendi,fiquei meio confuso ainda na 1ª parte, mas deu pra pegar, a coluna de produtos totais fiz da seguinte maneira, =CONT.SE($B$2:$B$11;B16)/CONT.VALORES($B$2:$B$11) , vi isso num video no yotube rs, deu certo, mas a coluna do custos totais fiz assim, não chegou a dar 70-20-10, deu valores aproximados, veja na figura

        https://uploads.disquscdn.com/images/8b10249e540ac564b13a49023f07fe8b079a5d76c37dfde65a564a7229dd1f71.jpg

        • Rafael

          Não tem problema serem valores aproximados não. Na hora de definir os cortes que você terá que tomar uma decisão, se quer pegar até o última valor antes dos valores de corte (70 – 20 – 10) ou o valor seguinte

          • Cláudio César Prates Junior

            ah certo, obrigado mais uma vez!!!

          • Rafael

            De nada Cláudio! Abraços e qualquer outra dúvida estou a disposição

  • Pingback: Curva ABC: Qual sua importância para o controle de custos? - Coteaqui()

Buscar no Blog LUZ

Digite abaixo…

×
Receba a sua dose semanal de conhecimento grátis
×
[optin_monster_id]
[optin_monster_id]
[email]
[email]
[opt_in]
[opt_in]
[opt_in]
[opt_in]