Tutoriel sur l'analyse de sensibilité dans Excel : Vue d'ensemble, types et meilleures pratiques

Lors de la conception d'un modèle dans une feuille de calcul, il peut être intéressant de savoir comment un résultat peut changer en modifiant la valeur de l'une des variables impliquées. Ce processus peut être réalisé dans Excel en effectuant une analyse de sensibilité

Savoir comment effectuer une analyse de sensibilité est essentiel si vous êtes un spécialiste des données travaillant dans le domaine de la modélisation financière ou des processus de décision. 

Dans cet article, nous allons explorer en profondeur l'analyse de sensibilité. Nous aborderons les bases de cette technique utile au sein de la famille des analyses "par hypothèse" et la manière de mener et d'interpréter les résultats des analyses de sensibilité de base et avancées dans Excel. Enfin, nous discuterons du rôle de l'IA et de l'apprentissage automatique dans la réalisation d'analyses de sensibilité plus puissantes, ainsi que des meilleures pratiques et des pièges les plus courants. 

Si vous souhaitez acquérir une expérience pratique, consultez notre cours sur l'analyse financière dans Excel, qui couvre en détail l'analyse par simulation. 

Qu'est-ce que l'analyse de sensibilité dans Excel ?

L'approche la plus courante en matière d'analyse de sensibilité consiste à examiner comment les changements d'une variable à la fois affectent le résultat final tout en maintenant tous les autres facteurs constants. Cette opération est répétée pour toutes les variables considérées. Il aide à identifier les facteurs les plus influents dans un modèle, permettant aux entreprises de se concentrer sur les facteurs clés qui affectent leur performance. 

L'analyse de sensibilité aide les entreprises à hiérarchiser les principaux risques et opportunités en identifiant les facteurs qui ont le plus d'impact sur les performances. De par sa nature, l'analyse de sensibilité est courante dans les domaines de la finance, des affaires et de la gestion de projet, par exemple pour calculer comment les taux d'intérêt peuvent évoluer dans le temps ou pour comprendre différentes tendances de croissance. 

En outre, la sensibilité est souvent combinée à l'analyse de scénarios, qui analyse généralement la combinaison de variables en même temps. Ces deux analyses d'hypothèses étudient la réaction d'une variable dépendante à certaines données. Cependant, l'analyse de scénario est spécifique à un certain "scénario", tandis que l'analyse de sensibilité est plus ouverte car elle donne une gamme d'entrées et de valeurs. 

L'objectif de l'analyse de sensibilité est de comprendre comment la variable dépendante réagit à une série de valeurs d'entrée, appelées variables indépendantes.

En intégrant cette technique dans la prise de décision, les organisations peuvent ajuster leurs stratégies de manière proactive et renforcer leur résistance à l'incertitude. 

Pour une explication plus détaillée de la théorie qui sous-tend l'analyse de sensibilité, nous vous recommandons vivement notre site Advanced Probability : Incertitude dans les cours sur les données.

Souvent, l'analyse de sensibilité est présentée dans un tableau de sensibilité avec une mise en forme conditionnelle qui met en évidence les valeurs de la plus élevée à la plus faible. 

Vous trouverez ci-dessous un exemple de la sensibilité de l'offre et de la demande au prix. L'offre et la demande sont les variables indépendantes indiquées sur les axes, et le prix est la variable dépendante, qui contient les valeurs du tableau.

Nous travaillerons avec des tableaux de sensibilité Excel similaires dans les sections suivantes.

Tableau de sensibilité. Source : DataCamp

Lancement de l'analyse de sensibilité dans Excel

Voyons comment effectuer une analyse de sensibilité dans Excel, en suivant chaque étape du processus. 

Outils et fonctionnalités d'Excel

Excel est un outil pratique pour créer des modèles mathématiques, y compris des analyses d'hypothèses. L'analyse d'hypothèses consiste essentiellement à créer des formules qui permettent d'explorer des questions, comme une analyse de sensibilité.

La plupart des versions récentes d'Excel sont dotées d'un ongletData, qui offre une myriade d'outils et de possibilités pour travailler avec des données. Il s'agit notamment d'un bouton What-if Analysis dans le ruban. Il comprend trois outils. Lebouton Data Table est spécialement conçu pour créer des tableaux de sensibilité.

Pour effectuer une analyse de scénario, il suffit de cliquer sur le bouton Gestionnaire de scénarios sur le bouton

Nous utiliserons également Solverun complément que vous pouvez activer à partir de la page Développeur Onglet.

Le solveur est utilisé dans les analyses d'hypothèses pour trouver une valeur optimale (maximale ou minimale) pour une formule dans une cellule - appelée cellule objectif - soumise à des contraintes, ou limites, sur les valeurs d'autres cellules de formule sur une feuille de calcul.

Avant d'activer le Solveur, vous devez accéder à l'onglet Développeur, qui n'est pas affiché par défaut. Pour afficher cet onglet :

• Aller à Fichier > Options
• Sélectionnez Personnaliser le ruban dans le panneau de gauche
• Dans la colonne de droite, sous "Onglets principauxcochez la case Développeur

Réalisation d'une analyse de sensibilité à une seule variable

Maintenant que nos outils sont prêts, commençons par créer une simple analyse de sensibilité à une seule variable dans Excel.

Mise en place de votre modèle

La première étape d'une analyse de sensibilité dans Excel consiste à identifier les données d'entrée et de sortie sur lesquelles votre modèle sera basé. 

Pour ce tutoriel, nous utiliserons l'analyse de sensibilité pour étudier comment le bénéfice net d'une entreprise de vente de guitares variera si nous changeons la valeur de certaines variables d'entrée, comme le nombre de guitares vendues, le prix de la guitare ou les coûts de production.

Vous trouverez ci-dessous les tableaux contenant les variables d'entrée et de sortie. 

Lorsque vous utilisez des tableaux de données pour l'analyse de sensibilité, il est important de lier les cellules de sortie aux variables d'entrée afin qu'Excel puisse comprendre la relation entre les variables du modèle. Ce processus est réalisé à l'aide de formules. Dans notre exemple :

• Le chiffre d'affaires est calculée en multipliant le prix par unité par les unités vendues (=B2*B4).
• Le coût des ventes est calculé en multipliant le coût de production par unité par les unités vendues (=B3*B4).
• Le bénéfice est calculé en soustrayant les recettes du coût des ventes (=B7-B8).

Création d'un tableau de données à sens unique

Vous voudrez peut-être savoir comment le bénéfice évolue si nous augmentons ou diminuons le prix unitaire, sans modifier le coût de production et le nombre d'unités vendues. Lorsque nous analysons l'évolution d'une variable de sortie lorsque nous modifions la valeur d'une variable d'entrée, nous avons besoin d'un tableau de données à sens unique.

