Einführung in Monte Carlo Simulation in Excel

Hinweis:  Wir möchten Ihnen die aktuellsten Hilfeinhalte so schnell wie möglich in Ihrer eigenen Sprache bereitstellen. Diese Seite wurde automatisiert übersetzt und kann Grammatikfehler oder Ungenauigkeiten enthalten. Unser Ziel ist es, Ihnen hilfreiche Inhalte bereitzustellen. Teilen Sie uns bitte über den Link am unteren Rand dieser Seite mit, ob die Informationen für Sie hilfreich sind. Hier finden Sie den englischen Artikel als Referenz.

In diesem Artikel wurde basiert auf Microsoft Excel Data Analysis and Business Modeling von Wayne L. Winston.

  • Wer Monte Carlo Simulation verwendet?

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

  • Wie können Sie die Werte in eine diskrete Zufallsvariable simulieren?

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

  • Wie kann eine Grußkarte Unternehmen wie viele Karten zu ermitteln?

Wir möchten genau die Wahrscheinlichkeit von nicht eindeutigen Ereignisse schätzen. Was ist beispielsweise die Wahrscheinlichkeit, dass ein neues Produkt Zahlungen einen positiven Nettobarwert (NBW) hat? Was ist die Risikofaktor unseres Portfolios Investition? Monte Carlo Simulation kann wir zum Modell Situationen, in denen Ungenauigkeit präsentieren und dann auf einem Computer Tausendertrennzeichen an, wie oft sich wiedergeben.

Hinweis: Der Name stammt Monte Carlo Simulation aus den Computer Simulationen ausgeführt werden, während Sie den 1930er Jahren und 1940s, um die Wahrscheinlichkeit schätzen, die der Kette Reaktion erforderlich für ein Atom Bombe zu detonate erfolgreich arbeiten möchten. Die verbindet diese Arbeit Physiker wurden große Fans von Spiele, damit diese den Simulationen den Code Namen Monte Carlogegeben hat.

In den nächsten fünf Kapitel finden Sie Beispiele, wie Sie Excel verwenden können, um Monte Carlo-Simulationen auszuführen.

Viele Unternehmen verwenden Monte Carlo Simulation als ein wichtiger deren Entscheidungsprozess. Hier sind einige Beispiele für ein.

  • General Motors, Proctor und Gamble, Pfizer, Bristol-Myers Squibb und Eli Lilly verwenden Simulation, um den Risikofaktor neuer Produkte und die durchschnittliche Rendite schätzen. Bei konzipiert ist diese Informationen von der Geschäftsführer verwendet, um zu bestimmen, welche Produkte vermarkten hier.

  • Konzipiert verwendet Simulation für Aktivitäten wie prognostizieren nettoeinzahlungen für das Unternehmen, Vorhersage strukturelle und Einkauf Kosten und deren Anfälligkeit für unterschiedliche Arten von Risiken (z. B. Zinssatz Änderungen und Kurs) zu bestimmen.

  • Lilly wird mit Simulation bestimmt die optimale produziert für jede Medikamenten.

  • Proctor und Gamble verwendet Simulation modellieren und optimal hedge Fremd Exchange Risiko.

  • Sears Simulation verwendet, um zu bestimmen, wie viele Einheiten jeder Zeile Produkt aus Lieferanten geordnet werden sollen – beispielsweise die Anzahl der Paare von Dockers etwa lange Hosen, die dieses Jahr sortiert werden soll.

  • Oil und Medikamenten Unternehmen verwenden Simulation Wert "real-Optionen", beispielsweise der Wert eine Option zum Erweitern, Vertrag oder ein Projekts zu verzögern.

  • Finanzmathematik Planer mit Monte Carlo Simulation können um optimale Investitionsstrategien für ihren Kunden Rente zu bestimmen.

Wenn Sie die Formel =RAND() in eine Zelle eingeben, erhalten Sie eine Zahl, die gleichmäßig wahrscheinlich einen beliebigen Wert zwischen 0 und 1 angenommen wird. Auf diese Weise sollte ungefähr 25 % der Zeit, einer Zahl kleiner oder gleich 0,25 werden; ist ungefähr 10 Prozent der Zeit, die sollten Sie einer Zahl erhalten, mindestens 0,90 usw.. Um zu veranschaulichen, wie die Funktion ZUFALLSZAHL funktioniert, sehen Sie sich die Datei Randdemo.xlsx, in der Abbildung 60-1 angezeigt.

Abbildung eines Buchs
Abbildung 60-1 zur die RAND-Funktion

Hinweis: Wenn Sie die Datei Randdemo.xlsx öffnen, sehen Sie nicht dieselben Zufallszahlen dargestellt in Abbildung 60-1. Die RAND-Funktion wird die Zahlen, die es generiert, wenn ein Arbeitsblatt geöffnet wird oder wenn neuer Informationen in das Arbeitsblatt eingegeben wird immer automatisch neu berechnet.

Zuerst Kopieren von Zelle C3 in C4:C402 der Formel =RAND(). Geben Sie einen Namen im Bereich C3:C402 Daten. Klicken Sie dann können Sie in Spalte D, den Mittelwert der 400 Zufallszahlen (Zelle F2) nachverfolgen und verwenden die Funktion ZÄHLENWENN Bruchzahlen bestimmen, die zwischen 0 und 0,25, 0,25 und 0,50, 0,50 und 0,75, und 0,75 und 1 sind. Wenn Sie die Taste F9 drücken, werden die Zufallszahlen neu berechnet. Beachten Sie, dass der Mittelwert der Zahlen 400 immer ist ungefähr 0,5 und ungefähr 25 % der Ergebnisse in Intervallen von 0,25 sind. Diese Ergebnisse sind mit der Definition eine Zufallszahl konsistent. Beachten Sie auch, dass die Werte von Zufallszahl in verschiedenen Zellen generiert unabhängig sind. Wenn generiert Zufallszahl in Zelle C3 wird eine große Anzahl (z. B. 0,99), wir erfahren nichts zu den Werten, die andere Zufallszahlen generiert.

Nehmen Sie an, dass die Anforderung für einen Kalender, die folgenden diskrete Zufallsvariable unterliegt:

Bei Bedarf

Wahrscheinlichkeit

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Wie können wir, dass Excel heraus wiederzugeben oder simulieren, diese bei Bedarf für Kalender oft? Der Trick besteht darin, jeden möglichen Wert von der Funktion Zufallszahl mit möglichen Demand für Kalender zugeordnet werden soll. Die folgende Zuordnung wird sichergestellt, dass eine Anforderung von 10.000 wird 10 Prozent der Zeit vorkommen, usw..

Bei Bedarf

Zufallszahl zugewiesen ist

10.000

Kleiner 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

Um die Simulation von Demand veranschaulichen, schauen Sie sich die Datei Discretesim.xlsx, in der Abbildung 60-2 auf der nächsten Seite angezeigt.

Abbildung eines Buchs
Abbildung 60-2 eine diskrete Zufallsvariable simulieren

Die EINGABETASTE, um unser Simulation besteht darin, eine Zufallszahl verwenden ein Nachschlagefelds aus dem Tabellenbereich (mit dem Namen Nachschlagen) F2:G5 einleiten. Größer als oder gleich 0 und kleiner als 0,10 Zufallszahlen werden eine Anforderung von 10.000 Rendite; größer als oder gleich 0,10 und kleiner als 0.45 Zufallszahlen werden eine Anforderung von 20.000 Rendite; größer als oder gleich 0.45 und kleiner als 0,75 Zufallszahlen werden eine Anforderung von 40.000 Rendite; und Zufallszahlen größer als oder gleich 0,75 werden eine Anforderung 60.000 Rendite. Sie generieren 400 Zufallszahlen durch Kopieren von C3 auf C4:C402 der Formel ZUFALLSZAHL (). Sie generieren dann 400 Versuche oder Iterationen, der Kalender Demand durch Kopieren aus B3 in B4:B402 die Formel VLOOKUP(C3,lookup,2). Mit dieser Formel wird sichergestellt, dass alle Zufallszahl kleiner als 0,10 generiert eine Anforderung von 10.000, eine Zufallszahl zwischen 0,10 und 0.45 generiert eine Anforderung der 20.000 usw.. Verwenden Sie die Funktion ZÄHLENWENN in den Zellbereich F8:F11 Anteil unsere 400, so dass jede Demand Iterationen zu bestimmen. Wenn wir drücken Sie F9, um die Zufallszahlen neu zu berechnen, dicht simulierten Wahrscheinlichkeiten unsere Wahrscheinlichkeiten angenommenen bei Bedarf an.

Wenn Sie in eine beliebige Zelle die Formel NORMINV(rand(),mu,sigma)eingeben, werden Sie simulierten Wert einer normalen Zufallsvariable mit einem Mittelwert und Standardabweichung Sigmagenerieren. Dieses Verfahren wird in der Datei Normalsim.xlsx, angezeigt in der Abbildung 60-3 dargestellt.

Abbildung eines Buchs
Abbildung 60-3 eine normale Zufallsvariable simulieren

Nehmen wir an, dass wir 400 Versuche oder Iterationen für eine normale Zufallsvariable mit den Mittelwert 40.000 und eine Standardabweichung von 10.000 simulieren möchten. (Sie können Geben Sie diese Werte in den Zellen E1 und E2 ein, und nennen Sie diese Zellen Mittelwert und Sigma.) Kopieren die Formel =RAND() von C4 in C5:C403 generiert 400 andere Zufallszahlen. Kopieren von B4 in B5:B403 die Formel NORMINV(C4,mean,sigma) generiert 400 unterschiedliche Testversion Werte aus einer normalen Zufallsvariable mit den Mittelwert 40.000 und eine Standardabweichung von 10.000. Wenn wir die F9 drücken, um die Zufallszahlen neu zu berechnen, bleibt das Mittel ähnlich 40.000 und die Standardabweichung in der Nähe 10.000.

Im Wesentlichen wird die Formel NORMINV(p,mu,sigma) für eine Zufallszahl X, das p-Quantil von einer normalen Zufallsvariable mit einem Mittelwert und eine Standardabweichung Sigmageneriert. Zum Beispiel (siehe Abbildung 60-3) generiert die Zufallszahl 0.77 in Zelle C4 in die Zelle B4 etwa das 77th Quantil einer normalen Zufallsvariable mit den Mittelwert 40.000 und eine Standardabweichung von 10.000.

In diesem Abschnitt sehen Sie, wie ein Entscheidung Tool Monte Carlo Simulation verwendet werden kann. Nehmen Sie an, dass die Anforderung einer Visitenkarte Valentinstag, die folgenden diskrete Zufallsvariable unterliegt:

Bei Bedarf

Wahrscheinlichkeit

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Die Grußkarte für 4,00 $ verkauft und der Variablen Stückkosten der Herstellung jede Karte ist $1.50. Verbleibende Karten müssen zu Kosten 0,20 US-Dollar pro Karte freigegeben werden. Wie viele Karten gedruckt werden soll?

Im Wesentlichen simulieren wir jede mögliche Herstellung Menge (10.000, 20.000, 40.000 oder 60.000) oft (z. B. 1000 Iterationen). Klicken Sie dann bestimmen wir, welche Bestellmenge den maximalen durchschnittlichen Gewinn über die Iterationen 1000 ergibt. Sie können die Daten für diesen Abschnitt in der Datei Valentine.xlsx, angezeigt in der Abbildung 60-4 suchen. Sie weisen die Bereichsnamen in Zellen B1:B11 zu C1:C11 Zellen ein. Der Zellbereich G3:H6 erhält den Namen Nachschlagen. Unsere Verkaufspreis und Kosten Parameter werden in Zellen C4:C6 eingegeben.

Abbildung eines Buchs
Abbildung 60-4 Valentinstag Karte simulation

Sie können eine Testversion Herstellung Menge (40.000 in diesem Beispiel) in Zelle C1 eingeben. Erstellen Sie anschließend eine Zufallszahl in Zelle C2 mit der Formel =RAND()ein. Wie zuvor beschrieben simulieren Sie bei Bedarf die Karte in Zelle C3 mit der Formel VLOOKUP(rand,lookup,2). (In der SVERWEIS-Formel ist Rand den Zellennamen in Zelle C3, nicht aber die Funktion Zufallszahl zugewiesen.)

Die Anzahl der Einheiten verkauft ist die kleinere unserer Herstellung Menge und bei Bedarf. Klicken Sie in Zelle C8 berechnen Sie unseren Umsatz mit der Formel MIN (erzeugten, bei Bedarf) * Unit_price. Klicken Sie in Zelle C9, die Sie berechnen gesamten Kosten mit der Formel gefertigt * Unit_prod_cost.

Wenn wir mehr Karten als Nachfrage erzeugen, übrig die Anzahl der Einheiten gleich Herstellung minus Demand; Andernfalls werden keine Einheiten übrig. Wir unsere Beseitigung Kosten in die Zelle C10 mit der Formel berechnet Unit_disp_cost * IF (gefertigt > Demand, gefertigt – Demand, 0). Schließlich berechnen in Zelle C11 wir unseren Gewinn als Umsatz – Total_var_cost-Total_disposing_cost.

Wir möchte eine effiziente Möglichkeit zum Drücken F9 oft (z. B. 1000) für jede einzelne Menge Herstellung und zu unseren erwarteten Gewinn für jede einzelne Menge berechnen. Diese Situation ist eine der in der bidirektionale Datentabelle unsere Rettung stammt. (Siehe Kapitel 15, "Vertraulichkeit Analyse mit Datentabellen," Details Datentabellen.) In diesem Beispiel verwendete Datentabelle wird in der Abbildung 60-5 angezeigt.

Abbildung eines Buchs
Abbildung 60-5 bidirektionale Datentabelle für Grußkarte simulation

Geben Sie den Zellbereich A16:A1015 die Zahlen 1 – 1000 (entspricht unserer Versuche 1000). Eine einfache Möglichkeit, diese Werte erstellen besteht darin durch Eingeben von 1 in Zelle A16 starten. Markieren Sie die Zelle, und klicken Sie auf der Registerkarte Start in der Gruppe Bearbeiten auf Ausfüllen, und wählen Sie die Reihe im Dialogfeld Serie angezeigt werden. Geben Sie im Dialogfeld Serie, angezeigt in Abbildung 60 bis 6 Schrittwert 1 und 1000 Wert beenden aus. Klicken Sie im Bereich Datenreihen In wählen Sie die Option Spalten, und klicken Sie dann auf OK. Können ich die Zahlen 1 – 1000 eingegeben in Spalte einer beginnend in Zelle a16 angezeigt.

Abbildung eines Buchs
Abbildung 60-6: verwenden das Dialogfeld Serie die Testversion Zahlen 1 bis 1000 ausfüllen

Geben Sie als Nächstes unsere möglichen Herstellung Mengen (10.000, 20.000, 40.000, 60.000) in Zellen B15:E15. Wir für jede Testversion Zahl (1 bis 1000) Gewinn berechnen möchten und jeder Herstellung Menge. Wir verweisen auf die Formel für Profit (in berechneten Zelle C11) in die obere linke Zelle unsere Datentabelle (A15) = C11eingeben.

Wir können nun Trick Excel in 1.000 Iterationen der Demand für jede einzelne Menge der Herstellung simulieren. Markieren Sie den Tabellenbereich (A15:E1014), und klicken Sie dann in der Gruppe Datentools auf der Registerkarte Daten auf was-wenn-Analyse, und wählen Sie dann auf Datentabelle. Um eine bidirektionale Datentabelle eingerichtet haben, wählen Sie unsere Herstellung Menge (Zelle C1) wie die Zelle der Zeile Eingabe und eine leere Zelle (Wir haben Zelle I14) wie die Zelle der Spalte Eingabe. Nachdem Sie auf OK, simuliert Excel 1000 bei Bedarf Werte für jede Bestellmenge.

Um zu verstehen, warum das funktioniert, erwägen Sie die Werte durch die Datentabelle im Zellbereich C16:C1015 platziert. Für jede dieser Zellen wird Excel 20.000 der Wert in Zelle C1 verwendet. In C16 wird der Spalte Eingabefeld Wert 1 platziert, in eine leere Zelle und Zufallszahl in Zelle C2 wird neu berechnet. Der entsprechende Gewinn wird in Zelle C16 dann aufgezeichnet. Klicken Sie dann in eine leere Zelle der Spalte Eingabewert Zelle von 2 platziert wird, und die Zufallszahl in C2 erneut neu berechnet wird. Der entsprechende Gewinn wird in Zelle C17 eingegeben.

Durch das Kopieren von Zelle B13 zu C13:E13 die Formel AVERAGE(B16:B1015), berechnen wir durchschnittliche simulierten Gewinn für jede einzelne Herstellung Menge. Durch das Kopieren von Zelle B14 zu C14:E14 die Formel STDEV(B16:B1015), berechnen wir die Standardabweichung unsere simulierten Gewinne für jede Bestellmenge. Jedes Mal wir drücken Sie F9, sind 1.000 Iterationen der Demand für jede Bestellmenge eines simulierten. Erstellen von Karten 40.000 immer ergibt den größten erwarteten Gewinn. Daher scheint es, dass erzeugt 40.000 Karten die richtige Entscheidung ist.

Die Auswirkung von Risiken auf unserer Entscheidung     Wenn wir 20.000 anstelle von 40.000 Karten erstellt, unsere erwarteten Gewinn legt ab, etwa 22 Prozent, aber unsere Risiken (wie durch die Standardabweichung des Gewinns gemessen) legt fast 73 Prozent ab. Also, wenn wir extrem Mengen zu Risiken sind, kann erzeugt 20.000 Karten die richtige Entscheidung befinden. Übrigen enthält die 10.000 Karten immer erzeugt eine Standardabweichung von 0 Karten, da Wenn wir 10.000 Karten erstellen, immer aller Folien ohne alle Leftovers verkauft wird.

Hinweis: In dieser Arbeitsmappe wird die Berechnungsoption auf Automatisch außer bei Tabellen festgelegt. (Verwenden Sie der Befehl ' Berechnung ' in der Gruppe Berechnung auf der Registerkarte Formeln.) Diese Einstellung wird sichergestellt, dass unsere Datentabelle nicht neu berechnet wird, es sei denn, wir drücken Sie F9, welche ist eine gute Idee, da Ihre Arbeit eine Datentabelle mit großen verlangsamen wird, wenn es neu berechnet wird jedes Mal, wenn Sie etwas in Ihrem Arbeitsblatt eingeben. Beachten Sie, dass in diesem Beispiel, sobald Sie F9 drücken der Mittelwerte Gewinn geändert wird. In diesem Fall, da jedes Mal Sie F9 drücken, eine andere Folge von 1000 Zufallszahlen zur Abnahme für jede Bestellmenge generieren verwendet wird.

Konfidenzintervall für den Mittelwert Gewinn     Eine natürliche Frage in diesem Fall ist, in welchen Intervall sind wir 95 Prozent sicher, dass der WAHR Mittelwerte Gewinn liegen wird? Dieses Intervall heißt das Konfidenzintervall für den Mittelwert Profit 95 Prozent. 95 Prozent Konfidenzintervalls für den Mittelwert einer Simulation Ausgabe wird durch die folgende Formel berechnet:

Abbildung eines Buchs

Klicken Sie in Zelle J11 berechnen Sie die Untergrenze für das Konfidenzintervall 95 Prozent auf Mittelwert Profit Wenn 40.000 Kalender mit der Formel D13–1.96*D14/SQRT(1000)gefertigt wurden. Klicken Sie in Zelle J12 berechnen Sie die Obergrenze für unsere 95 % Konfidenzintervall mit der Formel D13+1.96*D14/SQRT(1000). Diese Berechnungen werden in der Abbildung 60-7 angezeigt.

Abbildung eines Buchs
Abbildung 60-7 95 % Konfidenzintervall für Mittelwert Profit Wenn 40.000 Kalender angeordnet sind

Es tut 95 Prozent sicher, dass unsere Mittelwert Profit Wenn 40.000 Kalender angeordnet werden zwischen $56,687 und $62,589 ist.

  1. Eine GMCH Händler angenommen wird, dass bei Bedarf für 2005 Envoys mit den Mittelwert 200 und die Standardabweichung der 30 normalverteilt sein wird. Seine Kosten Erhalt einer Envoy ist 25.000 US-Dollar, und er eine Envoy für 40.000 US-Dollar verkauft. Um alle Envoys, die nicht zu vollständigen Preis verkauft kann für 30.000 € verkauft werden. Er ist die Sortierung 200, 220, 240, 260, 280 oder 300 Envoys in Betracht ziehen. Wie viele sollte er bestellen?

  2. Eine kleine Supermarkt versucht, wie viele Kopien von Personen magazine zu ermitteln, die sie jede Woche bestellen sollten. Glauben, deren Demand für Personen , die folgenden diskrete Zufallsvariable unterliegt:

    Bei Bedarf

    Wahrscheinlichkeit

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Die Supermarkt auszahlt $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 im Store sollte Reihenfolge?

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.

Ihre Office-Fähigkeiten erweitern
Schulung erkunden
Neue Funktionen als Erster erhalten
An Office Insider teilnehmen

War diese Information hilfreich?

Vielen Dank für Ihr Feedback!

Vielen Dank für Ihr Feedback. Es klingt, als ob es hilfreich sein könnte, Sie mit einem unserer Office-Supportmitarbeiter zu verbinden.

×