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

Автоматизации бухучёта: 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).

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

Рисунок 2

Рисунок 2

Для чего нужен архив

Для того чтобы обеспечить автоматический расчет выплат по больничным листкам, данные о выплатах, входящих в среднюю зарплату, и количестве фактически отработанных за месяц дней должны быть сохранены в архиве. Этот архив представляет собой таблицу, аналогичную таблице данных для расчета отпускных (рис. 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).


Следующий урок

План урока

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