Crystal Ball und Monte Carlo Simulation erklärt

Wer möchte nicht gern in die Zukunft schauen? Nur wenige von uns lieben das Risiko und die Unsicherheit. In der alten griechischen Mythologie wurde das Orakel von Delphi um Rat gebeten, wenn wichtige Entscheidungen getroffen werden mussten. Man dachte, das Orakel stände im Kontakt mit den Göttern (Hyperion war einer dieser Götter) und indem das Orakel durch eine Glaskugel (Crystal Ball) schaut, könne es in die Zukunft sehen. Auch heute noch vertrauen einige unter uns einen solchen Ansatz anstatt der Mathematik.

Oracle Crystal Ball (CB) ist ein Analysewerkzeug, mit dem durch Simulation in einer Tabellenkalkulation, Risiko und Unsicherheit quantifiziert und damit auch beherrschbar gemacht werden können. Es ist ein Microsoft Excel Add-In, welches sich die einfache Benutzeroberfläche, die Rechengeschwindigkeit und das einfache Modellieren in Excel zu Nutze macht. Crystal Ball bringt hierzu starke Statistik- und Analysefunktionalität ein. Diese erweitert eine einfache Tabellenkalkulation um wissenschaftliche Mittel.

Nach der Installation erweitert eine zusätzliche Menüleiste MS Excel mit den Kategorien „Define“, „Run“, „Analyze“, „Tools“ und „Help“. (Siehe auch Abbildung 1)

Abbildung 1: Menüleiste von Crystal Ball.

Oracle Crystal Ball enthält drei verschiedene Werkzeuge, um Risikoberechnungen und Prognosen zu machen. Diese sind in der Tabelle 1 aufgeführt.

 

Oracle Crystal Ball
Simulation	Prognoserechnung	Optimierung
Monte Carlo Simulation	Predictor	OptQuest
Wahrscheinlichkeits-berechnung mit Zufallszahlen als Variablen in einem Tabellenkalkulationsmodell.	In der Zeit zurückliegende Perioden werden auf Trends und Saisonbewegungen analysiert. Hieraus werden, auf mathematischer Grundlage, zukünftige Perioden prognostiziert.	Durch die Berechnung aller Optionen mit anschließendem Sortieren der Ergebnisse auf Kriterien wird die beste mathematische Lösung eines Optimierungsproblems gefunden.

Table 1: Werkzeuge in Crystal Ball

In diesem Beitrag werden wir uns mit der Monte Carlo Simulation befassen und die anderen außer Acht lassen.

 

Warum können normale Tabellenkalkulationsmodelle keine Angaben zur Wahrscheinlichkeit machen?

Eine einfache Definition eines Tabellenkalkulationsmodells ist die Übersetzung eines realen Problems in ein Tabellenkalkulationsblatt mit Eingabewerten, einem Ergebnis, Formeln, Funktionen und Daten.

Beispiel eines solchen Modells wäre, wieviel Arbeitstage kostet ein Versionsupgrade? Die verschiedenen Aufgaben würden untereinander aufgelistet sein und zu jedem würde eine Arbeitszeit in Stunden gegeben werden. Die Summe aller Stunden wäre dann die Schätzung der benötigten Zeit.

Dieser traditionelle Ansatz würde mit einer besten Schätzung keine Aussage zur Wahrscheinlichkeit machen. Je erfahrener die Person in der Schätzung ist und je mehr Informationen zur Situation vorliegen, desto besser wird vielleicht die Schätzung im Nachhinein sein.

Für ein Festpreisangebot würde die Person vielleicht das Modell erweitern mit einem „Besten“ und „Schlechtesten“ Szenario und entsprechende Schätzwerte eintragen. Mit etwas mehr Aufwand könnten auch einige “was geschieht, wenn” (what-if) Berechnungen gemacht werden durch Schätzwerte miteinander zu Mischen.

Aber noch immer kann keine Aussage gemacht werden, mit welcher Wahrscheinlichkeit diese Schätzung eintrifft und wieviel Arbeitszeit der Versionsupgrade in Anspruch nehmen wird. Diese Fragen können nicht beantwortet werden, wenn man nicht statistische Mittel einsetzt.

