Comment réaliser une analyse de sensibilité à l’aide d’une table de données dans Excel ?

Cette méthode exploite la fonctionnalité Table de données d’Excel pour réaliser une analyse de sensibilité. Veuillez suivre les étapes ci-dessous :

1. Remplissez le tableau du compte de résultat comme illustré ci-dessous :
(1) Dans la cellule B11, saisissez la formule =B4*B3 ;
(2) Dans la cellule B12, saisissez la formule =B5*B3 ;
(3) Dans la cellule B13, saisissez la formule =B11-B12 ;
(4) Dans la cellule B14, saisissez la formule =B13-B6-B7.

2. Préparez le tableau d’analyse de sensibilité comme illustré ci-dessous :
(1) Dans la plage F2:K2, saisissez les volumes de ventes allant de 500 à 1750 ;
(2) Dans la plage E3:E8, saisissez les prix allant de 75 à 200 ;
(3) Dans la cellule E2, saisissez la formule =B14

3. Sélectionnez la plage E2:K8, puis cliquez sur Données > Analyse de scénarios > Table de données. Voir la capture d’écran :

4. Dans la boîte de dialogue Table de données qui s’ouvre, veuillez (1) indiquer dans la zone Cellule d’entrée en ligne la cellule contenant le volume des ventes de chaises (B3 dans mon cas), (2) indiquer dans la zone Cellule d’entrée en colonne la cellule contenant le prix de la chaise (B4 dans mon cas), puis (3) cliquer sur le bouton OK. Voir capture d’écran :

5. Le tableau d’analyse de sensibilité est maintenant prêt, comme illustré ci-dessous.

Vous pouvez facilement observer l’évolution du bénéfice lorsque les ventes et le prix varient simultanément. Par exemple, en vendant 750 chaises à 125,00 $, le bénéfice s’élève à –3 750,00 $, tandis qu’en vendant 1 500 chaises à 100,00 $, il atteint 15 000,00 $.

Effectuer une analyse seuil de rentabilité dans Excel