Valeur cible, Solveur et Table de données : le guide complet pour simuler vos profits sur Excel
Dans ce tutoriel, je vais vous poser une question simple : est-ce que vous savez en ce moment précis combien de boîtes vous devez vendre ce mois-ci pour ne pas perdre d'argent ?
Et si votre manager vous posait cette question dans deux minutes, vous auriez la réponse ?
Si la réponse est non, alors cette vidéo va changer votre façon de travailler avec Excel. Parce qu'il existe dans Excel trois outils que 95% des utilisateurs n'ont jamais ouverts, et qui permettent de répondre à ce type de question en moins de deux minutes, sans calcul mental, sans formule compliquée, et sans tâtonner dans vos cellules pendant une heure.
Ces trois outils forment une boîte à outils complète pour simuler vos résultats, tester des hypothèses et prendre des décisions basées sur des chiffres réels — pas sur des intuitions.
Et restez bien jusqu'à la partie sur la Table de données — c'est celle qui impressionne le plus en réunion, et vous comprendrez rapidement pourquoi.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les données commerciales d'une boulangerie artisanale qui vend des boîtes de macarons.
Notre manager nous pose plusieurs questions auxquelles il attend des réponses précises et rapides :
- à partir de quelle quantité vendue atteignons-nous 15 000 € de chiffre d'affaires ?
- Combien faut-il vendre pour dégager 5 000 € de bénéfice ?
- Et si le marketing nous impose un prix maximum, comment optimiser nos deux variables à la fois ?
Voici le tableau de calcul que nous allons utiliser :
Pour commencer, nous allons renseigner les formules dans la colonne C. Nous cliquons sur la cellule C9 et nous saisissons la formule du chiffre d'affaires, qui est simplement la quantité multipliée par le prix :
=C7*C8
Nous nous plaçons ensuite en C13 pour calculer les coûts variables totaux, lesquels augmentent en proportion des quantités vendues :
=C11*C7
En C14, les coûts fixes ne bougent pas quelle que soit la production, donc nous pointons simplement sur la valeur :
=C12
Enfin, en C15, le bénéfice net est la différence entre le chiffre d'affaires et l'ensemble des coûts :
=C9-C13-C14
Notre modèle est désormais entièrement dynamique : si nous modifions la quantité en C7 ou le prix en C8, toutes les cellules calculées se mettent à jour instantanément.
C'est exactement ce comportement dont les outils d’analyse de scénarios ont besoin pour fonctionner correctement.
2. La Valeur Cible : trouver la quantité pour atteindre un objectif de chiffre d'affaires
2.1. Comprendre le principe
La Valeur Cible est l'outil le plus simple des trois.
Son fonctionnement est très intuitif : nous lui indiquons une cellule résultat (par exemple le chiffre d'affaires), une valeur cible à atteindre (par exemple 15 000 €), et une cellule variable qu'Excel est autorisé à modifier (par exemple la quantité).
Excel va alors faire varier la cellule variable par itérations successives jusqu'à ce que le résultat s'approche de notre cible au maximum.
C'est exactement comme si nous tâtonnions manuellement en changeant la quantité en C7 jusqu'à voir apparaître 15 000 € en C9 — sauf qu'Excel fait ce travail en une fraction de seconde.
2.2. Lancer la Valeur Cible
Nous commençons par cliquer sur la cellule C9, qui contient notre chiffre d'affaires.
Il est important de sélectionner d'abord la cellule objective avant d'ouvrir l'outil, car Excel la propose automatiquement en premier champ.
Nous nous rendons ensuite dans l'onglet « Données » du ruban, puis nous cliquons sur « Analyse de scénarios », et enfin sur « Valeur cible ».
Une boîte de dialogue s'ouvre avec trois champs.
- Le premier, « Définir la cellule », affiche déjà $C$9 car c'était notre cellule active — nous ne changeons rien.
- Dans le deuxième champ « À la valeur », nous saisissons 15000 (sans espace ni symbole €, Excel n'accepte que des chiffres bruts).
- Dans le troisième champ « En modifiant la cellule », nous cliquons sur C7 dans notre feuille pour insérer la référence de la quantité.
Nous validons en cliquant sur « OK ».
Excel affiche alors une fenêtre de résultat indiquant qu'il a trouvé une solution, et nous voyons la valeur en C7 changer.
Nous cliquons à nouveau sur « OK » pour conserver ce résultat.
Excel indique que nous devons vendre 1200 boîtes pour atteindre l’objectif de 15000€ de CA.
3. La Valeur Cible sur le bénéfice : calculer le seuil de rentabilité réel
Notre manager est satisfait mais pousse sa demande plus loin : il ne veut pas seulement savoir quand on couvre nos coûts, il veut savoir combien il faut vendre pour dégager 5 000 € de bénéfice net.
La logique est identique à l'étape précédente, mais cette fois nous travaillons sur la cellule C15 (le bénéfice), qui intègre à la fois les coûts variables et les coûts fixes dans son calcul.
C'est ici qu'on voit concrètement la puissance du modèle que nous avons construit en partie 1 : parce que toutes nos formules sont chaînées, Excel peut remonter la chaîne de calcul et ajuster la quantité pour que le bénéfice final atteigne exactement notre cible.
Nous cliquons sur la cellule C15 (Bénéfice net). Puis nous retournons dans « Données » → « Analyse de scénarios » → « Valeur cible ».
- Cette fois, le champ « Définir la cellule » affiche $C$15.
- Dans « À la valeur », nous saisissons 5000.
- Dans « En modifiant la cellule », nous cliquons sur C7 pour désigner la quantité comme variable d'ajustement.
Nous cliquons sur « OK », puis à nouveau « OK » pour valider. Excel a recalculé la quantité nécessaire pour atteindre 5 000 € de bénéfice net après déduction de tous les coûts.
Nous constatons que cette quantité est sensiblement plus élevée que celle calculée pour le seul chiffre d'affaires : c'est logique, car ici nous devons d'abord absorber 3 200 € de coûts fixes et les coûts variables avant de commencer à générer du bénéfice.
4. Le Solveur : optimisation avec plusieurs variables et contraintes
4.1. Pourquoi la Valeur Cible ne suffit plus
L'équipe marketing impose maintenant une contrainte forte : le prix de la boîte de macarons ne peut pas dépasser 11 €, sous peine de perdre des clients face à la concurrence.
Mais notre manager veut quand même atteindre 5 000 € de bénéfice.
Nous avons désormais deux variables que nous pouvons ajuster (la quantité ET le prix), et une contrainte à respecter (prix ≤ 11 €).
La Valeur Cible ne peut travailler que sur une variable à la fois : il nous faut un outil plus puissant, le Solveur.
4.2. Activer le Solveur
Le Solveur n'est pas activé par défaut dans Excel, mais il est entièrement gratuit et déjà présent dans votre installation.
Pour l'activer, nous nous rendons dans « Fichier » → « Options » → « Compléments ».
En bas de la fenêtre, dans le menu déroulant « Gérer », nous sélectionnons « Compléments Excel » puis nous cliquons sur « Atteindre ».
Une fenêtre s'ouvre avec la liste des compléments disponibles.
Nous cochons la case « Complément Solveur » et nous validons en cliquant sur « OK ».
Dorénavant, un bouton « Solveur » apparaît à l'extrémité droite de l'onglet « Données ».
Cette activation est permanente. Vous n'avez à la faire qu'une seule fois sur votre poste.
4.3. Paramétrer et lancer le Solveur
Nous cliquons sur « Solveur » dans l'onglet « Données ».
La fenêtre du Solveur s'ouvre. Voici comment remplir chaque champ :
- Dans « Définir l'objectif », nous cliquons sur la cellule C15 (Bénéfice net).
- Juste en dessous, nous choisissons l'option « Valeur » et nous saisissons 5000 — nous ne voulons pas maximiser ou minimiser, nous voulons atteindre une valeur précise.
- Dans « Cellules variables », nous sélectionnons la plage C7;C8.
- Cela signifie qu'Excel pourra modifier à la fois la quantité et le prix.
Nous devons maintenant ajouter notre contrainte.
Nous cliquons sur le bouton « Ajouter ».
Dans la fenêtre qui s'ouvre, nous cliquons sur C8 (Prix unitaire) dans le champ « Référence de cellule », nous choisissons l'opérateur « <= » dans la liste déroulante, et nous saisissons 11 dans le champ « Contrainte ».
Nous cliquons sur « OK » pour revenir à la fenêtre principale.
Nous voyons maintenant notre contrainte affichée : $C$8 <= 11.
Tout est prêt. Nous cliquons sur « Résoudre ».
Excel travaille quelques instants et nous présente une fenêtre de résultats indiquant qu'une solution a été trouvée.
Nous sélectionnons « Conserver la solution du Solveur » et nous cliquons sur « OK ».
Nous observons qu'Excel a fixé le prix à exactement 11 € (la contrainte maximale) et a ajusté la quantité en conséquence.
Attention : Le Solveur modifie directement les valeurs de votre feuille. Pensez à noter vos valeurs initiales ou à travailler sur une copie de votre feuille si vous souhaitez conserver votre scénario de départ.
5. La Table de données : visualiser toutes les combinaisons d'un seul coup
5.1. L'intérêt de l'analyse de sensibilité
Jusqu'ici, chaque outil nous a donné une réponse unique à une question précise.
Mais notre manager veut maintenant voir un tableau complet : comment le bénéfice évolue-t-il si l'on fait varier le prix entre 9 € et 13 €, et la quantité entre 600 et 1 200 boîtes ?
C'est l'objet de l'analyse de sensibilité, ou Table de données à double entrée.
C'est l'outil le plus visuel des trois, et souvent le plus impressionnant à présenter en réunion.
5.2. Construire la structure de la table
Nous créons notre table dans une zone vide de la feuille, par exemple à partir de la cellule E6.
La structure est la suivante : la cellule de coin supérieur gauche (E6) doit contenir un lien vers notre résultat-clé, c'est-à-dire le bénéfice net.
La ligne du à droite (F6:J6) accueille les valeurs de prix que nous voulons tester. La colonne de gauche (E7:E11) accueille les quantités (les valeurs de 9 à 13€) :
Nous saisissons ensuite les valeurs de quantité en colonne, en les tapant directement (de 600 à 1400) :
5.3. Lancer la Table de données
Nous sélectionnons toute la plage E6:J11, puis nous nous rendons ensuite dans « Données » → « Analyse de scénarios » → « Table de données ».
Une boîte de dialogue s'ouvre avec deux champs.
- Dans « Cellule d'entrée en ligne » (c'est-à-dire les valeurs qui se lisent horizontalement, nos prix), nous cliquons sur C8 dans notre tableau source.
- Dans « Cellule d'entrée en colonne » (les valeurs verticales, nos quantités), nous cliquons sur C7.
Nous validons avec « OK ».
Excel remplit instantanément toutes les cellules vides du tableau avec les bénéfices simulés pour chaque combinaison prix/quantité.
Nous pouvons observer, par exemple, que pour 800 boîtes à 9 € le bénéfice est négatif, tandis qu'à 1 200 boîtes à 13 € il est très positif.
Si nous regardons attentivement, nous pouvons constater qu’Excel insère dans chaque cellule résultat une formule matricielle de la forme {=TABLE($C$7;$C$8)}. Nous ne saisissons jamais cette formule à la main — Excel la génère automatiquement. Les accolades {} indiquent qu'il s'agit d'un calcul matriciel qu'on ne peut pas modifier cellule par cellule.
5.4. Mise en forme conditionnelle pour lire le tableau d'un coup d'œil
Pour rendre le tableau immédiatement lisible, nous allons appliquer une mise en forme par nuances de couleurs. Nous sélectionnons les cellules de résultats F7:J11, puis nous nous rendons dans l'onglet « Accueil » → « Mise en forme conditionnelle » → « Nuances de couleurs ».
Nous choisissons l'échelle Rouge → Jaune → Vert : les bénéfices négatifs ou faibles apparaissent en rouge, les valeurs intermédiaires en jaune, et les bénéfices les plus élevés en vert. En un seul coup d'œil, notre manager peut identifier quelles combinaisons prix/quantité sont rentables et lesquelles ne le sont pas.
Si vous souhaitez masquer la valeur de la cellule E6 (le lien vers le bénéfice) qui peut sembler gênante visuellement sur le tableau, il suffit de lui appliquer une couleur de police identique à la couleur de fond de la cellule. La valeur est toujours là pour que la Table de données fonctionne, mais elle devient invisible à l'écran.
