Guide pratique de l'analyse de sensibilité dans Excel : de l'évolution manuelle à l'intelligence

Lorsque nous concevons des modèles dans des feuilles de calcul, nous nous demandons souvent comment la modification de la valeur d'une variable impacterait le résultat final. Ce processus peut être réalisé en effectuant une analyse de sensibilité dans Excel.

Si vous êtes un professionnel des données travaillant dans la modélisation financière ou le processus décisionnel, il est alors crucial de savoir comment effectuer une analyse de sensibilité.

Dans cet article, nous allons plonger dans l'analyse de sensibilité. Nous couvrirons les bases de cette technique utile de type "et si", comment effectuer et interpréter les résultats d'une analyse de sensibilité de base et avancée dans Excel, et enfin, nous discuterons du rôle de l'IA et du machine learning pour permettre des analyses de sensibilité plus puissantes, ainsi que des bonnes pratiques et des pièges courants.

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

L'approche la plus courante de l'analyse de sensibilité consiste à examiner comment un changement dans une variable affecte le résultat final tout en maintenant tous les autres facteurs constants. Ce processus est répété pour toutes les variables considérées. Cela aide à identifier les facteurs les plus influents dans un modèle, permettant aux entreprises de se concentrer sur les éléments clés affectant leur performance.

L'analyse de sensibilité aide les entreprises à prioriser les risques et opportunités critiques en identifiant les facteurs qui ont le plus grand impact sur la performance. Étant donné sa nature, l'analyse de sensibilité est courante en finance, en gestion d'entreprise et en gestion de projet, par exemple, pour calculer comment les taux d'intérêt pourraient évoluer dans le temps ou pour comprendre différentes tendances de croissance.

De plus, l'analyse de sensibilité est souvent utilisée conjointement avec l'analyse de scénarios, qui analyse généralement des combinaisons de variables simultanément. Les deux analyses de type "et si" étudient comment la variable dépendante réagira à certaines entrées. Cependant, l'analyse de scénarios concerne un "scénario" donné, tandis que l'analyse de sensibilité est plus ouverte, car elle offre 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 plage de valeurs d'entrée (appelées variables indépendantes).

En intégrant cette technique dans le processus décisionnel, les organisations peuvent ajuster leurs stratégies de manière proactive et renforcer leur résilience face à l'incertitude.

Souvent, l'analyse de sensibilité est présentée sous forme de tableau d'analyse de sensibilité avec une mise en forme conditionnelle, mettant en évidence les valeurs les plus élevées aux plus basses.

Voici un exemple de sensibilité des prix pour l'offre et la demande. L'offre et la demande sont les variables indépendantes sur les axes, et le prix est la variable dépendante, qui occupe les valeurs dans le tableau.

Dans les sections suivantes, nous utiliserons des tableaux d'analyse de sensibilité Excel similaires.

Démarrer avec l'analyse de sensibilité dans Excel

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

Outils et fonctionnalités traditionnels dans Excel

Excel est un outil pratique pour créer des modèles mathématiques, y compris l'analyse de type "et si". L'analyse de type "et si" consiste essentiellement à créer des formules pour explorer des questions comme l'analyse de sensibilité.

La plupart des versions récentes d'Excel disposent d'un onglet Données qui offre de nombreux outils et possibilités pour travailler avec les données. Cela inclut le bouton Analyse de scénarios dans le ruban. Il contient trois outils. Le bouton Table de données est spécifiquement conçu pour créer des tableaux d'analyse de sensibilité.

Pour effectuer une analyse de scénarios, cliquez simplement sur le bouton Gestionnaire de scénarios.

Nous utiliserons également Solveur, un complément qui peut être activé depuis l'onglet Développeur.

Le Solveur est utilisé dans l'analyse de type "et si" pour trouver une valeur optimale (maximale ou minimale) pour une formule dans une cellule (appelée cellule cible) soumise à des contraintes, ou limites, sur les valeurs d'autres cellules de formule de la 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 :

• Allez dans Fichier > Options
• Sélectionnez Personnaliser le ruban dans le panneau de gauche
• Sous "Onglets principaux" dans la colonne de droite, cochez la case pour Développeur

Effectuer une analyse de sensibilité à une variable

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

Méthode traditionnelle : Configuration de votre modèle

La première étape pour effectuer une analyse de sensibilité dans Excel est d'identifier les entrées et sorties sur lesquelles le modèle est basé.

Dans ce tutoriel, nous utiliserons l'analyse de sensibilité pour étudier comment le bénéfice net d'une entreprise de vente de guitares changera si nous modifions les valeurs de certaines variables d'entrée, telles que le nombre de guitares vendues, le prix par guitare ou le coût de production.

Ci-dessous, vous pouvez voir le tableau contenant les variables d'entrée et de sortie.

Lors de l'utilisation des Tables de données pour l'analyse de sensibilité, il est important de lier la cellule de sortie à la variable d'entrée afin qu'Excel comprenne la relation entre les variables du modèle. Ce processus se fait via des formules. Dans notre cas :

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

Méthode traditionnelle : Création d'une table de données à une entrée

Vous pourriez vous demander comment le bénéfice changerait si nous augmentions ou diminuions le prix unitaire tout en maintenant constant le coût de production et le nombre d'unités vendues. Lorsque nous analysons comment une variable de sortie change lorsque nous modifions les valeurs d'une variable d'entrée, nous avons besoin d'une soi-disant table de données à une entrée.

Le GIF ci-dessous montre comment créer une table de données à une entrée dans Excel. Comme vous pouvez le voir, le tableau recalcule automatiquement le bénéfice pour chaque unité vendue. Naturellement, le bénéfice lors de la vente de 250 unités est le même que dans les paramètres d'origine (c'est-à-dire 50 000 €).

Méthode plus intelligente : Analyse à une variable avec Excelmatic

Bien que la fonctionnalité Table de données soit puissante, le processus de configuration nécessite des références de cellules précises et plusieurs étapes. Pour des modèles plus complexes ou dynamiques, cela peut devenir fastidieux et sujet aux erreurs.

Excelmatic offre une alternative plus intuitive. En tant qu'Agent IA pour Excel, il vous permet d'effectuer des analyses en utilisant le langage naturel.

Pour atteindre le même objectif, vous devez simplement :

1. Téléchargez votre fichier Excel contenant votre modèle sur Excelmatic.
2. Énoncez votre besoin en langage simple : "Créez un tableau d'analyse de sensibilité pour montrer comment le Bénéfice (cellule B9) change lorsque les Unités Vendues (cellule B4) varient de 150 à 500 par incréments de 50."

Excelmatic comprend instantanément la structure de votre modèle et génère automatiquement exactement les mêmes résultats qu'une table de données à une entrée créée manuellement, sans que vous ayez à configurer manuellement le tableau, à lier des cellules ou à naviguer dans des menus. L'avantage de cette approche réside dans sa rapidité et sa simplicité, vous permettant de vous concentrer sur l'analyse des résultats plutôt que sur l'utilisation de l'outil.

Vous pourriez également vouloir examiner comment différentes variables de sortie changent avec les ventes unitaires. Dans la méthode traditionnelle, vous devriez ajuster la configuration de la Table de données.

Avec Excelmatic, vous modifiez simplement légèrement votre demande : "Montrez comment à la fois le Bénéfice (B9) et le Coût des Ventes (B8) changent lorsque les Unités Vendues (B4) varient de 150 à 500." L'IA gère tous les détails pour vous.

Conduire une analyse de sensibilité à deux variables

Méthode traditionnelle : Création d'une table de données à deux entrées

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. Disons que vous voulez savoir comment le bénéfice changerait si nous modifiions à la fois le nombre de guitares vendues et le prix par guitare.

Tout d'abord, vous devez créer un tableau bidimensionnel où les lignes sont la plage pour les prix unitaires et les colonnes sont la plage pour les ventes unitaires. Ensuite, dans le coin supérieur gauche du tableau, sélectionnez la variable que vous souhaitez étudier. Enfin, dans le tableau des variables d'entrée, sélectionnez les références de cellules pour les variables indépendantes (c'est-à-dire le prix unitaire et le nombre d'unités vendues).

Comme vous pouvez le voir, une fois que vous avez créé le tableau, vous pouvez facilement changer la variable de sortie dans le coin supérieur gauche pour une autre variable, et Excel recalculera toutes les valeurs.

Méthode plus intelligente : Analyse à deux variables avec Excelmatic

La configuration d'une table de données à deux entrées est plus complexe qu'une table à une entrée, nécessitant un référencement approprié des entrées et sorties en lignes, colonnes et la cellule du coin.

Avec Excelmatic, ce processus est également simplifié en une seule instruction simple. Après avoir téléchargé votre fichier, vous pouvez directement demander :

"Générez un tableau de sensibilité à deux entrées montrant comment le Bénéfice change en fonction des Unités Vendues (de 150 à 500) et du Prix par Unité (de 150 € à 400 €)."

Excelmatic construit l'intégralité du tableau bidimensionnel pour vous, peuplant toutes les valeurs calculées, éliminant ainsi les erreurs de référence potentielles dues à une configuration manuelle. Si vous souhaitez analyser une autre sortie, comme le "Chiffre d'affaires", posez simplement une nouvelle question : "Maintenant, montrez le même tableau mais pour le Chiffre d'affaires." Ce flux de travail conversationnel rend l'analyse complexe sans effort.

Interpréter les résultats de l'analyse de sensibilité Excel

Maintenant que vous avez le tableau de données, la dernière étape est d'interpréter les résultats pour obtenir des insights sur la façon dont les changements d'entrée affectent la sortie.

Analyser la sortie de la Table de données

Sur la base de cette analyse de sensibilité, nous pouvons prendre des décisions éclairées concernant le bénéfice. Voici quelques insights :

• Si nous augmentons le prix de la guitare à 350 €, nous n'avons besoin de vendre que 200 guitares pour atteindre le bénéfice actuel de 50 000 €.
• Si nous diminuons le prix de la guitare à 200 €, nous devons vendre 500 unités pour atteindre le bénéfice actuel.
• Si nous réduisons le nombre de guitares vendues à 150, nous ne pouvons pas atteindre le bénéfice actuel à moins d'augmenter le prix de la guitare au-dessus de 400 €.

L'interprétation de l'analyse dépendra des besoins spécifiques de votre entreprise. Poser des questions telles que combien de guitares vous pouvez produire, quel est le niveau de bénéfice minimum que vous devez maintenir pour une entreprise saine, et ce que vous pouvez faire pour réduire le coût de production des guitares est essentiel pour extraire des insights significatifs de l'analyse de sensibilité.

Méthode traditionnelle : Optimisation et analyse de sensibilité avec le Solveur

Lorsque vous effectuez une analyse de sensibilité, vous obtenez une plage de sorties potentielles basées sur des changements des variables d'entrée de votre modèle. Cependant, vous pourriez vouloir en savoir plus 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 mentionné précédemment, le Solveur est utilisé dans l'analyse de type "et si" pour trouver une valeur optimale (maximale ou minimale) pour une formule soumise à des contraintes, ou limites, sur les valeurs d'autres cellules de formule de la feuille de calcul.

Le Solveur est plus qu'une simple version améliorée de la Valeur cible. Premièrement, le Solveur vous permet de calculer des résultats optimaux basés sur plusieurs variables et vous permet d'inclure des contraintes dans votre modèle. Plus important encore, le Solveur dispose également d'une fonctionnalité de sensibilité optionnelle qui vous permet de voir comment la solution optimale change lorsque vous modifiez les coefficients de votre modèle.

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

L'entreprise veut savoir combien d'unités de chaque modèle produire pour maximiser son bénéfice, compte tenu de la disponibilité actuelle de bois d'acajou et de cèdre.

Dans le GIF ci-dessous, nous montrons comment utiliser le Solveur pour trouver les valeurs optimales d'unités pour maximiser le chiffre d'affaires tout en utilisant les contraintes de disponibilité du bois et en s'assurant que les unités sont des entiers.

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

Le rapport de sensibilité nous donne des insights précieux pour estimer comment le bénéfice changerait si nous modifiions les variables d'entrée. Le Prix dual indique de combien le bénéfice augmenterait si vous ajoutiez une unité supplémentaire de la ressource contrainte. Dans cet