Personalplanung und -controlling mit Excel: Mitarbeiterkapazitäten und -kosten planen

Einleitung: Warum Personalplanung für Unternehmen unverzichtbar ist

Die Personalplanung gehört zu den wichtigsten Aufgaben im Controlling. Mitarbeiter sind in den meisten Unternehmen der größte Kostenfaktor – gleichzeitig sind sie die wertvollste Ressource. Eine professionelle Personalplanung mit Excel ermöglicht es, Personalkosten transparent zu machen, Kapazitäten optimal zu steuern und fundierte Entscheidungen für die Zukunft zu treffen.

In diesem Artikel zeigen wir Ihnen Schritt für Schritt, wie Sie mit Excel ein praxistaugliches System für Personalplanung und -controlling aufbauen. Sie lernen, welche Excel-Funktionen Sie benötigen und wie Sie diese konkret einsetzen – sodass Sie direkt mit der Umsetzung starten können.

Was umfasst Personalplanung und -controlling?

Personalplanung und -controlling beinhaltet mehrere zentrale Bereiche:

• Personalbedarfsplanung: Wie viele Mitarbeiter werden in welchen Bereichen benötigt?
• Personalkostenplanung: Welche Kosten entstehen durch Gehälter, Sozialabgaben und weitere Personalnebenkosten?
• Kapazitätsplanung: Wie viele Arbeitsstunden stehen zur Verfügung und wie werden sie genutzt?
• Personalcontrolling: Welche Kennzahlen zeigen die Effizienz und Wirtschaftlichkeit des Personaleinsatzes?

Mit einem gut strukturierten Excel-Tool behalten Sie den Überblick über all diese Aspekte und können schnell auf Veränderungen reagieren.

Schritt 1: Grundstruktur der Personalplanung in Excel aufbauen

Die Mitarbeiterstammdaten-Tabelle

Beginnen Sie mit einer Tabelle, die alle relevanten Mitarbeiterdaten enthält. Diese bildet die Grundlage für alle weiteren Berechnungen.

Benötigte Spalten:

• Mitarbeiter-ID
• Name, Vorname
• Abteilung
• Position/Funktion
• Eintrittsdatum
• Wochenstunden (Vollzeit/Teilzeit)
• Bruttogehalt (monatlich)
• Arbeitgeberanteil Sozialversicherung (in %)
• Weitere Personalnebenkosten (z.B. betriebliche Altersvorsorge)
• Status (aktiv/inaktiv)

Beispielstruktur:

Mitarbeiter-ID	Name	Abteilung	Position	Wochenstunden	Bruttogehalt	SV-Anteil AG	Status
MA001	Müller, Anna	Vertrieb	Vertriebsleiter	40	5.500 €	20%	aktiv
MA002	Schmidt, Tom	Produktion	Facharbeiter	40	3.800 €	20%	aktiv
MA003	Weber, Lisa	Verwaltung	Sachbearbeiter	30	2.700 €	20%	aktiv

Excel-Funktionen für die Stammdaten

Nutzen Sie benannte Bereiche für Ihre Tabelle, um später einfacher darauf zugreifen zu können:

1. Markieren Sie Ihre Tabelle komplett
2. Gehen Sie auf "Tabelle formatieren" (Strg+T)
3. Benennen Sie die Tabelle z.B. "MitarbeiterStamm"

Mit Datenüberprüfung können Sie Dropdown-Listen für Abteilungen und Status erstellen:

• Markieren Sie die Zellen in der Spalte "Abteilung"
• Gehen Sie auf "Daten" → "Datenüberprüfung"
• Wählen Sie "Liste" und geben Sie die Werte ein: Vertrieb, Produktion, Verwaltung, IT

Schritt 2: Personalkostenrechnung automatisieren

Berechnung der monatlichen Gesamtkosten pro Mitarbeiter

Erstellen Sie eine neue Spalte "Gesamtkosten pro Monat" mit folgender Formel:

=[@Bruttogehalt]*(1+[@[SV-Anteil AG]])+[@[Weitere Nebenkosten]]

Beispiel: Bei einem Bruttogehalt von 5.500 €, 20% SV-Anteil und 100 € weiteren Nebenkosten ergibt sich: = 5.500 € × 1,20 + 100 € = 6.700 €

Berechnung der Jahrespersonalkosten

Für die Jahresplanung benötigen Sie eine weitere Spalte:

=[@[Gesamtkosten pro Monat]]*12

Berücksichtigen Sie dabei auch Sonderzahlungen wie Urlaubsgeld oder Weihnachtsgeld. Erweitern Sie die Formel entsprechend:

=[@[Gesamtkosten pro Monat]]*12+[@Urlaubsgeld]+[@Weihnachtsgeld]

Aggregierte Auswertung nach Abteilungen

Erstellen Sie eine Übersichtstabelle, die die Personalkosten nach Abteilungen summiert. Nutzen Sie dafür die SUMMEWENN-Funktion:

=SUMMEWENN(MitarbeiterStamm[Abteilung];"Vertrieb";MitarbeiterStamm[Gesamtkosten pro Monat])

Diese Formel summiert alle monatlichen Gesamtkosten der Mitarbeiter aus der Abteilung "Vertrieb".

Tipp: Erstellen Sie eine separate Auswertungstabelle mit allen Abteilungen:

Abteilung	Anzahl Mitarbeiter	Personalkosten/Monat	Personalkosten/Jahr
Vertrieb	=ZÄHLENWENN(...)	=SUMMEWENN(...)	=SUMMEWENN(...)

Für die Anzahl der Mitarbeiter verwenden Sie:

=ZÄHLENWENN(MitarbeiterStamm[Abteilung];"Vertrieb")

Schritt 3: Kapazitätsplanung – Verfügbare Arbeitsstunden berechnen

Theoretische Jahresarbeitszeit pro Mitarbeiter

Um zu wissen, wie viele Stunden ein Mitarbeiter im Jahr zur Verfügung steht, berechnen Sie zunächst die theoretische Arbeitszeit.

Formel für theoretische Jahresstunden:

=[@Wochenstunden]*52

Bei 40 Wochenstunden ergibt das: 40 × 52 = 2.080 Stunden pro Jahr

Verfügbare Arbeitszeit (Netto-Kapazität)

Von der theoretischen Arbeitszeit müssen Urlaub, Feiertage und durchschnittliche Krankheitstage abgezogen werden.

Erweiterte Formel:

=[@Wochenstunden]*52-([@Urlaubstage]+[@Feiertage]+[@Krankheitstage])*[@Wochenstunden]/5

Beispielrechnung:

• Wochenstunden: 40
• Urlaubstage: 30
• Feiertage: 10 (je nach Bundesland)
• Krankheitstage: 10 (Durchschnitt)
• Berechnung: 40 × 52 - (30 + 10 + 10) × 40/5 = 2.080 - 400 = 1.680 Stunden

Auslastungsgrad berechnen

Wenn Sie Projekte oder Aufträge mit Zeiterfassung führen, können Sie den Auslastungsgrad ermitteln:

=[@[Ist-Stunden Jahr]]/[@[Verfügbare Stunden Jahr]]

Ein Wert von 0,85 (85%) bedeutet eine gute Auslastung, während Werte über 100% auf Überlastung hindeuten.

Schritt 4: Personalbedarfsplanung für die Zukunft

Planungstabelle für neue Stellen

Erstellen Sie eine separate Tabelle für geplante Neueinstellungen:

Geplantes Startdatum	Abteilung	Position	Wochenstunden	Geplantes Gehalt	Anteil am Jahr
01.04.2025	IT	Softwareentwickler	40	4.500 €	75%

Der "Anteil am Jahr" gibt an, wie viele Monate die neue Stelle im Planungsjahr besetzt ist. Bei Start am 1. April sind das 9 von 12 Monaten = 75%.

Formel für Personalkosten der neuen Stelle im ersten Jahr:

=[@[Geplantes Gehalt]]*(1+0,2)*12*[@[Anteil am Jahr]]

Gesamtpersonalkosten mit Neueinstellungen

Summieren Sie die aktuellen Personalkosten und die geplanten Neueinstellungen:

=SUMME(MitarbeiterStamm[Jahreskosten])+SUMME(NeueStellen[Jahreskosten])

So erhalten Sie eine realistische Prognose für das kommende Geschäftsjahr.

Schritt 5: Kennzahlen für das Personalcontrolling

Wichtige Personalkennzahlen in Excel berechnen

1. Personalkostenquote

Die Personalkostenquote zeigt, welchen Anteil die Personalkosten am Umsatz haben:

=Gesamtpersonalkosten/Umsatz

Interpretation: Eine Quote von 30% bedeutet, dass 30 Cent von jedem Euro Umsatz für Personal aufgewendet werden.

2. Personalkosten pro Mitarbeiter (Durchschnitt)

=Gesamtpersonalkosten/Anzahl_Mitarbeiter

3. Umsatz pro Mitarbeiter

=Umsatz/Anzahl_Mitarbeiter

Diese Kennzahl zeigt die Produktivität und kann im Branchenvergleich wichtige Hinweise geben.

4. Fluktuationsrate

=Anzahl_Abgänge/(Anzahl_Anfang+Anzahl_Ende)*2

5. Krankenquote

=Summe_Krankheitstage/(Anzahl_Mitarbeiter*Arbeitstage_Jahr)

Dashboard für Personalcontrolling erstellen

Erstellen Sie ein übersichtliches Dashboard mit den wichtigsten Kennzahlen. Nutzen Sie dafür:

• Bedingte Formatierung für Ampel-Systeme (z.B. Krankenquote über 5% = rot)
• Sparklines für Trenddarstellungen
• Pivot-Tabellen für detaillierte Auswertungen nach Abteilungen
• Diagramme für visuelle Darstellung (z.B. Personalkosten nach Abteilung als Kreisdiagramm)

Tipp für bedingte Formatierung: Markieren Sie die Zelle mit der Krankenquote → "Bedingte Formatierung" → "Neue Regel" → "Formel zur Ermittlung der zu formatierenden Zellen verwenden":

=UND(A1>0,05;A1<=0,07)

Diese Regel färbt die Zelle gelb, wenn die Krankenquote zwischen 5% und 7% liegt.

Schritt 6: Planung vs. Ist-Vergleich

Monatliches Tracking einrichten

Erstellen Sie eine Tabelle für den monatlichen Soll-Ist-Vergleich:

Monat	Geplante Personalkosten	Ist-Personalkosten	Abweichung	Abweichung %
Januar	85.000 €	87.200 €	2.200 €	2,6%

Formeln:

• Abweichung absolut: =[@[Ist-Personalkosten]]-[@[Geplante Personalkosten]]
• Abweichung prozentual: =[@Abweichung]/[@[Geplante Personalkosten]]

Rolling Forecast für Personalkosten

Für eine dynamische Planung empfiehlt sich ein Rolling Forecast. Erstellen Sie eine Formel, die die tatsächlichen Ist-Werte der vergangenen Monate mit den Planwerten der kommenden Monate kombiniert:

=WENN(MONAT(HEUTE())>=[@Monat];[@[Ist-Wert]];[@Planwert])

So haben Sie immer eine aktuelle Hochrechnung für das Gesamtjahr.

Schritt 7: Szenario-Analysen für Personalentscheidungen

Was-wäre-wenn-Analysen mit Excel

Nutzen Sie den Szenario-Manager von Excel, um verschiedene Personalplanungs-Szenarien zu vergleichen:

1. Gehen Sie auf "Daten" → "Was-wäre-wenn-Analyse" → "Szenario-Manager"
2. Erstellen Sie verschiedene Szenarien (z.B. "Basis", "Wachstum +10%", "Kostensenkung")
3. Definieren Sie für jedes Szenario die veränderten Werte (z.B. Anzahl Mitarbeiter, Gehaltssteigerungen)

Beispiel-Szenarien:

• Basis: Aktueller Stand
• Wachstum: 3 neue Mitarbeiter, 3% Gehaltssteigerung
• Sparprogramm: 2 Stellen einsparen, keine Gehaltssteigerung

Datentabellen für Sensitivitätsanalysen

Mit Datentabellen können Sie analysieren, wie sich Änderungen bei zwei Variablen auswirken:

1. Erstellen Sie eine Tabelle mit Gehaltssteigerungen (horizontal) und Mitarbeiterzahl (vertikal)
2. Markieren Sie die Tabelle
3. Gehen Sie auf "Daten" → "Was-wäre-wenn-Analyse" → "Datentabelle"
4. Geben Sie die Zellbezüge für beide Variablen ein

So sehen Sie sofort, wie sich verschiedene Kombinationen auf die Gesamtpersonalkosten auswirken.

Praxistipps für die Umsetzung

1. Strukturierte Arbeitsblätter anlegen

Organisieren Sie Ihr Excel-Tool in mehreren Arbeitsblättern:

• Stammdaten: Mitarbeiterliste mit allen Grundinformationen
• Kostenplanung: Detaillierte Personalkostenrechnung
• Kapazitätsplanung: Verfügbare Stunden und Auslastung
• Kennzahlen: Dashboard mit den wichtigsten KPIs
• Planung: Personalbedarfsplanung und Szenarien
• Controlling: Soll-Ist-Vergleich und Abweichungsanalyse

2. Automatisierung durch Pivot-Tabellen

Pivot-Tabellen sind ideal für flexible Auswertungen:

• Personalkosten nach Abteilung, Position oder Kostenart
• Zeitliche Entwicklung der Mitarbeiterzahl
• Vergleich verschiedener Perioden

So erstellen Sie eine Pivot-Tabelle:

1. Markieren Sie Ihre Datentabelle
2. Gehen Sie auf "Einfügen" → "PivotTable"
3. Ziehen Sie "Abteilung" in die Zeilen und "Gesamtkosten" in die Werte

3. Schutz sensibler Daten

Personaldaten sind sensibel. Schützen Sie Ihre Excel-Dateien:

• Blattschutz: Schützen Sie Arbeitsblätter mit Formeln (Überprüfen → Blatt schützen)
• Dateiverschlüsselung: Speichern Sie die Datei mit Passwort (Datei → Informationen → Arbeitsmappe schützen)
• Zugriffsrechte: Nutzen Sie unterschiedliche Dateien für verschiedene Berechtigungsstufen

4. Regelmäßige Aktualisierung

Personalplanung ist keine einmalige Aufgabe. Etablieren Sie feste Prozesse:

• Monatliche Aktualisierung der Ist-Daten
• Quartalsweise Überprüfung der Planung
• Jährliche Neuplanung für das kommende Geschäftsjahr

Erweiterte Excel-Funktionen für Profis

SVERWEIS für automatische Datenzuordnung

Wenn Sie Gehaltsdaten aus einem anderen System importieren, nutzen Sie SVERWEIS:

=SVERWEIS([@[Mitarbeiter-ID]];Gehaltstabelle;3;FALSCH)

Diese Formel sucht die Mitarbeiter-ID in einer Gehaltstabelle und gibt das Gehalt (3. Spalte) zurück.

INDEX/VERGLEICH als flexible Alternative

Noch flexibler ist die Kombination aus INDEX und VERGLEICH:

=INDEX(Gehaltstabelle[Gehalt];VERGLEICH([@[Mitarbeiter-ID]];Gehaltstabelle[ID];0))

SUMMEWENNS für mehrere Bedingungen

Wenn Sie Personalkosten nach mehreren Kriterien filtern möchten:

=SUMMEWENNS(MitarbeiterStamm[Kosten];MitarbeiterStamm[Abteilung];"Vertrieb";MitarbeiterStamm[Status];"aktiv")

Diese Formel summiert die Kosten aller aktiven Mitarbeiter im Vertrieb.

Dynamische Arrays (ab Excel 365)

Mit dynamischen Arrays können Sie mehrere Ergebnisse mit einer Formel erzeugen:

=EINDEUTIG(MitarbeiterStamm[Abteilung])

Diese Formel gibt automatisch alle verschiedenen Abteilungen aus – perfekt für automatische Dropdown-Listen.

Häufige Fehler vermeiden

Fehler 1: Sozialversicherungsbeiträge falsch berechnen

Achten Sie darauf, dass Sie den Arbeitgeberanteil korrekt berücksichtigen. In Deutschland liegt dieser bei ca. 20% des Bruttogehalts (kann je nach Krankenkasse variieren).

Fehler 2: Verfügbare Arbeitszeit zu optimistisch kalkulieren

Berücksichtigen Sie realistische Werte für:

• Urlaub (gesetzlich mindestens 20 Tage bei 5-Tage-Woche)
• Feiertage (variiert nach Bundesland: 9-13 Tage)
• Krankheitstage (Durchschnitt in Deutschland: 10-15 Tage)
• Weiterbildung und Meetings

Fehler 3: Teilzeit-Mitarbeiter nicht korrekt umrechnen

Rechnen Sie Teilzeit-Kräfte immer auf Vollzeitäquivalente (VZÄ) um:

=[@Wochenstunden]/40

Ein Mitarbeiter mit 30 Wochenstunden entspricht 0,75 VZÄ.

Fehler 4: Einmalige Kosten vergessen

Denken Sie an zusätzliche Personalkosten wie:

• Recruiting-Kosten bei Neueinstellungen
• Fortbildungskosten
• Arbeitsmittel und Hardware
• Firmenwagen oder Fahrtkostenzuschüsse

Checkliste: Ihr Personalplanungs-Tool in Excel

• [ ] Mitarbeiterstammdaten-Tabelle mit allen relevanten Informationen erstellt
• [ ] Personalkostenrechnung mit Bruttogehalt, Sozialabgaben und Nebenkosten
• [ ] Kapazitätsplanung mit verfügbaren Arbeitsstunden
• [ ] Auswertungen nach Abteilungen, Positionen und Kostenarten
• [ ] Kennzahlen-Dashboard mit den wichtigsten KPIs
• [ ] Personalbedarfsplanung für Neueinstellungen
• [ ] Soll-Ist-Vergleich für monatliches Controlling
• [ ] Szenario-Analysen für strategische Entscheidungen
• [ ] Datenschutz durch Blattschutz und Verschlüsselung
• [ ] Dokumentation der Formeln und Berechnungslogik

Fazit: Personalplanung mit Excel meistern

Mit einem gut strukturierten Excel-Tool für Personalplanung und -controlling behalten Sie jederzeit den Überblick über Ihre wertvollste Ressource: Ihre Mitarbeiter. Sie können Personalkosten transparent machen, Kapazitäten optimal steuern und fundierte Entscheidungen für die Zukunft treffen.

Die vorgestellten Funktionen und Formeln ermöglichen es Ihnen, ein professionelles Personalplanungs-System aufzubauen, das mit Ihrem Unternehmen mitwächst. Beginnen Sie mit den Grundfunktionen und erweitern Sie Ihr Tool Schritt für Schritt um zusätzliche Auswertungen und Automatisierungen.

Der größte Vorteil: Sie benötigen keine teure Spezialsoftware, sondern nutzen ein Tool, das in jedem Unternehmen verfügbar ist. Mit den richtigen Excel-Kenntnissen wird Ihre Personalplanung zum strategischen Werkzeug für bessere Unternehmensentscheidungen.

Viel Erfolg bei der Umsetzung Ihrer Personalplanung mit Excel!