Comment utiliser le Gestionnaire des scénarios d’Excel

Aujourd’hui nous allons découvrir un outil très puissant d’Excel, qui pourtant est bien méconnu, il s’agit du Gestionnaire des scénarios. Ce dernier permet d’adapter le contenu de groupes cellules en fonction de valeurs prédéfinies, afin d’en analyser les conséquences induites sur d’autres cellules. Nous allons voir dans ce tuto que lorsque l’outil est correctement maitrisé, il se révèle être une aide redoutable dans la prise de décisions stratégiques.

 

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

 

 

 

1. Présentation du cas d’étude

 

Pour illustrer cet article, nous allons analyser les différentes possibilités qui s’offrent à une entreprise dans le cadre du financement de l’un de ses projets (il s’agit d’un cas purement fictif) :

 

Excel formation - Le gestionnaire de scénarios d'Excel - 01

Pour résumer l’opération :

  • Cette entreprise envisage un investissement conséquent de 100 000 euros,
  • Elle peut financer une part de ce projet, mais pour pouvoir conserver une trésorerie suffisamment importante pour financer son activité, elle souhaite emprunter une partie de cet investissement,
  • Suite à une consultation de plusieurs banques, celle-ci disposent de trois offres qu’elle souhaite comparer

 

Pour confronter chacune des ses trois offres entre elles, cette entreprise pourrait créer trois tableaux différents avec des paramètres correspondant. Elle peut également compter sur Excel pour faire le travail à sa place, en utilisant pour cela l’outil d’analyse des scénarios !

 

2. Création des scénarios

 

Pour créer des scénarios, nous commençons par sélectionner les cellules que nous souhaitons faire varier (pour sélectionner plusieurs cellules non contiguës, nous gardons la touche [Ctrl] du clavier enfoncée)

Excel formation - Le gestionnaire de scénarios d'Excel - 02

Il s’agit des trois cellules contenant les paramètres constituant les offres de prêts, soient :

  • La cellule contenant la part que les banques demandent à l’entreprise d’autofinancer,
  • La cellule contenant le taux d’emprunt,
  • Et enfin la cellule contenant la durée de l’emprunt exprimée en nombre d’années

(Pour simplifier la tâche, ces cellules disposent d’un fond d’une couleur différente des autres cellules)

Note : Attention aux cellules sélectionnées, celles-ci ne doivent pas contenir de formule, car l’outil de Gestionnaire des scénarios va les écraser pour insérer une valeur que nous allons définir. Pensez à bien enregistrer régulièrement vos fichiers de travail pour éviter toute perte d’information !

Ensuite, nous nous rendons dans l’outil de gestion des scénarios (Données > Gestionnaire de scénarios) :

Excel formation - Le gestionnaire de scénarios d'Excel - 03

Excel affiche alors la fenêtre du Gestionnaire de scénarios, qui pour le moment ne contient aucun scénario.

Excel formation - Le gestionnaire de scénarios d'Excel - 04

Nous allons pouvoir ajouter nos propres scénarios en cliquant sur le bouton Ajouter…

Excel formation - Le gestionnaire de scénarios d'Excel - 05

Dans cette fenêtre nous ajoutons simplement un nom au scénario pour pouvoir être en mesure de l’identifier clairement par la suite, lorsque plusieurs scénarios auront été créés.

Le champ Cellules variable a été alimenté de manière automatique par Excel à l’aide des cellules que nous avions au préalable sélectionnées. Au besoin, nous pouvons cliquer sur la flèche noire pour modifier les cellules concernées.

Lorsque nous avons terminé de paramétrer le scénario, nous pouvons cliquer sur le bouton [OK] pour passer à l’étape suivante :

Excel formation - Le gestionnaire de scénarios d'Excel - 06

Dans cette nouvelle fenêtre nous pouvons définir les valeurs à donner aux cellules pour correspondre avec le scénario en cours. Celles-ci sont préremplies en fonction des valeurs déjà présentes dans la feuille de calculs.

Ces valeurs correspondent à notre premier scénario « Offre 1 ». Nous pouvons alors cliquer sur Ajouter pour créer un nouveau scénario. Lorsque tous les scénarios auront été ainsi créés, nous cliquerons sur le bouton [OK].

