Bridgesheet

Modélisation financière7 min de lecture

Table de sensibilité Excel : tester un modèle financier

Une table de sensibilité Excel recalcule le résultat d'un modèle pour plusieurs valeurs d'une ou deux hypothèses, sans copier le modèle. Voici comment la construire avec la Table de données, la vérifier et la lire, sur un DCF où la valeur va de 12,7 à 19,9 M€.

Livre ancien ouvert sur une table de logarithmes, colonnes de chiffres imprimés
Photo : ArnoldReinhold · CC BY-SA · Wikimedia

À quoi sert une table de sensibilité Excel

Un modèle financier repose sur quelques hypothèses que personne ne connaît avec certitude : le coût du capital, la croissance à long terme, une marge, un multiple de sortie. Une table de sensibilité Excel montre comment le résultat du modèle (valeur d'entreprise, TRI, prix par action) change quand ces hypothèses bougent. Elle répond à la question que posent un comité d'investissement, un acheteur ou un banquier : « et si vous vous trompez d'un point ? ».

Excel la produit avec un outil intégré, la Table de données (menu Données, puis Analyse de scénarios). L'outil remplace successivement la valeur d'une cellule d'hypothèse par chaque valeur d'une ligne ou d'une colonne, recalcule tout le modèle et inscrit le résultat dans la grille. On évite ainsi de copier le modèle cinq ou vingt-cinq fois.

L'exemple : un DCF qui affiche 15,4 M€

Prenons Laboratoires Vasseur, une société fictive d'analyses industrielles. Son DCF (actualisation des flux de trésorerie futurs) retient :

  • un chiffre d'affaires de 15,0 M€, en croissance de 6 %, 5 %, 4 %, 3 % puis 2 % sur cinq ans ;
  • une marge d'EBITDA (résultat d'exploitation avant amortissements) de 14 % ;
  • des amortissements de 3 % du chiffre d'affaires, des investissements de 3,5 % et un BFR (besoin en fonds de roulement, l'argent immobilisé dans les stocks et les créances clients) de 15 % du chiffre d'affaires ;
  • un impôt de 25 %, un WACC (coût moyen pondéré du capital) de 10 % et une croissance perpétuelle de 2 %.

Le flux disponible passe de 1,10 M€ la première année à 1,36 M€ la cinquième. Les cinq flux actualisés valent 4,64 M€ ; la valeur terminale, 1,36 × 1,02 ÷ (10 % − 2 %) = 17,34 M€, en vaut 10,77 une fois actualisée. La valeur d'entreprise ressort à 15,4 M€, dont 70 % viennent de la valeur terminale. C'est cette dépendance qui rend la sensibilité indispensable : la valeur terminale réagit fortement au WACC et à la croissance, comme l'explique notre article sur la valeur terminale d'un DCF.

Table de sensibilité à une variable

Commencez par une seule hypothèse, ici la marge d'EBITDA.

  1. Sur la feuille où se trouve la cellule de marge (14 %), écrivez les valeurs à tester dans une colonne : 12 %, 13 %, 14 %, 15 %, 16 %.
  2. Dans la cellule située une ligne au-dessus et une colonne à droite de la première valeur, tapez une formule qui renvoie au résultat : =C40 si la valeur d'entreprise est en C40.
  3. Sélectionnez le bloc entier, valeurs et formule comprises.
  4. Menu Données > Analyse de scénarios > Table de données. Laissez « Cellule d'entrée en ligne » vide et indiquez la cellule de marge dans « Cellule d'entrée en colonne ».

Le résultat :

Marge d'EBITDA Valeur d'entreprise
12 % 12,3 M€
13 % 13,8 M€
14 % 15,4 M€
15 % 17,0 M€
16 % 18,5 M€

Un point de marge vaut environ 1,6 M€, soit 10 % de la valeur. Pour une société dont la marge a varié de 12 % à 15 % sur les trois dernières années, c'est l'hypothèse à justifier en premier.

Table de sensibilité à deux variables : WACC × croissance

La table la plus courante d'un DCF croise le WACC et la croissance perpétuelle.

  1. Écrivez les WACC en ligne (9 % à 11 %, par pas de 0,5 point) et les taux de croissance en colonne (1 % à 3 %).
  2. Dans la cellule du coin, à l'intersection de la ligne et de la colonne, placez la formule =C40.
  3. Sélectionnez tout le bloc, puis Table de données : la cellule du WACC en « Cellule d'entrée en ligne », celle de la croissance en « Cellule d'entrée en colonne ».

Excel remplit la grille avec une formule matricielle {=TABLE(C12;C13)}. Valeur d'entreprise de Laboratoires Vasseur, en M€ :

g \ WACC 9,0 % 9,5 % 10,0 % 10,5 % 11,0 %
1,0 % 15,9 15,0 14,1 13,4 12,7
1,5 % 16,7 15,7 14,7 13,9 13,1
2,0 % 17,6 16,5 15,4 14,5 13,7
2,5 % 18,7 17,4 16,2 15,2 14,3
3,0 % 19,9 18,4 17,1 15,9 14,9

