Excel : créer un suivi automatique des paiements fournisseurs avec escomptes, pénalités et priorités

Difficulté

Intermédiaire

Le plus simple pour suivre automatiquement les paiements fournisseurs dans Excel est de séparer les factures et les conditions fournisseurs, puis de relier les deux avec RECHERCHEX. Vous pourrez ensuite calculer l’escompte, la pénalité de retard et une priorité de règlement à partir de SI, AUJOURDHUI et SOMME.SI.ENS.

Le tutoriel ci-dessous propose une structure de classeur et des formules prêtes à adapter. Les taux de pénalité et les règles d’escompte varient selon vos contrats : vérifiez-les avant mise en production.

Prérequis

Préparez un classeur Excel avec deux feuilles : une pour les factures, une pour les conditions négociées par fournisseur. Convertir chaque plage en Tableau Excel facilite les formules et l’extension automatique.

Matériel nécessaire

• Excel avec fonctions SI, AUJOURDHUI, RECHERCHEX et SOMME.SI.ENS
• Liste des fournisseurs avec conditions de paiement, escompte et pénalité
• Liste des factures fournisseurs à régler

Étapes

1. 1

   Créer la table des conditions fournisseurs

   Dans une feuille nommée Conditions, créez un tableau avec au minimum : Fournisseur, Délai_jours, Escompte_%, Jours_escompte et Pénalité_%. Convertissez la plage en tableau, par exemple tbConditions.

   Cette table centralise les règles de calcul. Une ligne par fournisseur évite de recopier des taux dans chaque facture et limite les erreurs.

2. 2

   Créer la table des factures à payer

   Dans une feuille Factures, créez les colonnes suivantes : N° facture, Fournisseur, Date facture, Montant TTC, Date paiement, puis les colonnes calculées. Convertissez aussi cette plage en tableau, par exemple tbFactures.

   Ajoutez ensuite les colonnes calculées : Délai_jours, Escompte_%, Jours_escompte, Pénalité_%, Échéance, Date limite escompte, Escompte potentiel, Jours retard, Pénalité estimée et Priorité.

3. 3

   Récupérer les conditions avec RECHERCHEX

   Dans Délai_jours, entrez une formule de type : =RECHERCHEX([@Fournisseur];Conditions[Fournisseur];Conditions[Délai_jours];""). Reprenez la même logique pour Escompte_%, Jours_escompte et Pénalité_%.

   Le quatrième argument renvoie ici une valeur vide si le fournisseur n’est pas trouvé. C’est utile pour repérer rapidement une condition manquante sans casser tout le tableau.

4. 4

   Calculer l’échéance et la date limite d’escompte

   

   Dans Échéance, utilisez : =[@[Date facture]]+[@Délai_jours]. Dans Date limite escompte, utilisez : =[@[Date facture]]+[@Jours_escompte].

   Formatez ces colonnes en date. Vous obtenez immédiatement la date normale de règlement et la dernière date permettant un paiement anticipé avec escompte.

5. 5

   Calculer l’escompte potentiel avec SI et AUJOURDHUI

   Dans Escompte potentiel, saisissez une formule de type : =SI(ET([@[Date paiement]]="";AUJOURDHUI()<=[@[Date limite escompte]]);[@[Montant TTC]]*[@[Escompte_%]];0). Cette formule ne propose un escompte que si la facture n’est pas encore payée et si la date du jour est encore dans la fenêtre d’escompte.

   Si vous souhaitez calculer l’escompte à partir d’une date de paiement réellement saisie, adaptez la logique avec Date paiement. Le choix dépend de votre usage : simulation avant paiement ou contrôle après paiement.

6. 6

   Mesurer le retard et estimer la pénalité

   

   Dans Jours retard, utilisez par exemple : =SI(ET([@[Date paiement]]="";AUJOURDHUI()>[@Échéance]);AUJOURDHUI()-[@Échéance];0). Vous obtenez un nombre de jours de retard uniquement pour les factures non payées et échues.

   Dans Pénalité estimée, utilisez une formule simple comme : =SI([@[Jours retard]]>0;[@[Montant TTC]]*[@[Pénalité_%]];0). Cette estimation applique ici un taux forfaitaire ; si votre contrat prévoit une pénalité proratisée au nombre de jours, il faudra adapter la formule selon cette règle contractuelle.

