Como Fazer Análise ABC no Excel: 5 Passos Fáceis

A análise ABC classifica os itens de inventário em três classes pelo valor de seu uso anual. Os itens da Classe A são os poucos que representam a maior parte do dinheiro. Os itens da Classe C são os muitos que representam pouco, e a classe B fica no meio. No Excel, você pode fazer isso com uma única tabela: valor anual, participação no total, um total acumulado e uma fórmula que atribui cada classe.

Este guia explica o que as classes significam, os cinco passos no Excel, um exemplo prático e como gerar um gráfico com o resultado. Ele também aborda como escolher seus limites de corte e o que fazer com cada classe assim que a análise estiver concluída.

O que é a análise ABC

A análise ABC é uma forma de decidir quais itens merecem mais atenção. Ela se baseia em um padrão simples: uma pequena parcela de itens responde por uma grande parcela dos gastos.

Um capítulo de 2012 sobre análise e controle de despesas farmacêuticas, da Management Sciences for Health (MSH), descreve isso de forma clara. Ele observa que "um número relativamente pequeno de itens responde pela maior parte do valor do consumo anual". E acrescenta: "A análise desse fenômeno é conhecida como análise de Pareto ou, mais comumente, análise ABC."

O mesmo capítulo explica que os itens "podem ser classificados em três categorias (A, B e C) com base no valor de seu uso anual". O método é o mesmo, quer você estoque medicamentos, peças de reposição ou produtos de varejo.

Um ponto que pode passar despercebido é que as classes não são rótulos permanentes. A MSH observa que "Se os padrões de uso mudarem, o item pode cair em uma categoria diferente na próxima vez que a análise ABC for realizada". Portanto, a análise ABC funciona melhor como uma verificação rotineira, e não como um projeto pontual.

O que significam as classes A, B e C

O capítulo da MSH apresenta faixas típicas para cada classe:

Classe	Parcela de itens	Parcela do valor anual	O que geralmente significa
A	10 to 20 percent	75 to 80 percent	Poucos itens, a maior parte do dinheiro
B	10 to 20 percent	15 to 20 percent	Um grupo intermediário
C	60 to 80 percent	5 to 10 percent	Muitos itens, pouco dinheiro

Essas são faixas típicas, não regras. A MSH afirma que "Esses limites são um tanto flexíveis". Seu exemplo define a classe A como os itens que somam 70 percent dos fundos.

O valor que direciona as classes é o valor de consumo anual: unidades usadas em um ano multiplicadas pelo custo unitário. Um item barato usado em volumes enormes pode parar na classe A. Um item caro usado uma vez por ano pode parar na classe C.

Um artigo de 2014 no American Journal of Business Education questiona o uso exclusivo do valor. Ele argumenta que os livros didáticos "focam no volume financeiro como critério único" e recomenda a adição de outros critérios. Para uma primeira etapa, o valor é o método que o capítulo da MSH utiliza.

O que você precisa antes de começar

A análise ABC no Excel precisa de apenas algumas colunas por item:

• Nome do item ou SKU. Uma linha por item.
• Unidades anuais usadas ou compradas. Use o mesmo período de 12 meses para cada item.
• Custo unitário. O custo de uma unidade, na mesma unidade de medida que você utiliza para contar.

A MSH enfatiza a correspondência do período: "Certifique-se de que o mesmo período de revisão seja usado para todos os itens para evitar comparações inválidas". Ela também aconselha o uso da mesma unidade básica para custo e quantidade, como um comprimido ou uma única caixa, em vez de misturar tamanhos de embalagens.

Se os seus dados vêm de um sistema de inventário ou compras, exporte-os como um arquivo CSV ou Excel. Remova os itens sem atividade no período, ou mantenha-os sabendo que eles cairão na classe C.

Como fazer a análise ABC no Excel

Os cinco passos abaixo seguem o método do capítulo da MSH, adaptado para fórmulas do Excel. O exemplo coloca um título na linha 1, cabeçalhos na linha 2 e 10 itens nas linhas 3 a 12. As colunas A, B e C contêm o nome do item, as unidades anuais e o custo unitário.

Passo 1: Listar os itens, unidades e custo unitário

Insira ou cole uma linha por item com seu nome, unidades anuais e custo unitário. Adicione cabeçalhos na linha 2 para que a tabela seja fácil de ordenar mais tarde.

Verifique os dados antes de prosseguir. Procure por custos em branco, quantidades negativas e SKUs duplicados, pois cada um deles distorcerá os totais. Um filtro rápido em cada coluna costuma encontrá-los.

Se várias compras do mesmo item foram feitas a preços diferentes, use um custo consistente. A MSH observa que "uma média ponderada ou uma média FIFO" são as alternativas mais precisas quando o custo unitário real é difícil de rastrear.

Passo 2: Calcular o valor anual e sua participação no total

Na coluna D, multiplique as unidades pelo custo para obter o valor anual de cada item. Em D3, insira =B3*C3 e arraste a fórmula para baixo.

Na coluna E, divida cada valor pelo total de todos os valores para obter sua participação. Em E3, insira =D3/SUM($D$3:$D$12) e arraste para baixo. Os cifrões mantêm o intervalo total fixo à medida que a fórmula é copiada. Formate a coluna E como porcentagem com duas casas decimais.

A MSH recomenda essa precisão por um motivo. Em suas palavras, "vários itens podem estar próximos em valor e muitos podem representar menos de 1 percent do valor total".

Passo 3: Ordenar os itens por valor, do maior para o menor

Selecione a tabela inteira, incluindo os cabeçalhos, e ordene pela coluna D do maior para o menor. No Excel, isso é feito em Dados, depois Classificar, com a coluna D e a ordem definida de Maior para Menor.

Se preferir uma fórmula, a função SORT retorna uma cópia classificada. A sintaxe da Microsoft é =SORT(array,[sort_index],[sort_order],[by_col]), onde uma ordem de classificação de -1 significa decrescente. Para esta tabela, =SORT(A3:E12,4,-1) ordena pela quarta coluna, com o valor mais alto primeiro.

Após este passo, o item com o maior valor anual fica no topo. Essa ordem é o que torna o total acumulado no próximo passo significativo.

Passo 4: Adicionar a porcentagem acumulada

Na coluna F, adicione um total acumulado das participações. Em F3, insira =SUM($E$3:E3) e arraste para baixo. A primeira parte do intervalo permanece fixa, e a segunda parte cresce uma linha a cada vez.

A última linha deve mostrar 100 percent. Se não mostrar, verifique se há células em branco ou valores de texto nas colunas D e E.

Esta coluna é o coração da análise ABC. Ela mostra quanto do valor total os itens acima de cada linha representam juntos.

Passo 5: Atribuir as classes A, B e C

Na coluna G, use uma fórmula para rotular cada item. Com limites de corte de 80 e 95 percent, insira isto em G3 e arraste para baixo:

=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")

A função IFS verifica cada condição em ordem e retorna a primeira correspondência. O próprio exemplo da Microsoft usa o mesmo padrão, com TRUE como o último caso geral. Itens com até 80 percent acumulados tornam-se A, itens com até 95 percent tornam-se B, e o restante torna-se C.

Por fim, conte cada classe com =COUNTIF(G3:G12,"A") e o mesmo para B e C. Compare as contagens com as faixas típicas acima. Ajuste os limites de corte se a classe A for grande ou pequena demais para a sua equipe gerenciar.

Um exemplo prático

Aqui está uma tabela ilustrativa para 10 itens, já ordenada pelo valor anual. Os números são exemplos, não dados de uma empresa real.

Item	Unidades anuais	Custo unitário	Valor anual	Participação	Acumulado	Classe
SKU-01	1,200	$45.00	$54,000	36.00%	36.00%	A
SKU-02	3,000	$12.00	$36,000	24.00%	60.00%	A
SKU-03	500	$40.00	$20,000	13.33%	73.33%	A
SKU-04	8,000	$1.50	$12,000	8.00%	81.33%	B
SKU-05	2,000	$4.00	$8,000	5.33%	86.67%	B
SKU-06	600	$10.00	$6,000	4.00%	90.67%	B
SKU-07	1,000	$5.00	$5,000	3.33%	94.00%	B
SKU-08	1,500	$3.00	$4,500	3.00%	97.00%	C
SKU-09	700	$5.00	$3,500	2.33%	99.33%	C
SKU-10	400	$2.50	$1,000	0.67%	100.00%	C

O valor anual total é $150,000. Três itens, 30 percent da lista, compõem 73.33 percent do valor e ficam na classe A. Quatro itens caem na classe B, e os três últimos, valendo 6 percent do valor, caem na classe C.

Dois detalhes se destacam. O SKU-04 tem de longe a maior quantidade de unidades, mas seu baixo custo o coloca na classe B. E com apenas 10 itens, as participações das classes não corresponderão às faixas típicas, o que é normal para uma lista curta.

Como gerar um gráfico com o resultado

Um gráfico torna o padrão fácil de mostrar em uma reunião. A MSH sugere traçar a porcentagem acumulada em relação ao número do item, o que gera a conhecida curva ABC.

O Excel possui um gráfico integrado para isso. A Microsoft descreve um gráfico de Pareto como aquele que "contém tanto colunas ordenadas em ordem decrescente quanto uma linha que representa a porcentagem total acumulada". Para criar um, selecione os nomes dos itens e os valores anuais, depois escolha Inserir, Inserir Gráfico Estatístico e Pareto.

Adicione duas linhas horizontais ou rótulos nos seus limites de corte, como 80 e 95 percent, para que os visualizadores possam ver onde cada classe começa. Nosso guia sobre como criar um gráfico de Pareto com IA aborda o gráfico em si com mais profundidade.

Escolhendo seus limites de corte

Não existe um único limite de corte correto. A MSH explica que a escolha "depende de como o volume e o valor estão dispersos entre os itens da lista". Também depende de "como os resultados da análise ABC serão utilizados".

A capacidade de gestão é o limite prático. A MSH coloca isso diretamente: "a alocação de itens para a classe A deve ser baseada na capacidade de gestão". Se a sua equipe consegue revisar 50 itens de perto a cada mês, uma classe A com 300 itens anula o propósito.

Algumas abordagens comuns:

• Limites de valor. A até 80 percent do valor, B até 95 percent, C para o restante. Este é o método utilizado acima.
• Limites por contagem de itens. Os 20 percent superiores dos itens por valor tornam-se A, os próximos 30 percent B, e o restante C.
• Listas fixas. Algumas equipes definem a classe A como os 25 ou 50 itens principais, independentemente de sua participação no valor.

Qualquer que seja a sua escolha, anote-a e use-a sempre. Comparar as classes deste trimestre com as do trimestre passado só funciona se os limites de corte permanecerem os mesmos.

O que fazer com cada classe

O objetivo da análise ABC é direcionar esforços para onde está o dinheiro. O capítulo da MSH lista várias maneiras de usar os resultados:

• Pedir itens da classe A com mais frequência. A MSH afirma que pedir itens da classe A "com mais frequência e em quantidades menores deve levar a uma redução nos custos de manutenção de estoque".
• Negociar os preços da classe A primeiro. "Reduções de preço para itens classificados como produtos A na análise podem levar a economias significativas", de acordo com o capítulo.
• Contar o estoque da classe A com mais frequência. A MSH observa que "as contagens cíclicas de estoque devem ser orientadas pela análise ABC, com contagens mais frequentes para itens da classe A".
• Acompanhar o status dos pedidos da classe A. Uma falta inesperada de um item da classe A pode levar a compras de emergência dispendiosas.

Os itens da classe C podem ter regras mais simples, como pedidos maiores e menos frequentes e menos contagens. A classe B fica no meio. Se os itens de baixa rotatividade forem uma preocupação, nosso guia sobre como identificar estoque de baixa rotatividade combina muito bem com esta análise.

Fazendo isso mais rápido com IA

Os passos no Excel levam alguns minutos depois que os dados estão limpos. Limpar a exportação e repetir o trabalho a cada trimestre demora mais.

Um espaço de trabalho de IA pode fazer a aritmética e a ordenação em uma única solicitação. Faça o upload da exportação de inventário ou compras para o Powerdrill Bloom e peça em linguagem natural uma análise ABC com seus limites de corte. Solicite o valor anual, a participação, a porcentagem acumulada e a classe para cada item, além de um gráfico de Pareto.

Depois, verifique como faria com qualquer planilha. Confirme o valor anual total em relação à sua própria soma e faça uma amostragem de dois itens em cada classe. Nossa página do assistente de IA para Excel aborda esse tipo de trabalho com planilhas em mais detalhes. Para uma visão mais ampla das ferramentas de previsão, consulte este compilado de ferramentas de IA para previsão de estoque e demanda.

Erros comuns a evitar

• Misturar períodos de tempo. Doze meses para um item e seis para outro tornam as participações sem sentido.
• Usar unidades em vez de valor. As classes dependem das unidades multiplicadas pelo custo, não apenas das unidades.
• Esquecer de ordenar antes do total acumulado. Uma porcentagem acumulada em uma lista não ordenada coloca os itens na classe errada.
• Tratar as classes como permanentes. Execute novamente a análise a cada trimestre ou ano, pois os itens mudam de classe.
• Limites de corte que ignoram a capacidade. Uma lista da classe A longa demais para ser gerenciada de perto não receberá mais atenção do que a classe B.
• Ignorar itens baratos essenciais. Um item de baixo valor ainda pode interromper o trabalho se acabar. O capítulo da MSH combina a análise ABC com uma classificação separada de itens vitais, essenciais e não essenciais.

Quando sua lista de itens vem de uma exportação desorganizada, você pode experimentar o Powerdrill Bloom para construir a primeira tabela e gráfico ABC.

Perguntas frequentes

O que é a análise ABC na gestão de estoque?

A análise ABC classifica os itens em três classes pelo valor de consumo anual. Os itens da classe A são os poucos que respondem pela maior parte do valor. Os itens da classe C são os muitos que respondem por pouco, e a classe B fica no meio. Ela ajuda as equipes a focar os esforços de controle onde está o dinheiro.

Como calcular a análise ABC no Excel?

Multiplique as unidades anuais pelo custo unitário de cada item e, em seguida, divida pelo total para obter a participação de cada item. Ordene pelo valor do maior para o menor, adicione um total acumulado das participações e atribua as classes com uma fórmula como IFS. Limites de corte de 80 e 95 percent estão dentro das faixas típicas do capítulo da MSH.

Quais são as porcentagens para a análise ABC?

Uma diretriz comum é que a classe A contenha de 10 a 20 percent dos itens e de 75 a 80 percent do valor. A classe B contém outros 10 a 20 percent dos itens e de 15 a 20 percent do valor. A classe C contém de 60 a 80 percent dos itens e de 5 a 10 percent do valor.

Qual é a fórmula para a classificação ABC no Excel?

Com a porcentagem acumulada na coluna F e os dados começando na linha 3, use =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Altere 0.8 e 0.95 para corresponder aos seus próprios limites de corte. Fórmulas SE aninhadas podem fazer o mesmo trabalho.

Por que a análise ABC é importante?

Ela mostra para onde vai a maior parte do dinheiro do estoque, para que as equipes possam gerenciar esses itens de perto. Os usos típicos incluem pedir itens da classe A com mais frequência, negociar seus preços primeiro e contá-los com mais frequência. Ela também sinaliza gastos que não correspondem aos planos.

Fontes: Management Sciences for Health, MDS-3 Capítulo 40: Analisando e controlando despesas farmacêuticas · Ravinder e Misra, Análise ABC para Gestão de Estoque (2014) · Suporte da Microsoft, função SORT · Suporte da Microsoft, função IFS · Suporte da Microsoft, Criar um gráfico de Pareto.