Comment faire la classification  ABC des stocks sur Excel ?

L’analyse ABC est l’une des méthodes de classification les plus utilisées dans la gestion des stocks. Elle est une application directe de la loi de Pareto connue sous l’intitulé de la « Loi des 80-20 ». Pour la petite histoire, le sociologue italien Vilfrédo Pareto aurait énoncé cette loi devenue un principe universel, à la suite d’une étude sociologique sur les inégalités de revenus en Italie. Cette étude avait révélé que 80% des richesses de l’Italie étaient détenues par seulement 20% de la population.

Aujourd’hui, ce principe est utilisé dans presque tous les domaines pour la segmentation des données ou la classification.

En logistique et plus particulièrement en gestion des stocks, on utilise la loi de Pareto pour effectuer la classification ABC.

Qu’est-ce que la classification ABC en gestion des stocks ?

La classification ABC est une méthode de segmentation des produits stockés qui repose sur la loi des 80-20 de pareto. Il ne faut pas la confondre avec la méthode ABC entendue en anglais Activity-Based Costing qui est une méthode comptable de calcul des coûts.

Elle consiste à regrouper les produits stockés  en 3 classes différentes notées successivement A, B et C.

• La Classe A regroupe les 20% des références de produits qui représentent 80% de la valeur de l’inventaire.
• La classe B est celle des 30% des références de produits qui représentent 15% de la valeur de l’inventaire.
• La classe C est celle de la masse, des produits à faible valeur, les 50% des références de produits qui représentent 5% de la valeur de l’inventaire.

Il est à noter que cette analyse peut se faire par rapport à l’espace de stockage ou au volume de ventes au lieu de la valeur de l’inventaire (en gestion d’entrepôts). Aussi dans la pratique, la répartition de chaque classe n’est pas exactement la même que la définition précédente. L’idée générale derrière cette classification est qu’une faible proportion des références (environ 20%) est valorisée à environ 80% (ça peut être inférieur ou supérieur à 80% en fonction de votre activité).

Quel est l’intérêt de la classification ABC en gestion des stocks?

La classification ABC est d’une importance capitale dans la définition de stratégies et tactiques de gestion des stocks et d’entrepôts.

Pour un approvisionneur, l’analyse ABC sur le volume de ventes (en valeur monétaire) ou des sorties de stocks de l’entrepôt permet de mettre en place un système de gestion des stocks adapté à chaque classe de références. Les références de classe A les plus vendues en valeur, donc qui rapportent le plus d’argent à l’entreprise peuvent être gérées avec un système de point de commande ou un système mixte. Le système de révision périodique  convient aux références de classes B et C avec une différence de périodicité (courte période pour la classe B et plus longue pour la classe C). Ainsi on accorde plus de temps et d’énergie aux références les plus génératrices de valeur ajoutée.

Pour le gestionnaire d’entrepôt, l’analyse ABC permet de définir les emplacements des références dans l’entrepôt et les fréquences d’inventaire de ces derniers en fonction de leur classe. Les articles de classe A seront positionnés dans des emplacements proches de la zone de traitement des commandes et d’expéditions pour minimiser les temps de parcours lors des prélèvements avec une fréquence d’inventaire plus courte (1 à 7 jours par exemple). Ceux de classe C seront plus éloignés dans des zones plus difficiles d’accès avec une fréquence d’inventaire plus longue (1 mois ou plus par exemple).

Dans la gestion de son espace de stockage, le gestionnaire d’entrepôt peut utiliser l’analyse ABC pour identifier les produits qui occupent la majorité de l’espace de stockage et prendre de décisions pour optimiser l’utilisation de l’espace. Il peut par exemple limiter l’espace pour certains produits à faible valeur qui occupent trop d’espace pour augmenter la valeur au mètre cube, ou identifier les produits à forte valeur qui occupent très peu d’espace et mettre en place une politique de sécurisation de ces derniers.

Puisque vous connaissez maintenant à quoi cette méthode peut vous servir, découvrons ci-dessous comment l’appliquer sur Excel.

