Wie kann ich schnell viele Verteilungen in einem Simulationsmodell durch Kopieren erzeugen?

Eine Excel-Mappe mit den Daten und den Ergebnissen, die in diesem Tutoriel behandelt werden, kann hier heruntergeladen werden. Dieses Tutoriel baut auf den Ergebnissen des ersten Tutoriel zur Simulation auf und stellt die Bausteine „Entscheidungsvariable“ und „Statistik“ vor.

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. XLSTAT-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.

Im Rahmen dieses Tutoriels wird ein Simulationsmodell erstellt, das Zinzahlungen eines Infine-Kredites über 5 Jahre hinweg simuliert und zum Zinsatz des ersten Jahres bewertet. Hierbei wird das Kopieren von Verteilungsvariablen mittels des normalen Excel Kopieren und Einfügen vorgestellt.

Unser Simulationsmodell behandelt die Zinzahlungen eines Infine-Kredites. Während fünf Jahren werden die Zinsen berechnet. Abschliessend wird der Zeitwert zum Ausgangszeitpunkt mittels der Excelfunktion NPV aus dem Zinssatz des ersten Jahres und den Zinszahlungen der fünf Jahren berechnet. Der Zinssatz wird als gleichmäßig verteilt zwischen 3.5% und 5.5% angenommen. Das Kapital des Infinekredites beträgt 10000 Euro.

Gehen wir zunächst von dem statischen Modell aus mit einem mittleren Zinssatz von 4.5%. Der Zeitwert ergibt sich dann zu 1975 Euro.

Dies finden Sie im Excel Blatt Modell.

Im Folgenden werden relative Referenzen benötigt, um die Verteilungen kopieren zu können. Überprüfen Sie, dass die entsprechende Option in den Optionen der Simulation ausgewählt wurde.

Wählen Sie als aktive Zelle B6, die erste Verteilungsvariable, die den Wert des Zinssatz für 2008 enthält.

Nach dem Öffnen von XLSTAT, wählen Sie den Befehl XLSTAT/XLSTAT-SIM/Definition einer Verteilung oder klicken Sie auf den entsprechenden Button in der Toolbar " XLSTAT-SIM " (siehe unten).

Nach dem Klicken des Buttons erscheint das entsprechende Dialogfenster der Definition einer Verteilung. Sie können nun die Daten im Excel-Blatt auswählen. Wählen Sie die Excel-Zelle mit dem Inhalt 2008 als Namen aus. Er wird in der erzeugten Formel als relative Excel Zellreferenz in Form von B5 wiedergegeben. Als Verteilung wählen wir Uniforme Verteilung mit a = 0,035 und b = 0,055.

Nach dem Klicken des Buttons wird der entsprechende Aufruf auf die Funktion XLSTAT_SimDist in die aktive Zelle einfügt.

Sie können nun die weiteren vier Verteilungen durch Kopieren und einfügen der ersten in die vier Zellen, die sich rechts daneben befinden, erzeugen. Sie können ebenfalls wie für andere Excelformeln die eben erzeugte Zelle B6 markieren und mit der Maus auf die untere rechte Ecke zeigen, der Cursor wird dann zu einem schwarzen Kreuz. Nun drücken Sie die linke Maustaste und ziehen den Cursor bis zur Zelle F6. Auf diese Weise haben Sie ebenfalls alle 5 Zellen definiert. Die Namen der Verteilungen ergeben sich zu 2008, ..., 2012.

Wählen Sie als aktive Zelle das Ergebnis, das den Wert 1975 Euro und die Formel =NPV(B6,B7,C7,D7,E7,F7) enthält. Nun wird die Ergebnisvariable definiert. Wählen Sie den Befehl XLSTAT/XLSTAT-SIM/Definition eines Ergebnisses oder klicken Sie auf den entsprechenden Button in der Toolbar " XLSTAT-SIM ".

Nach dem Klicken des Buttons erscheint das entsprechende Dialogfenster der Ergebnisdefinition. Sie können nun die Daten im Excel-Blatt auswählen. Wählen Sie die Excel-Zelle mit dem Inhalt Zeitwert als Namen.

Nach dem Klicken des Buttons wird der entsprechende Aufruf auf die Funktion XLSTAT_SimRes in die aktive Zelle einfügt.

Dies finden Sie im Excel Blatt Modell.

Um die Simulation nun zu starten klicken, wählen Sie den Befehl XLSTAT/XLSTAT-SIM/Sim — Start der Simulation oder klicken Sie auf den entsprechenden Button in der Toolbar " XLSTAT-SIM ".

Nach dem Klicken des Buttons erscheint das entsprechende Dialogfenster der Simulation. Setzen Sie die Anzahl der Simulationen auf 1000.

Im Reiter Optionen wählen Sie die Parameter der Tornado- und Spinnenanalyse. Wählen Sie den zentralen Wert als Vorgabewert der Zelle. Wählen Sie 10 Datenpunkte im Intervall und wählen Sie ein Intervall von -10% bis +10% der Wertabweichung:

Die Berechnungen beginnen, sobald der Button "OK" geklickt wird.

Das erste Ergebnis ist eine Zusammenfassung des konstruierten Modells. Es sind Details der Verteilungs- und der Ergebnisvariablen zu finden.

Die folgenden Tabellen geben Einzelheiten über die Verteilungsvariablen. Es werden deskriptive Statistiken, ein Histogramm und Statistiken über die Intervall angegeben.

Die folgenden Tabellen geben die gleichen Einzelheiten über die Ergebnisvariable. Es werden deskriptive Statistiken, ein Histogramm und Statistiken über die Intervall angegeben. Anschließend sind die Ergebnisse der Sensitivitäts-Analyse zu sehen.

Es schließt sich nun die Tornado-Analyse an.

Tornado- und Spinnenanalyse basieren nicht auf den Iterationen der Simulation, sondern auf einer Punkt-für-Punkt Analyse aller Ausgangsvariablen (Zufallsvariablen mit Verteilungen und Szenariovariablen).

Während der Tornado-Analyse wird für jede Ergebnisvariable, jede zufällige Ausgangsvariable und jede Szenariovariable einzeln untersucht. Ihre Werte werden dabei zwischen zwei Grenzen variiert und die Werte der Ergebnisvariablen werden gespeichert, um herauszufinden, wie jede zufällige und Szenariovariable die Ergebnisvariable beeinflusst. Für eine zufällige Variable können die angenommenen Werte entweder um den Median oder um den Vorgabewert der Zelle variieren. Die Grenzen können mittels Perzentilen oder Abweichungen definiert werden. Für eine Szenariovariable, wird die Analyse zwischen zwei bei der Definition der Szenariovariablen definierten Grenzen variiert. Die Anzahl der Punkte ist eine Option, die vom Benutzer vor Ausführen des Simulationsmodells bestimmt werden kann.

Die Spinnenanalyse stellt nicht nur die maximale und minimale Veränderung des Ergebnisses dar, sondern für jeden Einzelwert der zufälligen Verteilungsvariablen und der Szenariovariablen wird der zugehörige Wert des Ergebnisses angezeigt. Dies ist insbesondere nützlich, um zu überprüfen ob die Abhängigkeiten zwischen den Verteilungen und Ergebnissen monotoner Natur sind.

In einer Tabelle werden für die Verteilungen jeweils die maximale und minimale Veränderung des Ergebnisses und die sich daraus ergebende Bandbreite angezeigt. Hier sind alle Zinssätze in etwa gleichwertig. In der Spinnen-Analyse im Anschluss sieht man, dass der Zinssatz des ersten Jahres einen etwas geringen Einfluss hat als die übrigen, da er ebenfalls in der Formel des NPV verwendet wird, so dass sich die Variationen nicht auswirken.

Klicken Sie hier, um zu den übrigen Einführungen zu gelangen.