Wie man eine Sensitivitätsanalyse in Excel durchführt: Schritt für Schritt

Um eine Sensitivitätsanalyse in Excel durchzuführen, platzieren Sie jede Eingabevariable Ihres Modells in einer eigenen Zelle. Ändern Sie dann diese Eingaben und protokollieren Sie, wie sich das Ergebnis verändert. Verwenden Sie eine Datentabelle mit einer Variablen für eine Eingabe, eine Datentabelle mit zwei Variablen für zwei Eingaben und den Szenario-Manager für vollständige Fallstudien. Schließen Sie mit einem Tornado-Diagramm ab, das die Eingaben nach ihrer Auswirkung ordnet.

Dieser Leitfaden erklärt, was eine Sensitivitätsanalyse ist, wie man das Modell aufbaut und wie man jedes Excel-Tool verwendet. Er enthält ein praktisches Beispiel mit echten Formeln, eine schnellere Methode mit AI und die Fehler, die es zu vermeiden gilt.

Was eine Sensitivitätsanalyse ist

Eine Sensitivitätsanalyse zeigt, wie stark sich ein Ergebnis ändert, wenn sich eine Annahme ändert. Sie beantwortet Fragen wie: „Was passiert mit dem Gewinn, wenn der Preis um 10 % sinkt?“

Die meisten Geschäftsmodelle beruhen auf Schätzungen. Absatzvolumen, Preis, Kosten und Wachstumsraten sind allesamt Schätzwerte. Eine Sensitivitätsanalyse testet, welche dieser Schätzungen am wichtigsten sind.

Excel gruppiert diese Werkzeuge unter Was-wäre-wenn-Analyse. Microsoft definiert dies als „den Prozess der Änderung von Werten in Zellen“, um zu sehen, wie sich die Ergebnisse von Formeln ändern.

Microsoft listet drei integrierte Tools auf: „Szenarien, Zielwertsuche und Datentabellen.“ Jedes hat eine andere Aufgabe:

• Datentabellen zeigen ein Ergebnis über viele Werte von einer oder zwei Eingaben hinweg an.
• Szenario-Manager speichert vollständige Sätze von Eingaben, wie zum Beispiel einen Worst-Case und einen Best-Case.
• Zielwertsuche arbeitet rückwärts. Sie findet die Eingabe, die benötigt wird, um ein Zielergebnis zu erreichen.

Eine Sensitivitätsanalyse verwendet normalerweise die ersten beiden. Die Zielwertsuche hilft bei einer verwandten Frage, wie etwa dem Break-even-Preis.

Das Modell vor dem Start einrichten

Die Tools von Excel funktionieren nur, wenn das Modell sauber aufgebaut ist. Prüfen Sie zuerst diese Punkte.

Jede Eingabe in eine eigene Zelle setzen. Einheiten, Preis, Kosten und Raten benötigen jeweils eine Zelle. Tippen Sie keine Zahlen direkt in Formeln ein.

Das Ergebnis mit den Eingaben verknüpfen. Die Ergebniszelle, wie zum Beispiel der Gewinn, muss eine Formel sein, die sich auf die Eingabezellen bezieht. Wenn dies nicht der Fall ist, wird sich nichts ändern, wenn Excel neue Werte einsetzt.

Eingaben zusammenhalten. Gruppieren Sie diese in einem Block oben im Blatt. Beschriften Sie jede einzelne und färben Sie die Eingabezellen ein, damit sie hervorstechen.

Ein Ergebnis zum Testen auswählen. Eine Sensitivitätsanalyse ist einfacher zu lesen, wenn sie eine einzige Ausgangsgröße verfolgt, wie den Gewinn, den Kapitalwert (Net Present Value) oder den Kassenbestand am Jahresende.

Den Basisfall notieren. Schreiben Sie die Ausgangswerte auf. Jedes Ergebnis in der Analyse wird mit dieser Basis verglichen.

Hier ist ein einfaches Gewinnmodell zum Mitmachen. Es handelt sich um einen fiktiven Fahrradladen, Northgate Cycles:

Zelle	Beschriftung	Wert
B1	Verkaufte Einheiten	2.400
B2	Preis pro Einheit	$850
B3	Variable Kosten pro Einheit	$510
B4	Fixkosten	$520.000
B5	Gewinn	=B1*(B2-B3)-B4

Bei diesen Basiswerten beträgt der Gewinn $296.000.

Wie man eine Sensitivitätsanalyse mit einer Variablen mittels Datentabelle durchführt

Eine Datentabelle mit einer Variablen testet eine Reihe von Werten für eine Eingabe. Microsoft sagt, sie zeigt, „wie verschiedene Werte einer Variablen in einer oder mehreren Formeln die Ergebnisse verändern werden“.

So testen Sie den Preis im Beispielmodell:

1. Tippen Sie in D2 bis D6 die zu testenden Preise ein: $800, $825, $850, $875 und $900.
2. Tippen Sie in E1, eine Zeile oberhalb und eine Spalte rechts vom ersten Preis, =B5 ein.
3. Markieren Sie D1:E6.
4. Wählen Sie auf der Registerkarte Daten die Option Was-wäre-wenn-Analyse > Datentabelle.
5. Geben Sie im Feld Spalteneingabezelle B2 ein, die Preiszelle. Lassen Sie das Feld Zeileneingabezelle leer.
6. Klicken Sie auf OK.

Excel füllt E2:E6 mit dem Gewinn für jeden Preis. Die Platzierung der Formel ist wichtig. Microsoft sagt, man solle sie „eine Zeile oberhalb und eine Zelle rechts von der Wertespalte“ eingeben.

Die Ergebnisse für Northgate Cycles:

Preis	Gewinn
$800	$176.000
$825	$236.000
$850	$296.000
$875	$356.000
$900	$416.000

Jede Preisänderung um $25 verändert den Gewinn um $60.000. Das ist die Sensitivität des Gewinns gegenüber dem Preis.

Wie man eine Sensitivitätsanalyse mit zwei Variablen durchführt

Eine Datentabelle mit zwei Variablen testet zwei Eingaben gleichzeitig. Microsoft sagt, sie zeigt, „wie verschiedene Werte von zwei Variablen in einer Formel die Ergebnisse dieser Formel verändern werden“.

So testen Sie Preis und Einheiten zusammen:

1. Tippen Sie in eine leere Zelle, wie D10, =B5 ein.
2. Tippen Sie darunter, in D11 bis D13, die Stückzahlen ein: 2.000, 2.400 und 2.800.
3. Tippen Sie rechts davon, in E10 bis G10, die Preise ein: $800, $850 und $900.
4. Markieren Sie D10:G13.
5. Wählen Sie Was-wäre-wenn-Analyse > Datentabelle.
6. Geben Sie im Feld Zeileneingabezelle B2 ein. Geben Sie im Feld Spalteneingabezelle B1 ein.
7. Klicken Sie auf OK.

Das Ergebnis ist ein Raster von Gewinnen:

Einheiten \ Preis	$800	$850	$900
2.000	$60.000	$160.000	$260.000
2.400	$176.000	$296.000	$416.000
2.800	$292.000	$432.000	$572.000

Das Raster zeigt, dass der Laden in jeder getesteten Kombination profitabel bleibt. Die schwächste Ecke, 2.000 Einheiten bei $800, erwirtschaftet immer noch $60.000.

Es gibt eine feste Grenze. Microsoft sagt: „Eine Datentabelle kann nicht mehr als zwei Variablen aufnehmen.“ Für mehr Eingaben verwenden Sie den Szenario-Manager.

Datentabellen werden zudem jedes Mal neu berechnet, wenn das Blatt neu berechnet wird. In einer großen Arbeitsmappe kann das Excel verlangsamen. Microsoft schlägt vor, die Berechnung so einzustellen, dass Datentabellen nur neu berechnet werden, wenn Sie F9 drücken.

Wie man vollständige Fälle mit dem Szenario-Manager vergleicht

Der Szenario-Manager speichert komplette Sätze von Eingaben und tauscht sie aus. Microsoft beschreibt ein Szenario als „einen Satz von Werten, den Excel speichert und automatisch in Ihrem Arbeitsblatt ersetzen kann“.

So richten Sie drei Fälle ein:

1. Wählen Sie auf der Registerkarte Daten die Option Was-wäre-wenn-Analyse > Szenario-Manager.
2. Klicken Sie auf Hinzufügen, nennen Sie das Szenario Basis und geben Sie B1:B4 als veränderbare Zellen ein. Behalten Sie die aktuellen Werte bei.
3. Fügen Sie einen Worst-Case hinzu: 2.000 Einheiten, $825 Preis, $530 variable Kosten und $540.000 Fixkosten.
4. Fügen Sie einen Best-Case hinzu: 2.800 Einheiten, $875 Preis, $495 variable Kosten und $510.000 Fixkosten.
5. Wählen Sie ein Szenario aus und klicken Sie auf Anzeigen, um dessen Werte zu laden.
6. Klicken Sie auf Zusammenfassung und wählen Sie B5 als Ergebniszelle aus, um einen Bericht zu erstellen.

Für Northgate Cycles zeigt die Zusammenfassung drei Gewinnwerte. Der Worst-Case erzielt $50.000, der Basisfall $296.000 und der Best-Case $554.000.

Microsoft weist auf einige Einschränkungen hin. Jedes Szenario kann bis zu 32 veränderbare Werte enthalten. Der Zusammenfassungsbericht ist nicht automatisch: „Szenarioberichte werden nicht automatisch neu berechnet.“ Wenn Sie ein Szenario ändern, erstellen Sie eine neue Zusammenfassung.

Wie man einen Break-even-Punkt mit der Zielwertsuche findet

Eine Sensitivitätsanalyse zeigt, wie weit sich das Ergebnis bewegt. Die Zielwertsuche beantwortet die umgekehrte Frage: Welcher Eingabewert ergibt ein bestimmtes Zielergebnis?

Microsoft sagt, die Zielwertsuche „nimmt ein Ergebnis und bestimmt mögliche Eingabewerte, die dieses Ergebnis erzeugen“. Ihr Beispiel findet den Zinssatz, der für eine Kreditrate benötigt wird.

So finden Sie den Break-even-Preis für Northgate Cycles:

1. Wählen Sie auf der Registerkarte Daten die Option Was-wäre-wenn-Analyse > Zielwertsuche.
2. Geben Sie im Feld Zielzelle B5 ein, die Gewinnzelle.
3. Geben Sie im Feld Zielwert 0 ein.
4. Geben Sie im Feld Veränderbare Zelle B2 ein, die Preiszelle.
5. Klicken Sie auf OK.

Excel ändert den Preis, bis der Gewinn Null erreicht. Bei 2.400 Einheiten und den Basiskosten liegt der Break-even-Preis bei etwa $726,67. Das sind etwa 15 % unter dem aktuellen Preis von $850.

Die Zielwertsuche hat eine Einschränkung. Microsoft sagt, sie „funktioniert nur mit einem variablen Eingabewert“. Für mehr als eine Eingabe verweist Microsoft auf das Solver-Add-In.

Wie man ein Tornado-Diagramm erstellt

Ein Tornado-Diagramm ordnet Eingaben danach, wie stark sie das Ergebnis beeinflussen. Es ist der klarste Weg, eine Sensitivitätsanalyse zu präsentieren.

So erstellen Sie eines:

1. Wählen Sie einen Testbereich, zum Beispiel jede Eingabe 10 % niedriger und 10 % höher.
2. Ändern Sie jeweils eine Eingabe und protokollieren Sie das niedrige und das hohe Ergebnis. Lassen Sie die anderen Eingaben auf Basiswerten.
3. Tragen Sie die Ergebnisse in eine Tabelle mit einer Zeile pro Eingabe ein.
4. Sortieren Sie die Zeilen so, dass die Eingabe mit der größten Spanne oben steht.
5. Fügen Sie ein Balkendiagramm der niedrigen und hohen Ergebnisse ein.

Für Northgate Cycles, wobei jede Eingabe um 10 % nach oben und unten verschoben wurde:

Eingabe	Gewinn bei −10 %	Gewinn bei +10 %	Spanne
Preis	$92.000	$500.000	$408.000
Variable Kosten	$418.400	$173.600	$244.800
Verkaufte Einheiten	$214.400	$377.600	$163.200
Fixkosten	$348.000	$244.000	$104.000

Der Preis ist mit großem Abstand am wichtigsten. Ein Preisrückgang um 10 % senkt den Gewinn von $296.000 auf $92.000. Fixkosten sind am wenigsten wichtig. Das sagt dem Eigentümer, wo er Zeit investieren sollte, um Annahmen zu prüfen.

Ein Balkendiagramm in Excel zeichnet Balken von links nach rechts. Um die Tornado-Form zu erhalten, stellen Sie die Abweichung jeder Eingabe vom Basisgewinn dar anstatt der Rohwerte. Dann zeigen die Balken für den niedrigen Fall nach links und die Balken für den hohen Fall nach rechts.

Wie man eine Sensitivitätsanalyse mit AI durchführt

Das Erstellen von Datentabellen, Szenarien und einem Tornado-Diagramm für ein großes Modell kostet Zeit. Ein AI-Tool kann dieselben Tests direkt aus Ihrer Arbeitsmappe heraus durchführen.

Hier ist die Methode in drei Schritten unter Verwendung von Powerdrill Bloom.

Schritt 1: Ihr Modell hochladen

Beginnen Sie mit der Tabellenkalkulation, die Ihr Modell enthält. Der AI Sensitivity Analysis Generator fordert Sie auf, „DCF, Business Case, Betriebsplan oder ein beliebiges Tabellenmodell“ hochzuladen.

Stellen Sie sicher, dass die Eingaben und die Ergebniszelle klar beschriftet sind. Das Tool muss wissen, welche Zahlen Annahmen sind.

Schritt 2: Die Sensitivitätsansicht in natürlicher Sprache wählen

Beschreiben Sie den Test, den Sie wünschen. Zum Beispiel: „Zeige ein Tornado-Diagramm für den Gewinn, wobei jede Eingabe um 10 % nach oben und unten verschoben wird. Füge eine Zwei-Wege-Tabelle für Preis und verkaufte Einheiten hinzu.“

Die Tool-Seite listet vier Ansichten auf: „Tornado, Zwei-Wege, Monte Carlo oder Break-even.“ Sie beschreibt die Tornado-Ausgabe als ein Diagramm, das „die Eingaben ordnet, die das Ergebnis am stärksten beeinflussen“. Sie bietet auch eine „Sensitivitätstabelle über die zwei wichtigsten Annahmen“ an.

Schritt 3: Ergebnisse prüfen und die Analyse exportieren

Vergleichen Sie ein oder zwei Ergebnisse mit Ihrer eigenen Datentabelle, bevor Sie sie teilen. Der Basisfall sollte exakt mit Ihrem Modell übereinstimmen.

Dann exportieren Sie. Die Tool-Seite listet eine „Excel-Arbeitsmappe, ein gebrandetes PDF oder einen Canvas-Link“ auf. Für eine Präsentation verwandelt das Excel to PPT Tool dieselbe Datei in Folien. Die AI Financial Modeling Seite deckt die Erstellung des Modells selbst ab.

Wie man die Ergebnisse liest und präsentiert

Eine Sensitivitätsanalyse ist nur nützlich, wenn Menschen danach handeln. Diese Gewohnheiten helfen dabei.

Mit dem Ranking beginnen. Eröffnen Sie mit dem Tornado-Diagramm. Es zeigt in einem Bild, welche Annahmen das Ergebnis steuern.

Spannen in Entscheidungen übersetzen. „Der Gewinn fällt auf $92.000, wenn der Preis um 10 % sinkt“ ist eine Tatsache. „Wir sollten den Preis testen, bevor wir den Mietvertrag unterschreiben“ ist eine Entscheidung.

Den Break-even-Punkt zeigen. Kombinieren Sie die Analyse mit dem Wert, bei dem das Ergebnis Null erreicht. Für Northgate Cycles liegt der Break-even bei etwa 1.530 Einheiten bei Basispreis und -kosten. Unser Leitfaden zur Break-even-Analyse in Excel deckt diese Berechnung ab.

Realistische Spannen verwenden. Eine 10%ige Bewegung passt zu stabilen Eingaben. Das Volumen eines neuen Produkts benötigt möglicherweise eine viel größere Spanne.

Angeben, was konstant gehalten wurde. Jeder Einzeltest hält die anderen Eingaben auf Basiswerten. Sagen Sie das auf der Folie oder in einer Fußnote. Die Leser wissen dann, dass eine Preisänderung von 10 % das gleiche Volumen, die gleichen Kosten und die gleichen Fixausgaben voraussetzt. Diese eine Zeile verhindert die meisten Rückfragen zur Methode.

