Gestionnaire de scénarios - Apprendre Excel en ligne

📩 Télécharger gratuitement le fichier Excel du tutoriel :
Cliquer ici pour télécharger le fichier

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é.

É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%.

É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.

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

=> 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.

=> 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.

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.

É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 €.

É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).