Analyse de scénarios Excel : valeur cible, solveur et tables de données

Les quatre outils d'analyse de scénarios

Chacun répond à un type de question bien précis, si bien que les connaître tous t'évite de bricoler à la main ce qu'Excel sait faire en un clic.
Voici les quatre, présentés dans l'ordre où tu vas les rencontrer, du plus simple au plus puissant.

La valeur cible remonte du résultat voulu jusqu'à l'entrée qui y mèneElle travaille à l'envers, puisque tu lui fixes la cible, par exemple 10 000 € de marge, et qu'elle cherche par approximations successives la valeur d'entrée qui y conduit, ici le CA nécessaire. Comme elle n'ajuste qu'une variable pour un seul objectif, sans aucune contrainte, c'est de loin 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 nommésTu mémorises sur les mêmes cellules un scénario pessimiste, un réaliste et un optimiste, chacun avec son propre jeu de valeurs, puis tu bascules de l'un à l'autre d'un simple clic. Excel sait même générer une synthèse côte à côte, ce qui en fait l'outil idéal dès que tu prépares un budget prévisionnel ou un business plan à plusieurs variantes.

Les tables de données balaient une ou deux variables sur tout un éventailÀ une variable, Excel calcule le résultat pour chaque valeur d'une colonne, tandis qu'à deux variables il croise deux séries dans une grille, les taux en lignes et la durée en colonnes par exemple. Tu obtiens ainsi, en une seule opération, une carte complète de toutes les possibilités au lieu d'une réponse isolée.

Le solveur optimise plusieurs variables sous contraintesLà où les trois autres s'appuient sur une variable ou un jeu d'hypothèses figé, le solveur ajuste plusieurs variables en même temps pour maximiser ou minimiser un résultat, tout en respectant les règles que tu poses, qu'il s'agisse d'un budget, d'une capacité ou d'un stock. Répartir une production sur trois machines au moindre coût ou composer un portefeuille sous contrainte de risque, il trouve la combinaison optimale là où tu y passerais des heures.

Ce qu’il faut retenir

Une variable, un objectif, aucune contrainte : c'est la valeur cible. Une ou deux variables à balayer sur toute une plage : c'est la table de données. Quelques jeux d'hypothèses à comparer : c'est le gestionnaire de scénarios. Plusieurs variables à optimiser sous contraintes : c'est le solveur. Le bon réflexe consiste à choisir l'outil le plus simple qui répond encore à ta question.

C'est l'outil à connaître en premier, parce qu'il est le plus simple et de loin le plus utilisé.
Son principe tient en une phrase, puisqu'il calcule à l'envers, là où tu fixes le résultat voulu et où Excel remonte jusqu'à l'entrée qui y mène. Pour bien le voir à l'œuvre, prenons un cas de marge tout simple.

La question est la suivante, quel chiffre d'affaires faut-il pour dégager 10 000 € de marge ? Le modèle tient sur quelques cellules à peine, avec le CA 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 directement des trois autres.

À la main, tu testerais des CA un par un jusqu'à tomber juste, ce qui peut prendre un bon moment, alors que la valeur cible fait exactement ce travail de façon instantanée, en partant de la marge voulue pour remonter directement jusqu'au CA.

Comment utiliser la valeur cible en 4 étapes

1. 1

   Pars d'un modèle qui calcule. Il te faut d'abord une cellule de résultat reliée par une formule à une cellule d'entrée, comme ici la marge en B5 qui dépend du CA en B2 (marge = CA × taux − coûts). Si la cible ne contient aucune formule, Excel ne peut pas travailler à l'envers, tout simplement parce qu'il n'a rien à inverser.

2. 2

   Ouvre la valeur cible. Passe par Données > Analyse scénarios > Valeur cible, et une petite boîte s'ouvre avec seulement trois champs, « Cellule à définir », « Valeur à atteindre » et « Cellule à modifier ». Pas un de plus, ce qui en fait l'outil le plus direct de toute la suite d'analyse.

