Note: The other languages of the website are Google-translated. Back to English

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 pour Excel comprend plus de 300 outils pratiques pour Excel, gratuits à essayer sans limitation en 30 jours. Téléchargez et essayez gratuitement 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 de différents classeurs en une seule feuille. Mais avec Kutools pour Excel Combiner (feuilles de calcul et classeurs) utilitaire, vous pouvez le faire en quelques clics! Essai gratuit complet de 30 jours!
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:

Pointe: 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. Essai gratuit complet de 30 jours!


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 pour Excel comprend plus de 300 outils pratiques pour Excel, gratuits à essayer sans limitation en 30 jours. Téléchargez et essayez gratuitement maintenant!

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

Par exemple, lorsque vous avez 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's 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. Essai gratuit complet de 30 jours!
doc analyse break even ad 2


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtréeplus
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammesplus
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellulesplus
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDFplus
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement 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!
bas de cabine
Commentaires (6)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
Une manière simple, compréhensible et efficace de le faire ! Merci d'avoir simplifié l'économie :P
Ce commentaire a été minimisé par le modérateur sur le site
Lorsque vous essayez de télécharger l'exemple à l'aide d'un MAC, vous obtenez un fichier .cat qui est... inutile...
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, est-il possible que je n'aie pas de frais fixes ? J'achète et je vends des trucs à la maison. Si j'ai un coût unitaire, cela sera-t-il également considéré comme un « coût fixe » ? Merci Félix
Ce commentaire a été minimisé par le modérateur sur le site
le coût unitaire est un coût variable. (Cela varie selon le nombre d'unités que vous vendez, c'est un bon moyen de s'en souvenir)
Ce commentaire a été minimisé par le modérateur sur le site
Comment utiliser cette même formule lorsque le coût variable diffère en fonction de la quantité vendue ? Comme le coût variable = 10 $ si je vends entre 0 et 100, 9 $ si je vends entre 101 et 200, etc. Est-ce à ce moment-là que je suis censé utiliser une table de données ? Totalement confus, toute aide appréciée!
Ce commentaire a été minimisé par le modérateur sur le site
Hi,
Merci d'avoir expliqué tous les détails sur la création d'un grafiek. C'était très utile. La seule chose qui me manque, c'est combien de temps tu as atteint le seuil de rentabilité. Je peux voir quel prix il faut pour avoir un seuil de rentabilité, mais quand vous commencez tout juste, vous n'avez aucune idée du nombre d'unités vendues que vous vendez en combien de temps. Donc, si nous l'estimons, sur quoi l'estimons-nous ?
Il n'y a pas encore de commentaires postés ici
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés