Технология прогнозирования периодических экономических процессов на основе методов гармонического анализа в MS Excel Текст научной статьи по специальности «Математика»

УДК 336.02

DOI: 10.18384/2310-6646-2018-3-19-28

ТЕХНОЛОГИЯ ПРОГНОЗИРОВАНИЯ ПЕРИОДИЧЕСКИХ ЭКОНОМИЧЕСКИХ ПРОЦЕССОВ НА ОСНОВЕ МЕТОДОВ ГАРМОНИЧЕСКОГО АНАЛИЗА В MS EXCEL

Юров В.М.

Московский государственный областной университет

141014, Московская область, г. Мытищи, ул. Веры Волошиной, д. 24

Технологический университет

141070, Московская обл., г. Королев, ул. Гагарина, д. 42, Российская Федерация

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

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

A HARMONIC ANALYSIS-BASED TECHNIQUE FOR FORECASTING PERIODIC ECONOMIC PROCESSES IN MS EXCEL

V. Yurov

Moscow Region State University

141014, Moscow region, Mytischi, ul. Vera Voloshina, house 24

Abstract. The article considers a technique for constructing the point forecast of periodic economic processes by means of harmonic analysis and Excel tools. The technique is based on the construction and use of a time series model for forecasting, including trend, seasonal and cyclic components. Special attention is paid to the choice of the number of levels of the series used to build the model; identifying and assessing the significance of its parameters; study of the quality of the constructed predictive model and the adequacy of its source data with wide use of Excel tools. The process of forecasting is considered by the example of a series of quarterly data on the company's sales for eight years. The high predictive power of the constructed model for point forecasting is shown both for the considered indicator as a whole, and for seasonal and cyclic fluctuations.

© CC BY Юров В.М., 2018.

Keywords: time series, trend, seasonality, cyclic component, point forecast, harmonic analysis, retro forecast.

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

В общем случае экономический временной ряд Yt состоит из четырёх составляющих: трендовой T, сезонной S, циклической C и случайной £t [2]. Здесь t - момент времени. Модели взаимосвязей между этими компонентами могут иметь различный вид: аддитивный, мультипликативный, смешанный.

Для анализа поведения таких временных рядов и построения математических моделей, используемых для прогнозирования, широко применяется гармонический анализ. Метод гармонического анализа реализован во многих статистических пакетах [8]. Однако статистические пакеты имеют ряд недостатков: они требуют статистического образования, профессиональных навыков и высокой квалификации пользователей; в них фактически отсутствует возможность реализации собственных алгоритмов, они имеют высокую цену. В отличие от них, программа MS Excel является доступной, включает множество статистических функций, даёт возможность использовать встроенную надстройку "Анализ данных" и создавать собственные алгоритмы. Для небольших предприятий MS Excel будет экономически выгодной.

Поэтому представляется целесообразным разработать доступную широкому кругу специалистов и гибкую технологию прогнозирования периодических экономических процессов на основе методов гармонического анализа в табличном процессоре MS Excel.

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

Большое значение для построения модели ряда и прогноза по ней имеет выбор числа уровней ряда. Для успешного прогнозирования процесса важно, чтобы временной ряд имел достаточную длину. При изучении периодических колебаний желательно иметь статистические данные об изучаемом процессе не менее чем за два-три полных периода колебания [6]. Поэтому, если ряд имеет только сезонные колебания, необходимо иметь данные не менее чем за 2-3 года. Если ряд имеет и циклическую составляющую, длина его существенно увеличи-

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

Кроме того, необходимо, чтобы число уровней ряда было кратно периодам сезонных и циклических колебаний. Например, если за интервал в ряду принят квартал (период сезонных колебаний равен 4 кварталам) и при этом период циклических колебаний равен 10 кварталам, число уровней ряда должно быть равным 20; 40; 60 и т. д. В противном случае в построенной модели периоды сезонных и циклических колебаний будут отличаться от фактических, что непосредственно повлияет на точность прогноза по модели. Это определяется тем, что при использовании гармонического анализа период колебаний равен N/k, где N - число уровней ряда, а k - номер гармоники, отражающей данный вид колебаний. Например, если число уровней ряда равно 28 кварталам, период сезонных колебаний остаётся равным 4-м при k = 7, а период циклических колебаний при k = 2; 3 и 4 будет равен 14, 9, 33 и 7 кварталам, но никак не 10.

