Stochastische Planungssimulation (Monte Carlo) mit Excel ... / 8 Auslagern des Kalkulation-Modells nach R

Die Monte Carlo Simulation läuft in alle Varianten und die Komplexität ist überschaubar. Bislang habe ich mich immer auf 1000 Durchläufe beschränkt. In vielen Modellen ist dies allerdings nicht ausreichend. Man erkennt dies an starken Ergebnisschwankungen zwischen den verschiedenen Monte-Carlo-Simulationsläufen.

Eine Erhöhung beispielsweise auf 10.000 Läufen führt bereits bei unserem kleinen Modell zu signifikanten Performance-Einbußen. 10.000 Durchläufe dauern bereits eine Minute auf einem normalen Desktop.

Als Lösung kann die Blockkalkulation von R verwendet werden. Mit etwas R-Kenntnis können diese Modelle direkt in R aufgebaut werden. Dazu muss allerdings auch die Rechenlogik in R abgebildet werden. Hierzu braucht man eine Matrix zum Speichern der Ergebniswerte sowie Startwerte. Dazu sind die folgenden Abbildungen und Formeln in der Arbeitsmappe "MCS R Kalkulationsmodell.xlsm" dargestellt. Die Startwerte werden wie folgt nach R übernommen:

=R.E.defineM("SM";B1:S3)

Der Bereich B1:S3 enthält die Planungsobjekte und die Ausgangswerte. Über

=R.E.eval(“SM“)

als Matrixfunktion kann das R-Objekt angezeigt werden (Abb. 26):

Abb. 26: Parameter in R

Nun muss eine Matrix definiert werden. In den Spalten werden die Spalten von SM übernommen. Die Ausdehnung der Zeilen entspricht der Excel-Variable "Count".

=R.E.eval2("mcs_m=matrix(SM[1,],"&Count&",17,byrow=TRUE,dimnames = list(1:"&Count&",unlist(dimnames(SM)[2])));0")+E24

Auch die Matrix kann einfach angezeigt werden (Abb. 27).

Abb. 27: Darstellung der Simulationsmatrix

Nun müssen noch die Regeln angegeben werden. Die Regeln können Excel angelegt und als R-Kalkulationsanweisung übergeben werden (Abb. 28).

Abb. 28: Definition der Modelllogik in Excel

Abb. 29: Absatz Definition

Die Adressierung des Simulationsdatenspeichers mcs_m erfolgt über mcs_m[<Zeile>, <Spalte>]. Wird ein Parameter weggelassen, gilt die Regel für die gesamte Zeile bzw. die gesamte Spalte. Die Adressierung kann über die Positionsnummer oder über einen Zeilen-/Strich Spaltennamen erfolgen. Ich referenziere auf Absatz (Zelle C1), 835 (Zelle C2) und 82 (Zelle C23, sodass die Regel lautet:

mcs_m[,'Absatz']<-rnorm(100000,835,82)

Das gleiche kann für die deterministischen Regeln gemacht werden (Abbildung 30).

Abb. 30: Kalkulation der deterministischen Bestandteile

Die Modelllogik kann in 2 Schritten ausgeführt werden:

• Erzeugung der Zufallszahlen
• Kalkulation der Ergebnisse

Dies wird über 2 R-Funktionen erreicht, die sequenziell gekoppelt sind:

=R.E.eval2(CalcArea)+E25*0

=R.E.eval2(C6:S6)+B5*0

Die Kopplung mit E25 stellt sicher, dass die Matrix mcs_m in der aktuellen Version vorhanden ist.

Die Kalkulation ist nun deutlich schneller geworden. Auch ein Makro ist nicht mehr notwendig. Eine Ad-hoc-Kalkulation durch F9 ist ausreichend. Die Formeln sind sicherlich etwas gewöhnungsbedürftig, können jedoch über Excel gewartet werden.