Нове число  Дебет-Кредит
  Український бухгалтерський тижневик
#29'2002: Практична бухгалтерія - Автоматично

ExcelАвтоматизації бухобліку: Excel

Урок 32
Автоматизація бухгалтерського і податкового обліку зарплати з допомогою EXCEL

Володимир ЛАВРЄНОВ


Попередні уроки "Автоматизації бухобліку: Excel"

План уроку:

Розрахунок нарахувань за лікарняним листком

У зв'язку зі зміною законодавчої бази з питань соціального страхування змінилася і методика визначення нарахувань за лікарняними листками1. Вона значно відрізняється від аналогічного розрахунку при визначенні відпускних.


1Постанова КМУ від 26.09.2001 р. №1266 "Про обчислення середньої заробітної плати (доходу) для розрахунку виплат за загальнообов'язковим державним соціальним страхуванням".

Зміни зачепили такі компоненти розрахунку, як:

- розрахунковий період;

- середньоденна зарплата (її складові);

- кількість оплачуваних днів.

Для автоматизації розрахунку в таблиці використовується блок "Лікарняні" (U5:Z17, мал. 1).

Малюнок 1

Малюнок 1

У таблицю також додано графу "Кіл-ть невідпрац. днів" (Е5:Е17, мал. 1), в яку вручну заноситься кількість днів у звітному місяці, невідпрацьованих з різних причин (хвороба, прогул), та архів виплат для розрахунку допомоги за соцстрахом.

Розрахунковий період

При нарахуванні виплат за лікарняним листком, відповідно до Постанови КМУ №1266, розрахунковий період (РП) може дорівнювати:

У першому варіанті розрахунковий період визначається кількістю календарних днів. Наприклад, дата прийняття на роботу - 07.05.2002 р., а дата відкриття лікарняного листка - 21.06.2002 р. У цьому випадку розрахунковий період буде з 07.05 до 20.06 включно.

У другому і третьому випадку розрахунковий період визначається кількістю повних календарних місяців (з першого по перше число).

Тривалість лікарняного листка

Якщо при розрахунку нарахованої суми відпускних усе просто - середньоденну норму множимо на кількість календарних днів відпустки, то методика визначення нарахованої суми за лікарняним листком інша. Кількість днів, зазначених у лікарняному листку, буде множником при визначенні нарахованої суми тільки в тому випадку, якщо весь період хвороби припадає на робочі дні (тобто протягом одного робочого тижня). В іншому випадку розрахунок ускладнюється. Для того щоб правильно розрахувати суму, що має бути нарахована за лікарняним листком, треба правильно визначити кількість оплачуваних днів.

Наприклад, працівник подав до оплати лікарняний листок з 19 червня до 25 червня 2002 р. (тобто 7 днів) - від середи до вівторка включно (мал. 2). Формально оплачуються всі дні хвороби, а фактично при розрахунку нарахованої суми враховуватимуться тільки робочі (за графіком) дні. У цьому прикладі, при 5-денному робочому тижні та одному неробочому дні (Пн, 24.06), сума за лікарняним листком буде обчислена, враховуючи тільки чотири дні (див. комірку Х10, мал. 1).

У таблицю-відомість (графа "Дата відкриття") треба внести дату відкриття лікарняного листка. Якщо у звітному місяці до оплати подано лікарняний листок, відкритий в одному з попередніх періодів, то вноситься дата відкриття саме цього листка (U9, мал. 1). Якщо у звітному періоді подано до оплати лікарняні листки, відкриті цього ж місяця, - вноситься дата будь-якого з цих листків (U10, мал. 1). Введення такої дати дасть можливість автоматизувати розрахунок фактично відпрацьованого часу в розрахунковому періоді перед настанням страхового випадку.


Середня зарплата (середньоденний заробіток)

Для розрахунку середньоденного заробітку (СдЗП) треба знати:

- середню зарплату за розрахунковий період;

- кількість відпрацьованих днів у розрахунковому періоді

Методику розрахунку СдЗП, наведену в Постанові КМУ від 26.09.2001 р. №1266, у застосуванні до таблиці-відомості можна проілюструвати такою формулою:

ЗПпер
СдЗП =------------------------------------------
КДроб - Кднер

де

СдЗП - середньоденний заробіток;

ЗПпер - сумарний заробіток за розрахунковий період, що передує настанню страхового випадку. Види виплат, які входять до середньої зарплати при розрахунку лікарняних, тобто мають обкладатися прибутковим податком, зазначені в статтях 7-12 Постанови КМУ №1266. Для визначення ЗПпер треба суму значень, записаних у комірки К9:О15, відняти від суми значень, записаних у комірках G9:J15.

Варіанти періодів, за які визначається ЗПпер, показані в таблиці 1;

КДроб - кількість робочих днів (за графіком) у розрахунковому періоді. В електронній таблиці-відомості кількість робочих днів кожного звітного місяця визначається в комірках G3 і G4 автоматично2;


2 Автоматизацію розрахунку цих даних див. в уроці №30 ("ДК" №22-23/2002)

КДнер - кількість робочих днів за графіком, пропущених у звітному періоді. До цієї кількості входять невідпрацьовані дні звітного місяця (прогул, "лікарняні" оплачувані дні тощо). Наприклад, Бойко Т. В. подала в червні два лікарняні листки: один - з 31.05.2002 р. до 05.06.2002 р., другий - з 10.06.2002 р. до 12.06.2002 р. (7 оплачуваних днів). За звітний період (червень) було пропущено 6 робочих днів (з 03.06 до 5.06 і з 10.06 до 12.06). Тому в комірці Е9 записано значення 6, а не 7 (мал. 1).

Малюнок 2

Малюнок 2

В одному звітному періоді можуть бути подані до оплати лікарняні листки, відкриті як попереднього місяця, так і поточного. У комірки V9:V16 (мал. 1) записується загальна кількість оплачуваних днів за ЛЛ, відкритим попереднього звітного місяця, а в комірки Х9:Х16 - загальна кількість оплачуваних днів за всіма лікарняними листками, відкритими і закритими звітного місяця. Важливо розділити ці значення, оскільки база для визначення середньоденного заробітку для лікарняних листків, відкритих у різні місяці, буде різною.

Для чого потрібен архів

Для того щоб забезпечити автоматичний розрахунок виплат за лікарняними листками, дані про виплати, що входять до середньої зарплати, та кількість фактично відпрацьованих за місяць днів мають бути збережені в архіві. Цей архів є таблицею, аналогічною до таблиці даних для розрахунку відпускних (мал. 3). Відмінність у тому, що дані кожного місяця зберігаються у двох графах - "ГРН" і "ДН". Заповнюється він також автоматично, одночасно з архівом відпускних. Про те, як це зробити, буде розказано на наступному уроці.

Малюнок 3

Малюнок 3

Формула розрахунку страхових виплат

Алгоритм визначення суми страхових виплат за звітний період може бути різним і залежить від таких чинників:

- наскільки віддалена дата прийняття на роботу від дати настання страхового випадку;

- чи подані до оплати в одному звітному періоді лікарняні листки, відкриті в різних місяцях.

Розгляньмо можливі варіанти обліку лікарняних листів.

У них буде наведено три фрагменти однієї формули масиву, яку треба буде ввести в комірку Z9 (мал. 1) і скопіювати для інших працівників.

1 варіант. Страховий випадок настав у місяці прийняття на роботу. Лікарняний листок закритий і поданий до оплати цього ж місяця (мал. 4).

Малюнок 4

Малюнок 4

У цьому випадку середньоденна зарплата розраховується виходячи з тарифної ставки або посадового окладу (комірка G9) з урахуванням робочих днів за графіком (комірка G$4) у звітному місяці.

