Absatzmengenplanung mit Pivottabelle (I)

Planung gehört zu den wichtigsten Aufgaben eines Unternehmens. Das Controlling hat die Aufgabe, diesen Prozess zu koordinieren und zu organisieren.

Eine Aufgabe ist dabei, Planungsvorgaben zu tätigen:

• Wer plant was?
• Wie ist zu planen?
• Welche Zeiträume sind zu planen?
• Welche Formulare sind auszufüllen und abzugeben?
• Welche Zeitvorgaben gibt es für die Planung?
• usw.

Ein wichtiger Teilprozess ist in jeder Planung der Absatzmengenplan.

In diesem Beitrag soll es um eine praktikable Excel-Lösung für einen Absatzmengenplan in einem Mehrproduktunternehmen gehen.

Welche Möglichkeiten stehen in Excel zur Verfügung?

Das fiktive Unternehmen soll eine Möbelfabrik sein, die drei Produkte herstellt. Es plant den Produktabsatz nach Produkten, nach Regionen und nach Kunden.

Angenommen, es besteht seitens der Vertriebsleitung der Wunsch, den Plan deshalb in verschiedenen Ansichten darzustellen:

• Produkt-Region-Kunde
• Produkt-Kunde-Region
• Region-Produkt-Kunde
• Region-Kunde-Produkt
• Kunde-Region-Produkt
• Kunde-Produkt-Region

Für das fiktive Unternehmen habe ich mich selbst zum Controller ernannt, der für die Absatzmengenplanung zuständig ist.

Eine Möglichkeit der Planung ist die reine Excel-Lösung mit Funktionen und Formeln. Versuche meinerseits führten zwar zunächst zu Teilergebnissen, doch schnell wurde klar, dass dieses Vorhaben sich recht kompliziert gestaltet und zu Monsterformeln führen könnte.

Eine weitere Möglichkeit ist eine VBA-Lösung. Eine solche war aber nicht erwünscht, teils aus Angst vor den Makros, teils aus Nichtwissen um die Programmierung.

So entschied ich mich letztendlich für eine Lösung auf Basis einer Pivottabelle.

Was ist eine Pivottabelle?

Wikipedia schreibt dazu:

„Pivot-Tabellen … : Dreh-, Angelpunkt, Schlüsselfigur, Achse; auch Pivot-Table-Berichte oder pivot table reports) sind eine spezielle Art von Tabellen, die die Möglichkeit bieten, Daten einer Tabelle in verschiedener Art darzustellen und auszuwerten, ohne die Ausgangsdaten bzw. -tabelle(n) dabei ändern zu müssen.

Eine Pivot-Tabelle stellt aufgrund der verwendeten Aggregierung in den Datenfeldern die Ausgangsdaten in verdichteter, zusammengefasster Form dar. Das ist zwar mit Informationsverlust verbunden, aber genau darin liegt der Nutzen einer Pivot-Tabelle. Sie ist ein Hilfsmittel, um große Datenmengen auf überschaubare Größen zu reduzieren und einfache Auswertungen durchzuführen. Pivot-Tabellen können nur für die Abfrage, nicht zur Vermehrung von Daten verwendet werden. [1]

Das besondere Augenmerk will auf die Möglichkeit, Daten einer Tabelle in verschiedener Art darzustellen, lenken. Das ist genau das, was ich für meine Aufgabe brauche.

Wie werden die Plandaten zusammengetragen?

Die Vertriebsleute fassen ihre Einzelplanungen in eine Tabelle ein, die ich als Datenbank nutzen kann.

So etwa könnte die Tabelle aussehen:

Die Datensätze sind das Ergebnis von Gesprächen mit den Kunden. Daran, dass das Jahr Bestandteil der Datensätze ist, ist zu erkennen, dass die Tabelle beliebig auf kommende Jahre erweitert werden kann.

