- Seuil de rentabilité = charges fixes ÷ marge sur coût variable unitaire : c’est le volume (ou le chiffre d’affaires) à partir duquel le résultat devient positif.
- La marge de sécurité dit de combien l’activité peut baisser avant la perte ; le levier opérationnel dit de combien le résultat bouge quand l’activité bouge d’un pour cent.
- Valeur cible (Données > Analyse de scénarios) cherche la valeur d’une cellule qui donne un résultat voulu ; une formule inverse donne le même chiffre en une cellule.
- Le Solveur résout les cas à plusieurs variables et contraintes (capacité, demande), là où Valeur cible n’en gère qu’une.
« À partir de combien de ventes gagne-t-on de l’argent ? » C’est la question que pose chaque lancement de produit, chaque ouverture de point de vente, chaque demande de crédit. Le seuil de rentabilité y répond avec trois chiffres. Excel apporte en plus deux outils pour travailler à l’envers, de l’objectif vers les hypothèses : Valeur cible et Solveur. Les définitions comptables figurent dans notre article sur le seuil de rentabilité ; cet épisode les met en classeur.
Ce que vous saurez faire à la fin de l’épisode
- calculer marge unitaire, seuil, marge de sécurité et levier opérationnel ;
- déterminer le volume, le prix ou les charges fixes qui donnent un résultat cible ;
- reproduire ces calculs avec Valeur cible ;
- tracer chiffre d’affaires et coûts totaux, et lire le seuil sur la courbe ;
- poser un problème d’optimisation sous contraintes pour le Solveur.
Le modèle
Atlas Négoce étudie une gamme de produits dont les paramètres sont :
| Paramètre | Valeur |
|---|---|
| Prix de vente unitaire HT | 250 |
| Coût variable unitaire (achat, transport, commission) | 150 |
| Charges fixes annuelles | 1 200 000 |
| Volume annuel prévu | 15 000 unités |
Figure 1 : le modèle. Repère 1 : la marge unitaire ; repère 2 : le seuil ; repère 3 : la marge de sécurité.
Les formules
Marge unitaire (MCV) : =B2-B3 → 100,00
Taux de marge : =B6/B2 → 40,0 %
Seuil (unités) : =ARRONDI.SUP(B4/B6;0) → 12 000
Seuil (chiffre d'affaires) : =B8*B2 → 3 000 000
Résultat prévu : =B5*B6-B4 → 300 000
Marge de sécurité : =(B10-B9)/B10 → 20,0 %
Levier opérationnel : =B11/B12 → 5,00
Mois d'atteinte du seuil : =B8/B5*12 → 9,6- La marge sur coût variable (MCV) est ce qu’il reste de chaque vente pour payer les charges fixes : 250 − 150 = 100.
- Le seuil est le volume qui couvre exactement les charges fixes : 1 200 000 ÷ 100 = 12 000 unités, soit un chiffre d’affaires de 3 000 000.
ARRONDI.SUParrondit à l’unité supérieure : on ne vend pas 11 999,4 unités, et à 11 999 le résultat serait encore légèrement négatif. - La marge de sécurité (20 %) est la part du chiffre d’affaires prévu qui peut disparaître avant la perte.
- Le levier opérationnel (5) est le rapport entre la MCV totale (1 500 000) et le résultat (300 000) : une variation du volume de 10 % fait varier le résultat de 50 %. Vérifions : à 16 500 unités (+10 %), le résultat est 16 500 × 100 − 1 200 000 = 450 000, soit +50 % par rapport à 300 000.
- Si les ventes sont régulières, le seuil est atteint après 9,6 mois, soit vers la fin octobre : l’entreprise gagne de l’argent sur les deux derniers mois de l’année.
La courbe du seuil
L’onglet Courbe calcule, pour onze paliers de volume, le chiffre d’affaires, les coûts totaux (variables + fixes) et le résultat. Le résultat change de signe exactement à 12 000 unités :
Figure 2 : le croisement des deux courbes donne le seuil de rentabilité.
| Volume | Chiffre d’affaires | Coûts totaux | Résultat |
|---|---|---|---|
| 0 | 0 | 1 200 000 | −1 200 000 |
| 6 000 | 1 500 000 | 2 100 000 | −600 000 |
| 12 000 | 3 000 000 | 3 000 000 | 0 |
| 16 000 | 4 000 000 | 3 600 000 | 400 000 |
| 20 000 | 5 000 000 | 4 200 000 | 800 000 |
À zéro vente, la perte égale les charges fixes ; chaque unité vendue la réduit de 100 jusqu’à l’équilibre.
Travailler à l’envers : trois objectifs
Le dirigeant veut un résultat de 450 000. Trois leviers, trois formules :
Figure 3 : les trois leviers. Repère 1 : volume ; repère 2 : prix ; repère 3 : charges fixes.
Volume nécessaire : =ARRONDI.SUP((Modèle!B4+B2)/Modèle!B6;0) → 16 500
Prix nécessaire : =(Modèle!B4+B3)/Modèle!B5+Modèle!B3 → 260,00
Charges fixes max : =Modèle!B11-B4 → 1 050 000| Levier | Valeur actuelle | Valeur pour 450 000 | Variation |
|---|---|---|---|
| Volume | 15 000 | 16 500 | +10 % |
| Prix | 250 | 260 | +4 % |
| Charges fixes | 1 200 000 | 1 050 000 | −12,5 % |
Hausse de prix de 4 % ou baisse de charges fixes de 12,5 % : le prix est le levier le plus puissant, car chaque dirham gagné sur le prix tombe directement dans la marge (à volume constant).
Valeur cible : le même résultat par un autre chemin
Quand on ne dispose pas de formule inverse (modèle complexe, plusieurs cellules intermédiaires), la fonction Valeur cible cherche la solution par itérations :
- Sélectionnez la cellule du résultat (
B12). - Données > Analyse de scénarios > Valeur cible.
- Cellule à définir :
B12; Valeur à atteindre :0; Cellule à modifier :B5. - Excel remplace le volume par la valeur qui annule le résultat : 12 000, le seuil.
Avec 450 000 comme valeur à atteindre, on retrouve 16 500. Deux précautions : Valeur cible remplace le contenu de la cellule modifiée par un nombre (la formule éventuelle disparaît), et elle ne gère qu’une variable. Travaillez sur une copie, et notez l’hypothèse d’origine. Là où une formule inverse existe, elle est plus fiable : elle se met à jour toute seule, alors que Valeur cible donne un résultat figé jusqu’à ce qu’on la relance.
Le Solveur : plusieurs variables, des contraintes
Deux produits se partagent une machine de 20 000 heures. Le produit A rapporte une MCV de 100 par unité et consomme 2 heures ; le produit B rapporte 60 et consomme 1 heure. La demande est limitée à 8 000 unités de A et 12 000 de B. Combien produire de chacun pour maximiser la MCV totale ?
Figure 4 : la solution. Repère 1 : les cellules à modifier ; repère 2 : la cellule à maximiser ; repère 3 : le contrôle des contraintes.
Dans l’onglet Solveur, les quantités produites (cellules jaunes E2:E3) sont les cellules variables, la MCV totale (F4) est l’objectif à maximiser, et les contraintes sont : heures utilisées (G4) ≤ 20 000, quantités ≤ demande maximale, quantités entières et positives. Pour l’activer : Fichier > Options > Compléments > Atteindre > Solveur, puis Données > Solveur ; choisissez la méthode « Simplex PL » pour ce type de problème linéaire (les libellés varient légèrement selon la version d’Excel).
Le résultat se vérifie à la main, ce qui est une bonne pratique avec un outil d’optimisation :
- MCV par heure de machine : A rapporte 100 ÷ 2 = 50 par heure, B rapporte 60 par heure.
- B est donc le plus rentable par heure : on produit d’abord sa demande maximale, 12 000 unités, soit 12 000 heures.
- Il reste 8 000 heures, consacrées à A : 4 000 unités (sous la demande de 8 000).
- MCV totale : 12 000 × 60 + 4 000 × 100 = 720 000 + 400 000 = 1 120 000, avec 20 000 heures utilisées.
La cellule B7 contrôle que toutes les contraintes sont respectées. Le Solveur ne remplace pas le jugement : il optimise un modèle, et un modèle oublie (délais, stocks, clients clés).
Les limites du seuil
- Des coûts variables et fixes supposés constants sur toute la plage de volume ; au-delà d’un palier de capacité, les charges fixes augmentent.
- Un seul produit, ou un mix de ventes constant. Avec plusieurs produits, le seuil se calcule sur la MCV moyenne du mix.
- Un résultat avant impôt : pour viser un résultat net, majorez l’objectif de l’impôt.
- Les prévisions de volume sont des hypothèses : testez-les (épisode suivant).
À vous de jouer
Dans le classeur d’exercice :
- Onglet Modèle : complétez les lignes 6 à 15. Retrouvez 12 000 et 3 000 000.
- Si le coût variable passe à 160, que devient le seuil ? Et la marge de sécurité ?
- Utilisez Valeur cible pour trouver le prix qui annule le résultat au volume prévu de 15 000 unités.
- Onglet Solveur : effacez les quantités, lancez le Solveur, puis augmentez la capacité à 24 000 heures : que change la solution ?
Correction. (2) La MCV unitaire passe à 90 : seuil = 1 200 000 ÷ 90 = 13 333,33, arrondi à 13 334 unités (chiffre d’affaires de seuil 3 333 500) ; la marge de sécurité tombe à (15 000 − 13 334) ÷ 15 000 = 11,1 %. (3) Résultat nul : prix = coût variable + charges fixes ÷ volume = 150 + 1 200 000 ÷ 15 000 = 230. (4) Avec 24 000 heures, B reste à 12 000 unités (12 000 heures) et A monte à 6 000 unités (12 000 heures, sous la demande de 8 000) : MCV totale 12 000 × 60 + 6 000 × 100 = 1 320 000.
À retenir
- Seuil = charges fixes ÷ MCV unitaire ; arrondissez à l’unité supérieure.
- Marge de sécurité et levier opérationnel mesurent le risque de l’activité.
- Pour un objectif, utilisez d’abord une formule inverse ; Valeur cible pour les cas complexes, une variable à la fois.
- Le Solveur gère plusieurs variables et contraintes : vérifiez toujours sa solution par un raisonnement simple.
- Le seuil suppose des coûts stables sur la plage de volume considérée.
Épisode 44 : les scénarios et l’analyse de sensibilité, avec tables de données et gestionnaire de scénarios.
Questions fréquentes
Que faire si mes charges ne sont ni fixes ni variables ?
Beaucoup de charges sont semi-variables (un abonnement plus un coût à l’usage, une équipe qui s’agrandit par paliers). On les décompose en une part fixe et une part variable, ou on traite chaque palier de capacité comme un modèle distinct. Le seuil n’est valable que dans la zone d’activité où les hypothèses tiennent.
La fonction Valeur cible peut-elle donner un résultat faux ?
Elle fournit une valeur approchée par itérations, qui peut s’arrêter à quelques décimales de la solution. Elle ne modifie que la cellule désignée et remplace sa formule par un nombre : copiez le modèle avant de l’utiliser, ou préférez une formule inverse chaque fois que c’est possible.
Quand préférer le Solveur à Valeur cible ?
Dès qu’il y a plus d’une variable à ajuster ou des contraintes à respecter : répartition d’une capacité entre produits, budget à partager, quantités à commander. Valeur cible ajuste une seule cellule vers une seule valeur.
Le seuil de rentabilité intègre-t-il l’impôt ?
Non : c’est un seuil de résultat avant impôt. Pour viser un résultat net, on majore le résultat avant impôt de l’impôt sur les sociétés applicable (voir la fiscalité de la société avant de fixer l’objectif).
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.