6.1 Verteilungsfunktion einrichten
Für die nächsten Schritte sollen die bereits dargestellten BERT-Funktionen genutzt werden. Um nicht für jede Verteilungsfunktion eine eigene Excel-Funktionen schreiben zu müssen, wird die generische R.E.eval()-Funktion eingesetzt, die im ersten Beitrag bereits ausführlich erläutert wurden. Startpunkt ist zunächst der gleiche wie beim Funktion- und Prozedurmodell in Excel. Das R-Modell ist in der Arbeitsmappe "MCS R Basismodell.xlsm" enthalten und entspricht dem prozeduralen Modell (MCS Prozedurmodell.xlsm) mit dem Unterschied, dass andere Funktionen verwendet werden (Abb. 17).
Abb. 17: Die R-Variante
BERT muss zuvor installiert werden
Um die im folgenden beschriebenen Arbeitmappen mit R-Funktionen
- MCS R Basismodell,
- MCS R Korrelationsmodell und
- MCS R Kalkulationsmodell
nutzen und in Excel wie dargestellt ansehen zu können, müssen Sie vorher die BERT-Schnittstelle installieren. Ansonsten kann wird eine Fehlermeldung bei manchen Feldern angezeigt. Die Formeln selbst sind aber sichtbar.
Genutzt wird wieder die dynamische Variante der BERT-Schnittstelle, so dass man direkt R-Code schreiben kann. So können beispielsweise die Absatzwerte aus Excel heraus wie folgt erzeugt werden:
| =RUNDEN(R.E.eval("rnorm(1,"&D6&","&E6&")");0) |
Abb. 18: Erstellung der normal verteilten Werte
rnorm() aus der R.E.eval()-Funktion oben ist eine Standardfunktion von R und steht für randomisierte Normalverteilung. Jede Verteilungsfunktion in R hat verschiedene Variationen, die sich durch das Präfix unterscheiden. dnorm steht beispielsweise für die Dichtefunktion der Normalverteilung, also die Ableitung der Verteilungsfunktion. Wenn ein Präfix "r" vorangestellt wird, handelt es sich um eine Zufallszahlen generierende Funktion. Bei der Normalverteilung häuft sich die Zufallszahl bekanntermaßen um den Mittelwert. Der erste Parameter gibt immer an, wie viele Zufallszahlen generiert werden sollen. Die weiteren Parameter sind abhängig von der gewählten Verteilungsfunktion. Bei der Normalverteilung sind es Mittelwert und die Standardabweichung. D6 und E6 enthalten den Mittelwert und die Standardabweichung
R kann blockweise rechnen, Excel leider nicht. Deswegen ist der erste Parameter bei rnorm 1. Bei jedem Durchlauf wird genau eine Zufallszahl gemäß der Verteilung (hier Normalverteilung) an Excel zurückgegeben. Wie die Blockverarbeitung von R genutzt werden kann, zeigt Kap. 8.
6.2 Einzelrisiken abbilden
Der Schadenseintritt eines Risikos wird durch eine Binomialfunktion abgebildet. Hierzu wird ein Prozentwert für die Eintrittswahrscheinlichkeit eingegeben. Die folgende Funktion erzeugt einen Schaden in der gewünschten Häufigkeit:
| =R.E.eval("rbinom(1,1"&L3&")")*K3 |
Leider wird aber zunächst ein Fehler ausgegeben, da L3 einen Dezimalwert enthält. Übergeben wird der unformatierte Wert. Aus 25 % wird beispielsweise 0,25. Damit haben wir aber ein Problem mit der Übergabe an R, da die Übergabe als Zeichenkette erfolgt und Nachkommastellen in R durch einen Punkt abgegrenzt werden. Dazu kann die (selbsterstellte) Funktion R.E.d() verwendet werden. R.E.d() wandelt ein Dezimalkomma in einen Dezimalpunkt um.
| =R.E.eval("rbinom(1,1,"&R.E.d(L3)&")")*K3 |
Es können nun relativ einfach auch weitere Funktionen zur Beschreibung von Risiken angegeben werden. Die Multinomialverteilung erlaubt beispielsweise die Übergabe von Wertepaaren mit jeweils der Eintrittswahrscheinlichkeit und der Schadenhöhe, also mehr als zwei Risikozustände. So kann beispielsweise die Dauer von Betriebsunterbrechungen nach kurz, mittel und lang unterschieden werden.
Unter Umständen ist es notwendig, in R ein sogenanntes Package dazu zu laden. Alternativ könnte beispielsweise die Dreiecksverteilung mit Best, Worst, und Expected benötigt werden. Allerdings ist die Dreiecksverteilung nicht im Standardumfang enthalten. Hierzu muss das Package "triangle" installiert und aktiviert werden. Dies geht über die Konsole oder mit den-R-Befehlen install.packages("triangle") und library(triangle).
Die variablen Kosten könnten beispielsweise wie folgt erzeugt werden:
| =RUNDEN(R.E.eval("library(triangle);rtriangle(1,16,20,17)");2)+ZUFALLSZAHL()*0 |
Die Aktivierung des Packages kann direkt in der Funktion durchgeführt werden.
Da die Fläche unterhalb der Dichtefunktion der Dreiecksverteilung 1 (100 %) sein muss, sind keine weiteren Parameter anzugeben. Die Einfachheit der Eingabe und die leichte Plausibilisierung macht die Dreiecksverteilung für den Einsatz attraktiv. Intuitiv lassen sich diese Werte meistens recht gut bestimmen. Im Gegensatz zur Normalverteilung gibt es auch keine Grenzwertprobleme. Der typische Glockenkurvenverlauf wird damit allerdings nur approximiert. Dafür können auch schiefe Verteilungen erzeugt werden. Über den Befehl Hist() kann in R die Dichtefunktion sofort transparent gemacht werden:
| hist(rtriangle(100000,16,20,17)) |
Das Ergebnis wird in Abb. 19 dargestellt.
Abb. 19: Histogramm einer Dreiecksverteilung
6.3 Aufbau der Makrosteuerung
Die Ablaufsteuerung erfolgt über ein Makro, wie schon im einfachen Fall skizziert. Der Ablauf ist der prozedur...