Passer au contenu principal

Comment faire une analyse du seuil de rentabilité dans Excel?

L'analyse du seuil de rentabilité peut vous aider à obtenir le point où le bénéfice net est nul, ce qui signifie que le total des revenus est égal au total des dépenses. Il est très utile de fixer le prix d'un nouveau produit lorsque vous pouvez prévoir votre coût et vos ventes.


Faites une analyse du seuil de rentabilité avec la fonction de recherche d'objectif

Supposons que vous vendiez un nouveau produit et que vous connaissiez le coût variable de l'unité et le coût fixe total. Vous allez maintenant prévoir les volumes de ventes possibles et fixer le prix du produit en fonction de ceux-ci.

1. Créez un tableau simple et remplissez les éléments avec des données données dans le tableau. Voir la capture d'écran ci-dessous:

2. Entrez les formules appropriées pour calculer les revenus, les coûts variables et les bénéfices. Voir la capture d'écran ci-dessus:
Revenu = Prix unitaire x Unité vendue
Coûts variables = Coût par unité x unité vendue
Profit = Chiffre d'affaires - Coût variable - Coûts fixes

3. Clique le Données > What-If Analysis > Recherche d'objectifs.

4. Dans la boîte de dialogue d'ouverture de recherche d'objectifs, procédez comme suit (voir la capture d'écran ci-dessus):
(1) Spécifiez le Définir la cellule comme la cellule Profit, dans notre cas, c'est la cellule B7;
(2) Spécifiez le Évaluer as 0;
(3) Spécifiez le En changeant de cellule comme cellule de prix unitaire, dans notre cas, il s'agit de la cellule B1.
(4) Cliquez sur OK .

5. Et puis la boîte de dialogue État de la recherche d'objectifs apparaît. Veuillez cliquer sur le OK bouton pour l'appliquer.

Maintenant, il modifie le prix unitaire de 40 à 31.579, et le bénéfice net passe à 0. Par conséquent, si vous prévoyez le volume des ventes est de 50 et le prix unitaire ne peut pas être inférieur à 31.579, sinon une perte se produit.

Télécharger un modèle de seuil de rentabilité complexe

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

Démo: effectuez une analyse du seuil de rentabilité avec la fonction de recherche d'objectif dans Excel


Kutools for Excel: Plus de 300 outils pratiques à portée de main ! Commencez dès aujourd’hui votre essai gratuit de 30 jours sans limitation de fonctionnalités. Télécharger maintenant!

Combinez facilement plusieurs feuilles de calcul / classeurs / fichiers CSV en une seule feuille de calcul / classeur

Il peut être fastidieux de combiner des dizaines de feuilles provenant de différents classeurs en une seule. Mais avec Kutools pour Excel Combiner (feuilles de calcul et classeurs) utilitaire, vous pouvez le faire en quelques clics!


ad combiner feuilles livres 1

Faites une analyse de rentabilité avec la formule

En comparant à la fonctionnalité de recherche d'objectif, nous pouvons également appliquer la formule pour effectuer facilement l'analyse du seuil de rentabilité dans Excel.

1. Créez un tableau simple et remplissez les éléments avec des données données dans le tableau. Dans cette méthode, nous supposons que le profit est égal à 0 et nous avons déjà prévu l'unité vendue, le coût unitaire et les coûts fixes. 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, tapez la formule = B1 * B2 dans la cellule B3 pour calculer les revenus et tapez la formule = B2 * B4 dans la cellule B5 pour les coûts variables. Voir la capture d'écran ci-dessous:

Et puis, lorsque vous modifiez une valeur d'unité prévue vendue, le coût unitaire ou les coûts fixes, la valeur du prix unitaire changera automatiquement. Voir la capture d'écran ci-dessus:

Conseil: Enregistrer une plage sous forme d'insertion automatique (formats de cellule et formules restants) pour une réutilisation future
Il doit être très fastidieux de référencer des cellules et d'appliquer à chaque fois plusieurs formules pour l'analyse du seuil de rentabilité. Kutools for Excel fournit une jolie solution de contournement de Insertion automatique utilitaire pour enregistrer la plage sous forme d'insertion automatique, qui peut conserver les formats de cellule et les formules de la plage. Et puis, vous pouvez réutiliser cette plage en un seul clic dans n'importe quel classeur.



Effectuer une analyse de rentabilité avec un graphique

Si vous avez déjà enregistré les données de vente, vous pouvez également effectuer l'analyse du seuil de rentabilité avec un graphique dans Excel. Cette méthode vous guidera pour créer facilement un graphique de rentabilité.

1. Préparez un tableau des ventes comme illustré ci-dessous. Dans notre cas, nous supposons que les unités vendues, le coût unitaire et les coûts fixes sont fixes, et nous devons effectuer l'analyse du seuil de rentabilité par prix unitaire.

2. Terminez le tableau comme indiqué ci-dessous:
(1) Dans la cellule E2, tapez la formule = D2 * $ G $ 1, et faites glisser sa poignée de remplissage automatique jusqu'à RangeE2: E13;
(2) Dans la cellule F2, tapez la formule = D2 * $ B $ 1 + $ B $ 3et faites glisser sa poignée de remplissage automatique vers le bas jusqu'à Plage F2: F13;
(3) Dans la cellule G2, tapez la formule = E2-F2et faites glisser sa poignée de remplissage automatique jusqu'à la plage G2: G13.

Jusqu'à présent, nous avons terminé les données source du graphique de rentabilité que nous créerons plus tard. Voir la capture d'écran ci-dessous:

3. Dans le tableau, sélectionnez simultanément la colonne Chiffre d'affaires, la colonne Coûts et la colonne Bénéfice, puis cliquez sur insérer > Insérer un graphique en courbes ou en aires > Gamme. Voir la capture d'écran:

4. Maintenant, un graphique en courbes est créé. Veuillez cliquer avec le bouton droit sur le graphique et sélectionner Sélectionner les données dans le menu contextuel. Voir la capture d'écran ci-dessous:

5. Dans la boîte de dialogue Sélectionner la source de données, veuillez:
(1) Dans le Entrées de légende (série) section, sélectionnez l'une des séries selon vos besoins. Dans mon exemple, je sélectionne le Revenu séries;
(2) Cliquez sur le Modifier bouton dans le Étiquettes d'axe horizontal (catégorie) section;
(3) Dans la boîte de dialogue des étiquettes d'axe, veuillez spécifier la colonne Prix unitaire (sauf le nom de la colonne) comme plage d'étiquettes d'axe;
(4) Cliquez sur OK > OK pour enregistrer les modifications.

Maintenant, dans le graphique du seuil de rentabilité, vous verrez que le seuil de rentabilité se produit lorsque le prix est égal à 36, comme illustré ci-dessous:

De même, vous pouvez également créer un graphique du seuil de rentabilité pour analyser le seuil de rentabilité par unités vendues, comme illustré ci-dessous:

Démo: effectuez une analyse de rentabilité avec un graphique dans Excel


Kutools for Excel: Plus de 300 outils pratiques à portée de main ! Commencez dès aujourd’hui votre essai gratuit de 30 jours sans limitation de fonctionnalités. Télécharger maintenant!

Exportez / enregistrez facilement la sélection sous forme de fichiers PDF / CSV / TEXT / HTML séparés dans Excel

Par exemple, lorsque vous aurez terminé votre analyse du seuil de rentabilité dans Excel, vous souhaiterez peut-être faire votre présentation avec cette analyse du seuil de rentabilité. Dans cette condition, vous pouvez appliquer Kutools for Excel Exporter la plage vers un fichier utilitaire pour exporter rapidement la sélection en tant que fichier PDF, fichier HTML, fichier CSV, fichier texte, etc.


doc analyse break even ad 2

Meilleurs outils de productivité bureautique

🤖 Aide à l'IA Kutools: Révolutionner l'analyse des données en s'appuyant sur : Exécution intelligente   |  Générer du code  |  Créer des formules personnalisées  |  Analyser les données et générer des graphiques  |  Invoquer les fonctions Kutools...
Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples    VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule    Gestionnaire de classeurs et de feuilles   |  Centre de ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules    Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

Description


Office Tab apporte une interface à onglets à Office et facilite grandement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
Comments (6)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi,
Thanks for explaining all the details about making a grafiek. It was verry usefull. Only thing I am missing is in how many time u have u're break-even. I can see what the price it needs to be to have a break even, but when u just start u have no idea how many sold units u sell in how many time. So if we just estimate it, on what we estimate it?
This comment was minimized by the moderator on the site
How do I use this same formula when the variable cost differs based on quantity sold? Like the variable cost =$10 if I sell between 0-100, $9 if I sell 101-200, etc. Is this when I am supposed to used a data table? Totally confused, any help appreciated!
This comment was minimized by the moderator on the site
Hi, is it possible that I don't have any fixed costs? I'm buying and selling stuff from home. If I have a cost per unit amount, would this also be considered a 'fixed cost'? Thanks Felix
This comment was minimized by the moderator on the site
cost per unit is a variable cost. (It VARIES on how many units you sell is a good way to remember it)
This comment was minimized by the moderator on the site
When attempting to download the example using a MAC you get a .cat file that is.... useless...
This comment was minimized by the moderator on the site
Simply, understandable and efficient way of doing it! Thanks for simplyfying economy :P
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations