Как рассчитать прогноз продаж с учетом роста и сезонности в 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% и выше.
Зарегистрируйтесь и скачайте решения
Статья полезная? Поделитесь с друзьями