Tutorial: Sensitivitätsanalyse in Excel | Ein- und Zwei-Variablen-Datentabellen

Hier ist ein ausführliches Tutorial zur Durchführung einer Sensitivitätsanalyse in Excel mithilfe von Datentabellen (Datentabellen mit einer und zwei Variablen).

Die Sensitivitätsanalyse (auch „Was-wäre-wenn-Analyse“) ist ein unverzichtbares Werkzeug im Finanzwesen und Controlling. Sie hilft zu verstehen, wie sich Änderungen in den Eingabewerten (z. B. Zinssatz oder Verkaufsmenge) auf ein bestimmtes Ergebnis (z. B. den Gewinn oder eine Kreditrate) auswirken.

1. Vorbereitung: Das Basismodell

Bevor wir die Datentabellen erstellen, benötigen wir eine einfache Berechnungsgrundlage. In diesem Beispiel berechnen wir die monatliche Kreditrate.

Erstelle folgende Tabelle in Excel:

• B1: Kreditbetrag (z. B. 200.000 €)
• B2: Jährlicher Zinssatz (z. B. 3 %)
• B3: Laufzeit in Monaten (z. B. 120)
• B4 (Ergebnisformel): =RMZ(B2/12; B3; -B1)
  • Ergebnis: Die monatliche Rate wird berechnet.

2. Ein-Variablen-Datentabelle

Diese Tabelle zeigt, wie sich das Ergebnis ändert, wenn sich nur eine Variable (z. B. der Zinssatz) ändert.

Schritt-für-Schritt-Anleitung:

1. Eingabewerte auflisten: Schreibe verschiedene Zinssätze untereinander in eine Spalte (z. B. von D3 bis D7: 2,0%, 2,5%, 3,0%, 3,5%, 4,0%).
2. Formel verknüpfen: Gehe eine Zelle nach rechts und eine nach oben (Zelle E2). Gib dort = ein und klicke auf dein Ergebnis im Basismodell (Zelle B4).
3. Bereich markieren: Markiere den Bereich, der sowohl die Liste der Zinssätze als auch die verknüpfte Formel enthält (D2:E7).
4. Funktion aufrufen:
   • Gehe im Menü auf den Reiter Daten.
   • Klicke auf Was-wäre-wenn-Analyse.
   • Wähle Datentabelle....
5. Zellen zuweisen:
   • Da unsere Zinssätze in einer Spalte stehen, lassen wir "Zeileneingabe" leer.
   • Bei Werte aus Spalte, klicke auf die Zelle B2 (den Zinssatz in deinem Basismodell).
6. Bestätigen: Klicke auf OK. Excel füllt nun automatisch die monatlichen Raten für jeden Zinssatz aus.

3. Zwei-Variablen-Datentabelle

Diese Tabelle zeigt die Auswirkungen, wenn sich zwei Variablen gleichzeitig ändern (z. B. Zinssatz UND Kreditbetrag).

Schritt-für-Schritt-Anleitung:

1. Struktur aufbauen:
   • Wähle eine leere Zelle als Ecke (z. B. D10). Gib dort = ein und verknüpfe sie mit dem Ergebnis (B4).
   • Schreibe rechts daneben in die gleiche Zeile verschiedene Zinssätze (E10, F10, G10...).
   • Schreibe unter die Eckzelle in die gleiche Spalte verschiedene Kreditbeträge (D11, D12, D13...).
2. Bereich markieren: Markiere den gesamten Block (von der Formel-Ecke bis zum letzten Wert unten rechts).
3. Funktion aufrufen:
   • Daten > Was-wäre-wenn-Analyse > Datentabelle....
4. Zellen zuweisen:
   • Werte aus Zeile: Klicke auf die Zelle im Basismodell, die den Werten in deiner Tabellen-Kopfzeile entspricht (hier B2 für den Zinssatz).
   • Werte aus Spalte: Klicke auf die Zelle im Basismodell, die den Werten in deiner Tabellen-Spalte entspricht (hier B1 für den Kreditbetrag).
5. Bestätigen: Klicke auf OK. Excel erstellt eine Matrix, die zeigt, wie die monatliche Rate bei jeder Kombination aus Zins und Betrag aussieht.

4. Tipps für Profis

Formel in der Ecke verstecken

Die verknüpfte Formel in der oberen linken Ecke der Zwei-Variablen-Tabelle sieht oft unschön aus.

• Lösung: Rechtsklick auf die Zelle > Zellen formatieren > Benutzerdefiniert. Gib als Typ ;;; (drei Semikolons) ein. Der Wert ist noch da, aber unsichtbar.

Automatische vs. Manuelle Berechnung

Datentabellen können eine Datei verlangsamen, wenn sie sehr groß sind.

• Du kannst unter Formeln > Berechnungsoptionen die Option "Automatisch außer Datentabellen" wählen. Dann werden die Tabellen nur aktualisiert, wenn du F9 drückst.

Formatierung

Nutze die Bedingte Formatierung (z. B. Farbskalen), um in der Zwei-Variablen-Tabelle sofort zu sehen, wo die Werte besonders hoch (rot) oder niedrig (grün) sind.

Zusammenfassung:

• Ein-Variablen-Tabelle: Ideal, um Szenarien für einen Faktor zu vergleichen.
• Zwei-Variablen-Tabelle: Perfekt für die Visualisierung von Wechselwirkungen zwischen zwei Faktoren.