Доработка алгоритма прогнозирования объёма продаж в MS Excel

Вторым разделом статьи будет использование доработанного алгоритма на примере, который предоставил Кошечкин С.А.

1. Определение тренда. Первым шагом в построении модели является выбор линии тренда. Автор утверждает, что выбор полиномиальной линии тренда дает наиболее точную модель, опираясь на коэффициент детерминации, как критерий оценки всей модели в целом. Однако он пропускает тот факт, что точность модели зависит не только от ошибок моделирования тренда, но и от ошибок моделирования сезонных колебаний. Другими словами, модель F=T+S+E (F — значения модели, T — значения линии тренда, S — значения сезонной компоненты, E — величина ошибок) зависит от двух ключевых параметров Т и S, а не только от Т, как утверждает автор. Параметр Е определяет доверительный интервал модели и дает возможность анализировать точность построенной модели.

Выбор наиболее точной линии тренда (Т) с высоким коэффициентом детерминации не является достаточным условием построения оптимальной модели. При росте коэффициента детерминации уменьшается ошибка тренда, но не модели в целом. Таким образом, автор отсекает альтернативные модели, утверждая, что они заранее менее точны, опираясь при этом на данные анализа одного параметра всей модели — тренда (T).

2. Определение величин сезонной компоненты. Необходимо учитывать также ошибки сезонных колебаний (S), которые характеризуются суммой средних величин сезонной компоненты. Чем дальше от 0 значение суммы колебаний сезонной компоненты, тем больше ошибка параметра S. Кстати говоря, автор сообщает о том, что перечень товаров, относящихся к сезонным достаточно велик, но не рассказывает о том, как определить относится ли товар, продаваемый предприятием, к сезонному.

Таким образом, выбирая линию тренда, характеризующую общую тенденцию развития изучаемого явления, необходимо также рассчитывать сезонную компоненту (S) и смотреть на сколько сильно сумма средних значений S отклоняется от 0. Если эта величина близка к 0, то можно утверждать, что продажи действительно имеют сезонный характер и товар, следовательно, можно называть сезонным.

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

Итак, если мы уже определили, что в модели существует сезонность (сумма значений S близка к 0), то период сезонности рассчитывается как средняя арифметическая между количеством отрицательных и положительных значений сезонной компоненты.

3. Расчет ошибок модели. Изучив поведение сезонной компоненты можно переходить на следующий этап моделирования — расчет ошибок построенной модели. Ошибки рассчитываются по формуле:

E=F-T-S,
при этом вместо значений F подставляются фактические значения объемов продаж.

После нахождения среднеквадратической ошибки модели мы можем делать вывод о точности модели в целом.

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

Ведь задача была поставлена в статье «составить прогноз продаж продукции на следующий год по месяцам». А результат, полученный после прогнозирования, характеризуется одним числом. Следовательно, задача, поставленная самим автором, не решена в полном объеме.

Существует также ряд неясностей в ходе дальнейшего прогнозирования:

1. Почему взяты данные за январь (F_{ф t-1} =2 361), тогда как оба исследуемых периода начинаются с июля.
2. Как и кем определяется константа сглаживания a. Ведь экспертом, работающим над данной проблемой, является сам автор. А, следовательно, необходим инструментарий определения данной величины.
3. Почему не описан инструментарий получения данных доверительного интервала (± 7,8 (руб.)).
4. Какие «все возможные сценарии прогноза» автор имеет в виду: те которые зависят от константы сглаживания, или те, которые определяются альтернативными моделями.

Таким образом, автором допущены ошибки использования собственного алгоритма. Эти ошибки позволяют сделать вывод о его несовершенстве или о недостаточной конкретизации самого алгоритма. При этом, следует учесть, что основная идея алгоритма, методики и последовательность действий, выбранные автором, абсолютно верны. Следовательно, доработки требует только алгоритм.

С учетом описанных выше недостатков, можно предположить, что алгоритм должен иметь такой вид:

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

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

С учетом проведенных изменений алгоритма попробует использовать его на примере, предоставленном Кошечкиным С.А.

ПРИМЕР.

Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого "Пломбир" одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.

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

Воспользуемся алгоритмом, описанным в таблице 1.

1. Построение модели.

Создадим модели, описывающие продажи мороженого.

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

1.1. Изначально, нам не известно какое из уравнений трендов даст наилучший результат, поэтому на данном этапе моделирования целесообразнее всего использовать все линии тренда, которые может строить программный продукт MS Excel:

• линейный тренд;
• логарифмический тренд;
• полиномиальный тренд (возьмем 6-ю степень, чтобы увидеть ошибки, допущенные ранее);
• степенной тренд;
• экспоненциальный тренд.

Для простоты и большей наглядности данного примера, а также отражения сути предложенного алгоритма ограничимся выбором трех линий тренда. Заранее отметим, что линии тренда выбраны случайным образом. Полиномиальный и линейный тренд выбраны, т.к. Кошечкин С.А. отдал предпочтение полиному, выбирая между линейным и полиномиальным трендом. И еще один тренд взят на личное усмотрение —логарифмический тренд.

По коэффициентам детерминации видно, что наиболее предпочтителен полином, а наименее — линейный тренд. Но т.к. коэффициент детерминации (R2) не определяет точность всей модели, то выбор тренда на этом этапе мы сделать не можем.

1.2. Уравнения линий тренда принимают вид, указанный на рисунке 1. Чтобы получить цифровые значения линий тренда за каждый месяц, необходимо внести уравнения, показанные на графике в ячейки MS Excel в виде формул, где Х (независимая компонента) — это последовательность чисел от 1 до 24 (см. рисунок 2). А Y — это значения уравнения линии тренда для каждого из Х.

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

Кстати говоря, значения полиномиального тренда, полученные при работе, совершенно не совпадают с данными, которые получил Кошечкин С.А. в своей статье. Если кто-нибудь сможет подсказать как он рассчитывал данные полиномиального тренда, буду очень признателен.

1.3. В случае, если осуществляется не тактический, а стратегический анализ, т.е. собраны данные хотя бы за 4 сезона, то сезонная компонента (S) может быть представлена отдельными уравнениями, что увеличит точность S. Не следует забывать, что это усложнит процесс моделирования. Но т.к. исходные данные имеются только за 2 периода, то выбор средних величин в расчете сезонных компонент является оптимальным.

1.4. Используя методику Кошечкина С.А., рассчитываем сезонную компоненту для каждого из уравнений тренда. Из фактических данных вычитаем значения линий тренда для каждого из сезонов. Имеем 3 таблицы (табл. 4-6).

В таблице 4 четко видно, что отклонение сезонных колебаний модели с полиномиальным трендом от 0 весьма велико и утверждать, что в модели выявлена сезонность, мы не можем. А если предполагать, что сезонность существует, исходя из экономических соображений и знаний специфики рынка и товара, то ошибка модели в итоге вырастет. Таким образом, высокая точность модели, полученная благодаря выбору полинома, будет нейтрализована низкой точностью сезонной компоненты. Чтобы проверить данное утверждение, построим полностью модель с полиномиальным трендом.

В таблице 5 по сумме средних величин видно, что наблюдается сезонность колебаний, т.к. сумма средних величин сезонных колебаний близка к 0.

Чтобы довести средние колебания до 0, необходимо итоговую сумму средних разделить на количество периодов в сезоне (в нашем случае — это 12). Полученный результат вычитаем из значений среднего по каждому периоду. В итоге — сумма колебаний составит абсолютный 0.

По данным таблицы 6 можно утверждать, что в модели с логарифмическим трендом также существуют сезонные колебания, т.к. сумма средних близка к 0.

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

1.5. Получив 3 сезонных компоненты (S) с 3 уравнениями тренда (T), мы можем рассчитать ошибки построенных моделей (E). Для этого из исходных значений задачи необходимо отнять сумму S+T, E=F-(S+T). Данные расчета представлены в таблице 7.

На основании рассчитанных ошибок (E) рассчитаем среднеквадратическое отклонение (СКО) для каждого из периодов (см. Таблицу 8). Формула расчета приведена в работе Кошечкина С.А.

1.6. Рассчитав среднее значение СКО, полученных для каждой модели, рассчитаем точность по формуле:

Точность модели с полиномиальным трендом = 95.05%
Точность модели с линейным трендом = 99.81%
Точность модели с логарифмическим трендом = 98.66%

Таким образом, высокой точностью обладают все 3 модели (см. рисунок 3).

Т.к. в случае, если точность модели колеблется в районе 90%-100%, то можно утверждать, что модель достаточно точная. Однако, модель с линейным трендом является наиболее точной, т.к. ее показатель точности наиболее высокий. Следовательно, прогноз, сделанный на основании данных линейной модели будет наиболее точным. И только на данном этапе моделирования мы можем сделать окончательный вывод о предпочтительности модели. Выбрав модель с линейным трендом, в дальнейшем, будем работать только с ней.

1.7. Чтобы построить доверительный интервал воспользуемся данными СКО для модели с линейным трендом (СКО=0,0019). Доверительный интервал примет вид:

Данные такого расчета приведены в таблице 9.

2. Построение прогноза.

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

2.1. Для расчета прогнозных значений в пакете MS Excel, укажем условия прогнозирования:

• трендовая компонента (Т) зависит от последовательности чисел от 1 до 24. Следовательно, чтобы построить прогноз, необходимо продолжить эту последовательность до 36. Значения трендовой компоненты MS Excel рассчитает в автоматическом режиме. Достаточно выделить последнюю ячейку 24-го месяца и зажав черный квадратик в нижнем правом углу ячейки протащить выделение до 36 периода. В итоге получим трендовую компоненту Т. (Сделайте аналогичную операцию с полиномом и увидите: почему книжки не рекомендуют использовать полиномы в прогнозировании — уже в апреле 3-го сезона наши продавцы мороженого станут !!! банкротом !!!)
• сезонная компонента (S) рассчитанная для модели, остается неизменной для 25-36 месяцев. Выделим в MS Excel сезонную компоненту и скопируем на периоды 25-36.
• Для учета ошибок воспользуемся доверительным интервалом модели, рассчитанным для прогнозных значений. Доверительный интервал отражает в каких пределах может колебаться ошибка прогнозных значений.

Если представить графически прогноз, рассчитанный с помощью выбранной модели, то результаты прогнозирования примут вид:

2.2. Определять константу сглаживания можно несколькими способами:

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

Возникла проблема в доведении примера до логического конца — отсутствие статистической информации о Российской Федерации и, тем более, о Нижнем Новгороде.

Так что, могу повествовать лишь на идейном уровне. Т.к. Кошечкин С.А. утверждает, что константа сглаживания — это «вероятность сохранения существующей рыночной конъюнктуры», то

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

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

Таким образом, мы пришли к выводу, что:

• при моделировании хозяйственного процесса ВСЕГДА необходимо строить несколько моделей, чтобы сравнить результаты;
• тактический и стратегический анализ сильно отличаются. И если при построении прогноза на 1 год можно воспользоваться средними величинами при определении сезонных колебаний, то при создании стратегического плана необходимы более точные модели, а значит — более сложные методы;
• полином использовать в моделировании хозяйственных процессов крайне рискованно, т.к. несмотря на высокую точность трендовой компоненты можно получить данные искаженные данные;
• определить точность модели можно только после расчета и тренда, и сезонной компоненты;
• константа сглаживания формируется на основании конкретных данных, а не на основании «экспертных оценок»;
• в рассмотренном примере выбор линейного тренда более предпочтителен, чем выбор полинома;
• необходим дополнительный анализ внешней среды для построения более адекватной модели (с использованием экспоненциального сглаживания).

Авторская справка:

Бондаренко А.В.
Специалист-аналитик по исследованию товарного рынка ООО "УБС"
Магистрант кафедры "Прикладная экономика" Донецкого Национального Университета (Украина)