Break-even-Analyse in Excel: Gewinnschwelle, Deckungsbeitrag, Sicherheitsabstand und Szenarien
Break-even-Analyse in Excel Schritt für Schritt: Fixkosten und variable Kosten trennen, Deckungsbeitrag und DB-Quote, Break-even-Menge und -Umsatz, Sicherheitsabstand, Zielgewinn, Szenarien mit Zielwertsuche und Datentabelle sowie Break-even-Diagramm, mit Café-Beispiel.
Kurz gesagt: Die Break-even-Analyse ermittelt die Gewinnschwelle, also die Absatzmenge bzw. den Umsatz, bei dem der Betrieb weder Gewinn noch Verlust macht. Die Formeln: Break-even-Menge = Fixkosten / Deckungsbeitrag je Stück und Break-even-Umsatz = Fixkosten / DB-Quote. In Excel genügen vier Eingabezellen (Preis, variable Kosten je Stück, Fixkosten, aktuelle Menge), um Gewinnschwelle, Sicherheitsabstand, Absatz für einen Zielgewinn und eine Szenariotabelle zu berechnen.
Die Funktionsnamen entsprechen der deutschen Excel-Version mit Semikolon als Trennzeichen. Alle Beträge sind netto; die Zahlen im Beispiel sind frei gewählt.
Schritt 1: Fixe und variable Kosten trennen
| Variable Kosten | Fixkosten |
|---|---|
| Wareneinsatz, Material | Miete, Nebenkosten (Grundanteil) |
| Verpackung, Versand je Auftrag | Gehälter fest angestellter Mitarbeitender |
| Provisionen, Zahlungsgebühren | Versicherungen, Beiträge, Software |
| Fremdleistungen je Auftrag | Leasing, Abschreibungen, Steuerberatung |
Grundlage sind die Werte aus Ihrer GuV oder Ihrer Ausgabenliste: Ergänzen Sie eine Spalte „Kostenart“ (fix/variabel) und summieren Sie mit SUMMEWENN. Mischkosten wie Strom teilen Sie in einen Grund- und einen verbrauchsabhängigen Anteil.
Schritt 2: Eingaben und Formeln
| Zelle | Größe | Formel |
|---|---|---|
| B2 | Preis je Stück (netto) | Eingabe |
| B3 | Variable Kosten je Stück | Eingabe |
| B4 | Fixkosten pro Monat | Eingabe |
| B5 | Aktuelle Menge pro Monat | Eingabe |
| B7 | Deckungsbeitrag je Stück | =B2-B3 |
| B8 | DB-Quote | =WENNFEHLER(B7/B2;0) |
| B9 | Break-even-Menge | =WENN(B7<=0;"keine Gewinnschwelle";AUFRUNDEN(B4/B7;0)) |
| B10 | Break-even-Umsatz | =WENNFEHLER(B4/B8;"") |
| B11 | Gewinn bei aktueller Menge | =B5*B7-B4 |
| B12 | Sicherheitsabstand | =WENNFEHLER((B5-B4/B7)/B5;"") |
Aufgerundet wird, weil eine angefangene Einheit noch keine Deckung bringt.
Beispiel: ein Café
| Größe | Wert |
|---|---|
| Durchschnittlicher Bon (netto) | 7,50 € |
| Variable Kosten je Bon (Wareneinsatz 2,40 € + Kartengebühr 0,30 €) | 2,70 € |
| Deckungsbeitrag je Bon | 4,80 € (DB-Quote 64 %) |
| Fixkosten pro Monat (Miete 3.500 €, Personal 7.000 €, Sonstiges 1.500 €) | 12.000 € |
| Break-even | 2.500 Bons = 18.750 € Umsatz pro Monat |
| Je Öffnungstag (26 Tage) | rund 97 Bons |
| Aktuell | 3.100 Bons |
| Gewinn | (3.100 − 2.500) × 4,80 = 2.880 € |
| Sicherheitsabstand | (3.100 − 2.500) / 3.100 = 19,4 % |
Nachrechnung: 7,50 − 2,70 = 4,80; 4,80 / 7,50 = 64 %; 12.000 / 4,80 = 2.500; 2.500 × 7,50 = 18.750 €; 2.500 / 26 = 96,2, also 97 Bons pro Tag. Der Umsatz kann um knapp ein Fünftel sinken, bevor das Café Verlust macht. Die Tageszahl ist für das Team greifbarer als ein Monatsumsatz.
Schritt 3: Absatz für einen Zielgewinn
=AUFRUNDEN((B4+Zielgewinn)/B7;0)
Für 4.000 € Gewinn pro Monat: (12.000 + 4.000) / 4,80 = 3.333,3, also 3.334 Bons, rund 128 pro Öffnungstag. Beachten Sie, dass der Zielgewinn vor Steuern gilt und bei Einzelunternehmen auch den Unternehmerlohn abdecken muss.
Schritt 4: Szenarien
| Szenario | DB je Bon | Fixkosten | Break-even-Menge |
|---|---|---|---|
| Ausgangslage | 4,80 € | 12.000 € | 2.500 |
| Preis +0,50 € | 5,30 € | 12.000 € | 2.265 |
| Wareneinsatz +0,30 € | 4,50 € | 12.000 € | 2.667 |
| Personalkosten +800 € | 4,80 € | 12.800 € | 2.667 |
Nachrechnung: 12.000 / 5,30 = 2.264,2 → 2.265; 12.000 / 4,50 = 2.666,7 → 2.667; 12.800 / 4,80 = 2.666,7 → 2.667. Eine Preiserhöhung um 0,50 € senkt die Gewinnschwelle um 235 Bons; steigende Einkaufspreise oder Personalkosten erhöhen sie um je 167 Bons.
Automatisieren lässt sich das mit Daten > Was-wäre-wenn-Analyse > Datentabelle (Preise in einer Spalte, Fixkosten in einer Zeile). Mit der Zielwertsuche beantworten Sie die umgekehrte Frage: Welcher Preis ist nötig, damit schon 2.200 Bons reichen? Zielzelle B11 (Gewinn) auf 0 setzen, veränderbare Zelle B2, bei B5 = 2.200. Ergebnis: 12.000 / 2.200 + 2,70 = 8,15 €.
Mehrere Produkte: gewichtete DB-Quote
Bei einem Sortiment mit unterschiedlichen Margen rechnen Sie mit einer nach Umsatzanteil gewichteten DB-Quote: =SUMMENPRODUKT(Umsatzanteile;DB_Quoten). Verschiebt sich der Mix zu Produkten mit niedrigerer Marge, steigt der Break-even-Umsatz, obwohl sich an Preisen und Fixkosten nichts geändert hat. Wie Sie Spanne und Aufschlag je Artikel berechnen, zeigt der Beitrag Handelsspanne berechnen.
Break-even-Diagramm
- Spalte mit Mengen von 0 bis 4.000 in Schritten von 250.
- Umsatz:
=Menge*$B$2; Gesamtkosten:=$B$4+Menge*$B$3. - Liniendiagramm mit beiden Reihen; der Schnittpunkt ist die Gewinnschwelle, die Fläche rechts davon zwischen den Linien der Gewinn.
Weil Fixkosten jeden Monat anfallen, Umsätze aber schwanken, ergänzen Sie die Break-even-Analyse sinnvollerweise um eine Liquiditätsplanung.
Häufige Fehler
- Bruttopreise statt Nettopreise verwenden.
- Unternehmerlohn vergessen: Bei Einzelunternehmen erscheint der Betrieb profitabel, obwohl er den Inhaber nicht ernährt.
- Alle Kosten als fix behandeln, weil die Aufteilung mühsam ist.
- Listenpreis statt realem Durchschnittspreis nach Rabatten.
- Einmal rechnen und nie wieder: Nach Preis-, Miet- oder Lohnänderungen neu berechnen.
Häufige Fragen
Was ist der Unterschied zwischen Deckungsbeitrag und Gewinn?
Der Deckungsbeitrag ist der Betrag, den jeder Verkauf nach Abzug der variablen Kosten zur Deckung der Fixkosten beiträgt. Erst wenn alle Fixkosten gedeckt sind, entsteht Gewinn.
Wie oft sollte ich die Break-even-Analyse aktualisieren?
Mindestens quartalsweise und immer dann, wenn sich Preise, Einkaufskonditionen oder größere Fixkosten ändern.
Funktioniert das in Google Tabellen?
Ja. WENNFEHLER, AUFRUNDEN und SUMMENPRODUKT gibt es auch dort; die Zielwertsuche ist als Add-on verfügbar.
Weitere Beiträge: Finanzen für Kleinunternehmen.