Kennzahlen-Monitoring in Excel: Plan/Ist, Ampel und Abteilungsranking aufbauen

Mehrere Abteilungen, Dutzende Kennzahlen, jede Zahl in einer anderen Datei: In der Besprechung sieht die Leitung Einzelwerte, aber kein Gesamtbild. Wer steht gut da, wer fällt zurück, und wo geht die Entwicklung nach oben oder unten? Ohne einheitliche Berechnung lassen sich Abteilungen nicht fair vergleichen.

Dieser Artikel zeigt, wie Sie ein Kennzahlen-Monitoring in Excel aufbauen. Sie lernen, wie Plan und Ist erfasst werden, wie die Erfüllung bei „mehr ist besser“ und „weniger ist besser“ berechnet wird, wie Sie kumulieren und wie aus allem eine Ampel und ein Ranking entstehen.

Das Datenmodell: Plan und Ist nebeneinander

Legen Sie zwei Tabellen mit identischem Aufbau an, eine für den Plan, eine für das Ist. Jede Zeile ist eine Kombination aus Abteilung und Kennzahl, die Spalten sind die Monate Januar bis Dezember:

Pflegen Sie Abteilungen und Kennzahlen in einem Verzeichnis, mit Einheit, Richtung („mehr ist besser“ oder „weniger ist besser“), Art der Kumulierung (Summe oder Durchschnitt) und Gewicht. Im Ist-Blatt können Sie die Bezeichnungen per Formel aus dem Plan übernehmen, damit beide Tabellen immer zusammenpassen.

Erfüllung berechnen

Die Erfüllung hängt von der Richtung ab. Bei „mehr ist besser“ gilt Ist durch Plan, bei „weniger ist besser“ Plan durch Ist:

=WENN(Richtung="mehr";Ist/Plan;Plan/Ist)

Beispiel: Plan 2.450 Anfragen, Ist 2.600: Erfüllung 106 %. Bei Beschwerden: Plan 58, Ist 64: 58/64 = 91 %. Weniger Beschwerden wären also besser gewesen. Ist das Ist bei „weniger ist besser“ gleich 0, würde die Formel durch 0 teilen; fangen Sie das mit WENNFEHLER oder einer expliziten Regel ab.

Begrenzen Sie die Erfüllung nach oben, etwa auf 150 %, mit =MIN(Erfüllung;1,5). Sonst kann ein extremer Ausreißer den Durchschnitt einer Abteilung verzerren.

Kumulieren bis zum Berichtsmonat

Für das Jahr bis heute rechnen Sie nicht den einzelnen Monat, sondern kumuliert von Januar bis zum Berichtsmonat. Bei Mengen und Beträgen addieren Sie, bei Prozentwerten und durchschnittlichen Fristen bilden Sie den Mittelwert. Mit BEREICH.VERSCHIEBEN lässt sich der Bereich bis zum Berichtsmonat dynamisch wählen:

=SUMME(BEREICH.VERSCHIEBEN(C2;0;0;1;Berichtsmonat))

Alternativ nutzen Sie SUMMENPRODUKT mit einer Bedingung auf die Monatsnummer. Das hat den Vorteil, dass die Formel nicht flüchtig ist.

Ampel und Ranking

Legen Sie zwei Schwellen fest, etwa 95 % für „erreicht“ (grün) und 80 % für „Risiko“ (rot), dazwischen gelb:

=WENN(Erfüllung>=Schwelle_grün;"Grün";WENN(Erfüllung>=Schwelle_rot;"Gelb";"Rot"))

Die Schwellen sind Ihre internen Richtwerte; legen Sie sie in Abstimmung mit den Vorgaben Ihrer Organisation fest. Für das Ranking gewichten Sie die Kennzahlen (zum Beispiel 1 bis 5) und bilden den gewichteten Durchschnitt je Abteilung:

=SUMMENPRODUKT(Erfüllung;Gewicht)/SUMME(Gewicht)

Die Rangfolge liefert =RANG(B2;B$2:B$13). Beachten Sie die Grenzen dieses Verfahrens: Ein Ranking sagt nichts über die Qualität der Pläne. Wer sich leichte Ziele setzt, steht oben.

Entwicklung zum Vormonat

Neben dem Stand interessiert die Richtung. Vergleichen Sie die kumulierte Erfüllung dieses Monats mit der des Vormonats und zeigen Sie einen Pfeil oder Text: =WENN(B2>C2;"steigend";WENN(B2<C2;"fallend";"stabil")). So sehen Sie Abteilungen, die noch gut dastehen, aber nachlassen.

Häufige Fehler

• Kennzahlen mit unterschiedlicher Richtung gleich behandeln.
• Summen und Durchschnitte verwechseln: Eine durchschnittliche Bearbeitungsdauer wird nicht addiert.
• Zu viele Kennzahlen: Zehn aussagekräftige sind besser als fünfzig, die niemand liest.
• Gewichte und Schwellen nicht dokumentieren.

Fertige Excel-Vorlage für das Kennzahlen-Monitoring

Die kostenpflichtige Vorlage „Monitoring von Abteilungskennzahlen“ nimmt Ihnen die Konstruktion ab. Auf dem Blatt „Verzeichnisse“ stellen Sie Jahr, Berichtsmonat, Ampelschwellen, bis zu 12 Abteilungen und bis zu 20 Kennzahlen mit Einheit, Richtung, Kumulierung und Gewicht ein. „Plan“ und „Ist“ nehmen bis zu 200 Zeilen „Abteilung – Kennzahl“ mit den Monaten Januar bis Dezember auf; im „Ist“ werden die Bezeichnungen automatisch eingesetzt.

Die „Berechnung“ liefert kumulierte Erfüllung, Ampel, Entwicklung zum Vormonat und Erfüllung je Monat. Die „Übersicht“ zeigt das Abteilungsranking, die Zahl der Kennzahlen im grünen, gelben und roten Bereich, die Tabelle der Entwicklung nach Monaten und zwei Diagramme. Die Anrechnungsgrenze ist standardmäßig 150 %.

Das spart Ihnen den Aufbau der Rechenlogik und der Auswertung. Im Lieferumfang sind eine leere Tabelle und ein ausgefülltes Beispiel mit erfundenen Daten. Es ist eine Excel-Tabelle mit gewöhnlichen Formeln, keine Spezialsoftware, ohne Makros, für Excel 2007 und neuer sowie LibreOffice, auf Deutsch. Schwellen und Richtwerte sind Parameter: Gleichen Sie sie vor der Nutzung mit Ihren internen Regelungen ab.

FAQ

Wie viele Kennzahlen sollte eine Abteilung haben?

So wenige wie möglich. Fünf bis zehn Kennzahlen, die wirklich steuern, sind aussagekräftiger als eine lange Liste. Jede Kennzahl sollte eine verantwortliche Person und einen klaren Plan haben.

Wie gehe ich mit Kennzahlen um, die nicht monatlich erhoben werden?

Tragen Sie sie in den Monaten ein, in denen sie vorliegen, und legen Sie fest, ob für die Zwischenmonate der letzte Wert gilt oder die Kennzahl ausgeklammert wird. Entscheiden Sie das einheitlich und halten Sie es fest.

Soll ich Abteilungen öffentlich ranken?

Das ist eine Führungsfrage. Rankings können motivieren, aber auch Fehlanreize schaffen, etwa zu leichte Pläne. Ergänzen Sie sie immer um den Blick auf die Ziele selbst.

Wann ist ein Plan realistisch?

Wenn er auf Vorjahreswerten und erwarteten Änderungen beruht, nicht auf Wunschdenken. Prüfen Sie unterjährig, ob Pläne angepasst werden müssen, und dokumentieren Sie die Änderung, statt sie still zu überschreiben.