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) :

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)

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 affiche alors la fenêtre du Gestionnaire de scénarios, qui pour le moment ne contient aucun scénario.

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

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 :

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 nous ramène alors dans le Gestionnaire de scénarios, qui maintenant est alimenté des offres d’emprunt reçues des banques :

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) :

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 :

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) :

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

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 :

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) :

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 :

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

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