So erstellen Sie ein Break-Even-Diagramm in Excel (mit Bildern)

Inhaltsverzeichnis:

So erstellen Sie ein Break-Even-Diagramm in Excel (mit Bildern)
So erstellen Sie ein Break-Even-Diagramm in Excel (mit Bildern)
Anonim

Die Break-Even-Analyse ist ein Werkzeug zur Bewertung des Gewinnpotenzials eines Geschäftsmodells und zur Bewertung verschiedener Preisstrategien. Sie können Fixkosten, variable Kosten und Preisoptionen ganz einfach in Excel zusammenstellen, um den Break-Even-Point für Ihr Produkt zu bestimmen. Dies ist die Anzahl der Einheiten, die Sie zu dem von Ihnen festgelegten Preis verkaufen müssen, um die Gewinnschwelle zu erreichen.

Schritte

Teil 1 von 5: Erstellen einer Tabelle mit variablen Kosten

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 1

Schritt 1. Öffnen Sie Excel und erstellen Sie eine neue leere Arbeitsmappe

Sie werden in dieser Arbeitsmappe mehrere Blätter erstellen, um alle Ihre Kosten nachverfolgen zu können.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 2

Schritt 2. Klicken Sie unten auf dem Bildschirm auf die Schaltfläche "+" neben "Sheet1"

Dadurch wird ein neues leeres Arbeitsblatt erstellt.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 3

Schritt 3. Benennen Sie das neue Blatt in "VariableCosts

" Dieses Blatt enthält die Tabelle, in der alle variablen Kosten Ihres Produkts aufgeführt sind, z. B. Versand-, Provisions- und andere Kosten.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 4

Schritt 4. Erstellen Sie Kopfzeilenbeschriftungen für das neue Blatt

Um eine Basistabelle für variable Kosten zu erstellen, geben Sie "Beschreibung" in A1 und "Betrag" in B1 ein.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 5

Schritt 5. Geben Sie die Namen der variablen Kosten Ihres Unternehmens in Spalte A ein

Geben Sie unter der Überschrift "Beschreibung" die Arten der variablen Kosten ein, mit denen Sie für Ihr Produkt zu tun haben.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 6

Schritt 6. Lassen Sie Spalte B ("Betrag") vorerst leer

Die tatsächlichen Kosten tragen Sie später im Prozess ein.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 7

Schritt 7. Erstellen Sie eine Tabelle aus den eingegebenen Daten

Das Umwandeln der Daten in eine Tabelle erleichtert das spätere Einfügen in Formeln:

  • Wählen Sie alle Daten aus, einschließlich der Kopfzeile und der leeren Beträge, indem Sie mit der Maus über alle Zellen klicken und sie ziehen.
  • Klicken Sie auf die Schaltfläche "Als Tabelle formatieren". Diese finden Sie auf der Registerkarte Home. Wenn Sie Excel für Mac verwenden, klicken Sie auf die Registerkarte Tabellen, klicken Sie auf die Schaltfläche "Neu" und wählen Sie dann "Tabelle mit Kopfzeilen einfügen".
  • Aktivieren Sie das Kontrollkästchen "Meine Tabelle hat Überschriften". Dadurch bleiben die Beschriftungen in der ersten Zeile als Kopfzeilenbeschriftungen erhalten.
  • Klicken Sie oben rechts auf das Feld "Tabellenname" und nennen Sie es "VariableKosten".

Teil 2 von 5: Erstellen Ihrer Fixkostentabelle

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 8

Schritt 1. Klicken Sie unten auf dem Bildschirm neben "VariableCosts" auf die Schaltfläche "+"

Dadurch wird ein weiteres leeres Arbeitsblatt erstellt.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 9

Schritt 2. Benennen Sie das neue Blatt in "FixedCosts

" In dieser Tabelle sind alle Fixkosten für Ihr Produkt enthalten, z. B. Miete, Versicherung und andere Kosten, die sich nicht ändern.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 10

Schritt 3. Erstellen Sie die Kopfzeilenbeschriftungen

Erstellen Sie wie beim Blatt Variable Kosten ein Label "Beschreibung" in Zelle A1 und ein Label "Betrag" in Zelle B1.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 11

Schritt 4. Geben Sie die Fixkosten Ihres Unternehmens in Spalte A ein

Füllen Sie die erste Spalte mit Beschreibungen Ihrer Fixkosten aus, z. B. "Miete".

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 12

Schritt 5. Lassen Sie Spalte B ("Betrag") vorerst leer

Sie füllen diese Kosten aus, nachdem Sie den Rest der Tabelle erstellt haben.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 13

Schritt 6. Erstellen Sie eine Tabelle aus den eingegebenen Daten

Wählen Sie alles aus, was Sie auf diesem Blatt erstellt haben, einschließlich der Kopfzeilen:

  • Klicken Sie auf der Registerkarte Start auf die Schaltfläche "Als Tabelle formatieren".
  • Aktivieren Sie "Meine Tabelle hat Überschriften", um Zeile 1 in Überschriften für die Tabelle umzuwandeln.
  • Klicken Sie auf das Feld "Tabellenname" und nennen Sie die Tabelle "FixedCosts".

Teil 3 von 5: Erstellen des Break Even Sheet

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 14

Schritt 1. Benennen Sie Sheet1 in "BEP" um und wählen Sie es aus

Dieses Blatt enthält Ihr Haupt-BEP-Diagramm (Break Even Point). Sie müssen es nicht in "BEP" umbenennen, aber es ist einfacher, in Ihrer Arbeitsmappe zu navigieren, wenn Sie dies tun.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 15

Schritt 2. Erstellen Sie das Layout für Ihr Break-Even-Blatt

Erstellen Sie für dieses Beispiel Ihr Blatt mit dem folgenden Layout:

  • A1: Sales - Dies ist das Label für den Sales-Bereich der Tabelle.
  • B2: Preis pro Einheit - Dies ist der Preis, den Sie für jeden verkauften Artikel berechnen.
  • B3: verkaufte Einheiten - Dies ist die Anzahl der Einheiten, die Sie in einem festgelegten Zeitrahmen zum angegebenen Preis verkauft haben.
  • A4: Kosten - Dies ist die Bezeichnung für den Kostenabschnitt der Tabelle.
  • B5: Variable Kosten - Dies sind die Kosten Ihres Produkts, über die Sie die Kontrolle haben (Versand, Provisionssätze usw.)
  • B6: Fixkosten – Dies sind die Kosten Ihres Produkts, über die Sie keine Kontrolle haben (Miete, Versicherung usw.)
  • A7: Umsatz – Dies ist der Geldbetrag, der durch den Verkauf Ihrer Produkte generiert wird, bevor die Kosten berücksichtigt werden.
  • B8: Einheitsmarge – Dies ist der Geldbetrag, den Sie pro Einheit verdienen, nachdem die Kosten berücksichtigt wurden.
  • B9: Bruttomarge – Dies ist der Gesamtbetrag, den Sie für alle verkauften Einheiten abzüglich der Kosten verdienen.
  • A10: BEP - Dies ist die Bezeichnung für den Break Even Point-Abschnitt der Kalkulationstabelle.
  • B11: Einheiten - Dies ist die Anzahl der Einheiten, die Sie verkaufen müssen, um Ihren Kostenaufwand zu entsprechen.
Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 16

Schritt 3. Ändern Sie die numerischen Formate für die Ausgabe- und Eingabezellen

Sie müssen die numerischen Formate für bestimmte Zellen ändern, damit Ihre Daten korrekt angezeigt werden:

  • Markieren Sie C2, C5, C6, C8 und C9. Klicken Sie auf das Dropdown-Menü im Abschnitt "Zahl" des Tabs "Startseite" und wählen Sie "Währung".
  • Markieren Sie C3 und C11. Klicken Sie auf das Dropdown-Menü und wählen Sie "Weitere Zahlenformate". Wählen Sie „Zahl“und setzen Sie dann „Nachkommastellen“auf „0“.
Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 17

Schritt 4. Erstellen Sie Bereiche, die in den Formeln verwendet werden sollen

Wählen und erstellen Sie die folgenden Bereiche, damit Ihre Formeln funktionieren. Dadurch werden Variablen erstellt, die in Ihre Formeln eingefügt werden können, sodass Sie diese Werte leicht referenzieren und aktualisieren können.

  • Wählen Sie B2:C3 und klicken Sie dann auf die Registerkarte "Formeln". Klicken Sie auf "Aus Auswahl erstellen" und dann auf "OK".
  • Wählen Sie B5:C6 und klicken Sie dann auf die Registerkarte "Formeln". Klicken Sie auf "Aus Auswahl erstellen" und dann auf "OK".
  • Wählen Sie B8:C9 und klicken Sie dann auf die Registerkarte "Formeln". Klicken Sie auf "Aus Auswahl erstellen" und dann auf "OK".
  • Wählen Sie B11:C11 und klicken Sie dann auf die Registerkarte "Formeln". Klicken Sie auf "Aus Auswahl erstellen" und dann auf "OK".

Teil 4 von 5: Formeln eingeben

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 18

Schritt 1. Geben Sie die Formel für variable Kosten ein

Dadurch werden die variablen Gesamtkosten für die Anzahl der von Ihnen verkauften Artikel berechnet. Klicken Sie auf C5 und geben Sie die folgende Formel ein:

=SUMME(VariableKosten)*Einheiten_Verkauft

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 19

Schritt 2. Geben Sie die Fixkostenformel ein

Dadurch werden die gesamten Fixkosten für Ihr Produkt berechnet. Klicken Sie auf C6 und geben Sie die folgende Formel ein:

=SUMME(Festkosten)

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 20

Schritt 3. Geben Sie die Einheitsmargenformel ein

Dies berechnet die Marge, die Sie nach Berücksichtigung der variablen Kosten erzielen. Klicken Sie auf C8 und geben Sie die folgende Formel ein:

=Price_Per_Unit-SUM(VariableKosten)

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 21

Schritt 4. Geben Sie die Bruttomarge-Formel ein

Dies bestimmt den Gesamtbetrag, den Sie für alle verkauften Einheiten nach variablen Kosten erwirtschaften. Klicken Sie auf C9 und geben Sie die folgende Formel ein:

=Einheit_Margin*Einheiten_Verkauft

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 22

Schritt 5. Geben Sie die BEP-Formel ein

Dies nimmt Ihre Fixkosten und vergleicht sie mit Ihren Margen, wodurch Sie erfahren, wie viele Einheiten Sie verkaufen müssen, um die Gewinnschwelle zu erreichen. Klicken Sie auf C11 und geben Sie die folgende Formel ein:

=IFERROR(Fixed_Costs/Unit_Margin, 0)

Teil 5 von 5: Ermittlung des Break Even Points

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 23

Schritt 1. Geben Sie die variablen Kosten Ihres Unternehmens ein

Kehren Sie zur Tabelle VariableCosts zurück und geben Sie alle Kosten für Ihr Produkt ein. Je genauer Sie hier sind, desto genauer wird Ihre BEP-Berechnung.

Alle Kosten in der VariableCosts-Tabelle sollten pro verkaufter Einheit angegeben werden

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 24

Schritt 2. Geben Sie die Fixkosten Ihres Unternehmens ein

Tragen Sie diese Kosten in Ihre Fixkostentabelle ein. Dies sind die Kosten für die Führung Ihres Unternehmens und sollten alle auf das gleiche Intervall festgelegt werden (z. B. monatliche Kosten).

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 25

Schritt 3. Geben Sie einen Preis pro Einheit ein

Geben Sie im BEP-Blatt einen anfänglich geschätzten Preis pro Einheit ein. Sie können dies anpassen, während Sie die Berechnungen durchführen

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 26

Schritt 4. Geben Sie die Anzahl der Einheiten ein, die Sie verkaufen möchten

Dies ist die Anzahl der Einheiten, die Sie im gleichen Zeitraum wie Ihre Fixkosten verkaufen möchten. Wenn Ihre Fixkosten beispielsweise die monatliche Miete und Versicherung beinhalten, entsprechen die verkauften Einheiten der Anzahl der Einheiten, die im selben Zeitraum verkauft wurden.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 27

Schritt 5. Lesen Sie die Ausgabe "Einheiten"

Die Ausgabezelle Einheiten (C11) zeigt die Anzahl der Einheiten an, die Sie in Ihrem Zeitrahmen verkaufen müssen, um die Gewinnschwelle zu erreichen. Diese Zahl ändert sich je nach Preis pro Einheit sowie Ihren Tabellen für variable Kosten und Fixkosten.

Erstellen Sie ein Break-Even-Diagramm in Excel Schritt 28

Schritt 6. Nehmen Sie Anpassungen an Preis und Kosten vor

Wenn Sie den Preis pro Einheit ändern, ändert sich die Anzahl der Einheiten, die Sie benötigen, um die Gewinnschwelle zu erreichen. Versuchen Sie, den Preis zu ändern und sehen Sie, was mit Ihrem BEP-Wert passiert.

Tipps

Beliebt nach Thema