Objectif du chapitre

Ce chapitre a pour objectif de vous faire acquérir une double compétence : comprendre les documents financiers essentiels pour le pilotage d’un projet ou d’une organisation, et maîtriser les outils avancés d’Excel pour les modéliser de manière professionnelle.

Sur le plan financier, vous apprendrez à :

construire et suivre un budget, en distinguant clairement prévision et engagement réel ;
analyser les écarts et les taux de consommation par phase, fournisseur ou période ;
représenter un compte de résultat synthétique sur plusieurs exercices, en classant les charges et produits selon leur nature.

Sur le plan technique, vous serez guidé pas à pas pour :

utiliser les fonctions clés d’Excel comme SOMME.SI.ENS pour agréger des données ;
structurer vos tableaux avec des plages nommées et des tableaux dynamiques ;
nettoyer et transformer les données avec Power Query ;
concevoir un modèle de données relationnel avec Power Pivot et créer des mesures DAX pertinentes ;
construire un tableau de bord interactif avec segments, graphiques dynamiques et indicateurs visuels.

À travers deux cas concrets (un suivi budgétaire et un compte de résultat pluriannuel), vous développerez une méthodologie applicable à...

Le budget : socle du pilotage financier

Dans toute organisation, le budget n’est pas seulement un outil de gestion - c’est un véritable pilier de la stratégie économique. Il condense à la fois les orientations majeures, les priorités de terrain et les limites de moyens disponibles. Sans lui, il est difficile d’imaginer un cap financier clair, encore moins d’anticiper ce que l’avenir exigera.

Plus concrètement, le budget traduit, en chiffres, les ambitions de l’organisation pour une période donnée. Il fixe les montants alloués par poste, par service ou par projet, offrant ainsi une cartographie financière complète. Généralement construit en amont de l’exercice annuel, il est validé par la direction et sert ensuite de référence contractuelle tout au long de l’exécution.

Le budget, un outil aux multiples rôles

Pour être utile, un budget ne doit pas dormir dans un tableur. C’est un instrument de gestion actif, qui joue trois rôles essentiels :

Piloter : il permet de suivre, presque en temps réel, si les dépenses réelles s’alignent avec les prévisions. En d’autres termes, il rend visibles les engagements pris, les fonds consommés, et les éventuels écarts à corriger.
Arbitrer : confronté à des ressources limitées, le budget devient un levier d’allocation stratégique. Il aide à faire des choix : tel projet mérite-t-il un effort supplémentaire ? Peut-on réduire certaines dépenses au profit d’autres plus urgentes ? Un exemple typique : si un chantier dépasse son enveloppe, l’arbitrage consistera à redéployer des fonds depuis une activité jugée moins prioritaire.
Sécuriser : enfin, un bon budget est un filet de sécurité. Il aide à prévenir les dérapages, à anticiper les tensions de trésorerie, et à encadrer les engagements pour éviter les mauvaises surprises. Il s’agit ici de gestion des risques, ni plus ni moins.

Budget et engagements : une articulation souvent floue, pourtant essentielle

Une gestion budgétaire rigoureuse repose sur une distinction nette - et trop souvent négligée -...

Créer un budget avec Power Pivot dans Excel

Qu’est-ce que Power Pivot ?

Power Pivot est un outil de Business Intelligence intégré à Excel, conçu pour bâtir des modèles de données relationnels puissants sans avoir recours à un SGBD (Système de Gestion de Base de Données, comme Access ou SQL Server) externe comme Access ou SQL Server. Autrement dit, il transforme Excel en une plateforme d’analyse de données bien plus robuste que ses fonctions traditionnelles.

Apparu en tant que complément dans Excel 2010, Power Pivot a profondément modifié la manière dont les utilisateurs avancés (power users) exploitent leurs données. Grâce à lui, finies les chaînes interminables de RECHERCHEV imbriqués et les fichiers Excel bancals gorgés de formules hasardeuses - ce fameux "Excel Hell" souvent évoqué outre-Atlantique.

Depuis la version 2016, Power Pivot est directement intégré au ruban Excel. Il permet notamment de :

charger plusieurs tables hétérogènes dans un modèle unique ;
établir des relations entre ces tables (à la manière des bases relationnelles) ;
créer des mesures puissantes avec le langage DAX (Data Analysis Expressions) ;
générer des tableaux croisés dynamiques multi-tables, bien plus rapides et fiables que leurs équivalents classiques.

DAX : un langage puissant pour l’analyse de données

DAX (Data Analysis Expressions) est le langage de formule utilisé dans Power Pivot. Il permet de créer des mesures dynamiques, des colonnes calculées et des indicateurs personnalisés, adaptés à des analyses complexes.

Contrairement aux formules Excel classiques, DAX s’appuie sur la notion de contexte de filtre, qui dépend notamment de la ligne en cours (row context), des filtres actifs (filter context) et des relations entre tables.Concrètement, une même mesure peut retourner des résultats différents selon ce contexte. Par exemple, une mesure Total des dépenses se recalculera automatiquement pour ne prendre en compte que les données correspondant au mois, au fournisseur ou aux critères de filtre appliqués dans le rapport.

Pourquoi utiliser...

Analyse budgétaire à l’aide de tableaux croisés dynamiques

Une fois les relations établies et les mesures DAX créées, il est possible d’exploiter pleinement le modèle de données via des tableaux croisés dynamiques connectés à Power Pivot.

Ces TCD permettent de croiser les données budgétaires et réelles selon plusieurs axes d’analyse (phase, période, centre de coût, etc.), tout en s’appuyant sur les mesures dynamiques précédemment définies.

Cette section présente trois tableaux croisés dynamiques illustrant différents angles d’analyse.

Premier TCD - suivi budgétaire par phase

L’objectif est d’analyser la consommation budgétaire par phase de projet, en identifiant les écarts significatifs entre budget prévisionnel et dépenses.

Au niveau du ruban d’Excel, sélectionnez l’onglet Insertion et dans le groupe Tableaux, cliquez sur le menu déroulant du bouton Tableaux croisés dynamiques et sélectionnez À partir d’un Modèle de données.

Création du TCD à partir du modèle de données

Dans la boîte de dialogue permettant de choisir l’emplacement du TCD, laissez cochée l’option Nouvelle feuille de calcul et validez par OK.

Le volet Champs de tableau croisé dynamique apparaît à l’écran.

Il contient toutes les tables du modèle de données ainsi que les mesures que nous avons créée précédemment.

Volet Champs de tableau croisé dynamique

Construction du tableau

Cliquez sur la table T_Budget et faites glisser le champ Phase au niveau de la zone Lignes.

Toujours depuis la table T_Budget, faites glisser les mesures somme_budget et %_consommation dans la zone Valeurs.

Cliquez sur la table T_Dépenses et faites glisser la mesure somme_depenses dans la zone Valeurs, entre les mesures somme_budget et %_consommation déjà déposées.

Au niveau du volet Champs de tableau croisé dynamique, le résultat est le suivant :

Paramétrage du TCD dans le volet Champs de tableau croisé dynamique

Et le tableau se présente ainsi :

Tableau croisé dynamique présentant...

Réalisation d’un tableau de bord financier avec Power Query

Objectif

Dans cette section, nous allons aborder les outils de business intelligence d’Excel au travers d’un cas concret : la réalisation d’un tableau de bord financier.

Pour rappel, Excel propose deux outils de business intelligence complémentaires : Power Query (préparation des données) et Power Pivot (modèle de données). Nous allons maintenant nous concentrer sur Power Query.

Étapes de la préparation des données

Notre but est de créer un tableau de bord qui comprend un compte de résultat sous forme de tableau croisé dynamique et un graphique de l’évolution du résultat sur trois exercices.

Pour cela, nous allons partir des données comptables sous forme de tables afin de créer un modèle de données dans Excel.

Puis nous synthétiserons ces données grâce à un tableau croisée dynamique et un graphique croisé dynamique.

Enfin, nous allons mettre en forme un tableau de bord et y ajouter de l’interactivité grâce à un segment.

Afin de réaliser notre tableau de bord, nous allons commencer par retraiter les données grâce à Power Query, puis nous créerons un modèle de données dans Excel grâce à Power Pivot.

Notre tableau...

Le retraitement des données avec Power Query

Afin de créer notre modèle de données, la table T_Balance nécessite plusieurs retraitements. Nous allons dans un premier temps « dépivoter » les colonnes concernant les années puis rajouter un signe (positif ou négatif) aux différents montants selon leur catégorie (charges ou produits).

Dépivoter les colonnes

Les données de la table T_Balance ne sont pas structurées correctement et dans l’état actuel, il n’est pas possible de les utiliser pour réaliser un tableau croisé dynamique par exemple. Il est nécessaire de « dépivoter » les colonnes liées aux années.

Dans Power Query, pivoter revient à transformer les valeurs d’une colonne en plusieurs colonnes. À l’inverse, dépivoter consiste à regrouper plusieurs colonnes en une seule colonne de valeurs, avec des lignes supplémentaires.

On retrouve le terme « pivoter » dans la désignation anglo-saxonne des tableaux croisés dynamiques : pivot table.

Pourquoi est-ce important ?

Dans la pratique, bien souvent les rapports sont présentés de la manière suivante :

Exemple de données mensuelles par personne, sous forme « pivotée » avec une colonne par mois

Cependant, pour pouvoir analyser des données à partir d’un tableau croisé dynamique par exemple, les données doivent être présentées de cette manière :

Exemple de données mensuelles par personne, sous forme « dépivotée » en lignes

Dans ce type de présentation, un champ ou colonne correspond à un type de données de nature identique qui caractérise l’enregistrement. Par exemple : mois, montant, compte comptable, ville…

Une ligne correspond à un enregistrement, chaque enregistrement est caractérisé par un ensemble de champs.

Application

Ouvrez le fichier compte_resultat_bi.xlsx.

Dans la feuille balance, sélectionnez une cellule de la table T_Balance, par exemple la cellule B5 ; dans l’onglet Données du ruban, dans le groupe Récupérer et transformer...

Conclusion

En résumé, nous avons vu comment construire un budget simple et un compte de résultat à l’aide d’Excel, en combinant rigueur comptable et outils d’analyse.

Ces fondations sont essentielles pour piloter les finances opérationnelles au quotidien. Mais pour anticiper les évolutions à venir, il est nécessaire d’aller plus loin.

Dans la partie suivante, nous apprendrons à construire un modèle financier prévisionnel complet, intégrant plusieurs scénarios, des projections automatisées et une logique cohérente entre les différentes composantes financières de l’entreprise.

Piloter les finances opérationnelles avec Excel