L'analyse du seuil de rentabilité est un outil permettant d'évaluer les opportunités de profit d'un modèle commercial et de diverses stratégies de tarification. Vous pouvez rechercher des coûts fixes, des coûts variables et des options de tarification dans Excel pour déterminer le seuil de rentabilité d'un produit. Le seuil de rentabilité est le nombre d'unités qui doivent être vendues à un prix fixe pour atteindre le seuil de rentabilité (couvrir les coûts de production).
Étape
Partie 1 sur 5: Création d'un tableau des coûts variables
Étape 1. Ouvrez Excel et créez un classeur
Vous aurez besoin de feuilles de calcul dans le classeur pour garder une trace de tous vos coûts.
Étape 2. Cliquez sur le bouton "+" à côté de "Feuille1" en bas de l'écran
Cela ouvrira un nouveau document de travail.
Étape 3. Nommez la nouvelle feuille de calcul « Coût variable »
Cette feuille de calcul contiendra tous les tableaux qui surveillent tous les coûts variables, tels que les frais d'expédition, les commissions, etc.
Étape 4. Créez une étiquette de titre pour la nouvelle feuille de calcul
Pour créer un tableau des coûts variables standard, saisissez « Description » dans la cellule A1 et « Montant » dans la cellule B1.
Étape 5. Entrez le nom de votre coût variable d'entreprise dans la colonne A
Sous la rubrique "Description", saisissez le type de produit à coûts variables à traiter.
Étape 6. Laissez la colonne B ("Montant") vide pour le moment
Cette colonne sera plus tard remplie avec les coûts qui se sont réellement produits au cours du processus.
Étape 7. Créez une table à partir des données saisies
Transformez les données en tableaux pour pouvoir les saisir facilement dans des formules:
- Sélectionnez toutes les données, y compris la ligne de titre et les cellules vides, en cliquant et en faisant glisser le curseur de la souris sur toutes les cellules.
- Cliquez sur le bouton "Formater en tant que tableau". Vous le trouverez sous l'étiquette Maison. Si vous utilisez Excel pour Mac, cliquez sur l'étiquette Tableaux, cliquez sur le bouton " Nouveau " et sélectionnez " Insérer un tableau avec en-têtes ".
- Cochez la case " Ma table a des en-têtes ". Ainsi, l'étiquette de la première ligne sera définie comme l'étiquette du titre.
- Cliquez sur la case "Nom de la table" dans le coin supérieur droit et changez le nom en "Coût variable"
Partie 2 sur 5: Création d'un tableau des coûts fixes
Étape 1. Cliquez sur le bouton "+" à côté de "Coût variable" en bas de l'écran
Cela ouvrira un nouveau document de travail.
Étape 2. Renommez la nouvelle feuille de calcul en « Coûts fixes »
Cette feuille de calcul contiendra tous les coûts fixes du produit, tels que le loyer, l'assurance et d'autres coûts qui ne changent pas.
Étape 3. Créez une étiquette de titre
Comme pour la feuille de calcul Coût variable, créez une étiquette « Description » dans la cellule A1 et une étiquette « Montant » dans la cellule B1.
Étape 4. Entrez les noms des coûts fixes de votre entreprise dans la colonne A
Remplissez la première colonne avec une description des frais fixes, par exemple « Loyer ».
Étape 5. Laissez la colonne B (« Montant ») vide pour le moment
Vous remplirez ces coûts après avoir rempli tous les documents.
Étape 6. Créez une table à partir des données saisies
Sélectionnez tout ce que vous avez créé sur cette feuille de calcul, y compris le titre:
- Cliquez sur le bouton "Formater en tant que tableau" dans l'étiquette d'accueil.
- Cochez "Mon tableau a des en-têtes" pour changer la ligne 1 en titre du tableau.
- Cliquez sur la case "Nom de la table" et entrez le nom de la table "Coûts fixes"
Partie 3 sur 5: Création d'une feuille de calcul du seuil de rentabilité
Étape 1. Renommez Sheet1 en "BEP" et sélectionnez-le
Cette feuille de calcul contiendra votre graphique principal BEP (Break Even Point). Vous n'êtes pas obligé de changer le nom en « BEP », mais cela vous facilitera la tâche si vous le faites.
Étape 2. Définissez la mise en page de la feuille de calcul du seuil de rentabilité
Pour cet exemple, créez une feuille de calcul en utilisant la disposition suivante:
- A1: Ventes - Il s'agit de l'étiquette de la section Ventes de la feuille de calcul.
- B2: Prix unitaire – Il s'agit du prix facturé pour chaque article vendu.
- B3: Unités vendues – Il s'agit du nombre d'unités vendues à un prix fixe dans un laps de temps donné.
- A4: Coûts – Il s'agit de l'étiquette de la section Coûts de la feuille de calcul.
- B5: Coûts variables – Ce sont les coûts des produits que vous pouvez contrôler (expédition, taux de commission, etc.)
- B6: Coûts fixes – Ce sont les coûts des produits sur lesquels vous n'avez aucun contrôle (location de locaux, assurance, etc.)
- A7: Revenus – Il s'agit du montant d'argent généré par la vente du produit avant déduction des coûts.
- B8: Marge d'unité – Il s'agit du montant d'argent gagné par unité après déduction des coûts.
- B9: Marge bénéficiaire brute – Il s'agit du montant total d'argent généré par la vente d'unités de produit après déduction des coûts.
- A10: BEP – Il s'agit de l'étiquette de la section du seuil de rentabilité de la feuille de calcul.
- B11: Unités – C'est le nombre d'unités qui doivent être vendues pour couvrir les coûts de production.
Étape 3. Modifiez le format numérique des cellules de sortie et d'entrée
Vous devez modifier le format numérique de cellules spécifiques afin que les données s'affichent correctement:
- Mettez en surbrillance C2, C5, C6, C8 et C9. Cliquez sur le menu déroulant dans la section « Numéro » de l'étiquette d'accueil et sélectionnez « Devise ».
- Mettez en surbrillance C3 et C11. Cliquez sur le menu déroulant et sélectionnez "Plus de formats de nombres". Sélectionnez " Nombre " et réglez " Décimales " sur " 0 ".
Étape 4. Créez une plage à utiliser dans les formules
Sélectionnez et créez les plages suivantes pour que la formule fonctionne. De cette façon, des variables peuvent être créées pour être entrées dans des formules afin que vous puissiez facilement suivre et mettre à jour leurs valeurs.
- Sélectionnez B2:C3 puis cliquez sur le libellé "Formules". Cliquez sur « Créer à partir de la sélection » et continuez avec « OK ».
- Sélectionnez B5:C6 puis cliquez sur le libellé "Formules". Cliquez sur « Créer à partir de la sélection » et continuez avec « OK ».
- Sélectionnez B8:C9 puis cliquez sur le libellé "Formules". Cliquez sur « Créer à partir de la sélection » et continuez avec « OK ».
- Sélectionnez B11:C11 puis cliquez sur le libellé "Formules". Cliquez sur " Créer à partir de la sélection " et continuez avec " OK ".
Partie 4 sur 5: Saisie de formules
Étape 1. Saisissez la formule de coût variable
Cette formule calculera le coût variable total des marchandises vendues. Cliquez sur C5 et entrez la formule suivante:
=SOMME(Coût Variable)*Unités_Ventes
Étape 2. Branchez les coûts fixes dans la formule
Excel calculera le coût fixe total de votre produit. Cliquez sur C6 et entrez la formule suivante:
=SOMME(Coût fixe)
Étape 3. Saisissez la formule de marge unitaire
Cette formule calculera la marge résultante après avoir inclus les coûts variables. Cliquez sur C8 et entrez la formule suivante:
=Price_Per_Unit-SUM (Variable de coût)
Étape 4. Saisissez la formule de la marge bénéficiaire brute
Cette formule détermine le montant total du profit pour toutes les unités après déduction des coûts variables. Cliquez sur C9 et entrez la formule suivante:
=Margin_Unit*Unit_Sold
Étape 5. Entrez la formule BEP
Cette formule prend les coûts fixes et les compare à la marge afin que vous sachiez combien d'unités vous devez vendre pour atteindre le seuil de rentabilité. Cliquez sur C11 et entrez la formule suivante:
=SIERREUR(Coût_fixe/Marge_unité, 0)
Partie 5 sur 5: Détermination du seuil de rentabilité
Étape 1. Saisissez les coûts variables de votre entreprise
Retournez au tableau des coûts variables et remplissez tous les coûts associés au produit. Plus les coûts enregistrés sont précis, plus les calculs seront précis.
Chaque coût dans le tableau des coûts variables doit être par unité vendue
Étape 2. Saisissez les coûts fixes de votre entreprise
Saisissez ces coûts dans le tableau des coûts fixes. Il s'agit des coûts de gestion d'une entreprise, tous fixés à intervalles égaux (par exemple, les frais mensuels).
Étape 3. Entrez le prix unitaire
Dans la feuille de calcul BEP, entrez le prix estimé par unité. Vous pourrez l'ajuster au fur et à mesure de l'avancement du calcul.
Étape 4. Entrez le nombre d'unités que vous souhaitez vendre
Il s'agit du nombre de ventes souhaité dans le même délai que les coûts fixes. Par exemple, si vos coûts fixes incluent le loyer mensuel et l'assurance, Unités vendues correspond au nombre d'unités vendues au cours de la même période.
Étape 5. Lisez la sortie "Unité"
La cellule de sortie Unités (C11) affichera le nombre d'unités qui devront être vendues pour atteindre le seuil de rentabilité. Ce chiffre changera en fonction du prix unitaire ainsi que de vos tableaux de coûts variables et de coûts fixes.
Étape 6. Apportez des ajustements aux prix et aux coûts
La modification du prix unitaire modifiera également le nombre d'unités nécessaires pour atteindre le seuil de rentabilité. Essayez de changer le prix de vente de l'unité et voyez l'impact sur la valeur BEP.
