Méthode ABC sur Excel : Guide Complet et Analyse Pareto sur 500 produits

6 minutes de lecture

Découvrez comment appliquer la méthode ABC en gestion de stock sur Excel en utilisant l’analyse de Pareto pour classer vos produits et optimiser vos niveaux d’inventaire. Ce guide pratique présente les étapes nécessaires pour réaliser une courbe de Pareto et gérer efficacement le stock avec des classifications ABC.

Découvrez le tutoriel vidéo complet sur la méthode ABC pour la classification de stock sur Excel ci-dessous :

Télécharger l’Excel méthode ABC ICI

On a toujours l’impression qu’il faut être un spécialiste de la Data pour pouvoir analyser sa chaîne logistique et améliorer sa gestion des stocks. Il existe en effet des méthodes très avancées qui nécessitent une expertise particulière. Généralement, elles impliquent le déploiement de projets coûteux, qui peuvent s’avérer bénéfiques si le contexte s’y prête et si suffisamment de ressources sont allouées au projet.

Cependant, il existe des méthodes très simples et abordables pour évaluer et mieux gérer ses stocks. Certaines restent peu (ou mal) utilisées malgré leur efficacité démontrée. L’une d’entre elles est la méthode ABC, un incontournable de la gestion de stock.

Principe de Pareto

Principe de Pareto
Vilfredo Pareto

 Vilfredo Pareto, économiste italien du XIXᵉ siècle.

  • 20% de la population possède 80% de la richesse.
  • Vous passez 80% de votre temps avec 20% de vos amis.
  • 20% de vos clients représentent 80% de votre CA.
  • 20% de vos fournisseurs représentent 80% de vos achats.
  • 20% de vos produits représentent 80% de votre CA.
  • 20% de vos produits représentent 80% de votre stock.

La loi de Pareto consiste donc simplement à se concentrer sur l’essentiel pour obtenir les meilleurs résultats. Dans la gestion des stocks, il s’agit de classer les quelques articles qui représentent la majeure partie de votre activité (produits A), et les nombreux articles qui représentent une petite partie de votre activité (produits C). Les produits B se situent entre les deux.

Courbe de Pareto – analyse ABC

Nous pouvons appliquer la logique ABC pour les ventes passées et futures, que ce soit en valeurs ou en quantités.

Il en va de même pour la valeur de vos stocks.

abc analysis abc classification pareto law methode abc
Courbe de Pareto – Méthode ABC pour vos ventes

L’idée est de cibler différents niveaux de service en fonction de la classification des produits, ce qui aura un impact considérable sur votre stratégie de gestion des stocks. Pour les articles qui sont les principaux moteurs de votre activité, vous pouvez rechercher un niveau de service proche de 100% afin de maximiser les bénéfices des ventes et la satisfaction client. Pour les codes C, vous pouvez viser un niveau de service beaucoup plus bas, afin de réduire votre stock sur une large gamme de produits.

abc analysis service level inventory management methode abc

Voyons un exemple pratique.

Admettons que vous travailliez dans le secteur de la mode. Vous avez 3 types de tee shirt dans votre catalogue de produits : l’un est noir, un autre est orange et le dernier a des motifs originaux. Votre gestion de stock dépendra d’une priorisation spécifique de ces produits.

Le tee shirt noir a les plus gros volumes de ventes, c’est donc un code A. Votre article B est le tee shirt orange, car il est moins vendu. Le dernier tee shirt ne sera acheté que par quelques personnes ayant des critères d’achat spécifiques : il s’agit donc de votre code C.

Vous allez ensuite fixer différents taux de service cibles en fonction de cette classification ABC. Vous allez maintenir plus de stock pour le tee shirt noir afin d’assurer un taux de service maximal. Et vous accepterez plus de risque de rupture de stock pour le tee-shirt original, afin d’optimiser vos niveaux de stock.

Ces taux de service seront les données d’entrée pour calculer vos stocks de sécurité, définir votre stratégie de réapprovisionnement, etc.

Méthode ABC avec Excel : un exemple en 5 étapes

Voici un tutoriel étape par étape de la méthode ABC réalisé avec mon modèle Excel.

1) Collecte de données en magasin

En analyse de données pour la gestion de stock, l’une des parties les plus critiques est d’obtenir des données à la fois fiables et pertinentes. Heureusement, pour la classification ABC, vous n’aurez besoin que d’informations très basiques.

a) Inclure l’ensemble du catalogue produit

  • Actif : les articles que vous vendez actuellement.
  • Fin de série : les produits que vous ne commandez/ne fabriquez plus. Il est important d’avoir une vision claire de ces produits : pour estimer le stock obsolète et être sûr de ne pas les commander à nouveau.
  • Nouveau produit : les produits que vous allez vendre dans le futur.

b) Classification en quantité ou en valeur ?

Dans un premier temps, récupérez vos historiques et prévisions de ventes et classez-les par mois et par références. Je vous recommande vivement d’utiliser le volume en valeur plutôt qu’en quantité : si vous vendez des millions de sacs en plastique pour votre entreprise, ils représentent une quantité importante, mais n’ont pas ou peu de valeur.

c) Historique des ventes ou prévisions de ventes ? Quelle période utiliser ?

Prenez à la fois des valeurs passées et futures pour votre classification. Par exemple, je sélectionne généralement 3 mois d’historique et 9 mois de données prévisionnelles.

Premièrement, cela donne un bon équilibre entre les données passées et futures. Les données futures sont nécessaires, car vous voulez piloter votre gestion des stocks en fonction de ce qui se passera à moyen terme. Et l’historique vient compenser les erreurs potentielles des prévisions.

