Главная    Интернет-библиотека    Маркетинг    Маркетинговый инструментарий    Алгоритм прогнозирования объема продаж в MS Excel

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

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

Опубликовано в журнале "Маркетинг в России и за рубежом" №5 год - 2001

Кошечкин С.А.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Т — тренд;

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

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

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

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

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

Fм t — значение модели;

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

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

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

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

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

№п.п.

Месяц

Объем продаж (руб.)

№п.п.

Месяц

Объем продаж (руб.)

1

Июль

8174,4

13

Июль

8991,84

2

Август

5078,33

14

Август

5586,16

3

Сентябрь

4507,2

15

Сентябрь

4957,92

4

Октябрь

2257,19

16

Октябрь

2482,91

5

Ноябрь

3400,69

17

Ноябрь

3740,76

6

Декабрь

2968,71

18

Декабрь

3265,58

7

Январь

2147,14

19

Январь

2361,85

8

Февраль

1325,56

20

Февраль

1458,12

9

Март

2290,95

21

Март

2520,05

10

Апрель

2953,34

22

Апрель

3248,67

11

Май

4216,28

23

Май

4637,91

12

Июнь

8227,569

24

Июнь

9050,3264

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

Месяцы

Объем продаж

Значение тренда

Сезонная компонента

1

8174,4

7617,2674

557,1326

2

5078,3296

6104,0156

–1025,686

3

4507,2061

4420,3206

86,885473

4

2257,1992

3004,1224

–746,92323

5

3400,6974

2086,745

1313,95235

6

2968,7178

1741,0644

1227,65338

7

2147,1426

1924,9246

222,217979

8

1325,5674

2519,8016

–1194,2342

9

2290,9561

3364,7154

–1073,7593

10

2953,3411

4285,39

–1332,0489

11

4216,2848

5118,6614

–902,37664

12

8227,5695

5732,1336

2495,43589

13

8991,84

7617,2674

1374,5726

14

5586,1626

6104,0156

–517,85304

15

4957,9267

4420,3206

537,60608

16

2482,9191

3004,1224

–521,20332

17

3740,7671

2086,745

1654,02209

18

3265,5896

1741,0644

1524,52515

19

2361,8568

1924,9246

436,932237

20

1458,1241

2519,8016

–1061,6775

21

2520,0517

3364,7154

–844,6637

22

3248,6752

4285,39

–1036,7148

23

4637,9132

5118,6614

–480,74817

24

9050,3264

5732,1336

3318,19284

 

Открыть таблицу 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 с.

Также по этой теме: