Расчёт премии в Excel: KPI, формулы, пороги и понятные бонусы
Практическая модель премирования: структура файла, реальные формулы, веса KPI, пороги, бюджет фонда и проверяемый пример расчёта.
1. Сначала зафиксируйте правило премирования
Премиальная таблица должна переводить уже понятное правило мотивации в прозрачный расчёт. До Excel определите период оценки, базу премии, показатели, порог допуска к выплате, максимальный коэффициент и ситуации, когда результат корректируется вручную. Если правила нельзя объяснить сотруднику двумя-тремя предложениями, сложная формула не сделает их понятнее.
Для большинства отделов достаточно 3–5 KPI. Например, у менеджера по продажам это выручка, валовая прибыль, конверсия и качество без возвратов. Не стоит делать главным KPI число звонков, если бизнес зарабатывает на оплаченных и маржинальных сделках. Показатель должен быть измеримым и находиться в зоне влияния сотрудника.
Отдельно решите, является ли премия гарантированной частью дохода или переменной выплатой по результату. Excel помогает считать управленческую модель, но правила начисления и выплаты должны соответствовать трудовым документам и внутренним положениям компании. Поэтому таблица не должна незаметно подменять утверждённый порядок мотивации.
2. Рекомендуемая структура Excel-файла
Практичная книга состоит из четырёх листов. На «Настройки» храните KPI, веса, пороги, коэффициенты, максимальный бонус и бюджет фонда. На «Факт» загружайте исходные результаты сотрудников. На «Расчёт» выводите план, факт, выполнение, взвешенный балл, коэффициент и сумму премии. На «Контроль» — итог по отделам, превышение бюджета, строки с ошибками и ручные корректировки.
Минимальные столбцы листа «Расчёт»: Период, Сотрудник, Отдел, База премии, План KPI 1, Факт KPI 1, Выполнение KPI 1, План KPI 2, Факт KPI 2, Выполнение KPI 2, Итоговый KPI, Коэффициент, Расчётная премия, Корректировка, Итог к выплате и Комментарий. Если показателей больше, лучше хранить их в нормализованной таблице, а итог собирать формулами или сводной.
Не зашивайте пороги и веса внутрь десятков формул. Вес выручки 40% должен находиться в одной ячейке настроек, а формулы ссылаться на неё абсолютной ссылкой. Тогда изменение системы мотивации не потребует ручной правки каждой строки и снизит риск того, что у двух сотрудников останутся разные версии правил.
3. Формулы выполнения KPI: прямые и обратные показатели
Для прямого KPI, где больше — лучше, базовая формула выполнения при плане в E2 и факте в F2: =ЕСЛИОШИБКА(F2/E2;0). При плане выручки 1 000 000 ₽ и факте 920 000 ₽ получится 92%. Если перевыполнение не должно бесконечно увеличивать бонус, ограничьте результат: =МИН(ЕСЛИОШИБКА(F2/E2;0);120%).
Для обратного KPI, где меньше — лучше, логика противоположная. Например, допустимый уровень возвратов 4%, а факт 5%. Формула =МИН(ЕСЛИОШИБКА(E2/F2;0);120%) даст 80%. Нельзя применять факт/план одновременно к выручке и возвратам: тогда ухудшение обратного показателя ошибочно увеличит итоговый балл.
Если нужно видеть не только процент, добавьте отклонение. В рублях: =F2-E2. В процентах: =ЕСЛИОШИБКА((F2-E2)/E2;0). Эти поля полезны руководителю: выполнение 92% выглядит абстрактно, а отклонение −80 000 ₽ сразу показывает масштаб недобора.
4. Веса KPI и общий результат
Вес определяет вклад показателя в премию. Допустим: выручка — 40%, валовая прибыль — 30%, конверсия — 20%, качество — 10%. Сумма весов обязана быть 100%. На листе настроек добавьте контроль =СУММ(B2:B5); если результат отличается от 100%, расчёт премии следует считать некорректным.
Если выполнение четырёх KPI равно 92%, 105%, 110% и 100%, общий балл составляет 92%×40% + 105%×30% + 110%×20% + 100%×10% = 100,3%. В Excel при выполнении в G2:J2 и весах в G1:J1 используйте =СУММПРОИЗВ(G2:J2;$G$1:$J$1). Абсолютная ссылка позволяет протянуть формулу вниз без смещения весов.
Не давайте одному легко перевыполняемому KPI компенсировать провал критичного результата. Можно ввести стоп-фактор: например, если валовая прибыль выполнена менее чем на 70%, премия равна нулю независимо от общего балла. Такая логика должна быть явно записана в настройках и видна в расчёте отдельным полем «Допуск».
5. Пороги и коэффициенты премии
Пример шкалы: итог ниже 80% — коэффициент 0; от 80% до 94,99% — 0,5; от 95% до 99,99% — 0,8; от 100% до 109,99% — 1; от 110% — 1,2. Для простой модели формула может быть такой: =ЕСЛИ(K2<80%;0;ЕСЛИ(K2<95%;0,5;ЕСЛИ(K2<100%;0,8;ЕСЛИ(K2<110%;1;1,2)))).
Когда порогов много, вложенные ЕСЛИ становятся неудобными. Сделайте на листе «Настройки» таблицу из двух колонок: Нижняя граница и Коэффициент, отсортированную по возрастанию. Тогда коэффициент можно получать приближённым поиском через ПРОСМОТРX/XLOOKUP или ВПР с подходящим режимом поиска. Так шкалу можно менять без переписывания расчёта.
Итоговая премия при базе в D2 и коэффициенте в L2 считается как =D2*L2. Если есть абсолютный потолок 50 000 ₽, используйте =МИН(D2*L2;50000). Лучше ссылаться на ячейку с лимитом: =МИН(D2*L2;Настройки!$B$12), чтобы правило было видно и управлялось централизованно.
Создать расчёт премии в Excel
Опишите KPI, веса и правила бонусов — SmartXLSX соберёт персональный XLSX.
Открыть инструмент →6. Практический пример с числами
У Анны база премии 30 000 ₽. План выручки — 1 000 000 ₽, факт — 920 000 ₽: выполнение 92%. План валовой прибыли — 300 000 ₽, факт — 315 000 ₽: 105%. Целевая конверсия — 20%, факт — 22%: 110%. Качество выполнено на 100%. Веса — 40/30/20/10.
Взвешенный результат: 92%×40% + 105%×30% + 110%×20% + 100%×10% = 100,3%. По шкале из предыдущего раздела это коэффициент 1, поэтому расчётная премия Анны — 30 000 ₽. Если бы итог составил 96%, коэффициент был бы 0,8 и выплата — 24 000 ₽. При 112% коэффициент 1,2 дал бы 36 000 ₽.
Теперь добавим бюджетный контроль. В отделе 8 сотрудников с базой по 30 000 ₽, значит базовый фонд — 240 000 ₽. Если коэффициенты сотрудников 1; 0,8; 1,2; 1; 0,5; 1; 0; 1,2, итоговый фонд равен 201 000 ₽. Формула по столбцу премий: =СУММ(M2:M9). Если лимит отдела 220 000 ₽, запас бюджета составляет 19 000 ₽; при превышении таблица должна показать предупреждение до передачи данных на выплату.
7. Корректировки, бюджет фонда и контроль данных
Не заменяйте формулу премии вручную. Для исключений добавьте отдельные колонки «Корректировка, ₽», «Причина» и «Кто согласовал». Итог можно считать как =МАКС(0;РасчётнаяПремия+Корректировка). Тогда исходный автоматический результат сохраняется, а изменение остаётся объяснимым и проверяемым.
На листе «Контроль» выведите фонд премий по отделам через СУММЕСЛИМН. Например: =СУММЕСЛИМН(Расчёт[Итог к выплате];Расчёт[Отдел];A2;Расчёт[Период];$B$1). Рядом сравните сумму с бюджетом: =ЕСЛИ(B2>C2;"Превышение на "&ТЕКСТ(B2-C2;"# ##0 ₽");"В пределах бюджета").
Добавьте технические проверки: пустой план, отрицательная база премии, сумма весов не 100%, неизвестный сотрудник, дубли сотрудник+период, коэффициент выше установленного максимума и корректировка без комментария. Ошибка должна быть заметна до расчёта итогового фонда, а не после выплаты.
8. Типичные ошибки в расчёте премий
Первая ошибка — слишком много KPI: сотрудник перестаёт понимать, что реально влияет на результат. Вторая — одинаковая формула для прямых и обратных показателей. Третья — отсутствие потолка, из-за чего единичный всплеск одного показателя создаёт непропорциональный бонус. Четвёртая — изменение правил в середине периода без фиксации версии.
Не смешивайте исходные данные и ручные корректировки. Не храните коэффициенты в тексте формулы вроде 0,8 или 1,2, если они являются бизнес-правилом. Не считайте премию только по выручке, если менеджер может увеличить её скидками и ухудшить маржу. И не округляйте выполнение каждого KPI слишком рано: округление лучше делать в отображении или на финальном этапе.
Ещё одна ошибка — рейтинг ради рейтинга. Если два сотрудника получили одинаковую премию, но один просел по марже, а второй по конверсии, управленческие действия будут разными. Поэтому итоговый коэффициент всегда должен раскрываться до исходных KPI, а не быть единственным числом в отчёте.
9. Как внедрить файл в ежемесячную работу
Зафиксируйте простой регламент. После закрытия периода ответственный загружает факт, проверяет ошибки, руководитель подтверждает спорные корректировки, затем файл формирует итоговый реестр выплат. После утверждения сохраняйте копию периода или историю строк — не перезаписывайте прошлый месяц новыми значениями.
На дашборде достаточно 5–6 показателей: общий фонд премий, бюджет фонда, средний итоговый KPI, число сотрудников без премии, число сотрудников с повышенным коэффициентом и крупнейшие отклонения. Это полезнее десятка декоративных диаграмм и позволяет быстро перейти к конкретной строке расчёта.
Если исходный файл нужен быстро, используйте инструмент SmartXLSX для расчёта премии и задайте свои KPI, веса и пороги. Для проектирования самих показателей пригодится статья про KPI менеджеров, а для связи мотивации с финансовым результатом — материалы про P&L и план-факт анализ.
Частые вопросы
Как сделать порог премии в Excel?
Задайте таблицу нижних границ и коэффициентов либо используйте ЕСЛИ. Например, ниже 80% — 0, от 80% — 0,5, от 95% — 0,8, от 100% — 1, от 110% — 1,2.
Как рассчитать премию по нескольким KPI?
Посчитайте выполнение каждого KPI, умножьте на его вес и сложите результаты через СУММПРОИЗВ. Сумма весов должна равняться 100%.
Как считать KPI, где меньше — лучше?
Для обратного показателя используйте целевое значение, делённое на факт, и ограничьте максимальное выполнение. Не применяйте формулу факт/план, подходящую для выручки.
Как ограничить максимальную премию?
Задайте максимальный коэффициент и при необходимости абсолютный лимит суммы. Для суммы удобно использовать МИН между расчётной премией и лимитом из настроек.
Как учитывать ручную корректировку?
Не стирайте формулу. Храните корректировку отдельным полем вместе с причиной и согласующим, а итог считайте как расчётная премия плюс корректировка.
Как проверить, что фонд премий не превышен?
Суммируйте итоговые премии по отделу и периоду через СУММЕСЛИМН и сравнивайте результат с бюджетом фонда на отдельном листе контроля.