Como Fazer a Curva ABC no Excel?

Sumário

1. Introdução
2. Cálculo das Porcentagens Individuais e Acumuladas
3. Regras de Classificação
4. Representação Gráfica
5. Download de Arquivos

Introdução

Em um post anterior, vimos todos os conceitos teóricos da Classificação ABC e também aprendemos um método com 5 passos para usar essa ferramenta na gestão de estoques. Contudo, quando lidando com muitos itens, torna-se indispensável automatizar os cálculos utilizando planilhas eletrônicas. Neste post vou explicar como fazer a Curva ABC no Excel usando algumas funções avançadas para parametrizar e automatizar as classificações dos itens.

Se preferir, você pode assistir o vídeo abaixo que mostra toda a resolução do exemplo.

Para construirmos a curva ABC, precisamos das seguintes informações de cada SKU:

Código do Item: Geralmente utiliza-se o SKU do produto, sendo opcional a inclusão do nome de cada produto;
Quantidade utilizada no período: geralmente são feitas curvas ABC com base na quantidade utilizada ou vendida dos SKUs em um período de um ano, porém dependendo da situação, outros períodos podem ser escolhidos;
Valor unitário: indica o valor unitário do SKU; caso esse valor tenha se alterado ao longo do ano, uma média ponderada pode ser calculada;

Na Figura abaixo podemos ver parte do exemplo que será solucionado neste post, com 25 SKUs. Se você quiser acompanhar a resolução deste exemplo, clique aqui para baixar o arquivo com os dados originais.

Curva ABC no Excel - Exemplo

Cálculo das Porcentagens Individuais e Acumuladas

O primeiro passo é criar uma nova coluna “Valor Total” e calcular o valor total utilizado de cada SKU no período, multiplicando a quantidade utilizada pelo valor unitário. A Figura abaixo mostra como isso foi feito para cada SKU.

Curva ABC no Excel - Valor Total

Em seguida, precisamos reordenar a tabela de forma que todos os dados sejam dispostos de maneira decrescente com relação ao “Valor Total”. Para isso, selecione toda a tabela de dados (Atividade 1 da figura abaixo), vá na aba “Dados” do Excel (2) e clique em “Classificar” (3). Uma caixa de diálogo será apresentada, onde você precisa escolher o campo “Valor Total” (4) e ordenar “Do Maior para o Menor” (5).

Curva ABC no Excel - Ordenação

Após clicar em OK, a tabela de dados estará em ordem decrescente com relação à coluna “Valor Total”. Agora precisamos calcular o valor total consumido pela empresa no período. Em nosso exemplo, vá para a célula E28 e coloque a seguinte fórmula:

=SOMA(E3:E27)

Curva ABC no Excel - Soma Total

Agora vamos fazer o cálculo das porcentagens individuais. Crie uma nova coluna com o título “Porcentagem Individual” e, para cada item, divida o valor utilizado por esse item pelo valor total que acabamos de calcular (Célula E28).

Curva ABC no Excel - Porcentagem Individual

Note que o a célula E28 está travada, para que possamos replicar essa fórmula para os demais itens mantendo o termo da divisão. Isso garante que o valor total consumido de cada item será dividido pelo valor total global, que está em uma única célula.

Após fazer isso, os valores resultantes estão no formato fracionário. Vamos utilizar porcentagens com duas casas decimais. Para isso, selecione todos os valores na coluna porcentagem individual (Atividade 1 da Figura abaixo), clique na formatação de porcentagem (2) e adicione duas casas decimais (3).

Curva ABC no Excel - Formatação Porcentagem Individual

O passo seguinte é calcular as porcentagens acumuladas. Para isso, lembre-se das regras que vimos neste artigo. A porcentagem acumulada do primeiro SKU é igual a sua porcentagem individual. Para os demais itens, a porcentagem acumulada será igual a sua porcentagem individual mais a porcentagem acumulada do item anterior. Para implementar essas regras em nosso exemplo, insira na célula G3 a fórmula =F3. E na célula G4 insira a fórmula =F4+G3. Em seguida, replique a fórmula da célula G4 para todas os itens restantes.

Curva ABC no Excel - Porcentagem Acumulada

Regras de Classificação

Neste exemplo, vamos implementar a classificação parametrizada. Isso significa que os critérios de corte podem ser modificados posteriormente e a planilha automaticamente reclassificará os itens com base nas novas regras. Para isso, precisamos criar uma pequena tabela auxiliar para armazenar esses parâmetros, conforme mostra a figura a seguir.

Curva ABC no Excel - Critérios de Corte

Para que a classificação seja automatizada, precisamos explicar ao Excel como ele deve decidir a qual classe cada item pertence. Tal decisão é baseada comparando os valores da coluna “Porcentagem Acumulada” com os critérios de corte. Para fazer isso, coloque a seguinte fórmula na célula H3:

