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.
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.
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).
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)
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).
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).
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.
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.
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"))
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:
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.
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:
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).
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.
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.
