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

Dieser Artikel wurde aus Microsoft Excel Data Analysis and Business Modeling von Wayne L. Winston übernommen.

Übersicht

• Wer nutzt die Monte-Carlo-Simulation?
• Was passiert, wenn Sie =RAND() in eine Zelle eingeben?
• Wie können Sie Werte einer diskreten Zufallsvariablen simulieren?
• Wie können Sie Werte einer normalen Zufallsvariablen simulieren?
• Wie kann ein Unternehmen für Grußkarten bestimmen, wie viele Karten produziert werden sollen?

Wir möchten die Wahrscheinlichkeiten ungewisser Ereignisse genau einschätzen. Wie hoch ist zum Beispiel die Wahrscheinlichkeit, dass die Cashflows eines neuen Produkts einen positiven Kapitalwert (NBW) aufweisen? Was ist der Risikofaktor unseres Anlageportfolios? Die Monte-Carlo-Simulation ermöglicht es uns, unsichere Situationen zu modellieren und sie dann tausende Male auf einem Computer durchzuspielen.

In den nächsten fünf Kapiteln sehen Sie Beispiele dafür, wie Sie Excel zum Durchführen von Monte-Carlo-Simulationen verwenden können.

Wer nutzt die Monte-Carlo-Simulation?

Viele Unternehmen nutzen die Monte-Carlo-Simulation als wichtigen Teil ihres Entscheidungsprozesses. Hier sind einige Beispiele.

• General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb und Eli Lilly verwenden Simulationen, um sowohl die durchschnittliche Rendite als auch den Risikofaktor neuer Produkte abzuschätzen. Bei GM werden diese Informationen vom CEO verwendet, um zu bestimmen, welche Produkte auf den Markt kommen.
• GM verwendet Simulationen für Aktivitäten wie die Prognose des Nettogewinns für das Unternehmen, die Vorhersage der Struktur- und Einkaufskosten und die Bestimmung seiner Anfälligkeit für verschiedene Arten von Risiken (wie Zinsänderungen und Wechselkursschwankungen).
• Lilly verwendet Simulationen, um die optimale Pflanzenkapazität für jedes Medikament zu bestimmen.
• Proctor and Gamble nutzt Simulationen, um das Wechselkursrisiko zu modellieren und optimal abzusichern.
• Sears verwendet Simulationen, um zu bestimmen, wie viele Einheiten jeder Produktlinie bei Lieferanten bestellt werden sollten – zum Beispiel die Anzahl der Dockers-Hosen, die in diesem Jahr bestellt werden sollten.
• Öl- und Pharmaunternehmen nutzen Simulationen, um "reale Optionen" zu bewerten, wie z. B. den Wert einer Option, ein Projekt zu erweitern, zu verkleinern oder zu verschieben.
• Finanzplaner nutzen die Monte-Carlo-Simulation, um optimale Anlagestrategien für den Ruhestand ihrer Kunden zu ermitteln.

Was passiert, wenn Sie =RAND() in eine Zelle eingeben?

Wenn Sie die Formel =ZUFALLS() in eine Zelle eingeben, erhalten Sie eine Zahl, die mit gleicher Wahrscheinlichkeit einen beliebigen Wert zwischen 0 und 1 annimmt. Daher sollten Sie in etwa 25 Prozent der Fälle eine Zahl kleiner oder gleich 0,25 erhalten; In etwa 10 Prozent der Fälle sollten Sie eine Zahl erhalten, die mindestens 0,90 beträgt, und so weiter. Um die Funktionsweise der Funktion ZUFALLSZAHL zu veranschaulichen, sehen Sie sich die Datei-Randdemo.xlsx an, die in Abbildung 60-1 dargestellt ist.

Kopieren Sie zunächst die Formel =ZUFALLS() aus Zelle C3 in C4:C402. Dann benennen Sie den Bereich C3:C402 Daten. Anschließend können Sie in Spalte F den Mittelwert der 400 Zufallszahlen nachverfolgen (Zelle F2) und die Funktion ZÄHLENWENN verwenden, um die Bruchzahlen zu bestimmen, 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 Zahlen immer ungefähr 0,5 ist und dass etwa 25 Prozent der Ergebnisse in Intervallen von 0,25 liegen. Diese Ergebnisse stimmen mit der Definition einer Zufallszahl überein. Beachten Sie auch, dass die von ZUFALLSZAHL in verschiedenen Zellen generierten Werte unabhängig sind. Wenn die in Zelle C3 generierte Zufallszahl beispielsweise eine große Zahl ist (z. B. 0,99), sagt sie nichts über die Werte der anderen generierten Zufallszahlen aus.

Wie können Sie Werte einer diskreten Zufallsvariablen simulieren?

Angenommen, die Nachfrage nach einem Kalender wird durch die folgende diskrete Zufallsvariable gesteuert:

Nachfrage	Wahrsch
10.000	0,10
20.000	0,35
40,000	0,3
60.000	0,25

Wie kann Excel diesen Bedarf an Kalendern viele Male darstellen oder simulieren? Der Trick besteht darin, jeden möglichen Wert der Funktion ZUFALLSZAHL mit einem möglichen Bedarf an Kalendern zu verknüpfen. Die folgende Zuweisung stellt sicher, dass ein Bedarf von 10.000 in 10 Prozent der Fälle auftritt und so weiter.

Nachfrage	Nach dem Zufallsprinzip zugewiesene Nummer
10.000	Kleiner als 0,10
20.000	Größer oder gleich 0,10 und kleiner als 0,45
40,000	Größer oder gleich 0,45 und kleiner als 0,75
60.000	Größer oder gleich 0,75

Um die Simulation der Nachfrage zu veranschaulichen, sehen Sie sich die Datei Discretesim.xlsx an, die in Abbildung 60-2 auf der nächsten Seite dargestellt ist.

Der Schlüssel zu unserer Simulation besteht darin, eine Zufallszahl zu verwenden, um eine Suche aus dem Tabellenbereich F2:G5 ( benannte Suche) zu initiieren. Zufallszahlen, die größer oder gleich 0 und kleiner als 0,10 sind, ergeben einen Bedarf von 10.000; Zufallszahlen größer oder gleich 0,10 und kleiner als 0,45 ergeben einen Bedarf von 20.000; Zufallszahlen größer oder gleich 0,45 und kleiner als 0,75 ergeben eine Nachfrage von 40.000; und Zufallszahlen größer oder gleich 0,75 ergeben einen Bedarf von 60.000. Sie generieren 400 Zufallszahlen, indem Sie die Formel RAND() von C3 nach C4:C402 kopieren. Anschließend generieren Sie 400 Versuche oder Iterationen des Kalenderbedarfs, indem Sie die Formel SVERWEIS(C3;Nachschlage;2) von B3 nach B4:B402 kopieren. Diese Formel stellt sicher, dass jede Zufallszahl kleiner als 0,10 einen Bedarf von 10.000 erzeugt, jede Zufallszahl zwischen 0,10 und 0,45 einen Bedarf von 20.000 erzeugt usw. Verwenden Sie im Zellbereich F8:F11 die Funktion ZÄHLENWENN, um den Anteil unserer 400 Iterationen zu bestimmen, der jeden Bedarf ergibt. Wenn wir F9 drücken, um die Zufallszahlen neu zu berechnen, liegen die simulierten Wahrscheinlichkeiten nahe an unseren angenommenen Nachfragewahrscheinlichkeiten.

Wie können Sie Werte einer normalen Zufallsvariablen simulieren?

Wenn Sie in eine beliebige Zelle die Formel NORMINV(rand(),mu,sigma) eingeben, erzeugen Sie einen simulierten Wert einer normalen Zufallsvariablen mit einem Mittelwert mu und einer Standardabweichung von Sigma. Dieses Verfahren ist in der Datei Normalsim.xlsx dargestellt, die in Abbildung 60-3 dargestellt ist.

Angenommen, wir möchten 400 Versuche oder Iterationen für eine normale Zufallsvariable mit einem Mittelwert von 40.000 und einer Standardabweichung von 10.000 simulieren. (Sie können diese Werte in die Zellen E1 und E2 eingeben und diesen Zellen den Namen "Mittelwert " bzw. "Sigma" geben.) Wenn Sie die Formel =RAND() von C4 nach C5:C403 kopieren, werden 400 verschiedene Zufallszahlen generiert. Kopieren von B4 nach B5:B403 Die Formel NORMINV(C4,Mittelwert,Sigma) erzeugt aus einer normalen Zufallsvariablen 400 verschiedene Versuchswerte mit einem Mittelwert von 40.000 und einer Standardabweichung von 10.000. Wenn wir die Taste F9 drücken, um die Zufallszahlen neu zu berechnen, bleibt der Mittelwert nahe 40.000 und die Standardabweichung nahe 10.000.

Im Wesentlichen generiert die Formel NORMINV(p,mu,sigma) für eine Zufallszahl x das p-tePerzentil einer normalen Zufallsvariablen mit einem Mittelwert mu und einem Standardabweichungssigma. Beispielsweise generiert die Zufallszahl 0,77 in Zelle C4 (siehe Abbildung 60-3) in Zelle B4 ungefähr das 77. Perzentil einer normalen Zufallsvariablen mit einem Mittelwert von 40.000 und einer Standardabweichung von 10.000.

Wie kann ein Unternehmen für Grußkarten bestimmen, wie viele Karten produziert werden sollen?

In diesem Abschnitt erfahren Sie, wie die Monte-Carlo-Simulation als Entscheidungshilfe verwendet werden kann. Angenommen, die Nachfrage nach einer Valentinstags-Karte wird durch die folgende diskrete Zufallsvariable gesteuert:

Nachfrage	Wahrsch
10.000	0,10
20.000	0,35
40,000	0,3
60.000	0,25

Der Gruß Karte wird für 4,00 $ verkauft, und die variablen Kosten für die Herstellung jedes Karte betragen 1,50 $. Übrig gebliebene Karten müssen zu einem Preis von 0,20 USD pro Karte entsorgt werden. Wie viele Karten sollten gedruckt werden?

Grundsätzlich simulieren wir jede mögliche Produktionsmenge (10.000, 20.000, 40.000 oder 60.000) viele Male (z. B. 1000 Iterationen). Dann ermitteln wir, welche Bestellmenge über die 1000 Iterationen den maximalen durchschnittlichen Gewinn bringt. Die Daten für diesen Abschnitt finden Sie in der Datei Valentine.xlsx, die in Abbildung 60-4 dargestellt ist. Sie weisen die Bereichsnamen in den Zellen B1:B11 den Zellen C1:C11 zu. Dem Zellbereich G3:H6 wird die Namenssuche zugewiesen. Unsere Verkaufspreis- und Kostenparameter werden in die Zellen C4:C6 eingegeben.

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

Die Anzahl der verkauften Einheiten ist der kleinere Teil unserer 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 Formel produziert*unit_prod_cost.

Wenn wir mehr Karten produzieren, als nachgefragt werden, ist die Anzahl der verbleibenden Einheiten gleich der Produktion minus der Nachfrage; Ansonsten bleiben keine Einheiten übrig. Wir berechnen unsere Entsorgungskosten in Zelle C10 mit der Formel unit_disp_cost*WENN(produzierte>Nachfrage,produzierte Nachfrage,0). In Zelle C11 schließlich berechnen wir unseren Gewinn als Umsatz – total_var_cost-total_disposing_cost.

Wir möchten eine effiziente Möglichkeit, F9 viele Male (z. B. 1000) für jede Produktionsmenge zu drücken und unseren erwarteten Gewinn für jede Menge zu zählen. In dieser Situation hilft uns eine bidirektionale Datentabelle. (Weitere Informationen zu Datentabellen finden Sie in Kapitel 15, "Sensitivitätsanalyse mit Datentabellen".) Die in diesem Beispiel verwendete Datentabelle ist in Abbildung 60-5 dargestellt.

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

Als Nächstes geben wir unsere möglichen Produktionsmengen (10.000, 20.000, 40.000, 60.000) in die Zellen B15:E15 ein. Wir wollen den Gewinn für jede Versuchsnummer (1 bis 1000) und jede Produktionsmenge berechnen. Auf die Gewinnformel (berechnet in Zelle C11) in der linken oberen Zelle unserer Datentabelle (A15) verweisen wir mit =C11.

Wir sind jetzt bereit, Excel so auszutricksen, dass es 1000 Bedarfsiterationen für jede Produktionsmenge simuliert. Wählen Sie den Tabellenbereich (A15:E1014) aus, und klicken Sie dann in der Gruppe "Datentools" auf der Registerkarte "Daten" auf "What If Analysis", und wählen Sie dann "Datentabelle" aus. Um eine bidirektionale Datentabelle einzurichten, wählen Sie unsere Produktionsmenge (Zelle C1) als Zeileneingabezelle und dann eine beliebige leere Zelle (wir haben Zelle I14 ausgewählt) als Spalteneingabezelle aus. Nachdem Sie auf OK geklickt haben, simuliert Excel 1000 Bedarfswerte für jede Bestellmenge.

