Come fare l'analisi ABC in Excel: 5 semplici passaggi

L'analisi ABC suddivide gli articoli in inventario in tre classi in base al valore del loro consumo annuo. Gli articoli di classe A sono i pochi che rappresentano la maggior parte della spesa. Gli articoli di classe C sono i molti che rappresentano una quota minima, mentre la classe B si colloca nel mezzo. In Excel, è possibile eseguirla con un'unica tabella: valore annuo, quota sul totale, totale parziale e una formula che assegna ciascuna classe.

Questa guida spiega il significato delle classi, i cinque passaggi in Excel, un esempio pratico e come rappresentare graficamente il risultato. Copre inoltre come scegliere le soglie e cosa fare con ciascuna classe una volta completata l'analisi.

Cos'è l'analisi ABC

L'analisi ABC è un metodo per decidere quali articoli meritano maggiore attenzione. Si basa su un modello semplice: una piccola quota di articoli rappresenta una quota elevata della spesa.

Un capitolo del 2012 sull'analisi e il controllo della spesa farmaceutica, pubblicato da Management Sciences for Health (MSH), lo descrive chiaramente. Sottolinea che "un numero relativamente esiguo di articoli rappresenta la maggior parte del valore del consumo annuo". E aggiunge: "L'analisi di questo fenomeno è nota come analisi di Pareto o, più comunemente, analisi ABC".

Lo stesso capitolo spiega che gli articoli "possono essere classificati in tre categorie (A, B e C) in base al valore del loro utilizzo annuo". Il metodo è lo stesso, sia che si gestiscano scorte di medicinali, pezzi di ricambio o prodotti per la vendita al dettaglio.

Un dettaglio facile da trascurare è che le classi non sono etichette permanenti. MSH osserva che "Se i modelli di consumo cambiano, l'articolo potrebbe rientrare in una categoria diversa la prossima volta che viene eseguita l'analisi ABC". Pertanto, l'analisi ABC funziona al meglio come controllo periodico, non come un progetto una tantum.

Cosa significano le classi A, B e C

Il capitolo di MSH fornisce intervalli tipici per ciascuna classe:

Classe	Quota di articoli	Quota del valore annuo	Cosa significa di solito
A	Dal 10 al 20 percento	Dal 75 all'80 percento	Pochi articoli, la maggior parte della spesa
B	Dal 10 al 20 percento	Dal 15 al 20 percento	Un gruppo intermedio
C	Dal 60 all'80 percento	Dal 5 al 10 percento	Molti articoli, una quota minima della spesa

Questi sono intervalli tipici, non regole rigide. MSH afferma che "Questi confini sono in qualche modo flessibili". Il suo esempio, infatti, fissa la classe A in corrispondenza degli articoli che sommati raggiungono il 70 percento dei fondi.

Il valore che determina le classi è il valore di consumo annuo: le unità utilizzate in un anno moltiplicate per il costo unitario. Un articolo economico utilizzato in volumi enormi può rientrare nella classe A. Un articolo costoso utilizzato una sola volta all'anno può finire nella classe C.

Un articolo del 2014 sull'American Journal of Business Education mette in discussione l'uso del solo valore. Sostiene che i libri di testo "si concentrano sul volume monetario come unico criterio" e raccomanda di aggiungere altri criteri. Per una prima analisi, il valore è il metodo utilizzato nel capitolo di MSH.

Cosa serve prima di iniziare

L'analisi ABC in Excel richiede solo poche colonne per articolo:

• Nome dell'articolo o SKU. Una riga per articolo.
• Unità annue utilizzate o acquistate. Utilizzare lo stesso periodo di 12 mesi per ogni articolo.
• Costo unitario. Il costo di una singola unità, espresso nella stessa unità di misura utilizzata per il conteggio.

MSH sottolinea l'importanza della corrispondenza del periodo: "Assicurarsi che venga utilizzato lo stesso periodo di analisi per tutti gli articoli per evitare confronti non validi". Consiglia inoltre di utilizzare la stessa unità di base per il costo e la quantità, come una compressa o una singola scatola, anziché mescolare confezioni di dimensioni diverse.

Se i dati provengono da un sistema di inventario o di acquisto, esportarli come file CSV o Excel. Rimuovere gli articoli senza alcuna attività nel periodo, oppure conservarli sapendo che rientreranno nella classe C.

Come eseguire l'analisi ABC in Excel

I cinque passaggi seguenti seguono il metodo descritto nel capitolo di MSH, adattato alle formule di Excel. L'esempio inserisce un titolo nella riga 1, le intestazioni nella riga 2 e 10 articoli nelle righe da 3 a 12. Le colonne A, B e C contengono il nome dell'articolo, le unità annue e il costo unitario.

Passaggio 1: Elencare gli articoli, le unità e il costo unitario

Inserire o incollare una riga per articolo con il relativo nome, le unità annue e il costo unitario. Aggiungere le intestazioni nella riga 2 in modo che la tabella sia facile da ordinare in seguito.

Verificare i dati prima di procedere. Cercare costi vuoti, quantità negative e SKU duplicati, poiché ognuno di essi falserà i totali. Un rapido filtro su ciascuna colonna di solito consente di individuarli.

Se sono stati effettuati più acquisti dello stesso articolo a prezzi diversi, utilizzare un unico costo coerente. MSH osserva che "una media ponderata o una media FIFO" sono le alternative più accurate quando il costo unitario effettivo è difficile da tracciare.

Passaggio 2: Calcolare il valore annuo e la sua quota sul totale

Nella colonna D, moltiplicare le unità per il costo per ottenere il valore annuo di ciascun articolo. In D3, inserire =B3*C3 e trascinare la formula verso il basso.

Nella colonna E, dividere ciascun valore per il totale di tutti i valori per ottenerne la quota. In E3, inserire =D3/SUM($D$3:$D$12) e trascinare verso il basso. I simboli del dollaro mantengono fisso l'intervallo del totale durante la copia della formula. Formattare la colonna E como percentuale con due cifre decimali.

MSH raccomanda tale precisione per un motivo preciso. Per usare le sue parole, "diversi articoli possono avere un valore molto vicino e molti possono rappresentare meno dell'1 percento del valore totale".

Passaggio 3: Ordinare gli articoli per valore, dal più grande al più piccolo

Selezionare l'intera tabella, comprese le intestazioni, e ordinare in base alla colonna D dal più grande al più piccolo. In Excel, l'operazione si esegue da Dati, quindi Ordina, selezionando la colonna D e impostando l'ordine da Più grande a più piccolo.

Se si preferisce una formula, la funzione SORT restituisce una copia ordinata. La sintassi di Microsoft è =SORT(array,[sort_index],[sort_order],[by_col]), dove un ordine di ordinamento pari a -1 indica l'ordine decrescente. Per questa tabella, =SORT(A3:E12,4,-1) ordina in base alla quarta colonna, a partire dal valore più alto.

Dopo questo passaggio, l'articolo con il valore annuo più alto si troverà in cima. Questo ordine è ciò che rende significativo il totale parziale nel passaggio successivo.

Passaggio 4: Aggiungere la percentuale cumulativa

Nella colonna F, aggiungere un totale parziale delle quote. In F3, inserire =SUM($E$3:E3) e trascinare verso il basso. La prima parte dell'intervallo rimane fissa, mentre la seconda cresce di una riga alla volta.

L'ultima riga dovrebbe mostrare il 100 percento. In caso contrario, verificare la presenza di celle vuote o valori di testo nelle colonne D ed E.

Questa colonna è il cuore dell'analisi ABC. Mostra quale quota del valore totale è rappresentata complessivamente dagli articoli sopra ogni riga.

Passaggio 5: Assegnare le classi A, B e C

Nella colonna G, utilizzare una formula per etichettare ciascun articolo. Con soglie dell'80 e del 95 percento, inserire questo in G3 e trascinare verso il basso:

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

La funzione IFS controlla ciascuna condizione in ordine e restituisce la prima corrispondenza. L'esempio di Microsoft utilizza lo stesso modello, con TRUE come valore finale predefinito. Gli articoli fino all'80 percento cumulativo diventano A, quelli fino al 95 percento diventano B e i restanti diventano C.

Enfin, contare gli articoli di ciascuna classe con =COUNTIF(G3:G12,"A") e fare lo stesso per B e C. Confrontare i conteggi con gli intervalli tipici sopra indicati. Modificare le soglie se la classe A risulta troppo grande o troppo piccola per essere gestita dal proprio team.

Un esempio pratico

Ecco una tabella illustrativa per 10 articoli, già ordinati per valore annuo. I numeri sono puramente indicativi e non rappresentano dati di un'azienda reale.

Articolo	Unità annue	Costo unitario	Valore annuo	Quota	Cumulativo	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

Il valore annuo totale è di $150,000. Tre articoli, pari al 30 percento dell'elenco, costituiscono il 73.33 percento del valore e rientrano nella classe A. Quattro articoli rientrano nella classe B e gli ultimi tre, che rappresentano il 6 percento del valore, rientrano nella classe C.

Due dettagli saltano all'occhio. Lo SKU-04 ha di gran lunga il maggior numero di unità, ma il suo costo ridotto lo colloca nella classe B. Inoltre, con soli 10 articoli, le quote delle classi non corrisponderanno agli intervalli tipici, il che è normale per un elenco così breve.

Come rappresentare graficamente il risultato

Un grafico rende il modello facile da mostrare durante una riunione. MSH suggerisce di tracciare la percentuale cumulativa rispetto al numero dell'articolo, ottenendo così la familiare curva ABC.

Excel dispone di un grafico integrato per questo scopo. Microsoft descrive un grafico di Pareto come un grafico che "contiene sia colonne ordinate in ordine decrescente sia una linea che rappresenta la percentuale totale cumulativa". Per crearne uno, selezionare i nomi degli articoli e i valori annui, quindi scegliere Inserisci, Inserisci grafico statistico e Pareto.

Aggiungere due linee orizzontali o etichette in corrispondenza delle proprie soglie, ad esempio all'80 e al 95 percento, in modo che chi guarda possa vedere dove inizia ciascuna classe. La nostra guida su come creare un grafico di Pareto con l'IA approfondisce ulteriormente l'argomento.

Scegliere le proprie soglie

Non esiste un'unica soglia corretta. MSH spiega che la scelta "dipende da come il volume e il valore sono distribuiti tra gli articoli in elenco". Dipende anche da "come verranno utilizzati i risultati dell'analisi ABC".

La capacità di gestione rappresenta il limite pratico. MSH lo dice chiaramente: "l'assegnazione degli articoli alla classe A deve basarsi sulla capacità di gestione". Se il proprio team è in grado di esaminare attentamente 50 articoli al mese, una classe A composta da 300 articoli vanifica lo scopo.

Alcuni approcci comuni:

• Soglie di valore. Classe A fino all'80 percento del valore, classe B fino al 95 percento, classe C per il resto. Questo è il metodo utilizzato sopra.
• Soglie basate sul numero di articoli. Il primo 20 percento degli articoli per valore diventa classe A, il successivo 30 percento classe B e il resto classe C.
• Elenchi fissi. Alcuni team definiscono la classe A come i primi 25 o 50 articoli, indipendentemente dalla loro quota di valore.

Qualunque sia la scelta, è importante metterla per iscritto e utilizzarla ogni volta. Il confronto tra le classi di questo trimestre e quelle del trimestre precedente funziona solo se le soglie rimangono le stesse.

Cosa fare con ciascuna classe

Lo scopo dell'analisi ABC è concentrare gli sforzi dove si trova la maggior parte del valore economico. Il capitolo di MSH elenca diversi modi per utilizzare i risultati:

• Ordinare gli articoli di classe A più spesso. MSH afferma che ordinare gli articoli di classe A "più spesso e in quantità minori dovrebbe portare a una riduzione dei costi di gestione dell'inventario".
• Negoziare prima i prezzi della classe A. "Le riduzioni di prezzo per gli articoli classificati come prodotti A nell'analisi possono portare a risparmi significativi", secondo il capitolo.
• Contare le scorte di classe A più frequentemente. MSH osserva che "i conteggi ciclici delle scorte dovrebbero essere guidati dall'analisi ABC, con conteggi più frequenti per gli articoli di classe A".
• Monitorare lo stato degli ordini della classe A. Una carenza imprevista di un articolo di classe A può comportare costosi acquisti di emergenza.

Per gli articoli di classe C si possono adottare regole più semplici, como ordini più consistenti ma meno frequenti e un minor numero di conteggi. La classe B si colloca nel mezzo. Se gli articoli a bassa rotazione rappresentano un problema, la nostra guida su come individuare l'inventario a bassa rotazione si integra perfettamente con questa analisi.

Farlo più velocemente con l'IA

I passaggi in Excel richiedono pochi minuti una volta che i dati sono puliti. Pulire l'esportazione e ripetere il lavoro ogni trimestre richiede invece più tempo.

