Table des matières:
- Utilisation des validations de données pour modéliser les scénarios de rentabilité
- Application de formules à des scénarios
Vidéo: Excel : Réaliser des scénarios avec l'outil Table de données 2025
La méthode de construction de scénarios la plus couramment utilisée consiste à utiliser une combinaison de formules et de listes déroulantes. Dans le modèle financier, vous créez une table de scénarios possibles et leurs entrées et reliez les noms de scénarios à une zone de liste déroulante de cellule d'entrée. Les entrées du modèle sont liées à la table de scénarios. Si le modèle a été construit correctement avec toutes les entrées circulant dans les sorties, alors les résultats du modèle changeront lorsque 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 les scénarios de rentabilité
Télécharger le fichier 0801. xlsx. Ouvrez-le et sélectionnez l'onglet intitulé 8-1-start.
De 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 changeant l'une des entrées - par exemple, changer l'opérateur clients par appel de la cellule B3 de 40 à 45 vous verrez tous les nombres dépendants changer. Ce serait une analyse de sensibilité, parce que vous changez seulement une variable. Au lieu de cela, vous allez changer plusieurs variables à la fois dans cet exercice d'analyse de scénario complet, de sorte que vous devrez faire plus que peaufiner quelques chiffres manuellement.
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 copiez et collez les descriptions de la colonne C à la colonne F. Vous pouvez le faire en mettant en évidence 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 restera. 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 sélectionnant 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 changiez.
- Dans la rangée 2, entrez les titres Meilleur cas , Cas de base , et Pire cas.
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.
- Modifiez les entrées sous chaque scénario.
Vous pouvez mettre tout ce que vous pensez probable, mais pour faire correspondre les chiffres à ceux de cet exemple, entrez les valeurs. Ignorez la colonne B pour le moment.
Entrées pour l'analyse de scénarios.
Maintenant, vous devez ajouter la liste déroulante en haut, qui va conduire vos scénarios. Peu importe où exactement vous avez placé la liste déroulante, mais il devrait être dans 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 la mise en forme pour que l'utilisateur puisse voir que cette cellule est modifiable.
La façon la plus simple de procéder est de suivre ces étapes:
- Cliquez sur l'une des cellules déjà formatées en entrée, comme 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 va changer de pinceau.
- Sélectionnez la cellule F1 pour coller le formatage.
Format Painter est normalement à usage unique. Après avoir sélectionné la cellule, le pinceau disparaîtra du curseur. Si vous souhaitez que l'outil de mise en forme devienne "collant" et s'applique à plusieurs cellules, double-cliquez sur l'icône lorsque vous le 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 apparaît.
- Dans l'onglet Paramètres, modifiez la liste déroulante Autoriser à la liste, utilisez la souris pour sélectionner la plage = $ C $ 2: $ E $ 2, puis cliquez sur OK.
Création des scénarios de liste déroulante 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, Cas de base).
Application de formules à des scénarios
Les cellules de la colonne B continuent à piloter le modèle, et celles-ci doivent être remplacées par des formules. Cependant, avant d'ajouter les formules, vous devez modifier la mise en forme des cellules de la plage pour indiquer qu'elles contiennent des formules au lieu de nombres codés en dur. Procédez comme suit:
- 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 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 à 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 procédant comme suit:
- 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:
- = HLOOKUP ($ 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 que 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, il existe plusieurs options et la meilleure solution est celle qui est le plus simple et le plus facile à comprendre.Chacune de ces fonctions produira exactement le même résultat, mais avoir à changer le numéro d'index de la ligne dans le HLOOKUP n'est pas robuste, et l'ajout du 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 la fonction IF imbriquée, elle reste plutôt lourde. Le SUMIF est assez simple à construire et à suivre, et il est facile à développer si vous avez besoin d'ajouter des scénarios supplémentaires dans le futur.
Notez que IFS est une nouvelle fonction disponible uniquement avec Office 365 et Excel 2016 ou une version ultérieure. Si vous utilisez cette fonction et que quelqu'un ouvre ce modèle dans une version antérieure d'Excel, elle peut voir la formule, mais elle ne pourra pas la modifier.
- = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, 2, 0)
- Copiez la formule dans la cellule B3 dans la colonne.
L'analyse de scénario terminée.
En utilisant un copier-coller ordinaire, vous perdrez tout votre formatage. 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 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 surbrillance 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 conserver le formatage intact.
Maintenant, pour la partie amusante! Il est temps de tester la fonctionnalité du scénario dans le modèle.
- Cliquez sur la cellule F1, modifiez la liste déroulante et regardez les sorties du modèle changer lorsque vous basculez entre les différents scénarios.