Comment faire l’analyse ABC des stocks dans un classeur Excel ?

Pour faire l’analyse ABC, vous avez besoin des données sur votre stock. Une extraction en format csv contenant les informations suivantes est suffisante :

• Le SKU ou product_ID : le code ou identifiant du produit.
• Le nom ou la désignation du produit
• Le Volume unitaire du produit
• Le cout unitaire du produit
• Le prix de vente unitaire
• Le stock disponible
• La quantité vendue sur une période de votre choix.

Une fois le fichier CSV extrait de votre système, ouvrez-le sur Excel et traitez-le pour avoir un fichier exploitable. Pour une application directe de cette méthode, vous pouvez télécharger ici le fichier CSV que nous allons traiter dans cet article.

Rendre le fichier CSV exploitable

 Par définition, un fichier CSV est un fichier de données en colonnes séparées par des virgules ou un autre séparateur comme le point-virgule ou la tabulation. Voici comment se présente un fichier CSV brut sur Excel : Vous remarquerez que toutes les données sont dans une seule colonne Excel, la colonne A. Pour rendre ces données exploitables, il faut les répartir sur plusieurs colonnes Excel.

Pour ce faire, il faut se rendre dans l’onglet Données, puis dans le groupe de fonctions Outils de Données, cliquez sur Convertir.

Dans la fenêtre qui apparait, sélectionnez l’option Délimité puis cliquez sur Suivant.

Ensuite choisissez le séparateur de vos données. Vous pouvez le retrouver facilement en identifiant le séparateur des colonnes de l’entête. Dans notre cas, c’est la virgule.  Une fois le séparateur identifié et choisi, cliquez sur Suivant.

Sur la dernière étape, laissez les paramètres par défaut et cliquez sur Terminer pour valider. Vous obtenez une table données rangées en colonnes.

C’est ce fichier que nous allons utiliser pour faire notre classification ABC.

Comment procéder ?

Voici les différentes tâches à réaliser pour bien faire la classification :

• Déterminer la valeur totale de chaque référence en stock (valorisation)
• Ranger les références par ordre décroissant de leur valeur en stock
• Calculer le total de la valeur de l’inventaire
• Déterminer la valeur relative (en pourcentage) de chaque référence
• Déterminer le pourcentage cumulé de chaque article en stock
• Classer les références en classes A, B et c suivant leur pourcentage cumulé : A pour les références allant de 0 à 80% non inclus, B pour celles allant de 80% à 95% et C pour le reste des références.
• Déterminer pour chaque classe le ratio nombre de références de la classe / nombre total de références.
• Réajuster si nécessaire la classification en fonction du ratio de références souhaité pour chaque classe.

Déterminons la valeur totale de chaque référence.

Pour chaque article, il faut calculer la valeur du stock en utilisant la formule :

Sur Excel, voici ce qu’on a:

Le résultat final pour tous les articles se présente comme suit :

Ranger les valeurs en stock par ordre décroissant

Pour ce faire, il faut créer d’abord un filtre sur les données. Sélectionnez toutes les colonnes puis allez dans l’onglet Accueil, sous le groupe de fonctions Edition, cliquez sur Trier et Filtrer et ensuite sur Filtrer.

Dans la colonne de valeur en stock ou valorisation, cliquez sur le bouton de filtre puis cliquez sur Trier du plus grand au plus petit.

Vous constaterez un changement de l’ordre des articles et dans la colonne « Valorisation » les valeurs sont bien rangés du haut vers le bas de la plus grande à la plus petite valeur.

Calculer le total de la valeur de l’inventaire

Le montant total de l’inventaire se calcule en faisant la somme des valeurs du stock de chaque article. Sur Excel, nous allons utiliser la fonction SOMME() pour le calculer.  Pour ce faire, ajoutons d’abord une ligne en haut de notre tableur Excel. Placez le curseur sur la ligne 1 du tableur et faites un clic droit sur votre souris. Dans le menu-options qui apparait cliquez sur Insertion.

