Comment faire une analyse du seuil de rentabilité dans Excel?
L'analyse du seuil de rentabilité peut vous aider à obtenir le point où le bénéfice net est nul, ce qui signifie que le total des revenus est égal au total des dépenses. Il est très utile de fixer le prix d'un nouveau produit lorsque vous pouvez prévoir votre coût et vos ventes.
- Faites une analyse du seuil de rentabilité avec la fonction de recherche d'objectif
- Faites une analyse de rentabilité avec la formule
- Effectuer une analyse de rentabilité avec un graphique
Faites une analyse du seuil de rentabilité avec la fonction de recherche d'objectif
Supposons que vous vendiez un nouveau produit et que vous connaissiez le coût variable de l'unité et le coût fixe total. Vous allez maintenant prévoir les volumes de ventes possibles et fixer le prix du produit en fonction de ceux-ci.
1. Créez un tableau simple et remplissez les éléments avec des données données dans le tableau. Voir la capture d'écran ci-dessous:
2. Entrez les formules appropriées pour calculer les revenus, les coûts variables et les bénéfices. Voir la capture d'écran ci-dessus:
Revenu = Prix unitaire x Unité vendue
Coûts variables = Coût par unité x unité vendue
Profit = Chiffre d'affaires - Coût variable - Coûts fixes
3. Clique le Données > What-If Analysis > Recherche d'objectifs.
4. Dans la boîte de dialogue d'ouverture de recherche d'objectifs, procédez comme suit (voir la capture d'écran ci-dessus):
(1) Spécifiez le Définir la cellule comme la cellule Profit, dans notre cas, c'est la cellule B7;
(2) Spécifiez le Évaluer as 0;
(3) Spécifiez le En changeant de cellule comme cellule de prix unitaire, dans notre cas, il s'agit de la cellule B1.
(4) Cliquez sur OK .
5. Et puis la boîte de dialogue État de la recherche d'objectifs apparaît. Veuillez cliquer sur le OK bouton pour l'appliquer.
Maintenant, il modifie le prix unitaire de 40 à 31.579, et le bénéfice net passe à 0. Par conséquent, si vous prévoyez le volume des ventes est de 50 et le prix unitaire ne peut pas être inférieur à 31.579, sinon une perte se produit.
Télécharger un modèle de seuil de rentabilité complexe
http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx
Démo: effectuez une analyse du seuil de rentabilité avec la fonction de recherche d'objectif dans Excel
Combinez facilement plusieurs feuilles de calcul / classeurs / fichiers CSV en une seule feuille de calcul / classeur
Il peut être fastidieux de combiner des dizaines de feuilles provenant de différents classeurs en une seule. Mais avec Kutools pour Excel Combiner (feuilles de calcul et classeurs) utilitaire, vous pouvez le faire en quelques clics!
Faites une analyse de rentabilité avec la formule
En comparant à la fonctionnalité de recherche d'objectif, nous pouvons également appliquer la formule pour effectuer facilement l'analyse du seuil de rentabilité dans Excel.
1. Créez un tableau simple et remplissez les éléments avec des données données dans le tableau. Dans cette méthode, nous supposons que le profit est égal à 0 et nous avons déjà prévu l'unité vendue, le coût unitaire et les coûts fixes. Voir la capture d'écran ci-dessous:
2. Dans le tableau, saisissez la formule = B6 / B2 + B4 dans la cellule B1 pour calculer le prix unitaire, tapez la formule = B1 * B2 dans la cellule B3 pour calculer les revenus et tapez la formule = B2 * B4 dans la cellule B5 pour les coûts variables. Voir la capture d'écran ci-dessous:
Et puis, lorsque vous modifiez une valeur d'unité prévue vendue, le coût unitaire ou les coûts fixes, la valeur du prix unitaire changera automatiquement. Voir la capture d'écran ci-dessus:
Conseil: Enregistrer une plage sous forme d'insertion automatique (formats de cellule et formules restants) pour une réutilisation future
Il doit être très fastidieux de référencer des cellules et d'appliquer à chaque fois plusieurs formules pour l'analyse du seuil de rentabilité. Kutools for Excel fournit une jolie solution de contournement de Insertion automatique utilitaire pour enregistrer la plage sous forme d'insertion automatique, qui peut conserver les formats de cellule et les formules de la plage. Et puis, vous pouvez réutiliser cette plage en un seul clic dans n'importe quel classeur.
Effectuer une analyse de rentabilité avec un graphique
Si vous avez déjà enregistré les données de vente, vous pouvez également effectuer l'analyse du seuil de rentabilité avec un graphique dans Excel. Cette méthode vous guidera pour créer facilement un graphique de rentabilité.
1. Préparez un tableau des ventes comme illustré ci-dessous. Dans notre cas, nous supposons que les unités vendues, le coût unitaire et les coûts fixes sont fixes, et nous devons effectuer l'analyse du seuil de rentabilité par prix unitaire.
2. Terminez le tableau comme indiqué ci-dessous:
(1) Dans la cellule E2, tapez la formule = D2 * $ G $ 1, et faites glisser sa poignée de remplissage automatique jusqu'à RangeE2: E13;
(2) Dans la cellule F2, tapez la formule = D2 * $ B $ 1 + $ B $ 3et faites glisser sa poignée de remplissage automatique vers le bas jusqu'à Plage F2: F13;
(3) Dans la cellule G2, tapez la formule = E2-F2et faites glisser sa poignée de remplissage automatique jusqu'à la plage G2: G13.
Jusqu'à présent, nous avons terminé les données source du graphique de rentabilité que nous créerons plus tard. Voir la capture d'écran ci-dessous:
3. Dans le tableau, sélectionnez simultanément la colonne Chiffre d'affaires, la colonne Coûts et la colonne Bénéfice, puis cliquez sur insérer > Insérer un graphique en courbes ou en aires > Gamme. Voir la capture d'écran:
4. Maintenant, un graphique en courbes est créé. Veuillez cliquer avec le bouton droit sur le graphique et sélectionner Sélectionner les données dans le menu contextuel. Voir la capture d'écran ci-dessous:
5. Dans la boîte de dialogue Sélectionner la source de données, veuillez:
(1) Dans le Entrées de légende (série) section, sélectionnez l'une des séries selon vos besoins. Dans mon exemple, je sélectionne le Revenu séries;
(2) Cliquez sur le Modifier bouton dans le Étiquettes d'axe horizontal (catégorie) section;
(3) Dans la boîte de dialogue des étiquettes d'axe, veuillez spécifier la colonne Prix unitaire (sauf le nom de la colonne) comme plage d'étiquettes d'axe;
(4) Cliquez sur OK > OK pour enregistrer les modifications.
Maintenant, dans le graphique du seuil de rentabilité, vous verrez que le seuil de rentabilité se produit lorsque le prix est égal à 36, comme illustré ci-dessous:
De même, vous pouvez également créer un graphique du seuil de rentabilité pour analyser le seuil de rentabilité par unités vendues, comme illustré ci-dessous:
Démo: effectuez une analyse de rentabilité avec un graphique dans Excel
Exportez / enregistrez facilement la sélection sous forme de fichiers PDF / CSV / TEXT / HTML séparés dans Excel
Par exemple, lorsque vous aurez terminé votre analyse du seuil de rentabilité dans Excel, vous souhaiterez peut-être faire votre présentation avec cette analyse du seuil de rentabilité. Dans cette condition, vous pouvez appliquer Kutools for Excel Exporter la plage vers un fichier utilitaire pour exporter rapidement la sélection en tant que fichier PDF, fichier HTML, fichier CSV, fichier texte, etc.
Meilleurs outils de productivité bureautique
Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et facilite grandement votre travail
- Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!