Sensitivitätsanalyse in Excel (Beispieldatenblatt)

Prozesse im Finanzbereich sind immer miteinander verbunden – ein Faktor hängt vom anderen ab und verändert sich mit ihm. Verfolgen Sie diese Änderungen und verstehen Sie, was Sie in Zukunft erwarten können, vielleicht mithilfe von Excel-Funktionen und Tabellenkalkulationsmethoden.

Mehrere Ergebnisse mit einer Datentabelle erhalten

Datenblattfunktionen sind Elemente der Was-wäre-wenn-Analyse, die häufig über Microsoft Excel durchgeführt wird. Dies ist der zweite Name für die Sensitivitätsanalyse.

Überblick

Eine Datentabelle ist eine Art Zellbereich, der zur Lösung von Problemen verwendet werden kann, indem die Werte in einigen Zellen geändert werden. Es wird erstellt, wenn es notwendig ist, Änderungen in den Komponenten der Formel zu verfolgen und Aktualisierungen der Ergebnisse gemäß diesen Änderungen zu erhalten. Lassen Sie uns herausfinden, wie man Datentabellen in der Forschung verwendet und welche Typen sie sind.

Grundlagen zu Datentabellen

Es gibt zwei Arten von Datentabellen, sie unterscheiden sich in der Anzahl der Komponenten. Sie müssen eine Tabelle mit Schwerpunkt auf der Anzahl der Werte erstellen, die Sie damit überprüfen müssen.

Statistiker verwenden eine einzelne Variablentabelle, wenn es nur eine Variable in einem oder mehreren Ausdrücken gibt, die ihr Ergebnis ändern können. Beispielsweise wird es häufig in Verbindung mit der PMT-Funktion verwendet. Die Formel dient zur Berechnung der Höhe der regelmäßigen Zahlung und berücksichtigt den im Vertrag festgelegten Zinssatz. Bei solchen Berechnungen werden die Variablen in eine Spalte geschrieben und die Ergebnisse der Berechnungen in eine andere. Ein Beispiel für ein Typenschild mit 1 Variable:

Betrachten Sie als Nächstes die Platten mit 2 Variablen. Sie werden in Fällen verwendet, in denen zwei Faktoren die Änderung eines Indikators beeinflussen. Die beiden Variablen landen eventuell in einer weiteren, dem Kredit zugeordneten Tabelle, anhand derer die optimale Rückzahlungsdauer und die Höhe der monatlichen Rate ermittelt werden können. Bei dieser Berechnung müssen Sie auch die PMT-Funktion verwenden. Ein Beispiel für eine Tabelle mit 2 Variablen:

Erstellen einer Datentabelle mit einer Variablen

Betrachten Sie die Analysemethode am Beispiel einer kleinen Buchhandlung mit nur 100 Büchern auf Lager. Einige von ihnen können teurer verkauft werden (50 $), der Rest kostet die Käufer weniger (20 $). Berechnet werden die Gesamteinnahmen aus dem Verkauf aller Waren – der Besitzer entschied, dass er 60 % der Bücher teuer verkaufen würde. Sie müssen herausfinden, wie sich der Umsatz erhöht, wenn Sie den Preis einer größeren Warenmenge erhöhen – 70 % und so weiter.

Passt auf! Der Gesamtumsatz muss mit einer Formel berechnet werden, da sonst keine Datentabelle erstellt werden kann.

1. Wählen Sie eine freie Zelle außerhalb des Blattrands aus und schreiben Sie die Formel hinein: =Zelle der Gesamteinnahmen. Wenn zum Beispiel Einkommen in Zelle C14 geschrieben wird (zufällige Bezeichnung wird angezeigt), müssen Sie Folgendes schreiben: =S14.
2. Wir schreiben den Prozentsatz des Warenvolumens in die Spalte links von dieser Zelle – nicht darunter, das ist sehr wichtig.
3. Wir wählen den Zellbereich aus, in dem sich die Prozentspalte und der Link zum Gesamteinkommen befinden.

4. Wir finden auf der Registerkarte „Daten“ den Punkt „Was-wäre-wenn-Analyse“ und klicken darauf – wählen Sie im sich öffnenden Menü die Option „Datentabelle“.

5. Es öffnet sich ein kleines Fenster, in dem Sie in der Spalte „Werte durch Zeilen ersetzen in …“ eine Zelle mit dem Prozentsatz der ursprünglich zu einem hohen Preis verkauften Bücher angeben müssen. Dieser Schritt wird durchgeführt, um den Gesamtumsatz unter Berücksichtigung des steigenden Prozentsatzes neu zu berechnen.

