Analyse de sensibilité dans Excel | Tableau de données à une et deux variables

L'analyse de sensibilité dans Excel nous aide à étudier l'incertitude dans la sortie du modèle avec les changements des variables d'entrée. Il effectue principalement des tests de résistance de nos hypothèses modélisées et conduit à des informations à valeur ajoutée.

Dans le contexte de l'évaluation DCF, l'analyse de sensibilité dans Excel est particulièrement utile en finance pour modéliser le cours de l'action ou la sensibilité de l'évaluation à des hypothèses telles que les taux de croissance ou le coût du capital.

Dans cet article, nous examinons l'analyse de sensibilité suivante dans Excel pour la modélisation DCF de manière professionnelle.

    Le plus important - Téléchargez l'analyse de sensibilité dans un modèle Excel

    Apprenez une variable et deux types de table de données variables dans Excel

    Analyse de sensibilité dans Excel

    # 1 - Analyse de sensibilité du tableau de données à une variable dans Excel

    Prenons l'exemple des finances (modèle d'actualisation des dividendes) ci-dessous pour comprendre celui-ci en détail.

    Croissance constante DDM nous donne la juste valeur d'une action en tant que valeur actuelle d'un flux infini de dividendes qui croissent à un taux constant.

    La formule de Gordon Growth est comme ci-dessous -

    Où:

    • D1 = Valeur du dividende à recevoir l'année prochaine
    • D0 = Valeur du dividende reçu cette année
    • g = Taux de croissance du dividende
    • Ke = taux de remise

    Maintenant, supposons que nous voulons comprendre à quel point le cours de l'action est sensible au rendement attendu (ke). Il y a deux façons de le faire -

    • Chemin de l'âne :-)
    • Et si l'analyse

    # 1 - Chemin de l'âne

    L'analyse de sensibilité dans Excel à l'aide de Donkey est très simple, mais difficile à mettre en œuvre lorsque de nombreuses variables sont impliquées.

    Voulez-vous continuer à faire cela compte tenu de 1000 hypothèses? Évidemment pas!

    Apprenez l'analyse de sensibilité suivante dans la technique Excel pour vous éviter les ennuis.

    # 2 - Utilisation d'une table de données variable

    La meilleure façon de faire une analyse de sensibilité dans Excel est d'utiliser des tableaux de données. Les tableaux de données fournissent un raccourci pour calculer plusieurs versions en une seule opération et un moyen d'afficher et de comparer les résultats de toutes les différentes variantes ensemble sur votre feuille de calcul. Vous trouverez ci-dessous les étapes que vous pouvez suivre pour mettre en œuvre une analyse de sensibilité unidimensionnelle dans Excel.

    Étape 1 - Créez le tableau dans un format standard

    Dans la première colonne, vous avez les hypothèses d'entrée. Dans notre exemple, les entrées sont le taux de rendement attendu (ke). Veuillez également noter qu'il y a une ligne vide (colorée en bleu dans cet exercice) sous l'en-tête du tableau. Cette ligne vide sert un objectif important pour ce tableau de données unidimensionnel que vous verrez à l'étape 2.

    Étape 2 - Liez l'entrée et la sortie de référence comme indiqué dans l'instantané ci-dessous.

    L'espace fourni par la ligne vide est maintenant utilisé pour fournir l'entrée (retour attendu Ke) et la formule de sortie. Pourquoi est-ce fait comme ça?

    Nous allons utiliser «What if Analysis», c'est une façon d'indiquer à Excel que pour l'entrée (ke), la formule correspondante fournie sur le côté droit doit être utilisée pour recalculer toutes les autres entrées.

    Étape 3 - Sélectionnez l'outil d'analyse de simulation pour effectuer une analyse de sensibilité dans Excel

    Il est important de noter que cela se subdivise en deux étapes

    • Sélectionnez la plage de table en partant du côté gauche à partir de 10% jusqu'au coin inférieur droit du tableau.
    • Cliquez sur Données -> Et si analyse -> Tables de données

    Étape 4 - La boîte de dialogue Table de données s'ouvre.

    La boîte de dialogue recherche deux entrées - Entrée de ligne et Entrée de colonne. Puisqu'il n'y a qu'une seule entrée Ke à l'étude, nous fournirons une seule entrée de colonne.

    Étape 5 - Lier l'entrée de colonne

    Dans notre cas, toutes les entrées sont fournies dans une colonne et par conséquent, nous allons créer un lien vers l'entrée de la colonne. L'entrée de colonne est liée au retour attendu (Ke). Veuillez noter que l'entrée doit être liée à la source d'origine et non à celle qui se trouve à l'intérieur du tableau

    Étape 6 - Profitez de la sortie

    # 2 - Analyse de sensibilité du tableau de données à deux variables dans Excel

    Les tableaux de données sont très utiles pour l'analyse de sensibilité dans Excel, en particulier dans le cas du DCF. Une fois le cas de base établi, l'analyse DCF doit toujours être testée selon différents scénarios de sensibilité. Les tests consistent à examiner l'effet différentiel de divers changements d'hypothèses (coût du capital, taux de croissance finale, baisse de la croissance des revenus, augmentation des besoins en capital, etc.) sur la juste valeur des actions.

    Prenons l'analyse de sensibilité dans Excel avec un exemple financier de l'analyse des flux de trésorerie actualisés d'Alibaba.

    Avec les hypothèses de base du coût du capital à 9% et un taux de croissance constant à 3% , nous sommes arrivés à la juste évaluation de 191,45 milliards de dollars.

    Supposons maintenant que vous n'êtes pas entièrement d'accord avec les hypothèses de coût du capital ou les hypothèses de taux de croissance que j'ai prises dans Alibaba IPO Valuation. Vous voudrez peut-être modifier les hypothèses et accéder à l'impact sur les évaluations.

    Une façon est de changer les hypothèses manuellement et de vérifier les résultats de chaque changement. (mot de passe - méthode Donkey!)

    Cependant, nous sommes ici pour discuter d'un moyen bien meilleur et efficace de calculer la valorisation à l'aide de l'analyse de sensibilité dans Excel qui non seulement permet de gagner du temps, mais nous fournit également un moyen de visualiser tous les détails de sortie dans un format efficace.

    Si nous effectuons l'analyse hypothétique dans Excel de manière professionnelle sur les données ci-dessus, nous obtenons le résultat suivant.

    • Ici, les entrées de ligne consistent en des variations du coût du capital ou du WACC (7% à 11%)
    • Les entrées de colonne consistent en des changements dans les taux de croissance (1% à 6%)
    • Le point d'intersection est l'évaluation d'Alibaba. Pour par exemple. en utilisant notre scénario de base de 9% WACC et de taux de croissance de 3%, nous obtenons une évaluation de 191,45 milliards de dollars.

    Dans ce contexte, voyons maintenant comment nous pouvons préparer une telle analyse de sensibilité dans Excel en utilisant des tables de données bidimensionnelles.

    Étape 1 - Créez la structure de table comme indiqué ci-dessous
    • Étant donné que nous avons deux séries d'hypothèses - le coût du capital (WACC) et les taux de croissance (g), vous devez préparer un tableau ci-dessous.
    • Vous êtes libre de changer les entrées de ligne et de colonne. Au lieu du WACC, vous pouvez avoir des taux de croissance et vice-versa.

    Étape 2 - Liez le point d'intersection à la cellule de sortie.

    Le point d'intersection des deux entrées doit être utilisé pour relier la sortie souhaitée. Dans ce cas, nous voulons voir l'effet de ces deux variables (WACC et taux de croissance) sur la valeur des fonds propres. Par conséquent, nous avons lié la cellule d'intersection à la sortie.

    Étape 3 - Ouvrir une table de données bidimensionnelle
    • Sélectionnez la table que vous avez créée
    • Cliquez ensuite sur Données -> Et si analyse -> Tables de données
    Étape 4 - Fournissez les entrées de ligne et les entrées de colonne.
    • L'entrée de ligne est le coût du capital ou Ke.
    • L'entrée de la colonne est le taux de croissance.
    • N'oubliez pas de lier ces entrées à partir de la source d'hypothèse d'origine et non de n'importe où dans le tableau

    Étape 5 - Profitez de la sortie.
    • Les valeurs de production les plus pessimistes se trouvent dans le coin supérieur droit, où le coût du capital est de 11% et le taux de croissance de seulement 1%
    • La valeur IPO d'Alibaba la plus optimiste est lorsque Ke est 7% et g est 6%
    • Le scénario de base que nous avons calculé pour des taux de croissance de 9% ke et 3% se situe au milieu.
    • Cette analyse de sensibilité bidimensionnelle dans le tableau Excel fournit aux clients une analyse de scénario simple qui permet de gagner beaucoup de temps.

    # 3 - Recherche d'objectifs pour l'analyse de sensibilité dans Excel

    • La commande Goal Seek est utilisée pour amener une formule à une valeur spécifique
    • Pour ce faire, il modifie l'une des cellules référencées par la formule
    • Goal Seek demande une référence de cellule contenant une formule (la cellule Set). Il demande également une valeur, qui est le chiffre que vous voulez que la cellule égale
    • Enfin, Goal Seek demande une cellule à modifier afin de prendre la cellule Set à la valeur requise

    Jetons un coup d'œil au DCF de l'évaluation de l'introduction en bourse d'Alibaba.

    Comme nous le savons par DCF, les taux de croissance et la valorisation sont directement liés. L'augmentation des taux de croissance augmente le prix de l'action de l'action.

    Supposons que nous voulions vérifier à quel taux de croissance le cours de l'action atteindra 80 $?

    Comme toujours, nous pouvons le faire manuellement en modifiant les taux de croissance pour continuer à voir l'impact sur le cours de l'action. Ce sera encore une fois un processus fastidieux, nous devrons peut-être saisir les taux de croissance plusieurs fois pour nous assurer que le prix de l'action correspond à 80 $ dans notre cas.

    Cependant, nous pouvons utiliser une fonction telle que Goal Seek dans Excel pour résoudre ce problème en quelques étapes simples.

    Étape 1 - Cliquez sur la cellule dont vous souhaitez définir la valeur. (La cellule Set doit contenir une formule)

    Étape 2 - Choisissez Outils, Recherche d'objectifs dans le menu, et la boîte de dialogue suivante apparaît:
    • La commande Goal Seek suggère automatiquement la cellule active comme cellule Set.
    • Cela peut être sur-tapé avec une nouvelle référence de cellule ou vous pouvez cliquer sur la cellule appropriée sur la feuille de calcul.
    • Entrez maintenant la valeur souhaitée que cette formule doit atteindre.
    • Cliquez dans la zone "À la valeur" et saisissez la valeur à laquelle vous souhaitez que la formule sélectionnée soit égale
    • Enfin, cliquez à l'intérieur de la case «En changeant de cellule» et tapez ou cliquez sur la cellule dont la valeur peut être modifiée pour obtenir le résultat souhaité
    • Cliquez sur le bouton OK et la feuille de calcul modifiera la cellule à une valeur suffisante pour que la formule atteigne votre objectif.

    Étape 3 - Profitez de la sortie.

    La recherche d'objectif vous informe également que l'objectif a été atteint

    Conclusion

    L'analyse de sensibilité dans Excel augmente votre compréhension du comportement financier et opérationnel de l'entreprise. Comme nous l'avons appris des trois approches - Tableaux de données unidimensionnels, Tableaux de données bidimensionnelles et Goal Seek, l'analyse de sensibilité est extrêmement utile dans le domaine financier, en particulier dans le contexte des évaluations - DCF ou DDM.

    Cependant, vous pouvez également acquérir une compréhension au niveau macro de l'entreprise et du secteur en général. Vous pouvez développer des cas pour refléter la sensibilité de l'évaluation aux variations des taux d'intérêt, à la récession, à l'inflation, au PIB, etc. sur l'évaluation. La réflexion et le bon sens devraient être utilisés pour développer des cas de sensibilité raisonnables et utiles.

    Et ensuite?

    Si vous avez appris quelque chose sur l'analyse de sensibilité dans Excel, veuillez laisser un commentaire ci-dessous. Laissez-moi savoir ce que vous pensez. Merci beaucoup et prenez soin de vous. Bon apprentissage!

    Vous pouvez également consulter ces articles ci-dessous pour en savoir plus sur les évaluations et la finance d'entreprise -

    Original text


    • Formule de sensibilité au prix
    • Analyse des risques - Méthodes
    • Analyse du seuil de rentabilité Excel
    • Analyse de Pareto Excel
    • <