Aller au contenu principal
Le DojoLe Dojo
ComptablesAvancé45 min

Reporting financier mensuel sur Excel

Un exercice corrigé pour automatiser un reporting financier mensuel avec SOMME.SI.ENS et INDEX, consolider le réalisé et calculer les écarts budget, avec le fichier Excel à télécharger.

Chaque mois, c'est le même rituel, on exporte la balance, on recopie les chiffres, on recalcule les écarts à la main, et la moindre correction oblige à tout reprendre. Dans cet exercice, on va voir ensemble comment faire consolider ces milliers d'écritures par Excel et présenter une synthèse propre sans retraitement.

L'objectif est d'agréger le grand livre poste par poste, de confronter le réalisé au budget et d'aller chercher dynamiquement le bon mois, pour obtenir un fichier qu'on recolle d'un mois sur l'autre. Une fois ce réflexe de consolidation par critères acquis, il transforme n'importe quel export comptable brut en tableau de pilotage lisible par la direction !

Le chiffre clé
Chaque mois, on exporte la balance, on recopie les chiffres, on recalcule les écarts à la main, et la moindre correction oblige à tout reprendre. SOMME.SI.ENS fait ce travail à ta place. Dans le fichier de l'exercice, les 24 écritures du grand livre deviennent 7 lignes de synthèse, et la même formule tient sur un grand livre de plusieurs milliers de lignes.

Ce que tu vas construire

  • Consolider les écritures du grand livre en une synthèse d'une ligne par poste avec SOMME.SI.ENS.
  • Calculer les écarts budget vs réalisé en montant et en pourcentage pour chaque poste.
  • Récupérer dynamiquement le budget du mois étudié depuis le tableau budgétaire annuel avec INDEX et EQUIV.
  • Construire le cumul du réalisé depuis janvier pour détecter les tendances au-delà du mois étudié.
  • Lire chaque écart dans le bon sens, favorable ou défavorable selon qu'il touche un produit ou une charge, avant de le présenter à la direction.

À connaître avant de commencer

  • Avoir déjà utilisé SOMME.SI ou SOMME.SI.ENS avec des critères de date.
  • Comprendre la structure d'un compte de résultat (charges vs produits, résultat = produits - charges).
  • Connaître INDEX et EQUIV au moins de nom, on les combine pour retrouver le budget du bon poste.

Voici les données de départ, réparties sur 4 onglets comme dans le fichier (clique sur un onglet sous le tableau pour changer de feuille). Copie-les ou , puis entraîne-toi avant de regarder le corrigé.

ABCDEFG
1CodePosteBudget du moisRéalisé du moisÉcartÉcart %Réalisé cumulé
2701Ventes de produits
3706Prestations de services
4601Achats de matières premières
5613Locations
6622Honoraires
7641Rémunérations du personnel
8681Dotations aux amortissements

Résoudre l'exercice étape par étape

Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.

Ta progression
0/4
1
Consolider le réaliséDans l'onglet Reporting, en colonne Réalisé du mois, consolide le réalisé de chaque poste en additionnant les écritures de tous ses sous-comptes datées du mois étudié.
2
Récupérer le budget dynamiquementDans l'onglet Reporting, en colonne Budget du mois, retrouve dans l'onglet Budget le montant prévu pour le poste et pour le mois étudié, sans le recopier à la main.
3
Calculer les écartsDans l'onglet Reporting, en colonnes Écart et Écart %, calcule l'écart entre le réalisé et le budget arrondi à l'euro, puis rapporte-le au budget.
4
Créer le comparatif cumuléDans l'onglet Reporting, en colonne Réalisé cumulé, totalise les écritures de chaque poste depuis le 1er janvier jusqu'à la fin du mois étudié.
Le conseil du pro
Le réflexe qui t'évite de lister chaque sous-compte, c'est d'encadrer le numéro de compte. En gardant les comptes compris entre A2*1000 et (A2+1)*1000, SOMME.SI.ENS regroupe d'un coup 701100 et 701200 sous le poste 701. Le joker A2&« * » ne trouverait rien, car Excel range ces numéros de compte en nombres.

Astuces pour aller plus loin

Regroupe les sous-comptes par tranche de numéros

Un poste comme les ventes regroupe plusieurs sous-comptes, 701100 et 701200 dans le fichier. Le joker A2&"*" semble tout indiqué, mais il ne trouve rien, car Excel stocke ces numéros de compte comme des nombres et un joker ne s'applique qu'à du texte. Garde plutôt les comptes compris entre A2*1000 et (A2+1)*1000, ce qui capture tous les comptes à six chiffres qui commencent par le code du poste.

Arrondis à l'euro pour l'interne, au millier pour la direction

En interne, travaille à l'euro près avec ARRONDI(montant;0). Pour la présentation à la direction, arrondis au millier avec ARRONDI(montant;-3) et mentionne "en k euros" dans le titre du tableau. Les écarts d'arrondi disparaissent et les chiffres restent lisibles.

Utilise INDEX pour ne jamais copier-coller le budget mensuel

=INDEX(Budget!$B$2:$M$8;EQUIV(A2;Budget!$A$2:$A$8;0);MOIS(Paramètres!$B$2)) va chercher la ligne du poste et la colonne du mois étudié dans le budget annuel. Pour une date de mars, MOIS renvoie 3 et INDEX lit la troisième colonne, pour octobre la dixième. Le mois suivant, tu changes la date de l'onglet Paramètres et tout le reporting suit, sans toucher aux formules.

S'entraîner sur 3 exercices similaires au reporting financier mensuel

01

État de rapprochement bancaire automatisé

Pointer ton relevé contre la comptabilité avec RECHERCHEV, puis dresser l'état de rapprochement bancaire en deux colonnes jusqu'à l'égalité des soldes rectifiés.
Voir l'exercice

02

Matrice de décision multicritère

Construis une matrice de décision pondérée pour comparer plusieurs options sur des critères chiffrés et sortir une recommandation objective.
Voir l'exercice

03

Détecter les anomalies d'un journal comptable

Passe au crible un extrait de journal comptable en partie double pour repérer les écritures saisies deux fois, les pièces déséquilibrées et les montants hors seuil avec NB.SI.ENS, SOMME.SI et SI.
Voir l'exercice

Tu as peut-être encore une question

Sépare les sources de la synthèse. Un onglet GL reçoit toutes les écritures, un onglet Budget les douze mois de budget par poste, un onglet Paramètres le mois étudié, et l'onglet Reporting fait la synthèse. SOMME.SI.ENS consolide les écritures par compte et par mois, ARRONDI nettoie l'affichage et INDEX récupère le bon budget sans copier-coller.

Pour aller plus loin

Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.

Besoin d’aide sur Excel ?

Rejoins le Dojo pour poser tes questions, obtenir de l’aide sur tes fichiers Excel et participer chaque mois à un cours collectif en direct.

Rejoindre gratuitement

Gratuit · Sans carte bancaire