Startpunkt einer Umsetzung ist die häufig die Tabellenkalkulation, in der meistens die Controlling-Standardmodelle wie Deckungsbeitragsrechnung bereitstehen. Insofern ist es naheliegend, diese Modelle um statistische Aspekte direkt in Excel zu erweitern.
Ein erster Ansatz wäre die Modellierung mittels Excel-Funktionen. Die deterministische Modelllogik wird pro Szenario vervielfältigt. Man könnte das Modell in die Spalten und die Szenarien in die Zeilen einstellen.
Szenarien werden durch Kopieren der Modellzeilen erstellt. Die stochastischen Parameter werden über die Excel-Funktion ZUFALLSZAHL() und den invertierten Verteilungsfunktionen aus Excel (z. B. NORM.INV()) erzeugt.
Vorgehensweise kann über Beispieldateien nachvollzogen werden
Die Vorgehensweise in diesem Beitrag kann über diverse Musterdateien nachvollzogen werden, die ergänzend unter dem Namen Planungssimulation (Monte Carlo) bei den Arbeitshilfen zu finden sind.
Schauen wir uns folgende einfache Planung an. Für ein Unternehmen (vereinfachend ein Ein-Produkt-Unternehmen) wird eine Jahresplanung auf wenigen Positionen durchgeführt. Es wird eine integrierte Erfolgs- und Bilanzplanung eingesetzt. Die Struktur ist stark vereinfacht, um die Monte-Carlo-Simulation besser darstellen zu können. Die Jahresplanung sei abgeschlossen und das Planergebnis von der Geschäftsführung akzeptiert. Nun soll aber auch die inhärente Unsicherheit transparent gemacht werden. Das Modell mit seinen Verknüpfungen steht in den Zeilen, um das Kopieren zu vereinfachen (Abb. 2).
Abb. 2: Ein einfaches Planungsmodell aus der Musterdatei MCS Funktionsmodell.xlsm
Alle Basispositionen sind gewissen Schwankungen unterworfen. Es werden zu Beginn nur zwei Verteilungen verwendet: die Normalverteilung für unspezifische Schwankungen und die Binomialverteilung für identifizierbare Risiken: Als Parameter für die Normalverteilung werden Mittelwert und Standardabweichung angegeben. Parameter für die Binomialverteilung ist die Eintrittswahrscheinlichkeit und als Multiplikator die Schadenshöhe. Hierzu wird eine weitere Zeile eingefügt (Abb. 3).
Abb. 3: Erweiterte Parameter
Normalverteilung für unspezifische Schwankungen
Abb. 4: Parameter der Normalverteilung
Die beiden einzelnen Risiken werden mit der Binomialverteilung abgebildet. Auch hier wird wieder die invertierte Verteilungsfunktion BINOM.INV() angewendet (Abb. 5). Der zweite Parameter gibt die möglichen Zustände an. Binäre Risiken sind durch 0 und 1 gekennzeichnet.
Abb. 5: Parameter der Binomialverteilung
Achten Sie bitte auch auf die relative/absolute Adressierung. Bei richtiger Adressierung können die Formeln einfach in die Zeilen und Spalten kopiert werden. Und schließlich müssen noch die Definitionsgleichungen eingegeben werden (s. Abb. 6).
Abb. 6: Modellfunktion
Die erstellten Funktionen werden n-mal vertikal kopiert (s. Abb. 7).
Abb. 7: Ausschnitt der Ergebnisse der Simulation
Man sieht, dass die Risiko-Ereignisse nur gelegentlich (in der Häufigkeit durch den Prozentsatz angegeben) auftreten.
Nun müssen noch die Häufigkeitsklassen gebildet werden. Zunächst müssen erst einmal die Klassen definiert werden. Vereinfacht wird in unserem Beispiel mit 20 Klassen gearbeitet. Die Klassen ergeben sich wie folgt (s. Ab. 8):
Min(Ergebnis) + ( Max (Ergebnis) – Min ( Ergebnis)) /20 * i , für i von 1 bis 20 |
Abb. 8: Ermittlung der Klassen
Die Häufigkeit kann mit der Excel-Funktion HÄUFIGKEIT() abgebildet werden. HÄUFIGKEIT() wird als Matrixfunktionen genutzt (Abb. 9). Der Excel-Name "Bereich" stellt den (dynamischen) Ergebnisvektor dar.
Ziel ist die Häufigkeit für alle Attribute, also auch die unabhängigen Attribute darstellen zu können. Dazu muss ein wenig "getrickst" werden: "Bereich" ist ein Excel-Name, der nicht auf Zellen referenziert, sondern eine Formel enthält: =BEREICH.VERSCHIEBEN(Funktionsmodell!$A$4;1;VERGLEICH(Auswahl;Variablen;0);1000;1). "Auswahl" ist ein Datenüberprüfungsfeld, wo die Liste der Attribute (Ergebnis, Absatz, Umsatz usw.) ausgewählt werden kann. "Variablen" referenziert zu guter Letzt auf die Liste der Variablen. Hierzu ist MTRANS() notwendig, um die Attribut-Zeile in eine Spalte zu bekommen. Wenn man also den Inhalt von "Auswahl" ändert, verschiebt sich der Bereich genau auf die Spalte, die das Attribut aus "Auswahl" beinhaltet. U6:U25 referenzieren auf die Klassengrenzen. Wir sind an den relativen Häufigkeiten interessiert, so dass das Ergebnis durch die Anzahl der Datensätze geteilt wird.
Abb. 9: Ermittlung der Häufigkeiten
Drücken Sie nun mehrfach F9 zur Kalkulation des Modells. Es werden jedes Mal neue Zahlen präsentiert.
Abb. 10: Erster Lauf der Simulation
Abb. 11: Zweiter Lauf der Simulation
Sie sehen, dass die Ergebnisverteilung stark schwankt (Abb. 10 und 11). Auch der Value@Risk schwankt zwischen 7.000 und 10.500, wobei die Extremwerte noch höher werden. Wie verkaufen Sie so ein Ergebnis an das Management?
Es besteht auch die Möglichkeit, sich die Verteilung der Eingabeparameter durch die Auswahl in Zelle U3 (Pull-down-Menü) a...