Nachdem Sie in dem Fenster, in dem die Daten zum Erstellen der Tabelle eingegeben wurden, auf die Schaltfläche „OK“ geklickt haben, werden die Ergebnisse der Berechnungen in den Zeilen angezeigt.

Hinzufügen einer Formel zu einer Datentabelle mit einer einzelnen Variable

Aus einer Tabelle, die bei der Berechnung einer Aktion mit nur einer Variablen geholfen hat, können Sie ein ausgeklügeltes Analysetool erstellen, indem Sie eine zusätzliche Formel hinzufügen. Er muss neben einer bereits bestehenden Formel eingetragen werden – ist die Tabelle beispielsweise zeilenorientiert, tragen wir den Ausdruck in die Zelle rechts neben der bestehenden ein. Wenn die Spaltenausrichtung festgelegt ist, schreiben wir die neue Formel unter die alte. Folgen Sie als Nächstes dem Algorithmus:

1. Wählen Sie den Zellbereich erneut aus, aber jetzt sollte er die neue Formel enthalten.
2. Öffnen Sie das „Was wäre wenn“-Analysemenü und wählen Sie „Datenblatt“.
3. Wir fügen eine neue Formel in das entsprechende Feld in Zeilen oder Spalten ein, je nach Ausrichtung der Platte.

Erstellen Sie eine Datentabelle mit zwei Variablen

Der Anfang einer solchen Tabelle ist etwas anders – Sie müssen einen Link zum Gesamtumsatz über den Prozentwerten setzen. Als nächstes führen wir diese Schritte aus:

1. Schreiben Sie Preisoptionen in eine Zeile mit einem Link zum Einkommen – eine Zelle für jeden Preis.
2. Wählen Sie einen Zellbereich aus.

3. Öffnen Sie das Datentabellenfenster wie beim Erstellen einer Tabelle mit einer Variablen – über die Registerkarte „Daten“ in der Symbolleiste.
4. Ersetzen Sie in der Spalte „Ersetze Werte durch Spalten in …“ eine Zelle mit einem anfänglich hohen Preis.
5. Fügen Sie eine Zelle mit dem anfänglichen Prozentsatz des Verkaufs teurer Bücher in die Spalte „Werte durch Zeilen ersetzen in …“ ein und klicken Sie auf „OK“.

Dadurch wird die gesamte Tabelle mit Beträgen möglicher Einnahmen mit unterschiedlichen Bedingungen für den Verkauf von Waren gefüllt.

Beschleunigen Sie Berechnungen für Arbeitsblätter mit Datentabellen

Wenn Sie schnelle Berechnungen in einer Datentabelle benötigen, die keine Neuberechnung der gesamten Arbeitsmappe auslösen, gibt es ein paar Dinge, die Sie tun können, um den Prozess zu beschleunigen.

1. Öffnen Sie das Optionsfenster, wählen Sie im Menü rechts den Punkt „Formeln“.
2. Wählen Sie im Abschnitt „Berechnungen in der Arbeitsmappe“ den Punkt „Automatisch, außer für Datentabellen“.

3. Lassen Sie uns die Ergebnisse in der Tabelle manuell neu berechnen. Wählen Sie dazu die Formeln aus und drücken Sie die F-Taste.

Weitere Tools zur Durchführung von Sensitivitätsanalysen

Das Programm enthält weitere Tools, die Sie bei der Durchführung einer Sensitivitätsanalyse unterstützen. Sie automatisieren einige Aktionen, die sonst manuell durchgeführt werden müssten.

1. Die Funktion „Parameterauswahl“ ist geeignet, wenn das gewünschte Ergebnis bekannt ist und Sie den Eingabewert der Variablen kennen müssen, um ein solches Ergebnis zu erhalten.
2. „Suche nach einer Lösung“ ist ein Add-on zum Lösen von Problemen. Es ist notwendig, Grenzen zu setzen und darauf hinzuweisen, woraufhin das System die Antwort findet. Die Lösung wird durch Ändern der Werte bestimmt.
3. Die Sensitivitätsanalyse kann mit dem Szenario-Manager durchgeführt werden. Dieses Tool finden Sie im Menü „Was-wäre-wenn-Analyse“ auf der Registerkarte „Daten“. Es ersetzt Werte in mehreren Zellen – die Zahl kann 32 erreichen. Der Dispatcher vergleicht diese Werte, damit der Benutzer sie nicht manuell ändern muss. Ein Beispiel für die Verwendung des Skript-Managers:

Sensitivitätsanalyse eines Investitionsvorhabens in Excel

Was-wäre-wenn-Analysen sind besonders nützlich in Situationen, in denen Prognosen erforderlich sind, z. B. bei Investitionen. Analysten verwenden diese Methode, um herauszufinden, wie sich der Wert der Aktie eines Unternehmens aufgrund von Änderungen einiger Faktoren ändern wird.

Methode zur Analyse der Investitionsempfindlichkeit

Verwenden Sie bei der Analyse von „Was wäre, wenn“ die Aufzählung – manuell oder automatisch. Der Wertebereich ist bekannt und sie werden einzeln in die Formel eingesetzt. Das Ergebnis ist eine Menge von Werten. Wählen Sie daraus die passende Zahl aus. Betrachten wir vier Indikatoren, für die eine Sensitivitätsanalyse im Finanzbereich durchgeführt wird:

1. Nettogegenwartswert – Berechnet durch Subtrahieren des Investitionsbetrags vom Einkommensbetrag.
2. Interne Rendite / Gewinn – gibt an, wie viel Gewinn aus einer Investition in einem Jahr erzielt werden muss.
3. Die Amortisationsquote ist das Verhältnis aller Gewinne zur Anfangsinvestition.
4. Diskontierter Gewinnindex – zeigt die Effektivität der Investition an.

Formel

Die Einbettungsempfindlichkeit kann mit dieser Formel berechnet werden: Änderung des Ausgangsparameters in % / Änderung des Eingangsparameters in %.

Die Ausgabe- und Eingabeparameter können die zuvor beschriebenen Werte sein.

1. Sie müssen das Ergebnis unter Standardbedingungen kennen.
2. Wir ersetzen eine der Variablen und überwachen die Änderungen im Ergebnis.
3. Wir berechnen die prozentuale Änderung beider Parameter relativ zu den festgelegten Bedingungen.
4. Wir setzen die erhaltenen Prozentsätze in die Formel ein und bestimmen die Empfindlichkeit.

Ein Beispiel für die Sensitivitätsanalyse eines Investitionsprojekts in Excel

Zum besseren Verständnis der Analysemethodik wird ein Beispiel benötigt. Analysieren wir das Projekt mit den folgenden bekannten Daten:

1. Füllen Sie die Tabelle aus, um das Projekt darauf zu analysieren.

2. Wir berechnen den Cashflow mit der OFFSET-Funktion. In der Anfangsphase entspricht der Fluss den Investitionen. Als nächstes wenden wir die Formel an: =IF(OFFSET(Number,1;)=2;SUM(Inflow 1:Outflow 1);SUM(Inflow 1:Outflow 1)+$B$ 5)
   Zellbezeichnungen in der Formel können je nach Layout der Tabelle unterschiedlich sein. Am Ende kommt der Wert aus den Ausgangsdaten hinzu – der Restwert.

3. Wir bestimmen den Zeitraum, für den sich das Projekt auszahlt. Für die Anfangszeit verwenden wir diese Formel: =SUMMELL(G7: G17; »<0″). Der Zellbereich ist die Cashflow-Spalte. Für weitere Perioden wenden wir diese Formel an: =Anfangszeitraum+IF(Erster e.stream>0; Erster e.stream;0). Das Projekt hat den Break-Even-Punkt in 4 Jahren erreicht.

4. Wir erstellen eine Spalte für die Zahlen der Zeiträume, in denen sich das Projekt auszahlt.

5. Wir berechnen den Return on Investment. Es ist notwendig, einen Ausdruck zu machen, bei dem der Gewinn in einem bestimmten Zeitraum durch die Anfangsinvestition geteilt wird.

6. Den Abzinsungsfaktor ermitteln wir nach dieser Formel: =1/(1+Disc.%) ^Nummer.

7. Den Barwert berechnen wir durch Multiplikation – der Cashflow wird mit dem Abzinsungsfaktor multipliziert.

8. Lassen Sie uns den PI (Rentabilitätsindex) berechnen. Der Barwert über die Zeit wird durch die Investition zu Projektbeginn dividiert.

9. Lassen Sie uns den internen Zinssatz mit der IRR-Funktion definieren: =IRR(Bereich des Cashflows).

Analyse der Investitionsempfindlichkeit anhand des Datenblatts

