ArticlePDF Available

Abstract

Planungen sind meist subjektiv verzerrt, entweder bewusst aufgrund einer "hidden agenda" oder unbewusst, weil man sich manche Dinge einfach nicht vorstellen kann. Wer hätte zum Beispiel vor einigen Jahren ein Finanzergebnis mit negativen Zinsen geplant? Relativieren kann man die Verzerrung mit einer stochastischen Planung, die Zufallszahlen nutzt, um den zukünftigen Geschäftsverlauf zu prognostizieren. Ein VBA-Skript kann diese Monte-Carlo-Simulation mit Excel-Bordmitteln erstellen und ermöglicht so eine einfache Analyse.
34
Planungen sind meist subjektiv verzerrt, entwe-
der bewusst aufgrund einer „hidden agenda“
oder unbewusst, weil man sich manche Dinge
einfach nicht vorstellen kann. Wer hätte zum
Beispiel vor einigen Jahren ein Finanzergebnis
mit negativen Zinsen geplant? Relativeren kann
man die Verzerrung mit einer stochastischen
Planung, die Zufallszahlen nutzt, um den zu-
künftigen Geschäftsverlauf zu prognostizieren.
Ein VBA-Skript kann diese Monte-Carlo-Simu-
lation mit Excel-Bordmitteln erstellen und er-
möglicht so eine einfache Analyse.
„Planung ersetzt Zufall durch Irrtum“ zitieren
zynische Controller gerne. Damit wird eine im-
manente Eigenschaft aller Planungen kommen-
tiert: Die Zukunft ist unsicher und keine Pla-
nung ist je eingetroffen. Dennoch wird jedes
Jahr versucht, eine mehr oder weniger genaue
Projektion der Zukunft zu erstellen, damit die
Controller und Unternehmenslenker wissen, wo
sie hinsteuern sollen.
Planung wird subjektiv von den
handelnden Personen geprägt
Dabei wird diese Projektion von den handelnden
Personen geprägt. Manchmal erfolgt die Pla-
nung extrem konservativ, da Kostenüberschrei-
tungen drakonisch bestraft werden. Manchmal
gleicht die Planung mehr dem Wunschzettel ei-
nes Managers, der nach der Welt(markt)herr-
schaft strebt. Solche Planungen sind zur Unter-
nehmenssteuerung nutzlos, weil bereits zum
Zeitpunkt der Planerstellung sicher ist, dass die
Wirklichkeit deutlich anders sein wird.
Aus Controlling-Sicht ist eine Planung dennoch
unerlässlich, ermöglicht sie doch einen Ver-
gleich zwischen geplantem und tatsächlichem
Zustand. Aus diesen Abweichungen entwickelt
das Controlling Maßnahmen, um wieder auf
den gewünschten Pfad zurückzukehren. Beim
Versuch, extremen Ausprägungen einer Pla-
nung entgegenzuwirken, wird zum Teil zusätz-
lich mit einer Art Szenarioanalyse gearbeitet.
Dabei werden statt einer Planung drei Szena-
rien erstellt: die erwartete Zukunft, ein best-
case- und ein worst-case-Szenario.
Doch die Zukunft ist weitaus komplexer als die-
se drei Fälle – und es ist auch nicht gesagt,
dass die best- und worst-case-Szenarien wirk-
lich die obere und untere Grenze darstellen.
Doch vielleicht kann man in dem zynischen Zi-
tat ein Körnchen Wahrheit finden und das Zu-
fallselement in die Planung einfließen lassen?
Die stochastische Planung
Die Lösung ist eine stochastische Planung. Hier
wird die Unternehmensplanung mit einer Mon-
te-Carlo-Simulation kombiniert, um die Zukunft
zufallsgesteuert zu modellieren. Der zukünftige
Geschäftsverlauf wird nicht wie bisher von ein-
zelnen Fachbereichen prognostiziert, sondern
unterschiedliche Einflussfaktoren auf das Ge-
schäft werden innerhalb gewisser Bandbreiten
zufällig variiert und daraus ein Betriebsergebnis
abgeleitet.
Ausgehend von aktuellen GuV-Daten als Basis-
jahr werden Veränderungsraten für die einzel-
nen Positionen für das folgende Jahr zufällig er-
mittelt, wobei die Bandbreiten der Verände-
rungsraten entweder aus historischen Daten
abgeleitet werden oder bereits wie bei Roh-
stoffpreisen als Volatilitätskennzahlen bekannt
sind. Eine eigene „Wachstumsrate“ für die GuV-
Position Abschreibungen ist nur nötig, falls die
Abschreibungssumme durch schwankendes
Investitionsvolumen schwankt. Sind keine In-
vestitionen geplant, sondern nur Ersatzbe-
schaffungen, kann auf eine eigene „Wachs-
tumsrate“ verzichtet werden.
Natürlich könnte so ein Modell auch viel kom-
plexer gebaut werden. Hinter der Abschrei-
bungssumme würde vielleicht eine Investitions-
planung stehen, die mit einer zufallsbasierten
Umsatzplanung verknüpft ist. Dann würde auch
der zukünftige Umsatz nicht pauschal mit einer
einzelnen Wachstumsrate ermittelt werden,
sondern das gesamte Produktportfolio abgebil-
det werden mit unterschiedlichen Werten für
Mengen- und Preissteigerungen. Der Komple-
xität sind hier keine Grenzen gesetzt, aber
es sollte trotzdem auf eine wirtschaftliche
Verhältnismäßigkeit geachtet werden. In
diesem Text wird ein einfaches Beispiel mit
pauschalen Wachstumsraten für Umsatz, Per-
sonalkosten, Materialkosten und Abschreibun-
gen verwendet.
Die GuV-Daten des Basisjahrs werden dann mit
den zufälligen Wachstumsraten multipliziert
und bilden die Werte für das Planungsjahr eins.
Mit Hilfe der bestehenden GuV-Struktur wird
das Unternehmensergebnis im Jahr eins er-
rechnet. Für das zweite Planungsjahr wieder-
holt sich der Vorgang, aber als Ausgangsbasis
dient nun das Planungsjahr eins. Für die einzel-
nen Positionen werden neue Wachstumsraten
zufällig ermittelt und die Werte von Planungs-
jahr eins damit multipliziert, um die GuV des
Planungsjahrs zwei zu erstellen.
Monte-Carlo-Planung
in Excel
von Florian Bliefert
Monte-Carlo-Planung in Excel
35
Nach fünf Durchläufen hätte man eine zu-
fällige Unternehmensplanung für die
nächsten fünf Jahre, die eine mögliche
Zukunft abbildet. Um das erwähnte Prob-
lem zu umgehen, dass die Zukunft mehr als
eine Möglichkeit hat, erstellt man in der Mon-
te-Carlo-Simulation mehrere tausend ver-
schiedene Abbilder der Zukunft. Diese Daten-
flut wird dann aufbereitet, damit die unter-
schiedlichen Unternehmensergebnisse mit
einer Eintrittswahrscheinlichkeit versehen
werden können.
Monte-Carlo Simulation in Excel
Es gibt am Markt bereits Software, die diese
Aufgaben übernimmt, aber die ist meist teuer in
der Anschaffung und aufwändig im Customi-
zing. Doch auch mit Excel-Bordmitteln kann
eine stochastische Planung erstellt wer-
den, entweder für einen Teilbereich oder sogar
für ein ganzes Unternehmen. Die GuV-Struktur
als Modell des Unternehmens wird in Excel dar-
gestellt, die Zufallszahlen liefert ein VBA-Skript.
Dieses wirft die Datenflut in einem neuen Ta-
bellenblatt aus, wo sie wieder mit bekannten
Werkzeugen bearbeitet und verdichtet wird.
Das Ganze soll mit anhängendem Beispiel-Pro-
gramm demonstriert werden.
Excel-Datei aus drei Teilen
Die Excel-Datei besteht aus drei Teilen: einem
Tabellenblatt als Input-Schnittstelle, einem
VBA-Skript für die Monte-Carlo-Simulation und
einem weiteren Tabellenblatt für den Output. Im
Input-Teil ist eine einfache GuV aus den Positio-
nen Umsatz, Materialaufwand, Personalkosten
und Abschreibungen dargestellt. Für jede Posi-
tion werden der Basiswert und die Bandbreiten
der Wachstumsraten angegeben.
Diese GuV-Struktur wird als Datenstruktur im
VBA-Skript nachgebildet. Im Listing wird dazu
ein eigener Datentyp GuV definiert, der die ein-
zelnen Positionen zusammenfasst (siehe Abbil-
dung 1: Der neu definierte Datentyp „GuV“).
Das erleichtert den Umgang mit den Daten-
mengen, da so ein einzelnes Planungsjahr zu
einem Objekt zusammengefasst wird. Dieses
Objekt wird nun in der zweidimensionalen Ma-
trix Planungsreihe verwendet: Für jeden Durch-
lauf der Monte-Carlo-Simulation werden fünf
GuV benötigt, um fünf Planungsjahre aufzu-
nehmen. Die Anzahl der Simulationen wird mit
der Konstante mc_wiederholung direkt im
Quelltext gesteuert. Als weitere Variablen wer-
den noch die Bandbreiten der Wachstumsraten
und das Basisjahr definiert.
Zuerst liest das Skript die Werte für das Ba-
sisjahr und die Bandbreiten der Wachstums-
raten ein. Das Ermitteln der zufälligen Wachs-
tumsraten übernimmt die Funktion gen_
wachstumsrate. Der Funktion müssen die un-
tere und obere Bandbreite als Argumente
übergeben werden, sie liefert eine Wachs-
tumsrate in der Form 1+n zurück, wobei n ein
Prozentwert innerhalb der gegebenen Band-
breiten ist.
Die Erzeugung der Plandaten für fünf Jahre ist
bei jedem Durchlauf der Monte-Carlo-Simulati-
on gleich. Ausgehend vom Basisjahr wird jede
Position mit einer zufälligen Wachstumsrate für
das Jahr eins ermittelt. Danach werden mit ei-
ner Schleife die Jahre zwei bis fünf erzeugt, in-
dem immer die Vorjahreswerte mit einer zufälli-
gen Wachstumsrate modifiziert werden. Nach-
dem alle GuV-Positionen ermittelt wurden, wird
das Ergebnis des Planjahres berechnet und
auch in der Datenmatrix gespeichert. Eine
mögliche Version der fertigen Datenmatrix ist in
Abbildung 2 dargestellt.
In der Datenmatrix wird immer die komplette
GuV gespeichert, obwohl die nachfolgende
Analyse mit den Betriebsergebnissen durchge-
führt wird. Trotzdem wird jede einzelne Position
der GuV in einem eigenen Tabellenblatt ausge-
geben, um eventuelle Analysen von Einzelposi-
tionen zu ermöglichen.
Verarbeiten der Ergebnisse
Das VBA-Skript hat im Ausgabeblatt für das
Unternehmensergebnis 10.000 verschiedene
Fünf-Jahres-Planungen ausgegeben. Diese
Werte müssen nun zu einer Aussage verarbei-
tet und verdichtet werden. Eine einfache Mög-
lichkeit wäre es, wieder auf die drei Szenarien
wahrscheinlich, best und worst zurückzugrei-
fen. Das wahrscheinliche Szenario wird vom
Mittelwert der 10.000 Planergebnisse gebildet,
das best von den fünf größten und worst ent-
sprechend von den niedrigsten. Damit hätte der
Abb. 1: Der neu defi nierte Dat entyp „GuV“
Abb. 2 : Datenmat rix
CM September / Oktober 2017
36
matisch die Vorkommen der Ergebnisse aus
der Urliste in der jeweiligen Klasse.
Nach dem Zusammenfassen der Daten in
Klassen können sie in einem Balkendiagramm
als Histogramm angezeigt werden. Zusätzlich
wird auch die kumulierte Häufigkeit der Unter-
nehmensergebnisse eingetragen (siehe Abbil-
dung 5). Die Verteilung ist nicht ganz symme-
trisch und die Unternehmensergebnisse daher
nicht perfekt normalverteilt, wie auch aus der
Schiefe von 0,18 und der geringen Abwei-
chung des Medians vom Mittelwert abgelesen
werden kann.
Wären die Ergebnisse normalverteilt, könnten
jetzt schon erste Aussagen getroffen werden,
zum Beispiel, dass 95,45% der Unterneh-
mensergebnisse im Intervall von ±2 Standard-
abweichungen vom Median liegen. Damit wür-
de das Unternehmensergebnis im fünften Jahr
mit 95,45% Wahrscheinlichkeit zwischen
766.341,66 Euro und 1.580.313,14 Euro lie-
gen. Tatsächlich liegen 95,60% der Unterneh-
mensergebnisse zwischen diesen Werten, für
ergeben haben, kann man die Streuungsbreite
der Unternehmensergebnisse erkennen (siehe
Abbildung 3).
Die Bandbreite liegt von 531.092,71 Euro
bis 1.892.772,87 Euro, der Mittelwert ist
1.180.912,11 Euro. Da der Median mit
1.173.327,40 Euro nicht weit weg vom Mittel-
wert liegt, könnten die Werte normalverteilt
sein. Um das zu überprüfen, werden die Er-
gebnisse grafisch dargestellt.
Als ersten Schritt müssen die 10.000 Unter-
nehmensergebnisse in Klassen zusammenge-
fasst werden. Im vorliegenden Zahlenbeispiel
beträgt das niedrigste Ergebnis 531.092,71
Euro, das höchste 1.892.772,87 Euro, die
Klassen werden von 500.000 Euro bis
2.000.000 Euro gebildet mit einer Klassen-
breite von 50.000 Euro (siehe Abbildung 4).
Das Zählen kann komfortabel Excel überlassen
werden mit der Formel HÄUFIGKEIT().
HÄUFIGKEIT ist eine Matrixformel, die als Ar-
gumente ein Datenfeld und ein Klassenfeld
benötigt. Das Datenfeld ist die Urliste der Er-
gebnisse, das Klassenfeld die Liste der Klas-
sen von 500.000 Euro bis 2.000.000
(A2:A32). Zur Eingabe wird der Bereich
B2:B32 markiert, dann die Formel =HÄUFIG
KEIT(Urliste!A1:A10001;A2:A32) eingegeben,
aber anstatt normal mit Enter muss nun mit
Strg+Shift+Enter bestätigt werden! In der
Zelle steht nun die Matrixformel mit ge-
schweiften Klammern: {=HÄUFIGKEIT(Urliste!
A1:A10001;A2:A32)}. Excel zählt jetzt auto-
Controller die subjektive Prägung des Fach-
bereichs eliminiert, da die Werte stochastisch
ermittelt wurden.
Jedoch hätte dieses Verfahren zwei Nachteile.
Erstens würde man dabei einen Methoden-
bruch akzeptieren, da die fünf besten Werte
sehr wahrscheinlich aus fünf unterschiedlichen
Planungsszenarien entstammen. Die Kernaus-
sage bei dieser Vorgehensweise ist nicht „Wie
ist die Unternehmensentwicklung für die nächs-
ten fünf Jahre im schlechtesten Fall“, sondern
„Wie ist die jeweils schlechteste jährliche Ent-
wicklung in den nächsten fünf Jahren“. Viel-
leicht könnte dieser Methodenbruch sogar in
Kauf genommen werden, aber es gibt noch ei-
nen zweiten Grund, der gegen dieses Vorgehen
spricht: Der eigentliche Vorteil einer stochasti-
schen Planung wird nicht genutzt.
Durch die vielen Durchläufe besitzt der Control-
ler eine so große Menge an Daten, dass statis-
tische Werkzeuge zur Analyse und Prognose
dafür verwendet werden können. Die Brücke
von den Planungsdurchläufen zur Statistik ist
schnell geschlagen. In einer Fünf-Jahres-Pla-
nung sind zwar die Jahre eins bis vier wichtig,
aber für mittelfristige Steuerungszwecke ist
das Ergebnis im fünften Jahr das interessan-
teste. Die 10.000 möglichen Ergebnisse des
fünften Jahres stellen eine Urliste dar und kön-
nen mit statistischen Standardwerkzeugen be-
arbeitet und interpretiert werden.
Interpretation der Ergebnisse
Auch wenn die Minimum-, Maximum- und Mit-
telwerte nicht für die üblichen drei Szenarien
verwendet werden, sind sie doch interessante
Lageparameter für die Verteilung der Unterneh-
mensergebnisse. Bei den verwendeten Bei-
spielzahlen, die sich aus einem Simulationslauf
Autor
Florian Bliefert, MBA
ist Lehrbeauftragter und Dozent für Kostenrechnung, Finan-
zierung und Unternehmensbewertung in München.
E-Mail: mail@florian-bliefert.de
Abb. 3 : Lagepara meter der Urliste
Abb. 4 : Häufigkeit sverteil ung des Ergeb nisses im
fünf ten Jahr
Monte-Carlo-Planung in Excel
37
men in Zahlen darstellt. Das so geplante Ergeb-
nis wird mit den ermittelten Bandbreiten aus
der Monte-Carlo-Simulation verglichen. In die-
sem Beispiel liegen knapp 95% der Ergebnisse
zwischen 766 T€ und 1,58 Mio. €. Plant ein
Fachbereich ein Ergebnis von 500 T€ oder 1,9
Mio. €, sind das gute Gründe, die Planung ge-
nauer zu hinterfragen. Gibt es Sachverhalte, die
in der Simulation nicht richtig abgebildet wor-
den sind und hat der Fachbereich einen Infor-
mationsvorsprung? Oder ist die Planung ex trem
konservativ bzw. überoptimistisch und sollte
nachgeschärft werden?
Fazit
Mit der Monte-Carlo-Simulation ändert sich die
Herangehensweise an eine Planung. Klassisch
wird überlegt, was passieren könnte und das
dann in einer Planung abgebildet. Eventuell
wird die noch erweitert um die zwei Fälle „was
passiert, wenn es super läuft“ und „was pas-
siert, wenn es schlecht läuft“. Dabei fließt viel
Subjektivität mit ein, weil man sich manche
Entwicklungen von Kosten und Umsatz nicht
vorstellen kann oder will.
Dieser blinde Fleck wird durch das ma-
schinelle Generieren von Zufallszahlen
vermieden. Das kann auch gut genutzt
werden, um eine bestehende Fachbe-
reichsplanung zu validieren. Liegt die Pla-
nung innerhalb einer gewünschten Wahr-
scheinlichkeitsbandbreite? Wie groß ist die
Wahrscheinlichkeit in der Simulation, dass der
Planwert erreicht wird?
Die Qualität der Monte-Carlo-Simulation steht
und fällt mit der Modellierung des Geschäfts-
modells und der Adjustierung der Bandbreiten
der Wachstumsraten. Je schwieriger ein Ge-
schäftsverlauf zu prognostizieren ist, desto
besser ist die Monte-Carlo-Simulation geeig-
net. Die Komplexität des Geschäftsmodells
kann auch deutlich reduziert werden, wenn nur
ein Teilbereich des Unternehmens so abgebil-
det wird und die gewonnen Planzahlen in die
herkömmliche Planung integriert werden.
Planung werden dann Maßnahmen definiert,
die Einfluss auf die Bandbreiten haben. Zum
Beispiel könnten die obere Begrenzung der
Personalkosten durch Einfrieren der Gehälter
oder sogar eine Kürzungsrunde niedriger ein-
gestellt werden. Oder durch definierte Ver-
triebsoffensiven verschieben sich die Band-
breiten des Umsatzwachstums. Wichtig ist
nur, dass Anpassungen der Bandbreiten mit
tatsächlichen Maßnahmen unterlegt sind, da
die ursprünglichen Werte aus belastbaren
Daten hergeleitet wurden.
Um einen Fünf-Jahres-Plan zu erhalten, an
dem reale Ergebnisse gemessen werden kön-
nen und Abweichen ersichtlich werden, kann
dieses Verfahren für die Jahre eins bis vier wie-
derholt werden. Dadurch erhält der Controller
für jedes Jahr Bandbreiten, innerhalb derer das
Unternehmensergebnis mit einer definierten
Wahrscheinlichkeit liegen sollte. Fällt das Er-
gebnis aus diesem Korridor, muss geklärt wer-
den, ob dieser Fall zu der Restunsicherheit ge-
hört oder ob sich an den wirtschaftlichen Rah-
menbedingungen im Vergleich zur Monte-Carlo-
Simulation so viel geändert hat, dass ein
Gegensteuern notwendig ist.
Validierung einer Fachbereichsplanung
mittels Monte-Carlo-Simulation
Eine weitere mögliche Anwendung der Monte-
Carlo-Simulation ist die Validierung einer Fach-
bereichsplanung. Die Planung erfolgt wie bisher
im Fachbereich, der seine Ideen und Maßnah-
Planungszwecke kann also eine Normalvertei-
lung angenommen werden.
Doch auch ohne Normalverteilung können die
Daten gut über die kumulierte Häufigkeit inter-
pretiert werden. Aus der Verteilung in Abbil-
dung 4 lässt sich ablesen, dass 4,62% der
Ergebnisse kleiner als 850.000 Euro sind. Die
Wahrscheinlichkeit, dass das Unternehmenser-
gebnis im fünften Jahr schlechter als 850.000
Euro ist, beträgt 4,62%, im Umkehrschluss ist
das Ergebnis zu 95,38% besser. Auch eine
obere „Grenze“ kann so identifiziert werden.
95,90% der Ergebnisse sind kleiner als
1.550.000 Euro, die Wahrscheinlichkeit für ein
besseres Ergebnis ist daher 4,10%.
So oder so können untere und obere Bandbrei-
ten definiert werden, innerhalb derer das Unter-
nehmensergebnis mit einer gewissen Wahr-
scheinlichkeit liegt. Die Aussage erinnert an
die best/worst-case Szenarien, jedoch sind
hier die Grenzen ohne subjektiven Einfluss
zustande gekommen und sie bilden auch
nicht das beste und schlechteste Szenario ab.
Mit einer gewissen Wahrscheinlichkeit wird das
Ergebnis im fünften Jahr außerhalb der Band-
breiten liegen, doch diese Unsicherheit spiegelt
auch das reale Leben wider.
Für den Fall, dass die untere Grenze nicht zu-
friedenstellend ist und die Unternehmenslei-
tung ein besseres Ergebnis haben möchte,
dass mit 95% Wahrscheinlichkeit nicht unter-
schritten wird, müssen die Inputparameter
angepasst werden. Wie bei der klassischen
Abb. 5: Häufigkeit sverteilung
CM September / Oktober 2017
ResearchGate has not been able to resolve any citations for this publication.
ResearchGate has not been able to resolve any references for this publication.