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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• реклама,

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

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

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

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

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

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

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

Novo Forecast Enterprise – цифровая платформа №1 в России для интегрированного бизнес-планирования и планирования цепей поставок.

Записаться на демонстрацию системы

Краткий обзор модулей NF Ent:

• DFM - Точность прогноза ↑ на 20–30% выше, на 5-20% выше рынка
• CP - Совместное планирование - скорость согласования часы вместо дней - оценка рисков и прозрачность процессов
• S&OP - Учет ограничений и согласование планов — дни вместо недель
• S&OE - Реакция на изменения — ежедневная
• SCM - Сквозная цепочка поставок - снижение неликвидов ↓ до 70% - увеличение оборачиваемости рабочего капитала 10-20%
• PP - Оптимальная загрузка ↑ до 100% - учет ограничений, выравнивание планов и накопления

Внедряйте систему интегрированного бизнес-планирования для всех подразделений компании - познакомьтесь с Novo Forecast Enterprise