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

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

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

Розрахунок нарахувань за лікарняним листком (продовження)

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


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

План уроку:

Перенесення розрахункових даних в архіві

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

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

Для того щоб в архіві зберігалися суми, що не перевищують 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)))}

Малюнок 1

Малюнок 1

Кількість відпрацьованих у звітному місяці днів записується до архіву за допомогою формули, введеної у комірку BF9:

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

Нагадуємо, що кількість робочих днів за графіком у комірці G$4 визначається автоматично1, а до комірок графи Е вноситься кількість не відпрацьованих за графіком днів у звітному періоді.


1 Формулу див. в уроці №30 ("ДК" №22-23/2002).


Як автоматизувати заповнення архіву

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

Отже, відкрийте макрос "ЗанестиДоАрхіву"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).

Малюнок 2

Малюнок 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".

Малюнок 4

Малюнок 4

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

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

- автоматизувати розрахунок бази оподаткування під час визначення прибуткового податку і виплат за соціальним страхуванням. Такий розрахунок грунтується на прочитуванні ознаки з комірок К8:О8 (мал. 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 кількість невідпрацьованих днів за графіком звітного місяця.

Після цих дій в архіві будуть збережені дані, необхідні для розрахунку середньої зарплати за соцвиплатами наступних періодів, а також автоматично зроблено розрахунок виплат за лікарняними листками, пред'явленими у звітному місяці.

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



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