Авторегрессионная модель

На рис.5.32 представлен пример авторегрессионной модели случайной компоненты. В столбце A и B указаны соответственно номера уровней и значения случайной компоненты (остатки). Предварительно выбираем авторегрессию второго порядка, руководствуясь значениями АКФ и ЧАКФ рис.5.4.

В столбец С из столбца В (из столбца случайной компоненты) копируется вектор Y_t = E_p+1,n = E_3,24.

В столбец D из столбца В копируется вектор Y_p1 = E_p,n-1 = E_2,23.

В столбец E из столбца В копируется вектор Y_p1 = E_p-1,n-2 = E_1,22.

Коэффициенты авторегрессии определяются по (5.60). Для этого в ячейку I4 вводим выражение:

=МУМНОЖ(МУМНОЖ(МОБР(МУМНОЖ(ТРАНСП(D3:E24);D3:E24));ТРАНСП(D3:E24));С3:С24).

Затем выполняем такие действия: нажать Enter, копировать ячейку I4 в ячейку I5, нажать клавишу F2, нажать комбинацию клавиш Ctrl+Shift+Enter. В ячейках I4, I5 должны появится значения коэффициентов авторегрессии.

Далее по уравнению регрессии (5.61) выполнить прогноз случайной компоненты рис.5.32. Для этого в ячейки В30В32 вводим соответственно выражения: =I4*B26+I5*B25, =I4*B30+I5*B26, =I4*B31+I5*B30 и т.д. если прогноз выполняется на большее число периодов.

Суммируем прогноз случайной компоненты с прогнозом по уравнению тренда (в ячейку С30 записываем выражение =B30+TREND!G32 и копируем ее в блок ячеек С31:С32). По 5.62 определяем интервал прогноза: в ячейку D30 - =C30-$I$8*$I$10, в ячейку Е30 - =C30+$I$8*$I$10. Выражения D30 и Е30 копируем в блок ячеек D31:E32. Среднеквадратическое отклонение случайной компоненты и коэффициент Стьюдента в формуле (5.62) определяем с помощью встроенных функций: в ячейке I8 записываем выражение =СТАНДОТКЛОНП(B3:B26), в ячейке I10 записываем выражение =СТЬЮДРАСПОБР(0.05;22).

Как было сказано ранее, что полином 4-ой степени в качестве уравнения роста по значению коэффициента достоверности аппроксимации R² является более точной по сравнению с полиномами меньшего порядка. Однако прогноз по полиному четвертой степени не внушает доверия.

5.2.1.8.Анализ сезонных колебаний

На рис.5.33. представлена модель анализа сезонных колебаний с помощью индексов сезонности.

В столбце А, В и С приведены соответственно номера уровней, номера кварталов и значения уровней временного ряда. Как видно из графика (рис.5.34) представленный в качестве примера временной ряд имеет явно выраженную сезонную компоненту и трендовую составляющую.

Это подтверждается и автокорреляционной функцией (рис.5.35). На графике видно, что наибольший коэффициент автокорреляции соответствует лагу равному 4, т.е. имеются квартальные периодические колебания.

В столбце D производится проверка гипотезы о наличии сезонности с помощью критерия "пиков" и "ям" по (5.23). Для этого в ячейку D5 запишем выражение =ЕСЛИ(ИЛИ(И(B5>B4;B5>B6);И(B5<B4;B5<B6));1;0). Ячейку D5 копируем в ячейки D6:D23. В ячейке D28 определяем сумму р_р единичек в столбце D. В ячейке D29 определяется критическое значение р_кр. Т.к. эти значения не близки, то можно считать, что гипотеза об отсутствии тренда отвергается.

Выбор линии тренда производится также как и на рис.5.7. На рис.5.34. представлен график временного ряда, линия тренда и уравнение тренда. Расчет автокорреляционной функции производим по (5.13) по технологии, представленной в п.5.11.12.

В столбце E рассчитываем разность между текущим значением временного ряда и его средним значением. В ячейку E4 записываем выражение =C4-$C$31 и копируем эту ячейку на всю длину временного ряда.

В ячейках F4F11 определяем значения коэффициентов ковариации со сдвигом  = 1, 2,…, 8. Для этого в ячейки записываем выражения:

-в ячейку F4 сдвиг  = 1 - =СУММПРОИЗВ(E4:E26;E5:E27)/24,

-в ячейку F5 сдвиг  = 2 - =СУММПРОИЗВ(E4:E25;E6:E27)/24,

-в ячейку F6 сдвиг  = 3 - =СУММПРОИЗВ(E4:E24;E7:E27)/24,

-в ячейку F7 сдвиг  = 4 - =СУММПРОИЗВ(E4:E23;E8:E27)/24,

-в ячейку F8 сдвиг  = 5 - =СУММПРОИЗВ(E4:E22;E9:E27)/24,

-в ячейку F9 сдвиг  = 6 - =СУММПРОИЗВ(E4:E21;E10:E27)/24,

-в ячейку F10 сдвиг  = 7 - =СУММПРОИЗВ(E4:E20;E11:E27)/24,

-в ячейку F11 сдвиг  = 8 - =СУММПРОИЗВ(E4:E19;E12:E27)/24.

В ячейках F14F21 определяем значения коэффициенты автокорреляции со сдвигом  = 1, 2,…, 8. Для этого в ячейку F14 записываем выражение =F4/$B$30, которое копируем в ячейки F15F21. По данным ячеек F14F21 строим график автокорреляционной функции рис.5.35.

В столбце G производится расчет уровней временного ряда по уравнению тренда. В ячейку G4 записываем выражение для полинома:

=$M$16+$M$17*A4+$M$17*A4:A42+$M$19*A4^3, которое копируем в блок ячеек G4:G27.

В столбце H рассчитываются показатели сезонности по (5.66). В ячейку H4 записывается выражение =C4/G4*100, которое копируется в блок ячеек H4:H27.

Находим средний индекс сезонности по одноименным кварталам по (5.67). Для этого в ячейкуI4 записываем выражение =(H4+H8+H12+H16+H20+H24)/6. Это выражение копируем в ячейки I5, I6, I7.

В ячейке I10 определяем средний индекс сезонности =СУММ(I4:I7)/4.

Т.к. средний индекс сезонности близок к 100%, в выравнивании индексов нет необходимости. Для полной иллюстрации алгоритма выполним выравнивание. Для этого в ячейке I13 определяется выравнивающий множитель как обратная величина среднего индекса сезонности =1/I0*100 и умножение квартальных индексов на этот множитель. В ячейку I16 записывается выражение =I4*$I$11, которое копируется в ячейки I17, I18, I19. Т.о в ячейках I16, I17, I18, I19 получаем искомые квартальные индексы сезонности. При проверочном расчете среднего индекса сезонности (ячейка I22) получаем 100%.

Прогноз с помощью индексов сезонности (рис.5.36) выполняется по (5.69). Для оценки прогноза по уравнению тренда в ячейку С34 записывается выражение:

=$M$16+$M$17*A34+$M$18*A34^2+$M$19*A34^3,

которое копируется в ячейки С35, С36, С37.

Для уточнения прогноза с помощью индексов сезонности в ячейку Е34 записывается выражение =C34*I16/100, которое копируется в ячейки Е35, Е36, Е37.

Адаптивная модель Хольта-Уинтерса (аддитивная сезонная модель)

На рис.5.37 изображен пример анализа сезонности временного ряда с помощью адаптивного метода Хольта-Уинтерса. Номер уровня и значения самого уровня временного ряда приведены в столбцах А и В. В столбцах С иD определяются параметры адаптивного полинома (5.75, 5.76). В столбце Е определяются значения фактора сезонности (5.77).

Значения временного ряда в первом квартале приняты как начальные значения для коэффициента a_1,t. В ячейку С3 записываем выражение =B3 и копируем ее в ячейки С4С6. Для коэффициента a_2,t в качестве начальных значений взяты абсолютные приросты (y_i+1-y_i) в первом квартале. В ячейку D3 записываем выражение =B4-B3 и копируем ее в ячейки D4D6. Для фактора сезонности g_t за начальные значения берем приросты по кварталам. В ячейку F3 записываем выражение =B7-B3 и копируем ее в ячейки F4F6. Начальные значения параметров сглаживания ₁, ₂, ₃ принимаем также равными 0.3 (оптимальные их значения будут также найдены с помощью команды оптимизации /Сервис/Поиск решения).

В ячейки С7, D7, Е7 записываем формулы (5.75, 5.76, 5.77) для расчета коэффициентов адаптации и фактора сглаживания:

-в ячейку С7 - =$B$31*(B7-E3)+(1-$B$31)*(C6+D6);

-в ячейку D7 - =$B$31*(B7-E3)+(1-$B$31)*(C6+D6);

-в ячейку Е7 - =$D$31*(B7-C7)+(1-$D$31)*E3.

В ячейку F7 записываем выражение для определения расчетных значений временного ряда по уравнению адаптации (5.74) - =C6+D6+E3. В ячейку G7 записываем выражение для определения относительной ошибки аппроксимации - =ABS(B7-F7)/B7. Выражения в ячейках С7, D7, E7, F7, G7 копируем на всю длину временного ряда, т.е. в блок С7:G26.

В ячейке G27 определяем относительную ошибку аппроксимации - =СУММ(G7:G26)/20.

На рис.5.37 расчеты выполнены при случайных значениях параметров сглаживания. После выполнения команды оптимизации /Сервис/Поиск решения (рис.5.38) получаем оптимальное решение (рис.5.39).

На рис.5.40 изображены графики наблюдаемого (Ряд 1), расчетного (Ряд 2) временного ряда и прогноза (Ряд 3).

Анализ временных рядов с помощью моделей АРИСС(p,d,q) в Excel связан с громоздкими вычислениями. Поэтому эти расчеты здесь не приводим, хотя технология выполнения расчетов практически мало отличается от модели экспоненциального сглаживания или Брауна. АРИСС-модели достаточно полно представлены в пакетах - Олимп:СтатЭксперт, Мезозавр, Statistica и др.