Решение. 1 График временного ряда представлен на рисунке 4.1

1 График временного ряда представлен на рисунке 4.1.

Рисунок 4.1 – График временного ряда Рисунок 4.2 – Коррелограмма

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

Рисунок 4.3 – Расчет коэффициентов автокорреляции

3 Выравнивание исходных уровней ряда проводится мето­дом скользящей средней. Для этого:

а) суммируются уровни ряда последовательно за каждые четыре квартала со сдвигом на один момент времени и путем деления полученных сумм на 4 находятся скользящие сред­ние. При этом в ячейку С9 введена формула =СРЗНАЧ(B8:B11), скопированная в диапазон С10:С21. Полученные таким образом выровненные значения уже не со­держат сезонного компонента и представлены в столбце «скользящая средняя за 4 квартала» (рисунок 4.4).

б) полученные значения приводятся в соответствие с фактичес­кими моментами времени, для чего находятся средние значения из двух последовательных скользящих средних – центрированные скользящие средние. Для этого в столбце «центрированная скользящая средняя» представлены средние значения для двух последовательных скользящих средних. С этой целью в ячейку D10 введена формула: =СРЗНАЧ(C9:C10), скопированная в диапазон D11: D21.

Рисунок 4.4 – Расчет аддитивной модели временного ряда

4Находятся оценки сезонного компонента S как разность между фактическими уровнями ряда и центрированными скользящими средними. Для этого в ячейку Е10 введена формула =B10–D10, которая скопирована в диапазон Е11:Е21.

Эти оценки используются для расчета значений сезонного компонента S. Для этого находятся средние за каждый квартал (по всем годам) оценки сезонного компонента S.

Для определения средних значений оценок сезонного компонента по каждому кварталу в ячейку С35 вводится формула =СРЗНАЧ(C29:C32), которая копируется до столбца F.

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

Скорректированные значения сезонного компонента рассчитываются как разность между ее средней оценкой и корректирующим коэффициентом k. Для определения коэффициента k необходимо найти среднее значение суммы средних оценок сезонного компонента за 4 года. Для данной модели k = G33 = СРЗНАЧ(С34:F34).

В диапазоне С35:F35 рассчитаны скорректированные значения сезонного компонента как разности между соответствующими средними оценками и корректирующим коэффициентом. Для этого в ячейку С35 введена формула =C34–$G$34, которая скопирована в диапазон D35:F35.

Для проверки условия равенства нулю суммы значений сезонного компонента в ячейку G35 введена формула =СУММ(C35:F35). Скорректированные значения сезонной компоненты переносятся в столбец F.

5 Исключается влияние сезонного компонента путем вычи­тания его значения из каждого уровня исходного временного ряда. Для этого в ячейке G8 записана формула =B8–F8, которая скопирована вниз до строки 23 (рисунок 4.4). Получаются величины Т + Е = Y – S. Эти значения рассчитываются за каждый момент времени и содержат только трендовый и случайный компоненты.

6Для определения компонента Т данной модели проводится аналитическое выравнивание ряда (Т + Е)с помощью линейного тренда. Определить коэффициенты а и b линейного тренда можно с помощью формул:

– коэффициент а: =ОТРЕЗОК(G8:G23;A8:A23);

– коэффициент b: =НАКЛОН(G8:G23;A8:A23).

Уравнение тренда будет иметь вид Т = 5,977+0,055 t.

Подставляя в это уравнение значения t = 1, 2,..., 16, можно найти уровни Т для каждого момента времени (столбец H).

7 Расчетные значения (Т + S) получены в столбце I.

8 Абсолютные ошибки определяются по формуле E = y – (T + S), записанной в столбце J.

Прогнозное значение в аддитивной модели есть сумма скорректированной сезонной и трендовой составляющих. Для кварталов под номерами 17 и 18 прогнозные значения составят 7,018 и 4,323 ед.

На рисунке 4.6 приведен пример расчета мультипликативной модели временного ряда. Данный временной ряд представлен мультипликативной моделью, поскольку амплитуда колебаний ряда возрастает (рисунок 4.5).

Пусть имеются поквартальные данные о продажах за четыре года (рисунок 4.6).

На листе «Анализ2» подготовлены исходные данные, как показано на рисунке 4.6.

1 График временного ряда представлен на рисунке 4.5.

Рисунок 4.5 – График временного ряда

2 График временного ряда, представленный на рисунке 4.5, свидетельствует о наличии сезонных колебаний (период колебаний равен четырем) и общей возрастающей тенденции уровней ряда. Размах вариации фактических значений относительно линии тренда постоянно возрастает. К таким данным следует применять модель с мультипликативной компонентой.

3 Выравнивание исходных уровней ряда проводится мето­дом скользящей средней. Для этого:

а) суммируются уровни ряда последовательно за каждые четыре квартала со сдвигом на один момент времени и путем деления полученных сумм на 4 находятся скользящие сред­ние. При в ячейку С12 введена формула =СРЗНАЧ(B10:B13), скопированная в диапазон С12:С22. Полученные таким образом выровненные значения уже не со­держат сезонной компоненты и представлены в столбце «скользящая средняя за 4 квартала».

б) полученные значения приводятся в соответствие с фактичес­кими моментами времени, для чего находятся средние значения из двух последовательных скользящих средних – центрированные скользящие средние. Для этого в столбце «центрированная скользящая средняя» представлены средние значения для двух последовательных скользящих средних. С этой целью в ячейку D12 введена формула: =СРЗНАЧ(C12:C13), скопированная в диапазон D13: D21.

4.Находятся оценки сезонной компоненты S как частное от деления фактических уровней ряда у на центрированные скользящие средние. Для этого в ячейку Е12 введена формула: =B12/D12, которая скопирована в диапазон Е13:Е21.

Эти оценки используются для расчета значений сезонной компоненты S. Для этого находятся средние за каждый квартал (по всем годам) оценки сезонной компоненты S_r

Для расчета сезонной компоненты удобно переписать полученные оценки в виде таблицы (рисунок 4.6).

Определим средние значения оценок сезонной компоненты по каждому кварталу, для этого в ячейку С35 введем формулу: =СРЗНАЧ(C30:C33). которую скопируем до столбца F.

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

Скорректированные значения сезонной компоненты рассчитываются как разность между ее средней оценкой и корректирующим коэффициентом k.

Для определения коэффициента k необходимо ввести формулу k = G35 = 4/СУММ(C35:F35).

В диапазоне С36:F36 рассчитаны скорректированные значения сезонной компоненты как произведения соответствующих средних оценок и корректирующего коэффициента k. Для этого в ячейку С36 введена формула =C35*$G$35, которая скопирована в диапазон D36:F36.

Для проверки условия равенства четырем суммы значений сезонной компоненты в ячейку G36 введена формула =СУММ(C36:F36). Скорректированные значения сезонной компоненты переносятся в столбец F для соответствующих кварталов каждого года.

5 Исключается влияние сезонной компоненты, путем вычи­тания ее значения из каждого уровня исходного временного ряда.. Для этого в ячейке G8 записана формула =B8–F8, которая скопирована вниз до строки 23. Получаются величины Т + Е = Y – S. Эти значения рассчитываются за каждый момент времени и содержат только тенденцию и случайную компоненту (табл. 3.4).

6Для определения компоненты Т данной модели про­водится аналитическое выравнивание ряда (Т + Е) с помощью линей­ного тренда. Определить коэффициенты а и b линейного тренда можно с помощью формул:

Коэффициент а: =ОТРЕЗОК(G8:G23;A8:A23)

Коэффициент b: =НАКЛОН(G8:G23;A8:A23)

Уравнение тренда будет иметь вид: Т =9,819+2,412 t.

Подставляя в это уравнение значения t = 1, 2,..., 16, можно найти уровни Т для каждого момента времени (столбец H).

7 Расчетные значения (Т*S) получены в столбце I.

8 Абсолютные ошибки определяются по формуле E = y – (T * S), записанной в столбце J.

Прогнозное значение в аддитивной модели есть произведение трендовой и сезонной компонент.

Для кварталов под номерами 17 и 18 прогнозные значения составят 42,269 и 33,096 ед.

Рисунок 4.6 – Расчет мультипликативной модели временного ряда