KHIZHNYAKOVComo fazer a análise ABC-XYZ de estoque no Excel
Análise ABC-XYZ de sortimento — Modelo Excel — Ver a planilha →
Em quase toda loja, uma pequena parte dos produtos responde pela maior parte do faturamento. O resto ocupa prateleira, capital e atenção. O problema é que, sem números, a escolha do que comprar mais e do que liquidar acaba sendo feita por intuição ou pelo produto de que o dono mais gosta.
A análise ABC-XYZ coloca isso em dados. A parte ABC mostra a importância de cada produto no faturamento; a parte XYZ mostra se a demanda é estável ou imprevisível. Cruzando as duas, você tem nove grupos, cada um com uma estratégia de estoque diferente. Veja como fazer no Excel.
Que dados você precisa
As vendas mensais de cada produto nos últimos 12 meses, em reais ou em unidades (a mesma medida para todos). Uma linha por produto, uma coluna por mês. Produtos lançados há poucos meses distorcem o resultado: analise-os à parte.
Parte 1: a análise ABC
- Total do ano por produto:
=SOMA(B2:M2). - Participação no faturamento:
=N2/SOMA($N$2:$N$200). - Ordene os produtos do maior para o menor faturamento.
- Participação acumulada:
=SOMA($O$2:O2), copiada para baixo. - Grupo:
=SE(P2<=80%;"A";SE(P2<=95%;"B";"C")).
Os limites de 80% e 95% são os mais usados, mas podem ser ajustados ao seu negócio.
Parte 2: a análise XYZ
A estabilidade da demanda é medida pelo coeficiente de variação (CV): desvio padrão das vendas mensais dividido pela média.
=DESVPAD.P(B2:M2)/MÉDIA(B2:M2)
E o grupo:
=SE(R2<=10%;"X";SE(R2<=25%;"Y";"Z"))
Quanto menor o CV, mais previsível o produto. Um produto que vende 100, 95, 105, 98 unidades por mês é X; um que vende 10, 0, 80, 5 é Z.
Exemplo: um pet shop
| Produto | Faturamento no ano | Participação | Acumulado | ABC | CV | XYZ |
|---|---|---|---|---|---|---|
| Ração 15 kg | R$ 48.000 | 48% | 48% | A | 6% | X |
| Ração 3 kg | R$ 30.000 | 30% | 78% | A | 18% | Y |
| Areia higiênica | R$ 12.000 | 12% | 90% | B | 9% | X |
| Petiscos | R$ 6.000 | 6% | 96% | C | 35% | Z |
| Brinquedos | R$ 3.000 | 3% | 99% | C | 22% | Y |
| Roupinhas | R$ 1.000 | 1% | 100% | C | 60% | Z |
Dois produtos fazem 78% do faturamento. As roupinhas vendem pouco e de forma muito irregular (provavelmente só no inverno).
Parte 3: a matriz de nove grupos
| X (estável) | Y (oscila) | Z (imprevisível) | |
|---|---|---|---|
| A | Nunca deixar faltar; reposição automática | Estoque de segurança maior | Acompanhar de perto, prever com cuidado |
| B | Reposição regular | Revisar pedidos com frequência | Comprar sob demanda |
| C | Reduzir estoque ao mínimo | Lotes menores | Reavaliar ou retirar do sortimento |
No exemplo: a ração de 15 kg (AX) precisa de reposição automática e nunca pode faltar; as roupinhas (CZ) são candidatas a sair do mix ou a entrar só na temporada.
Para contar produtos em cada célula da matriz: =CONT.SES($S:$S;"A";$T:$T;"X"). Para somar o faturamento: =SOMASES($N:$N;$S:$S;"A";$T:$T;"X").
Erros comuns
- Usar poucos meses. Com 3 meses de dados, o CV não diz nada. Doze é o ideal.
- Misturar reais e unidades. Escolha uma medida para todos.
- Esquecer a sazonalidade. Produtos de temporada aparecem como Z mesmo sendo previsíveis. Avalie-os com bom senso.
- Retirar um C que puxa vendas. Às vezes o item barato traz o cliente que compra o A. Olhe a matriz como ponto de partida, não como sentença.
- Fazer uma vez e esquecer. Refaça a cada trimestre ou semestre.
Planilha pronta: análise ABC-XYZ de sortimento
O modelo pago faz todas essas etapas automaticamente a partir das vendas mensais:
- «Parâmetros»: limites dos grupos A, B e X, Y, por padrão 80% e 95%, 10% e 25%, que você pode alterar.
- «Dados»: até 300 produtos com vendas de 12 meses; à direita, total, participação, participação acumulada e grupos ABC e XYZ calculados sozinhos.
- «Ranking»: produtos em ordem decrescente de receita, com os grupos.
- «Matriz»: quantos produtos e quanta receita há em cada um dos nove grupos, uma recomendação curta do que fazer com cada combinação e um gráfico.
Você não precisa ordenar nada à mão nem montar fórmulas de acumulado. Funciona no Excel 2007 ou mais recente e no LibreOffice, sem macros. O cálculo é uma estimativa.
Perguntas frequentes
ABC por faturamento ou por margem?
Por faturamento é o padrão e o mais fácil de obter. Fazer também por margem de lucro é um ótimo complemento: um produto A em faturamento pode ser C em lucro.
Funciona para restaurante ou serviço?
Funciona. No restaurante, os "produtos" são os pratos; num serviço, os tipos de atendimento. A lógica de concentração e estabilidade é a mesma.
O que fazer com produtos novos?
Deixe-os fora da análise até terem pelo menos 6 meses de vendas, ou acompanhe-os numa lista separada.
Com quantos produtos vale a pena?
A partir de umas 30 referências a análise já mostra padrões úteis. Com menos que isso, você provavelmente enxerga a concentração a olho.
