Monte-Carlo Simulation mit Risk Kit

Monte-Carlo Simulationen spielen eine immer bedeutender werdende Rolle der Finanzwirtschaft, den Sozialwissenschaften und im Risk Management. Monte-Carlo Simulationen sind eine generische Methode, um die Wahrscheinlichkeitsverteilungen beliebiger Zielgrößen in komplexen Umfeldern zu schätzen, hierzu zählen z.B. Gewinnverteilungen, Unternehmenswertverteilungen, Portfolioverlustverteilungen und allgemein die Verteilung jeder einem Zufall unterworfenen Zielgröße.

Monte-Carlo Simulationen in der Praxis einzusetzen ist jedoch oft gravierenden Schwierigkeiten unterworfen, wie etwa der Notwendigkeit Zufallszahlen zu erzeugen, ein Modell zu programmieren und die Simulationsergebnisse auszuwerten. Die Lösung dieser Probleme erfordert die Handhabung mathematischer Methoden ebenso wie von moderner Computertechnologie.

Risk Kit ist ein Werkzeug für die schnelle und einfache Erstellung von Risikoanalysen und Monte-Carlo Simulationen. Es stellt die für quantitatives Risikomanagement erforderlichen Komponenten zur Verfügung und es ermöglicht sie in einfacher und intuitiver Weise zu vollständigen Modellen und Risikoanalysen zu kombinieren. Risk Kit bietet vielen Anwendern einen effizienten Zugang zu quantitativem Risikomanagement und Monte-Carlo Simulationen. Es beschleunigt die Entwicklungszeit um ein Vielfaches im Vergleich zu konventionellen Ansätzen.

Alle Risk Kit Funktionen können als Zellenfunktionen in Microsoft Excel in die Tabellenkalkulation integriert werden. Darüber hinaus können Risk Kit Funktionen von VBA aus aufgerufen oder als .NET Assembly in Produktionsumgebungen eingebunden werden.

Der vorliegende Text beschreibt an einem Beispiel wie Monte-Carlo Simulationen und Risikoanalysen mit Risk Kit durchgeführt werden können^¹.

2.Vorstellung von Risk Kit

To top

Risk Kit ist ein Addin für Microsoft Excel. Nach der Installation steht es über die ‚Risk Kit Toolbar‘ in Excel zur Verfügung.

Abbildung 1 – Die Risk Kit Toolbar in Microsoft Excel

Darüber hinaus beinhaltet das Kontextmenü der Zellen ein Untermenü ‘Risk Kit’, das ebenfalls die Funktionen der Toolbar enthält:

Abbildung 2 – Kontextmenü der Zellen

Die folgende Tabelle beschreibt alle Buttons der Risk Kit Toolbar:

Button	Beschreibung
	Öffnet den Funktionsdialog zur Auswahl von Risk Kit-Zellfunktionen.
	Öffnet die Galerie der eindimensionalen Verteilungen, in der Sie eine Verteilung auswählen können, um Zufallsvariablen zu ziehen oder die Dichte, Massenfunktion, Verteilungsfunktion oder inverse kumulative Verteilungsfunktion zu berechnen.
	Öffnet die Liste der Funktionen für stochastische Prozesse.
	Öffnet die Liste der statistischen Funktionen.
	Öffnet die Liste der allgemeinen Funktionen, z. B. zum Erstellen von Plots.
	Öffnet zusätzliche Funktionen, gruppiert nach multivariaten Verteilungen, Ratingverfahren und Zinsrechnung.
	Öffnet die zuletzt verwendeten Funktionen.
	Öffnet den Kalibrierungsdialog, der es erlaubt, univariate und multivariate Verteilungen auf empirische Daten zu kalibrieren.
	Markiert eine Zelle als Eingabezelle.
	Markiert eine Zelle als Ausgabezelle.
	Markiert eine Zelle als Grafikzelle
	Markiert eine Zelle als Prozesszelle
	Öffnet den Zellmanager, um Risk Kit-Funktionszellen in den offenen Arbeitsmappen anzuzeigen.
	Öffnet den Konfigurationsdialog, der die Einstellung von Simulationsparametern, das Ändern der Sprache der Benutzeroberfläche und anderes ermöglicht.
	Startet die Simulation.
	Macht einen Einzelschritt in der Simulation.
	Setzt die Simulation fort. Der Button ist aktiviert, wenn die Simulation pausiert.
	Bricht die Simulation ab. Der Button ist aktiviert, wenn die Simulation pausiert.
	Öffnet den Dialog zur Sensitivitätsanalyse.
	Der Button stellt eine Dropdown-Liste dar, die aus sechs Elementen besteht: 'Diagrammfenster anordnen' - Ordnet die Fenster so an, dass alle Diagramme auf einmal sichtbar sind; Chart-Fenster kaskadieren" - kaskadiert die Chart-Fenster hintereinander;: ‘Grafikfenster schließen’ – Schließt alle Grafikfenster; ‘Grafiken aus dem aktiven Arbeitsblatt löschen’ – Löscht die eingebetteten Grafiken aus dem aktiven Arbeitsblatt; ‘Grafiken aus der aktiven Arbeitsmappe löschen’ – Löscht die eingebetteten Grafiken aus der aktiven Arbeitsmappe; ‘Grafiken aus allen offenen Arbeitsmappen löschen’ – Löscht die eingebetteten Grafiken aus allen offenen Arbeitsmappen.
	Enthält eine Vielzahl von Beispielarbeitsmappen für den schnellen Einstieg in den Umgang mit Risk Kit.
	Öffnet die Add-In-Tools von Risk Kit, um die Online-Hilfe zu öffnen, die Systemsprache zwischen Englisch, Französisch und Deutsch zu ändern, Tastenkombinationen zu erstellen, Feedback an die Entwickler von Risk Kit zu senden, Risk Kit neuen Benutzern zu empfehlen, Probleme zu lösen und Informationen über die verwendete Risk Kit-Version zu erhalten.

Um eine Monte-Carlo Simulation eines bisher deterministischen Modells mit Risk Kit durchzuführen, gehen wir in vier Schritten vor. Zunächst müssen ein oder mehrere Inputs des Modells identifiziert werden, die in der Simulation zu Zufallsvariablen werden. Ihr bisheriger deterministischer Wert wird in der Simulation durch Zufallszahlen ersetzt, die sich in jedem Simulationslauf ändern. Durch diese Variablen kommt die Unsicherheit in das Modell.

Zweitens müssen die Modellergebnisse oder die Zielgrößen definiert werden. Sie werden aus den zufälligen Inputs und ggf. anderen Eingangsgrößen im Kontext des Modells berechnet.

Drittens muss eine Anzahl Simulationsläufe gewählt werden, so dass, viertens, die Simulation gestartet werden kann.

3.Eine Gewinn- und Verlustsimulation

To top

3.1.Ein deterministisches Modell als Ausgangspunkt

Um ein Beispiel zu geben, wie ein deterministisches Modell zu einer Monte-Carlo Simulation erweitert wird, betrachten wir eine einfache Gewinn- und Verlustrechnung.

Abbildung 3 - Deterministische Gewinn- und Verlustrechnung

Um Gewinn- und Verlust einer Firma zu berechnen, beginnen wir mit dem Umsatz der Firma (C4) und ziehen alle Kosten davon ab. In einem ersten Schritt sind dies die Material- (C5) und Personalkosten (C6) sowie Abschreibungen (C7), die zu dem ordentlichen Geschäftsergebnis vor Zinsen und Steuern (‘Earnings before interests and taxes’ (EBIT)) (C8) führen. Schließlich wird das Zinsergebnis (C9) hiervon abgezogen und der außerordentliche Ertrag (C10) dazu addiert, um den Vorsteuergewinn zu erhalten (‘Earnings before taxes’ (EBT)) (C11). Alle Formeln sind einfache Summen und Differenzen.

Da die tatsächlichen Materialkosten zum größten Teil variabel sind und vom Umsatzniveau abhängen, modellieren wir sie als relative Größe ‘in % des Umsatzes’. Die absoluten Materialkosten sind dann C5 = B5 * C4.

3.2.Die Quellen der Unsicherheit

To top

Nachdem das deterministische Modell definiert ist, besteht der erste Schritt hin zu einer Monte-Carlo Simulation darin die Quellen der Unsicherheit im Modell zu benennen.

Wir nehmen an, dass der Umsatz, die Materialkosten, die Personalkosten und die außerordentlichen Erträge der Unsicherheit unterliegen (Spalte B). Das Ziel besteht darin, ihre Wirkung auf das ordentliche Geschäftsergebnis und das Gesamtergebnis vor Steuern zu bestimmen (Spalte C). Beachte, dass Risiken auch im Sinne von Chancen (z.B. zufällige Kostenentlastungen) auftreten können, die den Gewinn steigern.

Während im deterministischen Modell ein Gewinn von 1.000 TEUR angenommen wurde, wissen wir aus Erfahrung, dass diese Größe nicht konstant ist, sondern in der Praxis zufällig schwankt, z.B. zwischen 900 und 1.050 TEUR, wobei 1.000 TEUR der ‚wahrscheinlichste Wert’ sei. Wir wählen daher eine Dreiecksverteilung als intuitives Modell für die Umsatzschwankungen.

Abbildung 4 - Modellierung der Umsatzschwankungen

Um eine dreiecksverteilte Zufallszahl in das Modell einzufügen, markieren wir Zelle B4 und klicken auf den ‘Funktionen’-Button in der Risk Kit Toolbar.

Dieser Dialog erscheint:

Abbildung 5 - Funktionsdialog

Risk Kit bietet mehr als 430 Funktionen aus 7 Kategorien:

Univariate – Univariate, d.h. eindimensionale Wahrscheinlichkeitsverteilungen;
Multivariate – Multivariate, d.h. mehrdimensionale Wahrscheinlichkeitsverteilungen. Diese Liste enthält Funktionen, die Zufallszahlen von multivariaten Verteilungen und Copula-Funktionen zur Verfügung stellen;
Process - Trends und zufällige Pfade und in einigen Fällen punktweise Konfidenzbänder von stochastischen Prozessen;
Statistics - Funktionen zur Berechnung von Statistiken der simulierten Verteilungen wie Mittelwert, Varianz, Quantile u.a.;
General - Die Rubrik enthält Funktionen, um beliebige Wahrscheinlichkeitsverteilungen und stochastische Prozesse zu plotten, zum dynamischen Sortieren, um Statistiken von Simulationsergebnissen zu berechnen, zur Portfoliooptimierung und Berechnung der Efficient Frontier, zur Gestaltung von Delta-Gamma-Engines.;
FixedIncome - Bietet eine Reihe grundlegender Funktionalitäten für Zinsinstrumente an wie etwa die Handhabung von Zins- und Spreadkurven, die Berechnung von Forwardzinsen, die Berechnung von Cashflows für zinstragende Geschäfts wie Kredite und Bonds, die Bewertung von variabel und festverzinslichen Bonds und die Berechnung von Ausfallwahrscheinlichkeiten aus Bond Spreads;
Rating - Ratingfunktionen für kleine und mittelständische Unternehmen in Deutschland, Österreich und den USA.

Da wir eine dreiecksverteilte Zufallszahl einfügen wollen, wählen wir die Kategorie ‘Univariate’ und in der Liste den Eintrag Triangular.

Nachdem wir mit ‘OK’ die Auswahl bestätigt haben, werden sechs Funktionen angezeigt:

Abbildung 6 - Funktionen für univariate Verteilungen

Eine gemeinsame Funktion, bei der der Funktionstyp über einen Parameter gewählt werden kann.
Die Wahrscheinlichkeitsdichte (probability density function (PDF)) im Fall von stetigen Verteilungen bzw. die Wahrscheinlichkeitsmassefunktion (probability mass function (PMF)) bei diskreten Verteilungen.
Die Verteilungsfunktion (cumulative distribution function (CDF)),
Die inverse Verteilungsfunktion (inverse cumulative distribution function (ICDF)) und
Einzelne Zufallszahl (variate) der betreffenden Verteilung.
Vektor aus Zufallszahlen der betreffenden Verteilung. Diese Funktion kann für Simulationen in hoher Geschwindigkeit verwendet werden, wenn ein Modell viele Risikofaktoren mit derselben Verteilungsfamilie enthält.

Diese Funktionen stehen für jede eindimensionale Verteilung zur Verfügung.

Eine alternative und oft intuitivere Möglichkeit, eine Verteilung auszuwählen, besteht darin, in der Symbolleiste auf die Schaltfläche "Univariat" zu klicken und die Verteilungsgalerie zu öffnen.

Abbildung 7 - Verteilungsgalerie

Die Galerie zeigt für jede Verteilung ein Vorschausymbol, das eine typische Form der Dichte- oder Massenfunktion der Verteilung illustriert. Die Verteilungen sind auch nach Namen geordnet.

Bewegen Sie den Mauszeiger über ein Vorschausymbol, um weitere Informationen über die Verteilung zu erhalten. Außerdem wird eine Tastenkombination für die Verteilung angezeigt, wenn eine solche definiert wurde^².

Abbildung 8 - Informationen über die Verteilung und Tasktenkombinationen in der Galerie

Klicken Sie auf eine Verteilung, um sie aus der Galerie auszuwählen, und wählen Sie "Einzelne Zufallszahl" wie oben beschrieben.

Sie können jetzt die Verteilungsparameter eingeben.

Abbildung 9 - Funktionsargumente der Dreiecksverteilung

Hierzu gibt es zwei Möglichkeiten. Sie können einmal die Parameter in ein Excel Arbeitsblatt schreiben und im Funktionsaufruf eine Referenz auf die betreffenden Zellen angeben. Dies wird in Abbildung 9 dargestellt. Parameter ‚a’ enthält eine Reference auf Zelle B17 mit der unteren Grenze für die Umsatz, ‚b’ enthält eine Referenz auf C17 mit dem ‚wahrscheinlichsten Wert’ und ‚c’ auf D17, der optimistischten angenommenen Umsatzgrenze.

Alternativ können Sie die Parameter direkt eingeben.

Abbildung 10 - Funktionsargumente

Im Dialog kann ein Ausgabename (Outputname) angegeben werden (nur für univariate Verteilungen). Der Ausgabename wird in Grafiken und Ausgaben von Statistiken als Legende für diese Verteilung erscheinen.

Der Dialog zeigt noch einige weitere Informationen über die gewählte Verteilung und ihre Parameter. Zunächst erhalten Sie eine kurze Zusammenfassung, was die Verteilung tut. In diesem Fall erzeugt sie eine ‚Zufallszahl einer Dreiecksverteilung mit den Parametern a, b und c’.

Wenn Sie einen Parameter eingeben, werden Informationen über seinen Definitionsbereich angezeigt. In unserem Fall ist es erforderlich, dass z.B. Parameter c größer oder gleich b ist.

Unterhalb der Grafik sehen Sie die Definition der Funktion, die in der Grafik dargestellt ist.

Beachten Sie schließlich den Link zur Onlinehilfe in der unteren linken Ecke des Dialogs und das Tastenkürzel der Funktion in der Mitte unten.

Wenn Sie auf ‘Einfügen’ klicken, wird die Funktionsdefinition in Zelle B4 eingetragen und erscheint in Excels Formelzeile. Der Gesamtumsatz in C4 spiegelt jetzt auch das Risiko. Die Materialkosten sind hiervon ebenfalls betroffen, obwohl ihr Prozentsatz immer noch konstant bei 50% liegt, weil sie ebenfalls vom Umsatz abhängen.

Sie können Zufallszahlen manuell neu ziehen lassen, indem Sie <F9> drücken.

Abbildung 11 - Zelle enthält die Risk Kit Funktion

Wir fahren mit den anderen Risiken ähnlich fort. Für die relative Abweichung von den geplanten Materialkosten nehmen wir eine Normalverteilung mit Erwartungswert mu = 50% und Standardabweichung sigma = 3% an.

Abbildung 12 - Modellannahmen für die Materialkosten

Um die Formel in Zelle B5 einzugeben, platzieren wir den Cursor auf dieser Zelle und öffnen den Risk Kit Funktionsdialog. In dem Feld ‚Suchen nach’ geben wir ‚normal’ ein und bestätigen mit ‚Suchen’. Wenn wir eine Funktion markieren, erhalten wir eine kurze Erklärung ihrer Bedeutung. Wir stellen so fest, dass die Funktion ‚NormalD’ ist, was wir suchen, nämlich die Normalverteilung.

Abbildung 13 - Der Suchdialog für die Funktionen

Wir bestätigen die Auswahl mit ‘OK’ und wählen ‘Einzelne Zufallszahl’. Hierdurch gelangen wir zu dem Dialog, in den wir die Referenz auf B21 für mu und auf C21 für sigma eingeben können. Mit ‚Einfügen’ wird die Funktionsdefinition in B5 eingetragen. Beachten Sie, dass Sie diesen Prozess abkürzen können, indem Sie die Funktionsdefinition direkt in die Zelle eingeben. Der Funktionsdialog dient lediglich der Unterstützung.

Abbildung 14 - Funktionsargumente der Normalverteilung

Die absoluten Materialkosten sind dann die simulierten Kosten in % des Umsatzes mal dem simulierten Umsatz.

Abbildung 15 - Verknüpfung der simulierten Materialkosten mit dem Modell

Die Personalkosten stellen wir ähnlich den Materialkosten dar. Wir nehmen eine Normalverteilung mit Erwartungswert mu = 325 und Standardabweichung sigma = 20 an. Wir gehen jedoch davon aus, dass die Personalkosten nicht unter ein bestimmtes Niveau fallen können. Wir nehmen daher an, sie seien links an der Stelle 300 trunkiert.

Abbildung 16 - Modellannahmen für die Personalkosten

Um diese Annahmen in einen Funktionsaufruf zu verwandeln, öffnen wir den Funktionsdialog und verknüpfen die Parameter mu, sigma und TruncLow mit den Zellen B25, C25 und D25.

Abbildung 17 - Normalverteilung mit Trunkierung

Die Trunkierung der Verteilung an der Stelle 300 stellt sicher, dass keine Zufallszahlen unterhalb dieses Wertes gezogen werden, während die Verteilung auf der rechten Seite unverändert bleibt.

Mit ‚Einfügen’ wird die Funktion in das Arbeitsblatt übertragen

Schließlich wird der außerordentliche Ertrag unserer Firma von Kreditrisiken betroffen. Wenn Kunden ausfallen, bevor Rechnungen bezahlt sind, verliert die Firma Geld.

Abbildung 18 - Modellannahmen für außerordentliche Erträge

Wir nehmen an, dass die Anzahl der Kunden, die im Geschäftsjahr ausfallen, Poisson-verteilt sei mit einem Erwartungswert von 5 Ausfällen. Wenn ein oder mehrere Ausfälle eintreten, nehmen wir darüber hinaus an, die Höhe eines jeden Ausfalls sei PERT-verteilt mit minimalem Verlust 0, maximalem Verlust 10 und ‚wahrscheinlichstem’ Verlust 3.

Beachten Sie, dass wir damit an dieser Stelle zwei Quellen der Unsicherheit haben. Einmal ist die Anzahl der Ausfälle zufällig und dann in jedem einzelnen Fall auch die Höhe der Verluste. Der Gesamtverlust ist damit die Summe einer zufälligen Anzahl von Summanden, die jeweils einen zufälligen Betrag haben.

Wir verwenden den Funktionsdialog, um herauszufinden, dass der Aufruf für eine Poisson-verteilte Zufallszahl

Poisson_variate(5)

lautet und für eine PERT-verteilte Zufallszahl

Pert_variate(0, 3, 5).

Einfache Aufrufe dieser Funktionen tragen nicht der Tatsache Rechnung, dass die Verluste in jedem einzelnen Ausfall eines Kunden unterschiedlich sind (die Höhe der Summanden). Es reicht deshalb nicht nur eine Poisson-verteilte und eine PERT-verteilte Zufallszahl zu ziehen und diese miteinander zu multiplizieren, da diese Darstellung bedeuten würde, dass die Verluste in jedem einzelnen Fall genau die gleiche Höhe haben.

Um die Summe einer zufälligen Anzahl Summanden einer jeweils zufälligen Höhe zu berechnen, stellt Risk Kit die Funktion ‚Compound’ zur Verfügung. Öffnen Sie den Funktionsdialog, wählen Sie ‚Compound’ aus der Galerie und fügen Sie die o.s. Funktionsaufrufe als Argumente ein.