Nach Vorliegen der Ergebnisse erneut prüfen. Sobald tatsächliche Zahlen vorliegen, vergleichen Sie diese mit Ihren Spannen. Ein Soll-Ist-Vergleich ist eine natürliche Fortsetzung.

Häufige Fehler bei der Sensitivitätsanalyse

Diese Fehler sind häufig und leicht zu vermeiden.

Fest codierte Zahlen in Formeln. Wenn eine Formel 850 anstelle von B2 enthält, kann die Datentabelle dies nicht ändern. Halten Sie jede Eingabe in einer Zelle.

Zu viele Eingaben gleichzeitig testen. Eine Datentabelle bewältigt ein oder zwei. Wechseln Sie für mehr zum Szenario-Manager oder zu einem Tornado-Diagramm.

Dieselbe Spanne für jede Eingabe verwenden. Preis und Volumen weisen selten die gleiche Unsicherheit auf. Wählen Sie Spannen, die das widerspiegeln, was Sie tatsächlich wissen.

Verknüpfungen zwischen Eingaben ignorieren. Ein niedrigerer Preis kann das Volumen erhöhen. Ein Einzeltest übersieht das, fügen Sie also ein Szenario hinzu, das beides bewegt.

Veraltete Szenario-Zusammenfassungen. Szenarioberichte aktualisieren sich nicht von selbst. Erstellen Sie diese nach jeder Änderung neu.

Den Basisfall vergessen. Jede Tabelle sollte das Basisergebnis zeigen, damit die Leser die Größe jeder Änderung sehen können.

Wenn Sie bereit sind, Ihr nächstes Modell einem Stresstest zu unterziehen, probieren Sie Powerdrill Bloom mit der Arbeitsmappe aus, die Sie für die Planung verwenden.

Häufig gestellte Fragen

Was ist eine Sensitivitätsanalyse in Excel?

Es ist eine Methode, um zu testen, wie sich ein Ergebnis ändert, wenn sich seine Eingaben ändern. In Excel verwenden Sie Was-wäre-wenn-Analyse-Tools, hauptsächlich Datentabellen und den Szenario-Manager, um neue Eingabewerte einzusetzen und die Ergebnisse aufzuzeichnen.

Wie führe ich eine Sensitivitätsanalyse mit einer Variablen in Excel durch?

Listen Sie die Eingabewerte in einer Spalte auf. Setzen Sie eine Formel, die sich auf das Ergebnis bezieht, eine Zeile oberhalb und eine Spalte rechts davon. Markieren Sie den Bereich, wählen Sie Was-wäre-wenn-Analyse > Datentabelle und geben Sie die Eingabezelle als Spalteneingabezelle ein.

Was ist der Unterschied zwischen einer Datentabelle und dem Szenario-Manager?

Eine Datentabelle testet eine oder zwei Variablen über viele Werte hinweg und zeigt alle Ergebnisse in einem Raster an. Der Szenario-Manager speichert vollständige Sätze von Eingaben mit jeweils bis zu 32 veränderbaren Werten und tauscht diese einzeln aus.

Wie erstelle ich ein Tornado-Diagramm in Excel?

Verschieben Sie jede Eingabe nacheinander um denselben Betrag nach oben und unten und protokollieren Sie das Ergebnis. Sortieren Sie die Eingaben nach der Größe der Spanne und stellen Sie dann die Abweichung jeder Eingabe vom Basisergebnis als horizontale Balken dar.

Kann AI eine Sensitivitätsanalyse durchführen?

Ja. Ein AI-Tool kann Ihr Tabellenmodell lesen, Tornado- und Zwei-Wege-Tests durchführen und die Ergebnisse als Arbeitsmappe oder Bericht exportieren. Sie müssen dennoch den Basisfall prüfen und realistische Spannen wählen.

Quellen: Microsoft Support, Einführung in die Was-wäre-wenn-Analyse · Microsoft Support, Berechnen mehrerer Ergebnisse mithilfe einer Datentabelle · Microsoft Support, Wechseln zwischen verschiedenen Sätzen von Werten mithilfe von Szenarien.