Comment réaliser une analyse ABC sur Excel : 5 étapes simples

L'analyse ABC classe les articles de stock en trois catégories selon la valeur de leur consommation annuelle. Les articles de classe A sont les rares qui représentent la majeure partie des dépenses. Les articles de classe C sont les plus nombreux mais ne représentent qu'une faible part, et la classe B se situe entre les deux. Dans Excel, vous pouvez réaliser cette analyse avec un seul tableau : valeur annuelle, part du total, cumul progressif et une formule qui attribue chaque classe.

Ce guide explique la signification de ces classes, les cinq étapes à suivre dans Excel, un exemple concret et la manière de représenter graphiquement le résultat. Il aborde également le choix de vos seuils et les actions à mener pour chaque classe une fois l'analyse terminée.

Qu'est-ce que l'analyse ABC ?

L'analyse ABC est une méthode permettant de déterminer quels articles méritent le plus d'attention. Elle repose sur un constat simple : une faible proportion d'articles représente une part importante des dépenses.

Un chapitre de 2012 sur l'analyse et le contrôle des dépenses pharmaceutiques, publié par Management Sciences for Health (MSH), la décrit clairement. Il note qu'« un nombre relativement restreint d'articles représente la majeure partie de la valeur de la consommation annuelle ». Il ajoute : « L'analyse de ce phénomène est connue sous le nom d'analyse de Pareto ou, plus communément, d'analyse ABC. »

Le même chapitre explique que les articles « peuvent être classés en trois catégories (A, B et C) en fonction de la valeur de leur consommation annuelle ». La méthode reste la même, que vous stockiez des médicaments, des pièces de rechange ou des produits de vente au détail.

Un point essentiel est souvent négligé : ces classes ne sont pas des étiquettes permanentes. MSH souligne que « si les habitudes de consommation changent, l'article peut être classé dans une catégorie différente lors de la prochaine analyse ABC ». L'analyse ABC est donc plus efficace lorsqu'elle est réalisée de manière périodique, plutôt que comme un projet ponctuel.

Que signifient les classes A, B et C ?

Le chapitre de MSH donne des fourchettes typiques pour chaque classe :

Classe	Part des articles	Part de la valeur annuelle	Signification habituelle
A	10 à 20 pour cent	75 à 80 pour cent	Peu d'articles, l'essentiel des dépenses
B	10 à 20 pour cent	15 à 20 pour cent	Un groupe intermédiaire
C	60 à 80 pour cent	5 à 10 pour cent	Beaucoup d'articles, une faible part des dépenses

Il s'agit de fourchettes typiques et non de règles absolues. MSH précise que « ces limites sont relativement flexibles ». Son exemple fixe plutôt la classe A aux articles qui représentent 70 pour cent des dépenses cumulées.

La valeur qui détermine les classes est la valeur de consommation annuelle : les unités utilisées en un an multipliées par le coût unitaire. Un article bon marché consommé en très grande quantité peut se retrouver en classe A. À l'inverse, un article coûteux utilisé une seule fois par an peut être classé en catégorie C.

Un article de 2014 paru dans l'American Journal of Business Education remet en question l'utilisation de la seule valeur financière. Il soutient que les manuels scolaires « se concentrent sur le volume financier comme unique critère » et recommande d'ajouter d'autres critères. Pour une première approche, la valeur reste la méthode employée dans le chapitre de MSH.

Ce dont vous avez besoin avant de commencer

L'analyse ABC dans Excel ne nécessite que quelques colonnes par article :

• Nom de l'article ou SKU. Une ligne par article.
• Unités annuelles utilisées ou achetées. Utilisez la même période de 12 mois pour chaque article.
• Coût unitaire. Le coût d'une unité, exprimé dans la même unité de mesure que vos quantités.

MSH insiste sur la cohérence de la période : « Assurez-vous d'utiliser la même période d'analyse pour tous les articles afin d'éviter des comparaisons erronées. » L'organisation conseille également d'utiliser la même unité de base pour le coût et la quantité, comme un comprimé ou une boîte individuelle, plutôt que de mélanger différents formats de conditionnement.

Si vos données proviennent d'un système de gestion des stocks ou d'achats, exportez-les au format CSV ou Excel. Supprimez les articles n'ayant enregistré aucune activité au cours de la période, ou conservez-les en sachant qu'ils seront classés en catégorie C.

Comment réaliser une analyse ABC dans Excel

