Erstellen einer Monte-Carlo-Simulation mit Excel

Eine Monte-Carlo-Simulation kann mit Microsoft Excel und einem Würfelspiel entwickelt werden. Bei der Monte-Carlo-Simulation handelt es sich um eine mathematisch-numerische Methode, bei der mithilfe von Zufallszügen Berechnungen und komplexe Probleme ausgeführt werden. Heute ist es weit verbreitet und spielt eine Schlüsselrolle in verschiedenen Bereichen wie Finanzen, Physik, Chemie und Wirtschaft.

Monte-Carlo-Simulation

Die Monte-Carlo-Methode wurde 1947 von Nicolas Metropolis erfunden und versucht, komplexe Probleme mit zufälligen und probabilistischen Methoden zu lösen. Der Begriff "Monte Carlo" stammt aus dem Verwaltungsgebiet von Monaco, das im Volksmund als Spielstätte der europäischen Eliten bekannt ist. Wir verwenden die Monte-Carlo-Methode, wenn das Problem zu komplex und durch direkte Berechnung schwer zu lösen ist. Eine große Anzahl von Iterationen ermöglicht eine Simulation der Normalverteilung.

Die Monte-Carlo-Simulationsmethode berechnet die Wahrscheinlichkeiten für Integrale und löst partielle Differentialgleichungen, wodurch ein statistischer Ansatz für das Risiko in einer probabilistischen Entscheidung eingeführt wird. Obwohl für die Erstellung von Monte-Carlo-Simulationen viele fortschrittliche statistische Tools existieren, ist es einfacher, das normale Gesetz und das einheitliche Gesetz mit Microsoft Excel zu simulieren und die mathematischen Grundlagen zu umgehen.

Für die Monte-Carlo-Simulation isolieren wir eine Reihe von Schlüsselvariablen, die das Ergebnis des Experiments steuern und beschreiben, und weisen dann eine Wahrscheinlichkeitsverteilung zu, nachdem eine große Anzahl von Zufallsstichproben durchgeführt wurde. Nehmen wir ein Würfelspiel als Vorbild.

Würfelspiel

So rollt das Würfelspiel:

• Der Spieler wirft drei Würfel mit 6 Seiten dreimal.

• Wenn die Summe der 3 Würfe 7 oder 11 beträgt, gewinnt der Spieler.

• Wenn die Summe der 3 Würfe 3, 4, 5, 16, 17 oder 18 beträgt, verliert der Spieler.

• Wenn die Summe ein anderes Ergebnis ergibt, spielt der Spieler erneut und würfelt erneut.

• Wenn der Spieler erneut würfelt, wird das Spiel auf die gleiche Weise fortgesetzt, mit der Ausnahme, dass der Spieler gewinnt, wenn die Gesamtsumme der in der ersten Runde bestimmten Summe entspricht.

Es wird auch empfohlen, eine Datentabelle zu verwenden, um die Ergebnisse zu generieren. Darüber hinaus sind 5.000 Ergebnisse erforderlich, um die Monte-Carlo-Simulation vorzubereiten.

Schritt 1: Würfelwürfe

Zunächst entwickeln wir eine Reihe von Daten mit den Ergebnissen der drei Würfel für 50 Würfe. Dazu wird vorgeschlagen, die Funktion "RANDBETWEEN (1, 6)" zu verwenden. So erzeugen wir jedes Mal, wenn wir auf F9 klicken, einen neuen Satz von Wurfergebnissen. Die Zelle "Ergebnis" ist die Gesamtsumme der Ergebnisse aus den 3 Würfeln.

Schritt 2: Bandbreite der Ergebnisse

Dann müssen wir eine Reihe von Daten entwickeln, um die möglichen Ergebnisse für die erste Runde und die nachfolgenden Runden zu ermitteln. Es gibt einen 3-spaltigen Datenbereich. In der ersten Spalte haben wir die Zahlen 1 bis 18. Diese Zahlen repräsentieren die möglichen Ergebnisse nach dreimaligem Würfeln: Das Maximum ist 3 * 6 = 18. Sie werden feststellen, dass für die Zellen 1 und 2 die Ergebnisse N / A sind, da es unmöglich ist, mit 3 Würfeln eine 1 oder eine 2 zu erhalten. Das Minimum ist 3.

In der zweiten Spalte sind die möglichen Schlussfolgerungen nach der ersten Runde enthalten. Wie in der Ausgangsaussage angegeben, gewinnt (Win) oder verliert (Lose) der Spieler, oder er wiederholt (Re-Roll), je nach Ergebnis (insgesamt 3 Würfelwürfe).

In der dritten Spalte werden die möglichen Schlussfolgerungen für nachfolgende Runden registriert. Diese Ergebnisse können wir mit der Funktion "IF" erzielen. Dies stellt sicher, dass, wenn das erzielte Ergebnis dem Ergebnis der ersten Runde entspricht, wir gewinnen. Andernfalls befolgen wir die anfänglichen Regeln des ursprünglichen Spiels, um zu bestimmen, ob wir die Würfel erneut würfeln.

Schritt 3: Schlussfolgerungen

In diesem Schritt identifizieren wir das Ergebnis der 50 Würfelwürfe. Die erste Schlussfolgerung kann mit einer Indexfunktion erzielt werden. Diese Funktion durchsucht die möglichen Ergebnisse der ersten Runde, wobei die Schlussfolgerung dem erhaltenen Ergebnis entspricht. Wenn wir zum Beispiel 6 bekommen, spielen wir erneut.

Man kann die Ergebnisse anderer Würfelwürfe erhalten, indem man eine "OR" -Funktion und eine Indexfunktion verwendet, die in einer "IF" -Funktion verschachtelt sind. Diese Funktion teilt Excel mit: "Wenn das vorherige Ergebnis Gewinn oder Verlust ist", hören Sie auf, die Würfel zu werfen, da wir fertig sind, sobald wir gewonnen oder verloren haben. Ansonsten gehen wir in die Spalte der folgenden möglichen Schlussfolgerungen und identifizieren die Schlussfolgerung des Ergebnisses.

Schritt 4: Anzahl der Würfelwürfe

Nun bestimmen wir die Anzahl der benötigten Würfel, bevor wir verlieren oder gewinnen. Dazu können wir eine "COUNTIF" -Funktion verwenden, bei der Excel die Ergebnisse von "Re-Roll" zählt und die Zahl 1 hinzufügt. Es fügt eins hinzu, weil wir eine Extrarunde haben und wir ein Endergebnis erhalten (gewinnen oder verlieren).

Schritt 5: Simulation

Wir entwickeln eine Reihe, um die Ergebnisse verschiedener Simulationen zu verfolgen. Dazu erstellen wir drei Spalten. In der ersten Spalte ist eine der Zahlen 5.000 enthalten. In der zweiten Spalte sehen wir uns das Ergebnis nach 50 Würfeln an. In der dritten Spalte, dem Titel der Spalte, werden wir nach der Anzahl der Würfel suchen, bevor wir den endgültigen Status erhalten (gewinnen oder verlieren).

Anschließend erstellen wir eine Sensitivitätsanalysetabelle unter Verwendung der Feature-Daten oder der Tabelle mit Tabellendaten (diese Sensitivität wird in die zweite Tabelle und die dritte Spalte eingefügt). Bei dieser Empfindlichkeitsanalyse muss die Anzahl der Ereignisse von 1 bis 5.000 in die Zelle A1 der Datei eingefügt werden. Tatsächlich könnte man jede leere Zelle auswählen. Die Idee ist einfach, jedes Mal eine Neuberechnung zu erzwingen und so neue Würfelwürfe (Ergebnisse neuer Simulationen) zu erhalten, ohne die Formeln an Ort und Stelle zu beschädigen.

Schritt 6: Wahrscheinlichkeit

Wir können endlich die Gewinn- und Verlustwahrscheinlichkeiten berechnen. Wir tun dies mit der Funktion "COUNTIF". Die Formel zählt die Anzahl von "Gewinnen" und "Verlieren" und dividiert dann durch die Gesamtanzahl von Ereignissen (5.000), um den jeweiligen Anteil des einen und des anderen zu erhalten. Wir sehen schließlich, dass die Wahrscheinlichkeit, ein Win-Ergebnis zu erzielen, 73, 2% beträgt, und dass ein Lose-Ergebnis daher 26, 8% beträgt.