Einführung in die Monte Carlo-Simulation in Excel

• Wer verwendet die Monte-Carlo-Simulation?

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

• Wie können Sie Werte einer diskreten Zufallsvariable simulieren?

• Wie können Sie Werte einer normalen Zufallsvariable simulieren?

• Wie kann ein Begrüßungs-Karte Unternehmen bestimmen, wie viele Karten produziert werden sollen?

Wir möchten die Wahrscheinlichkeiten für unsichere Ereignisse genau abschätzen. Wie ist beispielsweise die Wahrscheinlichkeit, dass die Cashflows eines neuen Produkts einen positiven Netto-Barwert (NPV) aufweisen? Was ist der Risikofaktor unseres Anlageportfolios? Die Monte-Carlo-Simulation ermöglicht es uns, Situationen zu modellieren, die Unsicherheiten darstellen, und diese dann tausende Male auf einem Computer auszuspielen.

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

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 zu schä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 Konzernergebnisses, die Vorhersage von Struktur- und Einkaufskosten und die Bestimmung der Anfälligkeit für verschiedene Arten von Risiken (z. B. Zinsänderungen und Wechselkursschwankungen).

• Lilly verwendet simulation, um die optimale Pflanzenkapazität für jedes Medikament zu bestimmen.

• Proctor and Gamble nutzt Simulationen, um Das Devisenrisiko zu modellieren und optimal abzusichern.

• Sears verwendet simulation, um zu bestimmen, wie viele Einheiten der einzelnen Produktlinien bei Lieferanten bestellt werden sollen – z. B. die Anzahl der Paar dockers Hosen, die in diesem Jahr bestellt werden sollen.

• Öl- und Pharmaunternehmen nutzen Simulationen, um "echte Optionen" zu schätzen, z. B. den Wert einer Option zum Erweitern, Abschließen oder Verschieben eines Projekts.

• Finanzplaner verwenden die Monte-Carlo-Simulation, um optimale Anlagestrategien für die Pensionierung ihrer Kunden zu bestimmen.

Wenn Sie die Formel =RAND() in eine Zelle eingeben, erhalten Sie eine Zahl, die ebenso wahrscheinlich einen beliebigen Wert zwischen 0 und 1 annimmt. Daher sollten Sie in etwa 25 Prozent der Zeit eine Zahl erhalten, die kleiner oder gleich 0,25 ist; in etwa 10 Prozent der Zeit sollten Sie eine Zahl erhalten, die mindestens 0,90 ist usw. Um die Funktionsweise der RAND-Funktion zu veranschaulichen, sehen Sie sich die Datei Randdemo.xlsx an, die in Abbildung 60-1 dargestellt ist.

Kopieren Sie zunächst aus Zelle C3 nach C4:C402 die Formel =RAND(). Anschließend nennen Sie den Bereich C3:C402 Data. Anschließend können Sie in Spalte F den Durchschnitt der 400 Zufallszahlen (Zelle F2) nachverfolgen und die FUNKTION ZÄHLENWENN verwenden, um die Brüche zwischen 0 und 0,25, 0,25 und 0,50, 0,50 und 0,75 sowie 0,75 und 1 zu bestimmen. Wenn Sie F9 drücken, werden die Zufallszahlen neu berechnet. Beachten Sie, dass der Durchschnitt der 400 Zahlen immer ungefähr 0,5 beträgt 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 RAND in verschiedenen Zellen generierten Werte unabhängig sind. Wenn z. B. die in Zelle C3 generierte Zufallszahl eine große Zahl ist (z. B. 0,99), sagt sie uns nichts über die Werte der anderen generierten Zufallszahlen aus.

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 mehrmals ausspielen oder simulieren? Der Trick besteht darin, jeden möglichen Wert der RAND-Funktion einem möglichen Bedarf an Kalendern zuzuordnen. Die folgende Zuweisung stellt sicher, dass ein Bedarf von 10.000 zu 10 Prozent der Zeit auftritt usw.