Für die Analyse von Projekten im Investitionsbereich sind andere Methoden besser geeignet als eine Datentabelle. Viele Benutzer erleben Verwirrung beim Kompilieren einer Formel. Um die Abhängigkeit eines Faktors von Änderungen anderer herauszufinden, müssen Sie die richtigen Zellen zum Eingeben von Berechnungen und zum Lesen von Daten auswählen.

Faktoren- und Dispersionsanalyse in Excel mit Berechnungsautomatisierung

Eine weitere Typologie der Sensitivitätsanalyse ist die Faktoranalyse und die Varianzanalyse. Der erste Typ definiert die Beziehung zwischen Zahlen, der zweite zeigt die Abhängigkeit einer Variablen von anderen.

ANOVA in Excel

Der Zweck einer solchen Analyse besteht darin, die Variabilität des Werts in drei Komponenten zu unterteilen:

1. Variabilität durch den Einfluss anderer Werte.
2. Änderungen aufgrund der Beziehung der Werte, die sich darauf auswirken.
3. Zufällige Änderungen.

Lassen Sie uns eine Varianzanalyse mit dem Excel-Add-In „Data Analysis“ durchführen. Wenn es nicht aktiviert ist, kann es in den Einstellungen aktiviert werden.

Die anfängliche Tabelle muss zwei Regeln folgen: Es gibt eine Spalte für jeden Wert, und die darin enthaltenen Daten sind in aufsteigender oder absteigender Reihenfolge angeordnet. Es ist notwendig, den Einfluss des Bildungsniveaus auf das Konfliktverhalten zu überprüfen.

1. Suchen Sie das Datenanalyse-Tool auf der Registerkarte Daten und öffnen Sie sein Fenster. In der Liste müssen Sie die Einweg-Varianzanalyse auswählen.

2. Füllen Sie die Zeilen des Dialogfelds aus. Das Eingabeintervall umfasst alle Zellen, mit Ausnahme von Überschriften und Zahlen. Nach Spalten gruppieren. Wir zeigen die Ergebnisse auf einem neuen Blatt an.

Da der Wert in der gelben Zelle größer als eins ist, kann die Annahme als falsch angesehen werden – es besteht kein Zusammenhang zwischen Bildung und Konfliktverhalten.

Faktorenanalyse in Excel: ein Beispiel

Lassen Sie uns die Beziehung der Daten im Bereich des Verkaufs analysieren – es ist notwendig, beliebte und unbeliebte Produkte zu identifizieren. Erste Informationen:

1. Wir müssen herausfinden, für welche Waren die Nachfrage im zweiten Monat am stärksten gestiegen ist. Wir erstellen eine neue Tabelle, um das Wachstum und den Rückgang der Nachfrage zu ermitteln. Das Wachstum wird mit dieser Formel berechnet: =IF((Forderung 2-Forderung 1)>0; Forderung 2-Forderung 1;0). Formel verringern: =IF(Wachstum=0; Nachfrage 1- Nachfrage 2;0).

2. Berechnen Sie das Wachstum der Warennachfrage in Prozent: =IF(Wachstum/Ergebnis 2 =0; Abnahme/Ergebnis 2; Wachstum/Ergebnis 2).

3. Lassen Sie uns zur Verdeutlichung ein Diagramm erstellen – wählen Sie eine Reihe von Zellen aus und erstellen Sie ein Histogramm über die Registerkarte „Einfügen“. In den Einstellungen müssen Sie die Füllung entfernen, dies kann über das Werkzeug Datenreihen formatieren erfolgen.

Zwei-Wege-Varianzanalyse in Excel

Die Varianzanalyse wird mit mehreren Variablen durchgeführt. Betrachten Sie dies an einem Beispiel: Sie müssen herausfinden, wie schnell sich die Reaktion auf ein Geräusch unterschiedlicher Lautstärke bei Männern und Frauen manifestiert.

1. Wir öffnen „Datenanalyse“, in der Liste müssen Sie eine Zwei-Wege-Varianzanalyse ohne Wiederholungen finden.
2. Eingabeintervall – Zellen, die Daten enthalten (ohne Kopfzeile). Wir zeigen die Ergebnisse auf einem neuen Blatt an und klicken auf „OK“.

Der F-Wert ist größer als der F-Kritisch, was bedeutet, dass der Boden die Reaktionsgeschwindigkeit auf Schall beeinflusst.

Zusammenfassung

In diesem Artikel wurde ausführlich auf die Sensitivitätsanalyse in einer Excel-Tabelle eingegangen, damit jeder Benutzer die Methoden seiner Anwendung verstehen kann.