Les cinq étapes ci-dessous suivent la méthode du chapitre de MSH, adaptée aux formules Excel. Notre exemple place un titre à la ligne 1, des en-têtes à la ligne 2 et 10 articles dans les lignes 3 à 12. Les colonnes A, B et C contiennent le nom de l'article, les unités annuelles et le coût unitaire.

Étape 1 : Lister les articles, les unités et le coût unitaire

Saisissez ou collez une ligne par article contenant son nom, ses unités annuelles et son coût unitaire. Ajoutez des en-têtes à la ligne 2 pour faciliter le tri ultérieur du tableau.

Vérifiez vos données avant de poursuivre. Recherchez les coûts manquants, les quantités négatives et les doublons de SKU, car chacun de ces éléments faossera les totaux. Un filtre rapide sur chaque colonne permet généralement de les identifier.

Si plusieurs achats d'un même article ont été effectués à des prix différents, utilisez un coût unique et cohérent. MSH indique qu'« une moyenne pondérée ou une moyenne FIFO » sont les alternatives les plus précises lorsque le coût unitaire réel est difficile à suivre.

Étape 2 : Calculer la valeur annuelle et sa part du total

Dans la colonne D, multipliez les unités par le coût pour obtenir la valeur annuelle de chaque article. En D3, saisissez =B3*C3 et étirez la formule vers le bas.

Dans la colonne E, divisez chaque valeur par le total de toutes les valeurs pour obtenir sa part. En E3, saisissez =D3/SUM($D$3:$D$12) et étirez vers le bas. Les symboles dollar maintiennent la plage du total fixe lors de la copie de la formule. Formatez la colonne E en pourcentage avec deux décimales.

MSH recommande cette précision pour une bonne raison. Selon ses termes, « plusieurs articles peuvent avoir des valeurs très proches et beaucoup d'entre eux peuvent représenter moins de 1 pour cent de la valeur totale ».

Étape 3 : Trier les articles par valeur, de la plus grande à la plus petite

Sélectionnez l'ensemble du tableau, y compris les en-têtes, et triez-le selon la colonne D, du plus grand au plus petit. Dans Excel, allez dans l'onglet Données, puis Trier, en sélectionnant la colonne D et l'ordre Du plus grand au plus petit.

Si vous préférez utiliser une formule, la fonction SORT renvoie une copie triée. La syntaxe de Microsoft est =SORT(array,[sort_index],[sort_order],[by_col]), où un ordre de tri de -1 signifie un tri décroissant. Pour ce tableau, =SORT(A3:E12,4,-1) effectue le tri selon la quatrième colonne, de la valeur la plus élevée à la plus faible.

Après cette étape, l'article ayant la valeur annuelle la plus élevée se trouve en haut du tableau. C'est cet ordre qui donne tout son sens au cumul progressif de l'étape suivante.

Étape 4 : Ajouter le pourcentage cumulé

Dans la colonne F, ajoutez un cumul progressif des parts. En F3, saisissez =SUM($E$3:E3) et étirez vers le bas. La première partie de la plage reste fixe, tandis que la seconde s'étend d'une ligne à chaque fois.

La dernière ligne doit afficher 100 pour cent. Si ce n'est pas le cas, vérifiez s'il y a des cellules vides ou des valeurs textuelles dans les colonnes D et E.

Cette colonne est le cœur de l'analyse ABC. Elle indique quelle part de la valeur totale représentent, ensemble, les articles situés au-dessus de chaque ligne.

Étape 5 : Attribuer les classes A, B et C

Dans la colonne G, utilisez une formule pour attribuer une catégorie à chaque article. Avec des seuils de 80 et 95 pour cent, saisissez ceci en G3 et étirez vers le bas :

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

La fonction IFS vérifie chaque condition dans l'ordre et renvoie la première correspondance. L'exemple de Microsoft utilise le même modèle, avec TRUE comme condition finale par défaut. Les articles représentant jusqu'à 80 pour cent du cumul deviennent des articles de classe A, ceux allant jusqu'à 95 pour cent deviennent des articles de classe B, et le reste est classé en C.

Enfin, comptez le nombre d'articles de chaque classe avec =COUNTIF(G3:G12,"A"), et faites de même pour B et C. Comparez ces effectifs avec les fourchettes typiques mentionnées plus haut. Ajustez les seuils si la classe A est beaucoup trop grande ou trop petite pour être gérée efficacement par votre équipe.

Un exemple concret

