Praktischer Leitfaden zur Sensitivitätsanalyse in Excel: Von manueller zu intelligenter Evolution

Bei der Modellierung in Tabellenkalkulationen fragen wir uns oft, wie sich die Änderung des Werts einer Variablen auf das Endergebnis auswirken würde. Dieser Prozess kann durch die Durchführung einer Sensitivitätsanalyse in Excel erfolgen.

Wenn Sie ein Datenexperte sind, der im Bereich Finanzmodellierung oder im Entscheidungsfindungsprozess arbeitet, dann ist es entscheidend zu wissen, wie man eine Sensitivitätsanalyse durchführt.

In diesem Artikel tauchen wir in die Sensitivitätsanalyse ein. Wir behandeln die Grundlagen dieser nützlichen "Was-wäre-wenn"-Technik, wie man grundlegende und fortgeschrittene Sensitivitätsanalysen in Excel durchführt und die Ergebnisse interpretiert, und schließlich diskutieren wir die Rolle von KI und maschinellem Lernen bei der Ermöglichung leistungsfähigerer Sensitivitätsanalysen, zusammen mit Best Practices und häufigen Fallstricken.

Was ist eine Sensitivitätsanalyse in Excel?

Der gängigste Ansatz für die Sensitivitätsanalyse besteht darin, zu untersuchen, wie sich eine Änderung einer Variablen auf das Endergebnis auswirkt, während alle anderen Faktoren konstant gehalten werden. Dieser Prozess wird für alle betrachteten Variablen wiederholt. Er hilft, die einflussreichsten Treiber in einem Modell zu identifizieren, sodass Unternehmen sich auf die Schlüsselfaktoren konzentrieren können, die ihre Leistung beeinflussen.

Die Sensitivitätsanalyse hilft Unternehmen, kritische Risiken und Chancen zu priorisieren, indem sie identifiziert, welche Faktoren den größten Einfluss auf die Leistung haben. Aufgrund ihrer Art ist die Sensitivitätsanalyse in Finanzen, Wirtschaft und Projektmanagement üblich, zum Beispiel um zu berechnen, wie sich Zinssätze im Laufe der Zeit ändern könnten, oder um verschiedene Wachstumstrends zu verstehen.

Darüber hinaus wird die Sensitivitätsanalyse oft in Verbindung mit der Szenarioanalyse verwendet, die typischerweise Kombinationen von Variablen gleichzeitig analysiert. Beide Was-wäre-wenn-Analysen untersuchen, wie die abhängige Variable auf bestimmte Eingaben reagiert. Die Szenarioanalyse gilt jedoch für ein bestimmtes "Szenario", während die Sensitivitätsanalyse offener ist, da sie eine Reihe von Eingaben und Werten bietet.

Das Ziel der Sensitivitätsanalyse ist es zu verstehen, wie die abhängige Variable auf eine Reihe von Eingabewerten (sogenannte unabhängige Variablen) reagiert.

Durch die Integration dieser Technik in den Entscheidungsfindungsprozess können Organisationen ihre Strategien proaktiv anpassen und ihre Widerstandsfähigkeit gegenüber Unsicherheit erhöhen.

Oft wird die Sensitivitätsanalyse als Sensitivitätsanalysetabelle mit bedingter Formatierung dargestellt, die die höchsten bis niedrigsten Werte hervorhebt.

Hier ist ein Beispiel für die Preissensitivität von Angebot und Nachfrage. Angebot und Nachfrage sind die unabhängigen Variablen auf den Achsen, und der Preis ist die abhängige Variable, die die Werte innerhalb der Tabelle einnimmt.

In den folgenden Abschnitten werden wir ähnliche Excel-Sensitivitätsanalysetabellen verwenden.

Erste Schritte mit der Sensitivitätsanalyse in Excel

Schauen wir uns an, wie man eine Sensitivitätsanalyse in Excel durchführt, und folgen dabei jedem Schritt des Prozesses.

Traditionelle Tools und Funktionen in Excel

Excel ist ein praktisches Werkzeug zum Erstellen mathematischer Modelle, einschließlich Was-wäre-wenn-Analysen. Was-wäre-wenn-Analysen drehen sich im Wesentlichen darum, Formeln zu erstellen, um Fragen wie die Sensitivitätsanalyse zu untersuchen.

Die meisten aktuellen Versionen von Excel verfügen über einen Daten-Tab, der zahlreiche 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. Sie enthält drei Werkzeuge. Die Schaltfläche Datentabelle ist speziell für die Erstellung von Sensitivitätsanalysetabellen konzipiert.

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

Wir werden auch Solver verwenden, ein Add-In, das über den Entwickler-Tab aktiviert werden kann.

Solver wird in der Was-wäre-wenn-Analyse verwendet, um einen optimalen (maximalen oder minimalen) Wert für eine Formel in einer Zelle (die Zielzelle genannt wird) zu finden, abhängig von Einschränkungen oder Grenzen für die Werte anderer Formelzellen im Arbeitsblatt.

Bevor Sie Solver aktivieren können, müssen Sie auf den Entwickler-Tab zugreifen, der standardmäßig nicht angezeigt wird. Um diesen Tab anzuzeigen:

• Gehen Sie zu Datei > Optionen
• Wählen Sie im linken Bereich Menüband anpassen
• Aktivieren Sie unter "Hauptregisterkarten" in der rechten Spalte das Kontrollkästchen für Entwickler

Durchführung einer Einzelvariablen-Sensitivitätsanalyse

Nachdem unsere Werkzeuge bereit sind, beginnen wir mit der Erstellung einer einfachen Einzelvariablen-Sensitivitätsanalyse in Excel.

Traditionelle Methode: Einrichten Ihres Modells

Der erste Schritt bei der Durchführung einer Sensitivitätsanalyse in Excel besteht darin, die Eingaben und Ausgaben zu identifizieren, auf denen das Modell basiert.

In diesem Tutorial verwenden wir die Sensitivitätsanalyse, um zu untersuchen, wie sich der Nettogewinn eines Gitarrenverkaufsunternehmens ändert, wenn wir die Werte einiger Eingabevariablen ändern, wie z.B. die Anzahl der verkauften Gitarren, den Preis pro Gitarre oder die Produktionskosten.

Unten sehen Sie die Tabelle mit den Eingabe- und Ausgabevariablen.

Bei der Verwendung von Datentabellen für die Sensitivitätsanalyse ist es wichtig, die Ausgabezelle mit der Eingabevariablen zu verknüpfen, damit Excel die Beziehung zwischen den Modellvariablen versteht. Dieser Prozess erfolgt über Formeln. In unserem Fall:

• Der Umsatz wird berechnet, indem der Stückpreis mit der Anzahl der verkauften Einheiten multipliziert wird (=B2*B4)
• Die Kosten der verkauften Waren werden berechnet, indem die Stückproduktionskosten mit der Anzahl der verkauften Einheiten multipliziert werden (=B3*B4)
• Der Gewinn wird berechnet, indem die Kosten der verkauften Waren vom Umsatz abgezogen werden (=B7-B8)

Traditionelle Methode: Erstellen einer Einweg-Datentabelle

Sie fragen sich vielleicht, wie sich der Gewinn ändern würde, wenn wir den Stückpreis erhöhen oder senken, während wir die Produktionskosten und die Anzahl der verkauften Einheiten konstant halten. Wenn wir analysieren, wie sich eine Ausgabevariable ändert, während wir die Werte einer Eingabevariablen ändern, benötigen wir eine sogenannte Einweg-Datentabelle.

Die folgende GIF-Animation zeigt, wie man eine Einweg-Datentabelle in Excel erstellt. Wie Sie sehen, berechnet die Tabelle automatisch den Gewinn für jede verkaufte Einheit neu. Natürlich ist der Gewinn beim Verkauf von 250 Einheiten derselbe wie in den ursprünglichen Parametern (d.h. 50.000 €).

Intelligentere Methode: Einzelvariablen-Analyse mit Excelmatic

Obwohl die Datentabellen-Funktion leistungsstark ist, erfordert der Einrichtungsprozess präzise Zellbezüge und mehrere Schritte. Für komplexere oder dynamischere Modelle kann dies mühsam und fehleranfällig werden.

Excelmatic bietet eine intuitivere Alternative. Als Excel-KI-Agent ermöglicht es Ihnen, Analysen mit natürlicher Sprache durchzuführen.

Um dasselbe Ziel zu erreichen, tun Sie einfach Folgendes:

1. Laden Sie Ihre Excel-Datei mit Ihrem Modell in Excelmatic hoch.
2. Geben Sie Ihre Anforderung in einfacher Sprache an: "Erstellen Sie eine Sensitivitätsanalysetabelle, die zeigt, wie sich der Gewinn (Zelle B9) ändert, wenn sich die verkauften Einheiten (Zelle B4) von 150 auf 500 in Schritten von 50 ändern."

Excelmatic versteht sofort die Struktur Ihres Modells und generiert automatisch genau dieselben Ergebnisse wie eine manuell erstellte Einweg-Datentabelle, ohne dass Sie die Tabelle manuell einrichten, Zellen verknüpfen oder Menüs navigieren müssen. Der Vorteil dieses Ansatzes liegt in seiner Geschwindigkeit und Einfachheit, sodass Sie sich auf die Analyse der Ergebnisse konzentrieren können, anstatt auf die Bedienung des Werkzeugs.

Vielleicht möchten Sie auch untersuchen, wie sich verschiedene Ausgabevariablen mit den Verkaufseinheiten ändern. Bei der traditionellen Methode müssten Sie die Einrichtung der Datentabelle anpassen.

Mit Excelmatic ändern Sie einfach Ihre Anfrage leicht: "Zeigen Sie, wie sich sowohl der Gewinn (B9) als auch die Kosten der verkauften Waren (B8) ändern, wenn sich die verkauften Einheiten (B4) von 150 auf 500 ändern." Die KI erledigt alle Details für Sie.

Durchführung einer Zwei-Variablen-Sensitivitätsanalyse

Traditionelle Methode: Erstellen einer Zweiweg-Datentabelle

Anstatt zu analysieren, wie eine Ausgabe von einer Eingabe beeinflusst wird, können Sie auch eine Zwei-Variablen-Tabelle erstellen, um die Auswirkung von zwei Eingabevariablen zu analysieren. Nehmen wir an, Sie möchten wissen, wie sich der Gewinn ändern würde, wenn wir sowohl die Anzahl der verkauften Gitarren als auch den Preis pro Gitarre ändern.

Zuerst müssen Sie eine zweidimensionale Tabelle erstellen, in der die Zeilen den Bereich für die Stückpreise und die Spalten den Bereich für die Verkaufseinheiten darstellen. Wählen Sie dann in der oberen linken Ecke der Tabelle die Variable aus, die Sie untersuchen möchten. Wählen Sie schließlich in der Tabelle der Eingabevariablen die Zellbezüge für die unabhängigen Variablen (d.h. den Stückpreis und die Anzahl der verkauften Einheiten).

Wie Sie sehen, können Sie, sobald Sie die Tabelle erstellt haben, die Ausgabevariable in der oberen linken Ecke leicht in eine andere Variable ändern, und Excel berechnet alle Werte neu.

Intelligentere Methode: Zwei-Variablen-Analyse mit Excelmatic

Das Einrichten einer Zweiweg-Datentabelle ist komplexer als das einer Einweg-Tabelle und erfordert die korrekte Referenzierung von Eingaben und Ausgaben in Zeilen, Spalten und der Eckzelle.

Mit Excelmatic wird dieser Prozess ebenfalls auf eine einzige einfache Anweisung vereinfacht. Nachdem Sie Ihre Datei hochgeladen haben, können Sie direkt fragen:

"Generieren Sie eine Zweiweg-Sensitivitätstabelle, die zeigt, wie sich der Gewinn basierend auf den verkauften Einheiten (von 150 bis 500) und dem Preis pro Einheit (von 150 € bis 400 €) ändert."

Excelmatic baut die gesamte zweidimensionale Tabelle für Sie auf, füllt alle berechneten Werte aus und eliminiert so potenzielle Referenzfehler aus der manuellen Einrichtung. Wenn Sie eine andere Ausgabe wie "Umsatz" analysieren möchten, stellen Sie einfach eine neue Frage: "Zeigen Sie jetzt dieselbe Tabelle, aber für den Umsatz." Dieser konversationsbasierte Arbeitsablauf macht komplexe Analysen mühelos.

Interpretation der Excel-Sensitivitätsanalyseergebnisse

Nachdem Sie die Datentabelle haben, besteht der letzte Schritt darin, die Ergebnisse zu interpretieren, um Erkenntnisse darüber zu gewinnen, wie sich Eingabeänderungen auf die Ausgabe auswirken.

Analyse der Datentabellenausgabe

Basierend auf dieser Sensitivitätsanalyse können wir fundierte Entscheidungen über den Gewinn treffen. Hier sind einige Erkenntnisse:

• Wenn wir den Gitarrenpreis auf 350 € erhöhen, müssen wir nur 200 Gitarren verkaufen, um den aktuellen Gewinn von 50.000 € zu erzielen.
• Wenn wir den Gitarrenpreis auf 200 € senken, müssen wir 500 Einheiten verkaufen, um den aktuellen Gewinn zu erreichen.
• Wenn wir die Anzahl der verkauften Gitarren auf 150 reduzieren, können wir den aktuellen Gewinn nicht erreichen, es sei denn, wir erhöhen den Gitarrenpreis auf über 400 €.

Die Interpretation der Analyse hängt von den spezifischen Bedürfnissen Ihres Unternehmens ab. Fragen zu stellen, wie z.B. wie viele Gitarren Sie produzieren können, welches Mindestgewinnniveau Sie für ein gesundes Unternehmen aufrechterhalten müssen und was Sie tun können, um die Gitarrenproduktionskosten zu senken, ist entscheidend, um aussagekräftige Erkenntnisse aus der Sensitivitätsanalyse zu gewinnen.

Traditionelle Methode: Optimierung und Sensitivitätsanalyse mit Solver

Wenn Sie eine Sensitivitätsanalyse durchführen, erhalten Sie eine Reihe potenzieller Ausgaben basierend auf Änderungen an den Eingabevariablen Ihres Modells. Möglicherweise möchten Sie jedoch mehr über die inneren Abläufe Ihres Modells erfahren.

Glücklicherweise verfügt Excel über ein leistungsstarkes Werkzeug namens Solver, das Ihnen bei dieser Aufgabe helfen kann. Wie bereits erwähnt, wird Solver in der Was-wäre-wenn-Analyse verwendet, um einen optimalen (maximalen oder minimalen) Wert für eine Formel zu finden, abhängig von Einschränkungen oder Grenzen für die Werte anderer Formelzellen im Arbeitsblatt.

Solver ist mehr als nur eine verbesserte Version von Zielwertsuche. Erstens ermöglicht Solver die Berechnung optimaler Ergebnisse basierend auf mehreren Variablen und ermöglicht es Ihnen, Einschränkungen in Ihr Modell aufzunehmen. Noch wichtiger ist, dass Solver auch über eine optionale Sensitivitätsfunktion verfügt, mit der Sie sehen können, wie sich die optimale Lösung ändert, wenn Sie die Koeffizienten Ihres Modells ändern.

Um die Leistungsfähigkeit von Solver zu veranschaulichen, kehren wir zu unserem Gitarrenunternehmen zurück. Stellen Sie sich vor, das Unternehmen produziert zwei Gitarrenmodelle (Modell A und Modell B), die jeweils eine bestimmte Menge Mahagoni- und Zedernholz benötigen. Jedes Modell hat einen anderen Preis.

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

In der folgenden GIF-Animation zeigen wir, wie Sie Solver verwenden, um die optimalen Werte der Einheiten zu finden, um den Umsatz zu maximieren, während Sie Holzbeschränkungen verwenden und sicherstellen, dass die Einheiten ganz