Analyse de scénarios dans Excel : Goal Seek, Scenario Manager et Data Tables

Vous lui fournissez trois éléments :

• Définir la cellule : la cellule qui contient votre formule (la sortie)
• À la valeur : le résultat cible souhaité
• En modifiant la cellule : la cellule d’entrée qu’Excel est autorisé à ajuster

Voyons cela en pratique. 

Vous tenez une petite entreprise de carnets. Votre chiffre d’affaires actuel est de $25,000, mais votre objectif est $50,000. Le prix est fixe à 25 $, vous devez donc déterminer combien d’unités vendre. 

Voici comment procéder :

1. Allez dans Données > Analyse de scénarios > Goal Seek

2. Dans la zone Définir la cellule, cliquez sur la cellule B4 (celle de la formule de chiffre d’affaires)

3. Dans À la valeur, tapez 50000

4. Dans En modifiant la cellule, sélectionnez B2 (unités vendues)

5. Cliquez sur OK

Goal Seek ajuste B2 de 1 000 à 2 000 unités — le nombre exact pour atteindre 50 000 $ de chiffre d’affaires à 25 $ l’unité.

Atteindre un résultat précis avec Goal Seek. Image de l’auteur.

Comment utiliser Scenario Manager dans Excel

Scenario Manager est un outil d’analyse de scénarios qui vous permet d’enregistrer et d’alterner entre plusieurs jeux de valeurs d’entrée dans une même feuille. Chaque version enregistrée est un « scénario ».

Vous pouvez en créer autant que nécessaire et passer de l’un à l’autre instantanément pour comparer les résultats.

Idéal pour planifier des cas « optimiste », « pessimiste » et « attendu » sans multiplier les fichiers.

Vous lui donnez trois éléments :

• Nom du scénario : un libellé pour chaque jeu d’hypothèses
• Cellules à modifier : les cellules d’entrée qui varient selon les scénarios
• Valeurs : les nombres propres à chaque scénario

Créer un scénario

Imaginons que vous planifiiez les ventes trimestrielles de vos carnets.

Votre bénéfice actuel est de $15,000. Vous souhaitez modéliser trois scénarios : optimiste, pessimiste et attendu.

Pour créer un scénario :

1. Allez dans Données > Analyse-de-scénarios > Scenario Manager

2. Dans la boîte de dialogue Scenario Manager, cliquez sur Ajouter

3. Dans Nom du scénario, saisissez Best Case.

4. Dans Cellules à modifier, sélectionnez B2:B4.

5. Une boîte de dialogue Valeurs du scénario apparaît. Renseignez les valeurs suivantes :

• B2 = 2000, 

• B3 = $30

• B4 = $10

6. Cliquez sur OK. 

Répétez ces étapes pour les deux autres scénarios avec les valeurs ci-dessous :

Créez un scénario avec trois cas. Image de l’auteur.

Pour basculer entre les scénarios, sélectionnez-en un dans la liste et cliquez sur Afficher. Excel remplace instantanément les valeurs et votre bénéfice se met à jour.

Cliquez sur Afficher pour voir les valeurs de chaque scénario. Image de l’auteur.

Modifier des scénarios

Sélectionnez un scénario dans la liste et cliquez sur Modifier. Vous pouvez le renommer, changer les cellules concernées ou mettre à jour les valeurs. Rien ne change dans la feuille tant que vous ne cliquez pas sur Afficher

Générer des rapports de scénarios

Scenario Manager peut également créer un rapport qui compare tous les scénarios enregistrés côte à côte.

Pour générer un rapport de scénarios :

1. Dans Scenario Manager, cliquez sur Récapitulatif

2. Dans Scenario Summary, choisissez Scenario Summary

3. Dans Cellules de résultat, sélectionnez B7 (votre cellule de bénéfice)

4. Cliquez sur OK

Excel génère, sur une nouvelle feuille, un tableau récapitulatif présentant les trois scénarios côte à côte

Générer un rapport récapitulatif de scénarios. Image de l’auteur.

Comment utiliser Data Tables dans Excel

Data Tables automatisent des calculs répétés sur une plage de valeurs d’entrée. Au lieu de modifier manuellement une entrée, noter la sortie et recommencer, vous définissez la plage une fois et Excel renseigne tous les résultats d’un coup.

Trois éléments sont nécessaires :

• Plage d’entrées : les valeurs à tester
• Cellule d’entrée ligne/colonne : la cellule de votre formule que ces valeurs remplacent
• Cellule de formule : la sortie autour de laquelle le tableau est construit

Data Tables à une variable

Un Data Table à une variable teste l’effet de la variation d’une seule entrée sur le résultat d’une formule.

Par exemple, vous avez une formule de mensualité d’emprunt et souhaitez voir comment elle évolue pour des taux de 5 % à 10 %.

Pour créer un Data Table à une variable :

1. Préparez votre jeu de données. Remarque : dans mon exemple, en E1, juste au-dessus du premier taux (et non à côté de l’en-tête de colonne), je saisis =B5. Si E1 reste vide ou si la référence de formule est placée ailleurs, le tableau fonctionnera mais retournera des zéros au lieu des paiements.

Créer un Data Table à une variable. Image de l’auteur.

2. Sélectionnez ensuite toute la plage D1:E9 (y compris E1)

3. Allez dans Données > Analyse de scénarios > Data Table

4. Laissez la Cellule d’entrée ligne vide

5. Dans la Cellule d’entrée colonne, sélectionnez B4 (taux d’intérêt)

6. Cliquez sur OK.

Excel remplit le tableau avec les résultats calculés pour chaque valeur d’entrée. Observez que la mensualité n’augmente pas linéairement. Le saut de 3 % à 4 % est de 49 $, mais de 9 % à 10 % il est de 61 $. Plus le taux est élevé, plus son impact est marqué.

Résultats calculés pour chaque valeur d’entrée. Image de l’auteur.

Data Tables à deux variables

Un Data Table à deux variables teste l’effet de la variation simultanée de deux entrées sur un résultat.

Par exemple, un modèle de tarification peut dépendre à la fois du prix et des unités vendues. Un Data Table à deux variables affiche le bénéfice selon différents prix et volumes de vente, en un coup d’œil.

Commencez par préparer le jeu de données. 

Voici le jeu de données utilisé :

• En cellule D1, saisissez =B5. C’est la référence de formule, elle doit se trouver dans l’angle supérieur gauche de la grille, pas à côté d’un libellé

• Sur la ligne 1, à partir de E1, listez les volumes à tester : 500, 1000, 1500, 2000

• Dans la colonne D, à partir de D2, listez les prix à tester : $20, $25, $30, $35

Préparer un Data Table à deux variables. Image de l’auteur.

Pour créer un Data Table à deux variables :

1. Sélectionnez toute la plage D1:H5.

2. Allez dans Données > Analyse de scénarios > Data Table

3. Dans la Cellule d’entrée ligne, sélectionnez B3 (unités vendues)

4. Dans la Cellule d’entrée colonne, sélectionnez B2 (prix unitaire)

5. Cliquez sur OK.

Excel renseigne le bénéfice pour chaque combinaison de prix et d’unités vendues

Résultats calculés pour chaque valeur d’entrée. Image de l’auteur.

Les Data Tables à deux variables sont particulièrement utiles lorsque l’analyse d’une hypothèse seule ne suffit pas. Un prix peut paraître intéressant isolément, mais la rentabilité dépend aussi du nombre d’unités réellement achetées. Cette seconde variable rend le modèle bien plus éclairant.

Goal Seek vs Scenario Manager vs Data Tables

Goal Seek, Scenario Manager et Data Tables appartiennent tous à la boîte à outils d’analyse de scénarios d’Excel, mais ils ne répondent pas aux mêmes problèmes.

Voici une rapide comparaison entre eux 

Analyse de scénarios vs Solver

L’analyse de scénarios et Solver aident toutes deux à explorer différents résultats dans Excel, mais elles adressent des problématiques distinctes.

• L’analyse de scénarios teste des hypothèses. Vous modifiez une ou plusieurs entrées et observez leur effet sur le résultat. L’objectif est de comprendre les issues possibles, pas de trouver la meilleure.
• Solver fonctionne différemment. Vous lui donnez un but — maximiser le profit, minimiser les coûts — et il recherche la combinaison d’entrées qui répond le mieux à cet objectif. Il ne teste pas une hypothèse : il résout pour l’optimum.

Par exemple, un demandeur de prêt peut utiliser Goal Seek pour déterminer le montant qui donne une mensualité de 500 $. Une entreprise industrielle utilisera plutôt Solver pour définir le mix de production qui maximise le profit sous contraintes de main-d’œuvre et de matériaux.

Note : Solver ne fait pas partie des outils standard d’analyse de scénarios. C’est un module complémentaire distinct. Dans la plupart des versions d’Excel, vous devez l’activer manuellement dans les Options Excel pour le voir apparaître dans l’onglet Données.

Bonnes pratiques pour l’analyse de scénarios

