Mehrere Verteilungen in einem Simulationsmodell durch Kopieren erstellen

Dieses Tutorium zeigt Ihnen, wie Sie in Excel mithilfe der Statistiksoftware XLSTAT ein Simulationsmodell einrichten und viele Verteilungen generieren.

Was sind Simulationsmodelle?

Simulationsmodelle erlauben es Informationen wie Mittelwert oder Median über Variablen zu erhalten, deren Werte nicht genau bekannt sind deren Verteilung jedoch bekannt oder geschätzt werden kann. Wenn einige „Ergebnis-Variablen“ von den „Verteilungs-Variablen“ durch eine genau bekannte oder unterstellte Formel abhängig sind, dann haben diese „Ergebnis-Variablen“ ebenfalls eine Verteilung. Sim erlaubt es Verteilungen zu definieren und dann mittels Simulation und eine empirische Verteilung der Ausgangs- und Ergebnisvariablen als auch über die zugehörigen Statistiken zu erhalten.

Simulationsmodelle finden heute Anwendung in vielen Gebieten wie Finanz und Versicherung, Medizin, Öl- und Gasprospektion, Buchhaltung oder Absatzplanung.

Zu Erstellung eines Simulationsmodells stehen Ihnen in XLSTAT die folgenden vier Bausteine zur Verfügung:

Verteilungen sind Zufallsvariablen zugeordnet. XLSTAT gibt einen die Wahl zwischen mehr als 20 Verteilungen, um die Ungewissheit der Werte, die die Variable annehmen kann, zu beschreiben (siehe Kapitel Definition einer Verteilung für mehr Details). Beispielsweise kann eine Trianguläre Verteilung gewählt werden, falls bekannt ist, dass die Werte zwischen zwei Grenzen variieren und ein wahrscheinlichster Wert (Modus) existiert. In jeder Iteration der Berechnung des Simulationsmodells wird eine zufällige Ziehung für jede definierte Verteilung durchgeführt.

Szenariovariablen erlauben das Modellieren eines Parameters der während des Simulationsmodells fix ist, außer während der Tornado- und Spinnenanalyse, in der sie zwischen zwei Grenzen variieren kann.

Ergebnisvariablen entsprechen den Ergebnissen oder Outputs des Modells. Sie hängen entweder direkt oder indirekt mittels einer oder mehreren Excelformeln von den Zufallsvariablen ab, denen Verteilungen zugeordnet sind, und falls vorhanden ebenfalls von Szenariovariablen. Das Ziel der Berechnungen des Simulationsmodells ist es die Verteilung der Ergebnisvariablen zu.

Statistiken erlauben das Verfolgen einer vorgegebenen Statistik einer Ergebnisvariablen. Zum Beispiel kann die Standardabweichung einer Ergebnisvariablen verfolgt werden.

Ein sinnvolles Modell sollte mindestens eine Verteilung und ein Ergebnis enthalten. Modelle können beliebig viele dieser Bausteine enthalten.

Sie können ein Modell entweder auf ein Excelblatt beschränken oder die gesamte Excelmappe benutzen.

Datensatz zur effizienten Generierung vieler Verteilungen in einem Simulationsmodell

Wir beginnen mit einem statischen Modell mit einem durchschnittlichen Zinssatz von 4,5 %. Der Netto-Gewinnwert wird in diesem Fall mit 1975 Euro berechnet.

Dieses Modell finden Sie im Tabellenblatt „Modell“.

Generierung vieler Verteilungen in einem Simulationsmodell durch Kopieren

Im Folgenden verwenden wir relative Referenzen zum korrekten Kopieren der Verteilungen. Bitte überprüfen Sie in den Sim-Optionen, ob die Option Relative Referenz aktiviert wurde, bevor Sie die Verteilungsvariablen erstellen. Auf diese Weise ist es möglich, dass die Aktionen Kopieren/Einfügen die Referenz automatisch ändern.

Erstellen der ersten Verteilung

Wählen Sie die erste Verteilungsvariable in B6, den „Zinssatz“ für 2008, um diese Zelle als aktive Zelle zu nutzen.

Nach dem Aktivieren von XLSTAT wählen Sie den Befehl XLSTAT/Sim/Definition einer Verteilung oder klicken Sie auf den entsprechenden Button der Symbolleiste Sim (siehe unten).

Es wird angezeigt. Wählen Sie die Excel-Zelle mit dem Namen „2008” als Name. Dies wird als relative Referenz (Format A1) in die Formel integriert.

Wählen Sie eine einheitliche Verteilung mit a = 0,035 und b = 0,055.

Sobald Sie auf OK geklickt haben, wird der entsprechende Funktionsaufruf von XLSTAT_SimDist in die aktive Zelle eingefügt.

Erstellen einer Verteilung durch Kopieren