Dans la cellule située au-dessus de la colonne « Valorisation » mettez la formule : « =SOMME(F3:F32) ». Vous obtenez le montant de l’inventaire qui est de 3 134 637 dans notre cas. Notez qu’il faudra prendre en compte la dernière ligne de votre base de données dans votre formule somme() pour trouver la bonne valeur. Par exemple, si votre base de données s’arrête à la ligne 1500 et que les valeurs de stock sont dans la colonne J, votre formule sera : « =SOMME(J3 :J1500) ».

Déterminer la valeur relative (en pourcentage) de chaque référence

Maintenant, nous allons créer une nouvelle colonne dans laquelle nous allons calculer la valeur du stock de chaque article en pourcentage du total calculé précédemment, cette valeur nous l’appellerons «valeur relative ». Pour la calculer on proocède comme suit :

Sur Excel, pour le premier article on a la formule suivante : « =F3/$F$1 »

Pour faire le calcul pour les autres articles, il suffira d’incrémenter la formule jusqu’à la fin de la base de données. Voici ce qu’on trouve à la fin.

Pour mettre les valeurs en pourcentage, il faut sélectionner la colonne « valeur relative » et dans l’onglet Accueil, sous le groupe Nombre, cliquez sur le signe %.

Déterminer le pourcentage cumulé de chaque article en stock

Pour déterminer le pourcentage cumulé de chaque article, on procède comme suit :

• Pour le premier article, le pourcentage cumulé est égal à la valeur relative (%). Donc la formule Excel dans notre cas est : « =G3 ».

• Pour tous les autres articles, le pourcentage cumulé de chaque article est égal au pourcentage cumulé de l’article qui le précède auquel on ajoute la valeur relative de l’article en question. La formule Excel pour l’article de la seconde ligne est : « =G3+H4 ». Il faut ensuite incrémenter cette formule pour les autres articles de la base de données.

Passons maintenant à la classification ABC proprement dite.

Classification ABC

Cette étape consiste à attribuer les classes A, B ou C à chaque article en fonction du pourcentage cumulé de ce dernier. Pour un pourcentage cumulé inférieur à 80%, on attribue par défaut la classe A, entre 80% et 95% on attribue la classe B, et au-delà de 95% on attribue la classe C.

On trouve donc la classification suivante dans notre exemple :

Déterminer le ratio de références par classe

Le ratio de références pour chaque classe est égal au nombre de références de la classe divisé par le nombre total de références :

Dans notre exemple on a :

Pour la classe A : 6/30 =20%

Pour la classe B : 10/30 = 33,33%

Pour la classe B : 14/30 = 46,67%.

Ainsi, on peut déjà conclure que : 20% des articles de notre base représentent 76,38% de la valeur de notre stock (Classe A), 33,33% des articles représentent 18,52% (94,89% – 76,38%) de la valeur de notre stock et 46,67% des articles représentent 5,1% de la valeur de notre stock.

Réajustement optionnel

Maintenant que vous connaissez la répartition de la valeur de votre stock par classe d’articles, vous pouvez, en fonction de votre politique de gestion, réajuster chaque classe d’article pour obtenir un taux satisfaisant à vos impératifs. Par Exemple, vous pouvez décider de réduire la classe A à 16% des articles qui immobilisent le plus votre fond de roulement. Puis les 30% suivants en classe B et le reste en classe C. Votre nouvelle classification sera donc comme suit :  

Classe A : 16,67% des Articles pour 70,8% de la valeur en stock

Classe B : 30% des articles pour 22,45% (93,25% – 70,8%) de la valeur en stock.

Classe C : 53,33% des articles pour 6,75% de la valeur en stock.

En conclusion, la classification ABC est très utile dans la vie de tout gestionnaire digne de ce nom. Elle vous permettra d’orienter vos actions sur des tâches qui apportent le plus de valeur à votre entreprise.

Nous espérons que cet article a été suffisamment clair pour vous montrer une façon simple d’appliquer cette méthode de classification. N’hésitez pas à laissez un commentaire ou à nous contacter pour plus d’éclaircissement ou pour des suggestions.