3. 3

   Traduis ta question dans les trois champs. Indique B5 (la marge) comme cellule à définir, 10000 comme valeur à atteindre et B2 (le CA) comme cellule à modifier. En somme, tu dis quelle cellule doit atteindre quelle valeur et quelle entrée Excel a le droit de toucher, puis tu valides et il part en approximations successives.

4. 4

   Lis et valide le résultat. Excel affiche la valeur qu'il a trouvée pour B2 et l'écrit directement dans la cellule, à toi ensuite d'accepter pour la garder ou d'annuler pour revenir à l'état initial. Si le résultat te paraît bizarre, commence par vérifier que ta formule en B5 vise bien ce que tu crois.

Le savais-tu ?

La valeur cible ne calcule pas la réponse d'un coup : elle procède par approximations successives, en ajustant l'entrée jusqu'à tomber sur la cible. Pour un calcul parfaitement direct, comme CA = (marge + coûts) / taux qui donne 40 000 € ici, une vraie formule resterait donc plus précise. Son intérêt éclate dès que la chaîne de calcul se complique, là où elle trouve la réponse sans effort alors qu'une formule inverse deviendrait vite illisible.

Quand tu cherches non pas une réponse unique 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 seul coup.
C'est l'outil idéal pour une simulation d'emprunt ou de tarif, et il s'appuie le plus souvent sur la fonction VPM, celle qui calcule une mensualité.

À une variable, tu balaies une seule sérieTu places une colonne de taux (1 %, 1,5 %, 2 %…) et une formule de mensualité en haut, puis Excel remplit la mensualité correspondant à chaque taux. Tu lis ainsi l'effet sur toute la plage sans jamais recopier la formule ni saisir une valeur après l'autre.

À deux variables, tu croises deux sériesTu disposes les taux en lignes et les durées en colonnes, et la grille se remplit de la mensualité pour chaque croisement. En un seul tableau, tu obtiens la carte complète de tes possibilités d'emprunt, toutes les combinaisons taux × durée réunies côte à côte.

L’erreur fatale

La formule de résultat doit se placer en haut à gauche de la zone, c'est-à-dire au-dessus de la première colonne de valeurs quand tu travailles à une variable, faute de quoi Excel renvoie une grille vide ou fausse. Le plus sûr reste de suivre à la lettre l'ordre que te demande la boîte Données > Analyse scénarios > Table de données.

Quand la question n'est plus « quelle valeur ? » mais « quelle est la meilleure combinaison possible ? », tu changes d'outil.
Le solveur ajuste alors plusieurs variables à la fois pour maximiser ou minimiser un résultat, tout en respectant les règles que tu définis toi-même, ce qui le rend autrement plus puissant que la valeur cible.

Un exemple d'optimisation concretImagine que tu doives répartir 1 000 unités à produire sur trois machines aux coûts et aux capacités différents, en minimisant le coût total. Les variables sont les quantités confiées à chaque machine, l'objectif est de réduire le coût global, et les contraintes imposent que le total fasse bien 1 000 et que chaque machine reste sous sa capacité maximale.

Le solveur teste alors les combinaisons et te renvoie la répartition la moins chère qui respecte toutes les contraintes, là où une recherche à la main devient vite impossible dès que le nombre de variables dépasse deux ou trois.

Une fois ce réflexe acquis, le choix de l'outil coule de source, puisqu'une seule entrée pour un objectif appelle la valeur cible, le balayage d'une ou deux variables appelle la table de données, la comparaison de jeux d'hypothèses nommés appelle le gestionnaire de scénarios, et l'optimisation de plusieurs variables sous contraintes appelle le solveur.

Le conseil du pro

Le solveur est un complément qu'il faut activer une seule fois. Passe par Fichier > Options > Compléments > Compléments Excel > Atteindre, puis coche « Complément Solver » et valide. Il vient alors se loger dans l'onglet Données, tout à droite, et il y restera pour de bon.

Les outils d'audit de formules

Des cellules bien nommées t'épargnent l'enquête, mais tous les classeurs n'ont pas cette chance, et c'est là que l'onglet Formules se révèle précieux.
Il cache une boîte à outils d'audit que peu de gens utilisent et qui transforme un tableau opaque en logique limpide, à commencer par le raccourci Ctrl + ` (l'accent grave, au-dessus de Tab) qui affiche toutes les formules d'une feuille d'un coup, le tout premier geste à faire devant un fichier inconnu.
Voici les quatre outils qui méritent vraiment ta confiance.

Les antécédents et les dépendants tracent la chaîne de calculSélectionne une cellule puis va dans Formules > Repérer les antécédents , et des flèches bleues partent des cellules qui alimentent la formule, tandis que Repérer les dépendants te montre exactement l'inverse. Tu remontes ainsi à la source d'une valeur ou tu mesures l'impact d'une modification d'un seul coup d'œil.

Évaluer la formule déroule un calcul pas à pasExcel traite chaque morceau l'un après l'autre et t'en montre le résultat intermédiaire, si bien que sur une formule imbriquée qui renvoie une erreur, tu repères l'étape précise qui dérape sans avoir à la découper en plusieurs cellules pour la disséquer.

La fenêtre Espion garde l'œil sur tes cellules clésElle affiche en permanence la valeur de cellules que tu choisis, même quand tu pars travailler sur une autre feuille, ce qui est parfait pour surveiller un total ou un indicateur pendant que tu modifies des données ailleurs, sans jamais perdre le fil de l'effet produit.

Afficher les formules bascule toute la feuille en mode lectureLe raccourci Ctrl + ` remplace les résultats par les formules sur l'ensemble de la feuille, et c'est idéal pour relire la logique d'un tableau hérité, repérer une valeur saisie en dur au milieu des formules, ou encore imprimer la structure d'un modèle.

Le combo gagnant

Trois outils s'enchaînent pour décortiquer un fichier inconnu. Commence par Ctrl + ` pour voir toutes les formules d'un coup, puis lance Repérer les antécédents sur la cellule de résultat final afin de remonter la chaîne, et termine par Évaluer la formule sur le calcul qui te pose question. En trois gestes seulement, tu reconstruis la logique de n'importe quel classeur.

Vérifier et tracer les erreurs

Ces mêmes flèches rendent un autre service quand un calcul casse, parce qu'une erreur qui s'affiche dans une cellule vient presque toujours d'ailleurs, plus haut dans la chaîne de calcul.
L'audit te permet alors de remonter jusqu'à la vraie cause au lieu de t'acharner sur la cellule visible, et trois réflexes suffisent à couvrir l'essentiel.

Vérifier les erreurs automatiquementFormules > Vérification des erreurs passe en revue toute la feuille et te liste chaque cellule fautive, qu'il s'agisse de #REF!, de #DIV/0! ou de #N/A, avec une explication et une piste de correction. Quand un classeur hérité est criblé de triangles verts, c'est bien plus efficace que de les chasser un par un. La leçon corriger les erreurs Excel détaille chaque code, de #VALEUR! à #REF!, ainsi que la fonction SIERREUR qui affiche un message propre à leur place.

Tracer une erreur jusqu'à sa sourceUne erreur ne naît presque jamais dans la cellule où elle s'affiche, puisqu'elle remonte d'un calcul en amont, et la commande Repérer les erreurs trace justement des flèches rouges vers la cellule d'origine. Tu arrives ainsi droit sur la cause réelle, souvent une division par zéro ou une référence cassée plusieurs niveaux plus haut.

Lire les petits triangles de couleurUn triangle vert dans le coin d'une cellule signale une incohérence possible, comme un nombre stocké en texte ou une formule qui diffère de ses voisines. Ce n'est pas forcément une erreur, mais ça mérite un coup d'œil, alors clique sur le losange jaune qui apparaît pour voir ce qu'Excel a détecté.

Tu sais désormais faire parler tes hypothèses et vérifier que tes calculs tiennent la route, ce qui te donne un modèle solide et digne de confiance.
La suite logique, c'est de mettre tout ça en scène, et c'est exactement le sujet de la prochaine leçon sur le tableau de bord, où tu apprends à résumer toute une activité sur une seule page, à choisir tes KPI et à les rendre vivants avec des graphiques et des filtres.