Es besteht die Möglichkeit, die anderen vier Verteilungen mithilfe von Kopieren und Einfügen der generierten Zelle in die vier Zellen rechts von der ersten Zelle einzugeben. Sie haben auch die Möglichkeit, wie bei jeder anderen Excel-Formel die Zelle B6 auszuwählen, die Sie soeben generiert haben, die Maus über die untere linke Ecke zu bewegen, wo der Cursor als schwarzes Kreuz angezeigt wird, die linke Maustaste zu drücken und zu halten und die Maus bis zur Zelle F6 zu bewegen. Auf diese Weise haben Sie auch die 5 Zellen definiert. Der Name der Verteilung wird lauten „2008, …, 2012”.

Wählen Sie die Ergebniszelle B9, die die Formel NPV(B6,B7,C7,D7,E7,F7) enthält, als aktive Zelle. Jetzt wird die Ergebnisvariable definiert. Wählen Sie den Befehl XLSTAT/Sim/Definition eines Ergebnisses oder klicken Sie auf den entsprechenden Button der Symbolleiste Sim.

Das Dialogfenster „Ergebnis definieren“ wird angezeigt. Danach markieren Sie die Daten in dem Excel-Tabellenblatt. Wählen Sie die Excel-Zelle mit dem Namen „NPV” als Name.

Sobald Sie auf OK geklickt haben, wird der entsprechende Funktionsaufruf von XLSTAT_SimRes in die aktive Zelle eingefügt.

Diesen finden Sie im Excel Tabellenblatt-Modell.

Ausführen der Simulation

Wählen Sie den Befehl XLSTAT/Sim/Simulation - Start der Berechnungen oder klicken Sie auf den entsprechenden Button der Symbolleiste Sim.

Das Dialogfenster „Simulation - Start der Berechnungen“ wird angezeigt. Stellen Sie die Anzahl der Simulationen auf 1000.

In der Registerkarte Optionen geben Sie die Parameter der Tornado- und Spinnenanalyse ein. Wählen Sie den Standard-Zellenwert als Vorgabewert. Wählen Sie 10 Datenpunkte im Intervall von -10 % bis +10 % der Wertabweichung:

Die Berechnungen beginnen, sobald sie auf OK geklickt haben.

Interpretieren der Ergebnisse der Simulation

Das erste Ergebnis ist eine Zusammenfassung der im Modell enthaltenen Elemente. Details zu den Verteilungsvariablen und zu der Ergebnisvariablen werden angezeigt.

Die folgenden Tabellen zeigen deskriptive Statistiken, Histogramme und Quantile für jede Verteilungsvariable an.

Die folgenden Tabellen zeigen Details für die Ergebnisvariablen an (deskriptive Statistiken, Histogramme und Quantile). Danach werden die Ergebnisse der Sensitivitätsanalyse dargestellt. Diese Ergebnisse hängen von den Iterationen der Simulationen ab.

Der nächste Abschnitt enthält die Tornado-Analyse. Tornado- und Spinnenanalysen basieren nicht auf den Iterationen der Simulation, sondern auf einer Punkt-zu-Punkt-Analyse aller Eingangsvariablen (Zufallsvariablen mit Verteilungen und Szenariovariablen).

Während der Tornado-Analyse werden für jede Ergebnisvariable, jede Zufällige Eingangsvariable und jede Szenariovariable nacheinander untersucht. Wir können ihren Wert zwischen zwei Grenzen variieren lassen und den Wert der Ergebnisvariablen erfassen, um zu wissen, wie jede Zufalls- und Szenariovariable sich auf die Ergebnisvariablen auswirkt. Für eine Zufallsvariable können die untersuchten Werte entweder um den Median oder um den Standard-Zellwert liegen, wobei die Grenzen durch Perzentile oder Abweichung definiert werden. Für eine Szenariovariable wird die Analyse zwischen zwei Grenzen durchgeführt, die bei der Definition der Variablen festgelegt werden. Die Anzahl der Punkte ist eine Option, die vom Benutzer vor dem Ausführen des Simulationsmodells modifiziert werden kann.

Die Spinnenanalyse zeigt nicht nur die maximale und minimale Änderung der Ergebnisvariablen an, sondern auch den Wert der Ergebnisvariablen für jeden Datenpunkt der Zufalls- und Szenariovariablen. Dies ist nützlich, um zu überprüfen, ob die Abhängigkeit zwischen Verteilungsvariablen und Ergebnisvariablen monoton ist oder nicht.

In der ersten Tabelle werden die minimale und maximale Änderung und der entsprechende Bereich für jede Verteilungsvariable angezeigt. In diesem Fall sind alle Zinssätze mehr oder weniger identisch. In der Spinnenanalyse im nächsten Abschnitt sehen wir, dass der Zinssatz des ersten Jahres sich weniger auswirkt als die anderen Zinssätze, da in der Formel des NPV der Zinssatz des ersten Jahres ebenfalls verwendet wird und daher haben Abweichungen dieses Zinssatzes nicht viel Einfluss auf die NPV.