Name: Modélisation financière avec Excel (2e édition) De l'analyse à la prise de décision
Rating: 4.6 (10 reviews)

Introduction

Objectif

Dans cette partie, 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.

Excel contient principalement deux outils de business intelligence :

Power Query : un outil pour nettoyer et préparer les données.
Power Pivot : un outil qui permet de travailler avec Excel de manière similaire à une base de donnée relationnelle, notamment en créant un modèle de données.

Plan d’attaque

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, créer un modèle de données dans Excel grâce à Power Pivot.

Notre tableau de bord se présentera de la manière suivante :

Les données nécessaires à ce cas pratique se trouvent dans le fichier compte_resultat_bi.xlsx, le fichier résolu est le fichier compte_resultat_bi_résolu.xlsx.

Présentation des données

Le fichier est composé de trois onglets.

Dans l’onglet balance se trouve une table nommée T_Balance qui représente une balance simplifiée.

Cette table est composée :

du compte comptable abrégé,
du libellé du compte,
du poste correspondant dans le compte de résultat : résultat d’exploitation, financier et exceptionnel,
de la catégorie : produits et charges d’exploitation, financier et exceptionnel,
de l’exercice comptable : 2022, 2023, 2024.

Dans l’onglet transition se trouvent deux tables :

La table T_Poste reprend les différents postes du compte de résultat. Une colonne d’index a été ajoutée afin de retrouver l’ordre correcte des éléments du compte de résultat.

La table T_Catégorie est composée des différentes catégories du compte de résultat et des postes correspondants. Un numéro d’index a été ajouté afin de retrouver l’ordre correct du compte de résultat.

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 d’y apposer un tableau croisé dynamique par exemple. Il est nécessaire de « dépivoter » les colonnes liées aux années.

Dans Power Query, « pivoter » est le fait de transformer des lignes uniques en colonnes ou de transformer les colonnes en lignes (dépivoter).

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 :

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 :

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é...

Créer un modèle de données grâce à Power Pivot

Pourquoi créer un modèle de données dans Excel ?

Un modèle de données permet d’organiser les données à la manière d’une base de données relationnelle directement dans Excel. Il s’agit d’une composante de l’outil Power Pivot d’Excel.

Il sera ainsi possible :

de gérer et analyser un ensemble de données volumineux qui ne pourrait pas être contenu dans une feuille de calcul Excel traditionnelle ;
de créer des relations de tables afin d’afficher et d’agréger les données à la demande ;
de créer des tableaux croisés dynamiques, non pas à partir d’une table unique, mais à partir d’un ensemble de tables organisées et reliées entre elles.

Vous est-il déjà arrivé de vouloir créer un tableau croisé dynamique à partir de données se trouvant dans deux tables distinctes ?

C’est en pratique ce que permet de réaliser un modèle de données dans Excel.

Les principes fondamentaux d’un modèle de données

La normalisation

D’une manière générale, la normalisation consiste à organiser les tables et les colonnes dans un modèle de données structuré afin de réduire les redondances et de préserver l’intégrité des données.

Les objectifs de la normalisation sont les suivants :

Éliminer les données redondantes pour réduire la taille des tables et améliorer la vitesse et l’efficacité du traitement.
Minimiser les erreurs et les anomalies dues aux modifications de données (insertion, mise à niveau ou suppression d’enregistrement).
Simplifier la mise en place de requêtes et de structurer la base de données pour une analyse significative.

Dans un modèle de données normalisé, chaque table doit avoir un objectif distinct et spécifique (informations sur les clients ou les fournisseurs, enregistrement d’une transaction, etc.).