Planung und Simulation mit Excel – noch zeitgemäß?

Viele Unternehmen setzen für die Planung spezialisierte Tools (zum Beispiel Corporate Planner, LucaNet, Valsight und viele mehr) 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 immer noch 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 einhergeht mit einer hohen Veränderungsgeschwindigkeit in den Prozessen. 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 und Features?

Rainer Pollmann beschreibt in diesem Beitrag, wie die Planung und Simulation mit Excel auch heute noch erfolgreich gelingt und welche Vorteile sich dadurch für Sie im Alltag bieten.

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 zum Beispiel mit seiner InMemory-Technik Datenverarbeitung in Echtzeit. Controller werden mit den zunehmenden Datenvolumina zum Data Scientist und versuchen, verborgene Informationen aus großen und unstrukturierten Datenmengen mit analytischen Methoden zu generieren. 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.

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

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

Auf dem Kongress der Controller 2022 wurde in einigen Vorträgen deutlich, dass viele Unternehmen zu einer treiberbasierten Planung übergegangen sind, bei der von Kostenstellenverantwortlichen wenige Parameter, zum Teil nur noch vier, abgefragt werden.

Mit welchen Excel-Features und -Funktionen kann dieser Bottom-Up-/Top-Down-Planungsansatz bzw. eine treiberbasierte Planung unterstützt werden? Hier sind hauptsächlich folgende Features hilfreich:

Im Planungsprozess eines Industrieunternehmens werden aus SAP FICO für jede Kostenstelle Excel-Dateien mit Vorjahres- und Ist-Werten 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 generierten Liste ermöglicht, die Verantwortlichen zu kontaktieren.

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!

Aufgaben und Rollen von Controllern werden sehr unterschiedlich verstanden. Der Internationale Controller Verein (ICV) versteht Controller als Unterstützer, als Berater des Managements. Dem Management soll im nächsten Beispiel ein Tool zur Verfügung gestellt werden, mit dem wichtige Treiber für ein Steuerungsmodell in einem Bottom-Up/Top-Down-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“ (Umsatz, Material, Personal, 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 verlinkt:

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 unter Umständen 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öglichen!) 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 ist erkennbar, dass zum Beispiel 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.

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.

In einem Business Case soll der Erfolg einer Produkteinführung über die Simulation des Produktlebenszyklus berechnet werden:

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.

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

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 Ergebnis 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. Die folgende Abbildung 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.

Am Anfang eines Planungs- oder Simulationsmodells in Excel 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 zur 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 (zum Beispiel Salesforce o.ä.) importiert.
    
2. Diese Daten werden mit einer Regressionsanalyse untersucht und die wichtigsten Regressionsparameter (unter anderem 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 in Excel sind dabei nützlich und sollten Sie kennen:

Wie dieser Beitrag gezeigt hat, können mit geringem Zeitaufwand und in Verbindung mit den geeigneten Features und Funktionen Simulations- und Planungsmodelle in Excel erstellt werden. Das ist insbesondere für Startups 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. 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!