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é.
Pas
Partie 1 sur 5: Création de votre tableau des coûts variables
Étape 1. Ouvrez 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.
Étape 2. Cliquez sur le bouton "+" à côté de "Feuille1" en bas de l'écran
Cela créera une nouvelle feuille de calcul vierge.
Étape 3. Renommez la nouvelle feuille en "VariableCosts
" 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.
Étape 4. Cré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.
Étape 5. Entrez les noms des coûts variables de votre entreprise dans la colonne A
Sous l'en-tête « Description », saisissez les types de coûts variables auxquels vous serez confronté pour votre produit.
Étape 6. Laissez la colonne B ("Montant") vide pour le moment
Vous remplirez les coûts réels plus tard dans le processus.
Étape 7. Cré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".
Partie 2 sur 5: Création de votre tableau des coûts fixes
Étape 1. Cliquez sur le bouton "+" à côté de "VariableCosts" en bas de l'écran
Cela créera une autre feuille de calcul vierge.
Étape 2. Renommez la nouvelle feuille en "FixedCosts
" 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.
Étape 3. Cré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.
Étape 4. Entrez les noms des coûts fixes de votre entreprise dans la colonne A
Remplissez la première colonne avec des descriptions de vos frais fixes, comme « Loyer ».
Étape 5. Laissez la colonne B ("Montant") vide pour le moment
Vous remplirez ces coûts après avoir créé le reste de la feuille de calcul.
Étape 6. Cré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 ».
Partie 3 sur 5: Création de la feuille de rentabilité
Étape 1. Renommez 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.
Étape 2. Cré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.
Étape 3. Modifiez 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 "Plus de formats de nombres". Sélectionnez « Nombre », puis définissez « Chiffres après la virgule » sur « 0 ».
Étape 4. Cré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: Saisie de vos formules
Étape 1. Saisissez 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
Étape 2. Saisissez 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)
Étape 3. Saisissez 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)
Étape 4. Saisissez 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
Étape 5. Entrez 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:
=SIERREUR(Coûts_fixes/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 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
Étape 2. Saisissez 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).
Étape 3. Saisissez 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
Étape 4. Entrez 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.
Étape 5. Lisez 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.
Étape 6. Apportez 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.
