XI Международная студенческая научная конференция Студенческий научный форум - 2019

Планирование и контроль рабочих процессов в логистике требуют точной оценки тех объемов продукции, с которыми будут выполняться соответствующие операции. Эта оценка обычно выполняется в форме прогнозов продаж или спроса. Прогнозирование спроса – важнейшая управленческая функция любой компании, которая занимается производством и продажами товаров и услуг [1,2,3]. Правильное прогнозирование – это основа успешного планирования и контроля всех основных функциональных подразделений компании – производства, логистики, маркетинга, финансов.

Общие методы прогнозирования можно разделить на четыре крупные группы [4,5,6]:

методы экспертных оценок;

методы экстраполяции трендов;

методы регрессионного анализа;

методы экономико-математического моделирования.

Методы экстраполяции трендов и методы регрессионногоанализаобъединяют в общее понятие “методы анализа временных рядов” [7,8].

Методы регрессионного анализа и метод экономико-математического моделирования вместе составляют понятие “методы анализа причинных связей”.

Методы экстраполяции трендов основаны на статистическом наблюдении динамики определенного показателя, определении тенденции (тренда) его развития и продолжении этой тенденции для будущего периода. Другими словами, при помощи методов экстраполяции трендов закономерности прошлого развития объекта переносятся в будущее [9,10].

Рассмотрим один из возможных алгоритмов построения прогноза объёма реализации для товаров с сезонным характером продаж. Сразу следует отметить, что перечень таких товаров гораздо шире, чем это кажется. Дело в том, что понятие “сезон” в прогнозировании применим к любым систематическим колебаниям, например, если речь идёт об изучении товарооборота в течение недели под термином “сезон” понимается один день. Кроме того, цикл колебаний может существенно отличаться (как в большую, так и в меньшую сторону) от величины один год. И если удаётся выявить величину цикла этих колебаний, то такой временной ряд можно использовать для прогнозирования с использованием аддитивных и мультипликативных моделей [11,12,13].

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

F = T + S + E,

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

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

F = T∙• S • E

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

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

Рассмотрим следующие исходные данные: объёмы реализации продукции за два сезона, представленные в таблице 6.

Таблица 1  Фактические объёмы реализации продукции

№ п.п.	Месяц	Выручка (тыс. руб.)	№ п.п.	Месяц	Выручка (тыс. руб.)
1	июль	8174,40	13	июль	8991,84
2	август	5078,33	14	август	5586,16
3	сентябрь	4507,20	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,56	24	июнь	9050,32

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

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

гдеY₁₂ – функция тренда, A₁₂ ‒ смещение синусоиды относительно нуля, B₁₂‒ амплитуда синусоиды, C₁₂ имеет смысл начальной фазы колебания, индекс 12 указывает на выбранный период изменения функции.

Итак, на рабочем листе (назовем его «Шаг 1») в ячейки А3:А26 запишем номера месяцев наблюдения от 1 до 24, а в ячейки В3:В26 ‒ наблюдаемые объемы продаж. Особенность надстройки «Поиск решения» состоит в том, что требуется задать начальные значения A₁₂, B₁₂ и C₁₂. Значение A₁₂ должно быть близко к среднему значению объема продаж за год; в качестве начального значения выберем 4000 и занесем это число в ячейку Н3. B₁₂ ‒ это амплитуда искомой синусоиды, ее начальное значение определим как « A₁₂ минус минимальное значение объема продаж». Подойдет самая грубая оценка: 3000; занесем это число в ячейку I3. Судя по графику, C₁₂ или начальная фаза приблизительно составляет – 1/4 периода, или – 3 месяца. Запишем – 3 в ячейку J3. В ячейку К3 поместим наш период: 12 (подбирать его мы не будем, но нам удобно, чтобы он фигурировал в этой ячейке). Затем необходимо уравнение нашей синусоиды записать в виде формулы MS Excel. Для этого в ячейку D3 заносим:

=$H$3+$I$3*SIN(2*ПИ()*(A3-$J$3)/$K$3) ,

где ПИ() ‒ это функция русифицированного MS Excel, которая возвращает константу 3,1415926….

