Gestionnaire de scénarios

Principe : Le gestionnaire de scénarios d’Excel permet de faire varier des valeurs (cellules variables) et d’observer l’influence de ces variations sur le résultat des calculs (cellules résultantes). Il est préférable de renommer les cellules variables et résultantes pour faciliter la lecture et la compréhension du tableau de résultat du gestionnaire de scénarios.

=> Le gestionnaire de scénario agit à l’inverse de la valeur cible (qui part du résultat pour trouver les données variables qui entraînent ce résultat).


Exemple 1 : On souhaite analyser les paramètres de remboursement d’un emprunt en fonction de sa durée : 5, 7 ou 9 ans pour pouvoir effectuer un choix.

  • Renommer les cellules suivantes : MontantEmprunt / DuréeEmprunt / Amortissement / KRD / TauxDintérêt / MontantIntérêts / Annuité.
  • Cellules variables : Montant emprunt / Durée emprunt / Taux d’intérêt.
  • Cellules résultantes : Amortissement / KRD / Montant des intérêts / Annuité.

Gestionnaire de scénarios

Étape 1 : Il faut identifier les cellules qui sont variables. Dans cet exemple on souhaite uniquement voir l’impact du changement de la durée d’emprunt avec les 3 scénarios (5, 7 et 9 ans) en gardant un montant d’emprunt = 100 000 € et un taux d’intérêt de 5%.

Gestionnaire de scénarios

Étape 2 : Les cellules résultantes sont le résultat de formules, il faut donc saisir les formules correspondantes avant de démarrer le gestionnaire de scénarios.

  • Amortissement = MontantEmprunt / DuréeEmprunt
  • KRD = MontantEmprunt – Amortissement
  • Montant des intérêts  = KRD x TauxDintérêt
  • Annuité = Amortissement + MontantIntérêts

Étape 3 : Une fois toutes les cellules renommées et formules des cellules résultantes saisies on peut alors lancer l’outil gestionnaire de scénarios en ajoutant nos 3 différents scénarios.

Gestionnaire de scénarios

=> Exemple avec le premier scénario : Durée emprunt = 5 ans.

Gestionnaire de scénarios

=> Ne reste plus qu’à reproduire la même opération pour les 2 autres scénarios : durée emprunt = 7 ans / 9 ans.

Étape 4 : On peut désormais lancer le gestionnaire de scénarios.

Gestionnaire de scénarios

Gestionnaire de scénarios

=> Pour sélectionner toutes les cellules résultantes il faut cliquer sur chacune d’entre elles en maintenant la touche “ctrl” enfoncée.

Étape 5 : On peut désormais analyser les résultats selon chaque scénarios préalablement défini à l’aide du gestionnaire de scénarios.

Gestionnaire de scénarios

  • Valeurs actuelles = La/Les valeurs qui étaient saisies dans le tableau au moment de la synthèse de scénarios.
  • Durée Emprunt 5 ans / 7 ans / 9 ans = Ce sont les 3 scénarios paramétrés avec le gestionnaire de scénarios.
  • En cellules variables et résultantes apparaît le nom des cellules qu’on a renommé précédemment (d’où l’importance de les renommer au départ pour en faciliter la lecture à l’arrivée).

Exemple 2 : On souhaite analyser la rentabilité d’un Hotel en fonction de 2 paramètres : Le prix moyen de la chambre et le taux moyen d’occupation.

  • Renommer les cellules suivantes : NBchambres / TxMoyenOccup / PxMoyenChambre / NbrJoursOuvrables / TauxCV / CA / CV / MCV / CF / RtExpl.
  • Cellules variables : Nombre total de chambres / Taux moyen d’occupation / Prix moyen de la chambre / Nombre de jours ouvrables / Charges variables.
  • Cellules résultantes : Chiffre d’affaires / Charges variables / Marge sur coût variable / Charges fixes / Résultat d’exploitation.

Gestionnaire de scénarios

Étape 1 : Il faut identifier les cellules qui sont variables. Dans cet exemple on souhaite uniquement voir l’impact du changement du prix moyen de la chambre et du taux moyen d’occupation (car forcément si l’offre change alors la demande change elle aussi) en fonction des 4 scénarios de l’étude statistique (ci-dessous) en gardant un nombre total de chambre = 80, un nombre de jours ouvrables = 365, un taux de charges variables = 30% et les charges fixes = 800 000 €.

Gestionnaire de scénarios

Étape 2 : Les cellules résultantes sont le résultat de formules, il faut donc saisir les formules correspondantes avant de démarrer le gestionnaire de scénarios.

  • Chiffre d’affaires = NBchambres x TxMoyenOccup x PxMoyenChambre x NbrJoursOuvrables
  • Charges variables = TauxCV x CA
  • Marge sur coût variable  = CA – CV
  • Résultat d’exploitation = MCV-CF

Étape 3 : Une fois toutes les cellules renommées et formules des cellules résultantes saisies on peut alors lancer l’outil gestionnaire de scénarios en ajoutant nos 4 différents scénarios à 2 variables.

=> On effectue la même opération que pour l’exemple 1 sauf que cette fois-ci on sélectionne 2 cellules variables (exemple ci-dessous pour le premier scénario).

Gestionnaire de scénarios

  • B7 = Taux moyen d’occupation
  • B8 = Prix moyen de la chambre

Gestionnaire de scénarios

Étape 4 : On peut désormais lancer le gestionnaire de scénarios.

Gestionnaire de scénarios

Gestionnaire de scénarios

Étape 5 : On peut désormais analyser les résultats selon chaque scénarios préalablement défini à l’aide du gestionnaire de scénarios.

Gestionnaire de scénarios

=> Voir aussi comment renommer des cellules