Utilisation de Scénario manager pour l’analyse de sensibilité dans Microsoft Excel

■ Je voudrais créer les scénarios les meilleurs, les pires et les plus probables pour les ventes d’un modèle automobile d’une entreprise en faisant varier les valeurs des ventes de l’année 1, de la croissance des ventes annuelles et du prix de vente de l’année 1. Les tableaux de données pour l’analyse de sensibilité me permettent de ne varier qu’une ou deux entrées, je ne peux donc pas utiliser de tableau de données. Excel a-t-il un outil que je peux utiliser pour faire varier plus de deux entrées dans une analyse de sensibilité?

Vous pouvez utiliser le gestionnaire de scénarios pour effectuer une analyse de sensibilité en faisant varier jusqu’à 32 cellules d’entrée. Avec le gestionnaire de scénarios, vous définissez d’abord l’ensemble des cellules d’entrée que vous souhaitez faire varier. Nommez votre scénario et entrez la valeur de chaque cellule d’entrée pour chaque scénario. Enfin, sélectionnez les cellules de sortie (également appelées cellules de résultat) que vous souhaitez suivre. Le gestionnaire de scénarios crée ensuite un beau rapport contenant les entrées et les valeurs des cellules de sortie pour chaque scénario.

Réponse à la question de cet article

Cette section fournit la réponse à la question répertoriée au début de cet article.

J’aimerais créer les meilleurs, les pires et les scénarios les plus probables pour les ventes d’un modèle automobile d’une entreprise en faisant varier les valeurs des ventes de l’année 1, de la croissance des ventes annuelles et du prix de vente de l’année 1. Les tableaux de données pour l’analyse de sensibilité me permettent de ne varier qu’une ou deux entrées, je ne peux donc pas utiliser de tableau de données. Excel a-t-il un outil que je peux utiliser pour faire varier plus de deux entrées dans une analyse de sensibilité?

Supposons que vous souhaitiez créer les trois scénarios suivants liés à la valeur actuelle nette (VAN) d’une voiture, en utilisant l’exemple de l’article 15, “L’outil d’audit et le complément Inquire”.

Pour chaque scénario, vous souhaitez examiner la VAN de l’entreprise et le bénéfice après impôt de chaque année. Le travail se trouve dans le fichier . La figure 1 montre le modèle de feuille de calcul (contenu dans l’original

Modèle de feuille de calcul) et la figure ci- dessous montre le rapport de scénario (contenu dans la feuille de calcul  synthèse de Scénario).

 

FIGURE  1: Ce sont les données sur lesquelles les scénarios sont basés.

FIGURE 2 : Voici le rapport de synthèse du scénario.

Pour commencer à définir le meilleur scénario, cliquez sur l’onglet Données, puis choisissez Gestionnaire de scénarios dans le menu Analyse des hypothèses du groupe Outils de données. Cliquez sur le bouton Ajouter et remplissez la boîte de dialogue Ajouter un scénario, comme illustré à la  troisième figure .

Entrez un nom pour le scénario (Best) et sélectionnez C2: C4 comme cellules d’entrée contenant les valeurs qui définissent le scénario. Après avoir cliqué sur OK dans la boîte de dialogue Ajouter un scénario, remplissez la boîte de dialogue Valeurs du scénario avec les valeurs d’entrée qui définissent le meilleur cas, comme illustré dans la quatrième figure .

FIGURE 4 : Définissez les valeurs d’entrée pour le meilleur des cas.

Lorsque vous cliquez sur Ajouter dans la boîte de dialogue Valeurs de scénario, vous pouvez entrer les données pour les scénarios les plus probables et les plus défavorables. Après avoir entré des données pour les trois scénarios, cliquez sur OK dans la boîte de dialogue Valeurs de scénario. La boîte de dialogue Gestionnaire de scénarios, illustrée à la figure 18-5, répertorie les scénarios que vous avez créés. Lorsque vous cliquez sur Résumé dans la boîte de dialogue Gestionnaire de scénarios, vous pouvez choisir les cellules de résultat qui seront affichées dans les rapports de scénarios. La figure 6 montre comment vous avez indiqué que vous souhaitiez que le rapport récapitulatif du scénario dans la boîte de dialogue Résumé du scénario suive le bénéfice après impôt de chaque année (cellules B17: F17) ainsi que la VAN totale (cellule B19).

FIGURE 5: La boîte de dialogue Gestionnaire de scénarios affiche chaque scénario que vous définissez.

FIGURE 6 :Utilisez la boîte de dialogue Résumé du scénario pour sélectionner les cellules de résultat pour le rapport de synthèse.

Étant donné que les cellules de résultat proviennent de plusieurs plages, vous pouvez séparer les plages B17: F17 et B19 par une virgule. (Vous auriez également pu appuyer sur la touche Ctrl pour sélectionner et entrer plusieurs plages.) Après avoir sélectionné le résumé du scénario (au lieu du tableau croisé dynamique), cliquez sur OK et Excel crée la belle

Rapport récapitulatif de Scénario illustré précédemment, dans la  deuxième figure. Notez qu’Excel inclut une colonne, intitulée Valeurs actuelles, pour les valeurs qui ont été initialement placées dans la feuille de calcul. Le pire des cas perd de l’argent (une perte de 13 345,75 $), tandis que le meilleur des cas est assez rentable (un bénéfice de 226 892,67 $). Parce que le prix du pire des cas est inférieur à votre coût variable, le pire des cas perd de l’argent chaque année.

Remarques

■ Le rapport de tableau croisé dynamique de scénario dans la boîte de dialogue Résumé du scénario présente les résultats du scénario au format de tableau croisé dynamique.

■ Supposons que vous sélectionnez un scénario dans la boîte de dialogue Gestionnaire de scénarios, puis cliquez sur le bouton Afficher. Les valeurs des cellules d’entrée pour le scénario sélectionné apparaissent alors dans la feuille de calcul et Excel recalcule toutes les formules. Cet outil est idéal pour présenter un diaporama de vos scénarios.

■ Il est difficile de créer un grand nombre de scénarios avec le gestionnaire de scénarios, car vous devez saisir les valeurs de chaque scénario individuel. La simulation de Monte-Carlo (voir Chapitre 73, “Introduction à la simulation de Monte-Carlo”) facilite la création de nombreux scénarios. Lorsque vous utilisez la méthode de simulation Monte Carlo, vous pouvez trouver des informations telles que la probabilité que la VAN des flux de trésorerie d’un projet soit non négative – une mesure importante car il s’agit de la probabilité que le projet ajoute de la valeur à l’entreprise.

■ Cliquez sur le signe moins (-) dans la ligne 5 du rapport Résumé du scénario pour masquer les cellules d’hypothèse et n’afficher que les résultats. Cliquez sur le signe plus (+) pour restaurer le rapport complet.

■ Supposons que vous envoyez un fichier à plusieurs personnes et que chacun ajoute ses propres scénarios. Une fois que chaque personne vous a renvoyé le fichier contenant les scénarios, vous pouvez fusionner tous les scénarios dans un classeur en ouvrant la version du classeur de chaque personne, en cliquant sur le bouton Fusionner dans la boîte de dialogue Gestionnaire de scénarios du classeur d’origine, puis en sélectionnant le travail – livres contenant les scénarios que vous souhaitez fusionner. Excel fusionne les scénarios sélectionnés dans le classeur d’origine.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x