Calculer le seuil de rentabilité sur Excel (Bonus : Représentation graphique)

Aujourd’hui nous allons découvrir la notion de seuil de rentabilité d’une entreprise et en particulier comment la calculer en utilisant Excel.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Le seuil de rentabilité représente le niveau de chiffre d’affaires à atteindre pour réussir à absorber le niveau des charges fixes et atteindre le point mort économique (c’est-à-dire obtenir un résultat qui soit au moins à l’équilibre).

Il s’agit d’un indicateur très important dans le cadre du pilotage d’une entreprise, celui-ci permet en effet de pouvoir estimer rapidement à partir de quel moment l’entreprise va commencer à générer du bénéfice.

Pour calculer le seuil de rentabilité économique, l’entreprise doit être capable de déterminer les trois notions suivantes :

• Le montant du chiffre d’affaires : il s’agit de la somme des ventes et prestations de services réalisées par l’entreprise dans le cadre de son activité,
• Les charges variables : il s’agit des dépenses engendrées par l’entreprise en corrélation directe avec son activité. Le niveau des charges variables est ainsi lié au niveau de son chiffre d’affaires (achat de matières premières, …),
• Les charges fixes : il s’agit des coûts de fonctionnement de l’entreprise, qui sont décorrélées de son activité (amortissements, loyers, …)

Lorsque nous disposons de ces informations, nous pourrons ainsi calculer la marge sur coûts variables, qui est égale à la différence entre le chiffre d’affaires et les coûts variables :

 Marge sur coûts variables = Chiffre  d’affaires – Charges variables 

Une fois la Marge sur coûts variables calculée, nous pouvons rapprocher celle-ci du chiffre d’affaires pour en déterminer un taux de Marge sur coûts variables :

 Taux = Marge sur coûts variables / Chiffre  d’affaires 

Et enfin, pour calculer le Seuil de rentabilité, il suffit de calculer le rapport entre le montant des charges fixes et ce taux de Marge sur coûts variables :

 Seuil de rentabilité = Charges fixes = Taux  de Marge sur coûts variables 

2. Seuil de rentabilité annuel

Pour commencer sur la découverte de la notion de seuil de rentabilité, voyons un exemple très simple :

Ici, nous souhaitons simplement connaître le seuil de niveau de rentabilité d’une entreprise dont les éléments principaux sont connus :

• Le montant du chiffre d’affaires est de 154367€,
• Le niveau des charges variables s’élève à 52% de ce chiffre d’affaires,
• Et pour finir, les charges fixes sont de 60000€ pour l’année complète

Pour simplifier l’analyse, nous allons ici chercher à déterminer les montants annuels.

Pour commencer, nous récupérons le montant du CA à partir de la cellule B9 :

Le montant des charges variables s’obtient en milluplant ce chiffre d’affaires par le taux de charges variables :

 =B15*B10 

Ensuite, nous pouvons obtenir le montant de la marge sur coûts variables par différence :

 =B15-B16 

Nous en profitons pour déterminer le taux de marge sur coûts variables correspondant, que nous pouvons déterminer :

• Soit en divisant le montant de la marge sur coûts variables par le chiffre d’affaires,
• Soit en calculant la différence entre 100% et le taux de charges variables

=B17/B15 
=100%-B10 

Le montant des charges fixes s’obtient en récupérant le montant correspondant.

Et enfin, le résultat net correspond à la marge sur coûts variables à laquelle nous venons retrancher le montant des charges fixes :

 =B17-B18 

Pour finir, il nous reste à calculer le montant ce qui nous intéresse ici, le seuil de rentabilité, qui est égal au montant des charges fixes divisé par le taux de marge sur coûts variables :

Dans cet exemple, le seuil de rentabilité est donc de 125000€ !

3. Seuil de rentabilité détaillé

Maintenant que nous avons bien compris le principe du calcul du seuil de rentabilité, nous pouvons aller un peu loin en calculant le résultat net au mois le mois, ce qui permettra de savoir à partir de quel moment l’entreprise va commencer à générer du profit.

Pour cela, nous allons partir du tableau suivant :

Ici, nous avons donc un tableau avec les douze mois de l’année, et pour chacun d’entre eux le niveau d’activité est reparti en fonction d’une saisonnalité non linéaire.

Nous constatons ici que l’essentielle de l’activité est répartie sur les mois d’été.

La première opération à mettre en place sera ici de déterminer le montant de la saisonnalité cumulée, qui va ensuite servir de base pour les calculs suivants.

Pour cela, nous utilisons la fonction Excel SOMME(), mais d’une manière un peu particulière.

Nous nous plaçons sur la cellule C25, et nous saisissons la fonction SOMME(B25:B25) : nous appelons la fonction comme d’habitude, puis nous cliquons sur la cellule B25, et ensuite, nous tapons simplement sur la touche « : ».

Excel insère alors automatiquement le second B25 :

Avant de valider, nous allons saisir un symbole dollar juste avant le premier 25. De cette manière, la référence à la cellule B25 va devenir mixte, et le point de départ du calcul de la somme va rester fixe lorsque nous allons étendre la cellule :

Et voilà, maintenant nous pouvons calculer le montant du CA mensuel cumulé, en multipliant simplement les saisonnalités déterminées à l’instant par le montant du CA annuel :

 =C25*$B$9 

Attention ici de bien appuyer sur la touche [F4] pour transformer la référence à la cellule B9 en référence absolue qui restera fixée lorsque nous étendrons la formule par la suite. Vous pouvez consulter cet article pour en savoir plus sur les différentes types de références (relative, absolue et mixte) et sur l’utilisation du symbole dollar.

Pour les charges variables, nous multiplions chacun de ces CA par le taux correspondant :

Vous commencez maintenant à en avoir l’habitude, la marge sur coûts variables se calcul par différence entre le chiffre d’affaires et les charges variables :

 =D25-E25 

Les charges fixes sont également à répartir au mois le mois, en divisant le montant correspond par 12, puis en multipliant par le numéro du mois en cours :

 =$B$11/12*A25 

Ce qui permet ensuite de déterminer le résultat net :

À ce moment-là, nous pouvons retrouver à quel moment la marge sur coûts variables atteint le niveau des charges fixes (60000€) : il s’agit du mois d’août :

Maintenant que nous avons pu analyser l’évolution du résultat mois par mois, nous pouvons également analyser la réalisation du seul de rentabilité ;

Pour cela, il suffit de modifier la colonne Charges fixes afin de faire figurer l’ensemble des charges annuelles dès le premier mois. Un peu comme si nous provignions avant même me début de l’exercice.

De cette manière, nous pourrons analyser comment la marge sur coûts variables va permettre de le financer :

Cela nous confirme donc que l’équilibre est atteint au cours du mois d’août.

4. Représentation graphique du seuil de rentabilité

Pour aller encore un peu plus loin et traduire cette analyse sous la formule d’un graphique, il suffit d’ajouter une colonne dans le tableau dans laquelle nous allons afficher le montant des charges fixes annuelles, non réparties :

Ensuite, nous sélectionnons les colonnes Marge/CV et cette nouvelle colonne Charges fixes annuelles (en maintenant la touche [Ctrl] du clavier enfoncé pour sélectionner deux colonnes non collées) :

Puis nous nous rendons dans le menu Insertion > Graphique courbe :

Il ne reste plus alors qu’à donner un titre à notre graphique :