Comment réaliser une analyse de sensibilité dans Excel : étape par étape

Pour effectuer une analyse de sensibilité dans Excel, placez chaque donnée d'entrée de votre modèle dans sa propre cellule. Modifiez ensuite ces données et enregistrez l'évolution du résultat. Utilisez une table de données à une variable pour une seule entrée, une table de données à deux variables pour deux entrées, et le Gestionnaire de scénarios pour des cas complets. Terminez par un diagramme tornade qui classe les entrées par impact.

Ce guide explique ce qu'est une analyse de sensibilité, comment configurer le modèle et comment utiliser chaque outil Excel. Il comprend un exemple concret avec des formules réelles, une méthode plus rapide avec l'IA et les erreurs à éviter.

Qu'est-ce qu'une analyse de sensibilité

Une analyse de sensibilité montre à quel point un résultat change lorsqu'une hypothèse est modifiée. Elle répond à des questions telles que « Qu'arrive-t-il au profit si le prix chute de 10 % ? »

La plupart des modèles d'affaires reposent sur des suppositions. Le volume des ventes, le prix, les coûts et les taux de croissance sont tous des estimations. Une analyse de sensibilité permet de tester lesquelles de ces suppositions comptent le plus.

Excel regroupe ces outils sous l'Analyse de scénarios (What-If Analysis). Microsoft la définit comme « le processus de modification des valeurs dans les cellules » pour voir comment les résultats des formules changent.

Microsoft répertorie trois outils intégrés : « Scénarios, Valeur cible et Tables de données ». Chacun a un rôle différent :

• Les tables de données affichent un résultat pour de nombreuses valeurs d'une ou deux entrées.
• Le Gestionnaire de scénarios enregistre des ensembles complets d'entrées, comme un cas pessimiste et un cas optimiste.
• La Valeur cible fonctionne à l'inverse. Elle trouve l'entrée nécessaire pour atteindre un résultat cible.

Une analyse de sensibilité utilise généralement les deux premiers. La Valeur cible aide à répondre à une question connexe, comme le prix de seuil de rentabilité.

Configurer le modèle avant de commencer

Les outils d'Excel ne fonctionnent que si le modèle est construit proprement. Vérifiez d'abord ces points.

Placez chaque entrée dans sa propre cellule. Les unités, le prix, les coûts et les taux ont chacun besoin d'une cellule. Ne tapez pas de chiffres à l'intérieur des formules.

Liez le résultat aux entrées. La cellule de résultat, comme le profit, doit être une formule qui fait référence aux cellules d'entrée. Si ce n'est pas le cas, rien ne changera lorsqu'Excel insérera de nouvelles valeurs.

Regroupez les entrées. Rassemblez-les dans un bloc en haut de la feuille. Étiquetez chacune d'elles et colorez les cellules d'entrée pour qu'elles se démarquent.

Choisissez un seul résultat à tester. Une analyse de sensibilité est plus facile à lire lorsqu'elle suit une seule sortie, telle que le profit, la valeur actuelle nette ou la trésorerie en fin d'année.

Notez votre cas de base. Notez les valeurs de départ. Chaque résultat de l'analyse est comparé à cette base.

Voici un modèle de profit simple à suivre. Il s'agit d'un magasin de vélos fictif, Northgate Cycles :

Cellule	Étiquette	Valeur
B1	Unités vendues	2,400
B2	Prix par unité	$850
B3	Coût variable par unité	$510
B4	Coûts fixes	$520,000
B5	Profit	=B1*(B2-B3)-B4

Avec ces valeurs de base, le profit est de $296,000.

Comment faire une analyse de sensibilité à une variable avec une table de données

Une table de données à une variable teste une plage de valeurs pour une seule entrée. Microsoft indique qu'elle montre « comment différentes valeurs d'une variable dans une ou plusieurs formules modifieront les résultats ».

Voici comment tester le prix dans le modèle d'exemple :

1. De D2 à D6, tapez les prix à tester : $800, $825, $850, $875 et $900.
2. En E1, une ligne au-dessus et une colonne à droite du premier prix, tapez =B5.
3. Sélectionnez D1:E6.
4. Sous l'onglet Données, sélectionnez Analyse de scénarios > Table de données.
5. Dans Cellule d'entrée en colonne, entrez B2, la cellule du prix. Laissez Cellule d'entrée en ligne vide.
6. Cliquez sur OK.

Excel remplit E2:E6 avec le profit pour chaque prix. L'emplacement de la formule est important. Microsoft indique de la taper « une ligne au-dessus et une cellule à droite de la colonne de valeurs ».

Les résultats pour Northgate Cycles :

Prix	Profit
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

Chaque variation de $25 du prix modifie le profit de $60,000. C'est la sensibilité du profit par rapport au prix.

Comment faire une analyse de sensibilité à deux variables

Une table de données à deux variables teste deux entrées à la fois. Microsoft indique qu'elle montre « comment différentes valeurs de deux variables dans une formule modifieront les résultats de cette formule ».

Voici comment tester le prix et les unités ensemble :

1. Dans une cellule vide, telle que D10, tapez =B5.
2. En dessous, de D11 à D13, tapez les volumes d'unités : 2,000, 2,400 et 2,800.
3. À sa droite, de E10 à G10, tapez les prix : $800, $850 et $900.
4. Sélectionnez D10:G13.
5. Sélectionnez Analyse de scénarios > Table de données.
6. Dans Cellule d'entrée en ligne, entrez B2. Dans Cellule d'entrée en colonne, entrez B1.
7. Cliquez sur OK.

Le résultat est une grille de profits :

Unités \ Prix	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

La grille montre que le magasin reste rentable dans chaque combinaison testée. Le point le plus faible, 2,000 unités à $800, génère tout de même $60,000.

Il existe une limite stricte. Microsoft précise qu'« une table de données ne peut pas accepter plus de deux variables ». Pour plus d'entrées, utilisez le Gestionnaire de scénarios.

Les tables de données sont également recalculées chaque fois que la feuille est recalculée. Dans un classeur volumineux, cela peut ralentir Excel. Microsoft suggère de régler le calcul de sorte que les tables de données ne soient recalculées que lorsque vous appuyez sur F9.

Comment comparer des cas complets avec le Gestionnaire de scénarios

Le Gestionnaire de scénarios enregistre des ensembles complets d'entrées et les permute. Microsoft décrit un scénario comme « un ensemble de valeurs qu'Excel enregistre et peut remplacer automatiquement sur votre feuille de calcul ».

Voici comment configurer trois cas :

1. Sous l'onglet Données, sélectionnez Analyse de scénarios > Gestionnaire de scénarios.
2. Cliquez sur Ajouter, nommez le scénario Base, et entrez B1:B4 comme cellules variables. Conservez les valeurs actuelles.
3. Ajoutez un cas Pessimiste : 2,000 unités, prix de $825, coût variable de $530 et coûts fixes de $540,000.
4. Ajoutez un cas Optimiste : 2,800 unités, prix de $875, coût variable de $495 et coûts fixes de $510,000.
5. Sélectionnez un scénario et cliquez sur Afficher pour charger ses valeurs.
6. Cliquez sur Synthèse et choisissez B5 comme cellule de résultat pour générer un rapport.

Pour Northgate Cycles, la synthèse affiche trois profits. Le cas pessimiste génère $50,000, le cas de base $296,000 et le cas optimiste $554,000.

Microsoft note quelques limites. Chaque scénario peut contenir jusqu'à 32 valeurs variables. Le rapport de synthèse n'est pas automatique : « Les rapports de scénario ne sont pas recalculés automatiquement ». Si vous modifiez un scénario, créez une nouvelle synthèse.

Comment trouver un point mort avec la Valeur cible

Une analyse de sensibilité montre l'ampleur de la variation du résultat. La Valeur cible répond à la question inverse : quelle valeur d'entrée donne un résultat cible ?

Microsoft indique que la Valeur cible « prend un résultat et détermine les valeurs d'entrée possibles qui produisent ce résultat ». Son exemple trouve le taux d'intérêt nécessaire pour le remboursement d'un prêt.

Voici comment trouver le prix de seuil de rentabilité pour Northgate Cycles :

1. Sous l'onglet Données, sélectionnez Analyse de scénarios > Valeur cible.
2. Dans Cellule à définir, entrez B5, la cellule du profit.
3. Dans Valeur à atteindre, entrez 0.
4. Dans Cellule à modifier, entrez B2, la cellule du prix.
5. Cliquez sur OK.

