Startseite > Lehrgang > Sozialversicherung > EXCEL-Aufgaben zur Gleitzone
Lehrgang der Lohnabrechnung - EXCEL-Aufgaben zur Sozialversicherung
Gleitzone im Niedriglohnbereich
Erstellen Sie folgende Tabelle. Die grauen Zellen enthalten Formeln. Die gelben Zellen sind Eingabefelder.
In den Zellen D3, D6, D7 und D8 werden mit der Gültigkeitsprüfung mögliche Zelleinträge festgelegt. In D3 sollen nur Dezimalzahlen zwischen 400,01 und 800 zulässig sein. Abbildung für Zelle D3:
Die Besonderheiten bei schwankendem Verdienst werden hier nicht behandelt. Trotz Schwankungen (mehr oder weniger Stunden im Monat oder Zahlung von Urlaubs- bzw. Weihnachtsgeld) kann der Verdienst im gesamten Jahr innerhalb der Gleitzone liegen. Entscheidend ist der Durchschnitt. Für die Berechnung der SV-Beiträge sind dann drei Fälle möglich:
- Entgelt liegt innerhalb der Gleitzone
Normale Gleitzonenregelung - Entgelt liegt unterhalb der Gleitzone
In solchen Fällen errechnet sich die beitragspflichtige Einnahme durch Multiplikation des Faktors F mit dem erzielten Arbeitsentgelt. - Entgelt liegt oberhalb der Gleitzone
Die Beitragsberechnung erfolgt nach den allgemeinen Regelungen zur Beitragsberechnung in der Sozialversicherung.
Diese Aufgabe behandelt nur den ersten Fall.
In D6, D7 und D8 sind nur ja oder nein zulässig. Wählen Sie bei Zulassen Liste und trennen Sie die möglichen Eingaben mit Semikolon.
In D5 ist mit einer Formel die fiktive beitragspflichtige Einnahme zu berechnen.
F * 400 + (2 - F) * (Arbeitsentgelt - 400)
Nehmen Sie Bezug auf D3 und D4.
Übernehmen Sie mit einem Zellbezug den Wert aus D5 in C13 und den Wert aus D3 in D13.
Die Formeln in C14, C15, C16 und C17 nehmen Bezug auf C13 und den entsprechenden Prozentwert aus Spalte B. C14 verwendet also den Beitragssatz aus B14. Denken Sie an die Berechnungsvorschrift.
Von der fiktiven Einnahme berechnet man die Gesamtbeiträge zur Kranken-, Pflege-, Renten- und Arbeitslosenversicherung unter Anwendung des jeweils halben Beitragssatzes, rundet und verdoppelt anschließend diesen Betrag. Die Rundung erfolgt auf 2 Nachkommastellen.
Sie brauchen also die Funktion RUNDEN. Im ersten Schritt vernachlässigen wir die Besonderheiten für Sachsen, den
Beitragszuschlag für Kinderlose und die Nichtanwendung der Gleitzone in der Rentenversicherung. Stellen Sie die Auswahl
in den Zellen D6, D7 und D8 wie im Bildschirmbild ein (D6 nein; D7 und D8 ja). Die kopierfähige Formel in C14 sieht
so aus:
=RUNDEN(B14/2*C$13;2)*2
Für die Berechnung der Beiträge zur Krankenversicherung ist wegen der Besonderheiten der Beitragsverteilung
eine modifizierte Form anzuwenden. Danach wird der für den Arbeitnehmer insgesamt zu zahlende
Krankenversicherungsbeitrag durch Addition der getrennt berechneten gerundeten Anteile des Arbeitgebers und des
Arbeitnehmers auf die beitragspflichtige Einnahme ermittelt. Optional kann der Krankenversicherungsbeitrag durch
Anwendung des halben Beitragssatzes auf die beitragspflichtige Einnahme bei anschließender Verdopplung des
gerundeten Ergebnisses berechnet werden.
Wir rechnen mit 7,3% und 8,2%.
Die Insolvenzgeldumlage in C18 wird durch Multiplikation von C13 mit B18 und anschließender Rundung errechnet. Die Anwendung des halben Beitragssatzes ist hier nicht notwendig, da die Insolvenzgeldumlage nur der Arbeitgeber trägt. Erstellen Sie die Formel vollständig, trotz der für 2011 ausgesetzten Umlage. Damit kann der Rechner mit wenigen Eingaben auch für kommende Jahre genutzt werden.
Die Arbeitgeberanteile in Spalte D werden als nächstes berechnet. In der Krankenversicherung ist der
Sonderbeitrag von 0,9% zu beachten. Die Formel in D14 sieht so aus:
=RUNDEN((B14-0,9%)/2*D$13;2)
Vom Beitragssatz in B14 wird der Sonderbeitrag von 0,9% abgezogen und der Rest durch zwei geteilt (Klammersetzung
beachten!).
In den Formeln der Zellen D15, D16 und D17 wird nur der Beitragssatz in Spalte B durch zwei geteilt und mit D13 multipliziert. Die Rundung erfolgt ebenfalls auf 2 Nachkommastellen. In D18 übernehmen Sie mit einem Zellbezug den Wert aus C18 (Insolvenzgeldumlage trägt nur der Arbeitgeber).
In Spalte E werden von den Gesamtbeiträgen die Arbeitgeberanteile abgezogen. In E14 steht also
=C14-D14
Bilden Sie in Zeile 19 die Summen der Zellen aus den Zeilen 14 bis 18. In der verbundenen Zelle D20 ist zur Kontrolle die Summe der Arbeitgeberanteile und Arbeitnehmeranteile zu bilden (D19 + E19).
Jetzt setzen wir die drei Spezialfälle um. Als erstes soll die Elterneigenschaft betrachtet werden. Wenn in D7 ein nein steht, muss folgendes passieren:
Der Gesamtbeitrag zur Pflegeversicherung erhöht sich um 0,25% auf 12,07 €. Der Arbeitgeberanteil bleibt
unverändert. Die Erhöhung des Gesamtbeitrags schlägt voll beim Arbeitnehmer durch. Sie müssen also mit einer
WENN-Funktion in C15 die Zelle D7 (Elterneigenschaft) abfragen. Die Formel in C15 sieht so aus:
=WENN(D7="ja";RUNDEN(B15/2*C$13;2)*2;RUNDEN(B15/2*C$13;2)*2+RUNDEN(0,25%*C$13;2))
Als nächstes betrachten wir das Land Sachsen. Wenn in D6 ein ja steht, muss folgendes passieren:
Bei diesem Beispiel wurde die Elterneigenschaft in D7 wieder auf ja gestellt.
Der Arbeitgeberanteil ist in Sachsen niedriger. Wenn wir diesen verändern, wird der Arbeitnehmeranteil automatisch
größer. Das erste Prozent zahlt in Sachsen der Arbeitnehmer allein.
Hintergrund: Der Beitragssatz wurde am 01.01.1995 auf 1% der beitragspflichtigen Einnahmen festgesetzt. Zum
Ausgleich der Arbeitgeberbeiträge sollten die Bundesländer einen gesetzlichen landesweiten Feiertag, der stets auf
einen Werktag fällt, aufheben. In Bundesländern die den Feiertag gestrichen haben (alle außer Sachsen) wurde der
Beitrag von AG und AN je zur Hälfte (je 0,5%) getragen. In Sachsen hatten die AN den Beitrag allein zu tragen
(AN 1% und AG 0%). Die späteren Beitragserhöhungen wurden ohne weitere Bedingungen je zur Hälfte auf AG und AN
aufgeteilt. Damit ist die Beitragsverteilung in Sachsen 2011 so:
AG 0,475% und AN 1,475%.
Wir müssen also mit einer WENN-Funktion in D15 die Zelle D6 (Sachsen) abfragen. Die Formel in D15 sieht so aus:
=WENN(D6="ja";RUNDEN((B15-1%)/2*D$13;2);RUNDEN(B15/2*D$13;2))
Wenn in D6 ein ja für Sachsen steht, wird vom Beitragssatz in B15 1,0% abgezogen und der Rest durch zwei geteilt
(Klammersetzung beachten!). Dieser Betrag wird mit D13 multipliziert und das Ergebnis muss gerundet werden. Der
SONST-Wert der WENN-Funktion ist die Berechnung die vorher schon in der Zelle stand.
Wenn wir jetzt noch die Elterneigenschaft auf nein setzen, muss folgendes passieren:
Das letzte Problem ist die Möglichkeit des Arbeitnehmers, auf die Anwendung der Gleitzone in der Rentenversicherung
zu verzichten. Der AN muss das schriftlich gegenüber dem Arbeitgeber erklären. Die Erklärung kann nur mit Wirkung
für die Zukunft und bei mehreren Beschäftigungen nur einheitlich abgegeben werden. Sie ist für die Dauer der
Beschäftigungen bindend.
Hintergrund: Verminderte Rentenversicherungsbeiträge führen auch zu einer verminderten Rente.
Wir müssen die Formeln in C16 und C18 anpassen. Wenn in D8 ein nein steht, muss folgendes passieren:
Bei diesem Beispiel wurde die Elterneigenschaft in D7 wieder auf ja gestellt und Sachsen in D6 auf nein.
Wenn in D8 ein nein steht, wird der Gesamtbeitrag zur Rentenversicherung nicht von der fiktiven beitragspflichtigen
Einnahme in C13 sondern vom tatsächlichen Arbeitsentgelt in D13 berechnet. Die Formel in C16 sieht so aus:
=RUNDEN(WENN(D8="ja";B16/2*C$13;B16/2*D$13);2)*2
Wenn in D8 ein ja steht, erfolgt die normale Berechnung (halber Beitragssatz mal fiktive beitragspflichtige Einnahme)
sonst wird der halbe Beitragssatz mit dem tatsächlichen Arbeitsentgelt multipliziert. Die WENN-Funktion ist in der
Funktion RUNDEN enthalten. Das Ergebnis der Funktion RUNDEN wird mit zwei multipliziert.
Grundlage für die Ermittlung der Insolvenzgeldumlage ist das rentenversicherungspflichtige Entgelt bis zur Beitragsbemessungsgrenze in der Rentenversicherung. Die Formel in C18 muss also ebenfalls die Zelle D8 abfragen. Wenn in D8 ein ja steht, erfolgt die normale Berechnung (Beitragssatz in B18 mal fiktive beitragspflichtige Einnahme in C13) sonst wird der Beitragssatz in B18 mit dem tatsächlichen Arbeitsentgelt in D13 multipliziert.
Die Zellen C21 bis C26 wurden zu einer Zelle verbunden. Hier soll eine Meldung (siehe Abbildung) erscheinen, wenn in D8 ein nein steht. Sie brauchen eine einfache WENN-Funktion (der Meldungstext muss in Anführungszeichen stehen).
Bei Problemen: E-Mail an lohn@lohn-info.de
Es folgen weitere Hinweise.
Einen praktischen Einstieg zum Thema Lohnabrechnung liefern folgende Bücher: