Excel и исторические данные в деле: Построение плана выручки/прибыли на следующий год за 15 минут

В этой статье мы рассмотрим Excel-файл, в который нужно добавить исторические данные, далее формулы автоматически сделают помесячный прогноз на следующий год и покажут планы. Если в бизнесе используется простейший учет финансов, то необходимые данные можно вытащить и вставить в файл за 15 минут.

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

Встроенная функция Excel FORECAST.ETS используется для прогнозирования временных рядов. Эта функция основана на методе Хольта-Винтерса и предназначена для прогнозирования значений, учитывая сезонность и тренды.

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

Также добавлены различные формулы, которые упрощают анализ выполнения планов и рассчитывают новые планы с учетом текущей ситуации

В файле есть 2 опции для прогнозирования - выручка и прибыль. Загрузка исторических данных для них идентичная. Листы содержащие _revenue заносятся данные по выручке, во вкладки содержащие _profit - прибыль

old_data_ - содержит информацию о выручке (заканчивается на _revenue) или прибыли (заканчивается на _profit). В эти вкладки вносятся данные в разрезе месяцев за полные года. Также сохраняйте сортировку от меньшего к большему по датам, это важно.

last_year_ - содержит информацию о выручке (заканчивается на _revenue) или прибыли (заканчивается на _profit). В эти вкладки вносятся данные в разрезе месяцев за предыдущий год. Если вы хотите сравнивать не с предыдущим годом, а с другим, то можете внести данные за тот который хотите, от этого формулы не сломаются. При таком кейсе рекомендую в основной вкладке forecast_and_plan переименовать столбцы свзянные с расчетом относительно прошлого года, чтобы не ошибиться при анализе планов. Также сохраняйте сортировку от меньшего к большему по датам, это важно.

Советы:

По итогу вам нужно выручку / прибыль по месяцам вставить в файл - на этом работа с исходными данными закончена и можно приступать к анализу листа forecast_and_plan

Таблицы с планами по выручке и прибыли схожи, поэтому в статье я подробно рассмотрю анализ планов выручки. Прибыль сможете проанализировать сами подобно анализу выручки.

В файле используются реальные данные B2C компании, они были видоизменены таким образом чтобы сезонность осталась, но при этом по цифрам даже сами представители компании не могли понять что это их бизнес

Кейс - создаем план на 2023 год в конце 2022 года. В момент планирования нет полных данных за 2022 год, поэтому данные за оставшиеся месяца мы примерно прикидываем, а когда год закроется вносим актуальные - тем самым улучшив точность планирования.

Начинается 2023 год, в столбец месяц записываем все месяца 2023 года, по мере закрытия месяцев вносим выручку в столбец выручка (Месяц и выручка заполняются вручную. Месяц - месяц текущего года, выручка - это выручка текущего года).

В таблице у вас есть 3 дельты:

Органический рост - это план который получается путем прогнозирования на основании исторических данных по методу Хольта-Винтерса

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

Органическим планом он был назван потому что прогнозирование опирается на исторические данные, которые содержат в себе информацию по сезонности, инфляции, курсу доллара (обычно все это вшивается в цену и напрямую влияет на выручку). И если в году на который вы делаете план - вы не будете вводить новые направления в бизнесе или цены резко не пойдут вниз - тогда отставание от органического плана будет сигнализировать что ваши темпы роста замедляются и стоит обратить на это внимание.

В текущем кейсе видно что компания в Q1 по выручке опережала выручку прошлого года, но отставала от органического роста, другими словами начала замедлятся. ТОП менеджмент обратил внимание на эту ситуацию, проработал проблемы и дальше ситуация исправилась. На момент конца августа выручка в текущем году опережала план по органическому росту на 2%

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

Прогнозный процент от выручки - доля в выручке которая приходится на каждый месяц. В этом столбце наглядно видна сезонность выручки вашего бизнеса. Если у вас встречаются ярковыраженные месяцы в на которые приходится 15 и более процентов выручки, стоит задуматься над тем как увеличить выручку в другие месяца, чтобы стабилизировать ситуацию по году.

Прогноз органическогого роста по выручке - помесячный прогноз выручки по методу Хольта-Винтерса

Новый процент от органического роста - считаем долю выручки приходящегося на каждый оставшийся месяц

Новый план - это столбец который пересчитывает план с учетом закрытых месяцев (месяца текущего года по которым есть выручка, работает автоматически, нужно лишь просто внести ). Если вы опережаете органически рост то новый план будет меньше изначально прогноза, а если вы отстаете от органического роста, то план подскажет какая выручка вам нужна чтобы закрыть план по органическому росту.

Увеличение от прошлого года - показывает насколько новый план на оставшиеся месяца больше или меньше результатов прошлого года.

Прогноз по выручке / органический - общий план по выгрузке на весь год

План к прошлому году - на сколько прогноз больше / меньше прошлого года

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

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

В таком случае вы можете взять весь прогноз по выручке / органической за год, в текущем кейсе это 25 329 316 683 и прибавить к нему ожидаемую выручку по новым напралениям за год. Если вы понимаете что цены вырастут сильнее чем в прошлые года, вы можете также заложить это тем самым увеличив амбициозный план. Также вы можете заложить какие-то прорывы и еще увеличить амбиционзный план. После всех этих итераций в нашем кейсе получилось что амбициозный план (или план максимум) стал равен 28 000 000 000

В амбициозном плане мы идем от обратного, а именно у нас есть план на год в размере 28 000 000 000 и нам нужно его распределить по месяцам

Для этого идем в табличку ниже Амбициозного плана и вписываем наш план в План по выручки от бизнеса / амбициозный. Далее автоматически рассчитается План к прошлому году и Оставшийся план для распределения.

После мы можем обратить внимание на Амбициозный план, данные в таблице меняются автоматически

Прогноз амбициозного плана по выручке - прогноз выручки с учетом амбициозного плана

Новый процент от амбициозной выручки - считаем долю выручки приходящегося на каждый оставшийся месяц

Новый амбициозный план - это столбец который пересчитывает амбициозный план с учетом закрытых месяцев (месяца текущего года по которым есть выручка, работает автоматически, нужно лишь просто внести)

Увеличение от прошлого года - показывает насколько новый амбициозный план на оставшиеся месяца больше или меньше результатов прошлого года.

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

Если я вижу что выручка между органическим ростом и амбициозным планом, значит компания наращивает рост и это позитивный фактор. В зависимости от особенностей амбициозного плана анализирую ситуацию. Например новое направление только запустилось, идет хороший рост выручки в нем - можно просто наблюдать за ситуацией. Или новое направление запустилось 6 месяцев назад, выручка растет, но темпы не очень. В целом компания идет хорошо (выше органического плана) но новое направление буксует и есть веротяность что закроет год хуже ожиданий - значит нужно погружаться в направление и искать проблемы уже в нем.

Я могу порекомендовать изменить амбициозный план, например резко изменилась ситуация и старое планирование уже не актуально, тогда стоит скорректировать амбициозный план и уже ориентироваться только на него.

Если у вас в компании сложная система продаж, есть разные отделы с руководителями которые сами строят план по своему направлению, то вы можете выдать этот файл каждому руководителю и пусть он его заполнит его по своему направлению и будет отслеживать. Заполнить и поддерживать файл - легко, поэтому каждый руководитель сможет его вести, тратя на заполнение не больше 10 минут в месяц.

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

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

Большая просьба - если будете кому-то отдавать файл или публиковать его соц сетях, сопровождать пост ссылкой на эту статью