La valeur va de 12,7 à 19,9 M€. Un point de WACC en plus ou en moins la déplace de 1,7 à 2,2 M€ ; un demi-point de croissance, d'environ 0,8 M€. Avec 4,0 M€ de dette nette, la valeur des fonds propres va de 8,7 à 15,9 M€ : l'écart relatif est encore plus large côté actionnaire, car la dette ne bouge pas.

Les règles de construction qui évitent les erreurs

La Table de données a des contraintes peu connues. Les ignorer donne une grille fausse sans message d'erreur.

  • Les cellules d'entrée doivent être sur la même feuille que la table. Si le WACC est calculé dans un onglet dédié, créez sur la feuille du DCF une cellule d'hypothèse qui l'alimente, et pointez la table sur elle.
  • La cellule d'entrée doit contenir une valeur, pas une formule. Excel y substitue chaque valeur testée ; s'il remplace une formule, le lien avec le reste du modèle disparaît pendant le calcul.
  • Ne pas inverser ligne et colonne. C'est l'erreur la plus fréquente : le WACC est testé à la place de la croissance, et la table reste plausible. Le contrôle est simple : la cellule centrale doit être égale, au centime près, au résultat du cas de base (15,4 M€ ici).
  • Un nombre impair de pas, cas de base au centre. Cinq valeurs, deux de part et d'autre de l'hypothèse retenue, rendent la lecture immédiate. Mettez la cellule du cas de base en gras ou en couleur par une mise en forme conditionnelle.
  • Des pas cohérents avec l'incertitude réelle. 0,5 point pour le WACC et la croissance, 1 point pour une marge, 0,5x pour un multiple. Des pas trop larges produisent des valeurs extrêmes que personne ne défend.
  • Écarter les combinaisons absurdes. Une croissance perpétuelle égale ou supérieure au WACC fait diverger la formule de Gordon-Shapiro. Gardez au moins 5 points d'écart, ou affichez « n.s. » avec une formule SI.

Calcul, vitesse et modèles circulaires

Chaque cellule de la table relance un calcul complet du modèle. Une grille de 5 × 5 relance donc 25 calculs à chaque modification, et un modèle lourd devient lent. Deux réglages :

  • Fichier > Options > Formules > « Automatique sauf dans les tables de données ». Les tables ne se recalculent plus qu'avec F9. Pensez à appuyer sur F9 avant d'imprimer ou d'envoyer le fichier, faute de quoi la grille affiche des valeurs périmées.
  • Limitez le nombre de tables. Trois tables bien choisies valent mieux que dix.

Les modèles LBO posent un problème particulier : les intérêts, calculés sur la dette moyenne de l'année, dépendent du flux qui rembourse cette dette. Cette référence circulaire, résolue par le calcul itératif d'Excel, rend parfois les tables de données instables. La solution courante est un interrupteur qui calcule les intérêts sur la dette d'ouverture pendant la sensibilité, ou une petite macro qui copie les valeurs une à une. Dans un modèle LBO, la table type croise le multiple d'entrée et le multiple de sortie, avec le TRI au centre ; le paper LBO montre l'ordre de grandeur de cette sensibilité.

Quelles hypothèses tester selon le modèle

Modèle Résultat suivi Table à deux variables la plus utile
DCF Valeur d'entreprise, prix par action WACC × croissance perpétuelle, ou WACC × multiple de sortie
LBO TRI et MOIC des investisseurs Multiple d'entrée × multiple de sortie, ou croissance de l'EBITDA × levier
Modèle de fusion Relution ou dilution du bénéfice par action Prix payé × part payée en actions
Plan de trésorerie Trésorerie minimale Délai de paiement clients × croissance du chiffre d'affaires

Choisissez les hypothèses qui sont à la fois incertaines et influentes. Le taux d'impôt est influent mais connu ; la croissance de la cinquième année est incertaine mais pèse peu. Pour classer les hypothèses, une table à une variable par hypothèse, avec la même amplitude (± 10 % par exemple), suffit : celle qui déplace le plus le résultat mérite la table à deux variables.

Lire et présenter une table de sensibilité

Une table de sensibilité n'est pas une fourchette de valeur. Ses coins combinent deux hypothèses extrêmes en même temps, ce qui est rarement réaliste. Pour présenter une valeur, retenez plutôt la zone centrale (ici 14,5 à 16,5 M€, soit un demi-point de WACC de part et d'autre) et expliquez pourquoi.

Avant de diffuser la table, vérifiez trois points : la cellule centrale égale au cas de base ; des valeurs qui évoluent dans le bon sens (la valeur baisse quand le WACC monte) ; une table recalculée (F9). Ces contrôles font partie des cinq vérifications d'un modèle DCF à faire avant de se fier à un résultat.

Notre modèle DCF Excel inclut une table de sensibilité WACC × croissance déjà construite, avec le cas de base au centre et la part de la valeur terminale affichée. Il s'agit d'un outil de calcul : le résultat reste une estimation qui dépend de vos hypothèses, ni une évaluation certifiée ni un conseil en investissement.