Die Datenart ist hier „Plan“, denn im Beitrag geht es um Planung. Klar wird aber, dass auch Istwerte in die gleiche Tabelle eingetragen werden könnten, die Reihenfolge spielt keine Rolle dabei.

Meine Beispieltabelle enthält 288 Datensätze für die Absatzmengenplanung der drei Produkte Bett, Schrank und Tisch in den Jahren 2016 und 2017.

Zu sehen ist auch, dass die Datensätze etwas durcheinander aufeinander folgen. Zusammenfassungen sind so ohne weiteres noch nicht möglich.

Beliebig viele weitere Datenfelder können noch ergänzt werden, z.B. Ort und Anschrift des Kunden, Ansprechpartner beim Kunden, Namen des Vertriebsmitarbeiters, Beleg-Nummern, Liefer-KW u.a.m.

Du wirst vielleicht einwenden, dass mit SUMMEWENNS oder SUMMENPRODUKT schon einiges gemacht werden kann. Das ist richtig, ich aber will hier eine Pivottabelle nutzen.

Wie erstelle ich eine Pivottabelle?

Die gesamte Datenbank will ich nun mit einer Pivottabelle darstellen. Dazu markiere ich den gesamten Bereich einschließlich Überschriften, bei mir ist das der Bereich A4:G292 im Arbeitsblatt Daten1. Im Menü Einfügen wähle ich unter Tabellen „PivotTable“ aus. Es erscheint diese Maske:

Nun ist noch festzulegen, wo, d.h., in welchem Arbeitsblatt in welcher Zelle beginnend die Pivottabelle abgelegt werden soll. Ich entscheide mich für ein vorhandenes Arbeitsblatt. Dazu nutze ich den Button rechts im Eingabefeld, gehe dann in mein Arbeitsblatt „Focus1“ und klicke z.B. auf die Zelle B9. Dann bestätige ich die Eingabe und erhalte eine Pivottabelle, die erst einmal so aussieht:

Auf der rechten Seite des Bildschirms befindet sich die Feldliste, aus der ich die Felder auswählen kann:

Wie ist damit umzugehen?

Ich habe zunächst den Haken bei Datenart gesetzt, die linke Taste der Maus festgehalten und das Feld in den Berichtsfilter gezogen. Auf die gleiche Art habe ich alle anderen benötigten Felder an die entsprechenden Stellen gebracht. Es müssen nicht zwingend alle Felder ausgewählt und verschoben werden, nur die, die in der Pivottabelle erscheinen sollen.

Beachte: die Felder, die ich den Zeilenbeschriftungen zugeordnet habe, stehen in einer bestimmten Reihenfolge, nämlich Produkt – Region – Kunde und definieren damit eine der oben erwähnten möglichen Ansichten. Will ich Region – Produkt – Kunde sehen, halte ich das Feld Region mit der linken Taste und schiebe es eine Zeile nach oben. Ein dicker Strich signalisiert mir den Vollzug.

Übrigens, rechts neben dem Schriftzug „Wählen Sie die Felder aus, die Sie dem Bericht hinzufügen möchten“ befindet sich ein Auswahlbutton. Nach Klick auf den Pfeil werden die Möglichkeiten zur Anordnung der Feldliste offengelegt und wählbar.

Nachdem ich nun meine Auswahl getroffen habe, ist die Pivottabelle im Entwurf fertig, hier ein Auszug:

Beachte bitte, dass nicht alle enthaltenen Summen einen Sinn machen, in der obigen Abbildung z.B. die Summen bei Nord und Ost, denn es werden Betten, Schränke und Tische addiert. Dennoch sind alle Informationen enthalten, die benötigt werden. Es ist also stets zu überlegen, welche Ansicht sinnvoll ist. Die Ansicht Produkt – Region – Kunde ist sinnvoll.

Der Beitrag wird doch wieder recht lang. Ich unterbreche hier deshalb, in der nächsten Woche erkläre ich, wie mit der Pivottabelle gearbeitet werden kann.

[1] https://de.wikipedia.org/wiki/Pivot-Tabelle