Sensitivitätsanalyse in Excel Tutorial: Überblick, Arten und bewährte Praktiken

Wenn wir ein Modell in einer Tabellenkalkulation entwerfen, möchten wir vielleicht wissen, wie sich ein Ergebnis verändert, wenn wir den Wert einer der beteiligten Variablen ändern. Dieser Prozess kann in Excel durchgeführt werden, indem eine Sensitivitätsanalyse durchgeführt wird

Das Wissen um die Durchführung einer Sensitivitätsanalyse ist entscheidend, wenn du als Datenexperte in der Finanzmodellierung oder bei Entscheidungsprozessen arbeitest. 

In diesem Artikel werden wir uns eingehend mit der Sensitivitätsanalyse befassen. Wir werden die Grundlagen dieser nützlichen Technik aus der Familie der "Was-wäre-wenn"-Analysen behandeln und zeigen, wie man die Ergebnisse von grundlegenden und erweiterten Sensitivitätsanalysen in Excel durchführt und interpretiert. Schließlich werden wir die Rolle von KI und maschinellem Lernen bei der Entwicklung leistungsfähigerer Sensitivitätsanalysen sowie bewährte Verfahren und häufige Fallstricke diskutieren. 

Wenn du gerne praktische Erfahrungen sammeln möchtest, schau dir unseren Kurs Finanzanalyse in Excel an, in dem die Was-wäre-wenn-Analyse im Detail behandelt wird. 

Was ist eine Sensitivitätsanalyse in Excel?

Der gängigste Ansatz bei der Sensitivitätsanalyse besteht darin, zu untersuchen, wie sich Änderungen bei jeweils einer Variable auf das Endergebnis auswirken, während alle anderen Faktoren konstant bleiben. Dies wird für alle betrachteten Variablen wiederholt. Es hilft dabei, die einflussreichsten Treiber in einem Modell zu identifizieren, damit sich Unternehmen auf die wichtigsten Faktoren konzentrieren können, die ihre Leistung beeinflussen. 

Die Sensitivitätsanalyse hilft Unternehmen, die wichtigsten Risiken und Chancen zu priorisieren, indem sie herausfindet, welche Faktoren den größten Einfluss auf die Leistung haben. Sensitivitätsanalysen werden häufig in den Bereichen Finanzen, Wirtschaft und Projektmanagement eingesetzt, um z.B. zu berechnen, wie sich die Zinssätze im Laufe der Zeit verändern können oder um verschiedene Wachstumstrends zu verstehen. 

Außerdem wird die Sensitivitätsanalyse oft mit der Szenarioanalyse kombiniert , bei der in der Regel die Kombination von Variablen gleichzeitig analysiert wird. Bei diesen beiden Was-wäre-wenn-Analysen wird untersucht, wie eine abhängige Variable auf bestimmte Eingaben reagieren wird. Die Szenarioanalyse ist jedoch spezifisch für ein bestimmtes "Szenario", während die Sensitivitätsanalyse offener ist, weil sie eine Reihe von Inputs und Werten angibt. 

Das Ziel der Sensitivitätsanalyse ist es, zu verstehen, wie die abhängige Variable auf eine Reihe von Eingangswerten, den sogenannten unabhängigen Variablen, reagiert.

Indem sie diese Technik in die Entscheidungsfindung einbeziehen, können Unternehmen ihre Strategien proaktiv anpassen und ihre Widerstandsfähigkeit gegenüber Unsicherheiten erhöhen. 

Für eine ausführlichere Erklärung der Theorie hinter der Sensitivitätsanalyse empfehlen wir dir unser Advanced Probability: Unsicherheit im Datenkurs.

Oft wird die Sensitivitätsanalyse in einer Tabelle mit bedingter Formatierung dargestellt, die die Werte vom höchsten zum niedrigsten Wert hervorhebt. 

Im Folgenden findest du ein Beispiel für die Preisempfindlichkeit von Angebot und Nachfrage. Angebot und Nachfrage sind die unabhängigen Variablen, die auf den Achsen beschriftet sind, und der Preis ist die abhängige Variable, die die Werte in der Tabelle enthält.

In den nächsten Abschnitten werden wir mit ähnlichen Excel-Tabellen arbeiten.

Tabelle zur Sensitivität. Quelle: DataCamp

Erste Schritte mit der Sensitivitätsanalyse in Excel

Wir zeigen dir, wie du eine Sensitivitätsanalyse in Excel durchführst, indem wir jeden Schritt des Prozesses verfolgen. 

Werkzeuge und Funktionen in Excel

Excel ist ein praktisches Werkzeug zur Erstellung mathematischer Modelle, einschließlich Was-wäre-wenn-Analysen. Bei der Was-wäre-wenn-Analyse geht es im Wesentlichen darum, Formeln zu erstellen, mit denen Fragen untersucht werden können, wie bei einer Sensitivitätsanalyse.

Die meisten neueren Versionen von Excel verfügen über eine RegisterkarteDaten, die unzählige Werkzeuge und Möglichkeiten für die Arbeit mit Daten bietet. Dazu gehört die Schaltfläche Was-wäre-wenn-Analyse im Menüband. Es umfasst drei Werkzeuge. DieSchaltfläche Datentabelle wurde speziell für die Erstellung von Sensitivitätstabellen entwickelt.

Um eine Szenarioanalyse durchzuführen, klicken Sie einfach auf den Szenario-Manager Schaltfläche.

Wir werden auch den Solververwenden, ein Add-In, das du in der Entwickler Registerkarte.

Solver wird in Was-wäre-wenn-Analysen verwendet, um einen optimalen (maximalen oder minimalen) Wert für eine Formel in einer Zelle - der so genannten Zielzelle - zu finden, der den Einschränkungen oder Grenzen der Werte anderer Formelzellen auf einem Arbeitsblatt unterliegt.

Bevor du Solver aktivierst, musst du die Registerkarte Entwickler aufrufen, die standardmäßig nicht angezeigt wird. Um diese Registerkarte anzuzeigen:

• Gehe zu Datei > Optionen
• Wähle Farbband anpassen aus dem linken Bedienfeld
• In der rechten Spalte unter "Haupt-Registerkarten" das Kästchen für Entwickler

Durchführen einer Sensitivitätsanalyse für eine einzelne Variable

Da wir nun unsere Werkzeuge bereit haben, erstellen wir zunächst eine einfache Sensitivitätsanalyse mit einer einzigen Variable in Excel.

Dein Modell einrichten

Der erste Schritt bei der Durchführung einer Sensitivitätsanalyse in Excel besteht darin, die Eingaben und Ausgaben zu bestimmen, auf denen dein Modell basieren soll. 

In diesem Tutorium werden wir mit Hilfe der Sensitivitätsanalyse untersuchen, wie sich der Nettogewinn eines Unternehmens, das Gitarren verkauft, verändert, wenn wir den Wert bestimmter Inputvariablen wie die Anzahl der verkauften Gitarren, den Preis der Gitarre oder die Produktionskosten ändern.

Unten findest du die Tabellen mit den Eingangs- und Ausgangsvariablen. 

Wenn du Datentabellen für Sensitivitätsanalysen verwendest, ist es wichtig, die Ausgabezellen mit den Eingabevariablen zu verknüpfen, damit Excel die Beziehung zwischen den Modellvariablen verstehen kann. Dieser Prozess wird über Formeln durchgeführt. In unserem Beispiel:

• Der Umsatz wird berechnet, indem der Preis pro Einheit mit den verkauften Einheiten multipliziert wird (=B2*B4)
• Kosten der Verkäufe werden berechnet, indem die Produktionskosten pro Einheit mit den verkauften Einheiten multipliziert werden (=B3*B4)
• Der Gewinn wird berechnet, indem die Umsatzerlöse von den Umsatzkosten abgezogen werden (=B7-B8).

Erstellen einer Tabelle mit einseitigen Daten

Du möchtest vielleicht wissen, wie sich der Gewinn verändert, wenn wir den Preis pro Einheit erhöhen oder senken, während die Produktionskosten und die Anzahl der verkauften Einheiten unverändert bleiben. Wenn wir analysieren, wie sich eine Ausgangsvariable verändert, wenn wir den Wert einer Eingangsvariablen ändern, brauchen wir eine so genannte Einweg-Datentabelle.

Das folgende GIF zeigt, wie du eine einseitige Tabelle in Excel erstellst. Wie du siehst, wird in der Tabelle der Gewinn für jede verkaufte Einheit automatisch neu berechnet. Natürlich ist der Gewinn bei 250 verkauften Einheiten derselbe wie bei den ursprünglichen Parametern (d.h. 50.000 €).

Du könntest auch untersuchen, wie sich die verschiedenen Outputvariablen verändern, wenn wir die Absatzmengen ändern. Auch das ist ziemlich einfach, wie unten gezeigt. In der folgenden GIF berechnen wir, wie sich der Absatz auf den Gewinn und die Umsatzkosten auswirkt.

Durchführen von Sensitivitätsanalysen mit zwei Variablen

Anstatt zu analysieren, wie eine Ausgabe von einer Eingabe beeinflusst wird, kannst du auch eine Tabelle mit zwei Variablen erstellen, um die Auswirkungen von zwei Eingabevariablen zu analysieren. Angenommen, du willst wissen, wie sich der Gewinn verändert, wenn wir die Gitarrenverkäufe und den Preis pro Gitarre ändern. 

Zuerst musst du eine zweidimensionale Tabelle erstellen, die in den Zeilen die Preisbereiche pro Einheit und in den Spalten den Absatz pro Einheit enthält. Wähle dann in der oberen linken Ecke der Tabelle die Variable aus, die du untersuchen möchtest. Wähle schließlich den Zellbezug der unabhängigen Variablen in der Tabelle mit den Eingangsvariablen (d. h. Preis pro Einheit und verkaufte Einheiten).

Wie du siehst, kannst du nach dem Erstellen der Tabelle die Ausgabevariable in der oberen linken Ecke einfach in eine andere Variable umwandeln, und Excel berechnet alle Werte neu

Interpretation der Ergebnisse der Excel-Sensitivitätsanalyse

Jetzt, wo du deine Tabellen hast, musst du die Ergebnisse interpretieren, um herauszufinden, wie sich Änderungen der Eingaben auf die Ausgaben auswirken.

Analysieren der Ausgaben von Tabellen

Mit dieser Sensitivitätsanalyse können wir eine fundierte Entscheidung über den Gewinn treffen. Hier sind einige Einblicke:

• Wenn wir die Preise der Gitarren auf 350 € erhöhen, könnten wir mit dem Verkauf von nur 200 Gitarren den aktuellen Gewinn von 50.000 € erreichen.
• Wenn wir den Preis der Gitarren auf 200 € senken, müssten wir 500 Stück verkaufen, um den aktuellen Gewinn zu erzielen.
• Wenn wir die Anzahl der verkauften Gitarren auf 150 reduzieren, werden wir den aktuellen Gewinn nicht erreichen, es sei denn, wir erhöhen den Preis der Gitarren auf über 400€.

Die Interpretation der Analyse hängt von den besonderen Bedürfnissen deines Unternehmens ab. Fragen wie die, wie viele Gitarren du produzieren kannst, wie hoch der Gewinn mindestens sein muss, damit dein Unternehmen gesund bleibt, und was du in der Hand hast, um die Produktionskosten der Gitarre zu senken, sind entscheidend, um aussagekräftige Erkenntnisse aus der Sensitivitätsanalyse zu gewinnen.

Solver für die Sensitivitätsanalyse verwenden

Wenn du eine Sensitivitätsanalyse durchführst, gibst du eine Reihe von möglichen Ergebnissen zurück, die auf Änderungen der Eingangsvariablen eines Modells basieren. Vielleicht möchtest du aber noch mehr Details über das Innenleben deines Modells wissen.

Glücklicherweise verfügt Excel über ein leistungsstarkes Tool namens Solver, das dir bei dieser Aufgabe helfen kann. Wie bereits erwähnt, wird Solver in Was-wäre-wenn-Analysen verwendet, um einen optimalen (maximalen oder minimalen) Wert für eine Formel zu finden, der den Beschränkungen der Werte anderer Formelzellen auf einem Arbeitsblatt unterliegt. 

Das klingt vielleicht wie das Ziel von Goal Seek, einer anderen Art von WENN-Analyse, die sich darauf konzentriert, eine einzelne Variable anzupassen, um ein gewünschtes Ergebnis zu erreichen. Solver ist jedoch viel mehr als Goal Seek. 

Erstens kannst du mit Solver ein optimales Ergebnis auf der Grundlage von mehr als einer Variable berechnen und Randbedingungen in die Modelle aufnehmen. 

Außerdem verfügt Solver über eine optionale Sensitivitätsfunktion, mit der du sehen kannst, wie sich die optimale Lösung verändert, wenn du die Koeffizienten des Modells änderst. 

Um die Leistungsfähigkeit von Solver zu veranschaulichen, lass uns noch einmal zu unserer Gitarrenfirma zurückkehren. Stell dir vor, das Unternehmen stellt zwei Gitarrenmodelle her (Modell A und B), für die jeweils eine bestimmte Kombination aus Mahagoni und Zedernholz benötigt wird. Jedes Modell hat einen anderen Preis. 

Das Unternehmen möchte wissen, wie viele Einheiten jedes Modells es angesichts der aktuellen Verfügbarkeit von Mahagoni und Zedernholz produzieren muss, um seinen Gewinn zu maximieren. 

Es gibt drei Dinge, die Solver berechnen soll: Gewinn, Einheiten von Modell A und Einheiten von Modell B. Das Bild oben zeigt die Eingaben, Formeln und Einschränkungen, auf denen das Modell basieren wird. 

Im folgenden GIF zeigen wir dir, wie du mit Solver die optimalen Werte für die Einheiten findest, um den Nutzen zu maximieren, indem du die Einschränkungen der Holzverfügbarkeit berücksichtigst und sicherstellst, dass die Einheiten vollzählig sind. Es gibt verschiedene Strategien, die Solver zur Optimierung des Modells verwenden kann, sowohl für lineare als auch für nichtlineare Probleme. In unserem Fall werden wir Simple LP verwenden. 

Wenn wir alle Parameterwerte addieren, schätzt Solver, dass die 48 Einheiten von Modell A und 9 von Modell B die beste Kombination sind, um den Gewinn zu maximieren (17.591€). Wir können auf Sensitivität klicken, bevor wir das Solver-Feld schließen, und Excel wird ein neues Blatt mit einem Sensitivitätsbericht erstellen.

Die Sensitivität liefert uns wertvolle Erkenntnisse, um abzuschätzen, wie sich die Gewinne verändern würden, wenn wir die Eingangsvariablen ändern. Der Schattenpreis gibt an, um wie viel der Gewinn steigen würde, wenn du eine zusätzliche Einheit einer begrenzten Ressource hättest. In diesem Beispiel erhöht jeder zusätzliche Quadratmeter Mahagoni den Gewinn um 98 €, während Zedernholz den Gewinn um 103 € steigert, was das größere Renditepotenzial von Zedernholz verdeutlicht

Ein weiteres wichtiges Element des Sensitivitätsberichts sind die Spannen, innerhalb derer du die Preise der Modelle halten könntest, um die Ergebnisse des Modells unverändert zu lassen. 

Der Gewinn wird berechnet, indem die Einheiten eines jeden Modells mit seinem Preis multipliziert werden, der in der Tabelle Endwert und Zielkoeffizient Spalten angegeben ist. Die Spannen sind in den Spalten angegeben Erlaubte Erhöhung und Zulässige Verringerung.

Solver würde z.B. die gleiche Anzahl an zu produzierenden Einheiten finden, wenn du den Wert von Modell A um 1140€ erhöhst oder um 180€ senkst.

Visualisierung von Daten mit bedingter Formatierung

Wenn du Tabellen mit einseitigen und zweiseitigen Sensitivitäten erstellst, kann die Formatierung der Ergebnisse dazu beitragen, dass du relevante Informationen schnell erkennst und bessere Entscheidungen treffen kannst. Bedingte Formatierung ist der Schlüssel zur Erstellung wichtiger Schwellenwerte und überzeugender Heatmaps. 

Kehren wir zurück zu unserer Tabelle mit den zwei Wegen. Anstatt die Tabelle in weißen Zellen darzustellen, können wir mithilfe der bedingten Formatierung eine mehrfarbige Heatmap mit einer zweifarbigen Skala erstellen und einen benutzerdefinierten Schwellenwert festlegen, wie im GIF gezeigt.

Integration von KI und maschinellem Lernen

Wir wollen kurz darauf eingehen, wie KI und maschinelles Lernen helfen können, deine Sensitivitätsanalyse zu verbessern.

KI-gestützte Sensitivitätsanalyse

Bis jetzt haben wir gesehen, wie man eine Sensitivitätsanalyse manuell durchführt. Mit zunehmender Komplexität deiner Modelle kann die Durchführung und Interpretation der Ergebnisse der Sensitivitätsanalyse jedoch zeitaufwändig werden. 

Hier kommt die KI ins Spiel. Mit KI und maschinellem Lernen kannst du Maschinen die Berechnungen durchführen lassen, wenn bestimmte Bedingungen erfüllt sind, und die Ergebnisse in aussagekräftige Dashboards einfügen. 

Sobald du die Berechnungen kennst, die du durchführen willst, ist dein nächster Schritt die klare Definition einer KI-Pipeline, um den Prozess zu rationalisieren und zu automatisieren. 

Bist du neugierig, wie man das macht? Schau dir unser Tutorial zu maschinellem Lernen, Pipelines, Einsatz und MLOps an.

Monte-Carlo-Simulationen

Die Monte-Carlo-Simulation ist eine mathematische Technik, die verwendet wird, um die Wahrscheinlichkeit verschiedener Ergebnisse in einem Prozess zu modellieren, der aufgrund des Eingreifens von Zufallsvariablen nicht einfach vorhergesagt werden kann.

Das Problem wird zunächst durch eine Wahrscheinlichkeitsverteilung für jede Variable modelliert, die eine inhärente Unsicherheit aufweist. Aus diesen Wahrscheinlichkeitsverteilungen werden dann eine große Anzahl von Stichproben gezogen, die zur Berechnung der Ergebnisse verwendet werden. 

Dieser Prozess wird viele Male wiederholt, um eine Verteilung möglicher Ergebnisse zu erstellen, die statistisch ausgewertet werden können, um Vorhersagen darüber zu treffen, wie sich ein System verhalten wird. Mit Monte Carlo kann die Unsicherheit umfassender analysiert werden als mit herkömmlichen Tabellen.

Alles über diese leistungsstarke Technik erfährst du in unserem Leitfaden zur Monte-Carlo-Simulation in Excel.

Mit der Monte-Carlo-Simulation kannst du deine Sensitivitätsanalyse aufstocken, um herauszufinden, welche Variablen den größten Einfluss auf das Ergebnis haben. 

In unserem Gitarrenbeispiel können wir den Einstandspreis und die verkauften Einheiten konstant halten und den Stückpreis ändern, um die Veränderungen in den Schätzungen zu verstehen. Dann wiederholst du den gleichen Vorgang für die restlichen zwei Variablen, eine nach der anderen. Letztendlich hilft diese Technik zu verstehen, auf welche Variable man sich konzentrieren sollte, um die Genauigkeit zu verbessern.

Bewährte Praktiken und häufige Fallstricke

Obwohl Excel die Sensitivitätsanalyse extrem einfach und zugänglich macht, gibt es dennoch einige häufige Fallstricke, die du beachten solltest. Hier sind ein paar Dinge, die du beachten solltest.

Robuste Modelle entwerfen

Wie du in diesem Lernprogramm gesehen hast, muss Excel die Beziehungen zwischen den Eingabe- und Ausgabevariablen kennen, damit die Sensitivitätsanalyse funktioniert. Dazu musst du Formeln klug verwenden und klar definierte Zellbezüge und Bereiche angeben. 

Wenn du neue Werte zu den Tabellen hinzufügen möchtest, ist es eine gute Praxis, mögliche Probleme bei der Neuberechnung zu vermeiden, indem du Excel-Tabellen verwendest, da diese von Natur aus dynamisch sind, d. h., sie erweitern sich automatisch, wenn neue Zeilen oder Spalten hinzugefügt werden. 

Das hilft dir, die richtigen Formeln neu zu berechnen, damit die Informationen im richtigen Format bleiben. 

Fehler eindämmen und Genauigkeit sicherstellen

Auch wenn deine Sensitivitätsanalysen klar definiert sind, musst du deine Ergebnisse sorgfältig überprüfen. Manuelle Tests und Diagramme sind großartige Optionen, um deine Ergebnisse zu visualisieren und auf Ausreißer oder Anomalien in den Ergebnissen zu prüfen.  

Es ist jedoch wichtig, daran zu denken, dass die Sensitivitätsanalyse für deinen Anwendungsfall vielleicht einfach nicht funktioniert. Diese Analyse basiert auf einer Reihe einfacher Annahmen, wie der Unabhängigkeit der Inputvariablen, der Existenz linearer Beziehungen und der statischen Natur der Inputvariablen. 

Dein Modell kann jedoch komplexer sein und von externen Faktoren beeinflusst werden, die bei der Sensitivitätsanalyse naturgemäß übersehen werden. Daher ist es trotz seiner Vorteile wichtig, ein umfassendes Verständnis deines Modells über die Sensitivität hinaus zu haben, um eine korrekte Analyse zu gewährleisten.

Fazit

Herzlichen Glückwunsch, dass du es bis zum Ende des Tutorials geschafft hast. Die Sensitivitätsanalyse ist eine leistungsstarke Was-wäre-wenn-Analyse, die deinen Entscheidungsprozess entscheidend beeinflussen kann. Es gibt nur Was-wäre-wenn-Analysen, die deine Finanzmodelle ersetzen, wie Goal Seek oder Scenario Manager. Zum Glück ist das DataCamp da, um dir zu helfen. Schau dir unsere kuratierten Excel-Kurse und -Materialien an:

• Datenanalyse in Excel
• Finanzmodellierung in Excel 
• Excel-Grundlagen 
• Datenbearbeitung in Excel - Spickzettel 
• Datenanalyse in Google Sheets 
• Erweiterte Excel-Funktionen 
• So berechnest du Konfidenzintervalle in Excel 
• Excel-Formeln Spickzettel 
• So berechnest du den Variationskoeffizienten in Excel 
• Monte-Carlo-Simulation in Excel: Ein vollständiger Leitfaden