Como fazer Curva ABC – Como criar gráfico curva ABC no Excel
Você aprenderá neste artigo como funciona a curva ABC, também chamado de diagrama de Pareto e aprenderá a criar o gráfico de curva ABC no Excel.
A curva ABC é um método de classificação também chamado de 80/20 criado pelo economista italiano Vilfredo Pareto, que tem por finalidade identificar os itens de maior importância ou impacto, os quais são normalmente em um número menor.
O método tem o apelido de 80/20 por que ele separa os itens em três categorias, A, B e C.
Estas categorias por sua vez terão um grupo de dados, a categoria A terá os 20% dos valores com maior representatividade no valor total, a classificação B terá os 30% de maior impacto e a categoria C os outros 50%.
Estes percentuais estão ligados á quantidade, então para uma amostra de 100 itens, teremos:
A -> 20 linhas
B -> 30 linhas
C -> 50 linhas
Após a divisão das informações em categorias é gerado um gráfico que demonstra como é distribuído o valor ao longo da quantidade de itens.
Este gráfico é muito utilizado na avaliação de Estoques, Vendas, Clientes, Fornecedores, Risco em projetos, entre várias outras aplicações.
1. Criando os dados para o gráfico Curva ABC
Para download do modelo pronto coloque seu nome e e-mail ao final do artigo, será gerado um link automaticamente abaixo do formulário e bastará clicar nele.
Para criar o gráfico de curva ABC inicialmente crie uma pequena tabela auxiliar:
Esta pequena tabela conterá os cálculos necessários para nos auxiliar e nos dar algumas respostas da nossa análise de dados.
A nossa tabela para análise dos dados é semelhante a tabela abaixo. No momento esqueça as colunas que não estão preenchidas, foque apenas nas duas colunas iniciais CLIENTE e VALOR.
No nosso modelo iremos realizar uma análise dos clientes que são mais valiosos para a companhia.
Por esta imagem preencha as colunas com as fórmulas:
| Coluna | Fórmula | Motivo |
|---|---|---|
| Qtde. | =CONT.NÚM($B$2:$B$90)*SOMA(M2:$M$4) | Identificar a quantidade de registros de cada categoria. Arraste a fórmula para todas as células da coluna |
| Valor | =SOMASE(H:H;L2;B:B) | Esta função realiza a soma condicional dos valores que estão em determinada categoria. |
| Total | =P3+O2 | Valor total acumulado das categorias. |
| Limite | =MAIOR($B$2:$B$90;N2) | Esta fórmula usa como referência a quantidade de registros de cada categoria e retorna o k maior valor daquela classificação. |
As fórmulas desta tabela não estão todas funcionando ainda, pois é necessário que sejam preenchidas as classificações da tabela principal para que as somas condicionais funcionem.
Na tabela de valores classifique-a pela coluna de valor, antes de iniciar as fórmulas.
| Coluna | Fórmula | Motivo |
|---|---|---|
| CLAS | =PROCV(B2;$K$2:$L$4;2;1) | Esta função retorna a classificação da categoria baseada no valor da lista e no limite da coluna Qtde da tabela auxiliar.
Note o uso do PROCV aproximado para realizar a consulta. Par que funcione é necessário que os valores estejam em ordem crescente. |
| %Valor | =B2/SOMA($B$2:$B$90) | A fórmula realiza o cálculo de quanto o valor da lista representa percentualmente do total da coluna de valor. |
| %Acum. | =B2/SOMA($B$2:$B$90)+SE(ÉNÚM(F1);F1;0) | Soma o percentual acumulado dos valores até aquele item da lista. |
| %Categ. | =SE(C2<>””;F2-SOMA($G$1:G1);””) | Esta função auxiliar irá marcar apenas as linhas dos limites das categorias. Será utilizada no gráfico para destacar estes valores. |
| Separação | =SE(H2<>H3;CONT.VALORES($B$2:B2);NÃO.DISP()) | Retorna a contagem dos valores que estão também nos limites das categorias A, B e C. Serão utilizados no gráfico da Curva ABC |
| Qtde | =SEERRO(D2;””) | Esta coluna irá ser utilizada como auxiliar no gráfico da curva ABC |
Se você já pesquisou na internet a respeito deste gráfico há várias formas de criá-lo, no nosso caso nós criamos uma estrutura que permitirá a separação visual das categorias A, B e C com uma cor de fundo, como na imagem abaixo do gráfico ABC.
2. Criando o gráfico Curva ABC
Como os dados estão agora todos preparados basta criarmos o nosso gráfico.
- Selecione os dados da coluna Separação;
- Pressione as teclas Ctrl+F1, o Excel irá criar um gráfico com estes dados;
- Clique com o botão direito sobre o gráfico criado e altere o tipo de gráfico para Barras Horizontais 100% Empilhadas;
- Clique com o botão direito sobre a barra criada do gráfico e selecione a opção Formatar série de dados;
- Altere a opção Largura do Espaçamento para 0%, desta forma a série irá ocupar todo o gráfico. Altere as cores como desejar, a primeira refere-se a classificação A, a segunda a classificação B e a terceira a classificação C;
- Selecione o gráfico e clique na barra Design do gráfico no botão Selecionar Dados;
- Clique no botão Adicionar;
- Em Valores da série selecione os valores da colunas %Acum.;
- Clique em OK;
- Veja que foi criado um gráfico desformatado, clique sobre este gráfico e na opção Formatar Série de Dados. Em Opções de Série, selecione a opção Plotar série no Eixo Secundário;
- Clique com o botão direito sobre qualquer parte do gráfico e clique em Alterar tipo de gráfico;
- No último item da lista altere para gráfico de linha, veja como os dados ficam.
- Neste ponto nós já conseguimos ver algumas informações, como que na classificação A temos mais de 60% de todo o faturamento. Ou seja, 20% dos clientes são responsáveis por mais de 60% do faturamento total da empresa.
- Clique sobre cada uma das barras de fundo e clique em Adicionar Rótulo de Dados, veja que o gráfico incluirá a quantidade de clientes que estão naquela categoria.
- Agora clique sobre o gráfico de linha e em Adicionar Rótulo de Dados, veja que os dados são todos preenchidos. Nas Opções do conteúdo do Rótulo de dados desmarque todas e clique em Selecionar Intervalo. Selecione a coluna %Categ. Assim serão destacados apenas os limites percentuais das categorias do gráfico.
- Se desejar adicione também o Eixo X Secundário, ele será exibido acima com os valores. Para espaçá-los clique na opção Rótulos Intervalo entre os rótulos e altere a propriedade Especificar unidade do intervalo para 5.
Planilha de Pequenas Empresas
Você tem uma pequena empresa e precisa de um controle simples e profissional?
Conheça a nossa planilha de Vendas e Estoque.
Funcionalidades
- Cadastro de clientes
- Configurações
- Fornecedores
- Metas Vendas
- Produtos
- Vendedores
- Entrada de produto
- Saída de estoque
- Fluxo de caixa
- Vendas
Relatórios
- Comissões
- Estoque
- Fluxo de caixa mensal
- Fluxo de caixa Perda/Ganho
- Vendas por canal
- Vendas por dia
- Vendas por mês
- Vendas por produto
Dashboards
- Dashboard de Vendas
- Dashboard de Fluxo de Caixa
Ferramentas
- Cálculo de preço do produto
- Planilha de orçamento para o cliente
São mais de 20 funcionalidades em uma solução completa para o gerenciamento da sua empresa, clique abaixo para conhecer mais e comprar a planilha.
4. DOWNLOAD
Você pode fazer o download da planilha digitando o seu e-mail para assinar a nossa newsletter gratuita abaixo.
Abraço
Marcos Rieper