Um zu verstehen, warum dies funktioniert, betrachten Sie die Werte, die von der Datentabelle im Zellbereich C16:C1015 platziert werden. Für jede dieser Zellen verwendet Excel den Wert 20.000 in Zelle C1. In C16 wird der Wert der Spalteneingabezelle 1 in eine leere Zelle eingefügt, und die Zufallszahl in Zelle C2 wird neu berechnet. Der entsprechende Gewinn wird dann in Zelle C16 erfasst. Dann wird der Eingabewert der Spaltenzelle 2 in eine leere Zelle gesetzt, und die Zufallszahl in C2 wird erneut neu berechnet. Der entsprechende Gewinn wird in Zelle C17 eingegeben.

Durch Kopieren der Formel MITTELWERT(B16:B1015) aus Zelle B13 nach C13:E13 berechnen wir den durchschnittlichen simulierten Gewinn für jede Produktionsmenge. Durch Kopieren der Formel STDEV(B16:B1015) aus Zelle B14 nach C14:E14 berechnen wir die Standardabweichung unserer simulierten Gewinne für jede Bestellmenge. Jedes Mal, wenn wir F9 drücken, werden 1000 Nachfrageiterationen für jede Bestellmenge simuliert. Die Produktion von 40.000 Karten bringt immer den größten erwarteten Gewinn. Daher scheint es, dass die Produktion von 40.000 Karten die richtige Entscheidung ist.

Die Auswirkungen des Risikos auf unsere Entscheidung Wenn wir 20.000 statt 40.000 Karten produzieren, sinkt unser erwarteter Gewinn um etwa 22 Prozent, aber unser Risiko (gemessen an der Standardabweichung des Gewinns) sinkt um fast 73 Prozent. Wenn wir also extrem risikoscheu sind, könnte die Produktion von 20.000 Karten die richtige Entscheidung sein. Übrigens hat die Produktion von 10.000 Karten immer eine Standardabweichung von 0 Karten, denn wenn wir 10.000 Karten produzieren, verkaufen wir immer alle ohne Reste.

Konfidenzintervall für den mittleren Gewinn Eine natürliche Frage, die man sich in dieser Situation stellen muss, ist, in welchem Intervall wir zu 95 Prozent sicher sind, dass der wahre mittlere Gewinn sinken wird. Dieses Intervall wird als 95-Prozent-Konfidenzintervall für den mittleren Gewinn bezeichnet. Ein 95-Prozent-Konfidenzintervall für den Mittelwert einer Simulationsausgabe wird mit der folgenden Formel berechnet:

In Zelle J11 berechnen Sie die untere Grenze für das 95-Prozent-Konfidenzintervall für den 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 95-Prozent-Konfidenzintervall mit der Formel D13+1,96*D14/SQRT(1000). Diese Berechnungen sind in Abbildung 60-7 dargestellt.

Wir sind uns zu 95 Prozent sicher, dass unser durchschnittlicher Gewinn, wenn 40.000 Kalender bestellt werden, zwischen 56.687 und 62.589 US-Dollar liegt.

Probleme

1. Ein GMC-Händler glaubt, dass die Nachfrage nach 2005er Envoys mit einem Mittelwert von 200 und einer Standardabweichung von 30 normal verteilt sein wird. Seine Kosten für den Empfang eines Gesandten betragen 25.000 Dollar, und er verkauft einen Gesandten für 40.000 Dollar. Die Hälfte aller Gesandten, die nicht zum vollen Preis verkauft werden, kann für 30.000 Dollar verkauft werden. Er erwägt, 200, 220, 240, 260, 280 oder 300 Gesandte zu bestellen. Wie viele sollte er bestellen?

2. Ein kleiner Supermarkt versucht herauszufinden, wie viele Exemplare des People-Magazins er pro Woche bestellen soll. Sie glauben, dass ihre Nachfrage nach People durch die folgende diskrete Zufallsvariable bestimmt wird:

   Nachfrage	Wahrsch
   15	0,10
   20	0,20
   25	0.30
   30	0,25
   35	0,15

3. Der Supermarkt zahlt 1,00 Dollar für jedes Exemplar von People und verkauft es für 1,95 Dollar. Jedes unverkaufte Exemplar kann für 0,50 $ zurückgegeben werden. Wie viele Exemplare von People sollte der Shop bestellen?

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen oder in Communitys Unterstützung erhalten.