Comment puis-je calculer l'analyse de rentabilité dans Excel?

L’analyse du seuil de rentabilité est l’étude du montant des ventes ou des unités vendues nécessaire pour atteindre le seuil de rentabilité après l’intégration de tous les coûts fixes et variables de la gestion des activités de l’entreprise. L'analyse du seuil de rentabilité est essentielle dans la planification des activités et le financement des entreprises, car les hypothèses relatives aux coûts et aux ventes potentielles déterminent si une entreprise (ou un projet) est sur la voie de la rentabilité.

À titre d’exemple simple, prenons un stand de limonade. Peu importe la quantité de limonade que vous vendez, vous devez quand même payer le même loyer chaque mois. Donc, le loyer est un coût fixe. Comparez cela à vos dépenses en citrons: vous n'acheterez que la quantité de fruits dont vous avez besoin pour satisfaire la demande de citronnade que vous prévoyez pour votre entreprise. En été, cela peut être plus de citrons; en hiver, moins de citrons. Cela fait donc des achats de citron à un coût variable. Les coûts variables augmentent proportionnellement à votre entreprise.

Formule pour l'analyse du seuil de rentabilité

Compte tenu de l'exemple ci-dessus, le seuil de rentabilité est atteint lorsque:

Total des coûts fixes + total des coûts variables = revenus

• Le total des coûts fixes est généralement connu; ils comprennent des éléments tels que le loyer, les salaires, les services publics, les intérêts débiteurs, l’amortissement.
• Le total des coûts variables est plus difficile à connaître, mais ils sont estimables et comprennent des éléments tels que les matériaux directs, la main-d'œuvre facturable, les commissions et les frais.
• Le revenu est le prix unitaire * Nombre d'unités vendues

Avec cette information, nous pouvons résoudre n'importe quel morceau du puzzle algébriquement. Avant d’illustrer cela, quelques points méritent d’être mentionnés.

La première est que, dans la formule, il y a désaccord sur ce qu'est le meilleur égaliseur. La définition standard est «revenu», mais le problème de l’utilisation de revenu est que dire «nous devons vendre un montant X pour couvrir les coûts» exclut les taxes, qui sont une dépense très réelle. Dans la planification des activités, vous devez toujours prendre en compte le résultat opérationnel après impôt. Cette mesure est appelée Résultat opérationnel net après impôt (NOPAT). En utilisant NOPAT, vous intégrez le coût de toutes les opérations réelles, y compris l’effet des taxes. Cependant, la définition largement comprise utilise revenus, c'est ce que nous allons utiliser.

Le deuxième point critique est que la marge de contribution, qui joue un rôle essentiel dans l’effet de levier opérationnel, est au cœur de cette formule.

La troisième chose à noter est que chaque partie de l'équation - total des coûts fixes, total des coûts variables et total des recettes - peut être exprimée en tant que «Total» ou en tant que mesure individuelle, en fonction de la mesure de rentabilité spécifique requise. Ceci est exploré plus en détail dans notre exemple Excel.

Rentabilité

Lorsque vous examinez le total des ventes, la mesure est en dollars ($) et non en unités:

Ventes au seuil de rentabilité = Total des coûts fixes / (marge de contribution)

Marge de contribution = 1 - (Coûts / revenus variables)

Notez que cela peut être par unité ou total, car il est exprimé en pourcentage.

Unités de rentabilité

Lorsque vous examinez les ventes totales, la mesure est exprimée en unités et non en dollars. Selon les données dont vous disposez, vous devrez peut-être convertir les valeurs monétaires totales en valeurs unitaires:

Unités de rentabilité = Total des coûts fixes / (Prix unitaire - Coût variable unitaire)

Prix ​​d'équilibre

Ici, nous résolvons le prix étant donné un coût fixe et variable connu, ainsi qu'un nombre estimé d'unités vendues. Notez que dans les deux premières formules, nous connaissons le prix de vente et calculons essentiellement la quantité vendue au seuil de rentabilité. Mais dans ce cas, nous devons estimer à la fois le nombre d’unités vendues (ou la quantité totale vendue) et le relier en fonction du prix de vente recherché.

Coûts variables Pourcentage par unité = Total des coûts variables / (Total variable + Total des coûts fixes)

Total des coûts fixes par unité = total des coûts fixes / nombre total d'unités

Prix ​​d'équilibre = 1 / ((1 - Total des coûts variables par unité) * (Total des coûts fixes par unité))

Pour l’essentiel, toutes ces formules peuvent être considérées comme une forme d’analyse de la période de récupération, à ceci près que la "durée en années" correspond bien au temps nécessaire pour générer le nombre de ventes requis dans les calculs ci-dessus.

Analyse de rentabilité dans Excel

Maintenant que nous savons en quoi consiste l’analyse de rentabilité, nous pouvons commencer à la modéliser dans Excel. Il y a plusieurs façons d'y parvenir. Les deux plus utiles sont de créer une calculatrice de rentabilité ou d’utiliser Goal Seek, un outil Excel intégré.

Nous présentons la calculatrice, car elle se conforme mieux aux meilleures pratiques en matière de modélisation financière, qui stipule que les formules doivent être ventilées et vérifiables.

En créant une analyse de scénario, nous pouvons dire à Excel de calculer en fonction de l'unité. ( Remarque: si la table semble petite, cliquez avec le bouton droit de la souris sur l'image et ouvrez-la dans un nouvel onglet pour obtenir une résolution supérieure.)

Ou basé sur le prix:

Enfin, nous pouvons facilement construire une matrice de sensibilité pour explorer la manière dont ces facteurs interagissent. Compte tenu des diverses structures de coûts, nous pouvons voir une fourchette de prix allant de 28 à 133 dollars.