Как рассчитать прогноз продаж с учетом роста и сезонности в Excel?

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

прогнозирование продаж в ExcelИз данной статьи вы узнаете, как в Excel рассчитать прогноз продаж с учетом роста и сезонности

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

Процесс расчета прогноза разделим на 3 части:

  1. Расчет значение тренда;
  2. Определение коэффициентов сезонности;
  3. Прогнозирование продаж;

Рассчитаем прогноз по месяцам на 2 года и 3 месяца на основании продаж за 5 лет (см. вложенный файл).

Для расчета значений тренда:

  1. Определим коэффициенты уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн(). Для этого в ячейки Excel вводим функцию =линейн(объёмы продаж за 5 лет; номера периодов; 1;0). функция Excel линейн Выделяем 2 ячейки, в левой - формула =линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2 + Ctrl+Shift+Enter). Excel рассчитает для нас значение коэффициентов a и b.  
  2. Рассчитываем значения тренда. Для этого в уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y-значения линейного тренда для каждого периода (см. вложенный файл).
О различных вариантах расчета значений линейного тренда вы можете почитать в статье "5 способов расчета значений линейного тренда" и выбрать для себя максимально удобный.

Для расчета коэффициентов сезонности:

  1. Рассчитываем отклонение фактических значений от значений тренда. Для этого фактические значения делим на значения тренда;
  2. Для каждого месяца определяем среднее отклонение за последние 5 лет.
  3. Определяем общий индекс сезонности — среднее значение коэффициентов, рассчитанных в 4 пункте;
  4. Рассчитываем коэффициенты сезонности; каждый коэффициент из пункта 4 делим на коэффициент из пункта 5 (см. вложенный файл);
Более подробно о коэффициентах сезонности вы можете почитать в статье "Как рассчитать коэффициенты сезонности, очищенные от роста?"

Рассчитываем прогноз продаж с учетом роста и сезонности:

  1. Задаём период, на который мы хотим рассчитать прогноз. Для этого продлеваем номера периодов временного ряда на 2 года и 3 месяца.
  2. Рассчитываем значения трена для будущих периодов. В уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде (от 61 до 87). Получаем y-значения линейного тренда для каждого будущего периода (см. вложенный файл).
  3. Рассчитываем прогноз. Для этого значения линейного тренда умножаем на коэффициенты сезонности.

Прогноз с учетом роста и сезонности готов.

Вы также можете руками корректировать прогноз, изменяя коэффициенты a и b линейного тренда y=bx+a, об этом подробно вы можете почитать в статье «О линейном тренде».

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

  • реклама,

  • мероприятия по стимулированию сбыта,

  • ввод новых продуктов,

  • открытие новых направлений продаж,

  • спец. клиенты с разовыми значительными закупками

и т.д., но об этом в следующих статьях.

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

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

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

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

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

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

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

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

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

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

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

 

Комментарии   

#66 Алексей Батурин 20.08.2018 06:54
Цитирую Дарья Лакалина:
Алексей, добрый вечер!
У меня к Вам есть еще вопрос. Каким, на Ваш взгляд, лучше пользоваться методом прогнозирования, если есть данные только за один день. И что делать в случаях, если проглядывается малая корреляция между данными за два года (основные всплески и спады приходятся на разные месяцы).

Дарья, добрый день.
Если данные за 1 день, то это "Новинка", а новинки нужно прогнозировать по аналогу или планировать кол-во клиентов, ср. заказ и исходя их этого продажи.
Если каждый год корреляция не проглядывается, то нужно разбираться.
Месяца в этом и прошлом году не похожи?
Цитировать
#65 Дарья Лакалина 19.08.2018 19:21
Алексей, добрый вечер!
У меня к Вам есть еще вопрос. Каким, на Ваш взгляд, лучше пользоваться методом прогнозирования , если есть данные только за один день. И что делать в случаях, если проглядывается малая корреляция между данными за два года (основные всплески и спады приходятся на разные месяцы).
Цитировать
#64 Дарья Лакалина 16.08.2018 17:02
Спасибо большое!
Цитировать
#63 Алексей Батурин 16.08.2018 16:20
Цитирую Дарья Лакалина:
Алексей, спасибо за Ваши ответы, у Вас нет какого-нибудь файла с примером такого расчета (метод, который Вы описали ниже), так как я в теме прогнозирования совсем новичок и это мое первое задание в прогнозировании, хотелось бы посмотреть на пример.

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

Надеюсь в примере разберетесь. Синим выделил результат.
https://yadi.sk/i/E-QWNvle3aJ4pa
Цитировать
#62 Дарья Лакалина 16.08.2018 15:31
Алексей, спасибо за Ваши ответы, у Вас нет какого-нибудь файла с примером такого расчета (метод, который Вы описали ниже), так как я в теме прогнозирования совсем новичок и это мое первое задание в прогнозировании , хотелось бы посмотреть на пример.
Цитировать
#61 Алексей Батурин 16.08.2018 15:16
Цитирую Дарья Лакалина:
Алексей, в моем случае подходит вариант:
1. Если данных для прогноза по дням не достаточно, то прогнозируем по месяцам, распределяем по дням, собираем в недели.

Как можно правильно сделать распределение по дням, оно неравномерно, что вызывает большие изменения в понедельных значениях.


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

С учетом этих долей распределить месячный прогноз по дням, а затем дни собрать в недели.
Цитировать
#60 Дарья Лакалина 16.08.2018 13:14
Алексей, в моем случае подходит вариант:
1. Если данных для прогноза по дням не достаточно, то прогнозируем по месяцам, распределяем по дням, собираем в недели.

Как можно правильно сделать распределение по дням, оно неравномерно, что вызывает большие изменения в понедельных значениях.
Цитировать
#59 Алексей Батурин 16.08.2018 07:21
Цитирую Дарья Лакалина:
Добрый вечер!

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


Дарья, доброе утро.

Подход к понедельному прогнозированию отличается. В зависимости от объем данных:

1. Если данных для прогноза по дням не достаточно, то прогнозируем по месяцам, распределяем по дням, собираем в недели.

2. Если данных для прогноза по дням достаточно, то прогнозируем по дням, прогноз по дням собираем в недели.
В этой статье не написано о прогнозировании по дням. Т.к. для прогнозирования по дням необходимо учитывать вложенную сезонность. Планируем об этом написать статью.
Цитировать
#58 Дарья Лакалина 15.08.2018 20:34
Добрый вечер!

Подскажите, чем будет отличаться подход к понедельному прогнозированию ?
Цитировать
#57 Алексей Батурин 18.05.2018 15:49
Цитирую Роман:
Если строить прогноз по ежемесячным данным за последние три года, то получается вполне приемлемый результат. Если трехлетнюю историю разбить на ежедневные данные, и построить дневные прогнозы на несколько месяцев вперед, то точность прогнозирования скачет до 60%, что не есть хорошо.

Роман, добрый вечер.

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

Планируем в ближайшем будущем об этом рассказать и написать статью.

Спасибо за комментарий.
Цитировать

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