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

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 vous liez les noms des scénarios à 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écharger le fichier 0801.xlsx . Ouvrez-le et sélectionnez l'onglet intitulé 8-1-start.

La façon 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, modifiez le nombre de clients par opérateur d'appel dans la cellule B3 de 40 à 45, et vous voir tous les numéros dépendants changer. Il s'agirait 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 :

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 dans les cellules B3 à B8 sont la plage active qui pilote le modèle et le resteront. Cependant, ils doivent devenir des formules qui changent en fonction de la liste déroulante que vous allez créer.

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

Vous pouvez le faire en mettant B3:B8 en surbrillance, 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 changiez.

Dans la ligne 2 Entrez les titres meilleur des cas , le scénario de base , et le pire cas.

Mise en place 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.

Modifiez les entrées sous chaque scénario.

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

Entrées pour l'analyse de scénarios.

Vous devez maintenant ajouter la liste déroulante en haut, qui va piloter vos scénarios. Peu importe où exactement vous placez la liste déroulante, mais elle doit être à un endroit facile à trouver, généralement en haut de la page.

Dans la cellule E1, entrez le titre Scénario .

Sélectionnez la cellule F1 et modifiez le formatage en entrée afin que l'utilisateur puisse voir que cette cellule est modifiable.

La façon la plus simple de le faire est de suivre ces étapes :

Cliquez sur l'une des cellules déjà formatées en tant qu'entrée, telle que la cellule E3.

Appuyez sur l'icône Format Painter dans la section Presse-papiers sur le côté gauche de l'onglet Accueil. Votre curseur se transformera en pinceau.

Sélectionnez la cellule F1 pour coller la mise en forme.

Format Painter est normalement à usage unique. Après avoir sélectionné la cellule, le pinceau disparaîtra du curseur. Si vous souhaitez que le Format Painter devienne "collant" et s'applique à plusieurs cellules, double-cliquez sur l'icône lorsque vous la sélectionnez dans l'onglet Accueil.

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.

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.

Création des scénarios déroulants de validation des données.

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, scénario de base).

Application de formules à des scénarios

Les cellules de la colonne B pilotent toujours le modèle et doivent être remplacées par des formules. Avant d'ajouter les formules, cependant, 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:

Sélectionnez les cellules B3:B8 et sélectionnez la couleur de remplissage dans le groupe Police de l'onglet Accueil.

Remplacez la couleur de remplissage par un arrière-plan 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 au fur et à 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 :

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 quelle sera la formule sous les différentes options :

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

  Notez qu'avec cette solution, vous devez modifier 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)

• =SI($F$1=$C$2,C3,SI($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. N'importe laquelle de ces fonctions produira exactement le même résultat, mais devoir modifier le numéro d'index de ligne dans RECHERCHEH n'est pas robuste et l'ajout de la ligne peut être déroutant pour un utilisateur. L'instruction IF imbriquée est difficile à 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 à construire et à suivre, et il est facile à développer si vous devez ajouter des scénarios supplémentaires à l'avenir.

  Notez qu'IFS est une nouvelle fonction qui n'est disponible qu'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.

Copiez la formule dans la cellule B3 dans la colonne.

L'analyse de scénario terminée.

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

Maintenant pour la partie amusante ! Il est temps de tester la fonctionnalité de scénario dans le modèle.

Cliquez sur la cellule F1, modifiez la zone de liste déroulante et observez le changement des sorties du modèle lorsque vous basculez entre les différents scénarios.