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

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

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

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


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

План уроку:

Близько двох років тому ми почали ознайомлювати вас з прийомами використання Excel для ведення бухгалтерського обліку на простих прикладах розрахунку заробітної плати.

Тепер ми вирішили знову повернутися до цієї теми. У цьому циклі уроків ви ознайомитеся з автоматизацією розрахунку виплат за лікарняними листками, комплексним розрахунком зарплати та її архівуванням, автоматизацією розрахунку нарахувань і відрахувань із зарплати. А на цьому і наступних уроках ви дізнаєтеся, як за допомогою електронної таблиці можна вирішити проблему розрахунку відпускних.

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

Щоб автоматизувати розрахунок встановленої до нарахування суми відпускних, треба визначити:

- дату початку відпустки;

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

- середньоденний заробіток.

Для групування цих даних у розрахунковій таблиці виділено окремий блок "Відпускні" (мал. 1). На малюнку 3 показано загальний вигляд цієї розрахункової таблиці, розташованої на аркуші "Відомість". Її складові елементи ми детальніше розглядатимемо в міру вивчення відповідного розділу обліку.

Малюнок 1

Малюнок 1

Електронна таблиця заробітної плати (мал. 2) побудована з дотриманням таких умов:

- розрахунок щомісячних виплат, нарахувань і утримань проводиться тільки на одному аркуші "Відомість";

- розрахункові дані кожного місяця зберігаються на окремих аркушах у такій само формі, як і у відомості (про це нижче);

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

Така схема дає можливість забезпечити високу автоматизацію розрахунку, наочність і простоту обліку.

Настановні дані місяця, у якому проводиться розрахунок зарплати, відображаються у блоці, розташованому у верхній частині таблиці (мал. 2). У неї заносяться дані про робочі та неробочі дні звітного місяця та поточного року. Заповнення цього блоку автоматизоване, крім комірок В4 (дата звітного місяця, яка обов'язково вноситься у форматі першого дня місяця, наприклад 01.06.2002) і D2 (кількість неробочих і святкових днів у році, які щороку встановлюються постановою КМУ).

Малюнок 2

Малюнок 2

В інші комірки введіть такі формули:

B2 - =В4. Для комірки В2 встановіть формат, що відображає тільки поточний рік;

B3 - =ДАТАМЕС(В4;-1). У рядку 3 ви можете відобразити кількість робочих і неробочих (святкових) днів будь-якого місяця поточного року. Для цього у формулі комірки В3 змініть аргумент "-1". Наприклад, якщо ви хочете отримати дані щодо місяця, наступного за поточним, змініть цей аргумент на "1" - =ДАТАМЕС(В4;1).

Малюнок 3

Малюнок 3Малюнок 3Малюнок 3
Малюнок 3Малюнок 3Малюнок 3
Малюнок 3Малюнок 3Малюнок 3

Оскільки святкові і неробочі дні, які припадають на період відпустки, у розрахунок його тривалості не включаються і не оплачуються (для працівників, які мають дітей), корисно автоматизувати визначення кількості таких днів. Для цього введіть формули у комірки:

D3 - =ЕСЛИ(ИЛИ(МЕСЯЦ(B3)=3;МЕСЯЦ(B3)=8);1;
ЕСЛИ(ИЛИ(МЕСЯЦ(B3)=1;МЕСЯЦ(B3)=6);2;
ЕСЛИ(МЕСЯЦ(B3)=5;4;0)))
;

D4 - =ЕСЛИ(ИЛИ(МЕСЯЦ(B4)=3;МЕСЯЦ(B4)=8);1;
ЕСЛИ(ИЛИ(МЕСЯЦ(B4)=1;МЕСЯЦ(B4)=6);2;
ЕСЛИ(МЕСЯЦ(B4)=5;4;0)))
;

Увага! Кількість святкових і неробочих днів в аргументах цих формул має відповідати щороку нормам, які встановлюються постановами КМУ.

Під час розрахунку середньої зарплати за відпрацьований період враховуються всі календарні дні цього періоду за мінусом святкових і неробочих днів, затверджених законодавством. Кількість днів періоду, що дорівнює 12 місяцям, визначається формулою в комірці:

G2 - =ДАТАМЕС(B2;12-МЕСЯЦ(B2-1))-ДАТАМЕС(B2;
-МЕСЯЦ(B2-1))-D2.

Кількість днів періоду меншого за 12 місяців визначається безпосередньо у формулі розрахунку середньоденного заробітку (розглянемо нижче).

Як довідкові значення вам знадобиться кількість "чистих" робочих днів місяця. Для місяця, що передує звітному, в комірку запишіть формулу:

G3 - =ЧИСТРАБДНИ(B3;КОНМЕСЯЦА(B3;0))-D3;

для звітного місяця:

G4 - =ЧИСТРАБДНИ(B4;КОНМЕСЯЦА(B4;0))-D4.


Дата початку відпустки

При визначенні відпускних цей показник має принципове значення тільки для працівників, які використовують відпустку протягом першого року роботи на підприємстві. Згідно зі ст. 10 Закону "Про відпустки", право на основну та додаткову відпустку повної тривалості (не менше 24 днів) у перший рік роботи настає після закінчення 6 місяців безперервної роботи на підприємстві. Окрема категорія працівників має право на відпустку повної тривалості в перший рік раніше 6 місяців1.


1 Див. абзац 7 ст. 10 Закону України від 15.11.96 р. №504/96-ВР "Про відпустки".

Дата початку відпустки вноситься у відповідну комірку вручну. Для фіксації дати відпустки, що надається раніше 6 місяців, можна використати спеціальне виділення. Наприклад, для комірки Р10 у вікні "Умовне форматування" введіть необхідний вид відображення комірки і формулу:

=$P$10-$C$10<183.

У результаті комірка Р10 буде або підсвічена іншим кольором, або виділена форматом.

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


Тривалість відпустки

Мінімальна тривалість основної відпустки для всіх категорій працівників, які безперервно пропрацювали на підприємстві понад 6 місяців, а також окремої категорії, що має право на відпустку в перший рік раніше 6 місяців, становить не менше 24 календарних днів.

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

Наприклад, працівникові підприємства Гетьману Н.А, прийнятому на роботу з 22.12.2001 р., надано відпустку з 03.06.2002 р. Щоб визначити тривалість встановленої йому відпустки, введіть у комірку Q10 таку формулу:

=ОКРУГЛ(24*(P10-C10)/183;0).

У загальному випадку цю формулу можна використати, розташувавши її поза таблицею, змінюючи для кожного конкретного випадку аргументи P10 і C10. Якщо ви не хочете утруднювати себе контролем, що цю формулу слід використовувати тільки для "6-місячних" пільговиків, трохи ускладніть її:

=ЕСЛИ(P10-C10<183;ОКРУГЛ(24*(P10-C10)/183;0)).

У принципі, ви можете скопіювати цю формулу у всі комірки графи. Якщо відпустка надається за другий і наступні роки, просто щодо них введіть числові значення.

Увага! Щорічну відпустку за другий і наступні роки роботи може бути надано працівникові в будь-який час відповідного робочого року.

Загальна тривалість відпустки кожного працівника визначається підсумовуванням основної і додаткової відпустки і не може перевищувати 59 днів, а для робітників підземних професій - не більше 69 днів.

Тривалість основної відпустки залежно від професійної належності коливається від 24 до 56 днів (для наукових і педагогічних працівників) і в кожному конкретному випадку може бути визначена за статтею 6 Закону "Про відпустки". Зауважимо, що тривалість основної відпустки з року в рік, як правило, не змінюється, тому автоматизувати її розрахунок в рамках цієї програми нераціонально.

Тривалість щорічної додаткової відпустки можна дізнатися з переліку, зазначеного в статтях 13-15 Закону "Про відпустки", і зі Списку виробництв, робіт, цехів, професій та посад, зайнятість працівників у яких дає право на щорічну додаткову відпустку, затвердженого постановою КМУ від 17.11.97 р. №1290. Тому вам треба заздалегідь визначити із зазначених джерел кількість днів відпустки, а потім уже вручну внести дані у відповідні елементи таблиці (графа "Тривалість відпустки").


Середньоденний заробіток

При розрахунку суми відпускних нарівні з кількістю днів відпустки розмір середньоденного заробітку - основний елемент.

Методику розрахунку, наведену в Постанові КМУ від 08.02.95 р. №100, можна проілюструвати такою формулою:

ЗПпер
ЗПср.дн. =--------------------------
КДпер - Сдпер

де

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

ЗПпер - сумарний заробіток за останні 12 місяців роботи, що передують місяцю надання відпустки (виплати компенсації за невикористану відпустку), або за менший фактично відпрацьований період. Види виплат, які входять до середньої зарплати при розрахунку відпускних, зазначено в статті 3 Постанови №100. Для варіанта, показаного на мал. 4, ЗПпер треба визначати за п'ять місяців (січень 2002 - травень 2002);

Малюнок 4

Малюнок 4

КДпер - кількість днів у розрахунковому періоді. Якщо в цьому періоді були відпрацьовані всі робочі дні, значення КДпер дорівнюватиме кількості календарних днів. Працівнику, що пропрацював на підприємстві менше року, КДпер визначається з першого числа місяця після оформлення на роботу до першого числа місяця, в якому надається відпустка або виплачується компенсація за невикористану відпустку. Для варіанта, показаного на мал. 4, КДпер = 152;

СДпер - кількість святкових і неробочих днів (законодавчо затверджених) у розрахунковому періоді. Наприклад, для варіанта, показаного на мал. 4, СДпер = 7.

Усі ці умови виконуються при автоматизованому розрахунку середньоденної зарплати за допомогою формули, введеної в комірку S9 (мал. 1) і скопійованої в інші комірки графи:

S9 - =ЕСЛИ(ИЛИ(МЕСЯЦ(В$4)<>МЕСЯЦ(P9);
$AQ$7=$AR$7;P9=0);””;
ЕСЛИ(ДОЛЯГОДА(КОНМЕСЯЦА(P9;-1);
КОНМЕСЯЦА(C9;0))>=1;
СУММ(AR9:СМЕЩ(AR9;0;11))/G$2;
(СУММ(AR9:СМЕЩ(AR9;0;(ДОЛЯГОДА(КОНМЕСЯЦА(C9;0);
КОНМЕСЯЦА(P9;-2)))*12))/(КОНМЕСЯЦА(P9;-1)
-КОНМЕСЯЦА(C9;0)-D9))))

Виділений фрагмент формули автоматизує розрахунок середньоденної зарплати за 12 останніх місяців, а наступний фрагмент - за період менше 12 місяців. В останньому фрагменті формули використовується аргумент D9 - він означає кількість святкових і неробочих днів періоду, меншого за 12 місяців. Введіть його вручну, якщо для працівника розраховується сума відпускних за такий період. Допоможе вам у цьому блок, показаний на малюнку 2.

Увага! Якщо у вас в Excel-і немає функцій ДОЛЯГОДА і КОНМЕСЯЦА, встановіть надбудову "Пакет аналізу" (меню Сервис - Надстройка).

Для використання наведеної формули необхідна наявність архіву сумарних виплат за попередні місяці розрахункового періоду (мал. 3в). Як автоматизувати заповнення такого архіву, ви дізнаєтеся на наступному уроці. А щоб перевірити, як працює формула уже зараз, ви можете побудувати форму архіву (обов'язково в зоні АР:ВC, мал. 3в) і вручну заповнити її комірки за необхідний період. Нараховану суму відпускних визначте самі. А на наступному уроці ми її перевіримо.


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

План уроку

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