Ensuite, prendre jusqu’à 9 mois de prévisions est un bon moyen de garder un œil sur les nouveaux produits en lancement.

Si vous n’avez pas de prévisions, vous pouvez toujours prendre l’historique des 6 ou 12 derniers mois.

Ici, l’article 471 sera introduit en février. Nous devons l’inclure dans la classification ABC

2) Trier les produits

Nous allons trier nos produits de manière décroissante en termes de chiffres d’affaires sur les 12 mois sélectionnés.

Insérez un tableau croisé dynamique en sélectionnant l’ensemble des données :

  • Sélectionnez les codes de l’article, la description de l’article en tant que Lignes et les revenus sur 12 mois en tant que Valeurs.

  • Via l’option Création ou Design du ruban du Tableau Croisé Dynamique, affichez les valeurs sous forme de tableau et supprimez tous les totaux pour obtenir un résultat clair et facile à lire.
  • Cliquez sur l’option de tri supplémentaire via le bouton filtre des Codes Articles, et sélectionnez le tri descendant sur la somme des revenus sur 12 mois.
  • Ajoutez une colonne de classement après le tri, avec une simple suite 1,2,3….

3) Calculer les pourcentages cumulés de l’article et du chiffre d’affaires

Après avoir trié notre portefeuille, nous devons calculer les pourcentages cumulés des produits et du chiffre d’affaires.

Pour chaque article :

  • On divise le classement de l’article par le nombre total d’articles :

  • On ajoute le %CA de l’article et le %CA cumulé de l’article précédent :

4) Classification ABC de chaque article

Mettez en place les codes ABC: Dans la dernière colonne, insérez la formule « SI ».

  • Si le pourcentage de CA cumulé < 40%, alors c’est un code A
  • Si le pourcentage de CA cumulé > 80%, alors c’est un code C
  • Si ce n’est ni l’un ni l’autre, c’est un code B.

Les détails de cette formule sont expliqués dans la vidéo et peuvent varier en fonction de vos données.

Pour plus de visibilité, vous pouvez inclure des couleurs de formatage conditionnel pour chaque code différent.

5) Créez votre courbe de Pareto

Pour afficher le graphe, sélectionnez votre tableau et insérez la courbe via l’onglet d’insertion :

  • % Articles pour l’axe des X
  • % CA pour l’axe des Y

Attention, votre Excel doit être actualisé au moins une fois par semaine, notamment si vous travaillez en approvisionnement. Une erreur commune est de ne pas passer assez de temps sur cette classification ABC qui représente pourtant un gain de temps et d’argent considérable.

Télécharger l’Excel de la Méthode ABC

Vous pouvez télécharger l’Excel Méthode ABC présenté ci-dessus avec 500 articles sur ce lien

Points Clefs à retenir

La méthode ABC est une technique de classification de stock permettant de catégoriser les produits en fonction de leur importance et de leur contribution à la valeur totale du stock.

La loi de Pareto, ou règle des 80/20, appliquée à la méthode ABC, stipule que 20 % des articles génèrent environ 80 % de la valeur totale du stock.

Classer les produits en A, B et C permet de mieux gérer les ressources en se concentrant sur les articles les plus critiques (catégorie A), et en surveillant les moins prioritaires (B et C) de manière différente.

Oui, il est tout à fait possible de faire une analyse ABC sur Excel en utilisant des formules et des fonctions pour trier et classer les produits en fonction de leur importance.

La courbe de Pareto est un outil visuel pour observer la répartition des produits selon leur contribution ; elle aide à identifier les articles prioritaires dans la gestion de stock.

Aller plus loin : classification ABC XYZ

L’un des défauts de la méthode ABC est qu’elle ne prend pas en compte la volatilité des ventes ou de la consommation. Il faut alors se tourner vers une classification ABC XYZ.

La classification ABC XYZ fournit ainsi un cadre à l’élaboration et le perfectionnement des stratégies de gestion des stocks en classifiant les produits en fonction de leur volume de ventes (ABC) mais aussi en fonction de leurs incertitudes (XYZ). Votre stratégie d’approvisionnement sera ensuite différente en fonction de cette nouvelle classification pour optimiser votre stock et votre taux de service client.

Abc-xyz-matrice-principe

Vous pouvez consulter notre article sur la classification ABC XYZ ici ainsi que notre article sur le Stock de sécurité.

MasterClass Gestion de Stock Gratuite

Boostez vos compétences en gestion de stock

Rejoignez ma MasterClass gratuite  :“Ne plus subir les ruptures et surstocks.”

  • La méthode pour faire face à l’Incertitude de la Demande
  • Comment intégrer la Fiabilité de vos Fournisseurs (3 exemples)
  • Les 13 Paramètres que j’utilise pour optimiser mes stocks (dont 7 indispensables)
  • Comment Automatiser sa gestion de stock sur Excel ou ERP (et arrêter de travailler comme un pompier)
  • Des études de cas de grands groupes : Zara, H&M, Amazon….
  • Fichier Excel Offert avec exemples réels

Prêt à booster vos compétences ?

Découvrez nos formations

Voir les formations

SCM Foundation Formation
SCM Foundation™️

Découvrir la formation

Vignette Formation Excel en ligne
Excel Expert™️

Découvrir la formation

Vignette Formation Gestion de Stock en ligne
Expert Gestion de Stock™️

Découvrir la formation

Vignette Formation SCM Analytics en ligne

SCM Analytics™️

Découvrir la formation

Vignette Formation Forecasting en ligne
Forecasting Expert™️

Découvrir la formation

Retour en haut