Comment réaliser une analyse du seuil de rentabilité dans Excel ?

Supposons que vous souhaitiez commercialiser un nouveau produit, et que vous connaissiez le coût variable unitaire ainsi que le coût fixe total. Vous allez maintenant prévoir les volumes de vente possibles et fixer le prix du produit en conséquence.

1. Créez un tableau simple et saisissez-y les données fournies. Consultez la capture d’écran ci-dessous :

2. Saisissez les formules appropriées pour calculer le chiffre d’affaires, les coûts variables et le bénéfice. Voir la capture d’écran ci-dessus :
Chiffre d’affaires = Prix unitaire × Quantité vendue
Coûts variables = Coût unitaire × Quantité vendue
Bénéfice = Chiffre d’affaires – Coûts variables – Coûts fixes

3. Cliquez sur l’onglet Données > Analyse de scénarios > Valeur cible.

4. Dans la boîte de dialogue Valeur cible qui s’ouvre, procédez comme suit (voir la capture d’écran ci-dessus) :
(1) Indiquez la cellule à définir comme étant la cellule Bénéfice ; dans notre exemple, il s’agit de la cellule B7 ;
(2) Saisissez la valeur à atteindre comme étant 0 ;
(3) Spécifiez la cellule variable comme étant la cellule Prix unitaire ; dans notre cas, il s’agit de la cellule B1.
(4) Cliquez sur le bouton OK.

5. La boîte de dialogue État de la valeur cible s’affiche alors. Cliquez sur le bouton OK pour appliquer les modifications.

Le prix unitaire passe désormais de 40 à 31,579, et le bénéfice net devient nul. Ainsi, si vous visez un volume de ventes de 50 unités, le prix unitaire ne doit pas descendre en dessous de 31,579, sous peine d’engendrer une perte.

Télécharger un modèle complexe d’analyse du seuil de rentabilité

http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx

Contrairement à la fonction Valeur cible, vous pouvez également utiliser une formule pour réaliser facilement une analyse du seuil de rentabilité dans Excel.

1. Créez un tableau simple et saisissez-y les données fournies. Dans cette méthode, nous partons du principe que le bénéfice est nul, et que la quantité vendue, le coût unitaire ainsi que les coûts fixes ont déjà été estimés. Voir la capture d’écran ci-dessous :

2. Dans le tableau, saisissez la formule =B6/B2+B4 dans la cellule B1 pour calculer le prix unitaire, entrez la formule =B1*B2 dans la cellule B3 pour obtenir le chiffre d’affaires, et tapez la formule =B2*B4 dans la cellule B5 pour déterminer les coûts variables. Voir la capture d’écran ci-dessous :

Ainsi, dès que vous modifiez l’une des valeurs prévisionnelles — quantité vendue, coût unitaire ou coûts fixes — le prix unitaire s’ajuste automatiquement. Voir la capture d’écran ci-dessus :

Astuce  : Enregistrez une plage sous forme d’entrée AutoTexte (en conservant les formats de cellule et les formules) pour la réutiliser ultérieurement
Il est très fastidieux de faire référence à des cellules et d’appliquer plusieurs formules pour l’analyse du seuil de rentabilité à chaque fois. Kutools pour Excel propose une solution astucieuse grâce à l’utilitaire AutoTexte, qui permet d’enregistrer la plage sous forme d’entrée AutoTexte tout en conservant les formats de cellule et les formules qu’elle contient. Vous pouvez ensuite réutiliser cette plage en un seul clic dans n’importe quel classeur.

Bénéficiez dès maintenant d’un essai gratuit de 30 jours avec toutes les fonctionnalités !

Si vous disposez déjà de données de ventes enregistrées, vous pouvez également réaliser une analyse du seuil de rentabilité à l’aide d’un graphique dans Excel. Cette méthode vous guide pas à pas pour créer facilement un graphique de seuil de rentabilité.

1. Préparez un tableau des ventes tel que présenté dans la capture d’écran ci-dessous. Dans notre exemple, la quantité vendue, le coût unitaire et les coûts fixes sont constants, et nous devons réaliser une analyse du seuil de rentabilité en fonction du prix unitaire.

2. Complétez le tableau comme indiqué ci-dessous :
(1) Dans la cellule E2, saisissez la formule =D2*$B$1, puis faites glisser la poignée de recopie vers le bas jusqu’à la plage E2:E13 ;
(2) Dans la cellule F2, entrez la formule =D2*$B$1+$B$3, puis étirez-la jusqu’à la plage F2:F13 ;
(3) Dans la cellule G2, tapez la formule =E2-F2, puis étirez-la jusqu’à la plage G2:G13.

À ce stade, les données nécessaires au graphique de seuil de rentabilité que vous allez créer sont prêtes. Voir la capture d’écran ci-dessous :

3. Dans le tableau, sélectionnez simultanément les colonnes Chiffre d’affaires, Coûts et Bénéfice, puis cliquez sur Insertion > Graphiques en courbes ou en aires > Courbes. Voir la capture d’écran :

4. Votre graphique en courbes est maintenant prêt ! Cliquez dessus avec le bouton droit, puis sélectionnez « Sélectionner les données » dans le menu contextuel. Consultez la capture d’écran ci-dessous :

5. Dans la boîte de dialogue Sélectionner la source de données, procédez comme suit :
(1) Dans la section Plage des noms de série, sélectionnez la série qui vous convient. Dans mon exemple, j’ai choisi la série Chiffre d’affaires ;
(2) Cliquez sur le bouton Modifier dans la section Étiquettes de l'axe horizontal (catégorie) ;
(3) Dans la boîte de dialogue Plage des étiquettes d’axe qui s’ouvre, spécifiez la colonne Prix unitaire (hors en-tête) comme Plage d’étiquettes d’axe ;
(4) Cliquez sur OK > OK pour enregistrer les modifications.

Dans le graphique de seuil de rentabilité, vous constatez désormais que le point d’équilibre se situe lorsque le prix est égal à 36, comme illustré dans la capture d’écran ci-dessous :

De la même manière, vous pouvez créer un graphique de seuil de rentabilité pour analyser le point d’équilibre en fonction de la quantité vendue, comme le montre la capture d’écran ci-dessous :

Exporter/enregistrer facilement une sélection sous forme de fichier PDF/CSV/TXT/Fichier HTML distinct dans Excel

Par exemple, une fois votre analyse du seuil de rentabilité terminée dans Excel, vous souhaiterez peut-être l’intégrer à une présentation. Dans ce cas, utilisez l’utilitaire Exporter la plage vers un fichier de Kutools pour Excel afin d’exporter instantanément votre sélection au format PDF, HTML, CSV, fichier texte, etc.