ЕСЛИ(И(ГОД(C9)=ГОД(В$4);МЕСЯЦ(C9)=МЕСЯЦ(В$4));
X9*Y9*G9/G$4;

2 варіант. У звітному місяці подано до оплати два ЛЛ, один з яких (Лл1) відкритий у місяці прийняття на роботу. Звітний місяць - наступний за місяцем прийняття на роботу.

Під час розрахунку нарахованих сум треба враховувати таке: оскільки Лл1 відкритий у місяці прийняття на роботу, а закритий у звітному, розрахунковою базою для нього є тарифна ставка (оклад), а для Лл2 - зарплата, нарахована за перший місяць роботи і розрахункова сума зарплати за звітний місяць (мал. 5). При цьому в комірці стовпчика V (мал. 1) зазначається кількість оплачуваних днів за лікарняним листком Лл1, а в комірці стовпчика Х - оплачуваних за листком Лл2.

Малюнок 5

Малюнок 5

Якщо у звітному місяці подано до оплати тільки Лл1 (тобто відкритий минулого місяця), комірка стовпчика X не заповнюється (наприклад комірка Х11, мал. 6).

Малюнок 6

Малюнок 6

ЕСЛИ(И(ГОД(КОНМЕСЯЦА(C9;1))=ГОД(В$4);
МЕСЯЦ(КОНМЕСЯЦА(C9;1))=МЕСЯЦ(В$4));
V9*W9*G9/G$4+X9*Y9*(СУММ((BG$8:BR$8="грн")*(BG9:BR9))+G9)/

(СУММ((BG$8:BR$8="дн")*(BG9:BR9))+G$4).

Не дивуйтеся, що у формулі перемножуються текстові значення (BG$8:BR$8="грн" або BG$8:BR$8="дн") і числові значення (BG9:BR9). Ця операція можлива при використанні формули масиву і дозволяє підсумовувати виплати або кількість днів за розрахунковий період залежно від аргументу, що використовується ("грн" або "дн").

3 варіант. Звітний місяць - не менш ніж другий за рахунком після місяця прийняття на роботу. У загальному випадку до оплати може бути подано кілька ЛЛ, один з яких відкритий не у звітному місяці (мал. 7).

Малюнок 7

Малюнок 7

У цьому випадку для поданих до оплати лікарняних листків розрахунковою базою є нарахована зарплата за минулі періоди. Ці дані прочитуються з архіву (мал. 3).

Фрагмент формули, який забезпечує розрахунок виплат за лікарняними листками за ці звітні періоди, такий:

ЕСЛИ(ИЛИ(ГОД(КОНМЕСЯЦА(C9;2))<=ГОД(В$4);
И(ГОД(КОНМЕСЯЦА(C9;2))=ГОД(В$4);
МЕСЯЦ(КОНМЕСЯЦА(C9;2))<=МЕСЯЦ(В$4));

V9*W9*(СУММ((BI$8:BT$8="грн")*
(BI9:BT9))+СУММ((BG$8:BR$8="грн")*(BG9:BR9)))/

(СУММ((BI$8:BT$8="дн")*(BI9:BT9)+СУММ((BG$8:BR$8="дн")*
(BG9:BR9)))+X9*Y9*СУММ((BG$8:BR$8="грн")*
(BG9:BR9))/СУММ((BG$8:BR$8="дн")*(BG9:BR9))))))

Зверніть увагу: для розрахунку виплат за лікарняним листком, відкритому в місяці, що передує звітному, архівні дані про зарплату прочитуються за допомогою "конструкцій" СУММ((BI$8:BT$8="грн")*(BI9:BT9)) і СУММ((BI$8:BT$8="дн")*(BI9:BT9)), а для листка, відкритого у звітному місяці, - за допомогою "конструкцій" СУММ((BG$8:BR$8="грн")*(BG9:BR9)) і СУММ((BG$8:BR$8="дн")*(BG9:BR9)).

Тобто інтервал комірок, з якого прочитуються дані для розрахунку Лл1, зміщений на один місяць, ніж для розрахунку Лл2 (порівняйте в архіві розташування комірок BI$8 і BG$8).

Для того щоб у комірках графи "Нараховано" розрахунок за лікарняними листками проводився з урахуванням усіх розглянутих варіантів, об'єднайте в комірці Z9 показані фрагменти формули масиву в одну і натисніть одночасно Ctrl-Shift-Enter. Перевірте, щоб наприкінці загальної формули масиву стояло 6 (шість) круглих дужок.

А тепер перевірте працездатність формули, заповнивши вручну потрібні комірки архіву (мал. 3).


Наступний урок

План уроку

Версiя для друку Версiя для друку  Вiдправити по поштiВiдправити по поштi
© 2002
"Дебет-Кредит"
Редакція: debet-kredit@gc.kiev.ua