Nous avons déjà expliqué qu’un modèle financier devait inclure assez de flexibilité pour répondre instantanément aux questions du type: qu’adviendrait-il si je changeais la valeur de certaines variables indépendantes sur la valeur d’autre variables dépendantes. Si vous n’avez pas lu cet article, je vous conseille fortement de le faire avant de lire ce qui suit. Vous pouvez y accéder en cliquant ici. Dans cet article, nous allons nous concentrer spécifiquement sur les étapques qui permettent d’élaborer une analyse de sensibilité dans Excel.
Étapes pour élaborer une analyse de sensibilité dans Excel
Le CFO masqué vous propose une démarche en 5 étapes pour élaborer une analyse de sensibilité dans Excel.
Étape 1 : Construire un modèle financier
D’abord et avant tout, vous devez construire un modèle financier souple qui met en relation les variables indépendantes et les variables dépendantes de votre problématique. Autrement dit, chaque fois qu’un usager change la valeur d’une variable indépendant, cela doit affecter la valeur des variables indépendantes rattachées.
Étape 2 : Choisir les variables indépendantes à tester dans l’analyse de sensibilité
Ensuite, vous devez choisir une ou deux variables indépendantes (dans notre exemple, nous élaborons une analyse de sensibilité pour deux variables indépendantes dont la valeur change en même temps) dont vous souhaitez connaître les impacts sur une variable dépendante. Dans l’exemple ci-dessous, nous voulons savoir ce qui adviendra de la marge brute si nous changeons le nombre de copies imprimées et le nombre de pages par copie.
Étape 3 : Attribuer des valeurs aux variables indépendantes de l’analyse de sensibilité
Il vous faudra aussi attribuer des valeurs à votre ou à vos variables indépendantes, i.e. les valeurs que vous souhaitez tester. Dans notre exemple, nous désirons connaître l’impact de faire varier le nombre de copies de 5,000 à 45,000 et le nombre de pages par copie de 5 à 70 sur notre marge brute.
Étape 4 : Construire une table qui accueillera l’analyse de sensibilité
Il sera essentiel de construire une table comme celle présentée ci-dessous pour insérer votre analyse de sensibilité. Vous devrez ainsi construire un tableau avec une ligne représentant l’une de vos variables indépendantes (dans notre exemple, le nombre de copies) et une colonne représentant votre autre variable indépendante (dans notre exemple, le nombre de pages).
Dans le coin supérieur gauche de votre tableau, vous devrez inscrire “=cellule” où cellule représente l’adresse de la cellule qui comprend votre variable dépendante, dans notre exemple, la marge brute, donc la cellule J29.
Étape 5 : Générer l’analyse de sensibilité
Sélectionnez la table (en incluant les étiquettes de lignes et de colonnes) et aller dans le menu Données et choisir Table de données dans le menu Analyse Scénarios. Entrez les coordonnées de la cellule de la variable indépendante formant la ligne de votre tableau, dans notre exemple, le nombre de copies F24 et entrer les coordonnées de la cellule de la variable dépendante formant la colonne de votre tableau, dans notre exemple, le nombre de pages F25.
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
Afin d’approfondir vos connaissances en modélisation financière dans Excel, nous vous recommandons notre formation Excel – Modélisation financière (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi cette formation :
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é.
Bonjour,
J’ai l’impression que la ligne et la colonne extrêmes de la matrice (celles qui prennent les variations des deux variables) n’acceptent pas de formule, mais uniquement des valeurs saisies.
Après des jours et des nuits à galérer dessus, je viens de me rendre compte que la raison pour laquelle “ma table ne donnait pas” était qe ces lignes avaient des résultats de formules (du genre, la variable augmentée / diminuée de 25%). J’ai dû saisir manuellement les résultats de cette formule avant d’atteindre mon objectif.
Pouvez-vous me le confirmer? A moins que ce soit mon modèle qui ait un problème…
Merci par avance…
Bonjour PJ,
Vous avez effectivement raison. Les données en ligne et en colonne doivent être des paramètres fixes. Excel utilise ensuite ces paramètres fixes pour refaire les calculs dans le modèle et livrer les résultats. Seul la cellule du coin en haut à gauche doit être une formule. La formule doit être le résultat sur lequel vous souhaitez mesurer les impacts en faisant changer vos paramètres de ligne et de colonne.
En espérant avoir bien répondu à votre question.
Au plaisir,
Sophie