Microsoft Excel im Personalwesen, 2., aktualisierte und erweiterte Auflage

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