Analyses de sensibilité avec Excel

Publié le 01 juin 2011
par Sophie Marchand M.Sc., CPA, CGA, MVP

Analyses de sensibilité avec Excel

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.

Petit modèle financier

 

É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).

Analyse de sensibilité étape 1

 

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.

 

analyse de sensibilité étape 2

 

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

Variables analyse sensibilité

 
Vous obtiendrez un tableau complet couvrant tous les cas de figures possibles. Les valeurs du tableau sont les marges brutes que vous obtiendrez en faisant varier le nombre de pages par copie et le nombre de copies de votre modèle financier. 
Analyse de sensibilité sur marge brute
 
 
Au-delà de procurer une vue d’ensemble extraordinaire de la situation, le tableau est mis à jour automatiquement lorsque vous apportez des modifications à votre modèle. C’est ce qu’on appelle une analyse de sensibilité.

 


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 :

Le-CFO-masque_Commentaires-Excel-Mod-fin-v2
CFO-Masque_Formations-en-ligne_FBLa 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é.

2 réflexions sur “Analyses de sensibilité avec Excel”

  1. 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…

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

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top