Если число уровней исходного ряда оказывается не соответствующим требуемому, возможны два подхода к разрешению возникшей проблемы: сокращение числа наблюдений до требуемого или добавление в исследуемый временной ряд необходимого числа нулей (средний уровень детрендированного ряда) [5]. В последнем случае полученные амплитуды колебаний будут несколько меньше фактических. Естественно, при прогнозировании колебаний предпочтительнее взять период времени, предшествующий предсказываемому.

Первичная обработка исходных данных начинается с построения графика исследуемого показателя. Уже на стадии графического анализа часто можно определить наличие и вид тренда, наличие сезонности, характер периодических колебаний (аддитивный или мультипликативный) и др. Циклические колебания обычно на графике визуально не определяются. Для определения их наличия и периода необходимо исключить из ряда тренд и сезонные колебания. Тренд определяется непосредственно по исходным данным путём построения уравнения тренда или путём сглаживания уровней ряда. В Excel для построения модели тренда можно использовать такой инструмент, как "Мастер диаграмм". Сезонная составляющая исключается из детрендированного ряда центрированной скользящей средней, рассчитанной по 12 уровням ряда для месячных данных или по 4 уровням ряда - для квартальных данных.

После выяснения наличия и периода сезонных и циклических колебаний определяется требуемое число уровней ряда и формируется ряд для построения модели.

Применение гармонического анализа для построения модели ряда предполагает разложение детрендированного ряда на функции синусов и косинусов разных частот [1]:

N/2 N/2

Yt = ^ ak cos(okt) + ^ bk sm(okt), (1)

к=1 k=1

ТЕГ

2я

где ® =-- основная частота колебаний;

N

N - число уровней ряда;

ak и Ьк - коэффициенты Фурье, определяемые по формулам:

N 2 N

ак = N £ ^ cos(©kt); К = N £ - при к= 1.. .(N/2-1)

1 N

ак = —£ cos(wkt); Ък = 0 - при к=Ы/2.

Каждый член суммы (1) представляет собой гармонику с определённым периодом. Для оценки значимости каждой гармоники рассчитываются статистика Fk и уровень значимости каждой гармоники рк [1]:

о:/2

• (3)

ост

п2 _ VN/2n2 -Yq п2

где °ост~ ¿-¡к=1 °к ¿-¡]=1 °k(j) - остаточная дисперсия;

О)? = (а? + Ь2)/2 - дисперсия, учитываемая каждой гармоникой [6];

N = N-(2q + 1);

j - номера учитываемых в модели гармоник;

q - число учитываемых гармоник.

Уровень значимости pk находится с помощью статистической функции Excel Б.РАСП при данном значении Fk и числе степеней свободы 2 и N. Это позволяет построить модель временного ряда, включающего тренд и только несколько значимых гармоник, отражающих сезонные и циклические колебания [4].

Модель временного ряда (1) представляет собой модель множественной линейной регрессии, в которой результирующая переменная Yt представляет собой уровни модельного ряда, а факторные переменные Uk(t) = cos(kwt) и Zk(t) = sin(ko)t) являются функциями синусов и косинусов отобранных гармоник. Поэтому для построения модели можно использовать инструмент "Регрессия" надстройки "Анализ данных" Excel [3] или инструмент "Анализ Фурье" [4] той же надстройки, если число уровней ряда равно числу 2 в степени 2, 3, 4 и т. д.

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

Процесс построения прогноза рассмотрим на примере временного ряда поквартальных объёмов продаж компании [7], включающего тренд, сезонные и

циклические колебания. Число уровней рассматриваемого временного ряда N равно 32. График этого ряда и модель тренда изображены на рис. 1.

Рис. 1. График объёмов продаж компании

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

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

. 30 и 20 Л ? ш • л' Л.

у 0 Л ^ -10 12 3 4 -20 -30

8 9^nfejjfW^16j^jrfl9202122232425i^728jg^)3132 t, КВ.

Рис. 2. График циклических колебаний

Данные на рис. 2 указывают на то, что в исследуемом временном ряду имеется циклическая компонента с неустойчивой амплитудой и периодом, кратным 10 кварталам.

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

ряда будем использовать для построения модели, а первые 12 уровней - для её верификации.

Расчёт коэффициентов модели ak и bk ряда будем производить с использованием надстройки "Регрессия" Excel. Результаты расчёта коэффициентов гармоник и других параметров, необходимые для идентификации модели, приведены в табл. 1.

Таблица 1

Результаты расчётов параметров гармоник

Номер гармоники k Период, кв Частота, кв-1 Коэффициент a, k Коэффициент b, k Дисперсия ^ Статистика Fk Значимость p

0 -2,67 7,11 0,33 0,73

1 20,00 0,05 13,85 6,46 116,76 5,39 0,02

2 10,00 0,10 10,82 9,03 99,36 4,58 0,03

3 6,67 0,15 -2,08 6,07 20,60 0,95 0,41

4 5,00 0,20 0,36 -8,66 37,56 1,73 0,22

5 4,00 0,25 -45,18 -7,56 1049,2 48,42 0,00

6 3,33 0,30 -2,01 4,81 13,61 0,63 0,55

7 2,86 0,35 3,09 -0,91 5,17 0,24 0,79

8 2,50 0,40 -0,31 9,17 42,06 1,94 0,19

9 2,22 0,45 2,72 -0,67 3,92 0,18 0,84

10 2,00 0,50 -16,71 0,00 279,22 12,88 0,00

Как видно из табл. 1, значимыми с уровнем р < 0,05 являются только четыре гармоники: 1, 2, 5 и 10.

Пятая гармоника отражает сезонные эффекты при периоде в 4 квартала и частоте колебаний 0,25 кв-1, она является основной составляющей в модели сезонности. Кратной этой частоте будет частота 0,5 кв-1, которой соответствует 10 гармоника. Её включение в модель сделает форму сезонной волны несимметричной, более соответствующей реальной форме и уменьшит остаточную дисперсию. Поэтому эта гармоника также должна быть включена в модель сезонности.

Аналогично первая гармоника с периодом в 20 кв. и частотой 0,05 кв-1 является основной составляющей в модели цикличности. Кратной этой частоте будет частота 0,1 кв-1, которой соответствует 2 гармоника. Поэтому в модель циклических колебаний войдут 1 и 2 гармоники.

Таким образом, получаем следующие модели тренда, сезонной и циклической компонент:

Т = 1,3103? + 253,2; = - 45,181со5(5й)0 -7,564зт(5М) -16,710<ш(10ю£); (4)

С = 13,8 51со5(«0 +6,45552'И(«0 +10,822СШ(2«0 + 9,033$1п(2ш?).

Полученная аддитивная модель ряда имеет следующие показатели точности: процентная ошибка модели Е% = 4,03%; коэффициент детерминации Я2 = 0,922; средняя квадратическая ошибка оост = 11,4 д. е. Эти показатели, а также представленные на рис. 3 графики исходных и модельных значений уровней ряда свидетельствуют о высокой точности построенной модели.

12345678 9 1011121314151617181920212223242526272829303132

Рис. 3. График исходных, модельных и ретроспективных значений уровней ряда

Графики исходных и ретроспективных значений уровней ряда (см. рис. 3) показывают высокую прогностическую способность построенной модели, поскольку она способна воспроизводить фактические данные за прошлые 12 кварталов. При этом показатели точности ретропрогноза ненамного уступают показателям точности самой модели: средняя квадратическая ошибка оост = 13,95 д.е.; процентная ошибка модели Е% = 5,33%. Следовательно, эту модель ряда можно использовать для построения точечного прогноза в целом для рассматриваемого показателя.

Поскольку построенная модель является аддитивной, она позволяет производить прогноз и в отдельности для сезонных и циклических колебаний. Сезонные волны в соответствии с моделью являются несимметричными, но имеющими постоянную амплитуду, равную 45,8 д. е. График сезонной компоненты представлен на рис. 4.

50

100

Рис. 4. График сезонной компоненты

Циклические волны в соответствии с моделью также являются несимметричными, но при этом они имеют непостоянную амплитуду. График циклической

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

Рис. 5. График циклической компоненты

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

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

Технология предусматривает использование большого набора статистических инструментов и процедур Excel, необходимых для идентификации и оценки значимости параметров прогнозной модели, исследование её качества, точности и адекватности исходным данным.

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

ЛИТЕРАТУРА

1. Андерсен Т. Статистический анализ временных рядов. М.: Мир, 1976. 754 с.

2. Бабич Т.Н., КозьеваИ.А., Вертакова Ю.В., Кузьбожев Э.Н. Прогнозирование и планирование в условиях рынка: учеб. пособие / М.: ИНФРА-М, 2017. 336 с.

3. Протасов Ю.М., Юров В.М. Гармонический анализ периодических колебаний продаж компании на основе инструмента "Регрессия" MS Excel // Вестник Московского государственного областного университета. Серия: Экономика. 2016. № 2. С. 115-121.

4. Протасов Ю.М., Юров В.М. Моделирование сезонных и циклических колебаний объёмов продаж компании с использованием методов гармонического анализа в MS Excel // Вестник Московского государственного областного университета. Серия: Экономика. 2015. № 2. С. 101-108.

5. Протасов Ю.М., Юров В.М. Сравнительная оценка способов моделирования экономических временных рядов с периодическими колебаниями // Вопросы региональной экономики. 2017. № 2. С. 181-187.

6. Статистика: учеб. / под ред. В.Г. Ионина. 4-е изд., перераб. и доп. М.: ИНФРА-М, 2017. 355 с.

7. Ханк Д.Э., Уичерн Д.У, Райтс А.Д. Бизнес-прогнозирование. М.: Вильямс, 2003. 652 с.

8. Цыпин А.П., Сорокин А.С. Статистические пакеты программ в социально-экономических исследованиях // Азимут научных исследований: экономика и управление. 2016. Т. 5. № 4 (17). С. 379-384.

1. Andersen T. Statisticheskii analiz vremennykh ryadov [Statistical Analysis of Time Series],

Moscow, Mir Publ., 1976. 754 p.

2. Babich T.N., Koz'eva I.A., Vertakova Yu.V., Kuz'bozhev E.N. Prognozirovanie i planirovanie

v usloviyakh rynka [Forecasting and Planning in Market Conditions]. Moscow, INFRA-M Publ., 2017. 336 p.

3. Protasov Yu.M., Yurov V.M. [Harmonic Analysis of Periodic Fluctuations in Sales Based

on Regression Tool in MS Excel]. In: Vestnik Moskovskogo gosudarstvennogo oblastnogo universiteta. Seriya: Ekonomika [Bulletin of Moscow Region State University. Series: Economics], 2016, no. 2, pp. 115-121.

4. Protasov Yu.M., Yurov V.M. [Modeling Seasonal and Cyclical Fluctuations in Sales of the

Company Using Methods of Harmonic Analysis in MS EXCEL]. In: Vestnik Moskovskogo gosudarstvennogo oblastnogo universiteta. Seriya: Ekonomika [Bulletin of Moscow Region State University. Series: Economics], 2015, no. 2, pp. 101-108.

5. Protasov Yu.M., Yurov V.M. [Comparative Evaluation of the Methods of Modeling Economic

Time Series with Periodic Fluctuations]. In: Voprosy regional'noi ekonomiki [Issues of Regional Economy], 2017, no. 2, pp. 181-187.

6. Ionina V.G., ed. Statistika [Statistics]. Moscow, INFRA-M Publ., 2017. 355 p.

7. Khank D.E., Uichern D.U., Raits A.D. Biznes-prognozirovanie [Business Forecasting]. Moscow,

Williams Publ., 2003. 652 p.

8. Tsypin A.P., Sorokin A.S. [Statistical Software Packages in Social and Economic Research]. In:

Azimut nauchnykh issledovanii: ekonomika i upravlenie [Azimuth Research: Economics and Management], 2016, vol. 5, no. 4 (17), pp. 379-384.

Юров Владимир Михайлович - кандидат технических наук, доцент, доцент кафедры прикладной математики и информатики Московского государственного областного университета, доцент кафедры управления качеством и стандартизации Технологического университета; e-mail: urow5@mail.ru

Vladimir M. Yurov - PhD in Engineering, associate professor, associate professor at the Department of Applied Mathematics and Informatics, Moscow Region State University, associate Professor at the Department of Quality Management and Standardization, Technological University; e-mail: urow5@mail.ru

REFERENCES

ИНФОРМАЦИЯ ОБ АВТОРЕ

INFORMATION ABOUT THE AUTHOR

ПРАВИЛЬНАЯ ССЫЛКА НА СТАТЬЮ Юров В.М. Технология прогнозирования периодических экономических процессов на основе методов гармонического анализа в MS Excel // Вестник Московского государственного областного университета. Серия: Экономика. 2018. № 3. С. 19-28.

DOI: 10.18384/2310-6646-2018-3-19-28

FOR CITATION

Yurov V.M. A Harmonic Analysis-Based Technique for Forecasting Periodic Economic Processes in Ms Excel. In: Bulletin of Moscow Region State University. Series: Economics, 2018, no. 3, рр. 19-28. DOI: 10.18384/2310-6646-2018-3-19-28