Kurs
Monte-Carlo-Simulation in Excel: Ein vollständiger Leitfaden
Monte-Carlo-Methoden, die ursprünglich nach dem Monte-Carlo-Kasino in Monaco benannt wurden, werden häufig in Bereichen wie Finanzen, Ingenieurwesen, Lieferketten und Wissenschaft eingesetzt, um Phänomene zu modellieren, deren Inputs mit großer Unsicherheit behaftet sind.
Aber was ist eine Monte-Carlo-Simulation? Wie funktioniert das? Und wie kann ich die Simulation durchführen und die Ergebnisse analysieren?
In diesem Tutorium lernst du die Monte-Carlo-Simulation und die relevanten statistischen Konzepte hinter dieser Technik kennen. Außerdem werden wir die Monte-Carlo-Simulation in Excel implementieren und dich mit den relevanten integrierten Excel-Funktionen vertraut machen.
Am Ende des Tutorials erhältst du bewährte Verfahren, fortgeschrittene Techniken und weitere Ressourcen, sodass du in diesem Tutorial alles über die Monte-Carlo-Simulation in Microsoft Excel aus einer Hand erfährst.
Was ist eine Monte-Carlo-Simulation?
Die Monte-Carlo-Simulation ist eine mathematische Technik, die verwendet wird, um die Wahrscheinlichkeit verschiedener Ergebnisse in einem Prozess zu modellieren, der aufgrund des Eingreifens von Zufallsvariablen nicht einfach vorhergesagt werden kann.
Es ist ein mächtiges Werkzeug, um die Auswirkungen von Risiko und Unsicherheit in verschiedenen Bereichen zu verstehen. Die Methode beruht auf wiederholten Stichproben, um das Verhalten komplexer Systeme und Prozesse zu simulieren.
Das Problem wird zunächst durch eine Wahrscheinlichkeitsverteilung für jede Variable modelliert, die eine inhärente Unsicherheit aufweist. Aus diesen Wahrscheinlichkeitsverteilungen werden dann eine große Anzahl von Stichproben gezogen, die zur Berechnung der Ergebnisse verwendet werden. Dieser Prozess wird viele Male wiederholt, um eine Verteilung möglicher Ergebnisse zu erstellen, die statistisch ausgewertet werden können, um Vorhersagen darüber zu treffen, wie sich ein System verhalten wird.
Also, ganz einfach ausgedrückt. Die Monte-Carlo-Simulation ist eine Technik, die vorhersagt, wie sich komplexe Systeme verhalten werden, indem ihre Ergebnisse viele Male mit Zufallswerten simuliert werden. Sie erfolgt in mehreren Schritten:
- Modellunsicherheit: Definiere, wie jede Variable mithilfe von Wahrscheinlichkeitsverteilungen variieren kann.
- Zufallsstichproben: Wähle zufällig Werte für diese Variablen auf der Grundlage ihrer Verteilungen aus.
- Simuliere die Ergebnisse: Verwende diese Werte, um das Verhalten des Systems zu simulieren.
- Analysiere die Ergebnisse: Wiederhole den Prozess viele Male, um eine Reihe möglicher Ergebnisse zu erhalten, und analysiere diese dann, um die wahrscheinlichsten Szenarien vorherzusagen.
Als Nächstes werden wir unser Grundverständnis der Monte-Carlo-Simulation vertiefen, indem wir uns mit einigen relevanten statistischen Konzepten beschäftigen.
Verstehen von Zufallsvariablen und Wahrscheinlichkeitsverteilungen
Zufallsvariablen und die dazugehörigen Wahrscheinlichkeitsverteilungen sind für die Monte-Carlo-Simulation von grundlegender Bedeutung, da sie den mathematischen Rahmen für die Modellierung und Simulation der Zufälligkeit und Variabilität komplexer Systeme bilden.
Zufallsvariablen
Eine Zufallsvariable ist eine Variable, deren Werte das Ergebnis eines zufälligen Ereignisses sind.
Zufallsvariablen werden in zwei Typen unterteilt:
- Diskrete Zufallsvariablen: Diese Variablen nehmen eine abzählbare Anzahl verschiedener Werte an. In Simulationen können diskrete Variablen Szenarien wie die Anzahl der fehlerhaften Artikel in einer Charge, Kundenankünfte pro Stunde oder andere zählbare Ereignisse modellieren.
- Kontinuierliche Zufallsvariablen: Diese Variablen können beliebige Werte in einem kontinuierlichen Bereich annehmen. Kontinuierliche Variablen werden für Simulationen verwendet, die sich mit physikalischen Messungen oder Zeitdauern beschäftigen.
Zufallsvariablen werden in Simulationen verwendet, weil sie die Unsicherheit enthalten, die mit Monte-Carlo-Techniken untersucht und quantifiziert werden soll.
Wahrscheinlichkeitsverteilungen
Wahrscheinlichkeitsverteilungen beschreiben, wie die Wahrscheinlichkeiten auf die Werte einer Zufallsvariablen verteilt sind.
Wahrscheinlichkeitsverteilungen werden in der Monte-Carlo-Simulation verwendet, um zu definieren, wie sich verschiedene Eingaben oder Szenarien voraussichtlich verhalten werden. Dies ist für eine genaue Modellierung und Entscheidungsfindung unerlässlich.
Die Normalverteilung ist die am häufigsten verwendete Verteilung in der Statistik und in Simulationen, da viele natürliche und vom Menschen verursachte Phänomene aufgrund des zentralen Grenzwertsatzes dazu neigen, dieser Verteilung zu folgen.
Normalverteilung(Quelle)
Die Normalverteilung wird für die Modellierung von Variablen verwendet, die von vielen kleinen, unabhängigen Effekten beeinflusst werden, wie z. B. Messfehler oder Börsenrenditen.
Einige andere Wahrscheinlichkeitsverteilungen sind Gleichverteilungen, die verwendet werden, wenn jedes Ergebnis in einem bestimmten Bereich gleich wahrscheinlich ist - eine gängige Annahme bei Simulationen, wenn keine vorherigen Daten verfügbar sind, und Binomialverteilungen, die bei der Modellierung von Szenarien mit zwei möglichen Ergebnissen (Erfolg/Misserfolg) in einer Reihe von Experimenten verwendet werden, z. B. bei Pass/Fail-Tests oder Qualitätskontrollen.
Nachdem wir nun die Konzepte und die Theorie hinter den Monte-Carlo-Simulationen verstanden haben, wollen wir uns der Umsetzung zuwenden.
Excel-Grundlagen lernen
Warum Excel für Monte-Carlo-Simulationen verwenden?
Wenn du dich für eine Monte-Carlo-Simulation entschieden hast, stehen dir verschiedene Tools wie Excel, Python, R, SAS und MATLAB zur Verfügung, die dich bei den Simulationen unterstützen.
Der wichtigste Faktor, den du berücksichtigen musst, wenn du die Monte-Carlo-Simulation zum ersten Mal durchführst, ist, dass du dich mit dem Tool auskennst. Excel ist eines der am häufigsten genutzten Tools in der Geschäftswelt, was bedeutet, dass viele Menschen bereits mit seinen grundlegenden Funktionen vertraut sind. Dadurch wird die Einarbeitungszeit verkürzt und die Notwendigkeit, eine neue Software von Grund auf zu erlernen, entfällt.
Excel bietet außerdem benutzerfreundliche Werkzeuge zum Erstellen von Diagrammen und Grafiken, die für die Visualisierung der Ergebnisse von Simulationen nützlich sein können. Darüber hinaus sind für Excel mehrere leistungsstarke Add-Ins verfügbar, die die Fähigkeit zur Durchführung komplexer Monte-Carlo-Simulationen verbessern.
Für fortgeschrittene Simulationen, insbesondere für solche, die den Umgang mit großen Datensätzen oder die Durchführung einer sehr großen Anzahl von Simulationen erfordern, sind andere spezialisierte Tools als Excel möglicherweise besser geeignet.
Überblick über relevante Excel-Funktionen
Als Nächstes werden wir uns zwei wichtige Excel-Funktionen ansehen: RAND und NORM.INV. Wir erklären ihre Syntax, Parameter und typischen Anwendungsfälle. Diese Funktionen helfen dabei, Zufallszahlen zu erzeugen und Wahrscheinlichkeitsverteilungen zu definieren, die grundlegende Aspekte jeder Simulation sind.
Die Syntax und Parameter für die RAND-Funktion
RAND erzeugt eine Zufallszahl, die größer als oder gleich 0 und kleiner als 1 ist. Die Zahlen sind gleichmäßig verteilt, das heißt, dass jede Zahl innerhalb des angegebenen Bereichs mit gleicher Wahrscheinlichkeit vorkommt.
Die Syntax für RAND lautet wie folgt:
RAND()
Die Funktion RAND
benötigt keine Argumente. Es wird einfach als RAND()
verwendet.
Im Zusammenhang mit der Monte-Carlo-Simulation kann RAND()
verwendet werden, um das Auftreten von Zufallsereignissen zu simulieren oder um die Eingaben in dein Modell zu variieren.
Die Syntax und Parameter für die Funktion NORM.INV
Während RAND gleichmäßige Zufallszahlen erzeugt, wird NORM.INV verwendet, um Zufallszahlen aus einer Normalverteilung zu generieren, was eine häufige Anforderung in einer Monte-Carlo-Simulation ist. Diese Funktion liefert die Umkehrung der kumulativen Normalverteilung für einen bestimmten Mittelwert und eine bestimmte Standardabweichung.
Die Syntax für die Funktion NORM.INV lautet wie folgt:
NORM.INV(probability, mean, standard_deviation)
Die Parameter sind:
- wahrscheinlichkeit: Eine Wahrscheinlichkeit, die der Normalverteilung entspricht und die einen Wert zwischen 0 und 1 haben muss. Diese wird in der Regel durch die Funktion
RAND()
erzeugt. - bedeuten: Das arithmetische Mittel der Normalverteilung.
- standard_deviation: Die Standardabweichung der Normalverteilung, ein Maß dafür, wie weit die Zahlen um den Mittelwert herum gestreut sind.
Die NORM.INV
wird verwendet, um gleichmäßig verteilte Zufallszahlen aus der Funktion RAND
in Zahlen umzuwandeln, die einer bestimmten Normalverteilung folgen. Dies ist nützlich für die Modellierung von Variablen, von denen erwartet wird, dass sie eine natürliche Variabilität aufweisen, die einer Normalkurve folgt.
Nachdem wir nun alle Bausteine, Funktionen und Konzepte hinter einer Monte-Carlo-Simulation kennen, wollen wir sie in Microsoft Excel implementieren.
Implementierung der Monte-Carlo-Simulation in Microsoft Excel: Ein Beispiel
Stell dir ein Szenario vor, in dem du als Datenanalyst in einem dynamischen Unternehmen der Unterhaltungselektronik arbeitest und damit beauftragt wirst, die finanzielle Tragfähigkeit der Markteinführung eines neuen tragbaren Fitness-Trackers zu bewerten.
Der Markt für solche Geräte ist hart umkämpft und die Verbrauchernachfrage kann stark schwanken, beeinflusst von saisonalen Trends, Marketingeffizienz und Aktionen der Konkurrenz. Außerdem unterliegen die Kosten für die Herstellung dieser Geräte Schwankungen aufgrund von Änderungen der Materialkosten und Unsicherheiten in der Lieferkette.
Du hast dich entschieden, die Monte-Carlo-Simulation in Excel einzusetzen, um diese Herausforderungen zu meistern. Du glaubst, dass dieser Ansatz dir helfen wird, die potenzielle Rentabilität unter verschiedenen Szenarien abzuschätzen, damit das Unternehmen gut informierte Entscheidungen über Preisstrategien, Produktionsmengen und Marketinginvestitionen treffen kann.
Du hast auch frühere Daten von ähnlichen Produkteinführungen und Marktstudien in der Unterhaltungselektronikbranche analysiert. Aus dieser Analyse hast du bestimmte Kennzahlen abgeleitet, die in deine Simulation einfließen werden:
- Eine durchschnittliche Nachfrage von 10.000 Einheiten für neue Geräte innerhalb des ersten Jahres nach der Markteinführung, mit einer Standardabweichung von 2.000 Einheiten, was die Unsicherheit bei der Verbraucherakzeptanz widerspiegelt.
- Der Verkaufspreis pro Einheit liegt in der Regel zwischen 50 und 70 US-Dollar, je nach Wettbewerbspreisen und Marktsättigung.
- Die Stückkosten, die von schwankenden Materialpreisen und der Produktionseffizienz beeinflusst werden, liegen im Durchschnitt bei 30 USD pro Stück mit einer Standardabweichung von 5 USD.
Diese historischen Daten bilden die Grundlage für deine Simulationsparameter und helfen dabei, die Simulation so zu gestalten, dass sie die aktuellen Marktbedingungen genauer widerspiegelt.
Die Schritte, die du befolgen könntest, um die Monte-Carlo-Simulation für dieses Beispiel durchzuführen, sind die folgenden:
Schritt 1: Richte deine Excel-Tabelle ein
Bereite zunächst dein Excel-Arbeitsblatt so vor, dass es Spalten für jede Variable und eine Spalte für den berechneten Gewinn enthält.
So würde es anfangs aussehen:
Einrichten der Excel-Tabelle.
Schritt 2: Eingabeformeln für Variablen
In jeder Zeile gibst du Formeln ein, um zufällige Werte für die Nachfrage, den Verkaufspreis und die Kosten zu generieren, die auf den von dir ermittelten Verteilungen basieren:
- Nachfrage: Normalverteilung (Mittelwert = 10.000 Einheiten, Standardabweichung = 2.000 Einheiten)
- Verkaufspreis: Einheitliche Verteilung ($50 bis $70)
- Kosten: Normalverteilung (Mittelwert = $30, Standardabweichung = $5)
Um diese Formeln nacheinander einzugeben, wähle die Zelle A2 aus und gib Folgendes ein:
=NORM.INV(RAND(), 10000, 2000)
Die obige Gleichung erzeugt eine Normalverteilung mit einem bestimmten Mittelwert und einer Standardabweichung wie unten dargestellt:
Die Verteilung für die Nachfrage schaffen.
Markiere als Nächstes die Zelle B2 und gib Folgendes ein:
=50 + (70-50) * RAND()
Die obige Gleichung erzeugt eine gleichmäßige Verteilung des Verkaufspreises zwischen 50 und 70 US-Dollar:
Erstellen der Verteilung für den Verkaufspreis.
Wähle Zelle C2 aus und gib Folgendes ein:
=NORM.INV(RAND(), 30, 5)
Die obige Gleichung erzeugt, ähnlich wie die Gleichung für die Nachfrage, eine Normalverteilung mit einem bestimmten Mittelwert und einer Standardabweichung wie unten:
Erstellen der Verteilung für die Kosten.
Schritt 3: Berechne die abhängige Variable
Berechne nun den Gewinn, der die abhängige Variable ist, für jede Simulation mithilfe der Formel in Spalte D:
=(B2 - C2) * A2
Berechne den Gewinn.
Schritt 4: Ausfüllen, um mehrere Szenarien zu simulieren
Bislang haben wir eine einzige Simulation erstellt. Lass uns das auf mehrere, sagen wir tausend Simulationen ausweiten.
Wähle die Zellen A2 bis D2 aus und ziehe den Füllgriff (ein kleines Quadrat unten rechts in der Auswahl) nach unten, um die Formeln über so viele Zeilen zu füllen, wie du simulieren willst (z. B. 1000 Zeilen für 1000 Simulationen).
Sie wird in etwa so aussehen:
Erstellen der Simulationen.
Schritt 5: Analysiere die Ergebnisse
Nachdem du die Simulationen durchgeführt hast, kannst du die Ergebnisse mit statistischen Funktionen wie Minimum, Maximum, Durchschnitt und Standardabweichung analysieren. Zögere nicht, den Excel-Spickzettel zu Rate zu ziehen, um die eingebauten Excel-Funktionen aufzufrischen, die wir als Nächstes verwenden werden.
Um den durchschnittlich erwarteten Gewinn pro Monat zu ermitteln, gibst du Folgendes in eine Zelle ein, z.B. G6:
=AVERAGE(D2:D1001)
Um den erwarteten Mindestgewinn pro Monat zu ermitteln, gibst du Folgendes in eine Zelle ein, z.B. G7:
=MIN(D2:D1001)
Um den maximalen Gewinn zu ermitteln, der jeden Monat erwartet wird, gibst du Folgendes in eine Zelle ein, zum Beispiel G8:
=MAX(D2:D1001)
Um die Standardabweichung des Gewinns zu ermitteln, gibst du Folgendes in eine Zelle ein, zum Beispiel G9:
=STDEV.P(D2:D1001)
Nach der Ausführung sollte die Excel-Tabelle etwa so aussehen:
Analysiere die Simulationsergebnisse.
Wir können die geschätzten Ergebnisse und die Auswirkungen auf die Produkteinführung wie folgt interpretieren:
- Die durchschnittliche Gewinnzahl stellt den erwarteten Gewinn aus der Einführung des neuen Fitness-Trackers dar. Daraus geht hervor, dass wir bei jedem Simulationsdurchlauf im Durchschnitt einen Gewinn von 298.278,67 $ erwarten können. Dieser Wert ist als zentrale Schätzung der Rentabilität unter den gegebenen Annahmen nützlich.
- Ein Mindestgewinn von 67.598,78 $ ist der niedrigste Gewinn, der in allen unseren Simulationen beobachtet wurde. Er zeigt das Worst-Case-Szenario unter den Annahmen deines Modells an, das immer noch profitabel ist, aber deutlich weniger als der Durchschnitt. Dies könnte auf eine besonders geringe Nachfrage oder ungünstige Kostenbedingungen in dieser speziellen Simulation zurückzuführen sein.
- Ein maximaler Gewinn von 641.955,42 $ stellt das Best-Case-Szenario dar, bei dem die Nachfrage und der Preis wahrscheinlich am höchsten und die Kosten bei allen Simulationen am niedrigsten waren. Das zeigt, wie hoch das Potenzial ist, wenn sich die Bedingungen als sehr günstig erweisen.
Angesichts der großen Spanne zwischen den minimalen und maximalen Gewinnen und der erheblichen Standardabweichung ist die Einführung des neuen Produkts mit einem erheblichen finanziellen Risiko verbunden.
Die Entscheidungsträger sollten abwägen, ob das Unternehmen mit diesem Maß an Unsicherheit und dem Potenzial für unterdurchschnittliche Gewinne einverstanden ist.
Außerdem empfehlen wir dir, Visualisierungen wie z.B. Histogramme zu erstellen, um die Ergebnisse der Simulationen visuell zu veranschaulichen, auch wenn dies optional ist.
Best Practices und fortgeschrittene Techniken zur Verbesserung der Simulationen
Wenn du die gleiche Simulation wie oben noch einmal durchführst, kannst du einen kleinen Unterschied in den Berechnungen feststellen, wie unten gezeigt:
Unterschiedliche Simulationsergebnisse.
Das liegt daran, dass sich die Werte der ursprünglichen Simulation zwischen den Iterationen ändern können, was die resultierenden Schätzungen beeinflusst. Auch wenn die Abweichung gering ist, wenn sich der geschätzte Wert ändert, kommen bei den Entscheidungsträgern Bedenken hinsichtlich der Genauigkeit und Zuverlässigkeit der Simulation auf.
Wir wollen einige fortschrittliche Techniken untersuchen, mit denen wir die Genauigkeit und Zuverlässigkeit der Simulationen verbessern können.
Erhöhung der Anzahl von Simulationen
Eine größere Anzahl von Simulationen hilft dabei, zufällige Schwankungen auszugleichen und eine stabilere und genauere Schätzung des Ergebnisses zu erhalten.
Für das obige Beispiel können wir die Anzahl der Simulationsläufe erhöhen (z. B. von 1.000 auf 10.000 oder mehr), vor allem, wenn wir mit stark variierenden Parametern arbeiten.
Die Bestimmung der "richtigen" Anzahl von Simulationen hängt von mehreren Faktoren ab.
Je komplexer das Modell ist (d. h. je mehr Variablen und je größer die Bandbreite ihrer Wechselwirkungen), desto mehr Simulationen sind in der Regel erforderlich, um alle möglichen Ergebnisse zu erfassen und sicherzustellen, dass die Ergebnisse nicht auf Zufall beruhen.
Wenn die Inputs eine hohe Variabilität aufweisen oder stark schief sind, sind mehr Simulationen erforderlich, um die Schwänze (Extremwerte) der Ergebnisverteilungen genau zu schätzen.
Für detailliertere Analysen, insbesondere im Finanzwesen oder Risikomanagement, sind 10.000 bis 100.000 Simulationen keine Seltenheit. Diese Spanne wird in der Regel verwendet, um robuste Ergebnisse für verschiedene Szenarien und Inputs zu gewährleisten. Wie wir bereits erwähnt haben, ist Excel für solche umfangreichen Analysen nicht immer die beste Wahl, sondern eher R oder Python.
Verfeinerung der Eingangsverteilungen
Die Genauigkeit der Simulationen hängt weitgehend davon ab, wie gut die eingegebenen Wahrscheinlichkeitsverteilungen die tatsächliche Unsicherheit und das Verhalten der zugrunde liegenden Variablen widerspiegeln. In unserem Beispiel oben haben wir eine Normalverteilung für Nachfrage und Kosten und eine Gleichverteilung für den Verkaufspreis angenommen.
Außerdem könnten wir umfassendere historische Daten analysieren, um die Verteilungen besser zu parametrisieren. Wir können das Verhalten der Kosten, des Verkaufs und der Nachfrage in Abhängigkeit von externen Faktoren auf der Grundlage der Angaben von Fachleuten besser verstehen. Wir können auch Verteilungen wie Log-Normal, Beta oder Gamma verwenden oder auf der Grundlage empirischer Daten eigene Verteilungen erstellen.
Durchführen einer Sensitivitätsanalyse
Diese Analyse wird durchgeführt, um herauszufinden, welche Inputvariablen den größten Einfluss auf den Output haben, indem jeder Input systematisch variiert wird, während andere konstant gehalten werden.
In unserem obigen Beispiel können wir zwei Variablen konstant halten und die Verteilung der einen Variable ändern, um die Veränderungen in den Schätzungen zu verstehen. Dann wiederholst du den gleichen Vorgang für die verbleibenden zwei Variablen, eine nach der anderen. Letztendlich hilft diese Technik zu verstehen, auf welche Variable man sich konzentrieren sollte, um die Genauigkeit zu verbessern.
Wenn du die oben genannten Techniken iterativ anwendest und die Ergebnisse analysierst, kannst du genauere und zuverlässigere Ergebnisse erzielen.
Fazit
In diesem Tutorium hast du die Monte-Carlo-Simulation und die relevanten statistischen Konzepte kennengelernt. Nach einer Einführung in die relevanten Excel-Funktionen bietet das Tutorial eine Schritt-für-Schritt-Anleitung zur Umsetzung der Monte-Carlo-Simulation in Excel anhand eines realen Beispiels.
Schließlich hast du einige Best Practices und fortgeschrittene Techniken kennengelernt, die sicherstellen, dass deine Ergebnisse genauer und zuverlässiger sind.
Wenn du besonders daran interessiert bist, die oben beschriebene Monte-Carlo-Simulation mit anderen Tools wie Python oder R zu implementieren, sind diese beiden Ressourcen von Nutzen:
Wenn du dich lieber an das vertraute Microsoft Excel halten und deine Fähigkeiten mit diesem weit verbreiteten Tool verbessern möchtest, solltest du dir den Lernpfad Excel Grundlagen ansehen.
Bringe deine Karriere mit Excel voran
Erwerbe die Fähigkeiten, um Excel optimal zu nutzen - keine Erfahrung erforderlich.

Als Senior Data Scientist konzipiere, entwickle und implementiere ich umfangreiche Machine-Learning-Lösungen, um Unternehmen dabei zu helfen, bessere datengestützte Entscheidungen zu treffen. Als Data-Science-Autorin teile ich Erfahrungen, Karrieretipps und ausführliche praktische Anleitungen.
Setze deine Excel-Reise heute fort!
Kurs
Finanzmodellierung in Excel
Kurs