Я уже писал пост про то, как можно посчитать взносы с ФОТ в Excel при помощи самописной функции. Сегодня хочу развить тему и показать, как при помощи этого инструмента сверстать бюджет на ФОТ.
Нам потребуется создать новую книгу с пятью листами и перенести в нее макрос с функцией СОЦВЗНОС() как это описано здесь и сохранить в формате с поддержкой макросов.
На последнем листе книги необходимо разместить таблицу с актуальными данными по размеру предельных баз и ставок для расчета социальных взносов функцией СОЦВЗНОС() и поименовать ячейки как показано на рисунке:
Бюджет на фонд оплаты труда является составной частью большого бюджета компании, бизнес-плана или проекта. Зачастую, требуется считать бюджет на ФОТ в разрезе направлений деятельности или подразделений компании (ИТ, бухгалтерия, производственный персонал подразделения А и т.п.).
Для сегментации бюджета ФОТ по подразделениям, предлагаю создать лист с плановым штатным расписанием и размером KPI для каждой ставки. Список полей, которые будут нужны для реализации примера:
Поля:
- key - рабочее поле которое в последующем можно будет скрыть. Понадобиться для обращения к таблице с помощью функции ВПР(). Содержит выражение: =B2&"_"&C2&"_"&D2 - которое объединяет значения в столбце B, C и D создавая уникальный ключ для каждой строки. Знак "&" обозначает конкатенацию (объединение строк), может быть заменен функцией СЦЕПИТЬ(). Знак "_" просто для красоты, чтобы проще интерпретировать значение ключа.
- Поля "Подразделение", "Должность" и "Ф.И.О Вакансия" заполняем любыми значениями, например, как в примере. Значения из поля "Подразделение", в конце примера, станут основой для расчета итоговых затрат на ФОТ.
- Оклад - размер оклада, который вы планируете указать в трудовом договоре, т.е. GROSS - c учетом НДФЛ
- KPI - или Key Performance Indicators (ключевой показатель эффективности) - в нашем примере это количество окладов, которое предполагается к выплате для данной вакансии, в качестве премиальной части за год.
Полезный совет: чтобы перенести строку внутри ячейки Excel, необходимо нажать Alt+Enter, в MacOS - Option+Return
Следующий лист - таблица с расчетом окладов на календарный год:
Обратите внимание, что заголовки начинаются со второй строки. Первая строка нужна для расчета KPI - она указывает месяц, в котором будет выплачена премия (указывается значение = 1).
Первые четыре колонки можно очень просто перенести из таблицы со штатным расписанием - копировать и "вставить" значения на лист как "связь" - это дополнительная опция инструмента вставки.
Можно сделать это "упражнение" вручную через символ "=". Фактически, ссылаемся на каждую ячейку листа из которой нужно "затянуть" значение. Соответственно, если меняем что-то на листе со штатным расписанием, автоматически меняются значения и на листе с ФОТ.
В самой правой колонке KPI формула имеет следующий вид (все формулы ниже будут для третьей строки, их можно копировать вниз нужное количество раз без потери данных):
=ЕСЛИОШИБКА(ВПР(A3;штатное_расписание;6;0);0)
При помощи ВПР затягиваем данные по KPI из листа со штатным расписанием по ключевому полю 'key'. Для удобства, я дал таблице имя "штатное_расписание". Функция ЕСЛИОШИБКА() возвращает значение, если выражение внутри ее (другая функция) выдает ошибку, например "Н/Д". В примере выше, ЕСЛИОШИБКА вернет 0 в случае ошибки.
Колонки, начиная с января (E) содержат выражение:
=ВПР($A3;штатное_расписание;5;0)*ЕСЛИ(E$1=1;$Q3/2+1;1)
ВПР() затягивается размер оклада, а при помощи ЕСЛИ рассчитывается коэффициент KPI - половина от размера значения KPI: если в первой строке, в колонке с месяцем, на который выполняется расчет стоит "1", то оклад умножается на 1+ KPI/2, в противном случае на 1.
Еще немного терпения и все будет готово:)) Следующий лист - по форме полностью аналогичен предыдущему с окладами за исключением того, что мы не будем переносить колонки key и KPI:
Первая строка осталась пустой для симметрии:)) Колонки A-D копируются по-аналогии с листом с окладами через копирование и вставку связи.
На этом листе мы будем считать все три вида социальных взносов (ОПС, ОСС и ОМС) при помощи самописной функции СОЦВЗНОС. Формула для ячейки D3 будет иметь следующий вид:
=СОЦВЗНОС(Оклады!E3;Оклады!$E3:Оклады!E3;ОПС_база;ОПС_Ставка1;ОПС_Ставка2)+СОЦВЗНОС(Оклады!E3;Оклады!$E3:Оклады!E3;ОСС_база;ОСС_Ставка1;ОСС_Ставка2)+СОЦВЗНОС(Оклады!E3;Оклады!$E3:Оклады!E3;ОМС_база;ОМС_Ставка1;ОМС_Ставка2)
Получилось громоздко... если приглядеться, то мы просто в каждой ячейки вызываем три раза функцию СОЦВЗНОС() для расчета каждого вида взносов через знак +. Основное, на что следует обратить внимание, это первые два аргумента, которые мы затягиваем с листа с окладами - размер оклада за месяц и размер оклада нарастающим итогом для этой вакансии: Оклады!E3;Оклады!$E3:Оклады!E3.
В заключении, добавим на новый и последний в рамках этого примера лист с наименованиями подразделений:
На этом листе мы посчитаем итоговые затраты на ФОТ с учетом взносов по подразделениям при помощи функции СУММЕСЛИ(). Подробнее о том, как работать с этой функцией можно посмотреть здесь.
Для ячейки B2 выражение будет иметь следующий вид:
=СУММЕСЛИ(Оклады!$B:$B;'Затраты на ФОТ'!$A2;Оклады!E:E)+СУММЕСЛИ(Соцвзнос!$A:$A;'Затраты на ФОТ'!$A2;Соцвзнос!D:D)
Последовательно считается сумма ФОТ по подразделению и сумму взносов. Для первой функции СУММЕСЛИ() передаются следующие аргументы:
- диапазон - колонка B на листе с окладами
- критерий - наименование подразделения по которому необходимо выполнить суммирование
- диапазон суммирования - колонка с ФОТ соответствующего месяца
Аналогичным образом заполняется функция СУММЕСЛИ() для листа со взносами с ФОТ.
В итоге, на пяти листах получился калькулятор для бюджетирования расходов на фонд оплаты труда. Если все выражения в листах с ФОТ и взносами растянуть на определенное количество строк с "запасом" для вашего проекта, их можно скрыть, также как и лист с данными по предельным значениям и ставками по социальным взносам. Также можно скрыть колонку key на листе со штатным расписанием.
