Pour analyser plusieurs scénarios en quelques clics, il y a quelques outils tout indiqués intégrés à Excel pour vous faciliter la vie. Pour vous démontrer comment utiliser les tables de données, je vais travailler avec l’exemple suivant.
Contexte : vous souhaitez analyser comment la variation des coûts des matériaux affectent les marges bénéficiaires de votre entreprise. Débutons avec un cas très simple. Le prix de vente de chaque item est de 1350 $. Les coûts de production s’élèvent à 700 $ dont 300 $ pour les salaires et 400 $ pour les matières premières. Le bénéfice brut est donc de 650$.
Nous utiliserons les tables de données pour générer plusieurs scénarios.
Qu’est-ce qu’une table de données?
Une table de données est une grille qui affiche les résultats de plusieurs calculs effectués avec différentes valeurs de variables. Les tables de données sont couramment utilisées pour l’analyse de scénarios et la simulation, permettant aux utilisateurs de voir comment les changements de variables affectent les résultats d’un modèle.
Dans notre exemple, la variable est le coût des matières premières et le résultat à analyser est la marge brute.
Créer une table de données à 1 variable
Sélectionnez maintenant la plage entière (cellules G12 à H18) et trouvez l’option « Table de données » dans le menu « Données ».
Une boîte de dialogue s’ouvrira pour vous demander où se situe la valeur à faire varier pour analyser chacun des scénarios. Dans ce cas-ci, la variable Coût des matières premières se trouve dans la cellule C14. C’est donc l’adresse C14 que nous devons saisir dans la section « Colonne » de la table de données. Nous utilisons la section « Colonne » parce que nous avons construit la table avec les valeurs à tester en colonne.
Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel. |
|---|
Si nous avions construit la table en ligne, ça aurait pris la forme suivante :
Lorsque vous appuyez sur « Ok », la zone grisée est remplie avec le résultat de chaque scénario.
Remarquez qu’une formule est générée dans chaque cellule grisée. Ça veut donc dire que les résultats sont dynamiques et s’ajusteront si votre modèle pour calculer la marge est ajusté. Par exemple, si j’ajoute des coûts d’énergie de 200 $ par unité, remarquez le changement sur les résultats.
Créer une table de données à 2 variables
Il est également possible de créer une table de données à 2 variables. J’adapte donc notre exemple pour analyser la variation de la marge bénéficiaire nette unitaire.
Voici les hypothèses de base :
Nous ferons varier les coûts de matières premières ainsi que les quantités vendues pour analyser la variation de la marge nette unitaire.
Pour y arriver, on doit créer les en-têtes d’une matrice qui contiendront les valeurs à tester. En colonne, nous testerons le coût des matières premières alors qu’en ligne, nous testerons les quantités vendues.
Dans le coin supérieur gauche de la matrice se trouvera la référence vers la cellule à recalculer pour chaque scénario.
Les résultats seront déposés dans la zone grisée.
La matrice prendra la forme suivante :
Pour lancer l’analyse de scénario, on doit tout d’abord sélectionner toute la plage à analyser (cellules G50 à O56) et trouvez l’option « Table de données » dans le menu « Données ».
Dans la boite de dialogue, on doit saisir quelles sont les cellules à faire varier pour réaliser les différents scénarios. En colonne, on doit saisir une référence vers la cellule qui contient le coût de matières premières (C51) alors qu’en ligne, on doit saisir la référence à la quantité vendue (D56).
Les résultats obtenus ressemblent à ceci :
Voici une courte vidéo qui vous démontre comment créer des simulations avec les tables de données. Bon visionnement !
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour une introduction à l’élaboration d’un tableau de bord dans Excel, et ainsi apprendre à alimenter, structurer et présenter vos données, suivez notre formation Excel – Tableaux de bord (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Tableaux de bord (niveau 1) :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