Voici un tableau illustratif pour 10 articles, déjà triés par valeur annuelle. Les chiffres sont fictifs et ne proviennent pas d'une entreprise réelle.

Article	Unités annuelles	Coût unitaire	Valeur annuelle	Part	Cumul	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

La valeur annuelle totale est de $150,000. Trois articles, soit 30 pour cent de la liste, représentent 73.33 pour cent de la valeur et se retrouvent dans la classe A. Quatre articles entrent dans la classe B, et les trois derniers, représentant 6 pour cent de la valeur, appartiennent à la classe C.

Deux détails se détachent. Le SKU-04 compte de loin le plus grand nombre d'unités, mais son faible coût le place dans la classe B. De plus, avec seulement 10 articles, la répartition des classes ne correspondra pas aux fourchettes typiques, ce qui est tout à fait normal pour une liste aussi courte.

Comment représenter graphiquement le résultat

Un graphique permet de présenter facilement cette tendance lors d'une réunion. MSH suggère de tracer le pourcentage cumulé en fonction du numéro de l'article, ce qui donne la célèbre courbe ABC.

Excel intègre un graphique conçu à cet effet. Microsoft décrit le diagramme de Pareto comme un graphique qui « contient à la fois des colonnes triées par ordre décroissant et une ligne représentant le pourcentage total cumulé ». Pour en créer un, sélectionnez les noms d'articles et les valeurs annuelles, puis choisissez Insertion, Insérer un graphique statistique, et Pareto.

Ajoutez deux lignes horizontales ou des étiquettes au niveau de vos seuils, par exemple à 80 et 95 pour cent, afin que les lecteurs puissent visualiser où commence chaque classe. Notre guide pour créer un diagramme de Pareto avec l'IA aborde la création de ce graphique plus en détail.

Choisir vos seuils

Il n'existe pas de seuil unique idéal. MSH explique que ce choix « dépend de la manière dont le volume et la valeur sont répartis entre les articles de la liste ». Il dépend également de « la façon dont les résultats de l'analyse ABC vont être utilisés ».

La capacité de gestion constitue la limite pratique. MSH le formule directement : « l'attribution des articles à la classe A doit être basée sur la capacité de gestion ». Si votre équipe ne peut examiner de près que 50 articles par mois, une classe A contenant 300 articles perd tout son intérêt.

Quelques approches courantes :

• Seuils basés sur la valeur. Classe A jusqu'à 80 pour cent de la valeur, classe B jusqu'à 95 pour cent, et classe C pour le reste. C'est la méthode utilisée ci-dessus.
• Seuils basés sur le nombre d'articles. Les premiers 20 pour cent des articles en valeur deviennent la classe A, les 30 pour cent suivants la classe B, et le reste la classe C.
• Listes fixes. Certaines équipes définissent la classe A comme les 25 ou 50 premiers articles, quelle que soit leur part de valeur.

Quelle que soit l'option choisie, notez-la et appliquez-la systématiquement. Comparer les classes de ce trimestre avec celles du trimestre précédent n'a de sens que si les seuils restent identiques.

Que faire de chaque classe ?

L'objectif de l'analyse ABC est de concentrer les efforts là où se trouvent les enjeux financiers. Le chapitre de MSH énumère plusieurs façons d'exploiter ces résultats :

• Commander les articles de classe A plus souvent. MSH indique que commander les articles de classe A « plus souvent et en plus petites quantités devrait permettre de réduire les coûts de détention des stocks ».
• Négocier en priorité les prix de la classe A. « Les réductions de prix pour les articles classés comme produits A dans l'analyse peuvent générer des économies significatives », selon le chapitre.
• Compter les stocks de classe A plus fréquemment. MSH note que « les inventaires tournants doivent être guidés par l'analyse ABC, avec des comptages plus fréquents pour les articles de classe A ».
• Surveiller le statut des commandes de classe A. Une rupture de stock imprévue sur un article de classe A peut entraîner des achats d'urgence très coûteux.

Les articles de classe C peuvent être soumis à des règles plus simples, comme des commandes plus importantes mais moins fréquentes, et des inventaires plus espacés. La classe B se situe entre les deux. Si les articles à rotation lente vous préoccupent, notre guide pour repérer les stocks à rotation lente complétera parfaitement cette analyse.

Aller plus vite grâce à l'IA

Les étapes dans Excel ne prennent que quelques minutes une fois les données nettoyées. En revanche, nettoyer l'export et répéter cette tâche chaque trimestre s'avère bien plus long.

