Comment calculer l'analyse du seuil de rentabilité dans Excel

Comment calculer l'analyse du seuil de rentabilité dans Excel

How Calculate Break Even Analysis Excel

How Calculate Break Even Analysis Excel

Excel a de nombreuses fonctions. Outre les tabulations normales, Excel peut vous aider dans divers calculs commerciaux, entre autres l'analyse du seuil de rentabilité.
Formule d



Comment calculer l'analyse du seuil de rentabilité dans Excel

L'analyse du seuil de rentabilité est l'étude du montant des ventes ou des unités vendues dont une entreprise a besoin pour couvrir toutes ses dépenses sans tenir compte des profits ou des pertes. Il survient après avoir incorporé tous les coûts fixes et variables de fonctionnement des opérations de l'entreprise (source: Investopedia).

Dans cet article, nous nous concentronssur la façon d'utiliser Excel pour calculer l'analyse du seuil de rentabilité.

Qu'est-ce que l'analyse du seuil de rentabilité

Le seuil de rentabilité d'une entreprise est celui où le volume de production et le volume des ventes de biens (ou de services) sont égaux. À ce stade, l'entreprise peut couvrir tous ses coûts. Au sens économique, le seuil de rentabilité est le point d'un indicateur d'une situation critique lorsque les profits et les pertes sont nuls. Habituellement, cet indicateur est exprimé en unités quantitatives ou monétaires.

Plus le seuil de rentabilité est bas, plus la stabilité financière et la solvabilité de l'entreprise sont élevées.

L'analyse du seuil de rentabilité est essentielle dans la planification commerciale et la finance d'entreprise, car les hypothèses sur les coûts et les ventes potentielles déterminent si une entreprise (ou un projet) est sur la bonne voie pour atteindre la rentabilité.

L'analyse du seuil de rentabilité aide les organisations / entreprises à déterminer le nombre d'unités dont elles ont besoin pour vendre avant de pouvoir couvrir leurs coûts variables et la partie de leurs coûts fixes impliquée dans la production de cette unité.

Formule d'analyse du seuil de rentabilité

Pour trouver le seuil de rentabilité, vous devez savoir:

  • Coûts fixes
  • Coûts variables
  • Prix ​​de vente unitaire
  • Revenu

Le seuil de rentabilité se produit lorsque:

Total des coûts fixes (TFC) + Total des coûts variables (TVC) = Revenus

  • Les coûts fixes totaux sont des éléments connus tels que le loyer, les salaires, les services publics, les intérêts débiteurs, l'amortissement et la dépréciation.
  • Les coûts variables totaux comprennent des éléments tels que le matériel direct, les commissions, la main-d'œuvre facturable et les frais, etc.
  • Le revenu est le prix unitaire * Nombre d'unités vendues.

Marge de contribution

Un élément clé du calcul de l'analyse du seuil de rentabilité consiste à comprendre la marge ou le profit généré par les ventes après avoir soustrait les coûts variables pour produire les unités. C'est ce qu'on appelle une marge de contribution. Donc:

Contribution Marin = Prix de vente - Coûts variables

Formule de seuil de rentabilité dans Excel

Vous pouvez calculer le seuil de rentabilité en fonction de deux choses:

  1. Équivalent monétaire: (revenus * coûts fixes) / (revenus - coûts variables).
  2. Unités naturelles: coût fixe / (prix - coûts variables moyens).

En gardant cela à l'esprit, il existe un certain nombre de façons de calculer le seuil de rentabilité dans Excel:

  1. Calculer l'analyse du seuil de rentabilité avec la fonction de recherche d'objectifs (un outil Excel intégré)
  2. Calculer l'analyse du seuil de rentabilité avec une formule
  3. Calculer l'analyse du seuil de rentabilité avec un graphique

Calculer l'analyse du seuil de rentabilité avec Goal-Seek

Cas : Supposons que vous souhaitiez vendre un nouveau produit. Vous connaissez déjà le coût variable unitaire et le coût fixe total. Vous souhaitez prévoir les volumes de ventes possibles et l'utiliser pour fixer le prix du produit. Voici ce que tu dois faire.

  1. Faites un facile table , et remplissez éléments / données .
    Tableau Excel

  2. Dans Excel, entrez les formules appropriées pour calculer le revenu, le coût variable et le profit.
    1. Revenu = Prix unitaire x Unité vendue
    2. Coûts variables = coût par unité x unité vendue
    3. Profit = Revenu - Coût variable - Coût fixe
    4. Utilisez ces formules pour votre calcul.
      Calcul de formule Excel

  3. Sur votre document Excel, cliquez sur Données> Analyse des hypothèses> sélectionnez Recherche d'objectifs.
    Analyse de recherche de Gail
  4. Lorsque vous ouvrez la boîte de dialogue Recherche d'objectifs, procédez comme suit:
    1. Spécifie le Définir la cellule comme la cellule Profit dans ce cas, c'est la cellule B7
    2. Spécifie le Valoriser comme 0
    3. Spécifie le En changeant de cellule comme le Cellule de prix unitaire , dans ce cas, il s'agit de la cellule B1.
    4. Clique le D'accord bouton
      Boîte de dialogue Rechercher un objectif
  5. La boîte de dialogue État de la recherche d'objectifs apparaîtra. Veuillez cliquer sur OK pour l'appliquer.
    Statut de recherche d

La recherche d'objectif changera le prix unitaire de 40 à 31,579 et le bénéfice net passera à 0. N'oubliez pas qu'au seuil de rentabilité le profit est égal à 0. Par conséquent, si vous prévoyez le volume des ventes à 50, le prix unitaire ne peut pas être inférieur à 31,579. . Sinon, vous subirez une perte.

Calculer l'analyse du seuil de rentabilité dans Excel avec une formule

Vous pouvez également calculer le seuil de rentabilité au point sur Excel à l'aide de la formule. Voici comment:

  1. Créez un tableau simple et remplissez les éléments / données. Dans ce scénario, nous supposons que nous connaissons les unités vendues, le coût unitaire, le coût fixe et le profit.
    Tableau de données

  2. Utilisez la formule pour calculer les éléments / données manquants.
    1. Tapez la formule = B6 / B2 + B4 dans la cellule B1 pour calculer le prix unitaire,
    2. Tapez la formule = B 1 * B2 dans la cellule B3 pour calculer les revenus,
    3. Tapez la formule = B2 * B4 dans la cellule B5 pour calculer les coûts variables.

calculer le seuil de rentabilité à l
Remarque
: si vous modifiez une valeur, par exemple, la valeur de l'unité vendue prévue ou le coût unitaire ou les coûts fixes, la valeur du prix unitaire changera automatiquement.

où voulez-vous installer Windows

Calculer l'analyse du seuil de rentabilité avec un graphique

Si vous avez déjà enregistré vos données de ventes, vous pouvez calculer le seuil de rentabilité avec un graphique dans Excel. Voici comment:

  1. Préparez une table de vente.Dans ce cas, nous supposons que nous connaissons déjà les unités vendues, le coût unitaire et les coûts fixes, et nous supposons qu'ils sont fixes. Nous devons faire l'analyse du seuil de rentabilité par prix unitaire.
    Tableau des ventes
  2. Terminez les calculs du tableau en utilisant la formule
    Calculs Excel
    1. Dans la cellule E2, tapez la formule = D2 * $ B $ 1 puis faites glisser sa poignée de remplissage automatique vers le bas jusqu'à la plage E2: E13
    2. Dans la cellule F2, tapez la formule = D2 * $ B $ 1 + $ B $ 3, puis faites glisser sa poignée de remplissage automatique vers le bas sur la plage F2: F13
    3. Dans la cellule G2, tapez la formule = E2-F2, puis faites glisser sa poignée de remplissage automatique jusqu'à la plage G2: G13.
  3. Ce calcul doit vous donner les données source du graphique du seuil de rentabilité.
    Données source
  4. Dans le tableau Excel, sélectionnez le Colonne Revenus , Colonne des coûts n et Colonne de profit simultanément, puis cliquez sur Insérer > Insérer une ligne ou Graphique de zone > Ligne . Cela créera un graphique en courbes.
    création d

  5. Ensuite, cliquez avec le bouton droit sur le graphique. Dans le menu contextuel, cliquez sur Sélectionnez Données.
    Sélectionnez les données

  6. dans le Sélectionnez la source de données boîte de dialogue, procédez comme suit:
    1. dans le Entrées de légende (série) section, sélectionnez l'une des séries dont vous avez besoin. Dans ce cas, nous sélectionnons le Revenu séries
    2. Cliquez sur le bouton Modifier dans le Axe horizontal (catégorie) Étiquettes section
    3. Une boîte de dialogue apparaîtra avec le nom Axis Labels. Dans la case spécifiez le Colonne Prix unitaire (sauf le nom de la colonne) comme plage d'étiquettes d'axe
    4. Cliquez sur D'accord > D'accord pour enregistrer les modifications.
      sélectionner la source de données
  7. Un chat sera créé, appelé le graphique du seuil de rentabilité. Vous remarquerez le seuil de rentabilité, qui se produit lorsque le prix est égal à 36.
    Graphique du seuil de rentabilité
  8. De même, vous pouvez créer un graphique du seuil de rentabilité pour analyser le seuil de rentabilité par unités vendues:

Vous avez terminé. C'est si simple.

Emballer

Vous pouvez modifier l'apparence de vos données grâce à la section des données et aux outils de conception. Excel vous permet de faire beaucoup d'autres choses avec les données.

Si vous recherchez plus de guides ou si vous souhaitez lire plus d'articles sur la technologie, pensez à vous abonner à notre newsletter. Nous publions régulièrement des tutoriels, des articles de presse et des guides pour vous aider.

Lectures recommandées

  1. 13 trucs et astuces Excel pour faire de vous un pro
  2. Top 51 des modèles Excel pour booster votre productivité
  3. Aide-mémoire Microsoft Office Excel
  4. Les raccourcis clavier Excel les plus utiles
  5. Quelle version d'Excel ai-je?