Excel für Controlling: der Plan-Ist-Vergleich als Praxisaufgabe

Kurz gesagt: Für Excel im Controlling brauchst du weniger exotische Funktionen als sichere Grundlagen. Summen mit Bedingungen, saubere Prozentrechnung, feste Bezüge und eine Regel, die Ausreißer sichtbar macht, tragen schon einen großen Teil der Monatsberichte.

Welche Excel-Kenntnisse im Controlling wirklich zählen

Controller arbeiten mit Daten aus dem Buchhaltungssystem, die meist als lange Liste exportiert werden. Die Aufgabe ist fast immer dieselbe: verdichten, vergleichen, erklären. Dafür reichen diese Werkzeuge, wenn du sie sicher beherrschst:

• SUMMEWENNS, um Buchungen nach Kostenstelle, Konto und Monat zu summieren.
• Absolute Bezüge, damit eine Formel beim Kopieren auf feste Werte wie Planzahlen oder Sätze zeigt.
• WENN für Statusspalten wie „im Plan“ oder „prüfen“.
• Bedingte Formatierung, damit große Abweichungen sofort auffallen.
• XVERWEIS, um Planwerte, Kontenbezeichnungen oder Verantwortliche aus anderen Tabellen zu holen.
• WENNFEHLER, damit fehlende Werte keinen Bericht mit #NV oder #DIV/0! füllen.
• Datumsfunktionen wie MONAT und JAHR, um Buchungsdaten Perioden zuzuordnen.
• PivotTables für schnelle Auswertungen, bevor du einen festen Bericht baust.

Die Praxisaufgabe: Kostenstellenbericht für das erste Quartal

Du arbeitest im Controlling eines mittelständischen Unternehmens. Die Buchhaltung hat die Kosten des ersten Quartals exportiert, jede Zeile ist eine Buchung mit Monat, Kostenstelle und Betrag. Die Kostenstellen tragen Nummern: 4100 Vertrieb, 4200 Marketing, 4300 IT, 4400 Personal, 4500 Einkauf und 4600 Produktion. Die Geschäftsführung möchte wissen, wo die Kosten vom Plan abweichen und welche Bereiche sie sich genauer ansehen sollte. Du gehst in drei Schritten vor.

Schritt 1: Ist-Kosten aus den Buchungen verdichten

Zuerst brauchst du die Ist-Kosten einer Kostenstelle in einem Monat. Kostenstelle und Monat stehen in eigenen Zellen, damit derselbe Bericht für jede Kombination funktioniert.

Aufgabe In A2:C9 stehen die Buchungen des ersten Quartals. Berechne in F3 mit SUMMEWENNS die Kosten der Kostenstelle aus F1 im Monat aus F2. Nimm die Kriterien aus den Zellen, damit der Bericht für jede Kostenstelle funktioniert.

	A	B	C	D	E	F
1	Monat	Kostenstelle	Betrag €		Kostenstelle	4200
2	1	4100	12400		Monat	2
3	2	4200	8300		Summe €
4	1	4200	7900
5	2	4100	13100
6	2	4200	2450
7	3	4200	8800
8	2	4300	5600
9	3	4100	12750

Die Formel =SUMMEWENNS(C2:C9;B2:B9;F1;A2:A9;F2) ist das Herzstück vieler Controlling-Dateien. In einer echten Datei stehen die Kostenstellen untereinander und die Monate nebeneinander, und dieselbe Formel füllt die ganze Matrix. Dafür brauchst du gemischte Bezüge wie $A3 und B$2.

Schritt 2: Plan-Ist-Abweichung in Prozent

Jetzt liegen Plan und Ist je Kostenstelle nebeneinander. Eine Abweichung in Euro allein sagt wenig: 2.000 € mehr sind bei der Produktion kaum der Rede wert, beim Einkauf schon. Deshalb rechnest du die Abweichung in Prozent.

Aufgabe Berechne in D2 die prozentuale Abweichung der Ist-Kosten vom Plan. Die Formel wird danach bis D7 nach unten ausgefüllt. Das Ergebnis erscheint als Dezimalzahl: 0,15 bedeutet 15 % über Plan.

	A	B	C	D
1	Kostenstelle	Plan €	Ist €	Abweichung
2	4100 Vertrieb	37500	38250
3	4200 Marketing	24000	27600
4	4300 IT	16000	15200
5	4400 Personal	9000	9450
6	4500 Einkauf	6500	7280
7	4600 Produktion	52000	49400

Bei Kosten heißt ein positiver Wert „teurer als geplant“. Bei Umsätzen ist es umgekehrt. Schreib deshalb in die Überschrift, wie die Abweichung zu lesen ist, oder rechne sie konsequent so, dass ein negativer Wert immer schlecht ist.

Schritt 3: Ausreißer automatisch markieren

Niemand liest in einem Bericht jede Zahl. Die Geschäftsführung will auf einen Blick sehen, welche Kostenstellen mehr als 10 % über Plan liegen. Eine Regel der bedingten Formatierung erledigt das und passt sich an, sobald neue Zahlen kommen.

Aufgabe Markiere mit bedingter Formatierung die ganzen Zeilen A2:C7, deren Ist-Kosten den Plan um mehr als 10 % überschreiten. Schreib die Formel der Regel so, wie sie für die erste Zelle A2 gilt.

	A	B	C
1	Kostenstelle	Plan €	Ist €
2	4100 Vertrieb	37500	38250
3	4200 Marketing	24000	27600
4	4300 IT	16000	15200
5	4400 Personal	9000	9450
6	4500 Einkauf	6500	7280
7	4600 Produktion	52000	49400

Wer statt Farbe lieber eine Textspalte möchte, schreibt daneben =WENN((C2-B2)/B2>0,1;"prüfen";"im Plan"). Das hat einen Vorteil: Nach der Statusspalte kannst du filtern und sortieren.

Typische Berichte im Controlling

Bericht	Worum es geht	Excel-Werkzeuge
Plan-Ist-Vergleich	Kosten oder Umsätze je Bereich gegen das Budget	SUMMEWENNS, Prozentformel, bedingte Formatierung
Kostenstellenbericht	Monatliche Kosten je Kostenstelle für die Verantwortlichen	SUMMEWENNS mit gemischten Bezügen, PivotTable
Forecast	Hochrechnung bis Jahresende aus Ist und Rest-Plan	SUMME, WENN je Monat, absolute Bezüge
Abweichungsanalyse	Erklärung der größten Abweichungen	SORTIEREN, FILTER, Statusspalte mit WENN
Deckungsbeitragsrechnung	Erlöse minus variable Kosten je Produkt	XVERWEIS, SUMMEWENNS, einfache Formeln

Tipps für saubere Controlling-Dateien

Daten und Bericht trennen

Leg den Export auf ein eigenes Blatt und rechne den Bericht auf einem anderen. Kommt im nächsten Monat ein neuer Export, ersetzt du nur die Daten, und alle Formeln rechnen weiter.

Parameter in Zellen statt in Formeln

Schwellen wie 10 %, Monat oder Kostenstelle gehören in eigene, beschriftete Zellen. Dann sieht jeder, mit welchen Annahmen gerechnet wird, und niemand muss Formeln umschreiben.

Durch null teilen abfangen

Hat eine Kostenstelle keinen Planwert, ergibt die Abweichung #DIV/0!. Mit =WENNFEHLER((C2-B2)/B2;"") bleibt die Zelle leer, und der Fehler zieht sich nicht durch Summen und Diagramme.

Abstimmen, bevor du berichtest

Prüfe, ob die Summe deines Berichts der Summe des Exports entspricht. Eine Kontrollzelle, die die Summe des Berichts von der Summe aller Buchungen abzieht, sollte 0 ergeben. Ist sie es nicht, fehlt meist eine Kostenstelle im Bericht oder ein Monat im Kriterium. Das spart unangenehme Rückfragen.

Häufige Fragen