Suivi Achat : Méthodes et Modèles Excel

Cet article explore en détail les méthodes et modèles Excel pour un suivi d’achat efficace.

💡 Dans un environnement commercial dynamique, le suivi des achats est essentiel pour optimiser la gestion des stocks, réduire les coûts, et assurer une satisfaction client élevée. Le suivi des achats, ou procurement tracking en anglais, est une composante critique de la chaîne d’approvisionnement. Il permet de surveiller, analyser, et optimiser chaque étape du processus d’achat, de la commande à la réception des marchandises. La mise en œuvre de méthodes et de modèles appropriés peut considérablement améliorer la performance globale de l’entreprise.

Méthodes de Suivi des Achats

1. Le Système de Gestion des Achats (SGA)

Un SGA est un logiciel intégré qui centralise toutes les informations liées aux achats. Il permet de :

• Suivre les commandes : Enregistrer chaque commande, de la création à la réception.
• Gérer les fournisseurs : Évaluer les performances des fournisseurs en termes de délais, de qualité, et de coût.
• Analyser les dépenses : Identifier les tendances et les opportunités de réduction des coûts.

2. La Méthode des Points de Commande (PDC)

La méthode des points de commande est basée sur le réapprovisionnement automatique des stocks. Un point de commande est défini pour chaque produit ; lorsque le stock atteint ce point, une nouvelle commande est automatiquement déclenchée. Cette méthode permet de :

• Éviter les ruptures de stock : Assurer une disponibilité continue des produits.
• Réduire les coûts de stockage : Minimiser les niveaux de stock tout en répondant à la demande.

Modèle Excel

Le fichier Excel intitulé “Modele_PDC_Enrichi_avec_Graphique.xlsx” a été conçu pour appliquer la Méthode des Points de Commande (PDC) afin de gérer efficacement les stocks de produits. Voici un descriptif détaillé de son contenu et de ses fonctionnalités :

Contenu du Fichier :

Feuille de Données Principale :

• Produit : Liste des produits.
• Consommation Moyenne (CM) : Quantité moyenne consommée par unité de temps.
• Délai de Réapprovisionnement (DR) : Temps nécessaire pour réapprovisionner le stock.
• Stock de Sécurité (SS) : Stock supplémentaire pour couvrir les imprévus.
• Point de Commande (PC) : Niveau de stock à partir duquel une nouvelle commande doit être passée, calculé avec la formule (CM * DR) + SS.
• Stock Actuel : Niveau actuel des stocks (donné à titre d’exemple).
• Prévision de Rupture de Stock (jours) : Temps estimé avant rupture de stock basé sur la consommation moyenne et le stock actuel.

• Un graphique à barres est inclus pour visualiser les Points de Commande (PC) par produit. Le graphique est positionné à droite des données principales.

3. L’Analyse ABC

L’analyse ABC classe les articles en trois catégories selon leur valeur et leur fréquence de consommation :

• Catégorie A : Articles à forte valeur, faible volume (environ 20 % des articles représentant 80 % de la valeur totale des stocks).
• Catégorie B : Articles de valeur et de volume moyens.
• Catégorie C : Articles à faible valeur, fort volume (environ 70 % des articles représentant 10 % de la valeur totale des stocks).

Cette méthode permet de concentrer les efforts de gestion sur les articles les plus critiques pour l’entreprise.

4. La Méthode Juste-à-Temps (JAT)

La méthode JAT vise à réduire les stocks au minimum en synchronisant les commandes avec la production ou la demande. Les avantages incluent :

• Réduction des coûts de stockage : Moins de stocks immobilisés.
• Amélioration de la flexibilité : Répondre plus rapidement aux changements de la demande.

Modèles Utiles pour le Suivi des Achats

1. Le Modèle EOQ (Economic Order Quantity)

Le modèle EOQ détermine la quantité optimale à commander pour minimiser les coûts totaux d’achat et de stockage. Il se base sur des paramètres tels que la demande annuelle, le coût de commande, et le coût de stockage par unité. La formule EOQ est :

où :

• ( D ) est la demande annuelle,
• ( S ) est le coût de commande par commande,
• ( H ) est le coût de stockage par unité par an.

2. Le Modèle de Wilson

Le modèle de Wilson est une extension du modèle EOQ, prenant en compte les variations de la demande et les délais de livraison. Il permet de déterminer le niveau optimal de stock de sécurité et les points de commande adaptés aux variations du marché.

3. Le Modèle de Prévision de la Demande

Ce modèle utilise des techniques statistiques pour prédire les besoins futurs en fonction des données historiques. Les techniques couramment utilisées incluent :

• Lissage exponentiel : Pondération décroissante des données passées.
• Analyse des séries chronologiques : Identification des tendances et des cycles.
• Régression linéaire : Relation entre la demande et d’autres variables explicatives.

Exemples de Tableaux de Suivi des Achats

Les tableaux de suivi des achats permettent de visualiser et de gérer efficacement les informations cruciales relatives au processus d’achat. Voici quelques exemples de tableaux de suivi couramment utilisés.

1. Tableau de Suivi des Commandes auprès des Fournisseurs

Date de Commande	Numéro de Commande	Fournisseur	Produit	Quantité Commandée	Prix Unitaire	Montant Total	Date de Livraison Prévue	Statut de la Commande
01/06/2024	CMD12345	Fournisseur A	Produit X	100	10,00 €	1 000,00 €	10/06/2024	En cours
05/06/2024	CMD12346	Fournisseur B	Produit Y	200	15,00 €	3 000,00 €	15/06/2024	Livré
07/06/2024	CMD12347	Fournisseur C	Produit Z	150	8,00 €	1 200,00 €	12/06/2024	En attente

2. Tableau de Suivi des Stocks

Référence Produit	Nom du Produit	Quantité en Stock	Point de Commande	Quantité à Commander	Fournisseur Principal	Dernière Commande	Date de Réapprovisionnement
P001	Produit X	50	30	100	Fournisseur A	01/06/2024	10/06/2024
P002	Produit Y	120	50	200	Fournisseur B	05/06/2024	15/06/2024
P003	Produit Z	80	40	150	Fournisseur C	07/06/2024	12/06/2024

3. Tableau de Suivi des Fournisseurs

Nom du Fournisseur	Contact	Téléphone	Email	Délai de Livraison Moyen	Fiabilité (Note sur 10)	Commentaires
Fournisseur A	Mr. Dupont	01 23 45 67 89	dupont@fournisseura.com	7 jours	8	Bonne réactivité
Fournisseur B	Mme. Martin	02 34 56 78 90	martin@fournisseurb.com	10 jours	7	Quelques retards
Fournisseur C	Mr. Durand	03 45 67 89 01	durand@fournisseurc.com	5 jours	9	Très fiable

4. Tableau de Suivi des Coûts d’Achat

Période	Montant Total des Achats	Coût de Stockage	Coût de Transport	Coût Total
Janvier	10 000,00 €	1 000,00 €	500,00 €	11 500,00 €
Février	8 000,00 €	800,00 €	400,00 €	9 200,00 €
Mars	12 000,00 €	1 200,00 €	600,00 €	13 800,00 €

Nous allons créer un modèle Excel pour le suivi des coûts d’achat. Le tableau de suivi des coûts d’achat peut inclure les éléments suivants :

1. Date d’Achat
2. Fournisseur
3. Description du Produit
4. Quantité
5. Prix Unitaire
6. Coût Total (qui est le produit de la quantité et du prix unitaire)
7. Catégorie de Produit
8. Mode de Paiement
9. Commentaire

Voici un exemple de structure pour votre tableau de suivi des coûts d’achat :

Date d’Achat	Fournisseur	Description du Produit	Quantité	Prix Unitaire	Coût Total	Catégorie de Produit	Mode de Paiement	Commentaire

Télécharger un modèle automatisé d’un tableau de suivi des coûts d’achat que vous pouvez réutiliser :

5. Tableau de Suivi des Performances des Fournisseurs

Fournisseur	Nombre de Commandes	Livraisons à Temps	Taux de Retard	Taux de Défaut	Note Globale (sur 10)
Fournisseur A	20	18	10%	2%	8.5
Fournisseur B	15	12	20%	5%	7
Fournisseur C	25	24	4%	1%	9.2

6. Tableau de Suivi des Inventaires

Le suivi des inventaires est essentiel pour éviter les ruptures de stock et gérer efficacement les niveaux de stock. Un tableau de suivi des inventaires pourrait inclure les éléments suivants :

Référence Produit	Description du Produit	Quantité Initiale	Quantité Utilisée	Quantité Restante	Date du Dernier Inventaire	Responsable de l’Inventaire
P004	Produit A	500	200	300	01/06/2024	Mr. Leroy
P005	Produit B	300	100	200	01/06/2024	Mme. Lefebvre
P006	Produit C	400	150	250	01/06/2024	Mr. Morel

Pour ⭐ créer un Tableau de Suivi des Inventaires dans Excel ⭐ visant à optimiser les achats, nous devons inclure plusieurs éléments clés pour assurer une gestion efficace des stocks. Voici les étapes pour créer ce tableau :

Colonnes essentielles :

• Référence du produit : Un identifiant unique pour chaque produit.
• Nom du produit : Description du produit.
• Catégorie : Type de produit (par exemple, électronique, vêtements).
• Quantité en stock : Quantité actuelle du produit en stock.
• Seuil de réapprovisionnement : Niveau de stock minimum avant de passer une commande.
• Quantité à commander : Quantité recommandée à commander lorsque le seuil de réapprovisionnement est atteint.
• Date de la dernière commande : Dernière date à laquelle le produit a été commandé.
• Fournisseur : Nom du fournisseur du produit.
• Délai de livraison : Temps nécessaire pour que la commande soit livrée.
• Prix unitaire : Coût unitaire du produit.
• Valeur totale du stock : Calcul automatique basé sur la quantité en stock et le prix unitaire.

Formules et Calculs :

• Utiliser des formules pour automatiser les calculs des valeurs totales de stock.
• Mettre en place des règles de mise en forme conditionnelle pour signaler les produits dont le stock est inférieur au seuil de réapprovisionnement.

Exemples de formules :

• Valeur totale du stock : =E2*J2 (où E2 est la Quantité en stock et J2 est le Prix unitaire).
• Alerte de réapprovisionnement : Utiliser la mise en forme conditionnelle pour colorer en rouge les cellules de Quantité en stock inférieure au Seuil de réapprovisionnement.

Création d’un tableau :

• Convertir les données en tableau Excel pour faciliter le tri, le filtrage et l’analyse.
• Ajouter des segments (slicers) pour une navigation et un filtrage plus intuitifs.

7. Tableau de Suivi des Réclamations et Retours

Gérer les réclamations et les retours de manière efficace est crucial pour maintenir la satisfaction des clients et améliorer les processus d’achat. Voici un exemple de tableau de suivi des réclamations et retours :

Numéro de Réclamation	Date de Réclamation	Client	Produit	Motif de la Réclamation	Action Prise	Statut
REC123	01/06/2024	Client A	Produit X	Produit défectueux	Remplacement	Résolu
REC124	05/06/2024	Client B	Produit Y	Livraison en retard	Remboursement	En cours
REC125	07/06/2024	Client C	Produit Z	Produit non conforme	Enquête	En attente

8. Tableau de Suivi des Économies Réalisées

Suivre les économies réalisées grâce aux stratégies d’achat permet d’évaluer l’efficacité des mesures mises en place. Un tableau de suivi des économies réalisées pourrait inclure :

Période	Montant des Achats Prévu	Montant des Achats Réel	Économie Réalisée	Stratégie Utilisée
T1 2024	50 000,00 €	45 000,00 €	5 000,00 €	Négociation de prix
T2 2024	60 000,00 €	55 000,00 €	5 000,00 €	Achat en gros
T3 2024	55 000,00 €	50 000,00 €	5 000,00 €	Réduction des frais de transport

9. Tableau de Suivi des Fournisseurs Alternatifs

Avoir une liste de fournisseurs alternatifs peut être crucial en cas de problèmes avec les fournisseurs principaux. Voici un exemple de tableau de suivi des fournisseurs alternatifs :

Produit	Fournisseur Principal	Fournisseur Alternatif	Conditions de Livraison	Contact
Produit A	Fournisseur A	Fournisseur X	7 jours	contactX@fournisseurx.com
Produit B	Fournisseur B	Fournisseur Y	10 jours	contactY@fournisseury.com
Produit C	Fournisseur C	Fournisseur Z	5 jours	contactZ@fournisseurz.com

10. Tableau de Suivi des Performances des Catégories de Produits

Analyser les performances par catégorie de produits permet de déterminer les articles les plus et les moins performants. Voici un exemple de tableau de suivi des performances des catégories de produits :

Catégorie	Volume des Ventes	Coût des Ventes	Marge Bénéficiaire	Taux de Rotation des Stocks
Catégorie A	100 000,00 €	60 000,00 €	40 000,00 €	2.5
Catégorie B	80 000,00 €	50 000,00 €	30 000,00 €	3.0
Catégorie C	120 000,00 €	70 000,00 €	50 000,00 €	1.8

Ces tableaux de suivi supplémentaires offrent une vue d’ensemble plus complète de divers aspects du processus d’achat, facilitant ainsi la prise de décision et l’optimisation des performances de l’entreprise.

Ces tableaux permettent une gestion précise et organisée des achats, en offrant une vue d’ensemble claire et en facilitant la prise de décision.

Conclusion

Le suivi des achats est une discipline complexe nécessitant des méthodes et des modèles adaptés pour optimiser les processus d’approvisionnement. L’utilisation d’un système de gestion des achats, combiné à des méthodes comme le point de commande ou l’analyse ABC, et des modèles comme l’EOQ, peut transformer la gestion des achats en un avantage concurrentiel significatif.

L’adoption de ces pratiques permet non seulement de réduire les coûts et d’améliorer l’efficacité, mais aussi d’accroître la satisfaction client en assurant une disponibilité continue des produits. Pour toute entreprise cherchant à se développer dans un marché compétitif, investir dans un suivi d’achat rigoureux et bien structuré est essentiel.