Wie führt man eine Break-even-Analyse in Excel durch?

Angenommen, Sie möchten ein neues Produkt verkaufen und kennen sowohl die variablen Kosten pro Einheit als auch die gesamten Fixkosten. Jetzt wollen Sie die potenziellen Absatzmengen prognostizieren und den Produktpreis entsprechend festlegen.

1. Erstellen Sie eine einfache Tabelle und tragen Sie die bereitgestellten Daten ein. Siehe folgender Screenshot:

2. Geben Sie passende Formeln ein, um Umsatz, variable Kosten und Gewinn zu berechnen. Siehe Screenshot oben:
Umsatz = Stückpreis × Verkaufte Einheiten
Variable Kosten = Kosten pro Einheit × Verkaufte Einheiten
Gewinn = Umsatz – Variable Kosten – Fixkosten

3. Klicken Sie auf Daten > Was-wäre-wenn-Analyse > Zielwertsuche.

4. Führen Sie im geöffneten Dialogfeld „Zielwertsuche“ die folgenden Schritte aus (siehe obiger Screenshot):
(1) Geben Sie für Zielzelle die Gewinn-Zelle an; in unserem Fall ist dies Zelle B7;
(2) Geben Sie für Zielwert 0 ein;
(3) Geben Sie für Veränderbare Zelle die Zelle für den Stückpreis an; in unserem Fall ist dies Zelle B1.
(4) Klicken Sie auf die Schaltfläche OK.

5. Anschließend wird das Dialogfeld „Status der Zielwertsuche“ angezeigt. Klicken Sie auf die Schaltfläche OK, um Ihre Änderungen zu übernehmen.

Der Stückpreis sinkt nun von 40 auf 31,579, und der Nettogewinn beträgt danach 0. Bei einer prognostizierten Absatzmenge von 50 dürfen Sie den Stückpreis daher nicht unter 31,579 festlegen – andernfalls entsteht ein Verlust.

Komplexes Break-even-Vorlage herunterladen

http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx

Im Vergleich zur Zielwertsuche-Funktion lässt sich für die Break-even-Analyse auch ganz einfach eine Formel in Excel verwenden.

1. Erstellen Sie eine einfache Tabelle und tragen Sie die gegebenen Daten ein. Bei dieser Methode gehen wir davon aus, dass der Gewinn bei 0 liegt, und haben bereits die verkauften Einheiten, die Kosten pro Einheit sowie die Fixkosten prognostiziert. Siehe folgender Screenshot:

2. Geben Sie in der Tabelle in Zelle B1 die Formel =B6/B2+B4 ein, um den Stückpreis zu berechnen; in Zelle B3 die Formel =B1*B2, um den Umsatz zu berechnen; und in Zelle B5 die Formel =B2*B4, um die variablen Kosten zu berechnen. Siehe folgender Screenshot:

Wenn Sie anschließend einen der prognostizierten Werte – verkaufte Einheiten, Kosten pro Einheit oder Fixkosten – anpassen, aktualisiert sich der Stückpreis automatisch. Siehe obiger Screenshot:

Tipp : Speichern Sie einen Bereich als AutoText-Eintrag (einschließlich der verbleibenden Zellenformate und Formeln), um ihn später wiederzuverwenden.
Es ist jedes Mal sehr mühsam, Zellen zu referenzieren und mehrere Formeln für die Break-even-Analyse anzuwenden. Kutools für Excel bietet hierfür eine elegante Lösung mit dem AutoText-Werkzeug: Speichern Sie den Bereich einfach als AutoText-Eintrag – Zellenformate und Formeln bleiben dabei vollständig erhalten. Anschließend können Sie diesen Bereich mit nur einem Klick in jeder Arbeitsmappe wiederverwenden.

Sichern Sie sich jetzt eine 30-tägige, vollständig funktionsfähige kostenlose Testversion!

Falls Sie bereits Verkaufsdaten erfasst haben, können Sie die Break-even-Analyse auch mithilfe eines Diagramms in Excel durchführen. Diese Methode führt Sie Schritt für Schritt durch die Erstellung eines Break-even-Diagramms.

1. Bereiten Sie eine Verkaufstabelle wie im folgenden Screenshot gezeigt vor. In unserem Beispiel gehen wir davon aus, dass die verkauften Einheiten, die Kosten pro Einheit und die Fixkosten feststehen, und führen die Break-even-Analyse basierend auf dem Stückpreis durch.

2. Vervollständigen Sie die Tabelle wie folgt:
(1) Geben Sie in Zelle E2 die Formel =D2*$B$1 ein und ziehen Sie deren AutoAusfüll-Griff bis zum Bereich E2:E13;
(2) Geben Sie in Zelle F2 die Formel =D2*$B$1+$B$3 ein und ziehen Sie deren AutoAusfüll-Griff bis zum Bereich F2:F13;
(3) Geben Sie in Zelle G2 die Formel =E2-F2 ein und ziehen Sie deren AutoAusfüll-Griff bis zum Bereich G2:G13.

Damit haben wir die Quelldaten für das später zu erstellende Break-even-Diagramm abgeschlossen. Siehe folgender Screenshot:

3. Markieren Sie in der Tabelle gleichzeitig die Spalten Umsatz, Kosten und Gewinn, und klicken Sie dann auf Einfügen > Linien- oder Flächendiagramm einfügen > Linie. Siehe Screenshot:

4. Jetzt wird ein Liniendiagramm erstellt. Klicken Sie mit der rechten Maustaste auf das Diagramm und wählen Sie im Kontextmenü „Daten auswählen“ aus. Siehe folgender Screenshot:

5. Führen Sie im Dialogfeld Datenquelle auswählen die folgenden Schritte aus:
(1) Wählen Sie im Abschnitt Bereich der Reihennamen eine der Datenreihen nach Bedarf aus. In meinem Beispiel wähle ich die Umsatz-Reihe aus;
(2) Klicken Sie auf die Schaltfläche Bearbeiten im Abschnitt Horizontale Achsenbeschriftungen;
(3) Geben Sie im erscheinenden Dialogfeld Bereich der Achsenbeschriftungen die Spalte „Stückpreis“ (ohne Spaltenüberschrift) als Achsenbeschriftungsbereich an;
(4) Klicken Sie auf OK > OK, um die Änderungen zu speichern.

Im Break-even-Diagramm sehen Sie nun, dass der Break-even-Punkt bei einem Preis von 36 liegt, wie im folgenden Screenshot dargestellt:

Auf ähnliche Weise können Sie auch ein Break-even-Diagramm erstellen, um den Break-even-Punkt basierend auf den verkauften Einheiten zu analysieren, wie im folgenden Screenshot gezeigt:

Auswahl problemlos als separates PDF/CSV/TEXT/HTML-Datei in Excel exportieren/speichern

Wenn Sie beispielsweise Ihre Break-even-Analyse in Excel abgeschlossen haben und diese in einer Präsentation verwenden möchten, können Sie das Bereich in Datei exportieren-Werkzeug von Kutools für Excel nutzen, um die Auswahl blitzschnell als separate PDF-, HTML-, CSV- oder Textdatei zu exportieren.