Office: Einführung in die Monte-Carlo-Simulation in Excel

Einführung in die Monte-Carlo-Simulation in Excel
Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Mehr... Weniger Dieser Artikel wurde von Wayne L. Winston aus Microsoft Excel Data Analysis and Business Modeling adaptiert.

Übersicht

• Wer verwendet die Monte-Carlo-Simulation?
  
  
• Was passiert, wenn Sie in einer Zelle = Rand () eingeben?
  
  
• Wie können Werte einer diskreten Zufallsvariablen simuliert werden?
  
  
• Wie können Werte einer normalen Zufallsvariablen simuliert werden?
  
  
• Wie kann ein Grußkarten Unternehmen ermitteln, wie viele Karten produziert werden?
  
  

Wir möchten die Wahrscheinlichkeiten von unsicheren Ereignissen genau abschätzen. Wie hoch ist beispielsweise die Wahrscheinlichkeit, dass die Zahlungsströme eines neuen Produkts einen positiven Nettobarwert (Barwert) haben? Was ist der Risikofaktor für unser Beteiligungsportfolio? Mit der Monte-Carlo-Simulation können wir Situationen modellieren, die Ungewissheit darstellen, und diese dann Tausende Male auf einem Computer abspielen.

Hinweis:  Der Name Monte Carlo-Simulation stammt aus den Computersimulationen, die in den 1930er und 1940er Jahren durchgeführt wurden, um die Wahrscheinlichkeit zu schätzen, dass die Kettenreaktion, die eine Atombombe zur Detonation benötigt, erfolgreich funktionieren würde. Die Physiker, die an dieser Arbeit beteiligt waren, waren große Fans von Glücksspielen, und so gaben Sie den simulationscodes den Codenamen Monte Carlo.

In den nächsten fünf Kapiteln werden Sie Beispiele für die Verwendung von Excel für Monte Carlo-Simulationen sehen.

Wer verwendet die Monte-Carlo-Simulation?
Viele Unternehmen nutzen die Monte-Carlo-Simulation als wichtigen Teil des Entscheidungsprozesses. Hier sind einige Beispiele.

• General Motors, Proctor und Gamble, Pfizer, Bristol-Myers Squibb und Eli Lilly verwenden Simulation, um sowohl die durchschnittliche Rendite als auch den Risikofaktor neuer Produkte zu schätzen. Bei GM werden diese Informationen vom CEO verwendet, um festzustellen, welche Produkte auf den Markt kommen.
  
  
• GM verwendet die Simulation für Aktivitäten wie Prognose des Nettoergebnisses des Unternehmens, Vorhersagen von strukturellen und Anschaffungskosten sowie Ermittlung der Anfälligkeit für verschiedene Arten von Risiken (wie Zinsänderungen und Wechselkursschwankungen).
  
  
• Lilly verwendet Simulation, um die optimale Anlagenkapazität für jedes Medikament zu ermitteln.
  
  
• Proctor und Gamble nutzt die Simulation, um das Fremdwährungsrisiko zu modellieren und optimal zu sichern.
  
  
• Sears verwendet Simulation, um zu ermitteln, wie viele Einheiten jeder Produktreihe von Lieferanten bestellt werden sollen, beispielsweise die Anzahl der andocker-Hosen, die in diesem Jahr bestellt werden sollen.
  
  
• Öl-und Pharmaunternehmen verwenden Simulation, um "reelle Optionen" zu schätzen, beispielsweise den Wert einer Option zum erweitern, Vertrag oder Verschieben eines Projekts.
  
  
• Finanzplaner verwenden Monte-Carlo-Simulation, um optimale Anlagestrategien für den Ruhestand ihrer Kunden zu ermitteln.
  
  

Was passiert, wenn Sie in einer Zelle = Rand () eingeben?
Wenn Sie die Formel = Rand () in eine Zelle eingeben, erhalten Sie eine Zahl, die ebenfalls einen beliebigen Wert zwischen 0 und 1 annehmen kann. Daher sollten Sie rund 25 Prozent der Zeit eine Zahl erhalten, die kleiner oder gleich 0,25 ist. ungefähr 10 Prozent der Zeit sollten Sie eine Zahl erhalten, die mindestens 0,90 ist usw. Um zu veranschaulichen, wie die Rand-Funktion funktioniert, werfen Sie einen Blick auf die Datei Randdemo.xlsx, wie in Abbildung 60-1 dargestellt.



Hinweis:  Wenn Sie die Datei Randdemo.xlsx öffnen, werden die gleichen Zufallszahlen in Abbildung 60-1 nicht angezeigt. Die Rand-Funktion berechnet immer automatisch die Zahlen neu, die beim Öffnen eines Arbeitsblatts generiert werden, oder wenn neue Informationen in das Arbeitsblatt eingegeben werden.

Kopieren Sie zunächst aus Zelle C3 in C4: C402 die Formel = Rand (). Dann benennen Sie den Bereich C3: C402- Daten. In Spalte F können Sie dann den Mittelwert der 400-Zufallszahlen (Zelle F2) nachvollziehen und mithilfe der ZÄHLENWENN-Funktion die Brüche ermitteln, die zwischen 0 und 0,25, 0,25 und 0,50, 0,50 und 0,75 sowie 0,75 und 1 liegen. Wenn Sie die Taste F9 drücken, werden die Zufallszahlen neu berechnet. Beachten Sie, dass der Mittelwert der 400-Nummern immer ca. 0,5 beträgt und dass rund 25 Prozent der Ergebnisse in Intervallen von 0,25 liegen. Diese Ergebnisse entsprechen der Definition einer Zufallszahl. Beachten Sie auch, dass die von Rand in verschiedenen Zellen generierten Werte unabhängig voneinander sind. Wenn beispielsweise die in Zelle C3 generierte Zufallszahl eine große Zahl ist (beispielsweise 0,99), gibt Sie uns nichts über die Werte der anderen generierten Zufallszahlen an.

Wie können Werte einer diskreten Zufallsvariablen simuliert werden?
Angenommen, der Bedarf für einen Kalender wird von der folgenden diskreten Zufallsvariablen bestimmt:

Nachfrage	Wahrsch
10.000	0,10
20.000	0,35
40.000	0,3
60.000	0,25

Wie können wir diese Nachfrage nach Kalendern viele Male abspielen oder simulieren? Der Trick besteht darin, jeden möglichen Wert der Rand-Funktion mit einem möglichen Bedarf für Kalender zu verknüpfen. Mit der folgenden Zuordnung wird sichergestellt, dass ein Bedarf von 10.000 10 Prozent der Zeit und so weiter eintritt.

Nachfrage	Zufallszahl zugewiesen
10.000	Weniger als 0,10
20.000	Größer als oder gleich 0,10 und kleiner als 0,45
40.000	Größer als oder gleich 0,45 und kleiner als 0,75
60.000	Größer als oder gleich 0,75

Wenn Sie die Simulation der Nachfrage demonstrieren möchten, sehen Sie sich die Datei Discretesim.xlsx an, die in Abbildung 60-2 auf der nächsten Seite angezeigt wird.



Der Schlüssel zu unserer Simulation besteht darin, eine Zufallszahl zu verwenden, um eine Suche aus dem Tabellenbereich F2: G5 (benanntes NachschlageVerfahren) zu initiieren. Zufällige Zahlen, die größer oder gleich 0 und kleiner als 0,10 sind, führen zu einer Forderung von 10.000; bei Zufallszahlen, die größer als oder gleich 0,10 und kleiner als 0,45 sind, ergibt sich eine Forderung von 20.000; bei Zufallszahlen, die größer als oder gleich 0,45 und kleiner als 0,75 sind, ergibt sich eine Forderung von 40.000; und zufällige Zahlen, die größer als oder gleich 0,75 sind, ergeben einen Bedarf von 60.000. Sie generieren 400-Zufallszahlen durch Kopieren von C3 in C4: C402 der Formel Rand (). Sie generieren dann 400-Testversionen oder Iterationen des Kalender Bedarfs, indem Sie die Formel SVERWEIS (C3, Lookup, 2)von B3 in B4: B402 kopieren. Mit dieser Formel wird sichergestellt, dass jede zufällige Zahl, die weniger als 0,10 ist, einen Bedarf von 10.000 generiert, wobei jede zufällige Zahl zwischen 0,10 und 0,45 einen Bedarf von 20.000 generiert usw. Verwenden Sie im Zellbereich F8: F11 die ZÄHLENWENN-Funktion, um den Bruchteil unserer 400-Iterationen zu ermitteln, die die einzelnen Anforderungen ergeben. Wenn wir F9 drücken, um die Zufallszahlen neu zu berechnen, liegen die simulierten Wahrscheinlichkeiten in der Nähe unserer angenommenen nach Frage Wahrscheinlichkeiten.

Wie können Werte einer normalen Zufallsvariablen simuliert werden?
Wenn Sie in eine beliebige Zelle die Formel NORMINV (Rand (), mu, Sigma)eingeben, generieren Sie einen simulierten Wert einer normalen Zufallsvariablen mit einer mittleren mu -und Standardabweichung Sigma. Diese Vorgehensweise wird in der Datei Normalsim.xlsx veranschaulicht, wie in Abbildung 60-3 dargestellt.



Nehmen wir an, dass wir 400-Testversionen oder Iterationen für eine normale Zufallsvariable mit einem Mittelwert von 40.000 und einer Standardabweichung von 10.000 simulieren möchten. (Sie können diese Werte in die Zellen E1 und E2 eingeben und diese Zellen Mean und Sigmabenennen.) Durch Kopieren der Formel = Rand () von C4 in C5: C403 wird 400 unterschiedliche Zufallszahlen generiert. Kopieren von B4 nach B5: B403 die Formel NORMINV (C4, Mean, Sigma) generiert 400 verschiedene Versuchs Werte aus einer normalen Zufallsvariablen mit einem Mittelwert von 40.000 und einer Standardabweichung von 10.000. Wenn wir die F9-Taste drücken, um die Zufallszahlen neu zu berechnen, bleibt der Mittelwert in der Nähe von 40.000 und der Standardabweichung in der Nähe von 10.000.

Im Wesentlichen generiert die Formel NORMINV (p, mu, Sigma) für eine Zufallszahl xdas p. Perzentil einer normalen Zufallsvariablen mit einem mittleren mu und einer Standardabweichung Sigma. Beispielsweise generiert die Zufallszahl 0,77 in Zelle C4 (siehe Abbildung 60-3) in Zelle B4 etwa das 77th-Perzentil einer normalen Zufallsvariablen mit einem Mittelwert von 40.000 und einer Standardabweichung von 10.000.

Wie kann ein Grußkarten Unternehmen ermitteln, wie viele Karten produziert werden?
In diesem Abschnitt erfahren Sie, wie Sie die Monte-Carlo-Simulation als Entscheidungshilfen verwenden können. Angenommen, die Nachfrage nach einer Valentinstag-Karte unterliegt der folgenden diskreten Zufallsvariablen:

Nachfrage	Wahrsch
10.000	0,10
20.000	0,35
40.000	0,3
60.000	0,25

Die Gruß Karte wird für $4,00 verkauft, und die Variablen Kosten für die Erstellung jeder Karte belaufen sich auf $1,50. Restkarten müssen zu einem Preis von $0,20 pro Karte entsorgt werden. Wie viele Karten sollten gedruckt werden?

Im Grunde simulieren wir jede mögliche Produktionsmenge (10.000, 20.000, 40.000 oder 60.000) vielfach (beispielsweise 1000-Iterationen). Anschließend ermitteln wir, welche Bestellmenge den maximalen durchschnittlichen Gewinn über die 1000-Iterationen ergibt. Sie finden die Daten für diesen Abschnitt in der Datei Valentine.xlsx, wie in Abbildung 60-4 zu sehen ist. Sie weisen die Bereichsnamen in den Zellen B1: B11 den Zellen C1: C11 zu. Der Zellbereich G3: H6 erhält den Namen Lookup. Unsere Verkaufspreis-und Kosten Parameter werden in die Zellen C4: C6 eingegeben.



In Zelle C1 können Sie eine Test Produktionsmenge (in diesem Beispiel 40.000) eingeben. Erstellen Sie als nächstes eine Zufallszahl in Zelle C2 mit der Formel = Rand (). Wie zuvor beschrieben, simulieren Sie den Bedarf für die Karte in Zelle C3 mit der Formel SVERWEIS (Rand, Lookup, 2). (In der SVERWEIS-Formel ist Rand der Zellname, der Zelle C3 zugewiesen ist, nicht die Rand-Funktion.)

Die Anzahl der verkauften Einheiten ist kleiner als unsere Produktionsmenge und-Nachfrage. In Zelle C8 berechnen Sie unseren Umsatz mit der Formel Min (produziert, Nachfrage) * unit_price. In Zelle C9 berechnen Sie die Gesamtproduktionskosten mit der erzeugten Formel * unit_prod_cost.

Wenn wir mehr Karten als in der Nachfrage produzieren, entspricht die Anzahl der übrig gebliebenen Einheiten der Produktion minus der Nachfrage; Andernfalls bleiben keine Einheiten übrig. Wir berechnen unsere Entsorgungskosten in Zelle C10 mit der Formel unit_disp_cost * wenn (produziert>Nachfrage, produziert-Nachfrage; 0). Und schließlich berechnen wir in Zelle C11 unseren Gewinn als Umsatz – total_var_cost total_disposing_cost.

Wir möchten eine effiziente Möglichkeit, F9 viele Male zu drücken (beispielsweise 1000), für jede Produktionsmenge und den erwarteten Gewinn für jede Menge. In dieser Situation ist eine bidirektionale Datentabelle zu unserer Rettung gekommen. (Weitere Informationen zu Datentabellen finden Sie in Kapitel 15, "Sensitivitätsanalyse mit Datentabellen".) Die in diesem Beispiel verwendete Datentabelle wird in Abbildung 60-5 angezeigt.



Geben Sie im Zellbereich A16: A1015 die Zahlen 1 – 1000 (entsprechend unseren 1000-Testversionen) ein. Eine einfache Möglichkeit zum Erstellen dieser Werte besteht darin, zunächst in Zelle a16 1 einzugeben. Wählen Sie die Zelle aus, und klicken Sie dann auf der Registerkarte Start in der Gruppe Bearbeiten auf Ausfüllen, und wählen Sie Serie aus, um das Dialogfeld Reihe anzuzeigen. Geben Sie im Dialogfeld Reihe , das in Abbildung 60-6 angezeigt wird, einen Schrittwert von 1 und einen Stoppwert von 1000 ein. Wählen Sie im Bereich Reihe in die Option Spalten aus, und klicken Sie dann auf OK. Die Zahlen 1 – 1000 werden in Spalte A beginnend in Zelle A16 eingegeben.



Als nächstes geben wir die möglichen Produktionsmengen (10.000, 20.000, 40.000, 60.000) in Zellen B15: E15 ein. Wir möchten den Gewinn für jede Testnummer (1 bis 1000) und jede Produktionsmenge berechnen. Wir verweisen auf die Formel für Profit (berechnet in Zelle C11) in der oberen linken Zelle unserer Datentabelle (A15) durch Eingabe von = C11.

Wir sind nun bereit, Excel in die Simulation von 1000-Iterationen der Nachfrage für jede Produktionsmenge zu überarbeiten. Wählen Sie den Tabellenbereich aus (A15: E1014), und klicken Sie dann in der Gruppe Daten Tools auf der Registerkarte Daten auf was ist, wenn Analyse, und wählen Sie dann Datentabelle aus. Wenn Sie eine bidirektionale Datentabelle einrichten möchten, wählen Sie die Produktionsmenge (Zelle C1) als Zeileneingabezelle aus, und wählen Sie eine leere Zelle aus (wir haben die Zelle I14) als Eingabezelle für die Spalte ausgewählt. Nachdem Sie auf OK geklickt haben, simuliert Excel 1000-Bedarfswerte für jede Bestellmenge.

Um zu verstehen, warum dies funktioniert, sollten Sie die Werte in der Datentabelle im Zellbereich C16: C1015. Für jede dieser Zellen wird in Excel ein Wert von 20.000 in Zelle C1 verwendet. In C16 wird der Wert für die Spalteneingabezelle 1 in eine leere Zelle und die Zufallszahl in Zelle C2 neu berechnet. Der entsprechende Gewinn wird dann in der Zelle C16 erfasst. Anschließend wird der Eingabewert für die Spaltenzelle 2 in eine leere Zelle und die Zufallszahl in C2 erneut berechnet. Der entsprechende Gewinn wird in die Zelle C17 eingegeben.

Durch Kopieren von Zell B13 in C13: E13 des Formel Mittelwerts (B16: B1015)berechnen wir den durchschnittlichen simulierten Profit für jede Produktionsmenge. Durch Kopieren von Zell B14 in C14: E14 der Formel STABW (B16: B1015)berechnen wir die Standardabweichung unserer simulierten Gewinne für jede Bestellmenge. Jedes Mal, wenn wir F9 drücken, werden 1000 Iterationen des Bedarfs für jede Bestellmenge simuliert. Bei der Erstellung von 40.000-Karten wird immer der größte erwartete Gewinn erzielt. Daher scheint es so zu sein, dass die Herstellung von 40.000-Karten die richtige Entscheidung ist.

Auswirkungen des Risikos auf unsere Entscheidung      Wenn wir 20.000 anstelle von 40.000-Karten produziert haben, sinkt unser Erwarteter Gewinn rund 22 Prozent, doch unser Risiko (gemessen an der Standardabweichung des Gewinns) fällt fast 73 Prozent. Wenn wir also extrem risikoscheu sind, ist die Herstellung von 20.000-Karten möglicherweise die richtige Entscheidung. Im übrigen hat die Herstellung von 10.000-Karten immer eine Standardabweichung von 0 Karten, denn wenn wir 10.000-Karten produzieren, werden wir Sie immer ohne Reste verkaufen.

Hinweis:  In dieser Arbeitsmappe ist die Berechnungs Option auf " automatisch" mit Ausnahme von Tabellengesetzt. (Verwenden Sie den Befehl "Berechnung" in der Gruppe "Berechnung" auf der Registerkarte "Formeln".) Mit dieser Einstellung wird sichergestellt, dass die Datentabelle nicht neu berechnet wird, es sei denn, wir drücken F9, was eine gute Idee ist, da eine große Datentabelle ihre Arbeit verlangsamt, wenn Sie jedes Mal neu berechnet wird, wenn Sie etwas in Ihr Arbeitsblatt eingeben. Beachten Sie, dass in diesem Beispiel, wenn Sie F9 drücken, der Mittelwert des Gewinns geändert wird. Dies geschieht, weil jedes Mal, wenn Sie F9 drücken, eine andere Sequenz von 1000-Zufallszahlen verwendet wird, um Anforderungen für jede Bestellmenge zu generieren.

Konfidenzintervall für Mittelwert      Eine natürliche Frage, die in dieser Situation zu stellen ist, ist, in welchem Intervall sind wir 95 Prozent sicher, dass der wahre Mittelwert fallen wird? Dieses Intervall wird als Konfidenzintervall von 95 Prozent für Mittel Gewinnbezeichnet. Ein Konfidenzintervall von 95 Prozent für den Mittelwert einer beliebigen Simulations Ausgabe wird anhand der folgenden Formel berechnet:





In der Zelle J11 berechnen Sie die untere Grenze für das Konfidenzintervall von 95 Prozent auf dem mittleren Gewinn, wenn 40.000 Kalender mit der Formel D13 – 1.96 * D14/sqrt (1000)erstellt werden. In Zelle J12 berechnen Sie die Obergrenze für unser Konfidenzintervall von 95 Prozent mit der Formel D13 + 1.96 * D14/sqrt (1000). Diese Berechnungen sind in Abbildung 60-7 zu sehen.



Wir sind 95 Prozent sicher, dass unser durchschnittlicher Gewinn, wenn 40.000 Kalender bestellt werden, zwischen $56.687 und $62.589 liegt.

Probleme

1. Ein GMC-Händler geht davon aus, dass die Nachfrage nach 2005-Gesandten in der Regel mit einem Mittelwert von 200 und einer Standardabweichung von 30 verbreitet wird. Seine Kosten für den Empfang eines Gesandten sind $25.000, und er verkauft einen Gesandten für $40.000. Die Hälfte aller Gesandten, die nicht zum vollen Preis verkauft werden, kann für $30.000 verkauft werden. Er bezieht sich auf die Bestellung von 200, 220, 240, 260, 280 oder 300-Gesandten. Wie viele sollte er bestellen?
   
   
2. Ein kleiner Supermarkt versucht, festzustellen, wie viele Exemplare der Personen Zeitschrift Sie jede Woche bestellen sollten. Sie gehen davon aus, dass der Bedarf an Personen durch die folgende diskrete Zufallsvariable geregelt wird:
   
   [parsehtml]<table id="tblID0EABAACAAA"> <thead><tr><th><p> <b>Nachfrage</b> </p></th><th><p> <b>Wahrsch</b> </p></th></tr></thead> <tbody> <tr> <td> <p>15</p> </td> <td> <p>0,10</p> </td> </tr> <tr> <td> <p>20</p> </td> <td> <p>0,20</p> </td> </tr> <tr> <td> <p>25</p> </td> <td> <p>0,30</p> </td> </tr> <tr> <td> <p>30</p> </td> <td> <p>0,25</p> </td> </tr> <tr> <td> <p>35</p> </td> <td> <p>0,15</p> </td> </tr> </tbody> </table>[/parsehtml]
3. Der Supermarkt zahlt $1,00 für jede Kopie von Personen und verkauft Sie für $1,95. Jede nicht verkaufte Kopie kann für $0,50 zurückgegeben werden. Wie viele Kopien von Personen sollten im Store bestellt werden?
   
   

Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen, Unterstützung in der Answers Community erhalten oder aber ein neues Feature oder eine Verbesserung auf Excel User Voice vorschlagen.

:)