Die Sensitivitätsanalyse in Excel hilft uns, die Unsicherheit in der Ausgabe des Modells mit den Änderungen in den Eingabevariablen zu untersuchen. Es führt in erster Linie Stresstests unserer modellierten Annahmen durch und führt zu Erkenntnissen mit Mehrwert.

Im Zusammenhang mit der DCF-Bewertung ist die Sensitivitätsanalyse in Excel im Finanzbereich besonders nützlich, um den Aktienkurs oder die Bewertungssensitivität gegenüber Annahmen wie Wachstumsraten oder Kapitalkosten zu modellieren.

In diesem Artikel betrachten wir die folgende Sensitivitätsanalyse in Excel für die DCF-Modellierung professionell.

Am wichtigsten - Laden Sie die Empfindlichkeitsanalyse in eine Excel-Vorlage herunter

Lernen Sie eine Variable und zwei Variablen. DATENTABELLENTypen in Excel

Sensitivitätsanalyse in Excel

# 1 - Sensitivitätsanalyse für Datentabellen mit einer Variablen in Excel

Nehmen wir das folgende Finanzbeispiel (Dividendenrabattmodell), um dieses im Detail zu verstehen.

Konstantes Wachstum DDM gibt uns den beizulegenden Zeitwert einer Aktie als Barwert eines unendlichen Stroms von Dividenden an, die mit konstanter Geschwindigkeit wachsen.

Die Gordon-Wachstumsformel lautet wie folgt:

Wo:

D1 = Wert der Dividende, die im nächsten Jahr zu erhalten ist
D0 = Wert der in diesem Jahr erhaltenen Dividende
g = Wachstumsrate der Dividende
Ke = Diskontsatz

Nehmen wir nun an, wir möchten verstehen, wie empfindlich der Aktienkurs in Bezug auf die erwartete Rendite (ke) ist. Es gibt zwei Möglichkeiten, dies zu tun -

Esel Weg :-)
Was, wenn die Analyse

# 1 - Esel Weg

Die Sensitivitätsanalyse in Excel nach Esels Art ist sehr einfach, aber schwer zu implementieren, wenn viele Variablen beteiligt sind.

Möchten Sie dies unter 1000 Annahmen fortsetzen? Offensichtlich nicht!

Lernen Sie die folgende Sensitivitätsanalyse in Excel-Technik, um sich vor den Problemen zu schützen.

# 2 - Verwenden einer variablen Datentabelle

Der beste Weg, um eine Sensitivitätsanalyse in Excel durchzuführen, ist die Verwendung von Datentabellen. Datentabellen bieten eine Verknüpfung zum Berechnen mehrerer Versionen in einem Vorgang und eine Möglichkeit, die Ergebnisse aller verschiedenen Variationen zusammen in Ihrem Arbeitsblatt anzuzeigen und zu vergleichen. Im Folgenden finden Sie die Schritte, die Sie ausführen können, um eine eindimensionale Sensitivitätsanalyse in Excel zu implementieren.

Schritt 1 - Erstellen Sie die Tabelle in einem Standardformat

In der ersten Spalte haben Sie die Eingabeannahmen. In unserem Beispiel sind Eingaben die erwartete Rendite (ke). Beachten Sie außerdem, dass sich unter der Tabellenüberschrift eine leere Zeile (in dieser Übung blau gefärbt) befindet. Diese leere Zeile dient einem wichtigen Zweck für diese eindimensionale Datentabelle, die Sie in Schritt 2 sehen werden.

Schritt 2 - Verknüpfen Sie den Referenzeingang und -ausgang wie im folgenden Schnappschuss angegeben.

Der durch die leere Zeile bereitgestellte Platz wird jetzt verwendet, um die Eingabe (erwartete Rückgabe Ke) und die Ausgabeformel bereitzustellen. Warum wird es so gemacht?

Wir werden "Was wäre wenn Analyse" verwenden. Dies ist eine Möglichkeit, Excel anzuweisen, dass für die Eingabe (ke) die entsprechende Formel auf der rechten Seite verwendet werden sollte, um alle anderen Eingaben neu zu berechnen.

Schritt 3 - Wählen Sie das Was-wäre-wenn-Analyse-Tool aus, um die Sensitivitätsanalyse in Excel durchzuführen

Es ist wichtig zu beachten, dass dies in zwei Schritte unterteilt ist

Wählen Sie den Tabellenbereich von der linken Seite ab 10% bis zur unteren rechten Ecke der Tabelle.
Klicken Sie auf Daten -> Was wäre wenn Analyse -> Datentabellen

Schritt 4 - Das Dialogfeld Datentabelle wird geöffnet.

Das Dialogfeld sucht nach zwei Eingaben - Zeileneingabe und Spalteneingabe. Da nur eine Eingabe Ke in Betracht gezogen wird, werden wir eine einspaltige Eingabe bereitstellen.

