Алгоритм прогнозирования объема продаж в MS Excel

к.э.н., старший преподаватель кафедры экономики
предпринимательства МИЭПМ ННГАСУ

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

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

Аддитивную модель прогнозирования можно представить в виде формулы

где F — прогнозируемое значение; Т — тренд; S — сезонная компонента; Е — ошибка прогноза.

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

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

Открыть рисунок 1 "Аддитивная и мультипликативная модели прогнозирования" >>>

Алгоритм построения прогнозной модели

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

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

2. Вычитая из фактических значений объемов продаж значения тренда, определяют величины сезонной компоненты и корректируют их таким образом, чтобы их сумма была равна нулю.

3. Рассчитываются ошибки модели как разности между фактическими значениями и значениями модели.

4. Строится модель прогнозирования:

где F — прогнозируемое значение;

Т — тренд;

S — сезонная компонента;

Е — ошибка модели.

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

где F_{пр t} — прогнозное значение объема продаж;

F_{ф t–1} — фактическое значение объема продаж в предыдущем году;

F_{м t} — значение модели;

а — константа сглаживания.

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

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

Применение алгоритма рассмотрим на следующем примере.

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

Открыть таблицу 1 "Фактические объемы реализации продукции" >>>

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

Реализуем алгоритм построения прогнозной модели, описанный выше. Решение данной задачи рекомендуется осуществлять в среде MS Excel, что позволит существенно сократить количество расчетов и время построения модели.

1. Определяем тренд, наилучшим образом аппроксимирующий фактические данные. Для этого рекомендуется использовать полиномиальный тренд, что позволяет сократить ошибку прогнозной модели).

Открыть рисунок 2 "Сравнительный анализ полиномиального и линейного трендов" >>>

На рисунке 2 показано, что полиномиальный тренд аппроксимирует фактические данные гораздо лучше, чем предлагаемый обычно в литературе линейный. Коэффициент детерминации полиномиального тренда (0,7435) гораздо выше, чем линейного (4E-05). Для расчета тренда рекомендуется использовать опцию «Линия тренда» ППП Excel (рис. 3).

Рис. 3. Опция «Линии тренда»

Применение других типов тренда (логарифмический, степенной, экспоненциальный, скользящее среднее) также не дает такого эффективного результата. Они неудовлетворительно аппроксимируют фактические значения, коэффициенты их детерминации ничтожно малы:

— логарифмический: R² = 0,0166;

— степенной: R² = 0,0197;

— экспоненциальный: R² = 8Е-05.

2. Вычитая из фактических значений объемов продаж значения тренда, определим величины сезонной компоненты, используя при этом пакет прикладных программ MS Excel (рис. 4).

Открыть рисунок 4 "Расчет значений сезонной компоненты в ППП MS Excel" >>>

Рассчитываем значения сезонной компоненты (табл. 2).

Открыть таблицу 2 "Расчет значений сезонной компоненты" >>>

Скорректируем значения сезонной компоненты таким образом, чтобы их сумма была равна нулю (табл. 3).

Открыть таблицу 3 "Расчет средних значений сезонной компоненты" >>>

3. Рассчитываем ошибки модели как разности между фактическими значениями и значениями модели (табл. 4).

Открыть таблицу 4 "Расчет ошибок" >>>

Находим среднеквадратическую ошибку модели (Е) по формуле

где Т — трендовое значение объема продаж;

S — сезонная компонента;

О — отклонения модели от фактических значений.

Е = 0,003739, или 0,37%

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

4. Построим модель прогнозирования:

F = T + S ± E.

Построенная модель представлена графически на рисунке 5.

Открыть рисунок 5 "Модель прогноза объема продаж" >>>

5. На основе модели строим окончательный прогноз объема продаж. Для смягчения влияния прошлых тенденций на достоверность прогнозной модели предлагается сочетать трендовый анализ с экспоненциальным сглаживанием. Это позволит нивелировать недостаток адаптивных моделей, т.е. учесть наметившиеся новые экономические тенденции:

F_{пр t} — прогнозное значение объема продаж;

F_{ф t -1} — фактическое значение объема продаж в предыдущем году;

F_{м t} — значение модели;

а — константа сглаживания.

Константу сглаживания рекомендуется определять методом экспертных оценок, как вероятность сохранения существующей рыночной конъюнктуры, т.е. если основные характеристики изменяются/колеблются с той же скоростью/амплитудой, что и прежде, значит, предпосылок к изменению рыночной конъюнктуры нет и, следовательно а ® 1; если наоборот, то а ® 0.

Таким образом, прогноз на январь третьего сезона определяется следующим образом.

Определяем прогнозное значение модели:

Фактическое значение объема продаж в предыдущем году (F_{ф t-1} ) составило 2361 руб. Принимаем коэффициент сглаживания равным 0,8. Получим прогнозное значение объёма продаж:

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

Литература

1. Голубков Е.П. Маркетинговые исследования: теория, методология и практика. — М.: Издательство «Финпресс», 1998. — 416 с.

2. Карлберг, Конрад. Бизнес-анализ с помощью Excel / Пер. с англ. — К.: Диалектика, 1997. — 448 с.

3. Лукасевич И.Я. Анализ финансовых операций. Методы, модели, техника вычислений. — М.: Финансы, ЮНИТИ, 1998. — 400 с.

4. Эддоус М., Стэнсфилд Р. Методы принятия решений / Пер. с англ.; под ред. член-корр. РАН И.И. Елесеевой. — М.: Аудит, ЮНИТИ, 1997. — 590 с.