Stochastische Planungssimulation (Monte Carlo) mit Excel ... / 7 Erweiterung um Korrelationen

Das Modell ist bereits leistungsfähig, allerdings fehlt ein wesentlicher Aspekt: Viele Risiken sind nicht unabhängig voneinander. So beeinflussen Währungsrisiken auch häufig Preisrisiken. Auch dürften Währungsschwankungen Auswirkungen auf Finanzierungsrisiken haben. Unbeachtet von der Schwierigkeit der Datenermittlung können Zusammenhänge zwischen Variablen über eine Korrelationsmatrix abgebildet werden. Die zentrale Aufgabe besteht nun darin, abhängige Zufallszahlen so zu erzeugen, dass die erzeugten Zufallszahlen im gewünschten Maße korrelieren.

Das Beispiel soll nun um eine Korrelationskomponente erweitert werden. Dazu werden Abhängigkeiten zwischen Absatz, Preis und Rabatt ergänzt. Diese Abhängigkeiten könnten beispielsweise durch Predictive Analytics ermittelt werden. Basierend auf Erfahrungen der Vergangenheit soll hier unterstellt werden, dass bei einer exogenen Preiserhöhung der Absatz mit -0,4 reagiert. Gleichzeitig hat die Vergangenheit gezeigt, dass der Rabatt einen weiteren Einfluss auf den Absatz hat. Alle Einflüsse werden in der Korrelationsmatrix gespeichert. Die Matrix könnte wie folgt aussehen (Abb. 21).

Abb. 22: Gewünschte Korrelationsmatrix in der Arbeitsmappe MCS R Korrelationsmodell.xlsm

Die Diagonale (von links oben nach rechts unten) ist genau 1, da hier das Spaltenelement identisch mit dem Zeilenelement ist. Der Bereich oberhalb der Diagonalen wird auf den Bereich unterhalb der diagonal gespiegelt.

Die Ermittlung von korrelierten-Zufallszahlen kann über eine Cholesky-Zerlegung erfolgen. Das Verfahren dazu ist die Imam-Conover-Methode. In Excel ist das ein wenig aufwendig, ein Beitrag von Dannenberg erläutert sehr gut, was dazu notwendig ist.^[1] Die Funktion conover() aus dem Package mc2d erzeugt genau diese Abhängigkeiten. Das IC-Verfahren hat allerdings für uns den Nachteil, dass alle Szenarien Werte gleichzeitig erzeugt werden müssen. Die Zufallszahlen werden bislang jedoch einzeln benötigt.

Hier der Lösungsansatz (Abb. 23):

Zunächst muss die Korrelationsmatrix CorM übernommen werden. Dies erfolgt über die Funktion R.E.defineM().^[2] Anschließend wird die Tabelle GenGroup mit den zufällig verteilten Parametern über cbind() erstellt. Noch sind die Variablen unabhängig verteilt. Über die R-Funktion cornode() werden die Zahlen so angeordnet, dass die gewünschte Korrelation herauskommt. cornode() erwartet eine Matrix. Mit as.matrix() wird eine Konvertierung vorgenommen.

Abb. 23: R-Code in Excel

Ob die erzeugten Zufallszahlen der gewünschten Verteilung entspricht, kann mit dem R- Befehl cor() getestet werden. Abb. 24 zeigt, dass die Korrelationen ganz gut getroffen wurden.

Abb. 24: generierte Korrelationsmatrix

Die korrelierten Werte werden bei der eigentlichen Monte-Carlo-Simulation nicht über rnorm erzeugt, sondern aus der erzeugten Tabelle CorSample übernommen. Über runif() (Gleichverteilung) wird jedes Mal ein Datensatz für alle korrelierten Elemente übernommen (Abb. 25).

Abb. 25: Erzeugung der Einzelszenarien

Alles Weitere ist gegenüber dem unkorrelierten Ansatz gleich geblieben.

[1] Vgl. Dannenberg, 2009.

[2] Vgl. Oehler, "Predictive Analytics mit Excel und R: Positionierung und erste Schritte".