Затем автозаполнением скопируем эту формулу в диапазон ячеек D4:D26. Теперь в ячейках Е3:Е26 вычислим отклонения нашей синусоиды от базовой линии прогноза: в Е3 запишем формулу: =B3–D3 , и скопируем ее автозаполнением в указанный диапазон. В ячейках F3:F26 необходимо рассчитать квадраты отклонений, для чего в F3 запишем: =E3*E3 и скопируем эту формулу ниже автозаполнением. В ячейке F27 подсчитаем сумму квадратов отклонений: =СУММ(F3:F26).

Затем, выделив ячейку F27 с подсчитанной суммой квадратов отклонений, в меню «Сервис» выбираем команду «Поиск решения…». В диалоговом окне в поле «Установить целевую ячейку» должно быть указано $F$27. В переключателе «равной» выбираем «минимальному значению», а в поле «Изменяя ячейки» указываем: $H$3:$J$3 . Скриншот этого диалога показан на рис. 2.

Рисунок 2  Диалоговое окно Поиск решения

Нажав кнопку «Выполнить», мы получаем в ячейках H3:J3 значения A₁₂ = 4160, B₁₂ = 2686 и C₁₂ = –2,03, наилучшим образом приближающие нашу синусоиду к базовой линии (рис. 13). Осталось в завершающем диалоге подтвердить сохранение найденного решения.

Рисунок 3  Выделение тренда с периодом 12 месяцев

Полезно вычислить квадрат коэффициента корреляции (коэффициент детерминации R²) полученного тренда с исходным временным рядом; в ячейку G27 введем формулу:

=КВПИРСОН(D3:D26;B3:B26) .

У меня получилось 0,696669. Хорошо это или плохо? Насколько значим полученный коэффициент детерминации?

Для оценки значимости коэффициента детерминации воспользуемся t-критерием Стьюдента. Обычно требуется определить фактическое значение критерия

и сравнить его с табличным значением t-критерия Стьюдента. Табличное значение критерия Стьюдента t_a(k) определяется с заданной доверительной вероятностью α и числом степеней свободы k = n – 1, где n – число наблюдений. Если t_r>t_a(k), то величину коэффициента корреляции следует признать существенной. Мы поступим иначе: определим минимальное значение R², при котором корреляцию можно считать существенной:

Запишем в ячейку Н27 число наблюдений исходного временного ряда: 24. В ячейке I27 с помощью формулы =СТЬЮДРАСПОБР(0,05;H27-1) найдем коэффициент Стьюдента для доверительной вероятности 0,95 и числа степеней свободы 23 (равен 2,06855). В ячейке J27 вычислим квадрат коэффициента Стьюдента формулой =I27*I27. И в ячейке К27 получим минимальное существенное значение коэффициента детерминации с помощью формулы =J27/(H27-2+J27). Итак, оказывается, что с вероятностью 0,95 корреляция будет существенна, если R² > 0,16284.

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

Анализ поведения отклонений позволяет сделать вывод, что в них тоже прослеживается периодичность, но уже с меньшим периодом − 6 месяцев.

Проделаем все то же самое, но для периода 6 месяцев, т.е. теперь отыщем линию тренда

Подберем коэффициенты A₆, B₆ и C₆, но вместо исходного временного ряда будем работать с разностями, полученными на первом шаге. Для этого:

Создайте копию листа «Шаг 1», переименуйте эту копию в «Шаг 2».

В ячейку В3 запишите формулу: ='Шаг 1'!E3 и автозаполнителем скопируйте ее в ячейки В4:В26; т.е. замените исходный ряд наблюдений разностями, полученными на листе «Шаг 1».

В ячейке Н3 укажите начальное значение для A₆; подойдет ноль.

В ячейке I3 укажите начальное значение для B₆; можно начать с 1000.

В ячейке К3 укажите период: 6.

В ячейке J3 укажите начальное значение для C₆; подойдет любое число в диапазоне от -3 до 3.

Выделите ячейку F27 и выполните «Поиск решения».

Результатом будет A₆ = 0,00127, B₆ = 1449 и C₆ = −1,099; коэффициент детерминации R² = 0,66827> 0,16284 (рис. 4). Таким образом, эту корреляцию также следует признать существенной.

Рисунок 4  Выделение тренда с периодом 6 месяцев

На рисунке 5 показаны графики базовой линии прогноза, суммы трендов 12 и 6 месяцев, отклонений суммы трендов от исходного временного ряда.

Рисунок 5  Графики объемов продаж и суммы трендов 12 и 6 месяцев

Анализ полученных отклонений показывает, что, во-первых, в них по-прежнему можно заметить периодичность с периодами 3 и 4 месяца. Во-вторых, в них просматривается тенденция постепенного роста.

Уже знакомым способом выделим в отклонениях колебания с периодом 3 месяца:

Для этого:

Создадим копию листа «Шаг 2», переименуем эту копию в «Шаг 3».

В ячейку В3 запишем формулу: ='Шаг 2'!E3 и автозаполнителем скопируем ее в ячейки В4:В26.

В ячейке Н3 укажем начальное значение для A₃; подойдет ноль.

В ячейке I3 укажем затравочное значение для B₃; можно начать с 1000.

В ячейке К3 укажем период: 3.

В ячейке J3 укажем затравочное значение для C₆: 0.

Выделим ячейку F27 и выполним «Поиск решения».

Результатом поиска будут значенияA₃=0, B₃=608 и C₃= – 0,566; коэффициент детерминации R²=0,354638> 0,16284. Таким образом, и эту корреляцию так же следует признать существенной.

Теперь аппроксимируем составляющую с периодом 4 месяца:

Создадим копию листа «Шаг 3», переименуем эту копию в «Шаг 4».

В ячейку В3 запишем формулу: ='Шаг 3'!E3 и автозаполнителем скопируем ее в ячейки В4:В26.

В ячейке Н3 укажем начальное значение для A₄: − 0.

В ячейке I3 укажем начальное значение для B₄: − 500.

В ячейке К3 укажем период: − 4.

В ячейке J3 укажем начальное значение для C₄: − 0.

Выделим ячейку F27 и выполним «Поиск решения».

Найденные значения A₄ = 0, B₄ = 543 и C₄ =−0,168; коэффициент детерминации R² = 0,438435> 0,16284. Корреляция существенна.

Суммируем все полученные синусоидальные тренды. На рис. 6 показаны графики исходного временного ряда, суммы всех выделенных периодических трендов и отклонений суммы трендов от исходного временного ряда.

Рисунок 6  Графики объема продаж и суммы всех периодических трендов

Больше статистически значимых периодических трендов в наших наблюдениях выделить не удастся. Но у нас еще остался возрастающий тренд. Какую аппроксимацию выбрать для него? Если величина тренда невелика по сравнению со значениями исходного временного ряда, то лучше всего выбирать линейную аппроксимацию, т.к. какой бы ни была реальная функция тренда, в первом приближении можно ограничиться линейным уравнением вида:

Создадим копию листа «Шаг 4», переименуем эту копию в «Шаг 5». С помощью средства MS Excel Выделить линию тренда построим линейный тренд, получив A_L= −310, 354 и B_L= 24,828 (смотри рис. 7).

Коэффициент детерминации для линейного тренда составляет 0,156, что меньше 0,16284, таким образом, эта корреляция может быть расценена как несущественная. Но мы можем принять этот тренд, если смягчим требования, уменьшив доверительную вероятность с 0,95 всего до 0,94.

Рисунок 7  Выделение линейного тренда

Далее рассчитаем «сезонную компоненту», т.е. усредненные по периодам отклонения от модели.

Создадим копию листа «Шаг 5», переименуем эту копию в «Шаг 6».

В ячейку В3 запишем формулу: ='Шаг 5'!E3 и автозаполнителем скопируем ее в ячейки В4:В26.

В ячейку D3 запишем формулу: =(B3+B15)/2; с помощью автозаполнителя скопируем ее в ячейки D4:D14.

В ячейку D15 запишем ту же формулу: =(B3+B15)/2 , и с помощью автозаполнителя скопируем ее в ячейки D16:D26.

В ячейке Е27 рассчитаем сумму сезонной компоненты: за 2 периода она равна 9,0949´10^-13, т.е. практически равна нулю, а это является признаком сезонности (смотри рис. 8).

Рисунок 8  Выделение сезонной компоненты

Создадим копию листа «Шаг 1», переименуем эту копию в «Шаг 7».

В ячейку D3 запишем формулу: ='Шаг 1'!D3+'Шаг 2'!D3+'Шаг 3'!D3+'Шаг 4'!D3+'Шаг 5'!D3+'Шаг 6'!D3; с помощью автозаполнителя скопируем ее в ячейки D4:D26. Это и будет искомая линия тренда (рис. 9).

Рисунок 9  Искомая линия тренда объемов продаж

В ячейку Q3 запишем формулу расчета относительных квадратов отклонений модели: =(E3/D3)*(E3/D3); с помощью автозаполнителя скопируем формулу в ячейки Q4:Q26.

В ячейку Q27 запишем формулу вычисления среднего относительного квадрата отклонения модели: =СУММ(Q3:Q26)/H27 ; оно равно 0,000597; таким образом, точность полученной модели составляет 99,94%.

Относительное среднеквадратичное отклонение будет равно корню квадратному из среднего относительного квадрата отклонения; вычислим его в ячейке Р27: =КОРЕНЬ(Q27).

И, наконец, перейдём к прогнозированию на основе построенной модели. Для этого:

На листах «Шаг 1» ‒ «Шаг 7» в ячейках А27:А38 запишем номера периодов с 25 по 36.

На листах «Шаг 1» ‒ «Шаг 5», «Шаг 7» формулу из ячейки D26 автозаполнителем скопируем в ячейки D27:D38.

На листе «Шаг 6» в ячейку D27 запишем формулу =(B3+B15)/2 и с помощью автозаполнителя скопируем ее в ячейки D28:D38.

На листе «Шаг 7» формулы из R26 и S26 автозаполнителем скопируем в ячейки R27:R38 и S27:S38. Прогноз продаж представлен на рис. 10.

Рисунок 10  Прогноз объема продаж на третий год

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

Литература

1. Бауэрсокс Д. Логистика: интегрированная цепь поставок [Текст]: пер. с англ. / Д. Бауэрсокс, Д Клосс − М.: Олимп-Бизнес, 2010. − 640 с.

2. Зеваков А.М. Логистика производственных и товарных запасов [Текст]: учебник / А.М. Зеваков, В.В. Петров – СПб.: Изд-во Михайлова В.А., 2012. – 320 с.

3. Кристофер М. Логистика и управление цепями поставок [Текст] / М. Кристофер – СПб.: Питер, 2010. – 316 с.

4. Плоткин Б.К. Экономико-математические методы и модели в управлении материальными ресурсами [Текст] / Б.К. Плоткин – СПб.: Издательство СПбУЭФ, 2012. – 64 с.

5. Плоткин Б.К. Экономико-математические методы и модели в логистике [Текст]: Учебное пособие / Б.К. Плоткин,Л.А. Делюкин – СПб.: Изд-во СПбГУЭФ, 2010. – 96 с.

6. Теория прогнозирования и принятия решений [Текст]: Учебное пособие. / Под ред. С. А. Саркисяна. – М.: «Высшая школа», 2007. – 351 с.

7. Фомин Г.П. Математические методы и модели в коммерческой деятельности [Текст] / Г.П.Фомин – М.: Финансы и статистика, 2010. – 416 с.

8. Хазанова Л.Э. Логистика: Методы и модели управления материальными потоками [Текст]. Учебник / Л.Э. Хазанова– М.: Изд-во БЕК, 2013. – 120 с.

9. Минько А.А. Прогнозирование в бизнесе с помощью Excel [Текст] / А.А. Минько – М.: Эксмо, 2013. – 208 c.

10. Орлова И.В. Экономико-математические методы и модели. Выполнение расчетов в среде Excel: Практикум [Текст] / И.В. Орлова  М.: «Высшая школа», 2012. – 207 с.

11. Филонов Н.Г. Логистика [Текст]: Учебное пособие / Н.Г. Филонов – Томск: Издательство томского пед. ун-та, 2013. – 250 с.

12. Бушуева Л.И. Метод прогнозирования объема продаж [Электронный ресурс]: // Маркетинг в России и за рубежом. 2002. №1. Режим доступа: http://www.cfin.ru/press/marketing/2002-1/index.shtml

13. Кошечкин С.А. Алгоритм прогнозирования объема продаж в MS Excel [Электронный ресурс]: Режим доступа:// http://www.cfin.ru.

14. Плоткин Б.К. Экономико-математические методы и модели в управлении материальными ресурсами [Текст] / Б.К. Плоткин – СПб.: Издательство СПбУЭФ, 2012. – 64 с.

Код для цитирования: Скопировать