Monatliche Planwerte mit Excel ermitteln

 

Wenn in einem Unternehmen die einzelnen Planwerte nur für das gesamte Planjahr ermittelt werden, bleibt die Aufgabe, diese Jahreswerte sinnvoll auf die Planmonate aufzuteilen.

Die einfachste Möglichkeit ist, den Jahreswert durch 12 Monate zu dividieren. Bekannt ist aber, dass dies in den meisten Fällen nicht so aufgeht. Abweichungen Plan-Ist sind vorprogrammiert und müssen in den monatlichen Berichterstattungen begründet werden.

Nachfolgend sind die Netzabgabemengen (Mengen, die verbraucht werden können) eines Wasserwerkes für die 12 Monate aus 7 Jahren zusammengestellt. Hieraus soll ein saisonaler Faktor ermittelt werden, der für die Monatsplanung Anwendung finden kann.

Monatsplan1

Die Monate April bis Oktober wurden im Bild aufgrund der Breite der Tabelle ausgeblendet. Alle Werte findest du in der Beispieldatei.

Richte dir eine Arbeitsmappe ein und gebe die Mengendaten dort ein.

Um zu saisonalen Faktoren zu kommen, erweiterst du die Tabelle um diese Zeilen:

Monatsplan2

Dazu verwende diese Formeln:

Monatsplan3

Mit der ersten Formel errechnest du die Mittelwerte der einzelnen Monate über die Jahre 2010-2016. Die zweite Formel errechnet die Summe dieser Mittelwerte.

Mit der Formel in Q17 errechnest du den Jahresplanwert für das Planjahr. Dazu hat z.B. die Geschäftsführung festgelegt, dass vom Jahres-Istwert aus 2016, um 0,6% reduziert, ausgegangen werden soll.

In Zeile 13 werden nun die Verhältnisse der Monats-Mittelwerte zum Gesamtmittelwert errechnet.

In R17 wird der Mittelwert aus dem Jahres-Planwert linear errechnet.

Die Monatsplanwerte in Zeile 20 errechnest du nun, indem du die Anteile mit dem linearen Mittelwert multiplizierst, allerdings nur bis Monat November. Für den Monat Dezember subtrahierst du die Summe der Planwerte Januar bis November vom Jahres-Planwert.

In Q20 wird schließlich noch die Summe der Monatsplanwerte errechnet, die erwartungsgemäß mit dem Jahresplanwert in Q17 übereinstimmt.

Damit hast du die Monatsplanwerte für das Planjahr unter Berücksichtigung saisonaler Schwankungen ermittelt.

Grafische Darstellungen lassen den saisonalen Einfluss auf die Monatsplanwerte mit einem Blick erkennen. Erstelle dazu ein Liniendiagramm mit den Monatsplanwerten und füge zusätzlich als Linie den Jahresplanmittelwert ein. Dafür hast du die Werte bereits in E22:P22 mit der Formel

=$R$17

eingetragen.

Monatsplan4

Anschaulich sind die Schwankungen über das Jahr erkennbar. Die ersten drei und die letzten drei Monatswerte liegen unter dem Mittelwert, die anderen darüber.

Diese Werte können in der Unternehmensplanung z.B. Einfluss auf die Umsatz-, Kapazitäts- und Investitionsplanung haben.

Beispieldatei:  Monatsplanwerte ermitteln

Advertisements

4 Kommentare zu „Monatliche Planwerte mit Excel ermitteln“

  1. Schöner Artikel und gute Anleitung.
    Die Planung mit der Schwankung um den Monatsdurchschnitt ist allerdings für mich ungewohnt. Eine Monatsverteilung mit der Angabe Jan 8 %, Feb. 10 % etc. des Jahreswertes ist m.E. griffiger.

    Gefällt mir

    1. Alles gut! Die Berechnung ist überhaupt nicht zu beanstanden und 100% korrekt.
      Die Monatsverteilung (die beispielhaft genannten 8%) ergäbe sich aus Ihrem Ansatz. Lediglich ab Zeile 12 wäre die Rechnung E12/$Q$12.
      Die absoluten Werte für die Monate sind natürlich identisch.
      Ich finde den Ansatz interessant, über eine Abweichung vom Durchschnittswert zur gleichen Lösung zu kommen. 😀

      Gefällt mir

    2. Danke auch für diesen Kommentar.
      Ich finde es generell gut, wenn es mehrere Lösungswege gibt. So kann der Nutzer den für sich praktikabelsten Weg wählen.
      Mir ist bekannt, dass ERP-Programme mit Planungstools meistens die Möglichkeiten einräumen, monatlich Prozente oder absolute Werte einzugeben.

      Gefällt mir

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s