Мне даже в голову бы не пришло писать целую статью про то, как посчитать процент выполнения плана в Excel (кто же не умеет считать проценты, да?), если бы не столкнулась с реальным случаем не знания таких простых понятий. Тут-то я задумалась: а действительно ли это так просто?
А посетив Яндекс.Вордстат и увидев, сколько запросов на тему “как посчитать процент выполнения плана” и подобных, решила, что нужно сделать небольшую шпаргалку по расчету процентов на примере Excel.
Для начала разберемся с базовыми понятиями: выполнение плана и отклонение факта от плана. Многие их путают. Посмотрите на формулы, сразу станет понятно, в чем отличие.
Выполнение плана = Факт / План * 100%
Отклонение факта от плана = Факт / План * 100% — 1
Как видите, разница заключается в единице, которая отнимается во втором случае.
Рассмотрим на примере:
Формулы, по которым посчитаны показатели, выведены под таблицей. В случае расчета процентов в Excel на 100% умножать не нужно, достаточно задать ячейке процентный формат.
Выполнение плана = 10000/12000*100% = 83%Отклонение от плана = 10000/12000*100% — 1= -17%
или
Отклонение от плана = Выполнение плана — 1
Таким образом, отклонение от плана может быть отрицательным (меньше нуля), а выполнение плана — нет. Даже если факт отрицательный, выполнение плана все равно не может быть меньше нуля, дальше разберемся почему.
Также важно: выше мы рассмотрели классическую формулу расчета выполнения плана, однако у этого показателя есть нюансы для случаев, когда факт или план меньше или равны нулю.
Случай № 1. Процент выполнения плана в Excel, если Факт и План больше нуля
Это самый распространенный случай в практике.
В этой ситуации выполнение плана считается по формуле, рассмотренной в начале статьи.
Выполнение плана = Факт / План * 100%
Случай № 2. Если Факт и План меньше нуля
Это тоже достаточно распространенный случай, например, в отчете о прибыли и убытках все убытки показываются с минусом.
Рассмотрим на примере себестоимости.
В этом случае:
Выполнение плана = План / Факт
Отклонение от плана = 1 — Факт/План
Если присмотритесь к данным, то поймете логику: фактическая себестоимость -5000, это больше (по модулю), чем план -4800. Т.е. мы как бы перетратили 200 и не выполнили план. Следовательно, выполнение плана будет меньше 100%.
Случай № 3. Если Факт больше нуля, а План меньше нуля
Этот случай более редкий, но вполне реален, например, для прибыли. Да, крупные компании иногда закладывают в план или бюджет отрицательную прибыль для определенных периодов.
Компания, в которой я работаю, заложила на февраль отрицательную EBITDA (прибыль до налогообложения и амортизации) из-за того, что ожидали ужесточения ковидных мер — однако, ограничения наоборот были ослаблены и EBITDA получилась положительной.
В этом случае, считать будем так:
Отклонение от плана будет считаться такой же формуле, как в предыдущем примере:
Отклонение от плана = 1 — Факт/План
А вот выполнение плана будет считаться немного хитрее:
Выполнение плана = (Факт — План) / Факт
В нашем примере это будет:
(1000 — (-1200)) / 1000 = (1000 + 1200) / 1000 = 2200 / 1000 = 120%
Случай № 4. Если Факт меньше нуля, а План больше нуля
Это совсем редкий случай. Например, у компании была переплата при закупке офисного оборудование, и в план заложили возврат части оплаченной суммы при отсутствии новых закупок.
Однако, по факту новые закупки все же были, и таким образом факт оказался отрицательным.
В этом случае отклонение от плана считается по классической формуле:
Факт / План — 1
А выполнение плана целесообразно поставить равным нулю, поскольку величину выполнения нелогично считать отрицательной. План либо выполнен на какую-то величину, либо не выполнен (0%). А вот насколько он не выполнен — уже показывает величина отклонения.
Случай № 5. Если Факт равен 0, а План больше нуля
Отклонение от плана = -100%
Случай № 6. Если Факт равен 0, а План меньше нуля
Отклонение от плана = 100%
Случай № 7. Если Факт больше 0, а План равен нулю
Отклонение от плана = 100%
Случай № 8. Если Факт меньше 0, а План равен нулю
Отклонение от плана = -100%
В этой статье мы узнали, как посчитать процент выполнения плана в excel с учетом различных нюансов.
Статья взята с моего сайта https://excel-analytics.ru/