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.

 

Étapes pour élaborer une analyse de sensibilité dans Excel

.

1. Construire un modèle financier souple qui met en relation les variables indépendantes et les variables dépendantes de votre problématique

 

Petit modèle financier

 

2. Choisir un 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

.

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

.

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

. .

5. Dans le coin supérieur gauche de votre tableau, inscrire “= cellule” où la cellule représente votre variable dépendante, dans notre exemple, la marge brute, donc la cellule J29

.

analyse de sensibilité étape 2

.

6. Sélectionner 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

.

7. Entrer 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 :

Jean-Philippe Provost
Écrit il y a 1 mois
Super formation !

Me permettra sans aucun doute de relevé la qualité de mes modélisations financières même si je modélise depuis plus de 10 ans.

Antoine Champagne
Écrit il y a 6 mois
Excellents outils

Des outils excellents pour bien organiser et rendre clair nos fichiers Excel. Je travaille à tous les jours avec excel et c'est bon de pouvoir bonifier et améliorer mon travail. Vraiment, Mme Marchand, vous êtes excellente; j'ai hâte à la formation suivante. Ça fait plaisir d'encourager une entreprise québécoise qui "excel" dans son domaine.

Paul Chainé
Écrit il y a 1 an
Vraiment très agréable à suivre

La narration et les explications se font avec un débit qui laisse le temps de voir et comprendre ce qui est expliqué, dans un style amical et décontracté, la simplicité des exercices est très appréciée ce qui fait qu'on les fait et c'est justement ce qui nous permet d'assimiler la matière.

Mélanie Hébert
Écrit il y a 2 ans
Très utile

J'aime que nous avons des excel que nous pouvons revoir par la suite j'aime que la formation soit faite avec des sections définies et claires et que chaque sujet ait son propre document de présentation ce qui permet de le consulter facilement au besoin

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

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 de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top