Comment réaliser une analyse du seuil de rentabilité sur Excel : Guide complet
Une analyse du seuil de rentabilité permet de déterminer le niveau de ventes auquel le chiffre d'affaires total est égal au coût total, de sorte que l'entreprise ne réalise ni bénéfice ni perte. Pour en réaliser une dans Excel, saisissez vos coûts fixes, votre prix unitaire et votre coût variable unitaire. Divisez les coûts fixes par la différence entre le prix et le coût variable, puis tracez le graphique du chiffre d'affaires par rapport au coût total.
Ce guide présente les formules, les données dont vous avez besoin, trois étapes dans Excel et un exemple concret. Il montre également comment utiliser Goal Seek et les tables de données pour tester l'impact d'une variation des prix ou des coûts.
Qu'est-ce qu'une analyse du seuil de rentabilité ?
La US Small Business Administration définit le seuil de rentabilité comme « le point auquel le coût total et le chiffre d'affaires total sont égaux ». En dessous de ce point, chaque vente ne permet pas encore à l'entreprise de couvrir ses coûts. Au-dessus, chaque vente génère un bénéfice.
La SBA classe l'analyse du seuil de rentabilité parmi les raisons de calculer les coûts de démarrage, aux côtés de l'estimation des bénéfices et de l'obtention de prêts. Les prêteurs et les investisseurs la demandent souvent car elle montre combien l'entreprise doit vendre avant de cesser de perdre de l'argent.
Une analyse du seuil de rentabilité répond à trois questions pratiques. Combien d'unités devons-nous vendre ? Quel chiffre d'affaires cela représente-t-il ? Et à quel point la réponse est-elle sensible aux variations de prix et de coûts ?
Elle sert également de test rapide pour une nouvelle idée. Avant de vous lancer dans un nouveau produit ou un nouvel emplacement, estimez les trois données d'entrée. Demandez-vous ensuite si le volume requis est réaliste pour le marché que vous ciblez.
Les formules du seuil de rentabilité
La SBA propose deux versions de la formule du seuil de rentabilité.
En unités :
Seuil de rentabilité (unités) = coûts fixes / (prix de vente unitaire - coût variable unitaire)
En valeur (chiffre d'affaires) :
Seuil de rentabilité (chiffre d'affaires) = coûts fixes / marge sur coût variable
La SBA explique le second terme comme « la différence entre le prix d'un produit et ce qu'il coûte pour fabriquer ce produit ». Pour la formule en valeur, elle calcule cette marge sous forme de ratio : le prix moins le coût variable, divisé par le prix.
Cette distinction est importante dans un tableur. La marge sur coût variable par unité est un montant en devises, par exemple $3 sur un produit à $5. Le taux de marge sur coût variable est un pourcentage, par exemple 60%. Utilisez le montant en devises pour la formule en unités et le taux pour la formule en valeur.
La SBA fixe également une limite : « Cette analyse du seuil de rentabilité repose sur l'hypothèse d'un produit ou service unique. » Une section ultérieure explique comment procéder avec plusieurs produits.
Ce dont vous avez besoin avant de commencer
Trois données d'entrée guident chaque analyse du seuil de rentabilité. Répartir correctement les coûts est plus important que le travail sur Excel.
| Donnée d'entrée | Signification | Exemples |
|---|---|---|
| Coûts fixes | Coûts qui restent identiques quel que soit le volume de vos ventes | Loyer, salaires, assurances, abonnements à des logiciels |
| Coût variable unitaire | Coûts qui augmentent avec chaque unité vendue | Matières premières, emballage, frais de paiement, expédition |
| Prix unitaire | Ce que le client paie pour une unité | Prix catalogue, ou prix de vente moyen après remises |
Utilisez la même période pour chaque donnée d'entrée. Si le loyer est mensuel, le résultat sera un seuil de rentabilité mensuel. Mélanger un salaire annuel avec un loyer mensuel produit un chiffre qui ne veut rien dire.
Le coût variable unitaire est la donnée d'entrée que les gens estiment le plus souvent au hasard. Estimez-le plutôt à partir de l'historique. Prenez le total des coûts variables du trimestre précédent et divisez-le par le nombre d'unités vendues au cours de ce même trimestre. Si le résultat varie beaucoup d'un trimestre à l'autre, utilisez la moyenne de plusieurs périodes.
Certains coûts sont mixtes. Un forfait téléphonique avec un abonnement de base et des frais de consommation comporte une partie fixe et une partie variable. Séparez-les plutôt que de deviner dans quelle catégorie ils se situent.
Comment réaliser une analyse du seuil de rentabilité dans Excel
Les trois étapes ci-dessous permettent de construire un modèle et un graphique de seuil de rentabilité fonctionnels. Ils utilisent des formules standards qui fonctionnent également dans Google Sheets.
Étape 1 : Configurer les données d'entrée
Ouvrez une feuille vierge et nommez les cellules A1 à A3 Coûts fixes, Prix unitaire et Coût variable unitaire. Saisissez les valeurs dans les cellules B1 à B3.
Conservez les données d'entrée dans leurs propres cellules, ne les saisissez jamais directement dans les formules. De cette façon, chaque calcul ultérieur se met à jour lorsqu'une donnée change, et vous pouvez tester des scénarios en modifiant une seule cellule.
Appliquez une couleur de remplissage claire aux cellules de données. Cela indique à toute personne ouvrant le fichier quels chiffres elle est autorisée à modifier.
Étape 2 : Calculer le seuil de rentabilité
Ajoutez les calculs dans les lignes 5 à 8.
- Dans la cellule A5, saisissez Marge sur coût variable unitaire, et dans B5, entrez
=B2-B3. - Dans la cellule A6, saisissez Unités au seuil de rentabilité, et dans B6, entrez
=ROUNDUP(B1/B5,0). - Dans la cellule A7, saisissez Taux de marge sur coût variable, et dans B7, entrez
=B5/B2. - Dans la cellule A8, saisissez Chiffre d'affaires au seuil de rentabilité, et dans B8, entrez
=B1/B7.
La fonction ROUNDUP est importante dans B6. Vous ne pouvez pas vendre une fraction d'unité, et un arrondi à l'inférieur vous laisserait juste en dessous du seuil de rentabilité.
Vérifiez que B8 est à peu près égal à B6 multiplié par le prix. Si ce n'est pas le cas, l'une des données d'entrée se trouve dans la mauvaise cellule.
Étape 3 : Construire le graphique du seuil de rentabilité
Sous les calculs, construisez un petit tableau. Dans la colonne D, listez les volumes unitaires à partir de 0 par paliers réguliers, comme 0, 500 et 1,000. Continuez au-delà de votre seuil de rentabilité. Dans la colonne E, calculez le chiffre d'affaires avec =D11*$B$2. Dans la colonne F, calculez le coût total avec =$B$1+D11*$B$3.
Sélectionnez les colonnes D à F et insérez un graphique. Un graphique en nuage de points avec des lignes droites fonctionne le mieux, car il traite la colonne des unités comme un véritable axe numérique.
La ligne du chiffre d'affaires commence à zéro et augmente rapidement. La ligne du coût total commence au niveau des coûts fixes et augmente plus lentement. Leur point d'intersection est le seuil de rentabilité. Ajoutez-y une étiquette de données pour que les lecteurs n'aient pas à l'estimer. Colorez la zone à droite du point d'intersection avec une couleur claire pour indiquer la zone de profit.
Un exemple concret
Un chariot de café a des coûts fixes de $6,000 par mois. Il vend du café à $5.00 la tasse, et chaque tasse coûte $2.00 en grains, lait, gobelets et frais de carte bancaire.
| Calcul | Résultat |
|---|---|
| Marge sur coût variable unitaire | $5.00 - $2.00 = $3.00 |
| Unités au seuil de rentabilité | $6,000 / $3.00 = 2,000 tasses |
| Taux de marge sur coût variable | $3.00 / $5.00 = 60% |
| Chiffre d'affaires au seuil de rentabilité | $6,000 / 0.60 = $10,000 |
Le chariot doit vendre 2,000 tasses par mois, soit $10,000 de chiffre d'affaires, pour couvrir ses coûts. Chaque tasse supplémentaire génère $3.00 de bénéfice.
Un objectif de bénéfice suit la même logique. Pour gagner $3,000 par mois, ajoutez ce montant aux coûts fixes : $9,000 divisé par $3.00 donne 3,000 tasses.
La marge de sécurité indique la marge de manœuvre dont vous disposez. Si le chariot prévoit de vendre 2,600 tasses, il peut lui en manquer 600 avant de commencer à perdre de l'argent. Cela représente environ 23% des ventes prévues.
Utiliser Goal Seek pour trouver le prix d'équilibre
Parfois, la question se pose dans l'autre sens. Vous connaissez votre volume et vous souhaitez savoir quel prix permet d'atteindre le seuil de rentabilité.
La page d'assistance de Microsoft présente ce cas d'utilisation de manière simple. Vous connaissez le résultat que vous souhaitez obtenir d'une formule, mais pas la donnée d'entrée qui le produit. Goal Seek trouve cette donnée en ajustant une seule cellule.
Ajoutez une cellule de bénéfice, par exemple B9, avec =B2*C1-B1-B3*C1, où C1 contient votre volume prévu. Suivez ensuite les étapes de Microsoft. Dans l'onglet Données, dans le groupe Prévision, sélectionnez What-If Analysis, puis Goal Seek. Définissez la cellule B9 sur la valeur 0 en modifiant la cellule B2.
Excel ajuste le prix jusqu'à ce que le bénéfice soit exactement égal à zéro. À 1,500 tasses par mois, le chariot de café aurait besoin d'un prix de $6.00 pour atteindre le seuil de rentabilité.
Microsoft note une limite : « Goal Seek ne fonctionne qu'avec une seule valeur d'entrée variable. » Pour résoudre l'équation avec plusieurs données d'entrée à la fois, elle renvoie au complément Solver.
Sensibilité : que se passe-t-il si le prix ou les coûts changent ?
Un chiffre unique de seuil de rentabilité masque sa fragilité potentielle. Un tableau de sensibilité présente la réponse pour toute une gamme de données d'entrée.
En reprenant l'exemple du chariot de café, une légère variation du prix ou du coût déplace sensiblement le seuil de rentabilité :
| Scénario | Marge sur coût variable | Unités au seuil de rentabilité |
|---|---|---|
| Cas de base : prix de $5.00, coût de $2.00 | $3.00 | 2,000 |
| Le prix augmente à $5.50 | $3.50 | 1,715 |
| Le coût variable augmente à $2.50 | $2.50 | 2,400 |
| Les coûts fixes augmentent à $7,500 | $3.00 | 2,500 |
La fonctionnalité Data Table d'Excel, sous What-If Analysis, permet de créer automatiquement une grille de ce type. Placez une plage de prix dans une colonne et une plage de coûts variables sur une ligne. Dirigez ensuite le tableau vers la cellule des unités au seuil de rentabilité.
Comparez des variations de même ampleur pour voir quelle donnée d'entrée a le plus d'impact. Dans cet exemple, une variation de 50 centimes ajoute 400 tasses au seuil de rentabilité lorsqu'elle touche le coût variable, mais n'en retire que 285 lorsqu'elle s'applique au prix. C'est pourquoi le coût variable est souvent le premier coût qu'il convient de négocier.
Analyse du seuil de rentabilité pour plus d'un produit
La formule de la SBA suppose un produit unique. La plupart des entreprises en vendent plusieurs, chacun ayant sa propre marge.
L'approche habituelle consiste à calculer une marge sur coût variable moyenne pondérée. Estimez la part des ventes de chaque produit, multipliez la marge de chaque produit par sa part, puis additionnez-les. Divisez les coûts fixes par ce chiffre pondéré pour obtenir les unités au seuil de rentabilité pour l'ensemble de la gamme.
Voici un court exemple. Le produit A a une marge de $3 et représente 60% des unités. Le produit B a une marge de $5 et représente 40%. La marge pondérée est de $3 multiplié par 0.6 plus $5 multiplié par 0.4, soit $3.80. Avec des coûts fixes de $7,600, le seuil de rentabilité est de 2,000 unités : 1,200 du produit A et 800 du produit B.
La SBA note que vous pouvez également effectuer le calcul individuellement pour chaque produit si les ventes varient d'un mois à l'autre. Elle ajoute une mise en garde importante à garder à l'esprit : le seuil de rentabilité est une estimation destinée à la planification et à l'évaluation de la viabilité pour les prêteurs, et il n'est pas destiné à remplacer une comptabilité détaillée.
Si la répartition des ventes change, le seuil de rentabilité change également. Un glissement vers des produits à faible marge augmente le seuil de rentabilité, même si les ventes totales restent stables.
Comment présenter une analyse du seuil de rentabilité
La plupart des lecteurs ont besoin de trois chiffres et d'un graphique. Commencez par les unités au seuil de rentabilité, le chiffre d'affaires au seuil de rentabilité et la marge de sécurité. Placez le graphique juste en dessous, avec le point d'intersection clairement identifié.
Présentez ensuite le tableau de sensibilité. Il répond à la question que tout prêteur et gestionnaire se pose ensuite : que se passe-t-il si les coûts augmentent ou si les ventes sont inférieures aux prévisions ?
Laissez les données d'entrée visibles. Une courte liste des coûts fixes, le prix et le coût variable unitaire permettent au lecteur de vérifier votre logique en une minute. La SBA souligne que le seuil de rentabilité est un calcul important dans un plan d'affaires, attendez-vous donc à ce qu'il soit examiné de près.
Erreurs courantes
- Mélanger les périodes. Un loyer mensuel combiné à un salaire annuel donne un résultat incohérent. Convertissez tout sur une seule et même période.
- Utiliser le prix catalogue lorsque les clients paient moins. Si les remises sont courantes, utilisez le prix moyen réellement perçu.
- Oublier les petits coûts variables. Les frais de carte bancaire, l'emballage et l'expédition s'accumulent par unité et modifient le résultat.
- Ignorer la capacité de production. Les coûts fixes augmentent souvent brusquement à certains volumes, par exemple lorsqu'une deuxième équipe ou un espace plus grand est nécessaire. Recalculez à chaque étape.
- Arrondir à l'inférieur. Un résultat fractionnaire signifie que vous avez besoin de l'unité entière supérieure, et non de la précédente.
Aller plus vite grâce à l'IA
La méthode du tableur fonctionne bien une fois que la répartition des coûts est claire. Le plus long est généralement d'y parvenir, car les coûts se trouvent dans un export de grand livre ou dans une pile de factures.
Un espace de travail IA peut effectuer ce tri. Importez l'export des coûts dans Powerdrill Bloom et demandez-lui de classer chaque ligne comme fixe ou variable, puis de calculer les unités et le chiffre d'affaires au seuil de rentabilité. Demandez le tableau de sensibilité et le graphique dans la même requête.
Vérifiez la classification avant de faire confiance au résultat. Les coûts mixtes, comme les services publics, nécessitent souvent un arbitrage que vous seul pouvez faire.
Pour une vision de planification plus large, les pages générateur d'analyse de sensibilité et modélisation financière par IA couvrent des modèles connexes. Notre guide sur le rapport budget vs réel montre comment suivre si les résultats réels se situent au-dessus ou en dessous des prévisions. Pour une question de calendrier plutôt que de totaux, un rapport de flux de trésorerie indique quand l'argent arrive réellement.
Si vous disposez d'un export de coûts prêt, vous pouvez essayer Powerdrill Bloom et comparer son résultat de seuil de rentabilité avec votre propre tableur.
Questions fréquentes
Qu'est-ce qu'une analyse du seuil de rentabilité ?
Une analyse du seuil de rentabilité calcule le niveau de ventes auquel le chiffre d'affaires total est égal au coût total. À ce point, une entreprise ne réalise ni bénéfice ni perte. Elle est couramment utilisée dans les plans d'affaires et les demandes de prêt.
Comment calcule-t-on le seuil de rentabilité en unités ?
Divisez les coûts fixes par la marge sur coût variable unitaire, qui correspond au prix de vente moins le coût variable unitaire. Avec $6,000 de coûts fixes et une marge de $3 par unité, le seuil de rentabilité est de 2,000 unités.
Comment faire une analyse du seuil de rentabilité dans Excel ?
Saisissez les coûts fixes, le prix et le coût variable dans des cellules distinctes. Calculez la marge en soustrayant le coût variable du prix, puis divisez les coûts fixes par celle-ci. Tracez le graphique du chiffre d'affaires et du coût total par rapport aux unités, et lisez le point d'intersection.
Quelle est la différence entre la marge sur coût variable et le taux de marge sur coût variable ?
La marge sur coût variable unitaire est un montant en devises : le prix moins le coût variable. Le taux est ce montant divisé par le prix, exprimé en pourcentage. Utilisez le montant en devises pour les unités au seuil de rentabilité et le taux pour le chiffre d'affaires au seuil de rentabilité.
Pourquoi l'analyse du seuil de rentabilité est-elle importante ?
Elle montre combien une entreprise doit vendre avant de cesser de perdre de l'argent. Elle révèle également quelle donnée d'entrée, comme le prix, le coût variable ou le loyer, déplace le plus ce seuil. La SBA l'inscrit parmi les raisons de calculer les coûts de démarrage.
Sources : US Small Business Administration, Calculez vos coûts de démarrage · Microsoft Support, Utiliser Goal Seek. L'exemple concret utilise des chiffres illustratifs.