Chapitre 3 · Avancé · Leçon 22 / 26
Analyse de scénarios
« Et si on baissait le prix de 5 % ? » « Et s'il faut 12 000 € de marge, quel CA viser ? » « Quelle répartition minimise nos coûts ? » Ces questions reviennent dans tous les métiers, et Excel sait y répondre sans que tu tâtonnes à la main. Les outils d'analyse de scénarios calculent à ta place la réponse à un « et si », du cas le plus simple à l'optimisation la plus pointue.
L'analyse de scénarios regroupe les outils Excel qui simulent l'effet de différentes hypothèses sur un modèle de calcul. Tous se trouvent dans Données > Analyse scénarios (sauf le solveur, à activer une fois). Au lieu de modifier tes données et de noter les résultats à la main, tu décris ta question, Excel explore et te donne la réponse. Quatre outils, du plus simple au plus puissant.
Le prérequis commun : un modèle qui calcule
Toutes ces méthodes supposent que tes cellules sont reliées par des formules : une entrée alimente un calcul qui produit un résultat. Sans cette chaîne, rien à simuler. Plus ton modèle est propre et nommé, plus l'analyse de scénarios est facile à mettre en place et à relire.
Les quatre outils d'analyse de scénarios
Chaque outil répond à un type de question différent. Les connaître tous t'évite de bricoler à la main ce qu'Excel sait faire en un clic. Voici les quatre, du plus simple au plus puissant.
La valeur cible part du résultat voulu et remonte à l'entrée qui y mène. Excel travaille à l'envers : tu fixes la cible (10 000 € de marge), il trouve la valeur d'entrée (le CA nécessaire). Une seule variable, un seul objectif, aucune contrainte. C'est la méthode la plus rapide pour répondre à « combien faut-il pour atteindre X ? ».
Le gestionnaire de scénarios compare plusieurs jeux d'hypothèses sur les mêmes cellules : pessimiste, réaliste, optimiste. Chaque scénario mémorise un ensemble de valeurs, tu bascules de l'un à l'autre d'un clic, et Excel génère une synthèse côte à côte. Il est parfait pour un budget prévisionnel ou un business plan à plusieurs variantes.
Les tables de données montrent l'effet d'une ou deux variables sur tout un éventail de valeurs. À une variable, Excel calcule le résultat pour chaque valeur d'une colonne. À deux variables, il croise deux séries dans une grille (taux en lignes, durée en colonnes). Tu obtiens une carte complète des possibilités en une seule opération.
Le solveur est l'outil d'optimisation. Il ajuste plusieurs variables en même temps pour maximiser ou minimiser un résultat, tout en respectant des contraintes (budget, capacité, stock). Répartir une production sur trois machines pour minimiser le coût, composer un portefeuille sous contrainte de risque : le solveur trouve la combinaison optimale.
Du plus simple au plus puissant
Une variable, un objectif, pas de contrainte → valeur cible. Une ou deux variables à balayer → table de données. Quelques jeux d'hypothèses à comparer → gestionnaire de scénarios. Plusieurs variables à optimiser sous contraintes → solveur. Choisis l'outil le plus simple qui répond à ta question.
La valeur cible, pas à pas
C'est l'outil à connaître en premier, le plus simple et le plus utilisé. Il calcule à l'envers : tu fixes le résultat voulu, Excel trouve l'entrée qui y mène. Prenons un cas concret de marge.
La question : quel CA pour 10 000 € de marge ? Ton modèle tient sur quelques cellules : le chiffre d'affaires en B2, un taux de marge de 30 % en B3, des coûts fixes de 2 000 € en B4, et la marge en B5 qui découle des trois.
| A | B | |
|---|---|---|
| 1 | Poste | Valeur |
| 2 | Chiffre d'affaires | 30 000 € |
| 3 | Taux de marge | 30 % |
| 4 | Coûts fixes | 2 000 € |
| 5 | Marge | 7 000 € |
=B2*B3-B4Avec 30 000 € de CA, la marge tombe à 7 000 €. Tu veux 10 000 € : il faut donc remonter le CA, mais lequel ?
À la main, tu testerais des CA jusqu'à tomber juste. La valeur cible fait exactement ce travail, instantanément, en partant de la marge voulue pour remonter au CA.
Pars d'un modèle qui calcule
Tu as une cellule de résultat reliée par formule à une cellule d'entrée. Ici la marge en B5 dépend du CA en B2 (marge = CA × taux − coûts). La valeur cible ne fonctionne que si la cible contient une formule et l'entrée une simple valeur.
Ouvre la valeur cible
Données > Analyse scénarios > Valeur cible. Une petite boîte à trois champs s'ouvre : « Cellule à définir », « Valeur à atteindre » et « Cellule à modifier ». Rien de plus, c'est volontairement minimaliste.
Renseigne les trois champs
Cellule à définir : B5 (la marge). Valeur à atteindre : 10000. Cellule à modifier : B2 (le CA). Tu dis à Excel « trouve le CA qui donne 10 000 € de marge ». Valide, il calcule par approximations successives.
Lis et valide le résultat
Excel affiche la valeur trouvée pour B2 et l'écrit dans la cellule. Tu peux accepter (la valeur reste) ou annuler (retour à l'état initial). En quelques millisecondes, tu as la réponse qu'il aurait fallu chercher à tâtons.
La valeur cible procède par approximations successives. Pour un calcul direct (CA = (marge + coûts) / taux, soit 40 000 € ici), une formule serait plus précise. Mais dès que la chaîne de calcul se complique, la valeur cible trouve la réponse là où une formule inverse deviendrait illisible.
Les tables de données à une et deux variables
Quand tu veux voir non pas une réponse, mais tout un éventail de réponses, la table de données calcule le résultat d'une formule pour chaque valeur d'entrée d'un coup. C'est l'outil idéal pour une simulation d'emprunt ou de tarif, en s'appuyant souvent sur la fonction VPM qui calcule une mensualité.
À une variable, tu balaies une seule série. Une colonne de taux (1 %, 1,5 %, 2 %…), une formule de mensualité en haut, et Excel remplit la mensualité pour chaque taux. Tu lis l'effet du taux sur toute une plage sans recopier la formule ni saisir une valeur après l'autre.
À deux variables, tu croises deux séries. Les taux en lignes, les durées en colonnes, et la grille se remplit de la mensualité pour chaque croisement. En un seul tableau, tu vois la mensualité pour toutes les combinaisons taux × durée, la carte complète de tes possibilités d'emprunt :
Mensualité d'un prêt de 200 000 € selon le taux (lignes) et la durée (colonnes).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Taux \ Durée | 15 ans | 20 ans | 25 ans |
| 2 | 1,5 % | 1 240 € | 966 € | 800 € |
| 3 | 2,0 % | 1 288 € | 1 012 € | 848 € |
| 4 | 2,5 % | 1 333 € | 1 060 € | 897 € |
Le piège classique de la table de données
La formule de résultat doit se placer en haut à gauche de la zone (au-dessus de la première colonne de valeurs, à une variable). Une formule mal positionnée renvoie une grille vide ou fausse. Suis bien l'ordre demandé par la boîte Données > Analyse scénarios > Table de données.
Le solveur : optimiser sous contraintes
Quand la question n'est plus « quelle valeur ? » mais « quelle est la meilleure combinaison ? », tu changes d'outil. Le solveur ajuste plusieurs variables à la fois pour maximiser ou minimiser un résultat, en respectant des règles que tu poses.
Un exemple d'optimisation
Tu dois répartir 1 000 unités à produire sur trois machines aux coûts et capacités différents, en minimisant le coût total. Variables : la quantité par machine. Objectif : minimiser le coût. Contraintes : le total fait 1 000, et chaque machine reste sous sa capacité maximale.
Le solveur teste les combinaisons et renvoie la répartition la moins chère qui respecte toutes les contraintes. Impossible à trouver à la main dès qu'il y a plus de deux variables.
Activer le solveur : Fichier > Options > Compléments > Compléments Excel > Atteindre, coche « Complément Solver ». Il apparaît ensuite dans l'onglet Données, tout à droite.
Quand utiliser quoi, en une phrase
Une entrée pour un objectif → valeur cible. Balayer une ou deux variables → table de données. Comparer des jeux d'hypothèses nommés → gestionnaire de scénarios. Optimiser plusieurs variables sous contraintes → solveur.
Questions fréquentes sur l'analyse de scénarios
Va dans Données > Analyse scénarios > Valeur cible. Renseigne la cellule à atteindre (qui contient une formule), la valeur visée, et la cellule à faire varier. Excel cherche par essais successifs la valeur d'entrée qui produit le résultat voulu. Exemple : quel chiffre d'affaires faut-il pour obtenir 10 000 € de marge ?
La valeur cible ajuste une seule cellule d'entrée pour atteindre un objectif sur une seule formule, sans contrainte. Le solveur va beaucoup plus loin : il ajuste plusieurs variables à la fois, sous des contraintes (budget maximum, stock minimum), pour maximiser ou minimiser un résultat. Valeur cible pour un cas simple, solveur pour l'optimisation.
Le gestionnaire de scénarios (Données > Analyse scénarios > Gestionnaire de scénarios) mémorise plusieurs jeux d'hypothèses sur les mêmes cellules : un scénario pessimiste, un réaliste, un optimiste. Tu bascules de l'un à l'autre d'un clic et tu génères une synthèse comparative, sans réécrire les valeurs à la main à chaque fois.
Une table de données calcule automatiquement le résultat d'une formule pour toute une série de valeurs d'entrée. À une variable, elle teste une plage de taux ou de prix d'un coup ; à deux variables, elle croise deux séries dans une grille. Idéale pour voir comment une mensualité évolue selon le taux et la durée d'un emprunt.
Le solveur est un complément à activer une fois : Fichier > Options > Compléments > Compléments Excel > Atteindre, coche « Complément Solver » et valide. Il apparaît alors dans l'onglet Données, à droite. Tu y définis l'objectif, les cellules variables et les contraintes, puis tu lances la résolution.
Valeur cible : trouver une seule entrée pour atteindre un objectif précis. Table de données : voir l'effet d'une ou deux variables sur toute une plage. Gestionnaire de scénarios : comparer quelques jeux d'hypothèses nommés. Solveur : optimiser plusieurs variables sous contraintes. Du plus simple au plus puissant, selon la complexité du « et si ».
Tu veux modéliser tes décisions dans Excel ?
Le Dojo Club te donne accès à des exercices guidés sur la valeur cible, les tables de données et le solveur, des lives experts chaque semaine et une communauté active pour répondre à tous tes « et si » avec des modèles fiables.
Rejoindre le Dojo