Tutorials

Dynamische Break-Even-Analyse in Excel erstellen: Das ultimative Tutorial

Melden

Hier ist ein ausführliches Schritt-für-Schritt-Tutorial, um eine dynamische Break-Even-Analyse (Gewinnschwellen-Analyse) in Excel zu erstellen.

Die Break-Even-Analyse (Gewinnschwellen-Analyse) hilft dir zu bestimmen, ab welcher Verkaufsmenge dein Unternehmen die Kosten deckt und beginnt, Gewinn zu machen. In diesem Tutorial erstellen wir eine Tabelle und ein Diagramm, das sich automatisch anpasst, wenn du deine Preise oder Kosten änderst.

1. Die mathematische Grundlage

Bevor wir in Excel starten, kurz die Formel:

  • Deckungsbeitrag = Verkaufspreis pro Stück - Variable Kosten pro Stück
  • Break-Even-Punkt (Stück) = Fixkosten / Deckungsbeitrag

2. Schritt 1: Eingabewerte festlegen

Erstelle zuerst einen Bereich für deine Variablen. So bleibt das Dashboard dynamisch.

Schreibe in die Zellen (beispielhaft):

  • B3: Fixkosten (z.B. 10.000 €)
  • B4: Variable Kosten pro Stück (z.B. 50 €)
  • B5: Verkaufspreis pro Stück (z.B. 150 €)

3. Schritt 2: Die Datentabelle erstellen

Wir benötigen eine Tabelle, die verschiedene Absatzmengen simuliert.

Erstelle folgende Spaltenüberschriften (z.B. ab Zeile 8):

  1. A8: Stückzahl (Menge)
  2. B8: Umsatz (Erlöse)
  3. C8: Gesamtkosten
  4. D8: Gewinn / Verlust

Formeln einfügen:

  • Stückzahl (A9, A10...): Trage hier Werte ein (z.B. 0, 20, 40, 60... bis 200).
  • Umsatz (B9): =$B$5 * A9 (Preis * Menge)
  • Gesamtkosten (C9): =$B$3 + ($B$4 * A9) (Fixkosten + Variable Kosten * Menge)
  • Gewinn / Verlust (D9): =B9 - C9 (Umsatz - Kosten)

Ziehe die Formeln nach unten bis zum Ende deiner Stückzahl-Liste.

4. Schritt 3: Den Break-Even-Punkt berechnen

Um den exakten Punkt dynamisch anzuzeigen, berechne ihn separat in einer Zelle (z.B. B6):

  • Formel: =B3 / (B5 - B4)
  • Ergebnis: In unserem Beispiel 100 Stück.

5. Schritt 4: Das Break-Even-Diagramm erstellen

Jetzt visualisieren wir die Daten.

  1. Markiere den Bereich A8 bis C20 (Stückzahl, Umsatz und Gesamtkosten).
  2. Gehe auf den Reiter Einfügen -> Diagramme -> Punkt-Diagramm mit Linien (XY-Scatter).
  3. Excel erstellt ein Diagramm mit zwei Linien:
    • Blaue Linie: Umsatz (steigt steil an).
    • Orange Linie: Gesamtkosten (startet bei den Fixkosten).
    • Schnittpunkt: Das ist dein Break-Even-Punkt!

6. Schritt 5: Das Diagramm dynamisch formatieren

Damit das Diagramm professionell aussieht:

  • Achsentitel hinzufügen: Klicke auf das Diagramm -> + Symbol -> Achsentitel. Beschrifte die X-Achse mit "Stückzahl" und die Y-Achse mit "Betrag in €".
  • Break-Even-Markierung: Du kannst einen einzelnen Punkt für den Break-Even-Punkt hinzufügen, indem du eine neue Datenreihe (B6 als X-Wert und Umsatz bei Break-Even als Y-Wert) hinzufügst.
  • Interaktivität: Da wir in den Formeln absolute Bezüge (mit $) verwendet haben, kannst du jetzt einfach die Fixkosten in Zelle B3 ändern, und das Diagramm passt sich sofort automatisch an.

7. Profi-Tipp: Den Break-Even-Punkt hervorheben

Möchtest du, dass die Zeile in deiner Tabelle, die dem Break-Even-Punkt am nächsten kommt, farbig markiert wird?

  1. Markiere deine Datentabelle (A9 bis D20).
  2. Gehe zu Bedingte Formatierung -> Neue Regel.
  3. Wähle: Formel zur Ermittlung der zu formatierenden Zellen verwenden.
  4. Gib ein: =$A9>=$B$6 (Angenommen, B6 ist dein berechneter Break-Even-Punkt).
  5. Wähle eine Füllfarbe (z.B. hellgrün).
  6. Jetzt wird alles ab der Gewinnschwelle automatisch grün markiert.

Zusammenfassung der Vorteile:

  • Transparenz: Du siehst sofort, wie Preiserhöhungen den BEP senken.
  • Simulation: Was passiert, wenn die Fixkosten (z.B. Miete) steigen? Einfach Wert ändern, Diagramm anschauen.
  • Entscheidungshilfe: Ideal für Businesspläne oder Budget-Präsentationen.

Viel Erfolg beim Erstellen deiner Analyse!

0