Schritt 5 - Verknüpfen Sie die Spalteneingabe

In unserem Fall werden alle Eingaben in einer Spalte bereitgestellt, und daher werden wir mit der Spalteneingabe verknüpfen. Die Spalteneingabe ist mit der erwarteten Rückgabe (Ke) verknüpft. Bitte beachten Sie, dass die Eingabe von der Originalquelle und nicht von der in der Tabelle enthaltenen verknüpft werden sollte

Schritt 6 - Genießen Sie die Ausgabe

# 2 - Sensitivitätsanalyse für Datentabellen mit zwei Variablen in Excel

Datentabellen sind sehr nützlich für die Sensitivitätsanalyse in Excel, insbesondere im Fall von DCF. Sobald ein Basisfall erstellt ist, sollte die DCF-Analyse immer unter verschiedenen Sensitivitätsszenarien getestet werden. Bei der Prüfung wird untersucht, inwieweit sich verschiedene Änderungen der Annahmen (Kapitalkosten, Endwachstumsraten, geringeres Umsatzwachstum, höhere Kapitalanforderungen usw.) inkrementell auf den beizulegenden Zeitwert der Aktie auswirken.

Nehmen wir die Sensitivitätsanalyse in Excel anhand eines Finanzbeispiels der Alibaba Discounted Cash Flow Analysis.

Mit den Grundannahmen der Kapitalkosten von 9% und einer konstanten Wachstumsrate von 3% kamen wir zu einer fairen Bewertung von 191,45 Mrd. USD.

Nehmen wir jetzt an, dass Sie den Kapitalkostenannahmen oder den Annahmen zur Wachstumsrate, die ich bei der IPO-Bewertung von Alibaba getroffen habe, nicht vollständig zustimmen. Möglicherweise möchten Sie die Annahmen ändern und auf die Auswirkungen auf die Bewertungen zugreifen.

Eine Möglichkeit besteht darin, die Annahmen manuell zu ändern und die Ergebnisse jeder Änderung zu überprüfen. (Codewort - Eselmethode!)

Wir sind jedoch hier, um eine viel bessere und effizientere Methode zur Berechnung der Bewertung mithilfe der Sensitivitätsanalyse in Excel zu diskutieren, die nicht nur Zeit spart, sondern uns auch die Möglichkeit bietet, alle Ausgabedetails in einem effektiven Format zu visualisieren.

Wenn wir die Was-wäre-wenn-Analyse in Excel auf professionelle Weise mit den oben genannten Daten durchführen, erhalten wir die folgende Ausgabe.

Hier bestehen Zeileneingaben aus Änderungen der Kapitalkosten oder des WACC (7% bis 11%).
Spalteneingaben bestehen aus Änderungen der Wachstumsraten (1% bis 6%)
Der Schnittpunkt ist Alibaba Valuation. Zum Beispiel. Unter Verwendung unseres Basisfalls von 9% WACC und 3% Wachstumsraten erhalten wir eine Bewertung von 191,45 Mrd. USD.

Lassen Sie uns vor diesem Hintergrund untersuchen, wie wir eine solche Sensitivitätsanalyse in Excel mithilfe zweidimensionaler Datentabellen erstellen können.

Schritt 1 - Erstellen Sie die Tabellenstruktur wie unten angegeben

Da wir zwei Annahmen haben - Kapitalkosten (WACC) und Wachstumsraten (g) - müssen Sie eine Tabelle unten erstellen.
Sie können die Zeilen- und Spalteneingaben umschalten. Anstelle von WACC können Sie Wachstumsraten haben und umgekehrt.

Schritt 2 - Verknüpfen Sie den Schnittpunkt mit der Ausgabezelle.

Der Schnittpunkt der beiden Eingänge sollte verwendet werden, um den gewünschten Ausgang zu verbinden. In diesem Fall möchten wir die Auswirkungen dieser beiden Variablen (WACC und Wachstumsrate) auf den Eigenkapitalwert sehen. Daher haben wir die sich überschneidende Zelle mit der Ausgabe verknüpft.

Schritt 3 - Öffnen Sie die zweidimensionale Datentabelle

Wählen Sie die Tabelle aus, die Sie erstellt haben
Klicken Sie dann auf Daten -> Was wäre wenn Analyse -> Datentabellen

Schritt 4 - Geben Sie die Zeilen- und Spalteneingaben an.

Die Zeileneingabe sind die Kapitalkosten oder Ke.
Die Spalteneingabe ist die Wachstumsrate.
Bitte denken Sie daran, diese Eingaben von der ursprünglichen Annahmequelle und nicht von irgendwo in der Tabelle zu verknüpfen

Schritt 5 - Genießen Sie die Ausgabe.

Die meisten pessimistischen Produktionswerte liegen in der rechten oberen Ecke, wo die Kapitalkosten 11% und die Wachstumsrate nur 1% betragen
Der optimistischste IPO-Wert von Alibaba ist, wenn Ke 7% und g 6% beträgt
Der Basisfall, den wir für 9% ke und 3% Wachstumsraten berechnet haben, liegt in der Mitte.
Diese zweidimensionale Sensitivitätsanalyse in der Excel-Tabelle bietet den Kunden eine einfache Szenarioanalyse, die viel Zeit spart.

# 3 - Zielsuche für Sensitivitätsanalyse in Excel

Mit dem Befehl Zielsuche wird eine Formel auf einen bestimmten Wert gebracht
Dazu wird eine der Zellen geändert, auf die in der Formel verwiesen wird
Bei der Zielsuche wird nach einer Zellreferenz gefragt, die eine Formel enthält (die festgelegte Zelle). Es wird auch nach einem Wert gefragt, der der Zahl entspricht, der die Zelle entsprechen soll
Schließlich fordert Goal Seek eine Zelle auf, sich zu ändern, um die Set-Zelle auf den erforderlichen Wert zu bringen

Lassen Sie uns einen Blick auf den DCF der IPO-Bewertung von Alibaba werfen.

Wie wir von DCF wissen, stehen Wachstumsraten und Bewertung in direktem Zusammenhang. Steigende Wachstumsraten erhöhen den Aktienkurs der Aktie.

Nehmen wir an, wir möchten prüfen, mit welcher Wachstumsrate der Aktienkurs 80 USD erreichen wird.

Wie immer können wir dies manuell tun, indem wir die Wachstumsraten ändern, um weiterhin die Auswirkungen auf den Aktienkurs zu sehen. Dies wird wieder ein langwieriger Prozess sein. Möglicherweise müssen wir mehrmals Wachstumsraten eingeben, um sicherzustellen, dass der Aktienkurs in unserem Fall 80 USD entspricht.

Wir können jedoch eine Funktion wie Goal Seek in Excel verwenden, um dies in einfachen Schritten zu lösen.

Schritt 1 - Klicken Sie auf die Zelle, deren Wert Sie festlegen möchten. (Die Set-Zelle muss eine Formel enthalten.)

Schritt 2 - Wählen Sie im Menü Extras, Zielsuche. Das folgende Dialogfeld wird angezeigt:

Der Befehl Zielsuche schlägt automatisch die aktive Zelle als festgelegte Zelle vor.
Dies kann mit einer neuen Zellreferenz überschrieben werden, oder Sie können auf die entsprechende Zelle in der Tabelle klicken.
Geben Sie nun den gewünschten Wert ein, den diese Formel erreichen soll.
Klicken Sie in das Feld "Zu Wert" und geben Sie den Wert ein, den Ihre ausgewählte Formel haben soll
Klicken Sie abschließend in das Feld "Durch Ändern der Zelle" und geben Sie entweder die Zelle ein oder klicken Sie darauf, deren Wert geändert werden kann, um das gewünschte Ergebnis zu erzielen
Klicken Sie auf die Schaltfläche OK. In der Tabelle wird die Zelle auf einen Wert geändert, der ausreicht, damit die Formel Ihr Ziel erreicht.

Schritt 3 - Genießen Sie die Ausgabe.

Die Zielsuche informiert Sie auch darüber, dass das Ziel erreicht wurde

Fazit

Die Sensitivitätsanalyse in Excel verbessert Ihr Verständnis für das finanzielle und betriebliche Verhalten des Unternehmens. Wie wir aus den drei Ansätzen - eindimensionale Datentabellen, zweidimensionale Datentabellen und Ziel - gelernt haben, ist die Sensitivitätsanalyse im Finanzbereich äußerst nützlich, insbesondere im Zusammenhang mit Bewertungen - DCF oder DDM.

Sie können sich jedoch auch auf Makroebene ein Bild über das Unternehmen und die Branche im Allgemeinen machen. Sie können Fälle entwickeln, die die Bewertungsempfindlichkeit gegenüber Änderungen der Zinssätze, der Rezession, der Inflation, des BIP usw. bei der Bewertung widerspiegeln. Denken und gesunder Menschenverstand sollten bei der Entwicklung vernünftiger und nützlicher Sensitivitätsfälle eingesetzt werden.

Was als nächstes?

Wenn Sie etwas über die Sensitivitätsanalyse in Excel gelernt haben, hinterlassen Sie bitte unten einen Kommentar. Lass mich wissen was du denkst. Vielen Dank und pass auf dich auf. Viel Spaß beim Lernen!

In den folgenden Artikeln erfahren Sie auch mehr über Bewertungen und Unternehmensfinanzierung.