Création de scénarios dans votre modèle financier


Posted on 2017-09-11 12:50:35 category:Logiciel


Maintenant que vous avez déterminé vos hypothèses de base qui reflètent les performances de l'entreprise, vous souhaitez également exécuter les pires et meilleurs scénarios dans votre modèle financier.Non seulement vous voulez voir comment vous pensez que l'entreprise se portera, mais vous voulez également voir comment l'entreprise se comportera si elle fait pire que les attentes ou mieux que les attentes.

L'exécution de plusieurs scénarios est une partie très importante de la modélisation financière - certains diront que c'est le point de la modélisation financière - car elle permet à l'utilisateur d'évaluer les différents résultats si certaines hypothèses finissent par être différentes..Parce que personne ne peut voir dans le futur et que les hypothèses finissent invariablement par être erronées, il est important de pouvoir voir ce qui arrive aux extrants lorsque les principaux facteurs d'hypothèse sont modifiés.

Étant donné que vous avez construit ce modèle financier intégré de telle sorte que tous les calculs soient liés soit aux cellules d'hypothèses d'entrée, soit à d'autres parties des états financiers, tout changement d'hypothèses devrait bien se faire dans tout le modèle.La preuve est cependant dans le pudding.

Saisie de vos hypothèses de scénario

Pour en revenir à la feuille de calcul Hypothèses, vous pensez que les principaux moteurs de rentabilité de votre café seront le nombre moyen de tasses que vous vendez par jour et le loyer que vous paierez.Vous pensez que réduire les tasses vendues par jour de 20 tasses et augmenter le loyer de 10 pour cent est le pire des cas, et augmenter les tasses vendues par jour de 20 tasses et réduire le loyer de 10 pour cent est un meilleur scénario raisonnable.

Tout en haut de la feuille de calcul Hypothèses, entrez les hypothèses d'entrée du scénario.

Hypothèses d'entrée de scénario
Hypothèses d'entrée de scénario.

Création d'une liste déroulante

Vous avez décidé de vos hypothèses de scénario, vous devez maintenant créer une liste déroulante, qui va guider votre analyse de scénario.Vous disposez d'un modèle financier complet et fonctionnel, vous voulez donc pouvoir basculer facilement entre vos scénarios pour voir comment les résultats changent en temps réel.Vous pouvez placer la liste déroulante du scénario sur l'un ou l'autre des états financiers, mais pour cet exemple, vous la placerez en haut du compte de résultat.

Suivez ces étapes:

  1. Accédez à la feuille de calcul IS Cash Flow et sélectionnez la cellule B1.
  2. Sélectionnez Validation des données dans la section Outils de données du ruban de données.

    La boîte de dialogue Validation des données s'affiche.

  3. Dans la liste déroulante Autoriser, sélectionnez Liste.

    Vous pouvez saisir les mots Meilleur, Base et Pire directement dans le champ, mais il est préférable de le lier à la source au cas où vous auriez mal orthographié une valeur.

  4. Dans le champ Source, saisissez=, puis cliquez sur la feuille de calcul Hypothèses et mettez en surbrillance les noms de scénario Pire, Base, Meilleur.

    Votre formule dans le champ Source doit maintenant être= Hypothèses! $ B $ 2: $ D $ 2.

  5. Cliquez sur OK.
  6. Revenez à la cellule B1 de la feuille de calcul IS Cash Flow et vérifiez que la liste déroulante fonctionne comme prévu et donne les options Meilleur, Base et Pire.
  7. Définissez la liste déroulante sur Base pour le moment.

Création de la fonctionnalité de scénario

Vous devez modifier vos hypothèses d'entrée pour le nombre de tasses vendues par jour et le loyer mensuel afin que, à mesure que la liste déroulante de la feuille de calcul IS Cash Flow change, les hypothèses d'entrée changent pour le scénario correspondant.Par exemple, lorsque Meilleur a été sélectionné dans la feuille de calcul IS Cash Flow, la valeur de la cellule B9 de la feuille de calcul Hypothèses doit être de 140 et la valeur de la cellule B23 doit être de 1 080 $.Cela devrait être fait en utilisant une formule.

Souvent, de nombreuses fonctions différentes produiront des résultats identiques ou similaires.La fonction que vous utilisez dépend de vous en tant que modélisateur financier, mais la meilleure solution sera celle qui exécute les fonctionnalités requises de la manière la plus claire et la plus simple, afin que les autres puissent comprendre ce que vous avez fait et pourquoi.

Dans ce cas, vous pouvez utiliser plusieurs options: une instruction HLOOKUP, une SUMIF ou une instruction IF.L'instruction IF, étant une fonction imbriquée, est la plus difficile à construire et la moins évolutive.Si le nombre d'options de scénario augmente, l'option de l'instruction IF est plus difficile à développer.Dans ce cas, j'ai choisi d'utiliser la RECHERCHEH avec ces étapes.

Suivez ces étapes:

  1. Sélectionnez la cellule B9 et appuyez sur le bouton Insérer une fonction dans l'onglet Formules ou à côté de la barre de formule.
  2. Recherchez HLOOKUP, appuyez sur Go et cliquez sur OK.

    La boîte de dialogue RECHERCHEH s'affiche.

  3. Cliquez sur le champ Lookup_value et sélectionnez la liste déroulante sur la feuille de calcul Flux de trésorerie IS.

    Ce sont les critères qui guident la RECHERCHEH.

  4. Appuyez sur F4 pour verrouiller la référence de cellule.

    Dans le champ Table_array, vous devez entrer le tableau que vous utilisez pour la RECHERCHEH.Notez que vos critères doivent apparaître en haut de la plage.

  5. Sélectionnez la plage qui est la table de scénario en haut - en d'autres termes, B2: D4 - et appuyez sur F4 pour verrouiller les références de cellule.

    Les références de cellule deviendront $ B $ 2: $ D $ 4.

  6. Dans le champ Row_index_num, saisissez le numéro de ligne 2.
  7. Dans le champ Range_lookup, saisissez un zéro ou un faux, car vous recherchez une correspondance exacte .
  8. Vérifiez que votre boîte de dialogue ressemble à l'image ci-dessous.
  9. Cliquez sur OK.

    La formule de la cellule B9 est= HLOOKUP ("IS Cash Flow"! B1, $ B $ 2: $ D $ 4,2,0) avec le résultat calculé de 120.

  10. Effectuez la même action dans la cellule B23 avec la formule= RECHERCHEH ("IS Cash Flow"! $ B $ 1, $ B $ 2: $ D 4,3,0 $) .

    Au lieu de recréer à nouveau la formule entière, copiez simplement la formule de la cellule B9 vers la cellule B23 et changez la référence de ligne de 2 à 3. Copier la cellule modifiera le formatnombre, vous devrez donc redéfinir le symbole monétaire sur $.

  11. Revenez à la feuille de calcul Flux de trésorerie du SI et modifiez la liste déroulante sur Meilleur.

    Vérifiez que vos hypothèses concernant le nombre moyen de tasses vendues par jour et le loyer mensuel sur la feuille de calcul Hypothèses ont changé en conséquence.Les coupes seront passées à 140 et le loyer à 1 080 $.

    Maintenant, le test important est de voir si le bilan est toujours en équilibre!

  12. Revenez à la feuille de calcul du bilan et assurez-vous que votre vérification des erreurs est toujours nulle.
  13. Testez à nouveau la liste déroulante en la remplaçant par Pire.
    Les coupes seront passées à 100 et le loyer sera de 1 320 $.Vérifiez à nouveau la vérification des erreurs sur la feuille de calcul du bilan.
HLOOKUP
Construire un scénario avec HLOOKUP.

Félicitations!Votre modèle financier entièrement intégré, ainsi que l'analyse de scénarios, est maintenant terminé!Vous pouvez télécharger une copie du modèle completed dans le fichier 1002.xlsx.

À propos de l'auteur du livre

Danielle Stein Fairhurst est la principale modélisatrice financière de Plum Solutions avec de nombreuses années d'expérience dans le domaine.Son groupe LinkedIn «Modélisation financière dans Excel» compte plus de 40 000 abonnés.Elle est également l'auteur de plusieurs articles et autres livres ainsi que d'un bulletin d'information sur la modélisation financière.

82261