Monte-Carlo-Simulation in Excel, mit oder ohne Add-In
Für ein Excel-Modell gibt es drei Wege zur Monte-Carlo-Simulation: mit RAND()-Formeln und einer Datentabelle, mit einem Add-In oder mit einem Werkzeug, das die Excel-Datei außerhalb von Excel durchrechnet. Alle drei berechnen das Modell tausendfach neu und ziehen die unsicheren Eingangsgrößen dabei aus Bereichen. Die Ergebnisse lesen Sie als Wahrscheinlichkeiten: die Chance einer Budgetüberschreitung, die Kosten, die Sie mit 80 % Konfidenz nicht überschreiten, die Eingangsgrößen mit dem größten Einfluss.
Warum simulieren, statt eine einzige Zahl zu nehmen
Eine Tabelle rechnet mit einem Satz Eingaben und liefert eine Antwort. Sind die Eingaben Schätzungen, verschweigt diese Antwort, wie weit sie danebenliegen kann, und sie fällt oft zu optimistisch aus: Wer die wahrscheinlichsten Kosten addiert, übersieht, dass Kosten leichter über- als unterschritten werden. Im Beispiel zu Projektkosten wird die Basisschätzung, die Summe der wahrscheinlichsten Kosten, in 94,2 % der simulierten Ergebnisse überschritten. Best Case, Basisfall und Worst Case beheben das nicht: Sie liefern drei Antworten, ohne zu sagen, wie wahrscheinlich die einzelnen sind.
1. Reines Excel ohne Add-In: RAND() und Datentabelle
Excel hat keinen Simulationsbefehl, für ein kleines Modell reichen die Funktionen aber aus:
- Ersetzen Sie jede unsichere Eingangsgröße durch eine Zufallsziehung.
RAND()liefert eine gleichverteilte Zahl zwischen 0 und 1; eine Umkehrfunktion der Verteilung macht daraus einen Wert aus dem gewünschten Bereich (Formeln unten). - Wiederholen Sie die Berechnung. Tragen Sie die Iterationsnummern 1 bis 5.000 in eine Spalte ein und in die Nachbarspalte, eine Zeile über der ersten Nummer, einen Bezug auf die Ausgangszelle. Markieren Sie beide Spalten ab dieser Zeile nach unten, wählen Sie Daten › Was-wäre-wenn-Analyse › Datentabelle und geben Sie als Spalteneingabezelle eine beliebige leere Zelle auf demselben Tabellenblatt an. Jede Zeile berechnet die Datei neu und bekommt deshalb neue Zufallsziehungen.
- Fassen Sie die Spalte zusammen.
AVERAGE,PERCENTILE.INCundCOUNTIF(geteilt durch die Anzahl der Iterationen) ergeben Mittelwert, P80-Wert und die Chance, ein Ziel zu überschreiten; ein Histogramm zeigt die Form der Verteilung.
| Eingangsgröße | Formel |
|---|---|
| Normal (Mittelwert, Standardabweichung) | =NORM.INV(RAND(), mean, sd) |
| PERT (Min, Wahrscheinlich, Max) | =BETA.INV(RAND(), 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max) |
| Dreieck (Min, Wahrscheinlich, Max), mit der Ziehung in U | =IF(U<(ml-min)/(max-min), min+SQRT(U*(max-min)*(ml-min)), max-SQRT((1-U)*(max-min)*(max-ml))) |
| Risikoereignis (Wahrscheinlichkeit p, Kosten c) | =IF(RAND()<p, c, 0) |
Das funktioniert und eignet sich gut zum Lernen. Bei realen Modellen stößt es an Grenzen:
- Eine Datentabelle berechnet für jede Iteration die ganze Datei neu, große Modelle werden deshalb langsam. Mit der Berechnungsoption „Automatisch außer Datentabellen“ lässt sich die Datei zwischendurch weiter bedienen.
RAND()zieht bei jeder Neuberechnung neue Werte; die Ergebnisse ändern sich also jedes Mal, es sei denn, Sie fügen sie als Werte ein.- Für korrelierte Eingangsgrößen, Sensitivitätsanalyse und Berichte brauchen Sie eigene Formeln und Diagramme.
2. Ein Excel-Add-In
Add-Ins wie @RISK, Crystal Ball, Analytic Solver und ModelRisk ergänzen Excel um Verteilungsfunktionen (zum Beispiel eine PERT-Funktion, die Sie in eine Zelle eingeben), führen die Iterationen aus und liefern Diagramme, Sensitivitätsanalysen und Berichte. Sie laufen in Excel. Alle, die damit arbeiten, brauchen daher Excel und das installierte Add-In, was in vielen Unternehmen eine Anfrage bei der IT bedeutet.
3. Ein Werkzeug, das die Datei außerhalb von Excel durchrechnet
Die dritte Möglichkeit ist ein Werkzeug mit eigener Berechnungs-Engine, das die .xlsx-Datei einliest und die Iterationen selbst rechnet. So arbeitet xellstorm, direkt im Browser:
- Öffnen Sie die vorhandene Excel-Datei; sie wird im Browser-Tab berechnet und nie hochgeladen. Eine Kompatibilitätsprüfung vergleicht die Formeln mit den Werten, die Excel gespeichert hat, und markiert jede Zelle, die die Engine nicht berechnen kann.
- Klicken Sie auf der Modellkarte die unsicheren Eingangsgrößen an und geben Sie ihnen Bereiche; ebenso die Ergebnisse, die xellstorm verfolgen soll. Verwendet die Datei bereits
NORM.INV(RAND(), …)oder die gängigen Verteilungsfunktionen von @RISK, ModelRisk oder Analytic Solver, macht xellstorm aus den unterstützten Formeln Eingangsgrößen, deren Parameter mit den Zellen verknüpft bleiben. Auch dieBETA.INV-Formel für eine PERT-Verteilung von oben wird umgewandelt: Die arithmetischen Formparameter bleiben verknüpft und werden vor jedem Lauf ausgewertet (siehe PERT-Verteilung in Excel). - Jeder Lauf nutzt einen Startwert (Seed) des Zufallszahlengenerators; dasselbe Modell liefert deshalb jedes Mal dieselben Zahlen. In den meisten Dateien werden nur die Zellen neu berechnet, die eine Iteration ändert.
Die drei Wege im Vergleich
| Reines Excel | Excel-Add-In | xellstorm | |
|---|---|---|---|
| Installation | Nichts | Ein Add-In in Excel | Nichts: läuft im Browser |
| Verteilungen | Aus Formeln gebaut | Viele, als Funktionen | 48, auch eigene Tabellen |
| Iterationen wiederholen | Eine Datentabelle, die die ganze Datei neu berechnet | Eingebaut | Eingebaut; in den meisten Dateien rechnet es nur neu, was sich ändert |
| Beim nächsten Mal dieselben Zahlen | Nein, außer als Werte eingefügt | Mit festem Seed | Mit Seed, unabhängig von der Zahl der CPU-Kerne |
| Korrelation, Sensitivität, Berichte | Eigene Formeln und Diagramme | Eingebaut | Eingebaut |
So lesen Sie die Ergebnisse
- Mittelwert
- Der Durchschnitt aller simulierten Ergebnisse. Bei schiefen Eingangsgrößen weicht er vom Ergebnis der wahrscheinlichsten Eingangsgrößen ab.
- Perzentile (P10, P50, P80, P90)
- Der Wert, den 10 %, 50 %, 80 % oder 90 % der Ergebnisse nicht überschreiten. P50 ist der Median; P80 und P90 sind gängige Budgetstufen (mehr zu Perzentilstufen).
- Wahrscheinlichkeit eines Ziels
- Der Anteil der Ergebnisse, die eine Bedingung erfüllen, etwa Gesamtkosten über dem Budget.
- Summenkurve (S-Kurve)
- Die Summenwahrscheinlichkeit, aufgetragen über dem Ergebnis: Daran lässt sich jedes Perzentil ablesen, ebenso die Chance, unter einem beliebigen Wert zu bleiben.
- Tornado-Diagramm
- Wie weit sich das Ergebnis bewegt, wenn eine Eingangsgröße von einem niedrigen auf ein hohes Perzentil wechselt, während die übrigen auf dem Median bleiben; die breitesten Balken zeigen die Eingangsgrößen mit dem größten Einfluss (Tornado-Diagramme).
- CVaR (Mittelwert der schlechtesten 5 %)
- Der Mittelwert der schlechtesten 5 % der Ergebnisse: wie schlecht die schlechten Fälle sind, nicht nur, wie oft sie auftreten (Reserve anhand der schlechten Fälle bemessen).
Wie viele Iterationen?
So viele, dass sich die Zahlen, die Sie berichten, nicht mehr verändern. Das Rauschen eines simulierten Mittelwerts sinkt mit der Wurzel aus der Iterationszahl: Bei vierfacher Iterationszahl halbiert es sich. Ein paar Tausend Iterationen sind ein üblicher Anfang; Perzentile weit am Ende der Verteilung wie P95 brauchen mehr als der Mittelwert. xellstorm kann so lange laufen, bis sich Mittelwert und Perzentile innerhalb einer gewählten Toleranz stabilisieren.
Häufige Fragen
Ist eine Monte-Carlo-Simulation dasselbe wie eine Szenarioanalyse?
Nein. Eine Szenarioanalyse berechnet einige ausgewählte Fälle, etwa Best Case, Basisfall und Worst Case, ohne zu sagen, wie wahrscheinlich jeder ist. Eine Simulation zieht Tausende Kombinationen aus den Bereichen und sagt, wie wahrscheinlich jedes Ergebnis ist.
Welche Verteilung eignet sich für eine Schätzung?
Für Expertenschätzungen als Min, Wahrscheinlich und Max: PERT- oder Dreiecksverteilung. Für Größen, die nicht unter null fallen können und ein langes rechtes Ende haben: Lognormalverteilung. Für ein Ereignis, das eintritt oder nicht: eine Wahrscheinlichkeit mit den Kosten im Eintrittsfall. Liegen Daten vor: eine Verteilung daran anpassen.
Muss ich die Datei ändern?
Mit xellstorm nicht: Die Verteilungen liegen neben der Datei, nicht in ihr, und können ihre Zahlen aus vorhandenen Zellen lesen, etwa aus Spalten für Min / Likely / Max. Die Formeln bleiben, wie sie sind. Bei reinem Excel oder mit einem Add-In stehen die Bereiche in der Datei selbst, als Formeln oder als eigene Definitionen des Add-Ins.
Wie erstelle ich eine PERT-Verteilung in Excel?
Excel hat keine PERT-Funktion. Eine PERT-Verteilung ist aber eine Beta-Verteilung, skaliert auf den Bereich von Min bis Max, und BETA.INV zieht daraus: =BETA.INV(RAND(), 1+4*(ml-min)/(max-min), 1+4*(max-ml)/(max-min), min, max); Min, Wahrscheinlich (ml) und Max ersetzen Sie durch Zellbezüge. Der Mittelwert beträgt (Min + 4 × Wahrscheinlich + Max) / 6. PERT-Verteilung in Excel vergleicht sie mit der Dreiecksverteilung.
Kann ich in Excel ohne Add-In eine Monte-Carlo-Simulation ausführen?
Ja. Ziehen Sie jede unsichere Eingangsgröße mit einer Formel wie =NORM.INV(RAND(), mean, sd), wiederholen Sie das Modell mit einer Datentabelle und fassen Sie die Ergebnisse mit AVERAGE, PERCENTILE.INC und COUNTIF zusammen. Oder öffnen Sie die Datei in xellstorm: Dort laufen die Iterationen im Browser, ohne Add-In und ohne Excel.
Verwandte Seiten
- Leitfaden
PERT-Verteilung in Excel: Formel, Mittelwert und Einsatz
Eine PERT-Verteilung ist eine Beta-Verteilung von Min bis Max mit dem Mittelwert (Min + 4 × Wahrscheinlich + Max) / 6. Die Excel-Formel mit BETA.INV und PERT im Vergleich zur Dreiecksverteilung. - Leitfaden
P50, P80 und P90: Bedeutung und die richtige Stufe für das Budget
P80 ist der Betrag, den 80 % der simulierten Ergebnisse nicht überschreiten. Sechs Kostenpositionen eines Bauprojekts haben P80-Werte, die sich auf 2.843 addieren, doch der P80 ihrer Summe beträgt 2.756 (Tsd. USD). - Leitfaden
Tornado-Diagramm und Sensitivitätsanalyse erklärt
Ein Tornado-Diagramm bewegt jede Eingangsgröße einzeln, in xellstorm standardmäßig von P10 bis P90. In einer Bauschätzung verschiebt ein Risiko die Kosten um 150 (Tsd. USD). - Projektkosten und Termine
Wie viel Risikozuschlag braucht ein Bauprojekt?
Praxisbeispiel: In 94,2 % der simulierten Ergebnisse liegen die Kosten über den wahrscheinlichsten Kosten der Bauschätzung. So bemessen Sie den Risikozuschlag mit der Monte-Carlo-Simulation.
xellstorm simuliert Excel-Modelle nach der Monte-Carlo-Methode direkt im Browser: ohne Add-In, und Ihre Datei verlässt Ihren Rechner nie.