=SE(G3<=$K$3;"A";SE(G3<=$K$4;"B";"C"))

Curva ABC no Excel - Classificação Automática

Note que as células K3 e K4 na fórmula foram travadas, pois estão em uma posição fixa na planilha. Agora replique essa fórmula para os demais itens e veja que a classificação deles é calculada automaticamente, mesmo se as regras de corte forem modificadas.

Para facilitar a análise dos dados, podemos automatizar algumas análises básicas da Curva ABC. Vamos criar duas novas colunas intituladas “Proporção de SKUs” e “Proporção de Valor”. A primeira informa qual a porcentagem de itens pertence a cada classe. Já a segunda coluna mostra quanto cada classe representa em termos de valor.

Para calcular a proporção de SKUs em cada classe, vamos usar a seguinte estratégia: primeiro vamos usar a função CONT.SE para contar quantas vezes cada classe aparece na coluna H e em seguida dividir esse valor pelo total de itens classificados, que pode ser obtido pela função CONT.VALORES. Coloque a seguinte função na célula L3 e replique para as demais linhas.

=CONT.SE($H$3:$H$27;J3)/CONT.VALORES($H$3:$H$27)

O cálculo da proporção de valores é realizado somando as porcentagens individuais dos itens pertencentes a cada classe. Isso pode ser feito com a fórmula SOMASE. Precisamos de três parâmetros para esta fórmula:

Parâmetro 1: O conjunto de dados onde serão feitas as buscas
Parâmetro 2: Critério usado para determinar se um valor deve ser somado ou não
Parâmetro 3: O conjunto de dados onde estão os valores que serão somados

Coloque na célula M3 a seguinte fórmula e replique para as demais linhas:

=SOMASE($H$3:$H$27;J3;$F$3:$F$27)

Essa fórmula percorre a coluna H (Classificação) e, para todos os valores que forem iguais ao valor em J3, será somado o valor correspondente na coluna F (Porcentagem Individual). Após colocar todas as fórmulas, você obterá o seguinte resultado:

Curva ABC no Excel - Relatório de Classificação

Representação Gráfica

Agora falta fazermos a representação gráfica da Curva ABC, na qual barras verticais são indicam as porcentagens individuais e uma linha mostra a porcentagem acumulada. O primeiro passo é selecionar as colunas “Item (SKU)”, “Porcentagem Individual” e “Porcentagem Acumulada”. Se você tiver dúvidas na realização deste passo, assista ao vídeo para ver como fazer a seleção de intervalos não contínuos.

Curva ABC no Excel - Representação Gráfica 1

Agora vá na aba “Inserir” (Atividade 1 da figura abaixo) e na seção “Gráficos” escolha a opção “Coluna 2D” (2). Após isso, você obterá o seguinte resultado:

Curva ABC no Excel - Representação Gráfica 2

Note que a representação gráfica está quase pronta. Falta apenas mudar o tipo da série de dados “Porcentagem Acumulada”, que em vez de ser apresentada em colunas precisa utilizar uma linha. Para fazer isso, clique em uma das barras da série “Porcentagem Acumulada” com o botão direito (1) e em seguida escolha a opção “Alterar tipo de gráfico de série” (2).

Curva ABC no Excel - Representação Gráfica 3

Uma caixa de diálogo se abrirá e basta alterar o tipo da série de dados “Porcentagem Acumulada” (1) e escolher a opção “Linha”, conforme ilustra a figura abaixo.

Curva ABC no Excel - Representação Gráfica 4

Após clicar em “OK” você terá o gráfico com a representação da Curva ABC, similar ao mostrado abaixo. Note que o título do gráfico foi apagado para melhorar a visualização.

Curva ABC no Excel - Representação Gráfica 5

Download de Arquivos

[XLSX] Exemplo do Vídeo Resolvido

[XLSX] Dados iniciais do exemplo (Sem resolução)

Siga nosso blog e curta nossa página!

7 thoughts on “Como Fazer a Curva ABC no Excel?

  1. Parabéns , agradecemos por disponibilizar o seu tempo e compartilhar seu conhecimento para ajudar outra pessoas.

        1. Olá Maurício! Infelizmente não tenho nenhuma planilha pronta com indicadores para gestão de estoques.
          Acho que esse seria um tema legal para um futuro post aqui no blog. Vou colocar em minha lista de temas futuros… nas próximas semanas vou fazer um artigo sobre indicadores de desempenho para estoques. Abraço!

  2. Parabenizo o idealizador desta obra maravilhosa!
    Que continue te dando muita sabedoria e te abençoando muito!
    Parabéns!!

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *