Le gestionnaire de scénarios dans Excel
Que se passe-t-il si la session ne réunit que 4 stagiaires ? Et si on en accueille 10 à un prix plus élevé ? Le gestionnaire de scénarios enregistre plusieurs jeux d’hypothèses dans la même feuille, les affiche d’un clic et produit un tableau de synthèse qui les compare côte à côte.
CommandeDonnées › Analyse de scénarios
Cellules variables32 maximum
ComparerBouton Synthèse
LisibilitéNommer les cellules
1
Le principe des scénarios
Un scénario est un jeu de valeurs nommé pour certaines cellules de données. Excel le mémorise dans la feuille : afficher un scénario remplace les valeurs de ces cellules, et toutes les formules se recalculent.
| Terme | Rôle | Exemple |
|---|---|---|
| Cellules variables | Les données qui changent d’un scénario à l’autre (32 au maximum) | Prix, nombre de stagiaires, tarif du formateur |
| Scénario | Un nom et une valeur pour chaque cellule variable | Pessimiste, Réaliste, Optimiste |
| Cellules résultantes | Les formules à comparer dans la synthèse | Chiffre d’affaires, marge, taux de marge |
Valeur cible ou scénarios ?La valeur cible part du résultat pour trouver une donnée. Les scénarios partent de plusieurs données pour comparer les résultats.
Des valeurs, pas des formulesLes cellules variables doivent contenir des constantes : si elles contiennent une formule, Excel la remplace par une valeur quand il affiche le scénario.
Simulateur : gestionnaire de scénarios
Gestionnaire de scénarios
Cellules variables :—Commentaire :—
Ajouter un scénario
Cellules variables :
Valeurs des cellules variables :
Synthèse de scénarios
Cellules résultantes :
2
Créer des scénarios
- Préparez le modèle et nommez les cellules importantes (Prix, Stagiaires, Marge…) : la synthèse affichera ces noms au lieu de
$B$1. - Données › Prévision › Analyse de scénarios › Gestionnaire de scénarios, puis Ajouter.
- Tapez le nom du scénario et sélectionnez les cellules variables (plage continue, ou Ctrl + clic pour des cellules séparées). Ajoutez un commentaire si besoin.
- OK : Excel demande une valeur pour chaque cellule variable (il propose les valeurs actuelles). Ajouter pour enchaîner le scénario suivant, OK pour terminer.
Commencez par un scénario « Actuel »Enregistrez d’abord les valeurs d’origine sous un scénario : vous pourrez toujours revenir au point de départ, car afficher un scénario écrase les valeurs de la feuille.
ProtectionChangements interdits et Masquer ne s’appliquent que si la feuille est protégée (Révision › Protéger la feuille, option « Modifier les scénarios » décochée).
3
Afficher, modifier, supprimer, fusionner
| Bouton | Effet |
|---|---|
| Afficher | Place les valeurs du scénario dans les cellules variables ; les formules se recalculent. Double-cliquer sur le nom produit le même effet. |
| Modifier | Change le nom, les cellules variables ou les valeurs d’un scénario existant (Excel ajoute « Modifié par … le … » au commentaire). |
| Supprimer | Retire le scénario de la liste, sans modifier les valeurs de la feuille. |
| Fusionner | Importe les scénarios d’une autre feuille ou d’un autre classeur ouvert, construit sur le même modèle. Idéal quand chaque collègue prépare ses hypothèses. |
Scénarios liés à la feuilleLes scénarios sont enregistrés dans la feuille où ils ont été créés : ils n’apparaissent pas dans le gestionnaire d’une autre feuille, d’où l’intérêt de Fusionner.
Dans le simulateurChargez les 3 scénarios, affichez-les tour à tour et observez la marge, puis créez votre propre scénario « Intra 12 stagiaires ».
4
La synthèse de scénarios
- Dans le gestionnaire, cliquez sur Synthèse.
- Choisissez Synthèse de scénarios (tableau) ou Rapport de tableau croisé dynamique.
- Sélectionnez les cellules résultantes à comparer (Ctrl + clic), par exemple le CA, la marge et le taux de marge.
- Excel crée une nouvelle feuille « Synthèse de scénarios » avec un plan : une colonne par scénario et la colonne Valeurs actuelles.
Rapport figéLa synthèse contient des valeurs, pas des formules : après une modification de scénario, recréez-la. Elle ne montre pas les scénarios masqués d’une feuille protégée.
Noms de cellulesDécochez « Les cellules sont nommées » dans le simulateur : la synthèse affiche
$B$1, $B$8… illisibles pour un lecteur.Exercice de synthèse : trois hypothèses pour une session
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Session : les cellules B1 à B9 sont déjà nommées. Créez le scénario Réaliste avec les valeurs actuelles (B1:B5).
- Ajoutez Pessimiste (400 € ; 4 stagiaires ; 2 jours ; 550 € ; 150 €) et Optimiste (480 € ; 10 stagiaires ; 2 jours ; 500 € ; 120 €).
- Affichez chaque scénario : marge de 40 €, 1 160 € et 3 160 €.
- Synthèse sur CA, Coûts, Marge et Taux de marge. Comparez avec la feuille Synthèse (corrigé). La feuille Corrigé contient les trois scénarios prêts à afficher.