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

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

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

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


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

План урока:

Около двух лет назад мы начали знакомить вас с приемами использования Excel для ведения бухгалтерского учета на простых примерах расчета заработной платы. Теперь мы решили опять вернуться к этой теме. В этом цикле уроков вы ознакомитесь с автоматизацией расчета выплат по больничным листам, комплексным расчетом зарплаты и ее архивированием, автоматизацией расчета начислений и отчислений из зарплаты. А на этом и следующих уроках вы узнаете, как с помощью электронной таблицы можно решить проблему расчета отпускных.

Какие данные необходимо иметь для расчета отпускных

Чтобы автоматизировать расчет положенной к начислению суммы отпускных, нужно определить:

- дату начала отпуска;

- общую продолжительность отпуска (основного и дополнительного);

- среднедневной заработок.

Для группировки этих данных в расчетной таблице выделен отдельный блок "Отпускные" (рис. 1). На рисунке 3 показан общий вид этой расчетной таблицы, расположенной на листе "Відомість". Составляющие ее элементы мы будем более подробно рассматривать по мере изучения соответствующего раздела учета.

Рисунок 1

Рисунок 1

Электронная таблица заработной платы (рис. 2) построена с соблюдением следующих условий:

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

- расчетные данные каждого месяца сохраняются на отдельных листах в такой же форме, как и в ведомости (об этом ниже);

- данные о 12-ти последних ежемесячных выплатах, используемых для расчета средней зарплаты, автоматически сохраняются в архиве, расположенном на листе "Відомість".

Рисунок 2

Рисунок 2

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

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

В остальные ячейки введите такие формулы:

В2 - =В4. Для ячейки В2 установите формат, отражающий только текущий год;

В3 - =ДАТАМЕС(В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, КДпер = 152;

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

Рисунок 4

Рисунок 4

Все эти условия выполняются при автоматизированном расчете среднедневной зарплаты с помощью формулы, введенной в ячейку 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-e отсутствуют функции ДОЛЯГОДА и КОНМЕСЯЦА, установите надстройку "Пакет анализа" (меню Сервис - Надстройка).

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


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

План урока

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