Ihre Datenanalyse Lösung

Einfache Simulationsmodelle in Excel - Anleitung

20/10/2017

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

Eine Excel-Mappe mit den Daten und den Ergebnissen, die in diesem Tutoriel behandelt werden, kann hier heruntergeladen werden.

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.

Im Rahmen dieses Tutoriels wird ein sehr einfaches Simulationsmodell erstellt mit zwei zufälligen Eingangsvariablen und einer Ergebnisvariablen, um die Grundlagen der Simulationsmodelle darzustellen. Weitere Tutoriels mit allen 4 Modellelementen und Optionen finden Sie hinter folgenden Links: Sim2, Sim3, and Sim4.

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

Gehen wir zunächst von dem statischen Modell aus mit Verkäufen von 120 und Kosten in Höhe von 80. Der Erfolg ergibt sich als Differenz zu 40. Ausgehend von diesem statischen Modell fügen wir nun die verschiedenen Modellvariablen ein:

sim101d.gif

Dies finden Sie im Excel Blatt Statisch.

Wählen Sie als aktive Zelle die erste Verteilungsvariable, die den Wert 120 als Betrag der Verkäufe enthält.

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

barsim1d.gif

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 „Verkäufe“ als Namen. Als Verteilung wählen wir Normalverteilung mit mu = 120 und sigma = 10.

sim102d.gif

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

Sie können nun die zweite Verteilung für die Kosten analog vornehmen. Als Verteilung wählen wir Normalverteilung mit mu = 80 und sigma = 20. Hier das zugehörige Auswahlfeld:

sim103d.gif

Wählen Sie als aktive Zelle die Ergebnisvariable, die den Wert 40 und die Formel =B2-B3 enthält. Nun wird die Ergebnisvariable definiert. Wählen Sie den Befehl XLSTAT/Sim/Definition eines Ergebnisses oder klicken Sie auf den entsprechenden Button in der Toolbar " 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 „Erfolg“ als Namen.

sim104d.gif

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/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. Sie können nun die Daten im Excel-Blatt auswählen. Setzen Sie die Anzahl der Simulationen auf 1000.

sim105d.gif

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:

sim106d.gif

Die Berechnungen beginnen, sobald der Button "OK" geklickt wird. Falls Sie in den Optionen von XLSTAT die Option „Auswahl bestätigen lassen“ aktiviert haben, so bittet Sie XLSTAT die Anzahl der Zeilen und der Spalten der Auswahlen zu bestätigen.

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

sim107d.gif

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

sim108d.gifsim109d.gif

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

sim110d.gif

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

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 einer Tabelle werden für die Verteilungen jeweils die maximale und minimale Veränderung des Ergebnisses und die sich daraus ergebende Bandbreite angezeigt. Im folgenden Diagramm wird sichtbar, dass die Kosten den stärksten Einfluss auf die Entwicklung des Erfolgs haben. Diese Ergebnisse basieren nicht auf den gesamten Simulationsiterationen.

sim111d.gif

Abschließend wird die Korrelationsmatrix der verschiedenen Verteilungs- und Ergebnisvariablen angezeigt. Man sieht, dass Kosten und Verkäufe nicht korreliert sind. Jedoch der Erfolg mit Kosten und Verkäufen korreliert.

sim112d.gif

Kontakt

E-Mail an Vertrieb

Kontaktieren Sie unseren Support-Team: support@xlstat.com

https://cdn.desk.com/
false
desk
Laden
Sekunden her
eine Minute her
Minuten her
eine Stunde her
Stunden her
einen Tag her
Tage her
über
false
Ungültige Zeichen gefunden
/customer/portal/articles/autocomplete
9283