Формула для расчета премии KPI в Excel, в зависимости от процента выполнения плана
При расчете премии KPI, часто возникает вопрос:"Как автоматически рассчитать сумму премии, в зависимости от процента выполнения плана сотрудником? Например в Excel? Какую прописать формулу?" Спойлер: в конце статьи ссылка на скачивание готовой Excel-таблицы для расчета KPI.
Утверждаем шкалу бонусирования (премиальный коэффициент). Сначала алгоритм, потом автоматизация
Чтобы применить формулу, для начала нужно выработать и утвердить правила расчета премии, в нашем случае "шкалу бонусирования". Систему коэффициентов, которые будем применять для расчета премии, в зависимости от процента выполнения плана.
К примеру, мы выбрали следующую шкалу бонусирования, с нижним порогом в 80% и верхним порогом в 120%. И установили следующую систему коэффициентов в зависимости от процента выполнения плана:
- до 80% - премия не выплачивается;
- от 80% до 100% - премия выплачивается с понижающим коэффициентом;
- от 100% до 120% (вкл.) - премия выплачивается с повышающим коэффициентом;
- свыше 120% - премия выплачивается с фиксированным коэффициентом 1,2.
Обратите внимание, что понижающий коэффициент не соответствует проценту выполнения. При выполнении плана на 80%, применяем понижающий коэффициент не 0,8, а 0,5. Это сделано осознанно для того, чтобы порог выполнения плана в 80% не воспринимался как "план почти выполнен". Чтобы нижний порог бонусирования не воспринимался как минимальный план.
Какая логика такого расчета при выполненном плане в 80%? С одной стороны, сотрудник стремился выполнить план, но немного не дотянул. Лишать совсем его премии будет несправедливо. Премия выплачивается. Но только половина, так как бизнесу важно выполнение плана на 100%, а не на 80%. Даже за 99% выполнения плана выплачивается только 80% премии (коэф. 0,8). Если выплачивать 99% от суммы премии, то разница для сотрудника будет незначительной. Стимул дотягивать план до 100% в этом случае почти отсутствует.
Кстати, в большинстве случаев, нижний порог бонусирования правильнее устанавливать в 90% от плана. Т.е. при выполнении плана ниже 90% премию не выплачивать. А при 90% выполнении плана, выплачивать 50% премии и т.д. Но иногда, в качестве временной меры, применим нижний порог бонусирования в 70%. Здесь, для примера, взят усредненный вариант.
Прописываем формулу расчета премии KPI
Как автоматически рассчитать премию по такой шкале бонусирования? Какую формулу прописать в Excel для получения суммы премии по KPI?
Для такого расчета, хорошо подходит формула "=ЕСЛИ". Если выполнение такое-то, то применяем коэффициент такой-то. И умножаем на назначенную сумму премии по этому показателю:
=ЕСЛИ(G12<80%;0;ЕСЛИ(G12<85%;D12*0,5;ЕСЛИ(G12<90%;D12*0,6;ЕСЛИ(G12<95%;D12*0,7;ЕСЛИ(G12<100%;D12*0,8;ЕСЛИ(G12<120%;D12*G12/100%;ЕСЛИ(G12>=120%;D12*1,2)))))))
Формула ссылается на ячейку с процентом выполнения плана и на ячейку с суммой премии. Коэффициенты и пороги бонусирования "зашиты" в формулу. В зависимости от процента выполнения плана, формула применяет нужный коэффициент и рассчитывает итоговую сумму премии KPI.
Скачать бесплатно и без регистрации готовый Excel шаблон для расчета премии KPI, с прописанными формулами можно на нашем сайте Стратегическое Re:шение. Там же приведен пример поэтапного заполнения всей таблицы для расчета премии KPI и ссылка на скачивание таблиц с примерами расчета KPI по восьми различным должностям (от ген.директора, РОПа, менеджера продаж до HR-менеджера и преподавателя).
P.S. Важно! Польза готовой таблицы для расчета KPI в экономии времени на разработку формы расчета премии и прописывание формул. Только таблицы недостаточно, чтобы правильно внедрить систему KPI.
С уважением, Александр Шведов. Управляющий партнер "Стратегического Re:шения"