- Le projet final assemble ce que la série a construit : import Power Query, table consolidée, SOMME.SI.ENS, noms définis, listes déroulantes, contrôles et tableau de bord.
- Un seul paramètre, le mois, pilote tout : on le change dans une liste et les indicateurs, variations, alertes et contrôles se recalculent.
- Un reporting n’est digne de confiance que s’il porte ses propres contrôles : balance équilibrée, nombre de comptes importés, conclusion « fiable : oui / non ».
- Les étapes suivantes sont l’automatisation par macros (VBA) et la diffusion interactive avec Power BI ; elles demandent d’abord un reporting propre et fiable, comme celui-ci.
Cinquante épisodes, huit modules : de l’interface d’Excel à la modélisation financière. Il reste à tout assembler dans un livrable que l’on peut réellement remettre à un dirigeant : un reporting financier mensuel, qui se met à jour à partir des balances et qui prouve ses propres chiffres. C’est le projet final de la formation.
Ce que vous saurez faire à la fin de l’épisode
- concevoir l’architecture d’un reporting automatisé ;
- piloter tous les indicateurs par un seul paramètre ;
- calculer flux du mois, cumuls et variations avec
SOMME.SI.ENS; - intégrer des contrôles qui concluent « fiable : oui ou non » ;
- présenter le résultat dans un tableau de bord, et savoir comment aller plus loin.
L’architecture : de la balance au tableau de bord
Le principe est celui d’une chaîne de montage : chaque étape fait une chose, et seule la dernière est regardée par la direction.
| Étape | Rôle | Épisodes mobilisés |
|---|---|---|
| 1. Import | Charger les balances mensuelles, les nettoyer, les empiler | 46 et 47 (Power Query) |
| 2. Données | Table consolidée : mois, n°, compte, intitulé, débit, crédit | 8 (tableaux), 32 (balance) |
| 3. Paramètres | Mois du reporting, seuils, listes | 10 (noms), 11 (listes déroulantes) |
| 4. Indicateurs | Flux du mois, cumuls, variations, alertes | 22 et 23 (SOMME.SI, SOMME.SI.ENS), 13 (SI) |
| 5. Contrôles | Équilibre, complétude, conclusion | 33 et 49 |
| 6. Tableau de bord | Indicateurs clés et graphiques | 27 à 30 |
Étape 1 : les paramètres
Un onglet Paramètres contient la seule cellule que l’utilisateur change : le mois du reporting, dans une liste déroulante (épisode 11). Une formule en déduit le numéro du mois :
=EQUIV(B1;A5:A7;0) → 3 (pour Mars)Le seuil d’alerte de trésorerie (ici 0) est aussi un paramètre. Les plages de la table de données portent des noms définis (NumMois, CompteCol, DebitCol, CreditCol) et le numéro du mois s’appelle MoisSel : les formules deviennent lisibles.
Figure 1 : les paramètres. Repère 1 : le mois, choisi dans une liste ; repère 2 : le seuil d’alerte.
Étape 2 : les données
La table est celle de l’épisode 47 : 18 lignes (6 comptes × 3 mois) avec le numéro du mois. En production, elle viendrait de la requête Power Query qui consolide le dossier de balances ; on l’actualise chaque mois.
Étape 3 : les indicateurs
La difficulté est de rester cohérent : un flux du mois (ventes de mars) et un solde cumulé (trésorerie à la fin de mars) ne se calculent pas pareil. Pour un flux, le critère du mois est « égal au numéro » ; pour un cumul, « inférieur ou égal ». Deux formules types :
Ventes du mois (crédit − débit du compte 7111, mois = B$2) :
=SOMME.SI.ENS(CreditCol;NumMois;B$2;CompteCol;"7111")-SOMME.SI.ENS(DebitCol;NumMois;B$2;CompteCol;"7111")
Trésorerie fin de mois (débit − crédit du compte 5141, mois ≤ B$2) :
=SOMME.SI.ENS(DebitCol;NumMois;"<="&B$2;CompteCol;"5141")-SOMME.SI.ENS(CreditCol;NumMois;"<="&B$2;CompteCol;"5141")Les colonnes B (mois retenu) et C (mois précédent, B2-1) utilisent la même formule : la variation est une simple soustraction. Le sens change selon la nature du compte : produits et dettes se lisent en crédit moins débit, charges et créances en débit moins crédit.
Figure 2 : le reporting de mars. Repère 1 : les indicateurs ; repère 2 : l’alerte de trésorerie ; repère 3 : la conclusion des contrôles.
| Indicateur | Mars | Février | Variation |
|---|---|---|---|
| Ventes du mois | 75 000 | 65 000 | +10 000 |
| Achats de marchandises | 55 000 | 50 000 | +5 000 |
| Charges de personnel | 20 000 | 20 000 | 0 |
| Résultat du mois | 0 | −5 000 | +5 000 |
| Résultat cumulé | −10 000 | −10 000 | 0 |
| Trésorerie de fin de mois | −16 000 | −14 000 | −2 000 |
| Créances clients | 20 000 | 15 000 | +5 000 |
| Dettes fournisseurs | 14 000 | 11 000 | +3 000 |
Étape 4 : les contrôles
Un reporting sans contrôle est une opinion. Trois lignes suffisent ici :
Balance équilibrée : =SI(SOMME.SI(NumMois;MoisSel;DebitCol)=SOMME.SI(NumMois;MoisSel;CreditCol);"OK";"ÉCART")
Six comptes importés : =SI(NB.SI(NumMois;MoisSel)=6;"OK";"ÉCART")
Reporting fiable ? : =SI(ET(B14="OK";B15="OK");"OUI";"NON")Le premier vérifie que la balance du mois s’équilibre, le second qu’aucun compte n’a été perdu à l’import, le troisième synthétise : si la conclusion est « NON », le reporting ne part pas. Dans un cas réel, on ajouterait un rapprochement avec la comptabilité (trésorerie du reporting = solde bancaire) et un contrôle d’absence d’erreurs de formule (épisode 49).
Étape 5 : le tableau de bord
Les indicateurs alimentent une page unique que l’on peut imprimer en PDF (épisode 12) :
Figure 3 : le tableau de bord. Un paramètre, un mois, une page.
Lire le reporting : ce que dit mars
Le reporting ne vaut que par le commentaire qui l’accompagne. Voici celui de mars :
- Activité : les ventes progressent de 15,4 % (75 000 contre 65 000) et de 25 % depuis janvier.
- Résultat : l’entreprise revient à l’équilibre en mars (résultat du mois nul) après deux mois à −5 000, mais reste à −10 000 sur le trimestre : le cumul ne bouge plus.
- Trésorerie : le découvert s’aggrave (−16 000 contre −14 000) alors que le résultat s’est redressé, parce que les créances clients augmentent plus vite que les dettes fournisseurs (+5 000 contre +3 000) : la croissance consomme du besoin en fonds de roulement (épisodes 41 et 42).
- Action : relancer les créances (20 000) et renégocier le découvert avant que la croissance ne l’alourdisse.
C’est ce commentaire de quatre lignes, adossé à des chiffres contrôlés, qui fait d’un tableau un outil de pilotage.
Aller plus loin : macros, VBA, Power BI
Macros et VBA. L’enregistreur de macros (onglet Développeur) mémorise des actions : actualiser les requêtes, mettre à jour le tableau de bord, l’exporter en PDF. Le code VBA qui en résulte se retouche. Un classeur contenant des macros s’enregistre au format .xlsm, et il faut n’activer que des macros dont on connaît la source. L’automatisation par macro vient après un modèle fiable : elle accélère, elle ne corrige pas.
Power BI. Power BI Desktop se connecte à vos classeurs et à vos dossiers, réutilise des requêtes de type Power Query et produit des rapports interactifs que l’on partage. Il convient quand plusieurs personnes doivent explorer les chiffres ; un classeur Excel bien conçu reste suffisant pour un reporting mensuel adressé à une direction.
Les compétences qui comptent restent les mêmes : des données propres, des formules cohérentes, des contrôles, un commentaire clair.
À vous de jouer
Dans le classeur d’exercice :
- Complétez l’onglet Reporting : lignes 3 à 10 (colonnes B à D), puis les contrôles.
- Choisissez Février dans la liste : quels sont les ventes, le résultat du mois, la trésorerie et les créances ?
- Choisissez Janvier : que montre la colonne « Mois précédent » ?
- Transformez la table Données en tableau (Ctrl+T) et ajoutez les six lignes d’avril : que faut-il modifier pour que le reporting prenne avril en compte ?
Correction. (2) Février : ventes 65 000, résultat du mois −5 000, résultat cumulé −10 000, trésorerie −14 000, créances 15 000, dettes 11 000. (3) Pour janvier, le « mois précédent » est le mois 0, qui n’existe pas : tous les indicateurs de cette colonne valent 0 et la variation égale la valeur du mois (ventes 60 000, résultat −5 000, trésorerie −12 000, créances 12 000, dettes 5 000). (4) Il faut ajouter « Avril » à la liste des mois (en prolongeant la liste de la liste déroulante) et s’assurer que les noms définis couvrent le tableau (les noms pointant sur des plages fixes B2:B19 doivent être redéfinis sur les colonnes du tableau, qui s’étendent seules). Le contrôle « six comptes importés » indiquerait sinon un ÉCART pour avril.
Ce que vous avez construit en cinquante épisodes
Vous savez désormais naviguer et saisir proprement, calculer et conditionner avec les formules, chercher et croiser les données, analyser avec les tableaux croisés dynamiques et les graphiques, tenir une comptabilité dans Excel (journal, balance, états, TVA, amortissements, balance âgée), traiter la paie, évaluer un investissement et un emprunt, bâtir un budget et un modèle prévisionnel, enfin automatiser l’import et fiabiliser le tout. Chaque épisode a un classeur d’exercice et un corrigé : reprenez-les au rythme qui vous convient, et appliquez-les à vos propres dossiers. Le programme complet est sur la page Formation Excel.
À retenir
- Un reporting automatisé = import, table consolidée, paramètre unique, indicateurs, contrôles, tableau de bord.
- Flux du mois : critère « = mois » ; solde cumulé : critère « <= mois » ; le sens (crédit − débit ou l’inverse) dépend du compte.
- Chaque reporting porte ses contrôles et conclut « fiable : oui ou non ».
- Le commentaire écrit est la valeur ajoutée du comptable.
- VBA et Power BI viennent après un modèle fiable, jamais à la place.
Ce reporting clôt la série : merci de l’avoir suivie, jusqu’au bout.
Questions fréquentes
Faut-il savoir programmer pour automatiser un reporting ?
Non. La grande majorité de l’automatisation d’un reporting mensuel se fait sans code : Power Query pour l’import, des formules pour les indicateurs, un paramètre pour le mois, des contrôles. La programmation (VBA) n’ajoute que l’enchaînement d’actions (actualiser, exporter en PDF, envoyer) et ne remplace jamais un modèle fiable.
Quand passer à Power BI ?
Quand le reporting doit être partagé, filtré par plusieurs utilisateurs ou alimenté par de gros volumes ou plusieurs sources. Pour un tableau de bord mensuel destiné à une direction restreinte, un classeur Excel bien conçu suffit. Dans les deux cas, la qualité des données et des contrôles reste la même.
Que contient un bon reporting financier mensuel ?
Peu d’indicateurs, bien choisis : activité (ventes), rentabilité (résultat du mois et cumulé), trésorerie, créances et dettes, avec la variation par rapport au mois précédent et au budget. S’y ajoutent les alertes, les contrôles et un commentaire écrit qui dit ce qui a changé et pourquoi.
Comment gérer l’ajout d’un nouveau mois ?
En déposant la nouvelle balance dans le dossier source et en actualisant la requête Power Query. Si les noms définis portent sur des plages fixes, transformez la table de données en tableau Excel (Ctrl+T) pour qu’elles s’étendent d’elles-mêmes.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.