7. 7

   Calculer une priorité de règlement

   

   Créez une colonne Priorité avec une règle simple et lisible. Par exemple : =SI([@[Jours retard]]>0;1;SI([@[Escompte potentiel]]>0;2;SI([@Échéance]-AUJOURDHUI()<=7;3;4))).

   Dans cet exemple, 1 correspond aux factures déjà en retard, 2 à celles qui offrent encore un escompte, 3 à celles qui arrivent à échéance sous 7 jours et 4 aux autres. Triez ensuite le tableau par priorité croissante pour obtenir l’ordre de traitement.

8. 8

   Additionner les montants par fournisseur avec SOMME.SI.ENS

   Dans une zone de synthèse, listez chaque fournisseur puis calculez l’encours non payé avec une formule de type : =SOMME.SI.ENS(Factures[Montant TTC];Factures[Fournisseur];A2;Factures[Date paiement];""). Vous pouvez ainsi voir immédiatement les montants à décaisser par fournisseur.

   Ajoutez au besoin une deuxième formule pour cumuler les escomptes potentiels : =SOMME.SI.ENS(Factures[Escompte potentiel];Factures[Fournisseur];A2). Cette vue aide à arbitrer entre relation fournisseur, trésorerie et gain financier.

9. 9

   Tester le fichier sur un exemple simple

   Prenez un fournisseur avec 30 jours de délai, 2 % d’escompte sous 10 jours et 1 % de pénalité. Pour une facture de 1 000 € datée du 1er mars, l’échéance est le 31 mars, la date limite d’escompte le 11 mars, l’escompte potentiel est de 20 € tant que la fenêtre est ouverte, et la pénalité estimée passe à 10 € dès que la facture est considérée en retard selon la règle choisie.

   Ce test sert surtout à vérifier la cohérence des formules et des formats. Si le résultat diffère, contrôlez d’abord les dates et les pourcentages stockés dans les cellules.

Erreurs fréquentes & dépannage

L’erreur la plus fréquente est d’utiliser des noms de fournisseurs différents entre les deux tables, ce qui empêche RECHERCHEX de ramener les conditions. Vérifiez aussi que les taux sont bien stockés comme pourcentages et que les dates sont reconnues par Excel.

Autre piège : mélanger simulation et constat réel. Un escompte calculé avec AUJOURDHUI() aide à décider aujourd’hui, mais il ne prouve pas qu’un paiement effectif a eu lieu dans le délai si la Date paiement n’est pas renseignée.

Astuces & pour aller plus loin

Ajoutez un filtre sur Date paiement vide pour afficher uniquement les factures à traiter. Vous pouvez aussi geler la ligne d’en-tête et trier d’abord par Priorité, puis par Échéance.

Si vous voulez fiabiliser davantage le fichier, créez une liste déroulante pour la colonne Fournisseur à partir de la table Conditions. Cela réduit fortement les écarts de saisie qui bloquent les recherches.

Questions fréquentes

Comment traiter les fournisseurs sans escompte négocié ?

Laissez Escompte_% et Jours_escompte à 0 dans la table Conditions. Les formules renverront alors un escompte potentiel nul.

Puis-je prioriser selon le montant plutôt que selon le retard ?

Oui. Il suffit d’adapter la formule de Priorité en ajoutant un test sur Montant TTC ou en créant un score plus détaillé selon vos règles internes.

Pourquoi utiliser une table séparée pour les conditions fournisseurs ?

Parce qu’une modification de taux ou de délai se fait à un seul endroit. Toutes les factures liées au fournisseur récupèrent ensuite la nouvelle valeur via RECHERCHEX.

Conclusion

Pour un suivi fiable, commencez par construire deux tables propres, puis validez vos formules sur quelques factures tests avant de généraliser. Si vos pénalités suivent une règle contractuelle plus complexe qu’un taux forfaitaire, adaptez ce modèle avant usage opérationnel.