Blog

Rainer Pollmann

Rainer Pollmann

Planung und Simulation mit Excel

Viele Unternehmen setzen für die Planung spezialisierte Tools (z.B. Corporate Planner. LucaNet, Valsight uvm.) ein und verwenden zunehmend Verfahren der Predictive Analytics und Künstlichen Intelligenz. In Abhängigkeit vom Geschäftsmodell, der Treiber für die Planung und der Granularität der Daten, kann Excel natürlich auch für die Jahresplanung eingesetzt werden. Dies wird erschwert durch die zunehmende Volatilität, Unsicherheit, Komplexität und Ambiguität (VUKA) in der Umwelt von Unternehmen, die einher geht mit einer hohen Veränderungsgeschwindigkeit in den Prozesse. Dies führt in einigen Geschäftsmodellen zu immer kürzeren Lebenszyklen von Produkten und Dienstleistungen und zu einem größeren Datenvolumen, das für die Planung verarbeitet werden muss. Das bedingt höhere Anforderungen an das Management, die Qualität, die Konsistenz und die Sicherheit von Daten. Kann Excel dabei noch „mithalten“? Und wenn ja, mit welchen Techniken / Features?

Planung & Simulation

Business Intelligence ist schon seit vielen Jahren ein wichtiges Werkzeug im Management und wird durch verschiedene Business Warehouses sowie OLAP-Tools unterstützt. SAP/HANA ermöglicht z.B. mit seiner InMemory-Technik Datenverarbeitung in Echtzeit. Controller werden mit den zunehmenden Datenvolumina zum Data Scientists und versuchen verborgene Informationen aus großen und unstrukturierten Datenmengen mit analytischen Methoden zu generieren[2]. Predictive Analytics ermöglicht die Analyse von „historischen“ Daten, Muster darin zu erkennen und so zukünftige Entwicklungen „vorherzusagen“. So kann die Expertise von Controllern, ergänzt um analytische, mittels Algorithmen von IT-Systemen generierten Prognosen genutzt werden. Controller sollten also die notwendigen mathematisch-statistischen Kompetenzen erwerben, um zur Entwicklung von Algorithmen beitragen zu können[3].

Excel unterstützt viele dieser Methoden mit zahlreichen Funktionalitäten und kann zur Unterstützung des Planungsprozesses

  • in einem agilen Umfeld, in dem Prozesse und Standards (noch) nicht existieren und
  • für die Berechnung von Business Cases am Besten eingesetzt werden.

Planung

Nach einer Studie der Hochschule Aalen mit Unternehmen in Deutschland, Österreich und der Schweiz (2021) ist das Gegenstromverfahren das am meisten eingesetzte Planungsverfahren [5]. Damit wird eine Studie von Deloitte mit der Hochschule Heilbronn aus dem Jahr 2019 bestätigt, die zu einem ähnlichen Ergebnis kommt. [6]

Mit welchen Excel-Features und -Funktionen kann dieser Bottom up/Top Down-Planungsansatz unterstützt werden? Hier sind hauptsächlich die Features

  • Power Query,
  • Szenario-Manager und
  • Solver hilfreich.

Use Case Upload-Liste für SAP FICO erstellen

Im Planungsprozess eines Industrieunternehmens werden aus SAP FICO für jede Kostenstellen Excel-Dateien mit Vorjahres und Istwerten exportiert und den Kostenstellenverantwortlichen per SharePoint zur Verfügung gestellt. Diese Dateien müssen zu einem definierten Termin um Planzahlen ergänzt und in einen bestimmten Ordner gespeichert werden. Mit Hilfe von Power Query werden die gewünschten Daten extrahiert, zu einer Liste zusammengestellt und in SAP FICO eingelesen. In einem automatisierten Prozess wird mittels Power Query täglich geprüft, wer Daten unvollständig, fehlerhaft oder nicht termingerecht bereitgestellt hat und dem Konzern-Controlling mittels einer automatisch generierte Liste ermöglicht, die Verantwortlichen zu kontaktieren.

Der Planungs- und Upload-Prozess

Jeder, der mit Hilfe von Excel schon einmal solch einen Prozess gestaltet hat, weiß, wie viel Zeit für die Umsetzung mit Excel (und wie viel VBA-Code!) benötigt wird. Mit Power Query nimmt die Entwicklung der Lösung gerade mal einige Stunden, schlimmstenfalls einen halben Tag in Anspruch. Und dabei wird von Power Query automatisch jeder einzelne Schritt dokumentiert!

Use Case Gegenstromverfahren

Aufgaben und Rollen von Controllern werden sehr unterschiedlich verstanden. Der Internationale Controller Verein (ICV) versteht Controller als Unterstützer, als Berater des Managements[7]. Dem Management soll im nächsten Beispiel ein Tool zur Verfügung gestellt werden, mit dem wichtige Treiber für ein Steuerungsmodell in einem Top Down-/Bottom Up-Ansatz simuliert werden können. Als Beispiel dient der Return On Investment (ROI)

In das Modell werden Daten aus der Finanzbuchhaltung mit Hilfe von Power Query eingeladen und verteilt.

Dabei beeinflussen die Steuerzellen „K4 : K7“ (D Umsatz, D Material, D Personal, D Sonstiges) die Formeln auf dem Sheet „Jahresabschluss“ zur Ermittlung des Betriebsergebnisses. Die für die Ermittlung des ROI wichtigen Bestandteile Umsatz und Betriebsergebnis sind auf das Sheet „ROI-Schema“ über Namen[9] verlinkt.

  • Werden also die Steuerzellen „K4 : K7“ überschrieben, sieht man sofort die Auswirkungen auf die Berechnung des ROI.
  • Sollen verschiedene Zahlenkombinationen in den Steuerzellen als Szenarien gesichert werden, kann man dies mit dem Szenario-Manager tun.
  • Sollen die verschiedenen Szenarien in ihrer Auswirkung auf den ROI betrachtet werden, so kann man sie auf Knopfdruck in die Steuerzellen einfügen.
  • Alternativ oder ergänzend können die Szenarien in einem Bericht zusammengestellt werden. So erhält man ein Diskussionspapier zur Entwicklung der notwendigen Maßnahmen, um die angestrebten Resultate zu erreichen.

Mit Hilfe des Solvers lässt sich ein Zielwert für den ROI vorgeben und die dafür notwendigen Werte in den Zellen „K4 : K7“ durch Iterationen ermitteln. Die Ergebnisse werden in die Steuerzellen „K4 : K7“ eingesetzt und können ebenfalls als Szenario gesichert werden.

Der Solver arbeitet ausschließlich nach mathematischen Prinzipien und ermittelt ohne zusätzliche Angaben Ergebnisse, die u.U. unrealistisch sein können. Um die „Realität“ in den Solver zu integrieren, setzen Sie bis zu 1.024 Nebenbedingungen ein. Der Solver passt die Werte in den (bis zu 200 möglich!) veränderbaren Zellen so an, dass sie den Einschränkungen in den Nebenbedingungen entsprechen, und das für die Zielzelle gewünschte Ergebnis ermittelt wird. Im Video oder in Abbildung 3 ist erkennbar, das z.B. der Umsatz maximal um 3% steigen darf.

Unter der Voraussetzung, dass die Ursache-Wirkungsbeziehungen bekannt und im Modell über Formeln abgebildet sind, kann so das Gegenstromverfahren in einer Excel-Datei angewendet werden.

Abbildung 3: Gegenstromverfahren – ROI mit Treibern

Das Beispiel stellt den Solver als Instrument zu einer erweiterten Zielwertsuche vor. Tatsächlich ist er mit den integrierten mathematischen Modellen ein sehr leistungsstarkes Feature zur Nutzung der Methoden von Predictive Analytics.

Simulation

In einem Business Case soll der Erfolg einer Produkteinführung über die Simulation des

Produktlebenszyklusses berechnet werden.

  • Die Analysen des Marketings lassen einen Produktlebenszyklus von etwa 4 Jahren mit einen maximalen Absatzgröße von 2.500 Stück vermuten, die sich ungleichmäßig auf vier Jahre verteilen, ebenso wie der prognostizierte Absatzpreis.
  • Mit einem Target-Costing-Ansatz wird die absolute Kostenobergrenze (Zeile 13) ermittelt.
  • Mit einem Prozesskostenmodell werden die Vollkosten (Herstellung, Absatz, Overhead usw.) ausgewiesen und so das Periodenergebnis für jedes Jahr des Produktlebenszyklusses errechnet.
  • Als Maßstab für den Erfolg werden der Barwert und der Kapitalwert des Produktlebenszyklus errechnet (Zeile 25 und 26).

Bei allen Werten handelt es sich um Annahmen und Planwerte. Das Modell ermöglicht es, die Erfolgsparameter in verschiedene Richtungen zu verändern und so zu erkennen, mit welchem Mindestabsatz der Target Profit erzielt werden kann. Dazu werden die Schaltflächen genutzt, dazu kann auch der Solver eingesetzt werden, während der Szenario-Manager die ermittelten Wert-Kombinationen als Szenarien speichert und ein „Diskussionspapier“ zur Verfügung stellt. Zusätzlich könnte in diesem Modell eine Monte-Carlo-Simulation mittels Chrystal Balls oder den Excel eigenen Features (ZUFALLSZAHLEN(), HISTOGRAMM) integriert werden.

Dynamische Erfolgsrechnung in einem Business Case

Das gezeigte Modell ist schnell erstellt und liefert mittels der Simulationen die Grundlagen für eine Entscheidung, ob dieses Projekt weiter verfolgt werden sollte.

Sensitivitäten von Treibern

Controller nutzen in der Planungsphase in der Regel Szenario-Techniken und die damit verbundene Fragestellung „Was-wäre-wenn…“, bei der eine oder mehrere Variablen verändert werden, um die Auswirkungen auf ein Ergebnisse zu berechnen. Dafür ist die Datentabelle aus dem Menü Daten Was-wäre-wenn-Analyse hervorragend geeignet. Eine Datentabelle ist ein mit einer Funktion versehener Zellbereich, der dynamisch ermittelt, wie das Ändern bestimmter Parameter das Ergebnis beeinflusst. So können alle Ergebnisse der Variationen auf einem Tabellenblatt angezeigt und verglichen werden. Abbildung 5 zeigt dies am Beispiel einer Break-Even-Berechnung. Auf der Basis der variablen Stückkosten und dem Stückpreis werden die Sensitivitäten für den Break-Even errechnet. Damit kann (dynamisch) jede Konstellation der beiden Variablen in der Auswirkung auf den Break Even abgebildet werden. Das Modell ist in maximal 10 Minuten erstellt und kann in dieser Standardstruktur auch für andere Sensitivitätsanalysen genutzt werden. Die Basisvariablen in der Spalte C steuern das Modell und können durch Schaltflächen sowie durch den Szenario-Manager und den Solver verändert werden.

Die Sensitivitäten für den Break Even

Prognoseparameter für ein Modell ermitteln

Am Anfang eines Planungs- oder Simulationsmodells steht eine statistische Analyse von historischen Daten, um Muster zu entdecken. Diese Muster lassen sich als Treiber für die Simulation von Ursache-Wirkungs-Beziehungen in einem Prognosemodell verwenden. Excel bietet insgesamt 110 Funktionen (Stand 8/2021) für die statistische Analyse. Die (technische) Anwendung dieser Funktionen ist sehr einfach, erfordert aber statistisches Know How, um die Ergebnisse beurteilen zu können. In einem Business Case Absatzprognose soll das zukünftig reduzierte Marketing-Budget optimal in der Marketingsteuerung eingesetzt werden. Dazu wird der Einfluss wichtiger Marketinginstrumente auf den Absatz untersucht. Eine Voranalyse hat ergeben, dass es sich dabei um die Preisgestaltung, die Höhe des Werbebudgets und die Besuchsfrequenz der Außendienstmitarbeiter handelt. Welches der drei Instrumente hat den größten Einfluss auf den Absatz?

  1. Die relevanten historischen Daten werden mittels Datenmodell (Power Query) aus einem (Sales-)Tool (z.B. Salesforce o.ä.) importiert.
  2. Diese Daten werden mit einer Regressionsanalyse untersucht und die wichtigsten Regressionsparameter (u.a Koeffizienten) auf einem Sheet ausgegeben.
  3. Die Koeffizienten werden in einer Formel zur Berechnung des zukünftigen Absatzes verwendet. Der Absatz ist die Basis, um Umsatz und Deckungsbeitrag zu ermitteln.
  4. In einem Prognosemodell werden Änderungen im Werbebudget und in der Preisgestaltung sowie die daraus resultierenden Änderungen auf Umsatz und Deckungsbeitrag simuliert.

An diesen Beispielen konnten Sie erkennen, wie hilfreich Excel im Planungsprozess sein kann. Diese Techniken zur Erstellung von Planungs & Simulations-Modellen sind dabei nützlich und sollten Sie kennen:

Eine Absatz-Deckungsbeitrags-Simulation

Ist Excel noch zeitgemäß für die Planung? Aber sicher!

Wie dieser Beitrag gezeigt hat, können mit geringem Zeitaufwand und in Verbindung mit den geeigneten Features und Funktionen Simulations- und Planungsmodelle erstellt werden. Das ist insbesondere für Start Ups interessant, aber überhaupt für Unternehmen, die mit geringem Aufwand Business Cases berechnen wollen. Voraussetzung ist der Einsatz der richtigen Excel-Techniken und ein wenig statistisch-mathematisches Know How. Der Internationale Controller Verein sieht hier seit fast 10 Jahren eine Veränderung in den Anforderungen an Controller.[10] Denn die Bedeutung von Predictive Analytics im Controlling-Umfeld nimmt zu. Ist Excel da noch zeitgemäß? Aber ja, denn frei nach IKEA: Entdecken Sie die Möglichkeiten von Excel, aber die richtigen!


Wenn Ihnen dieser Beitrag gefallen hat, dann teilen  Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.


[1] Was ist Controlling? – Internationaler Controller Verein Was ist Controlling? (icv-controlling.com), Letzter Aufruf 24.01.2022

[2] Potenzial für den Controller – Dream Car der Ideenwerkstatt im ICV 2014

[3] Potenzial für den Controller – Dream Car der Ideenwerkstatt im ICV 2014

[5] Unternehmensplanung in der Praxis- – Ergebnisse einer Online-Studie, Letzter Aufruf 21.01.2022

[6] Trends der Unternehmensplanung, Letzter Aufruf 25.01.2022

[7] Die Rolle des Controllers, ControllingWiki des ICV, https://www.controlling-wiki.com/de/index.php/Controller-_Rolle , zuletzt gelesen 26.01.2022

[10] Big Data- Potenzial für den Controller, Dream Car der Ideenwerkstatt im ICV 2014

Beitrag teilen:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

Die letzten Beiträge

Up to date bleiben

Melden Sie sich für unseren Newsletter an!