Analyse des hypothèses avec Scenario Manager

Le gestionnaire de scénarios est utile dans les cas où vous avez plus de deux variables dans l'analyse de sensibilité. Le gestionnaire de scénarios crée des scénarios pour chaque ensemble de valeurs d'entrée pour les variables considérées. Les scénarios vous aident à explorer un ensemble de résultats possibles, soutenant les éléments suivants:

• Varie jusqu'à 32 jeux d'entrées.
• Fusion des scénarios de plusieurs feuilles de calcul ou classeurs différents.

Si vous souhaitez analyser plus de 32 jeux d'entrées et que les valeurs ne représentent qu'une ou deux variables, vous pouvez utiliser des tables de données. Bien qu'elle soit limitée à une ou deux variables seulement, une table de données peut inclure autant de valeurs d'entrée différentes que vous le souhaitez. Reportez -vous à l'analyse hypothétique avec des tables de données dans ce didacticiel.

Scénarios

Un scénario est un ensemble de valeurs que Excel enregistre et peut remplacer automatiquement sur votre feuille de calcul. Vous pouvez créer et enregistrer différents groupes de valeurs en tant que scénarios sur une feuille de calcul, puis basculer entre ces scénarios pour afficher les différents résultats.

Par exemple, vous pouvez avoir plusieurs scénarios budgétaires différents qui comparent différents niveaux de revenus et dépenses possibles. Vous pouvez également avoir différents scénarios de prêt provenant de différentes sources qui comparent divers taux d'intérêt et durées de prêt possibles.

Si les informations que vous souhaitez utiliser dans les scénarios proviennent de sources différentes, vous pouvez collecter les informations dans des classeurs distincts, puis fusionner les scénarios des différents classeurs en un seul.

Une fois que vous avez tous les scénarios dont vous avez besoin, vous pouvez créer un rapport de synthèse de scénario -

• Cela intègre les informations de tous les scénarios.
• Cela vous permet de comparer les scénarios côte à côte.

Gestionnaire de scénario

Le gestionnaire de scénarios est l'un des outils d'analyse hypothétique d'Excel.

Pour créer un rapport d'analyse avec Scenario Manager, vous devez suivre ces étapes -

Step 1 - Définissez l'ensemble des valeurs initiales et identifiez les cellules d'entrée que vous souhaitez modifier, appelées cellules changeantes.

Step 2 - Créez chaque scénario, nommez le scénario et entrez la valeur de chaque cellule d'entrée changeante pour ce scénario.

Step 3- Sélectionnez les cellules de sortie, appelées cellules de résultat que vous souhaitez suivre. Ces cellules contiennent des formules dans l'ensemble initial de valeurs. Les formules utilisent les cellules d'entrée changeantes.

Le gestionnaire de scénarios crée un rapport contenant les valeurs d'entrée et de sortie pour chaque scénario.

Valeurs initiales des scénarios

Avant de créer plusieurs scénarios différents, vous devez définir un ensemble de valeurs initiales sur lesquelles les scénarios seront basés.

Les étapes de configuration des valeurs initiales des scénarios sont:

• Définissez les cellules contenant les valeurs d'entrée.
• Nommez les cellules d'entrée de manière appropriée.
• Identifiez les cellules d'entrée avec des valeurs constantes.
• Spécifiez les valeurs des entrées constantes.
• Identifiez les cellules d'entrée avec des valeurs changeantes.
• Spécifiez les valeurs initiales pour les entrées changeantes.
• Définissez les cellules contenant les résultats. Les cellules de résultat contiennent des formules.
• Nommez les cellules de résultat de manière appropriée.
• Placez les formules dans les cellules de résultat.

Prenons l'exemple précédent de prêt. Maintenant, procédez comme suit -

• Définissez une cellule pour le montant du prêt.

  • Cette valeur d'entrée est constante pour tous les scénarios.

  • Nommez la cellule Loan_Amount.

  • Spécifiez la valeur comme 5 000 000.

• Définissez les cellules pour le taux d'intérêt, le nombre de paiements et le type (paiement au début ou à la fin du mois).

  • Ces valeurs d'entrée changeront dans les scénarios.

  • Nommez les cellules Interest_Rate, NPER et Type.

  • Spécifiez les valeurs initiales de l'analyse dans ces cellules comme 12%, 360 et 0 respectivement.

• Définissez la cellule pour l'EMI.

  • C'est la valeur du résultat.

  • Nommez la cellule EMI.

  • Placez la formule dans cette cellule comme -

    =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Votre feuille de calcul ressemble à l'illustration ci-dessous -

Comme vous pouvez le voir, les cellules d'entrée et les cellules de résultat sont dans la colonne C avec les noms donnés dans la colonne D.

Créer des scénarios

Après avoir configuré les valeurs initiales pour les scénarios, vous pouvez créer les scénarios à l'aide du gestionnaire de scénarios comme suit -

• Cliquez sur l'onglet DONNÉES sur le ruban.
• Cliquez sur What-if Analysis dans le groupe Data Tools.
• Sélectionnez Scenario Manager dans la liste déroulante.

La boîte de dialogue Gestionnaire de scénarios s'affiche. Vous pouvez observer qu'il contient un message -

“No Scenarios defined. Choose Add to.”

Vous devez créer des scénarios pour chaque ensemble de valeurs changeantes dans le gestionnaire de scénarios. Il est bon que le premier scénario soit défini avec des valeurs initiales, car cela vous permet de revenir aux valeurs initiales quand vous le souhaitez tout en affichant différents scénarios.

Créez le premier scénario avec les valeurs initiales comme suit -

• Clique le Add bouton dans la boîte de dialogue Gestionnaire de scénarios.

le Add Scenario la boîte de dialogue apparaît.

• Sous Nom du scénario, tapez Scénario 1.
• Sous Changer les cellules, entrez les références des cellules, c'est-à-dire C3, C4 et C5 en appuyant sur la touche Ctrl.

Le nom de la boîte de dialogue devient Modifier le scénario.

• Modifiez le texte dans le Comment as – Initial Values boîte.

• Sélectionnez l'option Empêcher les modifications sous Protection, puis cliquez sur OK.

le Scenario Valuesla boîte de dialogue apparaît. Les valeurs initiales que vous avez définies apparaissent dans chacune des cases de cellules changeantes.

Scenario 1 avec les valeurs initiales est créé.

Créez trois autres scénarios avec des valeurs variables dans les cellules changeantes comme suit -

• Clique le Add dans la boîte de dialogue Valeurs du scénario.

La boîte de dialogue Ajouter un scénario s'affiche. Notez que C3, C4, C5 apparaissent dans la zone Changer les cellules.

• Dans la zone Nom du scénario, tapez Scénario 2.

• Modifiez le texte dans le Comment as - Taux d'intérêt différent.

• Sélectionnez Empêcher les modifications sous Protection et cliquez sur OK.

le Scenario Valuesla boîte de dialogue apparaît. Les valeurs initiales apparaissent dans les cellules changeantes. Changer la valeur deInterest_Rate à 0.13 et cliquez Add.

le Add Scenariola boîte de dialogue apparaît. Notez que C3, C4, C5 apparaissent dans la case sous les cellules changeantes.

• Dans la zone Nom du scénario, tapez Scénario 3.

• Modifiez le texte dans le Commentboîte comme - Différent pas. des paiements.

• Sélectionnez Empêcher les modifications sous Protection et cliquez sur OK.

La boîte de dialogue Valeurs du scénario s'affiche. Les valeurs initiales apparaissent dans les cellules changeantes. Modifiez la valeur de NPER à 300 et cliquez surAdd.

le Add Scenariola boîte de dialogue apparaît. Notez que C3, C4, C5 apparaissent dans la zone Changer les cellules.

• Dans la zone Nom du scénario, tapez Scénario 4.

• Modifiez le texte dans le Comment box as - Différents types de paiement.

• Sélectionnez Empêcher les modifications sous Protection et cliquez sur OK.

le Scenario Valuesla boîte de dialogue apparaît. Les valeurs initiales apparaissent dans les cellules changeantes. Modifiez la valeur de Type sur 1. Cliquez sur OK car vous avez ajouté tous les scénarios que vous vouliez ajouter.

le Scenario Managerla boîte de dialogue apparaît. Dans la zone sous Scénarios, vous trouverez les noms de tous les scénarios que vous avez créés.

• Cliquez sur Scénario 1. Comme vous le savez, le scénario 1 contient les valeurs initiales.


Maintenant, cliquez sur Summary. La boîte de dialogue Résumé du scénario s'affiche.

Rapports récapitulatifs de scénario

Excel fournit deux types de rapports de synthèse de scénario -

• Résumé du scénario.
• Rapport de tableau croisé dynamique de scénario.

Dans la boîte de dialogue Résumé de scénario, vous pouvez trouver ces deux types de rapport.

Sélectionnez Résumé du scénario sous Type de rapport.

Résumé du scénario

dans le Result cells boîte, sélectionnez la cellule C6 (Ici, nous avions mis le PMTfonction). Cliquez sur OK.

Le rapport Résumé du scénario apparaît dans une nouvelle feuille de calcul. La feuille de calcul est nommée Résumé du scénario.

Vous pouvez observer ce qui suit dans le rapport Résumé du scénario -

• Changing Cells- Inscrit toutes les cellules utilisées comme cellules changeantes. Comme vous avez nommé les cellules Interest_Rate, NPER et Type, celles-ci semblent donner un sens au rapport. Sinon, seules les références de cellule seront répertoriées.

• Result Cells - Affiche la cellule de résultat spécifiée, c'est-à-dire EMI.

• Current Values - Il s'agit de la première colonne et répertorie les valeurs de ce scénario qui est sélectionné dans la boîte de dialogue Gestionnaire de scénarios avant de créer le rapport de synthèse.

• Pour tous les scénarios que vous avez créés, les cellules changeantes seront surlignées en gris.

• Dans la ligne EMI, les valeurs de résultat pour chaque scénario seront affichées.

Vous pouvez rendre le rapport plus significatif en affichant les commentaires que vous avez ajoutés lors de la création des scénarios.

• Cliquez sur le bouton + à gauche de la ligne contenant les noms de scénario. Les commentaires des scénarios apparaissent dans la ligne sous les noms des scénarios.

Scénarios de différentes sources

Supposons que vous obteniez les scénarios de trois sources différentes et que vous deviez préparer le rapport de synthèse de scénario dans un classeur principal. Vous pouvez le faire en fusionnant les scénarios de différents classeurs dans le classeur principal. Suivez les étapes ci-dessous -

• Supposons que les scénarios se trouvent dans les classeurs, Bank1_Scenarios, Bank2_Scenarios et Bank3_Scenarios. Ouvrez les trois classeurs.

• Ouvrez le classeur principal, dans lequel vous avez les valeurs initiales.

• Cliquez sur DATA> What-if Analysis> Scenario Manager dans le classeur principal.

le Scenario Manager La boîte de dialogue apparaît.

Comme vous pouvez le constater, il n'y a pas de scénario car vous n'en avez pas encore ajouté. Cliquez surMerge.

La boîte de dialogue Scénarios de fusion s'affiche.

Comme vous pouvez le voir, sous Fusionner les scénarios à partir de, vous avez deux cases -

• Book
• Sheet

Vous pouvez sélectionner une feuille de calcul spécifique à partir d'un classeur spécifique contenant les scénarios que vous souhaitez ajouter à vos résultats. Cliquez sur la flèche déroulante deBook pour voir les classeurs.

Note - Les classeurs correspondants doivent être ouverts pour apparaître dans cette liste.

Sélectionnez le livre - Bank1_Scenarios.

La feuille Bank1 s'affiche. Au bas de la boîte de dialogue, le nombre de scénarios trouvés sur la feuille source est affiché. Cliquez sur OK.

La boîte de dialogue Gestionnaire de scénarios s'affiche. Les deux scénarios qui ont été fusionnés dans le classeur principal seront répertoriés sous Scénarios.

Clique le Mergebouton. leMerge Scenariosla boîte de dialogue apparaît. Maintenant, sélectionnezBank2_Scenarios dans la liste déroulante de la zone Livre.

La feuille Bank2 est affichée. Au bas de la boîte de dialogue, le nombre de scénarios trouvés sur la feuille source est affiché. Cliquez sur OK.

le Scenario ManagerLa boîte de dialogue apparaît. Les quatre scénarios qui ont été fusionnés dans le classeur principal sont répertoriés sous Scénarios.

Clique le Mergebouton. leMerge Scenariosla boîte de dialogue apparaît. Maintenant, sélectionnezBank3_Scenarios dans la liste déroulante de la zone Livre.

La feuille Bank3 s'affiche. Au bas de la boîte de dialogue, le nombre de scénarios trouvés sur la feuille source sera affiché. Cliquez sur OK.

La boîte de dialogue Gestionnaire de scénarios s'affiche. Les cinq scénarios qui ont été fusionnés dans le classeur principal seront répertoriés sous Scénarios.

Désormais, vous disposez de tous les scénarios requis pour produire le rapport de synthèse du scénario.

Cliquez sur le bouton Résumé. leScenario Summary la boîte de dialogue apparaît.

• Sélectionnez Résumé du scénario.
• Dans la zone Cellules de résultat, tapez C6 et cliquez sur OK.

Le rapport de synthèse du scénario apparaît sur une nouvelle feuille de calcul dans le classeur maître.

Affichage des scénarios

Supposons que vous présentiez vos scénarios et que vous souhaitiez passer dynamiquement d'un scénario à un autre et afficher l'ensemble des valeurs d'entrée et des valeurs de résultat du scénario correspondant.

• Cliquez sur DONNÉES> Analyse de simulation> Gestionnaire de scénarios dans le groupe Outils de données. La boîte de dialogue Gestionnaire de scénarios s'affiche. La liste des scénarios apparaît.

• Sélectionnez le scénario que vous souhaitez afficher. Cliquez surShow.

Les valeurs de la feuille de calcul sont mises à jour à celles du scénario sélectionné. Les valeurs de résultat sont recalculées.

Rapport de tableau croisé dynamique de scénario

Vous pouvez également voir le rapport de scénario sous la forme d'un tableau croisé dynamique.

• Cliquez sur le bouton Résumé dans le Scenario ManagerBoite de dialogue. La boîte de dialogue Résumé du scénario s'affiche.

• Sélectionnez le Scenario PivotTable report sous Type de rapport.

• Tapez C6 dans le Result cells boîte.

Le rapport de tableau croisé dynamique de scénario apparaît dans une nouvelle feuille de calcul.