Planung und Simulation mit Excel – noch zeitgemäß?

Kostenloser Download

Weiterbildungsprogramm für Führung & Management

01. Dezember 2022
Controlling
0 Kommentare

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.

Rainer Pollmann

Geschäftsführender Partner | PRT Pollmann & Rühm Training

Zum Profil

Excel in Planungs- und Simulationsprozessen

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

In einem agilen Umfeld, in dem Prozesse und Standards (noch) nicht existieren und

Für die Berechnung von Business Cases am besten eingesetzt werden.

Excel-Funktionen für treiberbasierte Planung

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:

Power Query

Szenario-Manager

Solver

1. Use Case: Upload-Liste für SAP FICO erstellen

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!

2. Use Case: Gegenstromverfahren in Excel anwenden

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:

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.

In diesem Beispiel wird mit vereinfachten Szenarien gearbeitet. Zum Arbeiten mit der Szenario-Technik lesen Sie bitte diesen Beitrag, in dem das Arbeiten mit Zukunftsbildern beschrieben wird.

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.

3. Use Case: Simulation eines Produktlebenszyklus durchführen

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

Die Analysen des Marketings lassen einen Produktlebenszyklus von etwa vier Jahren mit einer 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 Produktlebenszyklus 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.

Das gezeigte Modell ist schnell erstellt und liefert mittels der Simulationen die Grundlagen für eine Entscheidung, ob dieses Projekt weiterverfolgt 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 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.

4. Use Case: Prognoseparameter für ein Modell ermitteln

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:

REGRESSION

TREND()

VARIATION()

NBW()

ggf. weitere statistische Funktionen

Bedingte Formatierung

Ist Excel also zeitgemäß für Planung und Simulation? Aber sicher!

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!

Kostenloser Download

BE THE BEST: Seminarprogramm 2023

Holen Sie sich neue Impulse für Ihren Alltag! Wir geben in diesem Seminarprogramm zum Thema Führung & Management praktische Tipps und passende Seminarempfehlungen, mit denen Sie Ihre größten Herausforderungen im Daily Business erfolgreich meistern.

Gefällt Ihnen, was Sie lesen? Teilen Sie diesen Beitrag oder hinterlassen Sie uns einen Kommentar!

Kommentare

Keine Kommentare

Kommentar schreiben

* Diese Felder sind erforderlich

Die Management Circle AG mit Sitz in Eschborn im Taunus ist spezialisiert auf die berufliche Weiterbildung in Form von Seminaren, Konferenzen und Kongressen für Fach- und Führungskräfte.

© Management Circle 2023