Les tables de données dans Excel (data tables) sont très intéressantes quand vient le temps d’élaborer des analyses de sensibilité. Il s’agit d’une fonctionnalité simple à utiliser et qui permet d’ajouter de la profondeur à une analyse financière ou à un modèle financier. Toutefois, pour tirer profit de cette fonctionnalité, il faut savoir bien modéliser ses données. Lorsque j’audite des modèles financiers élaborés dans Excel, il m’arrive fréquemment de déceler des erreurs au niveau des analyses de sensibilité effectuées avec des tables de données. L’une d’entre elles concerne les variables sur lesquelles la sensibilité est appliquée et la façon dont elle est appliquée. Alors, question: Faut-il que la sensibilité soit appliquée sur les hypothèses (variables d’entrée) ou sur les résultats (variables de sortie)? Cet article vise justement à répondre à cette question.
Pour revoir comment élaborer une analyse de sensibilité dans Excel, je vous invite à relire les articles suivants:
- Exemple d’analyse de sensibilité avec la table de données dans Excel
- Meilleures pratiques d’affaires concernant l’analyse de sensibilité
Qu’est-ce que l’analyse de sensibilité?
Un des objectifs principaux d’un modèle financier est de pouvoir répondre à des questions du type « Qu’adviendrait-il si? », comme par exemple:
- Qu’adviendrait-il de mes profits si je lançais un nouveau produit?
- Qu’adviendrait-il de ma dette si j’investissais dans l’agrandissement de mes locaux?
- Qu’adviendrait-il de mon nombre d’employés si je doublais mes ventes?
Pour répondre à ce type de questions, il est possible de créer des scénarios et de faire ensuite l’analyse de ces scénarios. Il est également possible d’effectuer des analyses de sensibilité sur des variables précises du modèle financier. Par exemple, sachant que le taux de change et la demande sur mes produits vendus à l’étranger pourraient différer de mes hypothèses de base, j’aimerais me créer une table de sensibilité pour voir non seulement comment ces variables, séparément, pourraient affecter mes ventes, mais également, de façon combinée. Ceci me permettrait de constater l’impact d’une série de scénarios en un seul coup d’oeil et ainsi, de mieux comprendre le carré de sable dans lequel je joue.
Analyse de sensibilité sur les hypothèses
Dans l’exemple suivant, je calcule des revenus en $CAN à partir d’un prix de vente unitaire en $USD, un taux de change et un nombre d’unités. Vous remarquerez que les informations concernant le taux de change et les unités sont présentées dans une section distincte, intitulée Hypothèses de base, puisqu’il s’agit d’hypothèses et que j’aimerais effectuer une analyse de sensibilité éventuellement sur ces hypothèses.
Cliquez sur le tableau pour l’agrandir
À la ligne 10, je m’assure de calculer un taux de change qui correspond à la multiplication de l’hypothèse de taux de change insérée à la ligne 5 par le facteur de sensibilité du taux de change, que je fixe à 100%, à la ligne 16. Mon taux de change à la ligne 10 est donc équivalent à celui qui se trouve dans ma section d’hypothèses de base à la ligne 5.
Je fais la même chose avec le nombre d’unités.
Je peux donc créer une table de sensibilité sur mes revenus, en faisant varier le taux de change et le nombre d’unités. Lorsque mes hypothèses sont respectées à 100%, i.e. lorsque mon taux de change est à 0.75 et que mon nombre d’unités vendues est à 27,848, mes revenus en $CAN sont de 11,728,822. Par contre, lorsque le taux de change passe à 0.68 (et que le nombre d’unités demeure stable), les revenus baissent à 10,555,940 et lorsque le taux de change demeure stable et que le nombre d’unités passe à 13,924, les revenus baissent à 5,864,411. Et lorsqu’ à la fois le taux de change passe à 0.68 et le nombre d’unités à 13,924, les revenus passent à 5,277,970. Cette table de sensibilité nous renseigne donc sur le potentiel de risque et d’opportunité de notre entreprise, au niveau des revenus.
Pour recréer la table de sensibilité présentée au point précédent, sachez que j’ai caché une ligne et une colonne importantes. En effet, comme mes cellules d’entrées en colonne et mes cellules d’entrées en ligne dans ma table de données sont liées à des pourcentages (100% pour le taux de change et 100% pour le nombre d’unités), je dois utiliser les % dans ma table. Ensuite, je peux simplement convertir ces % en unités si désiré, comme je l’ai fait en cachant mes lignes et colonnes de pourcentages.
Dans l’exemple précédent, vous remarquez sans doute que l’analyse de sensibilité est effectuée sur des hypothèses, soit le taux de change et le nombre d’unités. Mais qu’adviendrait-il si vous aviez utilisé la même méthodologie pour effectuer une analyse de sensibilité directement sur les revenus en $CAN pour comprendre l’impact sur les bénéfices nets, par exemple? Et bien, si vous aviez inséré un facteur de sensibilité directement à la ligne 13, soit à la ligne des revenus en $CAN, il aurait fallu être très vigilant, et même en étant vigilant, il aurait été difficile de ne pas introduire d’erreurs dans les calculs. Pourquoi? Parce que les hypothèses de taux de change et de nombre d’unités sont fort probablement liées à d’autres sections de votre modèle financier. Ainsi, quand vous faites varier le taux de change, celui-ci n’affecte probablement pas seulement les revenus, mais il affecte probablement aussi certains coûts. Même chose pour le nombre d’unités. Donc, si vous faisiez varier seulement les revenus en $CAN (résultat de la multiplication des prix de vente en $USD, nombre d’unités et taux de change) pour connaître l’impact sur le bénéfice net, vous laisseriez les hypothèses de taux de change et d’unités intactes, alors qu’en réalité, une variation des revenus ne peut être expliquée que par une variation du taux de change ou du nombre d’unités vendues (ou du prix de vente qui n’était pas une hypothèses dans notre modèle).
Analyse de sensibilité sur les résultats
Lorsque j’audite des modèles financiers, je vois parfois des analyses de sensibilité effectuées sur des résultats (comme sur les revenus en $CAN dans l’exemple précédent). Si le taux de change et le nombre d’unités n’affectent rien d’autre dans votre modèle, alors, votre analyse de sensibilité présentera les bons résultats, mais ce scénario est peu probable. Autrement, cette analyse présentera une série de chiffres erronés, à moins que vous revoyez complètement votre modélisation. Vous devrez alors poser des hypothèses implicites quand vous élaborerez vos analyses de sensibilité, afin de vous assurez que votre modèle financier tient encore la route. Par exemple, si un utilisateur entre des hypothèses de sensibilité qui augmentent les revenus par 10% après avoir entré des hypothèses de base liées au taux de change et au nombre d’unités, vous devrez décider si ce changement dans les revenus est le résultat d’un changement de taux de change ou un changement de nombre d’unités ou les deux, afin de maintenir la logique du modèle. Il vous faudra donc user de reverse engineering et organiser votre modèle de façon à ce qu’il applique les changements sous-jacents rétroactivement au taux de change et au nombre d’unités.
Il n’existe pas de façon officielle de traiter ce type de situation. Soyez donc vigilants quand vient le temps de construire vos analyses de sensibilité et vérifiez d’abord avec les utilisateurs du modèle financier comment ils souhaitent manipuler les hypothèses de sensibilité et quelles devraient être les règles d’affaires sous-jacentes. En tout temps, vous devez vous assurer que les relations entre les différentes variables de votre modèle financier demeurent valides. Votre modèle ne devrait pas permettre aux utilisateurs de faire les 2 types d’analyses de sensibilité dans un modèle (sur les hypothèses et sur les résultats). Et surtout, vous devriez toujours communiquer clairement la logique derrière votre analyse de sensibilité, lorsque celle-ci est effectuée sur les résultats. Autrement, vous risquez d’induire les utilisateurs en erreur et vous les laisserez produire des analyses de sensibilité erronées.
Apprenez à développer des analyses de sensibilité dans Excel
Si vous désirez apprendre à développer des analyses de sensibilité dans Excel, Le CFO masqué vous offre différentes options:
- Formation en ligne sur l’analyse de sensibilité dans Excel
- Formation en classe sur la modélisation financière dans Excel (incluant un module sur l’analyse de sensibilité et de scénarios)
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble