Suivi Achat : Méthodes et Modèles Excel

Cet article explore en détail les méthodes et modèles Excel pour un suivi 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.

Tableau intégral du pilotage achat en entreprise

Ce tableau sert de carte complète du suivi achat. Il relie chaque étape du processus à son KPI, son risque principal, son document de contrôle, l’action corrective à engager et le modèle Excel le plus utile. L’objectif est de passer d’un simple suivi de commandes à un véritable pilotage achats.

KPI à suivre

Action corrective

Risque opérationnel

Criticité achat

Étape achat	Objectif métier	KPI clé	Risque à surveiller	Document / preuve	Action corrective	Outil Excel conseillé	Criticité
Demande d’achat	Clarifier le besoin avant engagement.	Délai de validation	Besoin mal défini	Fiche demande achat	Faire valider quantité, budget et urgence.	Registre des demandes	Moyen
Cahier des charges	Définir les exigences qualité, délai et conformité.	Taux de demandes complètes	Spécification ambiguë	Cahier des charges fournisseur	Ajouter critères techniques et seuils acceptables.	Checklist cahier des charges	Élevé
Consultation fournisseur	Comparer les offres disponibles.	Nombre de devis reçus	Comparaison insuffisante	Tableau comparatif devis	Consulter au moins trois fournisseurs qualifiés.	Comparateur de devis	Moyen
Sélection fournisseur	Choisir le fournisseur le plus fiable.	Score fournisseur	Choix basé seulement sur le prix	Grille de scoring	Comparer prix, délai, qualité, risque et service.	Scoring fournisseur	Élevé
Négociation	Obtenir de meilleures conditions commerciales.	Économie négociée	Gain court terme fragile	Compte rendu négociation	Négocier prix, délai, pénalités et conditions de paiement.	Suivi économies achats	Moyen
Bon de commande	Formaliser l’engagement achat.	Délai émission commande	Commande incomplète	Bon de commande	Vérifier référence, quantité, prix, délai et fournisseur.	Suivi commandes achats	Moyen
Suivi livraison	Contrôler l’arrivée des commandes.	Taux de livraison à temps	Retard fournisseur	Planning livraison	Relancer fournisseur et ajuster les priorités internes.	Tableau suivi livraison	Élevé
Réception	Vérifier quantité et conformité.	Taux de conformité réception	Écart quantité ou qualité	Bon de réception	Bloquer les écarts et ouvrir une réclamation.	Contrôle réception Excel	Élevé
Contrôle facture	Éviter les écarts de paiement.	Écart facture / commande	Surfacturation ou doublon	Facture, BC, réception	Rapprocher commande, réception et facture.	Rapprochement facture	Moyen
Suivi budget achat	Contrôler les dépenses engagées.	Budget consommé	Dépassement budgétaire	Budget achats mensuel	Analyser écarts et prioriser les achats critiques.	Budget achat Excel	Élevé
Retards répétés	Identifier les fournisseurs instables.	Nombre de retards	Rupture d’activité	Historique livraison	Mettre le fournisseur sous surveillance.	Dashboard retards fournisseur	Critique
Non-conformités	Mesurer la qualité livrée.	Taux de défaut fournisseur	Rebuts, retours, arrêt production	Fiche non-conformité	Demander plan correctif et suivi qualité.	Suivi non-conformités	Critique
Dépendance fournisseur	Évaluer le risque de source unique.	Part achat par fournisseur	Fournisseur unique trop dominant	Cartographie fournisseurs	Lancer un second sourcing.	Matrice dépendance fournisseur	Critique
Sourcing alternatif	Sécuriser les approvisionnements.	Nombre fournisseurs qualifiés	Absence d’alternative	Liste fournisseurs qualifiés	Tester, qualifier et référencer de nouveaux fournisseurs.	Base fournisseurs Excel	Élevé
Stock critique	Éviter rupture ou immobilisation excessive.	Stock mini / stock réel	Rupture ou surstock	État des stocks	Définir seuils d’alerte et réapprovisionnement.	Suivi stock critique	Élevé
Achats urgents	Contrôler les achats non planifiés.	Nombre achats urgents	Surcoût et désorganisation	Registre urgence achat	Analyser la cause et prévenir la répétition.	Journal achats urgents	Moyen
Contrat cadre	Stabiliser prix, volumes et engagements.	Taux achats sous contrat	Conditions instables	Contrat fournisseur	Formaliser prix, délais, SLA et pénalités.	Suivi contrats achats	Élevé
Performance fournisseur	Piloter la qualité de service.	OTD + qualité + réactivité	Dégradation progressive	Revue fournisseur	Mettre en place un score mensuel.	Tableau performance fournisseur	Élevé
Coût total achat	Mesurer le coût réel au-delà du prix.	TCO	Prix bas mais coût caché élevé	Analyse coût complet	Intégrer transport, qualité, retards et retours.	Calcul TCO fournisseur	Élevé
Reporting achats	Partager une vision claire à la direction.	KPI mensuels achats	Décisions sans données	Rapport achats	Construire un dashboard simple et régulier.	Tableau de bord achats	Moyen

Les KPI les plus décisifs Délai fournisseur, taux de conformité, écart facture, économie négociée et dépendance fournisseur.

Les risques à surveiller Rupture stock, retard répété, non-conformité, surcoût caché et fournisseur unique.

La décision utile Un bon suivi achat conduit toujours à une action : relancer, renégocier, sécuriser, auditer ou diversifier.

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.

---

Sans logiciel dédié, un suivi des achats peut déjà devenir très lisible au quotidien lorsqu’il repose sur un tableau clair, quelques statuts bien choisis et des alertes simples. Pour cette approche plus pratique, centrée sur l’usage régulier dans Excel, télécharger un modèle de suivi quotidien des achats avec Excel.