Como Fazer Curva ABC no Excel - Jornada do Gestor
Início Excel Como Fazer Curva ABC no Excel
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
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 %
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
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 %.
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
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.
Passo 13: Selecione a legenda Série 1 e após clique em DELETE.
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.
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
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%.
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).
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.
Conheça também a Planilha de Controle de Estoque da LUZ!
Artigo anteriorAprenda a implementar a metodologia OKR (objectives and key results)
Próximo artigoComo elaborar um plano de contas para empresas que vendem produtos
De nada Cláudio! Abraços e qualquer outra dúvida estou a disposição
ah certo, obrigado mais uma vez!!!
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
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
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…
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?
Justo, boa dica Thiago!
faltou colocar a região em baixo da curva preenchida para ficar 10 =v
Oi Madson, normalmente a função SUBTOTAL funciona para esses casos
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.
Poderia explicar como faz essa segunda tabela e como determina essas linhas coloridas do ABC?
Basta fazer um CONT.SE atrelado ao grupo da curva ABC que ele está atrelado
como obtenho os valores da ultima coluna da segunda tabela, % produtos totais ?
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
Como se faz o calculo da ultima tabela para fazer a separação do ABC???????
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/