Nachfrage	Zufallszahl zugewiesen
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 als oder gleich 0,75

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

Der Schlüssel zu unserer Simulation ist die Verwendung einer Zufallszahl, um eine Suche aus dem Tabellenbereich F2:G5 (benannte Suche) zu initiieren. Zufallszahlen größer oder gleich 0 und kleiner als 0,10 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 einen Bedarf 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 Testversionen oder Iterationen des Kalenderbedarfs, indem Sie die Formel SVERWEIS(C3,Suche,2) von B3 nach B4:B402 kopieren. Diese Formel stellt sicher, dass jede Zufallszahl kleiner als 0,10 einen Bedarf von 10.000 generiert, jede Zufallszahl zwischen 0,10 und 0,45 einen Bedarf von 20.000 generiert usw. Verwenden Sie im Zellbereich F8:F11 die Funktion ZÄHLENWENN, um den Anteil unserer 400 Iterationen zu bestimmen, die jeden Bedarf ergeben. Wenn wir F9 drücken, um die Zufallszahlen neu zu berechnen, liegen die simulierten Wahrscheinlichkeiten nahe an unseren angenommenen Bedarfswahrscheinlichkeiten.

Wenn Sie die Formel NORMINV(rand(),mu,sigma) in eine Zelle eingeben, generieren Sie einen simulierten Wert einer normalen Zufallsvariablen mit einem mittleren mu und einer Standardabweichung sigma. Dieses Verfahren wird in der Datei Normalsim.xlsx veranschaulicht, 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 diese Zellen mean bzw . sigma benennen.) Beim Kopieren der Formel =RAND() von C4 nach C5:C403 werden 400 verschiedene Zufallszahlen generiert. Beim Kopieren von B4 nach B5:B403 generiert die Formel NORMINV(C4,Mean,sigma) 400 verschiedene Testwerte 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 nahe bei 40.000 und die Standardabweichung nahe bei 10.000.

Im Wesentlichen generiert die Formel NORMINV(p,mu,sigma) für eine Zufallszahl x das p-tePerzentil einer normalen Zufallsvariablen mit einem mittleren mu und einem Standardabweichungs-Sigma. 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.

In diesem Abschnitt erfahren Sie, wie die Monte-Carlo-Simulation als Entscheidungstool verwendet werden kann. Angenommen, die Nachfrage nach einem Valentinstag 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

Die Begrüßungs-Karte wird für 4,00 USD verkauft, und die variablen Kosten für die Herstellung jedes Karte betragen 1,50 USD. Übrig gebliebene Karten müssen für 0,20 US-Dollar 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) mehrmals (z. B. 1000 Iterationen). Anschließend bestimmen wir, welche Bestellmenge den maximalen durchschnittlichen Gewinn über die 1000 Iterationen ergibt. Die Daten für diesen Abschnitt finden Sie in der Datei Valentine.xlsx, wie in Abbildung 60-4 dargestellt. 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 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 Zellenname, der Zelle C3 zugewiesen ist, nicht die RAND-Funktion.)

Die Anzahl der verkauften Einheiten ist die kleinere Menge und Nachfrage unserer Produktion. 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 erzeugt*unit_prod_cost.

Wenn wir mehr Karten produzieren, als nachgefragt werden, entspricht die Anzahl der übrig gebliebenen Einheiten der Produktion abzüglich des Bedarfs; andernfalls werden keine Einheiten mehr übrig. Wir berechnen unsere Entsorgungskosten in Zelle C10 mit der Formel unit_disp_cost*WENN(produziert>Nachfrage,produziert–Bedarf,0). Schließlich berechnen wir in Zelle C11 unseren Gewinn als Umsatz – total_var_cost-total_disposing_cost.

Wir wünschen uns eine effiziente Möglichkeit, F9 mehrmals (z. B. 1000) für jede Produktionsmenge zu drücken und unseren erwarteten Gewinn für jede Menge zu nennen. In dieser Situation kommt uns eine bidirektionale Datentabelle zur Hilfe. (Details zu Datentabellen finden Sie in Kapitel 15, "Vertraulichkeitsanalyse 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 Versuchen). Eine einfache Möglichkeit, diese Werte zu erstellen, besteht darin, mit der Eingabe von 1 in Zelle A16 zu beginnen. Wählen Sie die Zelle aus, klicken Sie dann auf der Registerkarte Start in der Gruppe Bearbeiten auf Ausfüllen, und wählen Sie Serie aus, um das Dialogfeld Serie anzuzeigen. Geben Sie im Dialogfeld Reihe ( siehe Abbildung 60-6) den Schrittwert 1 und den Stoppwert 1000 ein. Wählen Sie im Bereich Reihe in die Option Spalten aus, und klicken Sie dann auf OK. Die Zahlen 1 bis 1000 werden ab Zelle A16 in Spalte A 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 möchten den Gewinn für jede Testnummer (1 bis 1000) und jede Produktionsmenge berechnen. Wir verweisen auf die Formel für den Gewinn (berechnet in Zelle C11) in der oberen linken Zelle unserer Datentabelle (A15), indem wir =C11 eingeben.

Wir sind jetzt bereit, Excel dazu zu bringen, 1.000 Iterationen der Nachfrage für jede Produktionsmenge zu simulieren. Wählen Sie den Tabellenbereich (A15:E1014) aus, und klicken Sie dann in der Gruppe Datentools auf der Registerkarte Daten auf Was-wäre-wenn-Analyse, und wählen Sie dann Datentabelle aus. Um eine bidirektionale Datentabelle einzurichten, wählen Sie unsere Produktionsmenge (Zelle C1) als Zeileneingabezelle und 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 Spalteneingabezellenwert 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 eingefügt, und die Zufallszahl in C2 wird erneut berechnet. Der entsprechende Gewinn wird in Zelle C17 eingetragen.

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) von 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 Iterationen des Bedarfs für jede Bestellmenge simuliert. Die Produktion von 40.000 Karten bringt immer den größten erwarteten Gewinn. Daher scheint es, dass die Erstellung von 40.000 Karten die richtige Entscheidung ist.

Auswirkungen des Risikos auf unsere Entscheidung      Wenn wir 20.000 statt 40.000 Karten produzierten, sinkt unser erwarteter Gewinn um etwa 22 Prozent, aber unser Risiko (gemessen an der Standardabweichung des Gewinns) sinkt um fast 73 Prozent. Wenn wir dem Risiko also extrem abgeneigt sind, könnte die Erstellung 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, werden wir immer alle ohne Reste verkaufen.

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

In Zelle J11 berechnen Sie die untere Grenze für das Konfidenzintervall von 95 Prozent für den mittleren Gewinn, wenn 40.000 Kalender mit der Formel D13–1,96*D14/SQRT(1000) erzeugt 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 dargestellt.

Wir sind uns zu 95 Prozent sicher, dass unser mittlerer Gewinn bei der Bestellung von 40.000 Kalendern zwischen 56.687 und 62.589 US-Dollar liegt.

1. Ein GMC-Händler geht davon aus, dass die Nachfrage nach 2005 Envoys normalerweise mit einem Mittelwert von 200 und einer Standardabweichung von 30 verteilt wird. Seine Kosten für einen Envoy betragen 25.000 US-Dollar, und er verkauft einen Envoy für 40.000 US-Dollar. Die Hälfte aller Envoys, die nicht zum vollen Preis verkauft werden, kann für $ 30.000 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 zu bestimmen, wie viele Exemplare Personen Magazins sie jede Woche bestellen sollen. Sie glauben, dass ihre Nachfrage nach Personen durch die folgende diskrete Zufallsvariable gesteuert wird:

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

3. Der Supermarkt zahlt $ 1,00 für jede Kopie von Personen und verkauft es für $ 1.95. Jede nicht verkaufte Kopie kann für 0,50 USD zurückgegeben werden. Wie viele Kopien von Personen sollte der Store bestellen?