Le GIF suivant montre comment créer un tableau de données à sens unique dans Excel. Comme vous pouvez le constater, le tableau recalcule automatiquement le bénéfice pour chaque unité vendue. Naturellement, le bénéfice réalisé lorsque nous vendons 250 unités est le même que dans les paramètres initiaux (c'est-à-dire 50 000 €).

Vous pouvez également étudier comment les différentes variables de sortie changent lorsque nous modifions les ventes unitaires. C'est également assez facile, comme le montre le schéma ci-dessous. Dans le GIF suivant, nous calculons comment les ventes unitaires affectent à la fois les bénéfices et le coût des ventes.

Réalisation d'une analyse de sensibilité à deux variables

Au lieu d'analyser comment une sortie est affectée par une entrée, vous pouvez également créer un tableau à deux variables pour analyser l'impact de deux variables d'entrée. Supposons que vous souhaitiez connaître l'évolution du bénéfice si nous modifions les ventes de guitares et le prix par guitare. 

Tout d'abord, vous devez créer un tableau à deux dimensions avec les fourchettes de prix par unité dans les tableaux et les ventes unitaires dans les colonnes. Ensuite, dans le coin supérieur gauche du tableau, choisissez la variable que vous souhaitez étudier. Enfin, choisissez la référence des cellules des variables indépendantes dans le tableau des variables d'entrée (c'est-à-dire le prix unitaire et les unités vendues).

Comme vous pouvez le constater, une fois le tableau créé, vous pouvez facilement remplacer la variable de sortie dans le coin supérieur gauche par une autre variable, et Excel recalculera toutes les valeurs

Interprétation des résultats de l'analyse de sensibilité Excel

Maintenant que vous disposez de vos tableaux de données, la dernière étape consiste à interpréter les résultats afin d'obtenir des informations sur l'impact des modifications apportées aux données d'entrée sur les données de sortie.

Analyse des tableaux de données

Cette analyse de sensibilité nous permet de prendre une décision éclairée sur le profit. Voici quelques éléments d'appréciation :

• Si nous augmentons le prix des guitares à 350 €, nous pourrons réaliser un bénéfice de 50 000 € en vendant seulement 200 guitares.
• Si nous réduisons le prix des guitares à 200 euros, nous devrons vendre 500 unités pour réaliser les bénéfices actuels.
• Si nous réduisons le nombre de guitares vendues à 150, nous n'atteindrons pas le bénéfice actuel, à moins de porter le prix des guitares à plus de 400 euros.

L'interprétation de l'analyse dépendra des besoins particuliers de votre entreprise. Il est essentiel de se poser des questions telles que le nombre de guitares que vous pouvez produire, le niveau minimum de bénéfices dont vous avez besoin pour assurer la solidité de votre entreprise et les moyens dont vous disposez pour réduire le coût de production de la guitare afin d'extraire des informations significatives de l'analyse de sensibilité.

Utilisation de Solver pour l'analyse de sensibilité

Lorsque vous effectuez une analyse de sensibilité, vous obtenez une gamme de résultats potentiels basés sur des changements dans les variables d'entrée d'un modèle. Cependant, il se peut que vous souhaitiez obtenir plus de détails sur le fonctionnement interne de votre modèle.

Heureusement, Excel dispose d'un outil puissant appelé Solveur, qui peut vous aider dans cette tâche. Comme nous l'avons mentionné, le Solveur est utilisé dans les analyses de simulation pour trouver une valeur optimale (maximale ou minimale) pour une formule soumise à des contraintes, ou limites, sur les valeurs d'autres cellules de formule sur une feuille de calcul. 

Cela peut ressembler à l'objectif du Goal Seek, un autre type d'analyse d'hypothèses qui se concentre sur l'ajustement d'une seule variable pour atteindre un résultat souhaité. Cependant, Solver est bien plus qu'une simple recherche d'objectifs. 

Tout d'abord, Solver vous permet de calculer un résultat optimal en fonction de plusieurs variables et d'inclure des contraintes dans les modèles. 

De plus, Solver est également doté d'une fonction de sensibilité optionnelle qui vous donne un aperçu de l'évolution de la solution optimale lorsque vous modifiez les coefficients du modèle. 

Pour illustrer la puissance de Solver, revenons à notre entreprise de guitares. Imaginez que l'entreprise fabrique deux modèles de guitare (modèles A et B), chacun nécessitant une certaine combinaison d'acajou et de cèdre. Chaque modèle a un prix différent. 

L'entreprise souhaite savoir combien d'unités de chaque modèle elle doit produire pour maximiser ses bénéfices, compte tenu de sa disponibilité actuelle en acajou et en cèdre. 

Nous voulons que Solver calcule trois choses : Profit, unités du modèle A et unités du modèle B. L'image ci-dessus montre les entrées, les formules et les contraintes sur lesquelles le modèle sera basé. 

Dans le GIF suivant, nous montrons comment utiliser Solver pour trouver les valeurs optimales des unités afin de maximiser les bénéfices, en utilisant les contraintes de disponibilité du bois et en s'assurant que les unités sont des nombres entiers. Il existe plusieurs stratégies que Solver peut utiliser pour optimiser le modèle, à la fois pour les problèmes linéaires et non linéaires. Dans notre cas, nous utiliserons Simple LP. 

Une fois que nous avons ajouté toutes les valeurs des paramètres, Solver estime que les 48 unités du modèle A et les 9 unités du modèle B constituent la meilleure combinaison pour maximiser le profit (17 591 €). Nous pouvons cliquer sur Sensibilité avant de fermer la boîte Solveur, et Excel créera une nouvelle feuille avec un rapport de sensibilité.

La sensibilité nous donne des indications précieuses pour estimer comment les bénéfices varieraient si nous changions les variables d'entrée. Leprix fictif indique de combien le bénéfice augmenterait si vous disposiez d'une unité supplémentaire d'une ressource limitée. Dans cet exemple, chaque mètre carré supplémentaire d'acajou augmente les bénéfices de 98 euros, tandis que le cèdre augmente les bénéfices de 103 euros, ce qui souligne le meilleur retour potentiel sur investissement du cèdre.

Un autre élément important du rapport de sensibilité est la fourchette dans laquelle vous pourriez maintenir les prix des modèles pour que les résultats du modèle restent inchangés. 

Le bénéfice est calculé en multipliant les unités de chaque modèle par son prix, qui est indiqué dans le tableau ci-dessous. Valeur finale et Coefficient objectif et le coefficient d'objectif. Les fourchettes sont indiquées dans les colonnes Augmentation autorisée et Diminution autorisée.

Par exemple, Solver trouvera le même nombre d'unités à fabriquer si vous augmentez la valeur du modèle A de 1140 € ou si vous la diminuez de 180 €.

Visualiser les données avec la mise en forme conditionnelle

Si vous créez des tableaux de sensibilité à sens unique et à double sens, la mise en forme des résultats peut changer la donne pour repérer rapidement les informations pertinentes et améliorer la prise de décision. La mise en forme conditionnelle est essentielle pour créer des seuils importants et des cartes thermiques convaincantes. 

Revenons à notre tableau à double entrée. Au lieu d'afficher le tableau dans des cellules blanches, nous pouvons utiliser la mise en forme conditionnelle pour créer une carte thermique multicolore d'une échelle bicolore, en établissant un seuil personnalisé, comme le montre le GIF.

Intégrer l'IA et l'apprentissage automatique

Voyons brièvement comment l'IA et l'apprentissage automatique peuvent contribuer à améliorer votre analyse de sensibilité.

Analyse de sensibilité améliorée par l'IA

Jusqu'à présent, nous avons vu comment effectuer manuellement une analyse de sensibilité. Cependant, à mesure que la complexité de vos modèles augmente, la réalisation et l'interprétation des résultats de l'analyse de sensibilité peuvent prendre beaucoup de temps. 

C'est ici que l'IA entre en scène. Grâce à l'IA et à l'apprentissage automatique, vous pouvez laisser les machines effectuer les calculs chaque fois que certaines conditions sont remplies et insérer les résultats dans des tableaux de bord attrayants. 

Une fois que vous connaissez les calculs que vous souhaitez effectuer, l'étape suivante consiste à définir clairement un pipeline d'IA afin de rationaliser et d'automatiser le processus. 

Vous êtes curieux de savoir comment faire ? Consultez notre tutoriel sur l'apprentissage automatique, les pipelines, le déploiement et les MLOps.

Simulations de Monte Carlo

La simulation de Monte Carlo est une technique mathématique utilisée pour modéliser la probabilité de différents résultats dans un processus qui ne peut pas être facilement prédit en raison de l'intervention de variables aléatoires.

Le problème est d'abord modélisé par une distribution de probabilités pour chaque variable présentant une incertitude inhérente. Un grand nombre d'échantillons aléatoires sont ensuite tirés de ces distributions de probabilités, et ces échantillons sont utilisés pour calculer les résultats. 

Ce processus est répété de nombreuses fois pour créer une distribution des résultats possibles, qui peut être analysée statistiquement pour fournir des prévisions sur le comportement d'un système. Monte Carlo permet d'analyser l'incertitude de manière plus complète que les tableaux de sensibilité traditionnels.

Vous pouvez tout apprendre sur cette technique puissante dans notre Guide de la simulation Monte Carlo dans Excel.

Compte tenu de la nature de la simulation de Monte Carlo, vous pouvez l'utiliser pour renforcer votre analyse de sensibilité afin de déterminer les variables qui ont l'impact le plus important sur les résultats. 

Dans notre exemple de la guitare, nous pouvons maintenir le prix de revient et les unités vendues constants et modifier le prix unitaire pour comprendre les changements dans les estimations. Répétez ensuite le même processus pour les deux variables restantes, une à une. Enfin, cette technique permet de comprendre sur quelle variable il faut concentrer les efforts pour améliorer la précision.

Meilleures pratiques et pièges courants

Bien qu'Excel rende l'analyse de sensibilité extrêmement facile et accessible, il y a encore quelques pièges communs à garder à l'esprit. Voici quelques éléments à prendre en compte.

Concevoir des modèles robustes

Comme vous l'avez vu dans ce tutoriel, pour que l'analyse de sensibilité fonctionne, Excel doit connaître les relations entre les variables d'entrée et de sortie. Pour cela, vous devez utiliser les formules à bon escient, en définissant clairement les références des cellules et les plages. 

Si vous souhaitez ajouter de nouvelles valeurs aux tableaux de données, une bonne pratique pour éviter les problèmes potentiels lors du recalcul consiste à utiliser les tableaux Excel, car ils sont intrinsèquement dynamiques, ce qui signifie qu'ils se développent automatiquement lorsque de nouvelles lignes ou colonnes sont ajoutées. 

Cela vous aidera à recalculer les bonnes formules pour que les informations restent dans le bon format. 

Réduire les erreurs et garantir l'exactitude des données

Même si vos analyses de sensibilité sont clairement définies, vous devez vérifier vos résultats avec soin. Les tests manuels et les graphiques sont d'excellentes options pour visualiser vos résultats et vérifier l'existence de valeurs aberrantes ou d'anomalies dans les données de sortie.  

Cependant, il est important de se rappeler que l'analyse de sensibilité peut tout simplement ne pas fonctionner pour votre cas d'utilisation. Cette analyse repose sur une série d'hypothèses simples, telles que l'indépendance des variables d'entrée, l'existence de relations linéaires et la nature statique des variables d'entrée. 

Cependant, votre modèle peut être plus complexe et peut être affecté par des facteurs externes que l'analyse de sensibilité néglige par nature. C'est pourquoi, malgré ses avantages, il est important d'avoir une compréhension complète de votre modèle au-delà de la sensibilité pour garantir une analyse correcte.

Conclusion

Félicitations pour avoir atteint la fin de ce tutoriel. L'analyse de sensibilité est une analyse puissante qui peut changer la donne dans votre processus de prise de décision. Il n'y a que des analyses de simulation pour remplacer vos modèles financiers, comme Goal Seek ou Scenario Manager. Heureusement, DataCamp est là pour vous aider. Consultez nos cours et documents sur Excel :

• Analyse de données dans Excel
• Modélisation financière dans Excel 
• Fondamentaux d'Excel 
• Manipulation de données dans Excel - Aide-mémoire 
• Analyse de données avec Google Sheets 
• Fonctions Excel avancées 
• Comment calculer les intervalles de confiance dans Excel ? 
• Formules Excel - Aide-mémoire 
• Comment calculer le coefficient de variation dans Excel ? 
• Simulation de Monte Carlo dans Excel : Un guide complet