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

Автоматизации бухучёта: Excel

Урок 33
Автоматизация бухгалтерского и налогового учёта с помощью EXCEL

Расчет начислений по больничному листку (продолжение)

Владимир ЛАВРЕНОВ


Предыдущие уроки "Автоматизации бухучёта: Excel"

План урока:

Перенос расчетных данных в архиве

Данные, необходимые для расчета больничных листков, хранятся в архиве. На прошлом уроке была показана форма архива по соцстраху, в который одновременно заносятся два вида значений: сумма выплат, входящих в расчет средней зарплаты, и количество дней, отработанных в отчетном месяце. Этот архив должен быть рассчитан не менее чем на восемь месяцев (16 граф); еще две графы необходимы для интерактивной записи данных текущего месяца (графы BE и BF).

В среднюю зарплату включается доход в пределах максимальной величины зарплаты, с которого уплачиваются страховые взносы в фонды государственного социального страхования. Постановлением КМУ от 11.04.2002 г. №494 его размер установлен на уровне 2200 грн.

Рисунок 1

Рисунок 1

Для того чтобы в архиве сохранялись суммы, не превышающие 2200 грн, введите в ячейку ВЕ9 (рис. 1) формулу массива:

{=ЕСЛИ(BF$7=СМЕЩ($BF$7;0;1);0;ЕСЛИ(СУММ(G9:J9)-
СУММ((K$8:O$8=1)*K9:O9)>=2200;2200;СУММ(G9:J9)-
СУММ((K$8:O$8=1)*K9:O9)))}

Количество отработанных в отчетном месяце дней записывается в архив с помощью формулы, введенной в ячейку BF9:

=ЕСЛИ(BF$7=BG$7;0;G$4-E9).

Напоминаем, что количество рабочих дней по графику в ячейке G$4 определяется автоматически1, а в ячейки графы Е вносится число не отработанных по графику дней в отчетном периоде.


1 Формулу см. в уроке №30 ("ДК" №22-23/2002).


Как автоматизировать заполнение архива

На листе "Ведомость" уже есть два управляющих элемента, позволяющих автоматически вносить данные в архив средней зарплаты для расчета отпускных - кнопка записи и кнопка отмены, которые запускают соответствующие макросы. Нам не придется записывать новые макросы, чтобы автоматизировать сохранение данных для расчета средней зарплаты по социальным выплатам: мы просто отредактируем уже существующие.

Рисунок 2

Рисунок 2

Итак, откройте макрос "ЗанестиДоАрхіву"2 и вставьте перед строкой "Range("AR7").Select" еще восемь строк (рис. 2):

Columns("BG:BH").Select
Selection.Insert Shift:=xlToRight
Columns("BE:BF").Select
Selection.Copy
Columns("BG:BH").Select
Selection.PasteSpecial Paste:=xlValues,_
Operation:=xlNone, SkipBlanks:=False,_
Transpose:=False
Columns("BW:BX").Select
Selection.Delete Shift:=xlToLeft


2 Последовательность записи макроса "ЗанестиДоАрхіву" была рассмотрена в уроках 30 и 31 ("ДК" №22-23, №26/2002).

Чтобы не набирать эти строки вручную, вы можете скопировать их с уже записанных в этом макросе (выделены) и подправить только имена ссылок (рис. 2).

После нажатия на кнопку "Занести до архіву" (рис. 1) будет автоматически произведена выборка выплат, входящих в расчет средней зарплаты для соцстраха, и количества отработанных дней, а затем записана в графах "ГРН" и "ДН" архива. Можете проверить ее работу - записать данные в архив можно будет только один раз, при этом первые две его графы станут пустыми (BE9:BF15, рис. 1). Повторное нажатие на эту кнопку будет "холостым".

Однако не спешите нажимать на кнопку "Повернути дані місяця", чтобы вернуться к исходному положению. Макрос, управляемый этой кнопкой, не настроен для работы с архивом по соцстраху, и при его запуске некоторые данные архива могут быть утеряны. Вам придется подобную корректировку провести также с макросом "СкасуватиЗапис". Откройте его и вставьте перед строкой "Range("AQ7").Select" еще две строки (рис. 3):

Columns("BG:BH").Select
Selection.Delete Shift:=xlToLeft

Рисунок 3

Рисунок 3

Теперь архив по соцстраху полностью готов к выполнению своих функций.


Таблица выплат

Средняя зарплата застрахованного лица вычисляется исходя из начисленной зарплаты и других выплат, облагаемых подоходным налогом с граждан, с которых уплачиваются страховые взносы в фонды государственного социального страхования. Величина этих выплат рассчитывается при внесении данных в архив формулой, приведенной выше (см. ячейку ВЕ9):

{=ЕСЛИ(BF$7=СМЕЩ($BF$7;0;1);0;ЕСЛИ(СУММ(G9:J9)-
СУММ((K$8:O$8=1)*K9:O9)>=2200;2200;СУММ(G9:J9)-
СУММ((K$8:O$8=1)*K9:O9)))}

где

СУММ(G9:J9) - все выплаты, начисленные работнику в отчетном периоде, включая и не облагаемые подоходным налогом;

СУММ((K$8:O$8=1)*K9:O9) - выплаты, не облагаемые подоходным налогом, перечень которых определен ст. 5 Декрета о подоходном налоге.

Так как не все приведенные в перечне выплаты исключаются из расчета средней зарплаты для случаев соцстрахования, в ведомости необходимо дифференцировать выплаты по признаку вхождения в базу соцстрахования. Поэтому для того, чтобы автоматизировать выбор выплат, не входящих в среднюю зарплату, создайте на листе "Додат.Виплати" таблицу (рис. 4б). Сгруппируйте в этой таблице осуществляемые на вашем предприятии выплаты, указанные в ст. 5 Декрета. Тем из них, которые учитываются при определении средней зарплаты по соцстраху, присвойте признак "2" (подпункты "п", "р", "щ" ст. 5 Декрета), остальным - признак "1".

Такая таблица даст возможность:

- автоматически перенести в ведомость выбранное наименование выплаты;

- автоматизировать расчет базы налогообложения при определении подоходного налога и выплат по социальному страхованию. Такой расчет основан на считывании признака из ячеек К8:О8 (рис. 4а) и будет рассмотрен в других разделах ведомости.

Рисунок 4

Рисунок 4

Чтобы иметь возможность обрабатывать данные таблицы выплат, присвойте имя рабочей зоне этой таблицы (А2:С60, рис. 4б), например "ВибВип". Размер этого массива может быть другим, в зависимости от детализации видов выплат.


Автозаполнение выплат, не входящих в среднюю зарплату

Раздел ведомости "Выплаты, не входящие..." (К5:О17, рис. 4а) заполняется автоматически, кроме непосредственно сумм выплат. Для заполнения блока "Наименование выплаты" (К7:О7, рис. 4а) введите в ячейку К7 формулу:

=Если(ТИП(ВПР(1;ВибВип;2;ЛОЖЬ))=2;
ВПР(1;ВибВип;2;ЛОЖЬ);"");

в ячейку L7:

=Если(ТИП(ВПР(2;ВибВип;2;ЛОЖЬ))=2;
ВПР(2;ВибВип;2;ЛОЖЬ);"");

в ячейку М7:

=Если(ТИП(ВПР(3;ВибВип;2;ЛОЖЬ))=2;
ВПР(3;ВибВип;2;ЛОЖЬ);"");

и так далее, в зависимости от количества используемых на вашем предприятии в отчетном периоде выплат из перечня ст. 5 Декрета. Каждый бухгалтер из практики должен знать это количество. Данная ведомость рассчитана на пять таких выплат.

Для заполнения блока "Признак" (К8:О8, рис. 4а) в ячейку К8 введите формулу:

=Если(ТИП(ВПР(1;ВибВип;3;ЛОЖЬ))=1;
ВПР(1;ВибВип;3;ЛОЖЬ);"");

в ячейку L8:

=Если(ТИП(ВПР(2;ВибВип;3;ЛОЖЬ))=1;
ВПР(2;ВибВип;3;ЛОЖЬ);"");

И так далее, аналогично предыдущему блоку.

Теперь если в отчетном месяце появятся выплаты, входящие в перечень ст. 5 Декрета, откройте лист "Додат.Виплати" и в левой графе "№ выборки" напротив соответствующей выплаты установите порядковый номер выборки. Название выплаты и признак мгновенно появятся в вашей ведомости в последовательности, определенной присвоенным номером выборки, - слева направо.

Внимание! Номера выборки обязательно должны быть установлены в арифметической последовательности сверху вниз.


Последовательность учета и расчета больничных листков

Для правильного учета и расчета выплат по больничным листкам с помощью приведенной таблицы необходимо придерживаться определенной последовательности действий и правил.

Рассмотрим это на примере учета выплат Бойко Т. В. (рис. 5).

Рисунок 5

Рисунок 5

1. Введите суммы, выплаченные в отчетном месяце: в ячейки G9:I9 - выплаты согласно Инструкции по статистике зарплаты, в J9 - сумму всех остальных выплат, в К9:О9 - выплаты, не входящие в налогооблагаемый доход.

2. Определите количество оплачиваемых дней по больничным листкам (БЛ), открытым не в отчетном периоде, и введите его в ячейку V9.

3. Определите страховой коэффициент для таких БЛ на дату наступления страхового случая и введите его в ячейку W9.

4. Определите количество оплачиваемых дней по БЛ, открытым в отчетном периоде, и введите его в ячейку X9.

5. Определите страховой коэффициент для таких БЛ на дату наступления страхового случая и введите его в ячейку Y9.

6. Введите в ячейку Е9 число неотработанных дней по графику отчетного месяца.

После этих действий в архиве будут сохранены данные, необходимые для расчета средней зарплаты по соцвыплатам последующих периодов, а также автоматически произведен расчет выплат по больничным листкам, предъявленным в отчетном месяце.

Будьте здоровы!


Обсудить на форуме Дт-Кт "Автоматизация бухгалтерского учёта"


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

План урока

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