Модель для оценки влияния KPI на себестоимость и прибыль можно представить в виде такой таблицы
В этой таблице KPI от выручки с НДС у нас постоянный и равен 2,5%
Если мы будем менять значение KPI в ячейке С13, то значения таблицы будут пересчитываться, и для оценки влияния мотивации по показатели деятельности предприятия при сохранении всех остальных параметров получим такую таблицу
Из таблицы видно, что постоянный процент от выручки в 2,5% позволит предприятию получать прибыль при снижении выручки на 20%, при этом расходы на ФОТ при падении выручки будут снижаться в большей степени, а при росте выручки, будут расти в меньшей степени.
Увеличение KPI на 0,5% до 3% снижает рентабельность на 1%, При KPI 6%, рентабельность снижается уже на 4%.
Формулы для расчета таблицы
Кроме влияния KPI, по это таблице можно оценивать влияние на показатели деятельности предприятия и других составляющих, например, роста цен на закупку, при сохранении цены реализации и объемов продаж на том же уровне
Например, при роста цены закупки на 10%, наценка сократится до 1,36% и при сложившемся уровне расходов предприятие будет терпеть убытки.
Теперь вернемся к KPI и сделаем модель для переменных KPI, то есть процент от выручки будет увеличиваться при росте выручки, например, так
Из модели мы видим, несмотря на то, что KPI всегда меньше роста выручки, при перевыполнении плана на 10%, расходы на оплату труда продавцам возрастают уже на 65%, и в итоге рост выручки на 20% может привести к убыткам из-за роста расходов на оплату труда.
При этом при недовыполнении плана на 10%, расходы на оплату труда снижаются на 35%, это скорее всего демотивирует продавцов и приведет к увольнениям.
Формулы для модели с переменным KPI
Изменяя значения KPI в строке 22 можно подобрать систему мотивации, не приводящую к убыткам и стимулирующую продавцов к росту выручки.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке