Comment faire un graphique de rentabilité dans Excel?
Vous pouvez facilement compiler les coûts fixes, les coûts variables et les options de tarification dans Excel pour déterminer le seuil de rentabilité de votre produit.
L'analyse du seuil de rentabilité est un outil permettant d'évaluer le potentiel de profit d'un modèle commercial et d'évaluer diverses stratégies de tarification. Vous pouvez facilement compiler les coûts fixes, les coûts variables et les options de tarification dans Excel pour déterminer le seuil de rentabilité de votre produit. C'est le nombre d'unités que vous devez vendre au prix que vous avez fixé pour atteindre le seuil de rentabilité.
Partie 1 sur 5: créer votre tableau des coûts variables
- 1Ouvrez Excel et créez un nouveau classeur vierge. Vous allez créer plusieurs feuilles dans ce classeur pour gérer le suivi de tous vos coûts.
- 2Cliquez sur le bouton "+" à côté de "sheet1" en bas de l'écran. Cela créera une nouvelle feuille de calcul vierge.
- 3Renommez la nouvelle feuille en «coûts variables». Cette feuille abritera le tableau qui suit tous les coûts variables de votre produit, tels que l'expédition, les commissions et autres coûts.
- 4Créez des étiquettes d'en-tête pour la nouvelle feuille. Pour créer un tableau des coûts variables de base, entrez "Description" dans A1 et "Montant" dans B1.
- 5Entrez les noms des coûts variables de votre entreprise dans la colonne A. Sous l'en-tête «Description», entrez les types de coûts variables auxquels vous serez confronté pour votre produit.
- 6Laissez la colonne B ("montant") vide pour le moment. Vous remplirez les coûts réels plus tard dans le processus.
- 7Créez un tableau à partir des données que vous avez saisies. Transformer les données en tableau facilitera l'intégration ultérieure des formules:
- Sélectionnez toutes les données, y compris la ligne d'en-tête et les montants vides, en cliquant et en faisant glisser votre souris sur toutes les cellules.
- Cliquez sur le bouton "Formater en tant que tableau". Vous le trouverez dans l'onglet Accueil. Si vous utilisez Excel pour Mac, cliquez sur l'onglet Tableaux, cliquez sur le bouton "Nouveau", puis sélectionnez "Insérer un tableau avec des en-têtes".
- Cochez la case "Mon tableau a des en-têtes". Cela conservera les étiquettes de la première ligne en tant qu'étiquettes d'en-tête.
- Cliquez sur le champ "Nom de la table" dans le coin supérieur droit et nommez-le "VariableCosts".
C'est le nombre d'unités que vous devez vendre au prix que vous avez fixé pour atteindre le seuil de rentabilité.
Partie 2 sur 5: créer votre tableau des coûts fixes
- 1Cliquez sur le bouton «+» à côté de «coûts variables» en bas de l'écran. Cela créera une autre feuille de calcul vierge.
- 2Renommez la nouvelle feuille en «coûts fixes». Cette feuille abritera tous les coûts fixes pour votre produit, tels que le loyer, l' assurance, et d' autres coûts qui ne changent pas.
- 3Créez les étiquettes d'en-tête. Comme avec la feuille Coûts variables, créez une étiquette "Description" dans la cellule A1 et une étiquette "Montant" dans la cellule B1.
- 4Entrez les noms des coûts fixes de votre entreprise dans la colonne A. Remplissez la première colonne avec des descriptions de vos coûts fixes, comme « Loyer».
- 5Laissez la colonne B ("montant") vide pour le moment. Vous remplirez ces coûts après avoir créé le reste de la feuille de calcul.
- 6Créez un tableau à partir des données que vous avez saisies. Sélectionnez tout ce que vous avez créé sur cette feuille, y compris les en-têtes:
- Cliquez sur le bouton "Formater en tant que tableau" dans l'onglet Accueil.
- Cochez "Mon tableau a des en-têtes" pour transformer la ligne 1 en en-têtes pour le tableau.
- Cliquez sur le champ «Nom de la table» et nommez la table «Coûts fixes».
Cette feuille contiendra tous les coûts fixes de votre produit, tels que le loyer, l'assurance et d'autres coûts qui ne changent pas.
Partie 3 sur 5: créer la feuille de rentabilité
- 1Renommez sheet1 en "bep" et sélectionnez-le. Cette feuille abritera votre graphique principal BEP (Break Even Point). Vous n'avez pas besoin de le renommer en «BEP», mais il sera plus facile de naviguer dans votre classeur si vous le faites.
- 2Créez la mise en page de votre feuille de rentabilité. Pour les besoins de cet exemple, créez votre feuille en utilisant la mise en page suivante:
- A1: Ventes - Il s'agit de l'étiquette de la section Ventes de la feuille de calcul.
- B2: Prix unitaire - Ce sera le prix que vous facturez pour chaque article que vous vendez.
- B3: Unités vendues - Il s'agit du nombre d'unités que vous avez vendues au prix spécifié dans un laps de temps défini.
- 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 de votre produit que vous contrôlez (expédition, taux de commission, etc.)
- B6: Coûts fixes - Il s'agit des coûts de votre produit sur lesquels vous n'avez aucun contrôle (loyer de l'installation, assurance, etc.)
- A7: Revenus - Il s'agit du montant d'argent que la vente de vos produits génère avant que les coûts ne soient pris en compte.
- B8: Marge unitaire - Il s'agit du montant d'argent que vous gagnez par unité après prise en compte des coûts.
- B9: Marge brute - Il s'agit du montant total d'argent que vous gagnez pour toutes les unités vendues après les coûts.
- A10: BEP - Il s'agit de l'étiquette de la section Seuil de rentabilité de la feuille de calcul.
- B11: Unités - Il s'agit du nombre d'unités que vous devez vendre pour correspondre à vos dépenses.
- 3Modifiez les formats numériques des cellules de sortie et d'entrée. Vous devrez modifier les formats numériques de certaines cellules pour que vos 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'onglet Accueil et choisissez "Devise".
- Mettez en surbrillance C3 et C11. Cliquez sur le menu déroulant et sélectionnez "Autres formats de nombres ". Sélectionnez «Nombre», puis définissez «Chiffres après la virgule» sur «0».
- 4Créez des plages à utiliser dans les formules. Sélectionnez et créez les plages suivantes pour que vos formules fonctionnent. Cela créera des variables pouvant être insérées dans vos formules, vous permettant de référencer et de mettre à jour facilement ces valeurs.
- Sélectionnez B2:C3 puis cliquez sur l'onglet "Formules". Cliquez sur "Créer à partir de la sélection", puis cliquez sur "OK".
- Sélectionnez B5:C6 puis cliquez sur l'onglet "Formules". Cliquez sur "Créer à partir de la sélection", puis cliquez sur "OK".
- Sélectionnez B8:C9 puis cliquez sur l'onglet "Formules". Cliquez sur "Créer à partir de la sélection", puis cliquez sur "OK".
- Sélectionnez B11:C11 puis cliquez sur l'onglet "Formules". Cliquez sur "Créer à partir de la sélection", puis cliquez sur "OK".
Partie 4 sur 5: entrer vos formules
- 1Saisissez la formule des coûts variables. Cela calculera le total des coûts variables pour le nombre d'articles que vous vendez. Cliquez sur C5 et entrez la formule suivante:
- =SUM(VariableCosts)*Units_Sold
- 2Saisissez la formule des coûts fixes. Cela calculera le total des coûts fixes de votre produit. Cliquez sur C6 et entrez la formule suivante:
- =SOMME(Coûts Fixes)
- 3Entrez la formule de marge unitaire. Cela calculera la marge que vous faites après que les coûts variables aient été pris en compte. Cliquez sur C8 et entrez la formule suivante:
- =Price_Per_Unit-SUM(VariableCosts)
- 4Entrez la formule de la marge brute. Cela détermine le montant total que vous gagnez pour toutes les unités que vous vendez après les coûts variables. Cliquez sur C9 et entrez la formule suivante:
- =Unit_Margin*Units_Sold
- 5Entrez la formule BEP. Cela prend vos coûts fixes et les compare à vos marges, vous informant du nombre d'unités que vous devez vendre pour atteindre le seuil de rentabilité. Cliquez sur C11 et entrez la formule suivante:
- =IFERREUR(Coûts_fixes/Marge_unité,0)
Cette feuille abritera le tableau qui suit tous les coûts variables de votre produit, tels que l'expédition, les commissions et autres coûts.
Partie 5 sur 5: déterminer le seuil de rentabilité
- 1Saisissez les coûts variables de votre entreprise. Retournez au tableau des Coûts Variables et remplissez tous les coûts liés à votre produit. Plus vous êtes précis ici, plus votre calcul de BEP sera précis.
- Chaque coût dans la table VariableCosts doit être par unité vendue.
- 2Saisissez les coûts fixes de votre entreprise. Entrez ces coûts dans votre tableau des coûts fixes. Il s'agit des coûts de gestion de votre entreprise et doivent tous être réglés sur le même intervalle (par exemple, les coûts mensuels).
- 3Saisissez un prix unitaire. Dans la feuille BEP, entrez un prix initial estimé par unité. Vous pourrez ajuster cela au fur et à mesure que vous effectuerez les calculs
- 4Entrez le nombre d'unités que vous souhaitez vendre. Il s'agit du nombre d'unités que vous avez l'intention de vendre au cours de la même période que vos coûts fixes. Par exemple, si vos frais fixes comprennent le loyer mensuel et l'assurance, les unités vendues seront le nombre d'unités vendues au cours de la même période.
- 5Lisez la sortie «unités». La cellule de sortie Unités (C11) affichera le nombre d'unités que vous devrez vendre dans votre délai pour atteindre le seuil de rentabilité. Ce nombre changera en fonction du prix unitaire ainsi que de vos tableaux de coûts variables et de coûts fixes.
- 6Apportez des ajustements au prix et aux coûts. Changer le prix unitaire changera le nombre d'unités dont vous avez besoin pour atteindre le seuil de rentabilité. Essayez de changer le prix et voyez ce qui se passe avec votre valeur BEP.
- Augmenter votre prix de vente vous permettra d'atteindre votre seuil de rentabilité avec un volume de ventes inférieur, mais cela peut également réduire vos ventes totales.
- Lorsque vous saisissez votre volume de ventes et vos dépenses fixes, assurez-vous qu'ils sont tous deux basés sur la même période.
Questions et réponses
- Et si j'ai plusieurs produits? Et si je ne connais pas mes coûts variables, mais que je veux juste qu'ils soient un pourcentage des ventes?Dans ce cas, vous pouvez soit faire chacun séparément, puis additionner le résultat final, soit utiliser une moyenne.
En parallèle
