Simulationen mit Szenarien in Excel - Anleitung

Dieses Tutorium zeigt Ihnen, wie Sie ein einfaches Simulationsmodell, das eine Szenariovariable beinhaltet, in Excel mithilfe der Statistiksoftware XLSTAT einrichten und interpretieren.

Wie können Szenariovariablen und Statistiken in einem Simulationsmodell eingesetzt werden?

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.

Im Rahmen dieses Tutoriels wird dem schon bekannten einfachen Modell eine Szenariovariable und eine Statistik hinzugefügt.

Unser Simulationsmodell behandelt die Verkäufe und Kosten eines Geschäfts. Der Erfolg ergibt sich aus der Differenz zwischen Verkäufen und Kosten. Auf Basis von historischen Daten der Kosten und Verkäufen, die mittels des Tool „Anpassen an eine Verteilung“ analysiert wurden, wurde ermittelt, dass die Kosten einer Normalverteilung (mu=120, sigma=10) und die Verkäufe einer Normalverteilung (mu=80, sigma=20) folgen (siehe auch Tutoriel Anpassung eines Wahrscheinlichkeitsgesetzes an Daten für weitere Details).

Nun wird angenommen, dass 80% der Kosten und 30 % der Verkäufe von einem Wechselkurs abhängen. Der Wechselkurs wird zunächst mit 1 angenommen. Im Laufe der Tornadoanalyse werden verschiedene Szenarien von einem Wechselkurs von 0.8 bis 1.2 durchgespielt.

Verkäufe und Kosten werden wie im Tutorial Sim1 definiert. Die Formel des Erfolgs ist etwas komplizierter und Einbezug des Wechselkurses: = (0.3 * E2 * B2 + 0.7 *B2) -( 0.8 * E2* B3+ 0.2 * B3)

Ausgehend von diesem Modell fügen wir nun die verschiedenen Modellvariablen ein:

Dies finden Sie im Excel Blatt Modell.

Wählen Sie als aktive Zelle E2, diejenige die den Wert der Szenariovariable Wechselkurs enthält.

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

Nach dem Klicken des Buttons erscheint das entsprechende Dialogfenster der Definition einer Szenariovariablen. Sie können nun die Daten im Excel-Blatt auswählen. Wählen Sie die Excel-Zelle mit dem Inhalt Wechselkurs als Namen. Wählen Sie als untere Grenze die Zelle mit dem Inhalt 0.8 und als obere Grenze die Zelle mit dem Inhalt 1.2, um die Grenzen des Wechselkurses festzulegen. Wählen Sie den Datentyp kontinuierlich, da der Wechselkurs jeden Wert zwischen 0.8 und 1.2 annehmen kann.

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

Wählen Sie als aktive Zelle die Zelle rechts neben dem Text „Std.abw Erfolg“, um eine Statistik während der laufenden Simulation über die Standardabweichung der Ergebnisvariablen Erfolg zu berechnen. Diese Statistik wird während jeder Iteration berechnet. Wählen Sie den Befehl XLSTAT/Sim/Definition einer Statistik oder klicken Sie auf den entsprechenden Button in der Toolbar „Sim“.

Nach dem Klicken des Buttons erscheint das entsprechende Dialogfenster der Statistikdefinition. Sie können nun die Daten im Excel-Blatt auswählen. Wählen Sie die Excel-Zelle mit dem Inhalt Std.abw Erfolg als relativen Namen. Wählen Sie die Kategorie beschreibende Statistik aus und wählen Sie in der Liste Standardabweichung (n-1) aus.

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

Dies finden Sie im Excel Blatt Modell.

Das Modul Sim ermöglicht ebenfalls die Simulation im Einzelschrittmodus. Dabei können sie die ersten Modellschritte verfolgen und überprüfen ob Ihr Modell korrekt die Abhängigkeiten behandelt. Im diesem Beispiel können Sie die Wertentwicklung der Statistik verfolgen.

Um das Simulationsmodell zu initialisieren und um Information aus einer vorhergehenden Einzelschrittsimulation zu löschen, wählen Sie den Befehl XLSTAT/Sim/Neuinitialisierung der Simulation oder klicken Sie auf den entsprechenden Button in der Toolbar " Sim ".

Sie sehen bereits die Ergebnisse der ersten Simulationsiteration in den Zellen Kosten, Verkäufe und Erfolg. Die Entscheidungsvariable wird nur im Tornadomodus benützt und ändert hier Ihren Wert nicht. Die Statistik ist noch nicht berechnet, da erst mindestens zwei historische Werte vorliegen müssen, um die Standardabweichung zu berechnen.

Um weitere Simulationsschritte durchzuführen, wählen Sie den Befehl XLSTAT/Sim/Sim — Führe Simulationsschritt aus oder klicken Sie auf den entsprechenden Button in der Toolbar " Sim ".

Sie sehen wie sich die Statistik der Standardabweichung rasch gegen einen festen Wert konvergiert.

Um die Simulation nun zu starten, wählen Sie den Befehl XLSTAT/Sim/Sim — Start der Simulation oder klicken Sie auf den entsprechenden Button in der Toolbar " 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:

Im Reiter Ausgabe aktivieren Sie bitte die Option Simulationsdetails:

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

Die Ergebnisse entsprechen zunächst denen des Tutoriels Sim1.

Die Tornadoanalyse basiert 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.

In der Tornado-Analyse ist nun ebenfalls die Szenariovariable sichtbar. Ihr Einfluss ist auf dem gleichen Niveau wie die beiden übrigen Variablen Kosten und Verkäufe. Weitere Details ergeben sich im Spinnendiagramm. Der Wechselkurs ist klar negativ mit dem Erfolg korreliert. Ein steigender Wechselkurs mindert den Erfolg.

Abschließend werden am Ende nun die Simulationsdetails angezeigt. Man sieht für jede Variable des Modells Kosten, Verkäufe, Erfolg und Std. Abw Erfolg die Entwicklung in jeder Iteration. Man kann hier gut die Entwicklung der Statistik verfolgen und sehen, dass sie diese rasch dem Grenzwert 22 annähert und dann um diesen Wert herum schwankt.