Stochastische Planungssimulation (Monte Carlo) mit Excel ... / 4 Prozedurale Ausführungen in Excel

Interessanter scheint es deshalb, das Modell prozedural zu steuern, um die Parameter des Entscheidungsmodells durch zufallsgenerierte Zahlen zu füllen und nur die Ergebnisse (nur jeweils eine Ausprägung pro Lauf) zu speichern. Damit kann man nun auch deutlich komplexere Modelle bauen, ohne dass Unmengen von Formeln erzeugt werden.

Somit braucht man eine Prozedur, die nur die Eingabevariablen mit Zufallszahlen "beschickt". Dies könnte ein relativ einfaches Makro sein, welches n-mal iteriert. Die Ergebnisse werden in eine Excel-Tabelle geschrieben. Diese Tabelle kann analog zum Funktionsmodell statistisch ausgewertet werden.

Über ALT+F11 gelangt man in den VBA-Editor. Die Funktion kann direkt im VBA-Code für das Arbeitsblatt eingetragen werden (Abb. 14).

Abb. 14: VBA-Eingabe in der Arbeitsmappe MCS Prozedurmodell.xlsm

Es kann erhebliche Performance-Auswirkungen haben, wenn nicht das ganze Modell, sondern nur der notwendige Ausschnitt kalkuliert wird. Um die Kalkulation möglichst effizient zu halten, sollte daher nur ein Ausschnitt kalkuliert werden. In unserem kleinen Beispiel ist das zwar nicht bedeutend. Bei größeren Modellen ist diese Vorgehensweise unverzichtbar. Dazu wird auch der automatische Kalkulation-Modus ausgeschaltet. Ein guter Stil ist es, den vorherigen Status zu speichern und zum Schluss wieder zurück zu setzen. Der Code (aus der Abb. 14):

Sub calcProzedurmodell()

oldmodus = Application.Calculation

Application.Calculation = xlCalculationManual

With Worksheets("Prozedurmodell")

.Range("B7:R100000").Clear

For i = 1 To Range("Count")

Range("CalcArea").Calculate

For j = 2 To 18

.Cells(i + 6, j) = .Cells(5, j)

Next

Next

Application.Calculation = oldmodus

End With

End Sub

Die Anzahl der Szenarien kann über die Oberfläche gesteuert werden. Hierzu ist der Excel-Name "Count" für die Zelle U2 angelegt worden (Abb. 15). Die Einzelwerte werden nach der Modellkalkulation kopiert.

Abb. 15: Parametrisierung der Funktion

Das Ergebnis sieht ähnlich wie das Funktionsmodell aus, nur dass keine Formeln mehr vorhanden sind (Abb. 16). Die Auswertungen sind identisch.

Abb. 16: Ergebnis des prozeduralen Modells

Wenn man nur mit einfachen Verteilungsfunktionen arbeitet, ist die Verwendung von Excel wie dargestellt unproblematisch. Die Performance ist bei einfachen Modellen durchaus akzeptabel. Aber das Excel-Korsett wird i. d. R. schnell zu eng. Schon mit dem Einsatz einer Dreiecksverteilung ist man ohne Add-in-Unterstützung aufgeschmissen. Oder man muss sich wie der Autor auf die (erfolgreiche) Suche nach VBA-Code-Beispielen im Web begeben. Sollen auch Korrelationen einbezogen werden (Kap. 7), wird die Programmierung allerdings sehr komplex.