Stanisław Kijak

Przedmiot: przedmioty zawodowe

Rozrachunki z tytułu wynagrodzeń w arkuszu kalkulacyjnym

Artykuł opublikowany w specjalistycznym serwisie edukacyjnym http://awans.szkola.net/

Stanisław Kijak

Zespół Szkół Ekonomicznych w Mielcu

 

Rozrachunki z tytułu wynagrodzeń w arkuszu kalkulacyjnym

Pomoc do prowadzenia zajęć edukacyjnych z rachunkowości w liceum ekonomicznym, liceum handlowym, a także w ramach programu pracowni ekonomiczno-informatycznej w szkołach ponadpodstawowych, ponadgimnazjalnych i na kierunkach policealnych.

Prowadzenie korespondencji w sprawach osobowych, wprowadzanie i aktualizowanie akt osobowych pracowników, prezentowanie danych o pracownikach wg dowolnie wybranych kryteriów, wystawianie zaświadczeń pracownikom, rejestrowanie stałych i ruchomych składników płac, sporządzanie listy płac, przygotowanie deklaracji ZUS, obliczanie podatku dochodowego od osób fizycznych, to podstawowe umiejętności, których ukształtowanie przy wykorzystaniu informatycznych systemów kadrowo-płacowych jest jednym z zadań zajęć edukacyjnych w ramach programu pracowni ekonomiczno-informatycznej w szkołach ekonomicznych i handlowych. Profesjonalne programy kadrowo-płacowe, umożliwiające pełną obsługę działu kadry-płace z powodzeniem spełniają swoje zadania w każdej nowoczesnej firmie, jednakże jako części zintegrowanych systemów wymagają poznania specyficznego interfejsu użytkownika, co w edukacyjnej pracy z uczniami lub słuchaczami bazującymi na umiejętnościach kształtowanych na lekcjach „Elementy informatyki” czy „Informatyka” stwarza pewne utrudnienia.

Celem niniejszej publikacji jest zaprezentowanie, jak do realizacji tych zadań można wykorzystać arkusz kalkulacyjny Excel. Praktyczny przykład rozwiązania zawiera plik Wynagrodzenia.xls, który zawiera propozycje projektów baz danych, koniecznych formuł obliczeniowych i funkcji oraz system makr i łączy integrujących poszczególne arkusze projektu.

Wstęp

Prowadzenie rozrachunków z tytułu wynagrodzeń w prezentowanym projekcie sprowadza się do:

• wprowadzenia do arkusza Stawki stawek podatku dochodowego, składek na ubezpieczenia społeczne i zdrowotne oraz zasiłków, które należy aktualizować zgodnie zobowiązującymi przepisami prawa regulującymi sposób obliczania wynagrodzeń pracowników,

• wprowadzenia danych osobistych, adresowych i płacowych pracowników do arkuszy Dane osobiste i adresowe, Dane płacowe,

• wpisania do arkusza Lista płac kodów pracowników dla których naliczone ma być wynagrodzenie,

• wykorzystania formantu Pokrętło do wybierania kodów pracowników, przy sporządzaniu dokumentacji kadrowo-płacowej - arkusz ZUS,

• ewentualnego skorygowania formuł obliczających dodatek stażowy w celu przystosowania ich do przyjętego systemu wynagradzania - arkusz Dodatek stażowy.

Wprowadzanie i aktualizowanie akt osobowych pracowników

Dane osobiste, dane adresowe, dane do ubezpieczenia, informacje o zatrudnieniu, czasie pracy, opisy składników wynagrodzenia, opisy potrąceń, stawki podatków i stawek na ubezpieczenia społeczne i zdrowotne oraz inne dane niezbędne do rozrachunków z tytułu wynagrodzeń z powodzeniem można wprowadzać wykorzystując tabele, które są podstawowym dokumentem arkusza kalkulacyjnego.
Komórki tabeli przechowywać mogą:

• tekst,

• liczby,

• formuły obliczeniowe.

Wprowadzanie danych można dokonywać bezpośrednio do komórek tabeli arkusza,

Tabela Dane osobiste i adresowe

lub przy pomocy formularza, który wywołuje się na ekran poprzez zaznaczenie wiersza poniżej wiersza nagłówkowego oraz uruchomienie polecenia Formularz z menu Dane.

Formularz do wpisywania danych

Każda tabela arkusza staje się tym sposobem swoistą bazą danych,

a informacje zawarte w jej wierszu nagłówkowym - nazwami pól tej bazy.

Nazwy pól w arkuszu Dane osobiste i adresowe

Ze względu na częste odwołania do danych zamieszczonych w poszczególnych arkuszach, zdefiniowane zostały nazwy danych w poszczególnych bazach (po zaznaczeniu potrzebnych danych należy uruchomić polecenie Nazwa/Definiuj... z menu Wstaw).

W prezentowanym projekcie zdefiniowano nazwy:

• kadry - z danymi osobistymi i adresowymi, obejmującą dane z arkusza Dane osobiste i adresowe zawarte w polach: Kod pracownika, Nazwisko, imię , PESEL, NIP, Data zatrudnienia, Dział, Data urodzenia, Kraj, Województwo, Powiat, Gmina ulica, Nr domu, Nr lokalu, Miejscowość, Kod pocztowy, Poczta - (18 kolumn),

• płace - z danymi płacowymi, obejmującą dane z arkusza Dane płacowe zawarte w polach: Kod pracownika, Płaca podstawowa, Dodatek motywacyjny, Dodatek stażowy, Dodatek funkcyjny, Wynagrodzenie za czas niezdolności do pracy wypłacane ze środków zakładu pracy, Wynagrodzenie za czas niezdolności do pracy wypłacane ze środków ZUS, Składka ubezpieczenia na życie, Inne potrącenia, Zasiłek rodzinny (12 kolumn, w tym dwie ukryte),

• lista - obejmująca dane wyliczane na liście płac (27 kolumn),

• dodatek - z danymi do naliczenia dodatku stażowego, obejmującą pola: Kod pracownika, Data zatrudnienia, Staż, Płaca podstawowa, Dodatek stażowy z arkusza Dodatek stażowy (5 kolumn).

Aby mieć możliwość w przyszłości wprowadzania do baz, danych nowozatrudnionych pracowników (nowych rekordów bazy), zakresy definiowanych obszarów danych zaznaczone zostały z odpowiednią rezerwą (do 50 pracowników).

Jak można wywnioskować ze wstępnych uwag, dane osobiste, dane adresowe, dane do ubezpieczenia, informacje o zatrudnieniu, czasie pracy, opisy składników wynagrodzenia i potrąceń związane są z tym samym pracownikiem i zawierać mogą wiele identycznych informacji. W związku z tym umieszczono w każdej bazie pole z danymi sterującymi o nazwie Kod pracownika, które stanowić będzie odniesienie - podstawę wyszukiwania do Listy płac, Deklaracji ZUS, PIT, zaświadczeń i innych dokumentów kadrowo-płacowych.

W przedstawionym projekcie wprowadzanie i aktualizowanie danych osobowych pracowników dokonuje się w arkuszach Dane osobiste i adresowe oraz Dane płacowe. Dzięki stworzonemu systemowi łącz Kod pracownika wprowadzany jest równocześnie do arkuszy: Dane osobiste i adresowe, Dane płacowe, Dodatek stażowy. Wprowadzenie symbolu N w polu Dział uaktywnia procedurę automatycznego naliczania dodatku stażowego (wg systemu przejętego dla nauczycieli), niezależnie od danych wprowadzanych do pola Dodatek stażowy w arkuszu Dane płacowe. Przy podawaniu informacji o kosztach wystarczy podać cyfrowo jeden z czterech schematów ich naliczania.

Sporządzanie listy płac

Wprowadzenie kodu pracownika (liczba w formacie 0000) do pola Kod pracownika w arkuszu Lista płac, który zawiera tradycyjny wzór listy płac z układem 26 kolumn, uruchamia automat obliczeń miesięcznego wynagrodzenia pracownika zatrudnionego na podstawie umowy o pracę. Automat w oparciu o zaprojektowane formuły obliczeniowe i funkcje realizuje:

• wyszukiwanie na podstawie Kodu pracownika stosownych informacji w arkuszach Dane osobiste i adresowe oraz Dane płacowe i umieszczenie ich w kolumnach: 2, 3, 4, 5, 6, 7, 8, 15, 22, 23, 24 arkusza Lista płac,
  

• obliczanie wynagrodzenia brutto, potrąceń, składek ubezpieczenia społecznego i zdrowotnego oraz płacy netto i zachowanie wyników obliczeń w kolumnach:
  9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20, 21, 25.

Wyszukiwanie

Możliwość wyszukania informacji w arkuszach Dane osobiste i adresowe i Dane płacowe daje funkcja arkusza kalkulacyjnego WYSZUKAJ.PIONOWO, którą wykorzystuje się wg schematu:

=WYSZUKAJ.PIONOWO(odniesienie;tablica;nr_kolumny;kolumna)

Odniesienie   jest wartością, którą należy znaleźć w pierwszej kolumnie tablicy. Odniesienie może być wartością, adresem lub łańcuchem tekstowym; w naszym przypadku odniesieniem będzie wprowadzona w każdej bazie zmienna sterująca Kod pracownika.

Tablica   jest tablicą z informacją, którą należy przeszukać. Należy użyć adresu zakresu lub nazwy zakresu.

Nr_kolumny   jest to numer kolumny w tablicy, z którego pochodzić powinna pasująca wartość. Nr_kolumny o wartości 1 daje w wyniku wartość w pierwszej kolumnie tablicy tablica; nr_kolumny o wartości 2 daje w wyniku wartość w drugiej kolumnie tablicy tablica, i tak dalej. Jeśli indeks nr_kolumny jest mniejszy niż 1, WYSZUKAJ.PIONOWO podaje w wyniku wartość błędu #ARG!; jeśli indeks nr_kolumny jest większy niż liczba kolumn w tablicy tablica, WYSZUKAJ.PIONOWO daje w wyniku wartość błędu #ADR!.

Kolumna   jest wartością logiczną wskazującą, czy WYSZUKAJ.PIONOWO ma znaleźć dokładne czy też przybliżone dopasowanie. Jeżeli parametr ten ma wartość PRAWDA lub został pominięty, wartością wynikową będzie dopasowanie przybliżone; innymi słowy, w razie gdyby nie uzyskano dokładnego dopasowania, wynikiem będzie następna największa wartość mniejsza niż wartość odniesienie. Jeśli parametr ma wartość FAŁSZ, WYSZUKAJ.PIONOWO znajdzie dokładne dopasowanie. Jeśli nie znajdzie żadnego, wynikiem będzie wartość błędu #N/D.

W niniejszym projekcie jako odniesienie przyjęto Kod pracownika, a jako tablice - zdefiniowane obszary o nazwach kadry i płace. Przyjęto również dokładne dopasowanie wartości wynikowej (argument kolumna =0).

Przykład:

wyszukanie płacy podstawowej pracownika o kodzie Kod_pracownika znajdującej się w drugiej kolumnie obszaru Płace i umieszczenie jej w kolumnie 3 Listy płac uzyskuje się po wpisaniu:

=WYSZUKAJ.PIONOWO(Kod_pracownika;Płace;2;0)

Zastosowanie zagnieżdżenia wykorzystującego funkcję logiczną

=JEŻELI(Kod_pracownika=””;””;WYSZUKAJ.PIONOWO(Kod_pracownika;Płace;2;0))

pozwala wyeliminować komunikaty o błędach i zbyteczne zapisy, jakie pojawić się mogą gdy nie podany zostanie Kod pracownika.

Funkcja logiczna JEŻELI ma następującą składnię:

=JEŻELI(test_logiczny;wartość_jeżeli_prawda;wartość_jeżeli_fałsz)

test_logiczny - jest dowolną wartością lub wyrażeniem, sprawdzanym czy jest to PRAWDA czy FAŁSZ i dotyczy sprawdzenia warunku logicznego.

wartość_jeżeli_prawda - określa, co ma być wyświetlone w komórce, gdy warunek zostanie spełniony.

wartość_jeżeli_fałsz - określa, co ma być wyświetlone w komórce, gdy warunek nie zostanie spełniony.

Obliczanie

Funkcję obliczeń realizują w arkuszu Lista płac formuły obliczeniowe wprowadzone do pól: Przychód, Podstawa wymiaru składek ubezpieczeń społecznych , Odliczenia od dochodu, Podstawa wymiaru składki ubezpieczenia zdrowotnego, Podstawa naliczenia podatku dochodowego, Potrącona zaliczka na podatek dochodowy, Składka ubezpieczenia zdrowotnego, Należna zaliczka na podatek dochodowy. Zostały one zaprojektowane w oparciu o algorytmy, wykorzystujące obowiązujące przepisy prawne regulujące sposób obliczania wynagrodzeń jako zagnieżdżenia w funkcji JEŻELI, ze względów wcześniej wspominanych.

Dla poszczególnych, gdzie następuje realizacja obliczeń , wygląda to następująco:

kolumna 9 - Ogółem przychód:

=JEŻELI(SUMA(Składniki_wynagrodzenia)>0;SUMA(Składniki_wynagrodzenia);””)

kolumna 10 - Podstawa wymiaru składek ubezpieczeń społecznych (PWZUS):

=JEŻELI(Ogółem_przychód=””;””;Ogółem_przychód - Wynagrodzenie za czas niezdolności do pracy)

kolumna 11 - Ubezpieczenie emerytalne (UE):

=JEŻELI(PWZUS=””;””;9,76%*PWZUS)

kolumna 12 - Ubezpieczenie rentowe (UR):

=JEŻELI(PWZUS=””;””;6,5%*PWZUS)

kolumna 13 - Ubezpieczenie chorobowe (UCH):

=JEŻELI(PWZUS=””;””;2,45%*PWZUS)

kolumna 14 - Razem składki ubezpieczeń społecznych (SUS):

=JEŻELI(UE+UR+UCH=0;””;UE+UR+UCH)

kolumna 16 - Podstawa wymiaru składki ubezpieczenia zdrowotnego (PWSUZ):

=JEŻELI(Ogółem przychód=””;”;Ogółem przychód - Wynagrodzenie za czas niezdolności do pracy wypłacane ze środków ZUS - SUS)

kolumna 17 - Podstawa naliczenia podatku dochodowego (PNPD):

=JEŻELI(Ogółem przychód=””;””;JEŻELI(Ogółem przychód - SUS - Koszty uzyskania przychodu)<0;0; Ogółem przychód - SUS - Koszty uzyskania przychodu))

kolumna 18 - Potrącona zaliczka na podatek dochodowy (PZPD):

=JEŻELI(PNPD=””;””;JEŻELI(19%*PNPD-Miesięczna ulga w podatku)<0;0;19%*PNPD - miesięczna ulga w podatku))

kolumna 19 - Potrącona składka ubezpieczenia zdrowotnego (PSUZ):

=JEŻELI(PWSUZ=””;””;0,25%*PWSUZ)

kolumna 20 - Składka ubezpieczenia zdrowotnego podlegająca odliczeniu od podatku dochodowego (SUZPPD):

=JEŻELI(PWSUZ=””;””;7,75%*PWSUZ)

kolumna 21 - Należna zaliczka na podatek dochodowy (NZPD):

=JEŻELI(LUB(PZPD=””;SUZPPD=””);””;JEŻELI((PZPD-SUZPPD)<0;0;PZPD-SUZPPD))

kolumna 25 - Do wypłaty:

=JEŻELI(Ogółem przychód =””;””;Ogółem przychód - SUS - PSUZ - SUZPPD - NZPD - Potrącenia + zasiłek rodzinny)

Uwaga: Ponieważ stawki procentowe podatku dochodowego, składek ubezpieczeń społecznych oraz składki ubezpieczenia zdrowotnego mogą ulegać zmianom, przyjęto w miejscach ich użycia w formułach obliczeniowych, odwołania bezwzględne do komórek w arkuszu Stawki, gdzie w razie potrzeby wystarczy dokonać uaktualnienia, bez konieczności zmian formuł obliczeniowych.

Przygotowanie deklaracji ZUS RMUA

Informacje niezbędne do przygotowania deklaracji ZUS RMUA znajdują się w arkuszu Dane osobiste i adresowe oraz w arkuszu Lista płac. Arkusz ZUS zawiera natomiast formularz deklaracji.

Sporządzenie deklaracji ZUS sprowadza się zatem do wyszukania na podstawie Kodu pracownika w obszarze Kadry i Lista danych do pól:

• Podstawa wymiaru składki ubezpieczeń społecznych,

• Podstawa wymiaru składki ubezpieczenia zdrowotnego,

• Kwota składek ubezpieczenia emerytalnego i rentowego finansowana przez ubezpieczonego,

• Kwota składek ubezpieczenia emerytalnego i rentowego finansowana przez płatnika,

• Kwota składek ubezpieczenia chorobowego i zdrowotnego,

oraz wyliczenia:

• Kwoty składki ubezpieczenia wypadkowego,

• Łącznej kwoty składek.

i umieszczenia ich w odpowiednich polach deklaracji ZUS znajdującej się w arkuszu ZUS.

Do automatycznego ustawiania kodu pracownika wykorzystany został formant Pokrętło.

Pokrętło ustawiające automatycznie Kod pracownika w wybranej komórce.

Formant pokrętła tworzy się przy pomocy przycisku Pokrętło na pasku narzędzi Formularze.

Pasek narzędzi Formularze

W prezentowanym projekcie utworzony został formant Pokrętło z łączem do komórki $K$4, umożliwiający ustawianie kodu pracownika w zakresie od 0 do 30000.

Wyszukiwanie potrzebnych danych realizuje tak jak poprzednio funkcja WYSZUKAJ. PIONOWO zagnieżdżona w funkcji JEŻELI. Dla złożenia warunków definiujących test_logiczny wykorzystana została również funkcja LUB(logiczna1;logiczna2;...)

Oto przykład ich użycia:

=JEŻELI(LUB($K$4<1;$K$4>ILE.LICZB('Listapłac'!$A$9:$A$19));"";WYSZUKAJ.PIONOWO($K$4;lista;12;0))

Wystawianie zaświadczeń pracownikom

Wzory niektórych zaświadczeń oraz innych dokumentów kadrowo-płacowych umieszczone zostały w arkuszu Wydruki kadrowe. Wykorzystano w nich funkcje umożliwiające łączenie łańcuchów znakowych oraz funkcję WYSZUKAJ.PIONOWO. Jako odniesienie używa ona zmiennej sterującej Kod pracownika, której aktualną wartość ustala opisany wcześniej formant Pokrętło.

Przygotowanie stosownego zaświadczenia sprowadza się do:

• 
  ustalenia przy pomocy pokrętła wartości zmiennej sterującej Kod pracownika dla żądanego pracownika,
  
  
  
  
  
  
  

• wybrania przy pomocy przycisków stosownego wzoru dokumentu.

Istnieje możliwość rozbudowania arkusza o nowe wzory dokumentów. Poniższy przykład ilustruje, jakie schematy i w jaki sposób do tego zastosować.

Wprowadzenie pierwszej z powyższych formuł we wskazane miejsce wpisuje wyszukaną w obszarze lista (Lista płac) w kolumnie 11, na podstawie podanego w komórce M3 kodu pracownika wartość miesięcznego wynagrodzenia brutto (1738,00 zł).

Formuła druga łączy łańcuch znaków „jest zatrudniony(a) w” z łańcuchami znaków zawartymi w komórkach B2 i B3 arkusza Firma (FIRMA SZKOLENIOWA EKONOMIK).

Obliczanie dodatku stażowego

Jak wspomniano wcześniej, wprowadzenie symbolu N w polu Dział w arkuszu Dane osobiste i adresowe uaktywnia procedurę automatycznego naliczania dodatku stażowego (wg systemu przejętego dla nauczycieli), niezależnie od danych wprowadzanych do pola Dodatek stażowy w arkuszu Dane płacowe. Wykorzystany zostaje do tego celu moduł zawarty w arkuszu Dodatek stażowy.

W module tym w polu Staż ustalany jest aktualny (wg kalendarza systemowego) staż pracy pracownika w latach, na podstawie Daty zatrudnienia i Daty dzisiejszej. Realizuje to formuła:

=JEŻELI(DATA(ROK(DZIŚ());MIESIĄC(B2);DZIEŃ(B2))<=DZIŚ();ROK(DZIŚ())
-ROK(B2);ROK(DZIŚ())-ROK(B2)-1)

gdzie B2 jest adresem względnym daty zatrudnienia pracownika.

W polu Dodatek stażowy naliczana zostaje wartość dodatku stażowego w zł (wg systemu przejętego dla nauczycieli). Realizuje to formuła:

=JEŻELI(Staż<3;0;JEŻELI(Staż<20;Staż/100*Płaca podstawowa;20%*Płaca podstawowa))

W razie konieczności zastosowania innego systemu przyznawania dodatku stażowego, wystarczy w powyższej formule zmodyfikować odpowiednio wartości argumentów

test_logiczny (Staż<3, Staż<20), wartość_jeżeli_prawda (0, Staż/100*Płaca podstawowa), wartość_jeżeli_fałsz (20%*Płaca podstawowa, JEŻELI(Staż<20,Staż/100*Płaca podstawowa;20%*Płaca podstawowa).

W przypadku, gdy przyznawanie dodatku stażowego nie można zapisać algorytmem obliczeń, wystarczy wprowadzić informacje o jego wysokości bezpośrednio do arkusza Dane płacowe w polu Dodatek stażowy.

Firma

Zawarte w tym arkuszu dane:

• nazwa firmy,

• adres firmy,

• bank,

• NIP,

• REGON,

stanowią odniesienie do wszystkich arkuszy, gdzie potrzebne są informacje o firmie.

Można tutaj wprowadzić dane swojej firmy.

Prowadzenie rozrachunków z tytułu wynagrodzeń w arkuszu kalkulacyjnym

Wykorzystanie arkusza kalkulacyjnego do rozrachunków z tytułu wynagrodzeń

Stanisław Kijak Zespół Szkół Ekonomicznych w Mielcu

=JEŻELI(M3<1;"";WYSZUKAJ.PIONOWO(M3;lista;11;0))

="jest zatrudniony(a) w "&Firma!B2&" "&Firma!B3

=DZIŚ()

---