Как повысить точность прогноза? Модель прогноза продаж с точностью 95% на примере...

От клиента сети автозаправок поступил запрос, разработать модель прогноза продаж по автомойкам (при заправках) с отклонением в 5% прогноза от факта.

Расчет прогноза по объему продаж за предыдущие периоды с помощью Forecast4AC PRO с автоматическим подбором модели дал точность в 15%. Это не удовлетворяло запросу и мы сделали следующее.

Мы предложили разбить ряд по объему продаж на 3 ряда:

1. Количество машин;
2. Средний чек;
3. Объем продаж;

И рассчитать прогноз объема продаж по следующей формуле:

Прогноз по объему продаж = прогноз по количеству машин * прогноз по среднему чеку

И сделать следующее:

1. Подготовить данные к прогнозу;
2. Рассчитать на подготовленных данных прогноз по количеству машин + прогноз среднему чеку = прогноз объема продаж;
3. И следовать общим рекомендациям!

Пройдемся по каждому этапу подробнее:

1. Подготовка данных к прогнозу.

Скачайте файл с примером в Excel

Каждая из автомоек были несколько месяцев на реконструкции. В результате мы видим провалы в продажах в предыдущих периодах. Для расчета прогноза это не есть хорошо, т.к. нулевые продажи будут негативно влиять на точность прогноза (выделение сезонности и тренда), а также на тестирование моделей прогноза. Подготовим данные к прогнозу и рассчитаем прогноз на периоды реконструкций. Готовим ряд "кол-во машин" и "Объём продаж".

1. Удаляем кол-во машин и объемы продаж за неполные месяцы продаж перед реконструкцией и после:

См. лист "Исходные данные". 

2. На период когда проводилась реконструкция, рассчитываем прогноз "объёма продаж" и "кол-ва машин".

Транспонируем данные по "Объему продаж" и "кол-ва машин" на новый лист "Подготовка".

Прочитать статью "3 способа транспонирования данных в Excel"

Готовим данные, рассчитываем прогноз на период реконструкции:

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

Скачайте файл с примером в Excel

Используя Forecast4AC PRO (скачать Forecast4AC PRO), рассчитываем прогноз на период  реконструкции, используя автоматический подбор модели:

 

Устанавливаем курсор мыши в ячейку, с которой хотим начать расчет - начало нашего ряда с реконструкцией, вводим кол-во месяцев, на которое необходим прогноз, для 27 ряда - 6 месяцев, нажимаем кнопку на панели Excel "Рассчитать":

Получившиеся прогнозы переносим в периоды, когда была реконструкция:

Аналогичные действия проделываем со всеми рядами:

1. Копируем данные ряда до реконструкции на отдельный лист или область листа;
2. Рассчитываем прогноз на период, когда была реконструкция, используя автоматический выбор модели Forecast4AC PRO.
3. Готовим ряд, вставляем получившиеся прогноз на период реконструкции:

Для ряда №30 пересчитываем прогноз на период реконструкции руками, т.к. ряд не полный и программа подобрала модели, которые не учитывают сезонность (а мы точно знаем, что она есть, и учесть её важно!).

Для этого суммируем уже подготовленные ряды "объем продаж" и "кол-во машин":

Копируем ряды итого "Кол-во машин" и "Объем продаж" на новый лист или чистую область:

Рассчитываем коэффициенты сезонности к 3-м месяцам используя Forecast4AC PRO или изучаем статью  "Расчет прогноза продаж по методу скользящей средней в Excel" и рассчитываем руками.

Как рассчитать с помощью программы описано здесь: http://www.4analytics.ru/kak-rasschitat/koefficienti-sezonnosti.html

Важно! В Forecast4AC PRO в настройках сезонности выбираем "к 3-м предыдущим периодам" - для рассчета коэффициентов сезонности для модели прогноза скользящей средней к 3-м периодам:

Прочитать о том, для чего используются коэффициенты сезонности к 2-м, 3-м, 4-м месяцам.

Устанавливаем курсор в начало данных, жмем кнопку на панели Excel "Рассчитать":

 

Получаем 2 ряда с сезонностью "к 3-м месяцам" для "Кол-ва машин" и "Объема продаж".

Важно! Т.к. ряд у нас закончился маем, то сезонность в продолжении ряда будет соответствовать следующим месяцам (июню, июлю…):

 

Рассчитываем прогноз на период реконструкции для ряда №30. Для этого ср. кол-во машин умножаем на коэффициент сезонности к 3-м месяцам кол-ва машин, аналогично и с объемом продаж:

Подготовка данных "Кол-во машин" и "Объем продаж" завершена.

Копируем подготовленные данные на отдельные листы "Кол-во машин для прогноза" и "Объем продаж для прогноза".

• Лист с подготовленными рядами количеством машин "Кол-во для прогноза";
• Лист с подготовленным объемом продаж  "Объем для прогноза".

Из подготовленных рядов "Кол-во машин" и "Объём продаж" получаем ряд "Средний чек" - Средний чек = Объем продаж / количество машин:

Для расчета прогноза ряды готовы!

2. Рассчитываем прогноз!

2.1. Рассчитываем прогноз по кол-ву машин;

2.2. Рассчитываем прогноз по среднему чеку;

2.3. Получаем прогноз по объему продаж.

2.1. Рассчитываем прогноз по кол-ву машин.

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

1. Рассчитываем сезонность.

Суммируем количество машин для каждого месяца:

По сумме кол-ва машин рассчитываем сезонность к 2-м месяцам на 12 месяцев вперед с помощью Forecast4AC PRO:

2. Рассчитываем прогноз. Ср. кол-во машин за 2 месяца умножаем на коэф-т сезонности к 2-м месяцам рассчитанный по сумме машин:

2.2. Рассчитываем прогноз по среднему чеку.

Рассчитаем прогноз по среднему чеку. Для этого используем автоматический подбор модели в Forecast4AC PRO. Устанавливаем курсор в начало данных, выбираем в настройках "Прогноз", на 12 периодов, "Автоматический выбор модели" и нажимаем "Рассчитать":

Прогноз по среднему чеку рассчитан:

2.3. Получаем прогноз по объему продаж.

Прогноз объема продаж = прогноз по кол-ву машин *прогноз среднего чека – Лист "Объём для прогноза":

Скачайте файл с данными в Excel

3. Важные рекомендации!

Обновляем прогноз при появление новых данных:

1. Меняем прогнозные значения на фактические (количество машин, объем продаж, средний чек);
2. Обновляем коэффициенты сезонности для кол-ва машин;
3. Пересчитываем прогноз с учетом продаж за последний месяц (2-й раздел).

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

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

Если остались вопросы, пожалуйста, обращайтесь, буду рад помочь.

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