Как найти процент выполнения плана формула Excel

Одним из основных показателей управленческого учета являются выполнение плана и прогнозирование выполнения плана. Плановые показатели могут быть установлены в штуках, деньгах, клиентах и т.д., но подход к расчету выполнения плана одинаков во всех случаях.

При этом стоит различать показатели “выполнение плана” и “отклонение от плана”. В этой статье разберемся, как посчитать выполнение плана и отклонение от плана в excel. Также научимся делать простой прогноз выполнения плана в excel.

 

Расчет выполнения плана в excel 

 

Выполнение плана рассчитывается, как правило, в процентах. Другими словами, понятие “выполнение плана” означает, сколько процентов фактические показатели составляют относительно плановых.

Формула выполнения плана в процентах выглядит так:

Выполнение плана = Факт / План *100%

 

Рассмотрим пример расчета выполнения плана продаж.

У торгового представителя установлен план продаж:

  • сок вишневый — 110 упаковок
  • сок яблочный — 150 упаковок

Фактически в течение месяца он продал:

  • сок вишневый — 118  упаковок
  • сок яблочный — 133 упаковки

Рассчитаем процент выполнения плана продаж:

Выполнение “сок вишневый” = 118 / 110 * 100% = 107,27%
Выполнение “сок яблочный” = 133 / 150 * 100% = 88,67%

 

Рассчитаем выполнение плана продаж в excel по той же формуле:

выполнение плана

Обратите внимание, что в excel не нужно умножать на 100%. Вместо этого выбираем процентный формат ячейки (как показано на картинке или через правую кнопку мыши — Формат ячеек).

Таким образом, видим, что план продаж вишневого сока перевыполнен (т.к. он больше 100%), а во втором случае — не выполнен.

выполнение плана

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

 

 

 

Расчет отклонения от плана в excel

 

Формула отклонения факта от плана такая:

Отклонение от плана = (Факт / План — 1) * 100%

 

Воспользуемся тем же примером и посчитаем отклонение фактических показателей от плана продаж. 

Отклонение от плана “сок вишневый” = (118 / 110 — 1) * 100% = 7,27%
Отклонение от плана “сок яблочный” = (133 / 150 — 1) * 100% = -11,33%

 

В первом случае отклонение от плана положительное (больше нуля), следовательно, план перевыполнен. А в случае я яблочным соком отклонение отрицательное, поэтому можем сделать вывод, что план по продажам яблочного сока не выполнен, и до выполнения плана не хватило 11,3%

Теперь сделаем расчет отклонения факта продаж и от плана в excel.

выполнение плана

Как видите, мы снова не умножаем на 100%, а применяем процентный формат ячейки.

 

Прогноз выполнения плана

 

Прогнозирование — достаточно обширная тема, поэтому в рамках данной статьи рассмотрим простой прием прогнозирования выполнения плана.

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

Формула для прогноза выполнения плана:

Прогноз выполнения плана= (Факт на сегодня / Кол-во отработанных дней * Кол-во рабочих дней в месяце) / План * 100%

 

Вернемся к нашему примеру с продажами сока. Предположим, сегодня 17 октября (т.е. факт будет с 1 по 16 октября), и с начала месяца торговый представитель продал 55 коробок вишневого сока и 78 яблочного. Помним из первого примера, что план по вишневому соку 110 упаковок, а по яблочному 150.

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

 

Предположим, с 1 по 16 октября торговый представитель отработал 11 дней, а всего в октябре 21 рабочий день. 

Прогноз по вишневому соку = 55 / 11 * 21 = 105
Прогноз по яблочному соку = 78 / 11 * 21 = 149

Тот же пример в excel. Для наглядности выведем количество отработанных и рабочих дней в отдельные столбцы.

выполнение плана

Далее можно также сделать прогноз выполнения плана. 

выполнение плана

Из предыдущего примера мы уже знаем, что по факту за месяц было продано 110 коробок вишневого сока и 133 яблочного. Как видите, в первом случае погрешность прогнозирования совсем небольшая, в случае же с яблочным соком расхождение уже больше. 

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

Несмотря на недостатки укрупненного прогнозирования, многие компании используют именно такой метод прогноза выполнения плана.

В продолжение темы расчета процента выполнения плана: