Comment créer des scénarios déroulants dans votre modèle financier


Posted on 2017-09-11 11:01:54 category:Logiciel


La méthode la plus couramment utilisée pour créer des scénarios consiste à utiliser une combinaison de formules et de listes déroulantes.Dans le modèle financier, vous créez un tableau des scénarios possibles et de leurs entrées et liez les noms de scénario à une liste déroulante de cellule d'entrée.Les entrées du modèle sont liées à la table des scénarios.Si le modèle a été construit correctement avec toutes les entrées passant par les sorties, les résultats du modèle changeront à mesure que l'utilisateur sélectionne différentes options dans la liste déroulante.

Les listes déroulantes de validation des données sont utilisées à différentes fins dans la modélisation financière, y compris l'analyse de scénarios.

Utilisation des validations de données pour modéliser des scénarios de rentabilité

Téléchargez File 0801.xlsx.Ouvrez-le et sélectionnez l'onglet 8-1-start.

De la manière dont cela a été modélisé, les entrées sont alignées dans la colonne B. Vous pouvez effectuer une analyse de sensibilité simplement en modifiant l'une des entrées - par exemple, changer les clients par opérateur d'appel dans la cellule B3 de 40 à 45, etvous verrez tous les nombres dépendants changer.Il s'agit d'une analyse de sensibilité, car vous ne modifiez qu'une seule variable.Au lieu de cela, vous allez modifier plusieurs variables à la fois dans cet exercice d'analyse de scénario complet, vous devrez donc faire plus que modifier manuellement quelques chiffres.

Pour effectuer une analyse de scénario à l'aide des listes déroulantes de validation des données, procédez comme suit:

  1. Prenez le modèle téléchargé et coupez et collez les descriptions de la colonne C à la colonne F. Vous pouvez le faire en mettant en surbrillance les cellules C6: C8, en appuyant sur Ctrl + X, en sélectionnant la cellule F6 et en appuyant sur Entrée.

    Les entrées des cellules B3 à B8 sont la plage active qui pilote le modèle et le restera.Cependant, ils doivent devenir des formules qui changent en fonction de la liste déroulante que vous allez créer.

  2. Copiez la plage de la colonne B dans les colonnes C, D et E.

    Vous pouvez le faire en mettant en surbrillance B3: B8, en appuyant sur Ctrl + C, en sélectionnant les cellules C3: E3 et en appuyant sur Entrée.Ces montants seront les mêmes pour chaque scénario jusqu'à ce que vous les modifiiez.

  3. À la ligne 2, entrez les titres Meilleur cas , Cas de base , et Pire cas.
    configurer le modèle pour l'analyse de scénario
    Configuration du modèle pour l'analyse de scénarios.

    Notez que les formules sont toujours liées aux entrées de la colonne B, comme vous pouvez le voir en sélectionnant la cellule C12 et en appuyant sur la touche de raccourci F2.

  4. Modifiez les entrées sous chaque scénario.

    Vous pouvez mettre tout ce que vous pensez être probable, mais pour faire correspondre les nombres à ceux de cet exemple, entrez les valeurs.Ignorez la colonne B pour le moment.

    entrées de scénario de modélisation financière
    Entrées pour l'analyse de scénario.

    Vous devez maintenant ajouter la liste déroulante en haut, qui va piloter vos scénarios.L'endroit où vous placez la liste déroulante n'a pas vraiment d'importance, mais il doit se trouver dans un emplacement facile à trouver, généralement en haut de la page.

  5. Dans la cellule E1, entrez le titre Scénario .
  6. Sélectionnez la cellule F1 et modifiez la mise en forme en entrée afin que l'utilisateur puisse voir que cette cellule est modifiable.

    La façon la plus simple de procéder est de suivre ces étapes:

    1. Cliquez sur l'une des cellules déjà formatées en entrée, comme la cellule E3.
    2. Appuyez sur l'icône Reproduire la mise en forme dans la section Presse-papiers sur le côté gauche de l'onglet Accueil.Votre curseur se transforme en pinceau.
    3. Sélectionnez la cellule F1 pour coller la mise en forme.

      Format Painter est normalement à usage unique.Une fois que vous avez sélectionné la cellule, le pinceau disparaîtra du curseur.Si vous souhaitez que l'outil Reproducteur de mise en forme devienne «collant» et s'applique à plusieurs cellules, double-cliquez sur l'icône lorsque vous la sélectionnez dans l'onglet Accueil.

  7. Maintenant, dans la cellule F1, sélectionnez Validation des données dans la section Outils de données de l'onglet Données.

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

  8. Dans l'onglet Paramètres, modifiez la liste déroulante Autoriser en Liste, utilisez la souris pour sélectionner la plage= $ C $ 2: $ E $ 2, puis cliquez sur OK.
    data validation financial model
    Création de la goutte de validation des données-vers le bas des scénarios.
  9. Cliquez sur la liste déroulante, qui apparaît maintenant à côté de la cellule F1, et sélectionnez l'un des scénarios (par exemple, Cas de base).

Application de formules à des scénarios

Les cellules de la colonne B pilotent toujours le modèle, et elles doivent être remplacées par des formules.Toutefois, avant d'ajouter les formules, vous devez modifier la mise en forme des cellules de la plage pour montrer qu'elles contiennent des formules au lieu de nombres codés en dur.Suivez ces étapes:

  1. Sélectionnez les cellules B3: B8, puis sélectionnez la couleur de remplissage dans le groupe Police de l'onglet Accueil.
  2. Remplacez la couleur de remplissage par un fond blanc.

    Il est très important de faire la distinction entre les formules et les cellules d'entrée dans un modèle.Vous devez indiquer clairement à tout utilisateur ouvrant le modèle que les cellules de cette plage contiennent des formules et ne doivent pas être remplacées.

Vous devez maintenant remplacer les valeurs codées en dur dans la colonne B par des formules qui changeront à mesure que la liste déroulante change.Vous pouvez le faire en utilisant un certain nombre de fonctions différentes;un HLOOKUP, une instruction IF imbriquée, un IFS et un SUMIF feront tous l'affaire.Ajoutez les formules en suivant ces étapes:

  1. Sélectionnez la cellule B3 et ajoutez une formule qui modifiera la valeur en fonction de ce qui se trouve dans la cellule F1.

    Voici ce que sera la formule sous les différentes options:

    • = RECHERCHEH ($ F $ 1, $ C $ 2: $ E 8,2,0 $)

      Notez qu'avec cette solution, vous devez changer le numéro d'index de ligne de 2 à 3 et ainsi de suite lorsque vous copiez la formule vers le bas.Au lieu de cela, vous pouvez utiliser une fonction ROW dans le troisième champ comme ceci:= HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, ROW (A3) -1,0)

    • = IF ($ F $ 1= $ C $ 2, C3, IF ($ F $ 1= $ D $ 2, D3, E3))
    • = IFS ($ F $ 1= $ C $ 2, C3, $ F $ 1= $ D $ 2, D3, $ F $ 1= $ E $ 2, E3)
    • = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)

      Comme toujours, vous avez le choix entre plusieurs options et la meilleure solution est celle qui est la plus simple et la plus facile à comprendre.Chacune de ces fonctions produira exactement le même résultat, mais devoir changer le numéro d'index de ligne dans la RECHERCHEH n'est pas robuste, et l'ajout de la ROW peut être déroutant pour un utilisateur.L'instruction IF imbriquée est délicate à construire et à suivre, et bien que la nouvelle fonction IFS soit conçue pour simplifier une fonction IF imbriquée, elle reste assez lourde.Le SUMIF est assez simple à créer et à suivre, et il est facile à développer si vous devez ajouter des scénarios supplémentaires à l'avenir.

      Notez que IFS est une nouvelle fonction disponible uniquement avec Office 365 et Excel 2016 ou version ultérieure installés.Si vous utilisez cette fonction et que quelqu'un ouvre ce modèle dans une version précédente d'Excel, elle peut afficher la formule, mais elle ne pourra pas la modifier.

  2. Copiez la formule dans la cellule B3 dans la colonne.
    scenario analysis
    L'analyse de scénario terminée.

    En utilisant un copier-coller ordinaire, vous perdrez toute votre mise en forme.Il est important de conserver la mise en forme du modèle afin que vous puissiez voir d'un coup d'œil quelles entrées sont en dollars, en pourcentages ou en nombre de clients.Utilisez Coller les formules pour conserver la mise en forme.Vous pouvez y accéder en copiant la cellule dans le presse-papiers, en mettant en évidence la plage de destination, en cliquant avec le bouton droit de la souris et en sélectionnant l'icône Coller les formules pour coller uniquement les formules et laisser la mise en forme intacte.

    Passons maintenant à la partie amusante!Il est temps de tester la fonctionnalité de scénario dans le modèle.

  3. Cliquez sur la cellule F1, modifiez la liste déroulante et regardez les sorties du modèle changer à mesure que vous basculez entre les différents scénarios.

82256