Как рассчитать прогноз продаж с учетом роста и сезонности в Excel?
Из данной статьи вы узнаете, как в Excel рассчитать прогноз продаж с учетом роста и сезонности.
Прогнозируя продажи подобным образом, вы получите максимально точный и обоснованный прогноз на длительный промежуток времени.
Процесс расчета прогноза разделим на 3 части:
- Расчет значение тренда;
- Определение коэффициентов сезонности;
- Прогнозирование продаж;
Рассчитаем прогноз по месяцам на 2 года и 3 месяца на основании продаж за 5 лет (см. вложенный файл).
Для расчета значений тренда:
- Определим коэффициенты уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн(). Для этого в ячейки Excel вводим функцию =линейн(объёмы продаж за 5 лет; номера периодов; 1;0).
Выделяем 2 ячейки, в левой - формула =линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2 + Ctrl+Shift+Enter). Excel рассчитает для нас значение коэффициентов a и b. - Рассчитываем значения тренда. Для этого в уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y-значения линейного тренда для каждого периода (см. вложенный файл).
Для расчета коэффициентов сезонности:
- Рассчитываем отклонение фактических значений от значений тренда. Для этого фактические значения делим на значения тренда;
- Для каждого месяца определяем среднее отклонение за последние 5 лет.
- Определяем общий индекс сезонности — среднее значение коэффициентов, рассчитанных в 4 пункте;
- Рассчитываем коэффициенты сезонности; каждый коэффициент из пункта 4 делим на коэффициент из пункта 5 (см. вложенный файл);
Рассчитываем прогноз продаж с учетом роста и сезонности:
- Задаём период, на который мы хотим рассчитать прогноз. Для этого продлеваем номера периодов временного ряда на 2 года и 3 месяца.
- Рассчитываем значения трена для будущих периодов. В уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде (от 61 до 87). Получаем y-значения линейного тренда для каждого будущего периода (см. вложенный файл).
- Рассчитываем прогноз. Для этого значения линейного тренда умножаем на коэффициенты сезонности.
Прогноз с учетом роста и сезонности готов.
Вы также можете руками корректировать прогноз, изменяя коэффициенты a и b линейного тренда y=bx+a, об этом подробно вы можете почитать в статье «О линейном тренде».
Для более точного прогнозирования продаж не достаточно учитывать рост и сезонность, необходимо также учесть еще дополнительные факторы, которые значительно влияют на объем продаж, такие как
-
реклама,
-
мероприятия по стимулированию сбыта,
-
ввод новых продуктов,
-
открытие новых направлений продаж,
-
спец. клиенты с разовыми значительными закупками
и т.д., но об этом в следующих статьях.
Точных вам прогнозов!
С помощью программы Forecast4AC PRO вы сможете рассчитывать прогноз с учетом роста и сезонности для более, чем 5000 строк одновременно одним нажатием клавиши. Легко и быстро!
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite - автоматический расчет прогноза в Excel.
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Зарегистрируйтесь и скачайте решения
Статья полезная? Поделитесь с друзьями
Комментарии
Дарья, добрый день.
Если данные за 1 день, то это "Новинка", а новинки нужно прогнозировать по аналогу или планировать кол-во клиентов, ср. заказ и исходя их этого продажи.
Если каждый год корреляция не проглядывается, то нужно разбираться.
Месяца в этом и прошлом году не похожи?
У меня к Вам есть еще вопрос. Каким, на Ваш взгляд, лучше пользоваться методом прогнозирования , если есть данные только за один день. И что делать в случаях, если проглядывается малая корреляция между данными за два года (основные всплески и спады приходятся на разные месяцы).
Дарья, такое распределение мы обычно настраиваем в базе данных или в аналитической системе, в Excel не просто его собрать.
Надеюсь в примере разберетесь. Синим выделил результат.
https://yadi.sk/i/E-QWNvle3aJ4pa
Дарья, т.к. задача - прогноз по неделям, то достаточно определить долю дня в зависимости от того в какую часть месяца он попадает (обычно месяц делят на 3 части по 10 дней).
Распределение нужно сделать для каждого месяца.
С учетом этих долей распределить месячный прогноз по дням, а затем дни собрать в недели.
1. Если данных для прогноза по дням не достаточно, то прогнозируем по месяцам, распределяем по дням, собираем в недели.
Как можно правильно сделать распределение по дням, оно неравномерно, что вызывает большие изменения в понедельных значениях.
Дарья, доброе утро.
Подход к понедельному прогнозированию отличается. В зависимости от объем данных:
1. Если данных для прогноза по дням не достаточно, то прогнозируем по месяцам, распределяем по дням, собираем в недели.
2. Если данных для прогноза по дням достаточно, то прогнозируем по дням, прогноз по дням собираем в недели.
В этой статье не написано о прогнозировании по дням. Т.к. для прогнозирования по дням необходимо учитывать вложенную сезонность. Планируем об этом написать статью.
Подскажите, чем будет отличаться подход к понедельному прогнозированию ?
Роман, добрый вечер.
Для расчета по дням нужно использовать вложенную сезонность и немного другие подходы с трендами.
Планируем в ближайшем будущем об этом рассказать и написать статью.
Спасибо за комментарий.
RSS лента комментариев этой записи