KHIZHNYAKOVAnalisi ABC XYZ in Excel: quali prodotti tenere, quali rivedere
Analisi ABC-XYZ dell'assortimento — modello Excel — Vedi il modello →
In quasi ogni negozio o magazzino succede la stessa cosa: pochi articoli portano la maggior parte del fatturato, mentre molti altri occupano scaffali e capitale senza rendere quasi nulla. Il problema è che, a occhio, è difficile dire quali siano gli uni e quali gli altri.
L'analisi ABC risponde alla prima domanda: quali prodotti contano davvero per il fatturato. L'analisi XYZ risponde alla seconda: quanto è prevedibile la domanda di ciascuno. Insieme formano una matrice che suggerisce cosa tenere sempre in magazzino, cosa ordinare su richiesta e cosa togliere dall'assortimento. Vediamo come farla in Excel.
Passo 1: i dati di partenza
Ti servono le vendite di ogni prodotto per mese, idealmente degli ultimi 12 mesi, in euro o in pezzi (la stessa unità per tutti). Una riga per prodotto, una colonna per mese, e alla fine una colonna con il totale:
=SOMMA(B2:M2)
Passo 2: l'analisi ABC
Ordina i prodotti per fatturato decrescente e calcola per ciascuno la quota sul totale e la quota cumulata:
- quota:
=N2/SOMMA($N$2:$N$301) - quota cumulata:
=SOMMA($O$2:O2)
Poi assegna il gruppo con soglie dell'80% e del 95%:
=SE(P2<=0,8;"A";SE(P2<=0,95;"B";"C"))
Esempio con sei prodotti e 100.000 € di fatturato totale:
| Prodotto | Fatturato | Quota | Cumulata | Gruppo |
|---|---|---|---|---|
| P1 | 42.000 € | 42% | 42% | A |
| P2 | 25.000 € | 25% | 67% | A |
| P3 | 15.000 € | 15% | 82% | B |
| P4 | 9.000 € | 9% | 91% | B |
| P5 | 5.000 € | 5% | 96% | C |
| P6 | 4.000 € | 4% | 100% | C |
Due prodotti su sei fanno due terzi del fatturato. In un assortimento reale con centinaia di articoli lo squilibrio è spesso ancora più forte.
Una nota: alcuni includono nel gruppo A anche il prodotto che «scavalca» la soglia dell'80% (qui P3). Non c'è una regola unica: scegli un criterio e mantienilo.
Passo 3: l'analisi XYZ
L'XYZ misura la stabilità della domanda con il coefficiente di variazione: deviazione standard delle vendite mensili divisa per la media.
=DEV.ST.P(B2:M2)/MEDIA(B2:M2)
(nelle versioni più vecchie di Excel la funzione si chiama DEV.ST.POP). Soglie diffuse: X fino al 10%, Y fino al 25%, Z oltre.
=SE(Q2<=0,1;"X";SE(Q2<=0,25;"Y";"Z"))
Due esempi su sei mesi:
- un caffè in grani: 100, 110, 95, 105, 90, 100 pezzi → coefficiente circa 6%, gruppo X: domanda regolare;
- ombrelli: 5, 0, 30, 2, 40, 3 pezzi → coefficiente oltre il 100%, gruppo Z: dipende tutto dal meteo.
Passo 4: la matrice 3×3
Unendo i due gruppi ottieni nove combinazioni, ognuna con una strategia diversa:
| X (stabile) | Y (variabile) | Z (irregolare) | |
|---|---|---|---|
| A | scorta sempre disponibile, riordino automatico | scorta di sicurezza più alta | seguire da vicino, previsioni dedicate |
| B | riordino regolare | scorta media | ordinare con prudenza |
| C | scorta minima | ordini su richiesta | rivedere o eliminare |
Gli AX sono i prodotti che non devono mai mancare. I CZ sono i candidati a lasciare lo scaffale: vendono poco e in modo imprevedibile.
Errori comuni
- Periodo troppo breve. Tre mesi non bastano per l'XYZ: la stagionalità viene scambiata per irregolarità.
- Prodotti nuovi nell'analisi. Un articolo lanciato a settembre ha mesi a zero e finisce automaticamente in Z.
- Unità miste. Pezzi per alcuni prodotti ed euro per altri rendono le quote senza senso.
- Eliminare un C senza pensarci. Alcuni prodotti vendono poco ma trascinano acquisti più grandi: guarda anche il contesto.
Il modello pronto
Il nostro modello di analisi ABC XYZ dell'assortimento è a pagamento e fa tutti questi calcoli in automatico, senza dover ordinare a mano. Nel foglio «Parametri» imposti i limiti dei gruppi A, B e X, Y (predefiniti 80% e 95%, 10% e 25%).
Nel foglio «Dati» inserisci fino a 300 prodotti con le vendite di 12 mesi: a destra compaiono totale, quota, quota cumulata e i gruppi ABC e XYZ. La «Classifica» mostra i prodotti in ordine di fatturato decrescente con i loro gruppi. La «Matrice» conta quanti prodotti e quanto fatturato ci sono in ciascuno dei nove gruppi, con una breve raccomandazione su cosa farne e un grafico.
Funziona in Excel 2007 e successivi e in LibreOffice, senza macro; tutti i calcoli usano formule standard.
Domande frequenti
Meglio usare il fatturato o il margine?
Il fatturato è il punto di partenza più comune, ma se hai i dati, un'analisi ABC sul margine è spesso più utile: un prodotto con tanto fatturato e margine bassissimo può valere meno di quanto sembri.
Ogni quanto rifare l'analisi?
Una volta a trimestre è un buon ritmo per la maggior parte dei negozi; dopo un cambiamento importante dell'assortimento o dei prezzi, conviene rifarla subito.
Come tratto i prodotti stagionali?
Analizzali a parte, o considera solo i mesi della stagione. Altrimenti un ottimo prodotto estivo finisce in Z solo perché d'inverno non si vende.
Funziona anche per i servizi?
Sì. Per uno studio o un'agenzia i «prodotti» possono essere i servizi o i clienti: l'ABC sui clienti mostra da chi dipende il fatturato, l'XYZ quanto sono regolari i loro ordini.