Un espace de travail basé sur l'IA peut effectuer les calculs et le tri en une seule requête. Importez votre export de stocks ou d'achats dans Powerdrill Bloom et demandez une analyse ABC avec vos propres seuils en langage naturel. Demandez la valeur annuelle, la part, le pourcentage cumulé et la classe pour chaque article, ainsi qu'un diagramme de Pareto.

Vérifiez ensuite le résultat comme vous le feriez pour n'importe quel feuille de calcul. Confirmez la valeur annuelle totale par rapport à votre propre calcul, et effectuez un contrôle ponctuel sur deux articles de chaque classe. Notre page sur l'assistant IA pour Excel décrit ce type de travail sur tableur plus en détail. Pour une vue d'ensemble des outils de prévision, consultez notre sélection d'outils d'IA pour la prévision des stocks et de la demande.

Les erreurs courantes à éviter

• Mélanger les périodes. Prendre douze mois pour un article et six pour un autre rend les parts calculées insignifiantes.
• Utiliser les quantités plutôt que la valeur. Les classes dépendent du produit des unités par leur coût, et non des seules quantités.
• Oublier de trier avant de calculer le cumul. Un pourcentage cumulé calculé sur une liste non triée placera les articles dans la mauvaise classe.
• Considérer les classes comme permanentes. Relancez l'analyse chaque trimestre ou chaque année, car les articles évoluent d'une classe à l'autre.
• Choisir des seuils qui ignorent votre capacité de gestion. Une liste de classe A trop longue à gérer de près ne bénéficiera pas d'une attention plus soutenue que la classe B.
• Ignorer les articles bon marché mais critiques. Un article de faible valeur peut tout de même bloquer l'activité en cas de rupture. Le chapitre de MSH associe l'analyse ABC à une évaluation distincte des articles vitaux, essentiels et non essentiels.

Si votre liste d'articles provient d'un export désordonné, vous pouvez essayer Powerdrill Bloom pour générer votre premier tableau et graphique ABC.

Questions fréquemment posées

Qu'est-ce que l'analyse ABC dans la gestion des stocks ?

L'analyse ABC classe les articles en trois catégories selon leur valeur de consommation annuelle. Les articles de classe A sont les rares articles qui représentent la majeure partie de la valeur. Les articles de classe C sont les plus nombreux mais ne représentent qu'une faible valeur, et la classe B se situe entre les deux. Elle aide les équipes à concentrer leurs efforts de contrôle là où les enjeux financiers sont les plus importants.

Comment calculer l'analyse ABC dans Excel ?

Multipliez les unités annuelles par le coût unitaire pour chaque article, puis divisez par le total pour obtenir la part de chaque article. Triez par valeur de la plus grande à la plus petite, ajoutez un cumul progressif des parts, puis attribuez les classes à l'aide d'une formule comme IFS. Des seuils de 80 et 95 pour cent s'inscrivent dans les fourchettes typiques présentées dans le chapitre de MSH.

Quels sont les pourcentages de l'analyse ABC ?

Une règle générale veut que la classe A regroupe 10 à 20 pour cent des articles et 75 à 80 pour cent de la valeur. La classe B comprend 10 à 20 pour cent des articles et 15 à 20 pour cent de la valeur. Enfin, la classe C rassemble 60 à 80 pour cent des articles et 5 à 10 pour cent de la valeur.

Quelle est la formule de classification ABC dans Excel ?

Avec le pourcentage cumulé dans la colonne F et des données commençant à la ligne 3, utilisez =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Modifiez 0.8 et 0.95 pour les adapter à vos propres seuils. Des formules IF imbriquées peuvent également faire l'affaire.

Pourquoi l'analyse ABC est-elle importante ?

Elle montre où est consacrée la majeure partie du budget de stockage, permettant ainsi aux équipes de gérer ces articles de plus près. Les applications courantes consistent à commander les articles de classe A plus souvent, à négocier leurs prix en priorité et à les inventorier plus fréquemment. Elle permet également de signaler les dépenses non conformes aux prévisions.

Sources : Management Sciences for Health, MDS-3 Chapitre 40 : Analyse et contrôle des dépenses pharmaceutiques · Ravinder et Misra, Analyse ABC pour la gestion des stocks (2014) · Support Microsoft, fonction SORT · Support Microsoft, fonction IFS · Support Microsoft, Créer un diagramme de Pareto.