Расчет прогноза продаж по методу скользящей средней в Excel

Автор: Алексей Батурин.

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

  1. Рассчитать коэффициенты сезонности;
  2. Выбрать период для расчета среднего значения;
  3. Рассчитать прогноз, т.е. среднее значение умножить на коэффициент сезонности;
  4. Учесть дополнительные факторы, которые значительно влияют на продажи;

Рассчитать прогноз по методу скользящей средней очень просто. Для этого берём среднее значение, например, средние продажи за последние 3 месяца и умножаем на коэффициент сезонности к 3-м месяцам - и прогноз на месяц готов. Аналогичным образом делаем и на следующий месяц, только в расчет уже попадет предыдущий прогнозный месяц.

 

1. Рассчитаем коэффициенты сезонности для прогноза по методу скользящей средней.

Для этого рассчитываем коэффициенты сезонности очищенные от роста, как описано в статье «Как рассчитать коэффициенты сезонности, очищенные от роста?». Затем определяем коэффициенты сезонности к предыдущим периодам, к 1 месяцу, к 2-м месяца, к 3-м месяцам и т.д. в зависимости от того, за какой период берем среднее значение для прогнозирования продаж. Например, рассчитаем месячные коэффициенты сезонности (см. вложенный файл лист "Расчет коэффициентов")

  • к 1 месяцу:

    • коэффициент января — отношение январского коэффициента сезонности очищенного от роста к декабрьскому;

    • февраля — февральского коэффициента к январскому;

    • марта — март к февралю;

    • и т.д.

  • к 2-м месяцам:

    • для января — отношение январского коэффициента сезонности к среднему значению декабря и ноября

    • для февраля — февраль делим на среднее значение коэффициентов  января и декабря

    • для марта — март к среднему февральского и январского коэффициентов

    • и т.д.

  • к 3-м месяцам:

    • для определения январского коэффициента сезонности к 3-м месяцам мы январский коэффициент сезонности, очищенный от роста, делим на среднее значение коэффициентов сезонности, очищенных от роста, за декабрь, ноябрь, октябрь;

    • для февраля — коэффициент февраля делим на среднее значение коэффициентов ноября, декабря и января;

    • Для марта — отношение марта к среднему значению коэффициентов сезонности очищенных от роста декабря, января и февраля;

    • И т.д.

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

 

2. Выбираем период расчета среднего значения для прогноза по методу скользящей средней.

Для этого делаем прогноз для последнего и предпоследнего периодов, данные за который нам известны, тремя или более способами для определения подходящего периода расчета средней (см. вложенный файл лист «Выбор периода»). И смотрим, какой из вариантов делает более точный прогноз:

  1. Рассчитаем прогноз продаж по методу скользящей средней к 1-му месяцу:

Декабрь = объём продаж ноября умножим на декабрьский коэффициент сезонности к предыдущему месяцу.  

  1. Рассчитаем прогноз продаж по методу скользящей средней к 2-ум месяцам:

Декабрь = средний объём продаж за октябрь и ноябрь умножим на декабрьский коэффициент сезонности к 2-м месяцам.

  1. Рассчитываем прогноз по методу скользящей средней к 3-ем месяцам:

Декабрь = средний объём продаж за сентябрь, октябрь и ноябрь умножим на декабрьский коэффициент сезонности к 3-м месяцам.

Сейчас мы рассчитали прогноз тремя способами на декабрь.  Аналогичным образом рассчитаем на ноябрь.

Теперь сравниваем фактические значения за ноябрь и декабрь с прогнозными рассчитанными 3-мя способами. Мы видим, что в нашем примере наиболее точно прогноз рассчитан по методу скользящей средней к 2-м месяцам, возьмём его за базу. В вашем случае более точный прогноз может оказаться к предыдущему периоду, к 3-м предыдущим или к 4-м предыдущим периодам.

 

3. Рассчитаем прогноз продаж по методу скользящей средней.

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

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

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

 

4. Дополнительные факторы, которые стоит учесть при расчете прогноза продаж.

Для повышения точности прогноза важно:

  1. Из прошлых периодов вычесть факторы, которые значительно повлияли на объем продаж, но в прогнозных месяцах повторяться не будут (акции по стимулированию сбыта, разовая отгрузка крупного нерегулярного клиента, вывод из крупной розничной сети и т.д.).
  2. К прогнозируемым месяцам прибавить факторы, которые значительно повлияют на продажи — начало работы с крупными сетями, проведение крупных акций по стимулированию сбыта, вывод новых товаров, рекламные компании и т.д.

Точных вам прогнозов!

Программа Forecast4AC PRO рассчитает прогноз по методу скользящей средней одновременно более чем для 1000 временных рядов одним нажатием клавиши, значительно сэкономив ваше время, одним из 4-х способов:

  • К среднему за два предыдущих периода

  • К среднему за три предыдущих периода

  • К среднему за 4 предыдущих периода

  • Двойная средняя к 3 и 4 предыдущим периодам

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite - автоматический расчет прогноза в Excel.
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

 

Комментарии   

#23 Алексей Батурин 18.04.2018 09:21
Цитирую Кирилл:
а можно ли сделать прогноз например на 3 года или на 5 лет? если да, то надо ли, пересчитывать коэффициенты сезонности очищенные от роста каждый раз когда я сделал прогноз на год, чтобы сделать прогноз на следующий?

Кирилл, добрый день!

Конечно можно, по горизонту прогнозирования ограничений нет, вопрос целях и точности.
Сезонность не нужно пересчитывать. Тенденция (тренд, скользящая...) задает прогноз на каждый последующий год, а сезонность эту тенденцию распределяет по месяцам.
Цитировать
#22 Кирилл 18.04.2018 08:55
а можно ли сделать прогноз например на 3 года или на 5 лет? если да, то надо ли, пересчитывать коэффициенты сезонности очищенные от роста каждый раз когда я сделал прогноз на год, чтобы сделать прогноз на следующий?
Цитировать
#21 АЛЕКСАНДР 20.09.2017 13:59
Большое спасибо!
Моя почта:
Цитировать
#20 +1 Алексей Батурин 20.09.2017 11:07
Цитирую АЛЕКСАНДР:
Огромное спасибо за Ваш ответ. У меня тогда вопрос, могли бы Вы, если это возможно, каким-либо образом их прислать для ознакомления? Спасибо!

Пример на почту отправлю!
У нас есть продукты, которые автоматизируют управление акциями, оценку эффективности, планирование, согласование и прочие задачи. Возможно заинтересует https://4promotion.ru/
Цитировать
#19 АЛЕКСАНДР 20.09.2017 08:11
Огромное спасибо за Ваш ответ. У меня тогда вопрос, могли бы Вы, если это возможно, каким-либо образом их прислать для ознакомления? Спасибо!

Отправил!
Цитировать
#18 Алексей Батурин 12.04.2017 14:56
Цитирую natalay95:
Добрый день. Помогите разобраться. В пункте 2-ом, дан файл (см. вложенный файл лист «Выбор периода») . я в этом новичок поэтому не поняла ,как расчитан коэффициент сезонности в марте . Заранее спасибо

Наталья, добрый день!
Откройте лист "Расчет коэффициентов" - в нем пошаговый расчет коэф-тов сезонности для всех месяцев
Цитировать
#17 natalay95 12.04.2017 11:30
Добрый день. Помогите разобраться. В пункте 2-ом, дан файл (см. вложенный файл лист «Выбор периода») . я в этом новичок поэтому не поняла ,как расчитан коэффициент сезонности в марте . Заранее спасибо
Цитировать
#16 Алексей Батурин 08.03.2017 21:15
Цитирую Ирина:
А что в файле Excel обозначает полученное отношение (2011 к 2010) в размере 112%? Точность прогноза или что-то ещё? Просто обычно точность прогноза идёт до 100% (встречалось так в примерах моделей), не больше.

Ирина, извиняюсь за задержку, почему-то сообщение о комментарии пришло с опозданием, 2011 к 2010 - это отношение 2011 к 2010 году - коэф-т роста, 112% - прирост на 12% в 2011 по сравнению с 2010 годом
Цитировать
#15 Ирина 18.01.2017 16:40
А что в файле Excel обозначает полученное отношение (2011 к 2010) в размере 112%? Точность прогноза или что-то ещё? Просто обычно точность прогноза идёт до 100% (встречалось так в примерах моделей), не больше.
Цитировать
#14 ольга 16.06.2016 10:07
Цитирую Алексей Батурин:
Цитирую ольга:
Может задам конечно глупый вопрос.
А максимум к 4 месяцам?
Или можно рассчитывать коэффициенты сезонности например к 12 месяцам и т.д?) :oops:
И там по кругу пошло, пошло..... :-*

Может задам конечно глупый вопрос.
А максимум к 4 месяцам?
Или можно рассчитывать коэффициенты сезонности например к 12 месяцам и т.д?) :oops:
И там по кругу пошло, пошло..... :-*

В этой модели нет сезонности. Минимум 12 месяцев, или сезонность от аналога
В этой модели понятно нет.
Я к своим данным считаю к 8 месяцу шло повышение а к 11 прогноз продаж пошел на снижение.
Цитировать

Добавить комментарий