Crystal Ball Modelle berechnen die Wahrscheinlichkeit mit der Monte Carlo Simulation. In diesem Prozess werden Zufallszahlen oder Zufallsvariablen für Eingabewerte in der Tabellenkalkulation generiert und angewendet. Die Ermittlung dieser Zufallsvariablen ist nicht frei, sondern geschieht innerhalb der für diese Variable definierten Wahrscheinlichkeitsverteilung (http://de.wikipedia.org/wiki/Wahrscheinlichkeitsverteilung). Beispiele von Wahrscheinlichkeitsverteilungen sind ersichtlich in Abbildung 2. Die Simulation berechnet in dem Modell eine sehr große Anzahl Szenarien indem immer wieder andere Zufallsvariablen gewählt und in dem Modell miteinander verbunden werden. Durch das „Gesetz der großen Zahlen“ (http://de.wikipedia.org/wiki/Gesetz_der_gro%C3%9Fen_Zahlen) erreicht man mit statistischen Mitteln viele Werte. Diese können dann entsprechend ausgewertet werden.

Abbildung 2: Wahrscheinlichkeitsverteilungen. Links, Normalverteilung, rechts Ja/Nein Verteilung.

In unserem Beispiel des Versionsupgrade würde ein Arbeitspaket der Export aller Daten sein. Dieses würde mit 20 Stunden Aufwand geschätzt. Ein Schätzbereich würde zwischen 18 und 25 Stunden liegen, aber mit der meisten Wahrscheinlichkeit von 20 Stunden. Eine solche Wahrscheinlichkeitsverteilung ist in Abbildung 3 dargestellt.

Abbildung 3: Wahrscheinlichkeitsverteilungen von Arbeitspaket Datenexport (Beta Distribution)

Eine Crystal Ball Simulation umfasst tausende Berechnungen. In jeder Berechnung wird ein Datenwert pro Arbeitspaket aus der jeweiligen Wahrscheinlichkeitsverteilungen genommen und in die Tabellenkalkulation gebracht. Die Summe aller Arbeitspakete wird berechnet und das Ergebnis in die Ergebnistabelle erfasst.

Der Datenwert ist natürlich nicht immer derselbe. Crystal Ball ermittelt jede Zufallsvariable nach der angegebenen Wahrscheinlichkeitsverteilung. Für unser Beispiel wird es einen Wert zwischen 18 und 25 Stunden wählen unter Berücksichtigung der in der Kurve dargestellten Verteilung . Also wird der Wert häufiger rund um die 20 Stunden liegen als um die 23 Stunden.

Anstatt mit drei Szenarien, wie einem „Besten“, einem „Schlechtesten“ und „wahrscheinlichstem“ Szenario zu arbeiten, bekommen wir TAUSENDE Szenarien auf Basis von Zufallszahlen in den Zeitaufwendungen der Arbeitspakete. Diese werden für die Analyse bereitgestellt.

Wie macht man eine Monte Carlo Simulation?

Die Basis einer Monte Carlo Simulation ist ein Tabellenkalkulationsmodell. Dieses enthält

• Eingabewerte, die in Zufallsvariablen umgewandelt werden und
• mindestens eine Berechnung, die das Ergebnis darstellt.

Anhand eines einfachen Modells werde ich die Schritte erklären. Dieses Modell ist dargestellt in Tabelle 2.

Arbeitspaket	Aufwand in Stunden
Installation auf neuer Hardware	60
Übertrag der Repositories	10
Datenexport	20
Datenimport	10
Testen	10
Summe Aufwand	110

Tabelle 2: Upgrade Modell

Beim Aufbau des Modells sind die Eingabewerte noch nicht wichtig. Diese werden ja später mit Wahrscheinlichkeitsverteilungen ersetzt. Empfehlung ist, mit einer einfachen Struktur zu beginnen, denn später kann das Modell leicht ausgebaut werden.

Wenn das Modell fertig ist, werden zuerst die unveränderlichen Eingabewerte identifiziert, weil diese für eine Modellierung nicht sinnvoll sind. Zum Beispiel die Mehrwertsteuer, denn dieser Prozentsatz wird sich nicht ändern und eine Simulation mit einem Satz von 18% und dann wieder 22% ist nicht sinnvoll.

Annahmen (Assumptions)

Im nächsten Schritt werden die veränderlichen Eingabewerte mit Annahmen und einer Wahrscheinlichkeitsverteilung versehen. In Crystal Ball heißen diese „Assumptions“. In unserem Beispiel wählen wir für alle Eingaben eine „Dreieck-Verteilung“ mit einem minimalen-, einem maximalen- und einem wahrscheinlichsten Schätzwert. Siehe hierzu auch Tabelle 3.

Arbeitspaket	Aufwand in Stunden			Verteilung
	Wahrscheinlichster Schätzwert	Minimum	Maximum
Installation auf neuer Hardware	60	40	80	Dreieck (Triangular)
Übertrag der Repositories	10	8	12	Dreieck (Triangular)
Datenexport	20	18	25	Dreieck (Triangular)
Datenimport	10	9	12	Dreieck (Triangular)
Testen	10	5	12	Dreieck (Triangular)
Summe Aufwand	110	80	141

Tabelle 3: Upgrade Modell mit Variablen

Mit einem anderen Verteilungstyp sähe das Arbeitspaket “Datenexport” ganz anders aus und würde auch andere Ergebnisse hervorbringen. Crystal Ball hat mehr als 40 verschiedene Verteilungstypen, und es kann mit eigenen Daten auch ein eigener Verteilungstyp erstellt werden.

Abbildung 4: Wahrscheinlichkeitsverteilungen von Arbeitspaket Datenexport (Triangular distribution)

Mit Zellenreferenzen können die Eingabeparameter auf dem Excel abgelegt und für die Analysten sichtbar gemacht werden.

Zielwert (Forecast value)

Ein Modell muss mindestens einen Zielwert haben, es können aber mehrere definiert werden. Die Zielwerte haben immer eine Formel und sind abhängig von anderen Formeln und Eingabewerte.

In unserem Beispiel aus Tabelle 3 ist die „Summe Aufwand“ der Zielwert. Wie wir in der Tabelle sehen können, liegt der Wert irgendwo zwischen 80 Stunden und 141 Stunden. Der meist wahrscheinliche Wert ist 110 Stunden. Was wird die Simulation zeigen? Was wird die Wahrscheinlichkeit sein, dass der Wert, sagen wir mal, unter 120 Stunden liegt?

 

Simulation

Wir setzen die Simulation auf 10.000 Berechnungen. In jeder Berechnung wird Crystal Ball einen zufälligen Wert aus der Wahrscheinlichkeitsverteilung jeder Eingabe erstellen und in das Excel Modell übergeben. Danach wird der Zielwert „Summe Aufwand“ berechnet und in die Tabelle mit den Lösungen geschrieben. Dann fängt die nächste Berechnung auf diselbe Art und Weise an.

Dieses geht solange, bis die eingestellten 10.000 Berechnungen gemacht sind und es 10.000 Werte in der Ergebnistabelle gibt.

 

Das Ergebnis der Simulation ist dargestellt in Abbildung 5. Hier zeigt sich, dass in 14,45% der Fälle, der Upgrade mehr als 120 Stunden benötigt.

Abbildung 5: Frequenzverteilung der Simulation.

Crystal Ball berechnet verschiedene andere statistische Kennzahlen wie in Abbildung 6 ersichtlich ist.

Abbildung 6: Statistische Kennzahlen aus der Simulation.

Eine interessante Grafik ist die “sensitivity chart”. Diese zeigt, welche Eingabegröße den größten Einfluss auf die Varianz des Ergebnisses hat. Dieses liegt an der absoluten Größe der Zahl, aber auch an ihrer Wahrscheinlichkeitsverteilung.

Abbildung 7: Sensitivity chart, welche Zufallsvariable den größten Einfluss auf Veränderungen des Endergebnisses hat.

Abbildung 8: Ergebniswerte von den 10.000 Berechnungen.

Ergebnisanalyse

Die Ergebnisse haben immer einen direkten Bezug auf die Eingabe. Deshalb gilt auch hier die Regel “garbage in, garbage out”, und Crystal Ball zaubert nicht, es rechnet nur.

Das ist auch der Grund, dass sich eine erfahrene Person oder Personenkreis mit Fach- und Statistikwissen mit dem Modell befassen sollte. Mit Sorgfalt sollte die Auswahl der Wahrscheinlichkeitsverteilungen geschehen sowie der Werte, die diese definieren.

Es ist wichtig, diese Annahmen zu speichern, um zu einem späteren Zeitpunkt die Daten zu validieren, Änderungen an dem Modell vorzunehmen oder den Hintergrund der Ergebnisse zu klären. Weil die Werte schon in Excel sind, können sie auch in Essbase, Planning oder HFM geladen werden.

Wenn wir sehen, dass mit einer Wahrscheinlichkeit von 15% der Upgrade mehr als 120 Stunden Arbeit kostet, dann ist es seine Managemententscheidung, ob das Festpreisangebot auf dieser Anzahl Stunden basiert wird oder nicht. Wenn das Management aber noch eine Sicherheitsmarge auf diese Stunden legt, dann sollte das Prinzip von Crystal Ball und Monte Carlo Simulation (nochmals) erklärt werden.

Wichtig im Management ist auch zu wissen, welche Größen den größten Einfluss auf die Variabilität des Ergebnisses haben. In unserem Beispiel wäre eine detaillierte Kontrolle des Arbeitsschrittes „Erfolgreicher Datenimport“ weniger sinnvoll als das Monitoring des Arbeitsschrittes „Erfolgreiche Installation“.

 

Und jetzt?

Haben sie ein Projekt welches zum Festpreis angeboten werden soll? Haben sie ein Tabellenkalkulationsmodell, wo sie mit einem „Besten“ und „Schlechtesten“ Szenario arbeiten und sich doch noch unsicher fühlen?

Für weniger als € 1.000 erhalten sie schon die erste Crystal Ball Lizenz zur Lösung dieser Probleme!

Ihr Philip Hulsebosch

nach oben