Bridgesheet

Finanzmodellierung7 Min. Lesezeit

Sensitivitätsanalyse Excel: Finanzmodell mit Datentabelle

Eine Sensitivitätsanalyse in Excel berechnet das Ergebnis eines Modells für mehrere Werte einer oder zweier Annahmen neu, ohne das Modell zu kopieren. So bauen Sie sie mit der Datentabelle auf, prüfen und lesen sie, an einem DCF, dessen Wert zwischen 12,7 und 19,9 Mio. € liegt.

Altes Buch, aufgeschlagen bei einer Logarithmentafel mit Zahlenspalten
Foto: ArnoldReinhold · CC BY-SA · Wikimedia

Wozu dient eine Sensitivitätsanalyse in Excel

Ein Finanzmodell beruht auf einigen Annahmen, die niemand sicher kennt: Kapitalkosten, langfristiges Wachstum, eine Marge, ein Exit-Multiple. Eine Sensitivitätsanalyse in Excel zeigt, wie sich das Ergebnis des Modells (Unternehmenswert, IRR, Preis je Aktie) verändert, wenn sich diese Annahmen verschieben. Sie beantwortet die Frage, die ein Investitionskomitee, ein Käufer oder ein Banker stellt: „Und wenn Sie um einen Prozentpunkt danebenliegen?"

Excel liefert dafür ein eingebautes Werkzeug, die Datentabelle (Menü Daten, dann Was-wäre-wenn-Analyse). Das Werkzeug ersetzt nacheinander den Wert einer Annahmezelle durch jeden Wert einer Zeile oder Spalte, berechnet das gesamte Modell neu und trägt das Ergebnis in das Raster ein. So müssen Sie das Modell nicht fünf- oder fünfundzwanzigmal kopieren.

Das Beispiel: ein DCF mit 15,4 Mio. €

Nehmen wir Laboratoires Vasseur, ein fiktives Unternehmen für Industrieanalysen. Sein DCF (Abzinsung der künftigen Free Cashflows) beruht auf Folgendem:

  • ein Umsatz von 15,0 Mio. €, der in fünf Jahren um 6 %, 5 %, 4 %, 3 % und 2 % wächst;
  • eine EBITDA-Marge (operatives Ergebnis vor Abschreibungen) von 14 %;
  • Abschreibungen von 3 % des Umsatzes, Investitionen von 3,5 % und ein Working Capital (das in Vorräten und Kundenforderungen gebundene Geld) von 15 % des Umsatzes;
  • ein Steuersatz von 25 %, ein WACC (gewichtete durchschnittliche Kapitalkosten) von 10 % und ein ewiges Wachstum von 2 %.

Der Free Cashflow steigt von 1,10 Mio. € im ersten Jahr auf 1,36 Mio. € im fünften. Die fünf abgezinsten Cashflows ergeben 4,64 Mio. €; der Endwert, 1,36 × 1,02 ÷ (10 % − 2 %) = 17,34 Mio. €, beträgt abgezinst 10,77 Mio. €. Der Unternehmenswert liegt bei 15,4 Mio. €, davon stammen 70 % aus dem Endwert. Gerade diese Abhängigkeit macht die Sensitivitätsanalyse unverzichtbar: Der Endwert reagiert stark auf WACC und Wachstum, wie unser Artikel zum Terminal Value im DCF erklärt.

Sensitivitätsanalyse mit einer Variablen

Beginnen Sie mit einer einzigen Annahme, hier der EBITDA-Marge.

  1. Schreiben Sie auf dem Blatt, auf dem die Margenzelle (14 %) liegt, die zu testenden Werte untereinander: 12 %, 13 %, 14 %, 15 %, 16 %.
  2. Tippen Sie in die Zelle eine Zeile über und eine Spalte rechts vom ersten Wert eine Formel, die auf das Ergebnis verweist: =C40, wenn der Unternehmenswert in C40 steht.
  3. Markieren Sie den gesamten Block, Werte und Formel eingeschlossen.
  4. Menü Daten > Was-wäre-wenn-Analyse > Datentabelle. Lassen Sie „Werte aus Zeile" leer und tragen Sie die Margenzelle bei „Werte aus Spalte" ein.

Das Ergebnis:

EBITDA-Marge Unternehmenswert
12 % 12,3 Mio. €
13 % 13,8 Mio. €
14 % 15,4 Mio. €
15 % 17,0 Mio. €
16 % 18,5 Mio. €

Ein Prozentpunkt Marge ist rund 1,6 Mio. € wert, also 10 % des Unternehmenswerts. Bei einem Unternehmen, dessen Marge in den letzten drei Jahren zwischen 12 % und 15 % schwankte, ist das die Annahme, die Sie zuerst begründen müssen.

Sensitivitätsanalyse mit zwei Variablen: WACC × Wachstum

Die gängigste Tabelle eines DCF kreuzt den WACC mit dem ewigen Wachstum.

  1. Schreiben Sie die WACC-Werte in eine Zeile (9 % bis 11 % in Schritten von 0,5 Punkten) und die Wachstumsraten in eine Spalte (1 % bis 3 %).
  2. Setzen Sie in die Eckzelle, am Schnittpunkt von Zeile und Spalte, die Formel =C40.
  3. Markieren Sie den gesamten Block und öffnen Sie die Datentabelle: die WACC-Zelle bei „Werte aus Zeile", die Wachstumszelle bei „Werte aus Spalte".

Excel füllt das Raster mit einer Matrixformel {=MEHRFACHOPERATION(C12;C13)}. Unternehmenswert von Laboratoires Vasseur in Mio. €:

g \ WACC 9,0 % 9,5 % 10,0 % 10,5 % 11,0 %
1,0 % 15,9 15,0 14,1 13,4 12,7
1,5 % 16,7 15,7 14,7 13,9 13,1
2,0 % 17,6 16,5 15,4 14,5 13,7
2,5 % 18,7 17,4 16,2 15,2 14,3
3,0 % 19,9 18,4 17,1 15,9 14,9

Der Wert reicht von 12,7 bis 19,9 Mio. €. Ein Prozentpunkt mehr oder weniger WACC verschiebt ihn um 1,7 bis 2,2 Mio. €; ein halber Punkt Wachstum um rund 0,8 Mio. €. Bei 4,0 Mio. € Nettoverschuldung liegt der Eigenkapitalwert zwischen 8,7 und 15,9 Mio. €: Die relative Spanne ist auf Seiten der Aktionäre noch größer, weil die Schulden sich nicht bewegen.

Bauregeln, die Fehler vermeiden

Die Datentabelle hat wenig bekannte Einschränkungen. Wer sie ignoriert, erhält ein falsches Raster ohne Fehlermeldung.

  • Die Eingabezellen müssen auf demselben Blatt liegen wie die Tabelle. Wird der WACC in einem eigenen Blatt berechnet, legen Sie auf dem DCF-Blatt eine Annahmezelle an, die ihn speist, und lassen die Tabelle darauf zeigen.
  • Die Eingabezelle muss einen Wert enthalten, keine Formel. Excel setzt jeden getesteten Wert dort ein; ersetzt es eine Formel, geht die Verbindung zum Rest des Modells während der Berechnung verloren.
  • Zeile und Spalte nicht vertauschen. Das ist der häufigste Fehler: Der WACC wird anstelle des Wachstums getestet, und die Tabelle bleibt plausibel. Die Kontrolle ist einfach: Die mittlere Zelle muss auf den Cent genau dem Ergebnis des Basisfalls entsprechen (hier 15,4 Mio. €).
  • Eine ungerade Zahl von Schritten, Basisfall in der Mitte. Je zwei Werte beidseits der gewählten Annahme machen das Lesen unmittelbar. Heben Sie die Basisfallzelle per bedingter Formatierung fett oder farbig hervor.
  • Schritte passend zur tatsächlichen Unsicherheit. 0,5 Punkte für WACC und Wachstum, 1 Punkt für eine Marge, 0,5x für ein Multiple. Zu große Schritte liefern Extremwerte, die niemand vertritt.
  • Absurde Kombinationen ausschließen. Ein ewiges Wachstum, das dem WACC entspricht oder ihn übersteigt, lässt die Gordon-Shapiro-Formel divergieren. Halten Sie mindestens 5 Punkte Abstand oder zeigen Sie mit einer WENN-Formel „n. a." an.

Berechnung, Geschwindigkeit und zirkuläre Modelle

Jede Zelle der Tabelle löst eine vollständige Neuberechnung des Modells aus. Ein 5 × 5-Raster löst also bei jeder Änderung 25 Berechnungen aus, und ein schweres Modell wird langsam. Zwei Einstellungen helfen:

  • Datei > Optionen > Formeln > „Automatisch außer bei Datentabellen". Die Tabellen werden dann nur mit F9 neu berechnet. Drücken Sie F9, bevor Sie drucken oder die Datei versenden, sonst zeigt das Raster veraltete Werte.
  • Begrenzen Sie die Zahl der Tabellen. Drei gut gewählte Tabellen sind besser als zehn.

LBO-Modelle werfen ein besonderes Problem auf: Die Zinsen, berechnet auf die durchschnittliche Schuld des Jahres, hängen vom Cashflow ab, der diese Schuld tilgt. Dieser Zirkelbezug, den die iterative Berechnung von Excel löst, macht Datentabellen manchmal instabil. Üblich ist ein Schalter, der die Zinsen während der Sensitivität auf die Anfangsschuld berechnet, oder ein kleines Makro, das die Werte einzeln kopiert. In einem LBO-Modell kreuzt die typische Tabelle das Einstiegs- mit dem Ausstiegsmultiple, mit der IRR in der Mitte; das Paper LBO zeigt die Größenordnung dieser Sensitivität.

Welche Annahmen je nach Modell testen

Modell Beobachtetes Ergebnis Nützlichste Tabelle mit zwei Variablen
DCF Unternehmenswert, Preis je Aktie WACC × ewiges Wachstum oder WACC × Exit-Multiple
LBO IRR und MOIC der Investoren Einstiegsmultiple × Ausstiegsmultiple oder EBITDA-Wachstum × Verschuldungsgrad
Fusionsmodell Gewinnverwässerung oder -steigerung je Aktie Kaufpreis × Anteil in Aktien
Liquiditätsplan Mindestliquidität Zahlungsziel der Kunden × Umsatzwachstum

Wählen Sie Annahmen, die zugleich unsicher und einflussreich sind. Der Steuersatz ist einflussreich, aber bekannt; das Wachstum im fünften Jahr ist unsicher, wiegt aber wenig. Um die Annahmen zu ordnen, genügt je Annahme eine Tabelle mit einer Variablen und gleicher Schwankungsbreite (zum Beispiel ± 10 %): Die Annahme, die das Ergebnis am stärksten verschiebt, verdient die Tabelle mit zwei Variablen.

Eine Sensitivitätstabelle lesen und präsentieren

Eine Sensitivitätstabelle ist keine Wertspanne. Ihre Ecken kombinieren zwei Extremannahmen gleichzeitig, was selten realistisch ist. Um einen Wert zu präsentieren, halten Sie sich lieber an den mittleren Bereich (hier 14,5 bis 16,5 Mio. €, also ein halber Punkt WACC beidseits) und begründen Sie ihn.

Prüfen Sie vor der Weitergabe der Tabelle drei Punkte: die mittlere Zelle entspricht dem Basisfall; die Werte bewegen sich in die richtige Richtung (der Wert sinkt, wenn der WACC steigt); die Tabelle ist neu berechnet (F9). Diese Kontrollen gehören zu den fünf Prüfungen eines DCF-Modells, die Sie durchführen sollten, bevor Sie einem Ergebnis trauen.

Unser DCF-Modell für Excel enthält eine fertige Sensitivitätstabelle WACC × Wachstum, mit dem Basisfall in der Mitte und dem angezeigten Anteil des Endwerts. Es ist ein Rechenwerkzeug: Das Ergebnis bleibt eine Schätzung, die von Ihren Annahmen abhängt, weder ein zertifiziertes Gutachten noch eine Anlageberatung.