Voici quelques bonnes pratiques à suivre quand vous travaillez avec l’analyse de scénarios :

1. Séparer les hypothèses des formules

Stockez les valeurs d’entrée dans des cellules dédiées et référencez ces cellules dans vos formules. Évitez de coder en dur des nombres dans les calculs.

Par exemple, cette formule est difficile à mettre à jour :

=(50*1000)-10000

Cette version est bien plus simple à maintenir :

=(B2*B3)-B4

Quand les hypothèses évoluent, vous ne modifiez que les cellules d’entrée, pas des formules partout dans le classeur.

2. Libeller clairement les scénarios

Les noms de scénarios doivent indiquer immédiatement ce qu’ils représentent. « Scénario 1 », « Scénario 2 » et « Scénario 3 » ne vous diront rien dans six mois. Préférez des libellés parlants, comme « Optimiste : croissance 15 % ».

3. Valider les entrées

La fiabilité de l’analyse dépend de celle des entrées testées. Avant de lancer une analyse, vérifiez que :

• Les pourcentages sont saisis correctement
• Les dates partagent un même format
• Les unités de mesure sont cohérentes
• Les valeurs d’entrée restent réalistes

Une prévision de revenus fondée sur +500 % de ventes peut être techniquement correcte mais inutile pour décider.

4. Documenter les hypothèses

Un modèle limpide aujourd’hui ne le sera pas forcément pour vous — ou pour d’autres — dans trois mois.

Une courte note à côté de chaque hypothèse, ne serait-ce que « basé sur les données réelles de T3 », évite bien des tâtonnements. Les documenter aide à comprendre l’origine des chiffres et facilite les mises à jour futures.

Problèmes fréquents avec l’analyse de scénarios

La plupart des soucis viennent de la structure de la feuille plus que des outils eux-mêmes. Si Goal Seek, Scenario Manager ou Data Tables renvoient des résultats inattendus, vérifiez ces points courants : cela règle souvent le problème.

1. Formules qui ne se recalculent pas 

Vous modifiez une entrée et rien ne se passe. Souvent, le mode de calcul d’Excel est passé en manuel en cours de route.

Solution : allez dans Formules > Options de calcul et vérifiez que Automatique est bien sélectionné. Si oui, appuyez sur F9 pour forcer un recalcul.

2. Références de cellules erronées

Les outils d’analyse de scénarios reposent sur des références de cellules exactes. Si vous sélectionnez les mauvaises cellules, le résultat sera faux même si Excel calcule sans erreur.

Par exemple, Goal Seek peut modifier la mauvaise cellule d’entrée. Un Data Table peut référencer une mauvaise cellule d’entrée en ligne ou en colonne. Scenario Manager peut enregistrer des valeurs pour les cellules inadaptées.

Solution : revérifiez vos cellules d’entrée avant l’analyse, contrôlez les sélections dans chaque boîte de dialogue et utilisez des libellés explicites pour clarifier le rôle de chaque cellule.

3. Mauvaise compréhension des limites de Goal Seek

Beaucoup s’attendent à ce que Goal Seek résolve des problèmes à variables multiples. Ce n’est pas le cas. Goal Seek ne modifie qu’une cellule d’entrée à la fois pour atteindre une cible.

Par exemple, il peut déterminer le volume de ventes nécessaire pour 15 000 $ de bénéfice. Il ne peut pas ajuster simultanément volume, prix et coûts pour atteindre cette cible.

Solution : utilisez Goal Seek quand une seule variable doit changer. Utilisez Scenario Manager ou Solver si plusieurs variables sont en jeu.

4. Modèles de scénarios trop complexes

Scenario Manager autorise techniquement jusqu’à 32 cellules modifiables par scénario — mais ce n’est pas toujours souhaitable.

Au-delà de 4 ou 5 variables, la comparaison devient difficile, et l’explication à d’autres encore plus. Si votre modèle exige autant de paramètres, mieux vaut le restructurer que tout entasser dans un seul scénario.

Dernières réflexions 

Tout tableur repose quelque part sur des hypothèses : un taux de croissance, un prix, un taux d’intérêt.

L’analyse de scénarios ne supprime pas ces hypothèses. Elle montre ce qui se passe si elles sont fausses — et c’est l’essentiel d’une bonne prise de décision.

Si vous débutez, n’attaquez pas un modèle avec cinq variables et trois scénarios imbriqués. Commencez petit : une entrée, une sortie, un Goal Seek, et familiarisez-vous avec la façon dont l’entrée et le résultat évoluent ensemble. Le reste viendra une fois les bases acquises.