Un'area di lavoro basata sull'IA può eseguire i calcoli e l'ordinamento in un'unica richiesta. Carica l'esportazione dell'inventario o degli acquisti su Powerdrill Bloom e richiedi in linguaggio naturale un'analisi ABC con le tue soglie. Chiedi il valore annuo, la quota, la percentuale cumulativa e la classe per ciascun articolo, oltre a un grafico di Pareto.

Poi verificalo come faresti con qualsiasi foglio di calcolo. Conferma il valore annuo totale confrontandolo con la tua somma ed effettua un controllo a campione su due articoli per ciascuna classe. La nostra pagina dedicata all'Excel AI assistant descrive questo tipo di lavoro sui fogli di calcolo in modo più dettagliato. Per uno sguardo più ampio sugli strumenti di previsione, consulta questa rassegna dei migliori strumenti di IA per la previsione dell'inventario e della domanda.

Errori comuni da evitare

• Mescolare periodi di tempo diversi. Considerare dodici mesi per un articolo e sei per un altro rende le quote prive di significato.
• Utilizzare le unità anziché il valore. Le classi dipendono dalle unità moltiplicate per il costo, non dalle sole unità.
• Dimenticare di ordinare prima del totale parziale. Una percentuale cumulativa su un elenco non ordinato inserisce gli articoli nella classe errata.
• Trattare le classi come permanenti. Eseguire nuovamente l'analisi ogni trimestre o anno, poiché gli articoli si spostano da una classe all'altra.
• Soglie che ignorano la capacità di gestione. Un elenco di classe A troppo lungo da gestire attentamente non riceverà maggiore attenzione rispetto alla classe B.
• Ignorare articoli economici ma critici. Un articolo di scarso valore può comunque bloccare il lavoro se si esaurisce. Il capitolo di MSH affianca all'analisi ABC una valutazione separata degli articoli vitali, essenziali e non essenziali.

Quando l'elenco degli articoli proviene da un'esportazione disordinata, puoi provare Powerdrill Bloom per creare la prima tabella e il primo grafico ABC.

Domande frequenti

Cos'è l'analisi ABC nella gestione dell'inventario?

L'analisi ABC suddivide gli articoli in tre classi in base al valore del consumo annuo. Gli articoli di classe A sono i pochi che rappresentano la maggior parte del valore. Gli articoli di classe C sono i molti che rappresentano una quota minima, mentre la classe B si colloca nel mezzo. Aiuta i team a concentrare gli sforzi di controllo dove si trova la maggior parte del valore economico.

Come si calcola l'analisi ABC in Excel?

Moltiplica le unità annue per il costo unitario di ciascun articolo, quindi dividi per il totale per ottenere la quota di ciascun articolo. Ordina per valore dal più grande al più piccolo, aggiungi un totale parziale delle quote e assegna le classi con una formula come IFS. Le soglie dell'80 e del 95 percento rientrano negli intervalli tipici indicati nel capitolo di MSH.

Quali sono le percentuali per l'analisi ABC?

Una linea guida comune prevede che la classe A contenga dal 10 al 20 percento degli articoli e dal 75 all'80 percento del valore. La classe B contiene un altro 10-20 percento degli articoli e il 15-20 percento del valore. La classe C contiene dal 60 all'80 percento degli articoli e dal 5 al 10 percento del valore.

Qual è la formula per la classificazione ABC in Excel?

Con la percentuale cumulativa nella colonna F e i dati a partire dalla riga 3, utilizza =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Modifica 0.8 e 0.95 in base alle tue soglie. Anche le formule IF nidificate possono svolgere lo stesso compito.

Perché l'analisi ABC è importante?

Mostra dove finisce la maggior parte del denaro destinato all'inventario, consentendo ai team di gestire tali articoli più da vicino. Gli utilizzi tipici includono l'ordinazione più frequente degli articoli di classe A, la negoziazione prioritaria dei loro prezzi e il loro conteggio più frequente. Inoltre, segnala le spese che non corrispondono ai piani previsti.

Fonti: Management Sciences for Health, MDS-3 Capitolo 40: Analyzing and controlling pharmaceutical expenditures · Ravinder e Misra, ABC Analysis for Inventory Management (2014) · Supporto Microsoft, funzione SORT · Supporto Microsoft, funzione IFS · Supporto Microsoft, Creare un grafico di Pareto.