Excel modifie le prix jusqu'à ce que le profit atteigne zéro. À 2,400 unités et avec les coûts de base, le prix de seuil de rentabilité est d'environ $726.67. C'est environ 15 % de moins que le prix actuel de $850.

La Valeur cible a une limite. Microsoft précise qu'elle « ne fonctionne qu'avec une seule valeur d'entrée variable ». Pour plus d'une entrée, Microsoft oriente vers le complément Solver.

Comment construire un diagramme tornade

Un diagramme tornade classe les entrées selon l'ampleur de leur impact sur le résultat. C'est la manière la plus claire de présenter une analyse de sensibilité.

Voici comment en construire un :

1. Choisissez une plage de test, par exemple chaque entrée 10 % plus basse et 10 % plus haute.
2. Modifiez une entrée à la fois et enregistrez le résultat bas et haut. Gardez les autres entrées à leur valeur de base.
3. Placez les résultats dans un tableau avec une ligne par entrée.
4. Triez les lignes de sorte que l'entrée avec la plage la plus large soit en haut.
5. Insérez un graphique à barres des résultats bas et hauts.

Pour Northgate Cycles, avec chaque entrée déplacée de 10 % vers le haut et vers le bas :

Entrée	Profit à −10%	Profit à +10%	Plage
Prix	$92,000	$500,000	$408,000
Coût variable	$418,400	$173,600	$244,800
Unités vendues	$214,400	$377,600	$163,200
Coûts fixes	$348,000	$244,000	$104,000

Le prix est ce qui compte le plus, et de loin. Une baisse de prix de 10 % réduit le profit de $296,000 à $92,000. Les coûts fixes comptent le moins. Cela indique au propriétaire où consacrer du temps pour vérifier les hypothèses.

Un graphique à barres dans Excel dessine des barres de gauche à droite. Pour obtenir la forme de tornade, tracez la variation de chaque entrée par rapport au profit de base au lieu des valeurs brutes. Ainsi, les barres du cas bas pointent vers la gauche et celles du cas haut vers la droite.

Comment faire une analyse de sensibilité avec l'IA

Construire des tables de données, des scénarios et un diagramme tornade pour un grand modèle prend du temps. Un outil d'IA peut exécuter les mêmes tests à partir de votre classeur.

Voici la méthode en trois étapes, en utilisant Powerdrill Bloom.

Étape 1 : Téléchargez votre modèle

Commencez par la feuille de calcul qui contient votre modèle. L'AI Sensitivity Analysis Generator vous demande de télécharger un « DCF, business case, plan opérationnel ou tout modèle de feuille de calcul ».

Assurez-vous que les entrées et la cellule de résultat sont clairement étiquetées. L'outil doit savoir quels chiffres sont des hypothèses.

Étape 2 : Choisissez la vue de sensibilité en langage naturel

Décrivez le test que vous souhaitez. Par exemple : « Affiche un diagramme tornade pour le profit avec chaque entrée déplacée de 10 % vers le haut et vers le bas. Ajoute un tableau à double entrée pour le prix et les unités vendues. »

La page de l'outil répertorie quatre vues : « tornade, double entrée, Monte Carlo ou seuil de rentabilité ». Elle décrit la sortie tornade comme un graphique « classant les entrées qui font le plus varier la réponse ». Elle propose également un « Tableau de sensibilité sur les deux hypothèses qui comptent ».

Étape 3 : Vérifiez les résultats et exportez l'analyse

Comparez un ou deux résultats avec votre propre table de données avant de les partager. Le cas de base doit correspondre exactement à votre modèle.

Ensuite, exportez. La page de l'outil propose un « classeur Excel, un PDF personnalisé ou un lien canvas ». Pour une présentation, l'outil Excel to PPT transforme le même fichier en diapositives. La page AI Financial Modeling traite de la construction du modèle lui-même.

Comment lire et présenter les résultats

Une analyse de sensibilité n'est utile que si l'on agit en conséquence. Ces habitudes peuvent aider.

Commencez par le classement. Ouvrez avec le diagramme tornade. Il montre en une seule image quelles hypothèses pilotent le résultat.

Traduisez les plages en décisions. « Le profit tombe à $92,000 si le prix baisse de 10 % » est un fait. « Nous devrions tester le prix avant de signer le bail » est une décision.

Montrez le point mort. Associez l'analyse à la valeur où le résultat atteint zéro. Pour Northgate Cycles, le seuil de rentabilité est d'environ 1,530 unités au prix et aux coûts de base. Notre guide sur l'analyse du seuil de rentabilité dans Excel couvre ce calcul.

Utilisez des plages réalistes. Une variation de 10 % convient à des entrées stables. Le volume d'un nouveau produit peut nécessiter une plage beaucoup plus large.

Précisez ce qui a été maintenu constant. Chaque test individuel maintient les autres entrées à leur base. Indiquez-le sur la diapositive ou dans une note de bas de page. Les lecteurs sauront alors qu'un changement de prix de 10 % suppose le même volume, les mêmes coûts et les mêmes dépenses fixes. Cette simple ligne évite la plupart des questions de suivi sur la méthode.

Réévaluez une fois les résultats obtenus. Une fois que les chiffres réels arrivent, comparez-les avec vos plages. Un rapport budget vs réel est un suivi naturel.

Erreurs courantes en analyse de sensibilité

Ces erreurs sont fréquentes et faciles à éviter.

Chiffres codés en dur dans les formules. Si une formule contient 850 au lieu de B2, la table de données ne peut pas le modifier. Gardez chaque entrée dans une cellule.

Tester trop d'entrées à la fois. Une table de données en gère une ou deux. Passez au Gestionnaire de scénarios ou à un diagramme tornade pour en tester davantage.

Utiliser la même plage pour chaque entrée. Le prix et le volume comportent rarement la même incertitude. Choisissez des plages qui reflètent ce que vous savez réellement.

Ignorer les liens entre les entrées. Un prix plus bas peut augmenter le volume. Un test individuel passe à côté de cela, alors ajoutez un scénario qui fait varier les deux.

Synthèses de scénarios obsolètes. Les rapports de scénario ne se mettent pas à jour d'eux-mêmes. Reconstruisez-les après tout changement.

Oublier le cas de base. Chaque tableau devrait afficher le résultat de base, afin que les lecteurs puissent voir l'ampleur de chaque changement.

Lorsque vous serez prêt à tester la résistance de votre prochain modèle, essayez Powerdrill Bloom avec le classeur que vous utilisez pour la planification.

Foire aux questions

Qu'est-ce qu'une analyse de sensibilité dans Excel ?

C'est un moyen de tester comment un résultat change lorsque ses entrées changent. Dans Excel, vous utilisez les outils d'Analyse de scénarios, principalement les tables de données et le Gestionnaire de scénarios, pour insérer de nouvelles valeurs d'entrée et enregistrer les résultats.

Comment faire une analyse de sensibilité à une variable dans Excel ?

Listez les valeurs d'entrée dans une colonne. Placez une formule qui fait référence au résultat une ligne au-dessus et une colonne à droite. Sélectionnez la plage, choisissez Analyse de scénarios > Table de données, et entrez la cellule d'entrée comme cellule d'entrée en colonne.

Quelle est la différence entre une table de données et le Gestionnaire de scénarios ?

Une table de données teste une ou deux variables sur de nombreuses valeurs et affiche tous les résultats dans une seule grille. Le Gestionnaire de scénarios enregistre des ensembles complets d'entrées, jusqu'à 32 valeurs variables chacun, et les permute une par une.

Comment créer un diagramme tornade dans Excel ?

Faites varier chaque entrée vers le haut et vers le bas du même montant, une à la fois, et enregistrez le résultat. Triez les entrées par la taille de la plage, puis tracez la variation de chaque entrée par rapport au résultat de base sous forme de barres horizontales.

L'IA peut-elle effectuer une analyse de sensibilité ?

Oui. Un outil d'IA peut lire votre modèle de feuille de calcul, exécuter des tests tornade et à double entrée, et exporter les résultats sous forme de classeur ou de rapport. Vous devez toujours vérifier le cas de base et choisir des plages réalistes.

Sources : Microsoft Support, Introduction à l'analyse de scénarios · Microsoft Support, Calculer plusieurs résultats à l'aide d'une table de données · Microsoft Support, Basculer entre différents ensembles de valeurs à l'aide de scénarios.