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

Angenommen, Sie planen, ein neues Produkt zu verkaufen, und Sie kennen die variable Kosten pro Einheit und die gesamten Fixkosten. Nun möchten Sie die möglichen Verkaufsvolumen prognostizieren und das Produkt auf dieser Grundlage preisen.

1. Erstellen Sie eine einfache Tabelle und füllen Sie die Elemente mit den gegebenen Daten in der Tabelle aus. Siehe untenstehenden Screenshot:

2. Geben Sie geeignete Formeln ein, um Einnahmen, variable Kosten und Gewinn zu berechnen. Siehe obenstehenden Screenshot:
Einnahmen = Stückpreis x Verkaufte Einheiten
Variable Kosten = Kosten pro Einheit x Verkaufte Einheiten
Gewinn = Einnahmen – Variable Kosten – Fixkosten

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

4. Im sich öffnenden Dialogfeld „Zielwertsuche“ gehen Sie wie folgt vor (siehe obenstehenden Screenshot):
(1) Geben Sie die Zelle festlegen als die Gewinnzelle an, in unserem Fall ist es Zelle B7;
(2) Geben Sie den Zu-Wert als 0;
(3) Geben Sie die Durch Änderung der Zelle als die Zelle für den Stückpreis an, in unserem Fall ist es Zelle B1.
(4) Klicken Sie auf OK Schaltfläche.

5. Anschließend erscheint das Dialogfeld „Status der Zielwertsuche“. Klicken Sie bitte auf die OK Schaltfläche, um es anzuwenden.

Jetzt ändert sich der Stückpreis von 40 auf 31,579, und der Nettogewinn ändert sich auf 0. Daher, wenn Sie das Verkaufsvolumen auf 50 prognostizieren, darf der Stückpreis nicht weniger als 31,579 betragen, sonst entsteht ein Verlust.

Komplexes Break-even-Muster 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 können wir auch die Formel anwenden, um die Break-even-Analyse in Excel einfach durchzuführen.

1. Erstellen Sie eine einfache Tabelle und füllen Sie die Elemente mit den gegebenen Daten in der Tabelle aus. Bei dieser Methode nehmen wir an, dass der Gewinn 0 ist, und wir haben bereits die prognostizierten verkauften Einheiten, die Kosten pro Einheit und die Fixkosten vorhergesagt. Siehe untenstehenden Screenshot:

2. Geben Sie in der Tabelle die Formel =B6/B2+B4 in Zelle B1 zur Berechnung des Stückpreises ein, geben Sie die Formel =B1*B2 in Zelle B3 zur Berechnung der Einnahmen ein und geben Sie die Formel =B2*B4 in Zelle B5 für variable Kosten ein. Siehe untenstehenden Screenshot:

Und dann, wenn Sie einen beliebigen Wert der prognostizierten verkauften Einheiten, der Kosten pro Einheit oder der Fixkosten ändern, ändert sich der Wert des Stückpreises automatisch. Siehe obenstehenden Screenshot:

Tipp: Speichern Sie einen Bereich als AutoText-Eintrag (mit Zellformaten und Formeln) zur späteren Wiederverwendung
Es muss sehr mühsam sein, jedes Mal Zellen zu referenzieren und mehrere Formeln für die Break-even-Analyse anzuwenden. Kutools für Excel bietet eine praktische Lösung mit der AutoText Funktion, um den Bereich als AutoText-Eintrag zu speichern, wobei die Zellformate und Formeln im Bereich erhalten bleiben. Und dann können Sie diesen Bereich mit nur einem Klick in jedem Arbeitsblatt wiederverwenden.

Jetzt eine 30-tägige kostenlose Vollversion-Testversion starten!

Wenn Sie die Verkaufsdaten bereits aufgezeichnet haben, können Sie auch die Break-even-Analyse mit einem Diagramm in Excel erstellen. Diese Methode wird Sie durch die einfache Erstellung eines Break-even-Diagramms führen.

1. Erstellen Sie eine Verkaufstabelle wie im untenstehenden Screenshot gezeigt. In unserem Fall nehmen wir an, dass die verkauften Einheiten, die Kosten pro Einheit und die Fixkosten fest sind, und wir müssen die Break-even-Analyse nach Stückpreis durchführen.

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

Bisher haben wir die Quelldaten des Break-even-Diagramms fertiggestellt, das wir später erstellen werden. Siehe untenstehenden Screenshot:

3. Wählen Sie in der Tabelle gleichzeitig die Spalte „Einnahmen“, die Spalte „Kosten“ und die Spalte „Gewinn“ aus, und klicken Sie dann auf Einfügen > Liniendiagramm oder Flächendiagramm einfügen > Linie. Siehe Screenshot:

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

5. Im Dialogfeld „Datenquelle auswählen“ gehen Sie wie folgt vor:
(1) Im Legenden-Einträge (Reihen) Abschnitt wählen Sie eine der Reihen aus, die Sie benötigen. In meinem Beispiel wähle ich die Einnahmen Reihe;
(2) Klicken Sie auf die Bearbeiten Schaltfläche im Horizontale (Kategorie-) Achsenbeschriftungen Abschnitt;
(3) Geben Sie im erscheinenden Dialogfeld „Achsenbeschriftungen“ die Spalte „Stückpreis“ (außer dem Spaltennamen) als Achsenbeschriftungsbereich an;
(4) Klicken Sie auf OK > OK um die Änderungen zu speichern.

Jetzt sehen Sie im Break-even-Diagramm, dass der Break-even-Punkt auftritt, wenn der Preis 36 entspricht, wie im untenstehenden Screenshot gezeigt:

Ähnlich können Sie auch ein Break-even-Diagramm erstellen, um den Break-even-Punkt anhand der verkauften Einheiten zu analysieren, wie im untenstehenden Screenshot gezeigt:

Auswahl ganz einfach als separate PDF/CSV/TEXT/HTML-Dateien exportieren/speichern in Excel

Zum Beispiel, wenn Sie Ihre Break-even-Analyse in Excel abgeschlossen haben, möchten Sie vielleicht Ihre Präsentation mit dieser Break-even-Analyse erstellen. In diesem Fall können Sie Kutools für Excel’s Bereich in Datei exportieren Funktion verwenden, um die Auswahl schnell als separate PDF-Datei, HTML-Datei, CSV-Datei, Textdatei usw. zu exportieren.