Guide complet : Créer votre tableau de suivi de trésorerie sur Excel
Objectif : Créer un tableau de trésorerie prévisionnel sur 12 semaines glissantes avec actualisation hebdomadaire automatisée et indicateurs de pilotage.
Étape 1 : Structure du classeur Excel
Organisation des feuilles
Créez un classeur avec 5 feuilles distinctes :
- Feuille 1 - "Tableau_Bord" : Vue synthétique et indicateurs
- Feuille 2 - "Prévisionnel" : Plan de trésorerie détaillé
- Feuille 3 - "Encaissements" : Détail des entrées
- Feuille 4 - "Décaissements" : Détail des sorties
- Feuille 5 - "Paramètres" : Données de référence
Étape 2 : Créer la feuille "Paramètres"
| Cellule | Libellé | Valeur à saisir | Format |
|---|---|---|---|
| A1 | Date de début | =AUJOURDHUI() | Date |
| A2 | Solde bancaire initial | [Votre solde actuel] | Monétaire € |
| A3 | Délai moyen paiement clients (jours) | 30 | Nombre |
| A4 | Délai paiement fournisseurs (jours) | 45 | Nombre |
| A5 | Taux TVA | 20% | Pourcentage |
| A6 | Seuil alerte trésorerie | [1 mois de charges] | Monétaire € |
Étape 3 : Structurer la feuille "Encaissements"
Colonnes à créer (ligne 1)
| Colonne | Titre | Description |
|---|---|---|
| A | N° Facture | Référence unique |
| B | Client | Nom du client |
| C | Date facture | Date d'émission |
| D | Montant HT | Montant hors taxes |
| E | TVA | =D2*Paramètres!$A$5 |
| F | Montant TTC | =D2+E2 |
| G | Échéance | =C2+Paramètres!$A$3 |
| H | Statut | Liste déroulante : En attente/Encaissé/Retard |
| I | Date encaissement réel | À remplir manuellement |
| J | Écart (jours) | =SI(I2<>"";I2-G2;AUJOURDHUI()-G2) |
Étape 4 : Structurer la feuille "Décaissements"
Colonnes à créer (ligne 1)
| Colonne | Titre | Description/Formule |
|---|---|---|
| A | Catégorie | Liste : Salaires/Charges sociales/Fournisseurs/Loyer/Impôts/Autres |
| B | Libellé | Description de la dépense |
| C | Fournisseur | Nom du fournisseur |
| D | Date facture | Date de réception facture |
| E | Montant HT | Montant hors taxes |
| F | TVA | =E2*Paramètres!$A$5 |
| G | Montant TTC | =E2+F2 |
| H | Échéance prévue | =D2+Paramètres!$A$4 |
| I | Récurrent | OUI/NON |
| J | Fréquence | Mensuel/Trimestriel/Annuel |
| K | Statut | À payer/Payé/En attente |
Étape 5 : Construire le plan de trésorerie prévisionnel
Important : Cette feuille centralise toutes les données pour une vision hebdomadaire sur 12 semaines.
Structure du tableau prévisionnel
Colonnes de base :
- Colonne A : Semaines (S1 à S12)
- Colonne B : Dates début semaine :
=Paramètres!$A$1+(7*(LIGNE()-2)) - Colonne C : Dates fin semaine :
=B2+6
Formules pour les encaissements (Colonne D) :
=SOMME.SI.ENS(Encaissements!$F:$F;Encaissements!$G:$G;">="&B2;Encaissements!$G:$G;"<="&C2;Encaissements!$H:$H;"En attente")
Cette formule somme tous les encaissements prévus entre les dates de début et fin de semaine avec statut "En attente"
Formules pour les décaissements (Colonne E) :
=SOMME.SI.ENS(Décaissements!$G:$G;Décaissements!$H:$H;">="&B2;Décaissements!$H:$H;"<="&C2;Décaissements!$K:$K;"À payer")
Solde hebdomadaire (Colonne F) :
=D2-E2
Trésorerie cumulée (Colonne G) :
=SI(LIGNE()=2;Paramètres!$A$2+F2;G1+F2)
Indicateur visuel (Colonne H) :
=SI(G2>Paramètres!$A$6*2;"VERT";SI(G2>Paramètres!$A$6;"ORANGE";"ROUGE"))
Étape 6 : Créer le tableau de bord synthétique
Indicateurs clés à calculer
- Trésorerie actuelle :
=Paramètres!A2 - Trésorerie prévisionnelle à 4 semaines :
=INDEX(Prévisionnel!G:G;5) - Point bas prévisionnel :
=MIN(Prévisionnel!G2:G13) - Date du point bas :
=INDEX(Prévisionnel!B:B;EQUIV(MIN(Prévisionnel!G2:G13);Prévisionnel!G2:G13;0)+1)
Ratios de performance
- DSO (délai moyen encaissement) :
=MOYENNE(Encaissements!J:J) - Taux d'impayés :
=NB.SI(Encaissements!H:H;"Retard")/NB.SI(Encaissements!H:H;"<>")*100 - Total encaissements en attente :
=SOMME.SI(Encaissements!H:H;"En attente";Encaissements!F:F) - Total décaissements à venir (30j) :
=SOMME.SI(Décaissements!H:H;"<="&AUJOURDHUI()+30;Décaissements!G:G)
Étape 7 : Mise en forme conditionnelle
Règles de mise en forme automatique
Pour la colonne "Trésorerie cumulée" (Prévisionnel!G:G) :
- Sélectionner la plage G2:G13
- Menu Accueil → Mise en forme conditionnelle → Nouvelle règle
- Type : "Appliquer une mise en forme aux cellules en fonction de leur valeur"
- Créer 3 règles :
- Si valeur < Paramètres!$A$6 → Fond rouge
- Si valeur entre Paramètres!$A$6 et Paramètres!$A$6*2 → Fond orange
- Si valeur > Paramètres!$A$6*2 → Fond vert
Pour les retards de paiement (Encaissements!J:J) :
- Si valeur > 7 → Fond jaune
- Si valeur > 15 → Fond orange
- Si valeur > 30 → Fond rouge
Étape 8 : Créer les graphiques de suivi
Graphique d'évolution de trésorerie
- Sélectionner les données : Prévisionnel!B2:B13 (dates) et G2:G13 (trésorerie cumulée)
- Insertion → Graphique → Courbe avec marqueurs
- Ajouter une ligne horizontale pour le seuil d'alerte :
- Créer une colonne supplémentaire avec la valeur =Paramètres!$A$6 sur chaque ligne
- Ajouter cette série au graphique
- Formater en ligne pointillée rouge
- Titre du graphique : "Évolution prévisionnelle de la trésorerie sur 12 semaines"
Graphique de répartition des décaissements
- Créer un tableau croisé dynamique depuis les décaissements
- Catégorie en ligne, Montant TTC en valeur
- Insérer un graphique secteur (camembert)
- Afficher les pourcentages sur chaque secteur
Étape 9 : Automatiser les relances clients
Création d'un système d'alertes
Ajouter une colonne "Action" dans Encaissements (colonne K) :
=SI(H2="Encaissé";"";SI(J2>30;"CONTENTIEUX";SI(J2>15;"RELANCE URGENTE";SI(J2>7;"RELANCE AMIABLE";SI(J2>-7;"RAPPEL PRÉVENTIF";"")))))
Créer une liste des actions à mener (nouvelle feuille "Actions") :
- Colonne A : =SI(Encaissements!K2<>"";Encaissements!B2;"")
- Colonne B : =SI(Encaissements!K2<>"";Encaissements!A2;"")
- Colonne C : =SI(Encaissements!K2<>"";Encaissements!F2;"")
- Colonne D : =SI(Encaissements!K2<>"";Encaissements!K2;"")
- Colonne E : =SI(Encaissements!K2<>"";Encaissements!J2;"")
Filtrer pour masquer les lignes vides et obtenir la liste des actions du jour.
Étape 10 : Formules avancées pour l'analyse
Calculs analytiques supplémentaires
Besoin en Fonds de Roulement (BFR) :
=SOMME.SI(Encaissements!H:H;"En attente";Encaissements!F:F)-SOMME.SI(Décaissements!K:K;"À payer";Décaissements!G:G)
Rotation des créances clients (en jours de CA) :
=(SOMME.SI(Encaissements!H:H;"En attente";Encaissements!F:F)/SOMME(Encaissements!F:F))*365
Prévision de rupture de trésorerie :
=SI(MIN(Prévisionnel!G2:G13)<0;"ALERTE : Rupture prévue le "&TEXT(INDEX(Prévisionnel!B:B;EQUIV(MIN(Prévisionnel!G2:G13);Prévisionnel!G2:G13;0)+1);"jj/mm/aaaa");"Pas de rupture prévue")
Calcul du cash burn rate mensuel :
=(SOMME(Décaissements!G:G)/NB.MOIS(MIN(Décaissements!D:D);MAX(Décaissements!D:D)))
Étape 11 : Création de scénarios
Analyse de sensibilité
Créer 3 scénarios dans une nouvelle feuille "Scénarios" :
| Paramètre | Optimiste | Réaliste | Pessimiste |
|---|---|---|---|
| Délai encaissement | -10 jours | Normal | +15 jours |
| Taux d'impayés | 0% | 2% | 5% |
| Variation CA | +20% | 0% | -20% |
Formule pour calculer l'impact sur la trésorerie finale :
=Prévisionnel!G13*(1+Scénarios!B3)*(1-Scénarios!B2)+((Scénarios!B1-Paramètres!A3)*MOYENNE(Encaissements!F:F)/30)
Étape 12 : Maintenance et bonnes pratiques
Actualisation hebdomadaire
Chaque lundi matin :
- Mettre à jour le solde bancaire réel dans Paramètres!A2
- Saisir les nouvelles factures émises dans Encaissements
- Enregistrer les paiements reçus (colonne I)
- Ajouter les nouvelles factures fournisseurs dans Décaissements
- Marquer les paiements effectués (statut "Payé")
- Ajuster les dates d'échéance si nécessaire
- Vérifier les écarts prévisionnel/réel
Points de contrôle mensuels
- Analyser l'évolution du DSO
- Vérifier le taux d'impayés
- Comparer les 3 scénarios avec le réalisé
- Ajuster les paramètres (délais moyens)
- Exporter un rapport PDF pour archivage
- Faire une sauvegarde datée du fichier
- Analyser les écarts significatifs
- Mettre à jour le seuil d'alerte si nécessaire
Conseils pour optimiser l'utilisation :
- Utiliser des tableaux structurés Excel (Insertion → Tableau) pour faciliter les formules
- Nommer les plages importantes (Formules → Gestionnaire de noms) pour des formules plus lisibles
- Protéger les cellules contenant des formules (Révision → Protéger la feuille)
- Créer une macro pour l'actualisation automatique des données récurrentes
- Utiliser la validation de données pour éviter les erreurs de saisie
- Documenter les formules complexes avec des commentaires
Erreurs fréquentes à éviter :
- Oublier de mettre à jour le solde bancaire initial régulièrement
- Ne pas tenir compte de la TVA dans les calculs
- Mélanger les dates de facture et les dates d'échéance
- Négliger les charges récurrentes (loyer, salaires)
- Ne pas prévoir de marge de sécurité dans les prévisions
- Oublier d'actualiser le statut des factures encaissées/payées
