- Un scénario est un jeu cohérent d’hypothèses (pessimiste, base, optimiste) ; un sélecteur et la fonction INDEX font basculer tout le modèle en une cellule.
- Une grille de sensibilité croise deux variables (prix × volume) avec une seule formule à références mixtes ; la Table de données d’Excel produit le même tableau.
- Le diagramme en tornade classe les variables par amplitude d’effet : ici le prix pèse plus de trois fois plus que les charges fixes.
- Un scénario pessimiste doit rester plausible : il sert à savoir si l’entreprise tient, pas à faire peur.
Un budget est une promesse sur l’avenir, et l’avenir ne tient pas toujours ses promesses. L’épisode précédent a fixé le seuil de rentabilité d’une gamme de produits à 12 000 unités. Reste à répondre aux questions que posent le banquier et l’associé : « Et si le prix baisse ? Et si on vend moins ? Quelle variable surveiller en priorité ? ». C’est le travail des scénarios et de la sensibilité.
Ce que vous saurez faire à la fin de l’épisode
- bâtir un tableau de scénarios commandé par un sélecteur ;
- construire une grille de sensibilité à deux variables ;
- comprendre ce que fait la Table de données d’Excel ;
- classer les variables par influence avec un diagramme en tornade ;
- présenter le résultat sans le surcharger.
Les trois scénarios
On reprend le modèle de l’épisode 43. Chaque scénario est un jeu cohérent d’hypothèses : le pessimiste combine un prix plus bas, des coûts plus élevés et moins de volume (par exemple une concurrence plus vive), pas seulement un chiffre isolé.
Figure 1 : le tableau de scénarios. Repère 1 : le résultat de chacun ; repère 2 : le résultat du scénario retenu.
| Pessimiste | Base | Optimiste | |
|---|---|---|---|
| Prix de vente | 240 | 250 | 260 |
| Coût variable | 160 | 150 | 145 |
| Charges fixes | 1 250 000 | 1 200 000 | 1 180 000 |
| Volume | 12 500 | 15 000 | 17 000 |
| Marge unitaire | 80 | 100 | 115 |
| Résultat | −250 000 | 300 000 | 775 000 |
| Seuil (unités) | 15 625 | 12 000 | 10 261 |
| Marge de sécurité | −25,0 % | 20,0 % | 39,6 % |
Dans le scénario pessimiste, le seuil (15 625 unités) dépasse le volume prévu (12 500) : la marge de sécurité est négative, ce qui veut dire que l’activité est sous le seuil. Dans le scénario optimiste, il suffit de vendre 10 261 unités pour couvrir les charges.
Le sélecteur
Une liste déroulante en B11 (validation de données, épisode 11) propose les trois noms. Deux formules font le reste :
Rang : =EQUIV(B11;B1:D1;0) → 2
Résultat : =INDEX(B7:D7;1;B12) → 300 000EQUIV retrouve la position du nom choisi dans l’en-tête, INDEX renvoie le résultat de cette colonne. Dans un vrai modèle, le sélecteur alimente directement la colonne « actif » des hypothèses, et tout le reste suit. On évite ainsi de copier-coller des hypothèses, source classique d’erreurs.
La grille de sensibilité : prix × volume
Un scénario change plusieurs choses à la fois. Pour savoir quelle variable compte, on en fait varier deux et on lit le résultat dans une grille. Chaque case calcule le résultat pour un prix (colonne A) et un volume (ligne 1), les autres paramètres restant à leur valeur de base :
=B$1*($A2-Scénarios!$C$3)-Scénarios!$C$4La référence B$1 fige la ligne (le volume), $A2 fige la colonne (le prix) : une seule formule recopiée dans les 25 cases (épisode 4, références mixtes).
Figure 2 : la grille. Repère 1 : la zone calculée ; repère 2 : le cas de base (250 × 15 000 = 300 000).
| Prix \ Volume | 11 000 | 13 000 | 15 000 | 17 000 | 19 000 |
|---|---|---|---|---|---|
| 230 | −320 000 | −160 000 | 0 | 160 000 | 320 000 |
| 240 | −210 000 | −30 000 | 150 000 | 330 000 | 510 000 |
| 250 | −100 000 | 100 000 | 300 000 | 500 000 | 700 000 |
| 260 | 10 000 | 230 000 | 450 000 | 670 000 | 890 000 |
| 270 | 120 000 | 360 000 | 600 000 | 840 000 | 1 080 000 |
Lecture : le seuil de rentabilité dépend du prix. Il est de 15 000 unités à 230 (la case correspondante affiche 0), 13 334 à 240, 12 000 à 250, 10 910 à 260 et 10 000 à 270. Un prix plus élevé rend le volume moins critique : à 260 ou 270, le résultat reste positif sur toute la plage de la grille, alors qu’à 230 ou 240 il faut dépasser 13 000 à 15 000 unités pour gagner de l’argent. Le tableau chiffre ce que le dirigeant sent déjà.
La Table de données d’Excel
Excel sait produire cette grille tout seul : Données > Analyse de scénarios > Table de données. On place la formule de résultat dans le coin supérieur gauche, les volumes en ligne, les prix en colonne, et l’on indique la « cellule d’entrée en ligne » (le volume du modèle) et la « cellule d’entrée en colonne » (le prix). Excel remplit la grille avec une formule matricielle {=TABLE(…)}. Deux contraintes : les cellules d’entrée doivent se trouver sur la même feuille que la table, et la table n’est recalculée que si le calcul automatique des tables est actif (Formules > Options de calcul). La grille à formule explicite ci-dessus a l’avantage de fonctionner partout et de se relire.
Le diagramme en tornade
Quelle variable surveiller en priorité ? On fait varier chacune de 10 % dans les deux sens, seule, et l’on mesure l’écart entre les deux résultats (B1 contient 10 %).
Figure 3 : l’effet de ±10 %. Repère 1 : la variation testée ; repère 2 : l’amplitude.
| Variable | Si elle baisse de 10 % | Si elle monte de 10 % | Amplitude |
|---|---|---|---|
| Prix de vente | −75 000 | 675 000 | 750 000 |
| Coût variable | 525 000 | 75 000 | 450 000 |
| Volume | 150 000 | 450 000 | 300 000 |
| Charges fixes | 420 000 | 180 000 | 240 000 |
Classées de la plus forte à la plus faible, les amplitudes dessinent la « tornade » :
Figure 4 : le prix domine, loin devant le volume.
Enseignement : 10 % de prix valent plus de trois fois 10 % de charges fixes (750 000 contre 240 000). Un prix qui baisse de 10 % suffit à transformer un résultat de 300 000 en perte de 75 000. Pour ce produit, la politique de prix et la maîtrise du coût d’achat comptent plus que la chasse aux charges fixes ou la course au volume. Cette hiérarchie est celle des risques à surveiller et à négocier (contrats d’approvisionnement, clauses de révision de prix).
Les règles d’un bon scénario
- Plausible, pas catastrophiste. Un pessimiste qui n’arrive jamais ne sert à rien ; choisissez ce qui est arrivé à un concurrent ou dans l’historique.
- Cohérent. Les hypothèses d’un scénario vont ensemble (prix et volume sont liés).
- Documenté. Chaque hypothèse a une source ou un raisonnement, pour que le banquier discute les chiffres.
- Complété par un seuil. Pour chaque scénario, indiquez le volume d’équilibre : c’est lui qui dit si l’entreprise tient.
- Lisible. Trois scénarios, une grille, un classement : au-delà, on noie la décision.
À vous de jouer
Dans le classeur d’exercice :
- Onglet Scénarios : complétez les lignes 6 à 9, puis le rang et le résultat. Retrouvez −250 000, 300 000 et 775 000.
- Choisissez « Optimiste » dans la liste : que valent le rang et le résultat ?
- Onglet Sensibilité : complétez la grille. Dans quelles cases le résultat est-il négatif ?
- Onglet Tornade : passez la variation à 5 % : que deviennent les amplitudes ?
Correction. (2) Le rang est 3 et le résultat 775 000. (3) Les résultats sont négatifs pour un prix de 230 avec 11 000 et 13 000 unités, pour 240 avec 11 000 et 13 000 unités, et pour 250 avec 11 000 unités ; à 260 et 270 tout reste positif (minimum 10 000), et à 230 avec 15 000 unités le résultat est exactement nul. (4) Le modèle étant linéaire, les amplitudes sont divisées par deux : 375 000 pour le prix, 225 000 pour le coût variable, 150 000 pour le volume et 120 000 pour les charges fixes ; le classement ne change pas.
À retenir
- Un scénario est un jeu cohérent d’hypothèses ; un sélecteur et
INDEXfont basculer le modèle. - Une grille à deux variables s’écrit avec une formule à références mixtes, ou avec la Table de données.
- Le diagramme en tornade classe les variables par influence : surveillez d’abord celle qui pèse le plus.
- Un scénario pessimiste plausible dit si l’entreprise tient, et le seuil de chaque scénario dit où est la limite.
- Présentez trois scénarios, une grille et un classement, pas vingt tableaux.
Épisode 45 : le modèle prévisionnel, qui enchaîne compte de résultat, trésorerie et bilan avec un contrôle d’équilibre.
Questions fréquentes
Quelle différence entre scénario et analyse de sensibilité ?
Un scénario change plusieurs hypothèses en même temps, de façon cohérente (une récession baisse à la fois le prix et le volume). L’analyse de sensibilité change une ou deux variables à la fois, toutes choses égales par ailleurs, pour mesurer leur influence respective.
Faut-il utiliser le Gestionnaire de scénarios d’Excel ?
Il existe (Données > Analyse de scénarios > Gestionnaire de scénarios) et produit une synthèse, mais il enregistre des valeurs figées, peu lisibles et difficiles à maintenir. Un tableau de scénarios avec sélecteur et INDEX est plus transparent, se relit d’un coup d’œil et se met à jour avec le modèle.
Pourquoi la Table de données demande-t-elle des cellules d’entrée sur la même feuille ?
Parce que Excel substitue tour à tour les valeurs de la grille dans ces cellules d’entrée, et qu’il ne peut le faire que sur la feuille qui contient la table. Si vos hypothèses sont sur une autre feuille, la grille à formule explicite (comme ici) est plus simple.
Combien de scénarios faut-il présenter ?
Trois suffisent pour décider : un pessimiste plausible, un central, un optimiste prudent. Multiplier les scénarios donne une fausse impression de précision. Pour une vraie décision, ajoutez le seuil à partir duquel le projet bascule.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.