Excel formation - Le gestionnaire de scénarios d'Excel - 07

Excel nous ramène alors dans le Gestionnaire de scénarios, qui maintenant est alimenté des offres d’emprunt reçues des banques :

Excel formation - Le gestionnaire de scénarios d'Excel - 08

Pour mettre à jour les données de la feuille de calcul avec les paramètres des offres, il suffit de sélectionner l’une d’entre, puis de cliquer sur le bouton Afficher (il est également possible de double cliquer sur un scénario pour l’utiliser immédiatement) :

Excel formation - Le gestionnaire de scénarios d'Excel - 09

 

3. Comparer les scénarios entre eux

 

Il est ainsi très pratique de pouvoir faire varier simplement les paramètres des différents scénarios, mais il peut être aussi très utile de pouvoir les confronter les uns aux autres.

Pour cela, il suffit simplement de cliquer sur le bouton Synthèse depuis le Gestionnaire de scénario :

Excel formation - Le gestionnaire de scénarios d'Excel - 10

Une nouvelle fenêtre s’affiche alors afin de définir le type de rapport que nous souhaitons générer (sous forme de table de données, ou de tableau croisé dynamique) :

Excel formation - Le gestionnaire de scénarios d'Excel - 11

Les cellules résultantes sont les cellules qui contiennent les informations que nous souhaitons analyser. Ici ce qui va intéresser l’entreprise, c’est tout d’abord le coût mensuel du remboursement des emprunts, mais également le coût total que ce financement va représenter pour elle à la fin, lorsque celui-ci sera totalement remboursé.

Nous sélectionnons donc les cellules correspondantes en maintenant la touche [Ctrl] du clavier enfoncée.

Puis nous validons la génération du rapport en cliquant sur le bouton [OK].

Excel formation - Le gestionnaire de scénarios d'Excel - 12

Une nouvelle feuille de calculs est ainsi générée, dans laquelle les différentes offres sont maintenant confrontées.

En revanche, comme vous pouvez le constater, l’identifications des cellules n’est pas très claire car celles-ci sont identifiées par leurs coordonnées respectives ! C’est que nous allons corriger dans la prochaine partie de ce tuto.

 

4. Création de noms cellules

 

Comme nous venons de le voir, l’identification des cellules à chaque niveau de création et d’analyse des scénarios n’est toujours évidentes, car les cellules sont identifiées à partir de leurs coordonnées. Pour régler ce problème, nous allons simplement affecter des noms à ces cellules.

Pour cela, il suffit de sélectionner une cellule sur la feuille de calcul :

Excel formation - Le gestionnaire de scénarios d'Excel - 13

Puis de venir saisir le nom dans la Zone des noms de cellule, qui se trouve en haut, juste à gauche de la barre des formules (la création du nom de cellule se fait en appuyant sur la touche [Entrée] du clavier) :

Excel formation - Le gestionnaire de scénarios d'Excel - 14

Nous procédons de la manière pour toutes les cellules utilisées dans le scénario.

Note : il est possible d’utiliser des accents dans les noms de cellules, en revanche les espace ne sont pas permis

À présent, lorsque nous créons un nouveau scénario, les cellules sont clairement nommées :

Excel formation - Le gestionnaire de scénarios d'Excel - 15

Il en est de même pour le rapport d’analyse des scénarios qui est bien plus parlant maintenant :

Excel formation - Le gestionnaire de scénarios d'Excel - 16

C’est là que l’outil de Gestionnaire de scénario se révèle être un véritable outil d’aide à la prise de décision stratégique, qui même s’il n’a pas vocation à être utilisé au quotidien peut rendre bien des services, est ce doit d’être maîtrisé !

 

 



Articles qui pourraient vous intéresser

Comment créer un graphique sur deux axes, avec les ordonnées sur une échelle secondaire sur Excel

Comment obtenir des nombres aléatoires par formule sur Excel

Comment imprimer le quadrillage d’Excel

Comment identifier les doublons et triplets automatiquement sur Excel

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.