Unterjährige Berechnung des Weihnachtsgeldes
251
Eine ausführliche Erläuterung der Funktion
DATEDIF()
finden Sie in Kapitel 4 im Abschnitt »Listen mit
stichtagsbezogenem Alter«.
Die Funktion
DATEDIF() wird in diesem Beispiel in die WENN()-Bedingung eingebettet:
=WENN(Stichtag>Austritt;0;DATEDIF(D2;Stichtag;"M"))
Abbildung 6.45 In der Spalte R berechnen Sie die Beschäftigungsmonate
Schreiben Sie die Formel in die Zelle R2 und notieren Sie in der Zelle R1 den Text B-Monate. Weisen
Sie schließlich der Spalte R den Namen BMonate zu.
Berechnen des Weihnachtsgeldes
In die Berechnung des Weihnachtsgeldes fließen mehrere Rahmenbedingungen ein. Beschäftigte, die
zum Stichtag ausgetreten sind und daher nicht mehr abgerechnet werden, dürfen nicht in die Berech-
nungslogik fallen. Es sollen in diesem Beispiel alle außertariflich Beschäftigten einen festen Betrag
von 3.500 Euro erhalten. Den Wert haben Sie bereits auf dem Tabellenblatt Parameter in der Zelle mit
dem Namen AT _ W G hinterlegt. Alle tariflich Beschäftigten sollen entsprechend ihrer Betriebszugehö-
rigkeit einen prozentualen Anteil ihres Monatsentgelts erhalten. Ist ein Beschäftigter weniger als zwölf
Monate im Unternehmen, dann soll sein Weihnachtsgeld nur anteilig auf die letzten zwölf Monate
gebildet werden.
Wenn Sie die gesonderte Regelung für die außertariflich Beschäftigten nutzen wollen, gleichfalls aber
nicht alle diese Vollzeit arbeiten, können Sie das Weihnachtsgeld für diese mit einer Prüfung auf die Wochenarbeitszeit
angleichen. Ein Beispiel: Ausgehend von einer 40-Stunden-Woche der außertariflich Beschäftigten erhalten Sie den Aus-
druck
3500*IRWAZ/40
anstelle des Wertes
3500
aus dem nachfolgend gezeigten Beispiel.
Tragen Sie das Weihnachtsgeld in die Spalte S ein, welche Sie in der ersten Zeile mit Weih nach tsgeld
überschreiben. Die gesuchte Formel können Sie am einfachsten von innen nach außen erarbeiten.
Betrachten Sie zunächst das Weihnachtsgeld für die tariflichen Angestellten. Die Anzahl der Beschäf-
tigungsmonate ist in der Spalte BMonate ausgewiesen und das Monatsentgelt in der Spalte P, die Sie
zuvor mit Monatsentgelt benannt haben. Den gesuchten Prozentsatz aus der Liste WG_Staffel können
Sie mit der Formel
=SVERWEIS(BMonate;WG_Staffel;2)*Monatsentgelt ermitteln.
Jetzt sollten Sie mit der Funktion
WENN() überprüfen, ob der Beschäftigte mindestens zwölf Monate im
Unternehmen ist. Für den Fall, dass er weniger als zwölf Monate dem Betrieb angehört, soll sein
Weihnachtsgeld anteilig auf die letzten zwölf Monate, also mit dem Faktor
BMonate/12 berechnet wer-
den. Das bedeutet, dass ein Beschäftigter, der am 01.04. des betrachteten Jahres angefangen hat, am
HINWEIS
HINWEIS
252
Kapitel 6: Abschlussarbeiten und unterjährige Berechnungen
Stichtag 01.12. exakt acht Monate gearbeitet hat. Da er bis zum Ende des Jahres insgesamt neun
Monate gearbeitet haben wird, ist mit dem Faktor 9/12 zu rechnen. Daraus ergibt sich diese Teilfor-
mel:
=WENN(BMonate>=12;SVERWEIS(BMonate;WG_Staffel;2);SVERWEIS(BMonate;WG_Staffel;2)*(BMonate+1)/12)*Monatsentgelt
Wenn Ihr Stichtag auf den ersten Dezember eines Jahres fällt, trifft die oben genannte Regelung zu.
Prüfen Sie hingegen mit dem Stichtag 30. November, sollten Sie den Ausdruck wie folgt verändern:
=WENN(BMonate>=12;SVERWEIS(BMonate;WG_Staffel;2);SVERWEIS(BMonate;WG_Staffel;2)*(BMonate+2)/12)*Monatsentgelt
Damit haben Sie den Kern der Berechnung bereits entwickelt. Nutzen Sie ein weiteres Mal die Funk-
tion
WENN(), um eine Unterscheidung zwischen tariflich und außertariflich Beschäftigten zu erreichen.
Die Überprüfung geht auf die Spalte Ta r i f t y p zurück, in der nach dem Text AT gesucht wird:
=WENN(Tariftyp="AT";3500;WENN(BMonate>=12;SVERWEIS(BMonate;WG_Staffel;2);SVERWEIS(BMonate;WG_Staffel;2)*
(BMonate+1)/12)*Monatsentgelt)
Dieser Ausdruck muss zum Schluss noch kaufmännisch auf zwei Nachkommastellen gerundet wer-
den, sodass Sie abschließend diese Formel zur Ermittlung des Weihnachtsgeldes erhalten:
=RUNDEN(WENN(Tariftyp="AT";3500;WENN(BMonate>=12;SVERWEIS(BMonate;WG_Staffel;2);SVERWEIS(BMonate;
WG_Staffel;2)*(Bmonate+1)/12)*Monatsentgelt);2)
Abbildung 6.46 Berechnen Sie das Weihnachtsgeld der Beschäftigten in Abhängigkeit von der Betriebszugehörigkeit
Nach der Eingabe der Formel markieren Sie die Spalte S und benennen sie mit dem Namen WGeld.
Um zu erfahren, welchen Betrag das Unternehmen zusätzlich für die Zahlung des Weihnachtsgeldes
aufbringen muss, sollten Sie zudem die Arbeitgeberanteile an der Sozialversicherung in einer reali-
tätsnahen Abschätzung hinzuziehen. Auch hier können Sie auf Ihre Vorarbeit auf dem Arbeitsblatt
Parameter zurückgreifen. Legen Sie in der Spalte T eine Überschrift SV-Anteil an und schreiben Sie in
die Zelle T2 diese Formel:
=WENN(Monatsentgelt*12,72+WGeld<BBGW1;WGeld*SV1;WENN(Monatsentgelt*12,72<BBGW1;(BBGW1-(Monatsentgelt
*12,72))*SV1;0))+WENN(Monatsentgelt*12,72+WGeld<BBGW2;WGeld*(SV2+SV3);WENN(Monatsentgelt*12,72<BBGW2;
(BBGW2-(Monatsentgelt*12,72))*(SV2+SV3);0))
Der Ausdruck Monatsentgelt*12,72 spiegelt das Jahresentgelt inklusive einer zusätzlichen Urlaubsvergü-
tung wider. Diese wurde mit 2,4 Prozent bei 30 Urlaubstagen auf insgesamt 72 Prozent kalkuliert. Ist
die zusätzliche Urlaubsvergütung in Ihrem Unternehmen abweichend geregelt, ist der Wert entspre-
chend anzupassen.
ACHTUNG

Get Microsoft Excel im Personalwesen, 2., aktualisierte und erweiterte Auflage now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.