Рост точности прогноза = рост оборотных средств

Рост точности прогноза - это точка роста оборотных средств при неизменном объеме. Чем меньше ошибка прогноза, тем меньше денег необходимо на обслуживание модели прогноза. 

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

В данной статье мы рассмотрим:

• Как рассчитать среднюю абсолютную ошибку прогноза и выбрать модель, которая дает наименьшую ошибку;
• Сравним модели и оценим, сколько оборотных средств мы можем сохранить за год, если будем использовать модель, которая дает минимальную ошибку прогноза.

По ходу статьи мы разберем

• Что такое ошибка прогноза;
• Как рассчитывается среднее абсолютное отклонение;

и рассчитаем:

• Прогноз с помощью модели "Скользящей средней к 4-м месяцам с аддитивной сезонностью";
• Прогноз с помощью модели "Логарифмический тренд с сезонностью";
• Ошибку прогноза для каждой модели;
• Среднее абсолютное отклонение и для каждой модели.

А также сравним модели, опираясь на среднее абсолютное отклонение и оценим экономию оборотных средств за счет использования более точной модели прогнозирования.

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

---

Что такое ошибка прогноза?

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

Чем меньше ошибка прогноза, тем более точные решения мы приминаем в закупках, производстве, планировании … а следовательно более эффективно распределяем оборотные средства и повышаем оборачиваемость товаров. 

Существует несколько методов оценки ошибок. Большинство этих методов состоит в усреднении некоторых функций от разностей между действительными значениями и их прогнозами.

Ошибку прогноза  (et) для каждого момента времени во временном ряду мы можем вычистить по формуле:

et = Yt  - Y^t  ,

где

• Yt - действительное значение временного ряда в момент t - в наших примерах объем продаж,
•  Y^t - прогноз значения Yt - в наших примерах прогноз объема продаж.

Ошибка MAD - среднее абсолютное отклонение

Среднее абсолютное отклонение (MAD) измеряет точность прогноза, усредняя величины ошибок прогноза (абсолютные значения каждой ошибки). Чаще всего MAD используют, когда ошибку прогноза необходимо измерить в тех же единицах, что и исходные значения временного ряда.

Формула вычисления ошибки: 

• Yt - действительное значение временного ряда в момент t,
• Y^t - прогноз значения Yt,
• n - номера периодов

Среднее абсолютное отклонение - средняя ошибка (разность между фактом продаж и прогнозом продаж) по модулю.

Рассчитаем прогноз и оценим следующие модели:

1. Скользящей средней к 4-м месяцам с аддитивной сезонностью;
2. Логарифмический тренд с сезонностью.

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

Для оценки ошибки модели "Скользящей средней к 4-м месяцам с аддитивной сезонностью" рассчитаем:

1. Скользящую среднюю к 4-м месяцам;
2. Разность между значениями ряда и средними значениями к 4-м месяцам (пункт 1);
3. Усредним разность ряда и средней для каждого месяца получим сезонность в абсолютной величание - аддитивную сезонность;
4. Продлим значения ряда с помощью скользящей средней к 4-м месяцам и скорректируем её аддитивной сезонностью;
5. Модель прогноза для каждого момента времени t;
6. Ошибку прогноза;
7. Среднее абсолютное отклонение.

1. Скользящую среднюю к 4-м месяцам для каждого момента времени во временном ряду начиная с 5-го периода:

2. Разность между значениями ряда и средними значениями к 4-м месяцам для каждого момента времени t (пункт 1):

3. Усредним разность ряда и средней для каждого месяца получим сезонность в абсолютной величание - аддитивную сезонность.

Для этого вначале выделим номера месяцев с помощью функции Excel =месяц(дата). Для этого проверяем являются ли наши даты "январь 2010 г.", датой, если нет, то переводим в дату и используя функцию Excel =месяц(дата), получаем номера месяцев:

Получаем ряд с пронумерованными месяцами:

Далее усредняем отклонения ряда от средней для каждого месяца, получаем 12 значений аддитивной сезонности.

Для этого используем формулы Excel:

СУММЕСЛИ($D$7:$AY$7 (диапазон с номерами месяцев);D7 (номер месяца, для которого мы рассчитываем сезонность);$D$6:$AY$6 (разность между рядом и средней))

СЧЁТЕСЛИ($D$7:$AY$7(диапазон с номерами месяцев);D7(номер месяца, для которого мы рассчитываем сезонность))

Обязательно фиксируем ссылки на диапазоны с "Номерами месяцев" и "разность между рядом и средней". Подробнее об этом в статье " Как зафиксировать ссылку в Excel"

Подробнее о формулах Excel СУММЕСЛИ и СЧЁТЕСЛИ читайте с статье "Формулы Excel "СУММЕСЛИ" и "СЧЕТЕСЛИ" при расчете сезонности"

Протянули формулу на 12 месяцев, получили аддитивную сезонность для каждого месяца:

 

4. Продлим значения ряда с помощью скользящей средней к 4-м месяцам и скорректируем её аддитивной сезонностью. 

Скорректируем скользящую рассчитанной аддитивной сезонностью.

Для этого к прогнозному среднему прибавим аддитивную сезонность. Сезонность для каждого месяца подтянем с помощью функции Excel ГПР.

Подробнее об этом читайте с статье "ГПР в Excel на примере скользящей средней".

Прогноз = средние продажи за последние 4 месяца + сезонность:

=СРЗНАЧ(AV4:AY4(средние продажи за 4 последних месяца))+ГПР(AZ3 (искомый номер месяца);$D$8:$O$9 (зафиксированная ссылка на таблицу с сезонностью);2 (номер строки);0)

5. Рассчитаем модель прогноза для каждого момента времени t.

К скользящей средней прибавим аддитивную сезонность начиная с 5 периода:

6. Рассчитаем значение ошибки для каждого месяца.

Для этого из объема продаж вычтем значение прогнозной модели:

7. Определим среднее абсолютное отклонение.

Для каждого момента времени t рассчитаем ошибку по модулю с помощью формулы Excel =ABS(H11 (ссылка на ошибку)):

 

Среднее абсолютное отклонение равно средней ошибке по модулю:

Среднее абсолютное отклонение для модели скользящей средней к 4-м месяцам с аддитивной сезонностью у нас равно 55 475

Теперь рассчитаем прогноз с помощью "Логарифмического тренда с сезонностью".

1. Выделим логарифмический тренд;
2. Рассчитаем сезонность;
3. Рассчитаем значение модели;
4. Рассчитаем ошибку прогноза и Среднее абсолютное отклонение.

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

1. Выделим логарифмический тренд.

О всех возможных способах выделения логарифмического тренда в Excel вы можете узнать в нашей статье "5 способов расчета логарифмического тренда в Excel. + О логарифмическом тренде и его применении".

Рассчитаем значения тренда с помощью функции =ПРЕДСКАЗ(LN(D2(номер периода));$D$4:$AY$4 (зафиксированная ссылка на диапазон с объемами продаж);LN($D$2:$AY$2 (зафиксированная ссылка на диапазон с номерами периодов)))

2. Рассчитываем отклонения объемов продаж от тренда (объем продаж делим на значения тренда):

3. Определяем сезонность с помощью формул Excel =СУММЕСЛИ() и =СЧЁТЕСЛИ()

Подробнее о формулах Excel СУММЕСЛИ и СЧЁТЕСЛИ читайте с статье "Формулы Excel "СУММЕСЛИ" и "СЧЕТЕСЛИ" при расчете сезонности":

Т.к. полученная сезонность в среднем равна 1, то нормирующий коэффициент вводить не нужно, и среднее отклонение у нас будет равно сезонности по месяцам.

4. Определим значения модели прогноза для каждого момента времени t, для этого значения тренда умножим на сезонность. Сезонность подтянем с помощью функции ГПР (см. статью "Функция ГПР в Excel"):

5. Определим ошибку прогноза для каждого момента времени t. Для этого из объема продаж вычтем значение модели прогноза для каждого момента времени t:

6. Рассчитаем ошибку по модулю с помощью функции =ABS(D21"ссылка на ошибку"):

7. Получим среднее абсолютное отклонение по модулю - среднее значение ошибки по модулю:

Среднее абсолютное отклонение для модели "Логарифмического тренда с сезонностью" у нас равно 70 412

Оценим эффективность использования в рамках года одной модели относительно другой.

Среднее абсолютное отклонение для модели

• "Логарифмического тренда с сезонностью" = 70 412 руб.
• "Скользящей средней к 4-м месяцам с аддитивной сезонностью" = 55 475 руб.

Итак модель скользящей средней делает более точный прогноз по сравнение с логарифмическим трендом для этого ряда в месяц на 14 937 руб. = 70 412 руб. — 55 475 руб.

В результате для нас это означает экономию оборотных средств на 14 937 руб. в месяц на обслуживание модели и 179 242 руб. в год, т.е. 14 937 руб. в месяц = 14 937 руб. * 12 месяцев =179 242 руб.

Т.е. в год мы получаем дополнительные оборотные средства в размере 179 242 руб.

Вот так вот за счет оценки точности прогноза и использования модели, которая дает меньшую ошибку прогноза, вы получаете дополнительные оборотные средства -  179 242 руб. в год.

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

---

Коллеги, эти 2 модели я выбрал наугад, давайте теперь оценим модель, которую автоматически подберет Forecast4AC PRO. И оценим, какой эффект в год нам это даст.

В настройках программы во вкладке "Доп. возможности" ставим галочку "MAD - Среднее абсолютное отклонение" и отключаем модели экспоненциального сглаживания (т.к. они дают ошибку для данного ряда больше чем скользящая средняя и трендовые модели):

Сохраняем и рассчитываем прогноз с помощью Forecast4AC PRO с автоматическим выбором модели.

Автоматически программа выбрала модель Средняя за 2 предыдущих периода + Сезонность относительно средней в абсолютной величине (т.е. аддитивная сезонность). Среднее абсолютное отклонение для этой модели у нас получилось равным 39 882 руб.

Экономия оборотных средств модели Forecast4AC PRO относительно модели скользящей к 4-м месяцам в год руб.:	187 115 руб.
Экономия оборотных средств модели Forecast4AC PRO относительно модели Логарифмический тренд с сезонностью в год руб.:	366 357 руб.

Оцените точность моделей прогнозирования, которые вы используете сейчас, рассчитайте проноз с помощью Forecast4AC PRO и оцените сумму оборотных средств, которую вы можете сэкономить за счет использования нашей программы.

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

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