Пример 9.4. По данным табл. 9.4 требуется:
1) Рассчитать критерий Дарбина-Уотсона.
2) Оценить полученный результат при 5%-ном уровне значимости.
3) Указать, пригодно ли уравнение для прогноза.
Решение:
1) В Excel составим вспомогательную таблицу как на рис. 9.43.
Рис. 9.43. Вспомогательная таблица к примеру 9.4
Теоретические значения можно определить несколькими способами:
1 способ) Используя полученное в примере 9.2 уравнение множественной линейной регрессии можно вычислить для каждого набора и свое значение
2 способ) Применить инструмент анализа данных Регрессия, поставив флажок для вывода остатков. В столбце с названием «Предсказанное » будут находиться теоретические значения
Заполним столбец G, который получается смещением столбца F на одно значение вниз. Столбец H найдем как разность между столбцами F и G. Заполним столбцы I и J, возведя в квадрат значения столбцов F и H соответственно.
Критерий Дарбина-Уотсона рассчитываем по формуле:
или в Excel =I17/J17.
2) Фактическое (найденное выше) значение сравним с табличными значениями при 5%-ном уровне значимости. При =15 и =2 нижнее значение =0,95, а верхнее =1,54 (таблица 3 приложения). Так как (1,54<1,59< 2.46)? то это означает наличие в остатках автокорреляция отсутствует.
3) Уравнение регрессии может быть использовано для прогноза, так как в нем отсутствует автокорреляция в остатках.
Пример 9.5. Динамика средней номинальной начисленной зарплаты региона по кварталам 2008-2011 гг. характеризуется данными, представленными в табл. 9.6.
Таблица 9.6
Год
|
Квартал
|
t
|
Средняя номинальная начисленная зарплата, тыс. руб.
|
2008
|
I
|
1
|
15,43
|
II
|
2
|
16,97
|
III
|
3
|
17,58
|
IV
|
4
|
18,97
|
2009
|
I
|
5
|
17,45
|
II
|
6
|
18,42
|
III
|
7
|
18,68
|
IV
|
8
|
20,67
|
2010
|
I
|
9
|
19,51
|
II
|
10
|
20,81
|
III
|
11
|
21,03
|
IV
|
12
|
23,49
|
2011
|
I
|
13
|
21,34
|
II
|
14
|
23,15
|
III
|
15
|
23,37
|
IV
|
16
|
26,90
|
Необходимо построить аддитивную модель временного ряда, оценить качество построенной модели и сделать прогноз об средней заработной плате на I и II кварталы 2012 г.
Решение:
Занесем данные и в Excel и построим поле корреляции (рис. 9.44):
Рис. 9.44. Корреляционное поле
Рассчитаем коэффициенты автокорреляции. Для этого составляем первую вспомогательную таблицу рис. 9.45.
Рис. 9.45. Вспомогательная расчетная таблица
Столбец С получается сдвигом данных столбца B на одно значение вниз (т.е. в C3 заносим формулу «=B2» и копируем её вниз). Столбец D вычисляется как разность между столбцом B и его средним значением (20,5560). Формула для вычисления столбца E имеет вид «=C3-$C$20».
Замечание. Для вычисления средних используйте статистическую функцию СРЗНАЧ(диапазон), поскольку среднее значение получается путем деления суммы не на 16, а на 15.
В ячейке I2 вычислим коэффициент автокорреляции первого порядка: =F18/КОРЕНЬ(G18*H18) или
Аналогично составляем вспомогательную таблицу для расчета коэффициента автокорреляции второго порядка. Для этого скопируем исходные данные и вставим их ниже (например, начиная с ячейки A21), а затем немного исправим значения, в частности для расчета значений и используем формулы «=СРЗНАЧ(B25:B38)» и «=СРЗНАЧ(C25:C38)» соответственно (рис. 9.46.)
Рис. 9.46. Вспомогательная расчетная таблица для коэффициента автокорреляции второго порядка
Следовательно,
Аналогично находим коэффициенты автокорреляции более высоких порядков, и все полученные значения заносим в сводную табл. 9.7, на основании которой построим коррелограмму (рис. 9.47).
Таблица 9.7
Лаг ()
|
|
1
|
0,8356
|
2
|
0,8802
|
3
|
0,7322
|
4
|
0,9874
|
5
|
0,7710
|
6
|
0,8358
|
7
|
0,5808
|
8
|
0,9743
|
9
|
0,5345
|
10
|
0,5823
|
11
|
0,0916
|
12
|
0,9618
|
13
|
0,7544
|
14
|
1,0000
|
Рис. 9.47. Коррелограмма
Анализ коррелограммы (рис. 9.47) и графика (рис. 9.44) исходных уровней временного ряда позволяет сделать вывод циклических (периодических) колебаний.
Рассчитаем компоненты аддитивной модели временного ряда.
Шаг 1. Проведем выравнивание исходных уровней ряда методом скользящей средней. Для этого заполним табл. 9.8:
• Просуммируем уровни ряда последовательно за каждые четыре квартала со сдвигом на один момент времени и определим условные годовые средние заработные платы населения (столбец C). Для этого в ячейку C3 поместим«=СУММ(B2:B5)» и протянем за правый нижний уголок ячейки до ячейки C15. В результате произойдет автоматическое заполнение диапазона «C3 – C15».
• Разделив полученные суммы на 4, найдем скользящие средние (столбец D). Например, в ячейку D3 поместим =C3/4. Полученные таким образом выровненные значения уже не содержат сезонной компоненты.
• Приведем эти значения в соответствие с фактическими моментами времени, для чего найдем средние значения из двух последовательных скользящих средних – центрированные скользящие средние (столбец E). Например, в ячейку E4 поместим «=СРЗНАЧ(D3:D4)».
Таблица 9.8
|
A
|
B
|
C
|
D
|
E
|
F
|
1
|
|
|
Итого за четыре квартала
|
Скользящая средняя за четыре квартала
|
Центрированная скользящая средняя
|
Оценка сезонной компоненты
|
2
|
1
|
15,43
|
-
|
-
|
-
|
-
|
3
|
2
|
16,97
|
68,95
|
17,2375
|
-
|
-
|
4
|
3
|
17,58
|
70,97
|
17,7425
|
17,490
|
0,090
|
5
|
4
|
18,97
|
72,42
|
18,1050
|
17,924
|
1,046
|
6
|
5
|
17,45
|
73,52
|
18,3800
|
18,243
|
-0,793
|
7
|
6
|
18,42
|
75,22
|
18,8050
|
18,593
|
-0,172
|
8
|
7
|
18,68
|
77,28
|
19,3200
|
19,063
|
-0,383
|
9
|
8
|
20,67
|
79,67
|
19,9175
|
19,619
|
1,051
|
10
|
9
|
19,51
|
82,02
|
20,5050
|
20,211
|
-0,701
|
11
|
10
|
20,81
|
84,84
|
21,2100
|
20,858
|
-0,048
|
12
|
11
|
21,03
|
86,67
|
21,6675
|
21,439
|
-0,409
|
13
|
12
|
23,49
|
89,01
|
22,2525
|
21,960
|
1,530
|
14
|
13
|
21,34
|
91,35
|
22,8375
|
22,545
|
-1,205
|
15
|
14
|
23,15
|
94,76
|
23,6900
|
23,264
|
-0,114
|
16
|
15
|
23,37
|
-
|
-
|
-
|
-
|
17
|
16
|
26,9
|
-
|
-
|
-
|
-
|
Шаг 2. Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и центрированными скользящими средними (столбец F табл. 3.3). Так, в ячейку F4 поместим =B4-E4.
Составим таблицу 9.9, распределив значения столбца F таблицы 9.8 по кварталам и годам. С помощью статистической функции СРЗНАЧ(диапазон) найдем средние за каждый квартал (по всем годам) оценки сезонной компоненты
Таблица 9.9.
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
19
|
Показатели
|
Год
|
№ квартала,
|
Сумма
|
20
|
I
|
II
|
III
|
IV
|
21
|
|
2008
|
–
|
–
|
0,090
|
1,046
|
|
22
|
2009
|
-0,793
|
-0,172
|
-0,383
|
1,051
|
|
23
|
2010
|
-0,701
|
-0,048
|
-0,409
|
1,530
|
|
24
|
2011
|
-1,205
|
-0,114
|
–
|
–
|
|
25
|
|
|
-0,900
|
-0,111
|
-0,234
|
1,209
|
-0,035
|
26
|
|
|
-0,891
|
-0,102
|
-0,225
|
1,218
|
0,000
|
|
|
-0,008854167
|
|
|
|
|
|
|
|
|
|
|
|
|
В ячейке G25 рассчитаем сумму средних с помощью встроенной статистической функции =СУММ(C25:F25).
Вычислим корректирующий коэффициент: . Рассчитаем скорректированные значения сезонной компоненты и занесем полученные данные в табл. 9.9.
В моделях с сезонной компонентой обычно предполагается, что сезонные воздействия за период взаимопогашаются. В аддитивной модели это выражается в том, что сумма значений сезонной компоненты по всем кварталам должна быть равна нулю (ячейка G26).
Составим табл. 9.10, в которой в столбец С поместим вычисленные . Причем через каждые четыре квартала эти значения будут повторяться.
Таблица 9.10
Шаг 3. Исключим влияние сезонной компоненты, вычитая ее значение из каждого уровня исходного временного ряда. В столбце D табл. 9.10 получим величины которые рассчитываются за каждый момент времени и содержат только тенденцию и случайную компоненту.
Шаг 4. Определим компоненту данной модели аналитическим выравниванием ряда с помощью линейного тренда. Порядок действий:
• Выделим диапазон значений D29:D44, а затем на ленте выбираем вкладку «Вставка», группа Диаграммы и выбираем График с маркерами, помечающими точки данных.
• На полученной диаграмме выделим Область построения диаграммы и на ленте вкладку «Макет», группа «Анализ» команда «Линия тренда», в появившемся спискевыбираем «Дополнительные параметры линии тренда». В диалогом окне выберем Линейная и поставим флажок «показать уравнение на диаграмме». Получим рис. 9.48.
Рис. 9.48. Линейный тренд аддитивной модели
В результате аналитического выравнивания линейный тренд имеет вид: . Подставляя в это уравнение значения =1,2, …, 16, найдем уровни для каждого момента времени (столбец E табл. 9.10).
Шаг 5. Найдем значения уровней ряда. Для этого прибавим к уровням значения сезонной компоненты для соответствующих кварталов (столбец F табл. 9.10). Например, F29 =C29+E29.
На одном графике (рис. 9.49) построим фактические значения уровней временного ряда и теоретические полученные по аддитивной модели.
Рис. 9.49. Фактические и теоретические значения уровней временного ряда, полученные по аддитивной модели
Вычислим абсолютные ошибки (столбец G табл. 9.10): Например, в ячейку G29 поместим =B29-F29. В столбце H найдем
Для оценки качества построенной модели вычислим:
Следовательно, аддитивная модель объясняет 97,4% общей вариации уровней временного ряда средней зарплаты населения по кварталам за 4 года.
Шаг 6. Прогнозирование по аддитивной модели. Прогнозное значение уровня временного ряда в аддитивной модели Для определения воспользуемся уравнением тренда . Получим:
; .
Значения сезонных компонент за соответствующие кварталы: и . Значит, и .
Т.е. в первые два квартала 2012 г. следует ожидать средней заработной платы населения 24,002 тыс. руб. и 25,34 тыс. руб. соответственно.
Пример 9.6. По данным табл. 9.6 построить мультипликативную модель временного ряда, оценить качество построенной модели и сделать прогноз об объеме платных услуг населению на I и II кварталы 2012 г.
Решение:
Шаг 1. Полностью совпадает с методикой построения аддитивной модели. Можно скопировать табл. 9.8 без столбца F на новый лист Excel (табл. 9.11).
Таблица 9.11
|
A
|
B
|
C
|
D
|
E
|
F
|
1
|
|
|
Итого за четыре квартала
|
Скользящая средняя за четыре квартала
|
Центрированная скользящая средняя
|
Оценка сезонной компоненты
|
2
|
1
|
15,43
|
-
|
-
|
-
|
-
|
3
|
2
|
16,97
|
68,95
|
17,2375
|
-
|
-
|
4
|
3
|
17,58
|
70,97
|
17,7425
|
17,490
|
1,005
|
5
|
4
|
18,97
|
72,42
|
18,1050
|
17,924
|
1,058
|
6
|
5
|
17,45
|
73,52
|
18,3800
|
18,243
|
0,957
|
7
|
6
|
18,42
|
75,22
|
18,8050
|
18,593
|
0,991
|
8
|
7
|
18,68
|
77,28
|
19,3200
|
19,063
|
0,980
|
9
|
8
|
20,67
|
79,67
|
19,9175
|
19,619
|
1,054
|
10
|
9
|
19,51
|
82,02
|
20,5050
|
20,211
|
0,965
|
11
|
10
|
20,81
|
84,84
|
21,2100
|
20,858
|
0,998
|
12
|
11
|
21,03
|
86,67
|
21,6675
|
21,439
|
0,981
|
13
|
12
|
23,49
|
89,01
|
22,2525
|
21,960
|
1,070
|
14
|
13
|
21,34
|
91,35
|
22,8375
|
22,545
|
0,947
|
15
|
14
|
23,15
|
94,76
|
23,6900
|
23,264
|
0,995
|
16
|
15
|
23,37
|
-
|
-
|
-
|
-
|
17
|
16
|
26,9
|
-
|
-
|
-
|
-
|
Шаг 2. Найдем оценки сезонной компоненты как частное от деления фактических уровней ряда на центрированные скользящие средние (столбец Fтабл. 3.6). Например, в ячейку F4 поместим =B4/E4.
Составим табл. 9.12, распределив значения столбца F таблицы 9.11 по кварталам и годам. С помощью статистической функции СРЗНАЧ(диапазон) найдем средние за каждый квартал (по всем годам) оценки сезонной компоненты В ячейке G25 рассчитаем сумму средних с помощью статистической функции =СУММ(C25:F25).
Таблица 9.12
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
19
|
Показатели
|
Год
|
№ квартала,
|
Сумма
|
20
|
I
|
II
|
III
|
IV
|
21
|
|
2008
|
–
|
–
|
1,005
|
1,058
|
|
22
|
2009
|
0,957
|
0,991
|
0,980
|
1,054
|
|
23
|
2010
|
0,965
|
0,998
|
0,981
|
1,070
|
|
24
|
2011
|
0,947
|
0,995
|
–
|
–
|
|
25
|
|
|
0,9561
|
0,9945
|
0,9887
|
1,0605
|
3,9999
|
26
|
|
|
0,9562
|
0,9946
|
0,9887
|
1,0606
|
4
|
|
|
|
|
|
|
|
|
Вычислим корректирующий коэффициент: 4/3,9999=1,000032454. Рассчитаем скорректированные значения сезонной компоненты и занесем полученные данные в табл. 9.12.
Так же как и в аддитивной модели считается, что сезонные воздействия за период взаимопогашаются. В мультипликативной модели это выражается в том, что сумма значений сезонной компоненты по всем кварталам должна быть равна числу периодов в цикле. В нашем случае число периодов одного цикла равно 4, как и сумма в ячейке G26.
Составим табл. 9.13, в которой в столбец С поместим вычисленные . Причем через каждые четыре квартала эти значения будут повторяться.
Таблица 9.13
Шаг 3. Разделим каждый уровень исходного ряда на соответствующие значения сезонной компоненты. В результате в столбце D табл. 9.13 получим величины которые содержат только тенденцию и случайную компоненту.
Шаг 4. Определим компоненту в мультипликативной модели выравниванием ряда с помощью линейного тренда. Порядок действий:
• Выделим диапазон значений D29:D44, а затем на ленте выбираем вкладку «Вставка», группа «Диаграммы» и строим График с маркерами, помечающими точки данных.
• На полученной диаграмме выделим Область построения диаграммы и на ленте вкладку «Макет», группа «Анализ» команда «Линия тренда», в появившемся спискевыбираем «Дополнительные параметры линии тренда». В диалогом окне выберем Линейная и поставим флажок «Показать уравнение на диаграмме». Получим рис. 9.50:
Рис. 9.50. Линейный тренд мультипликативной модели
В результате аналитического выравнивания линейный тренд имеет вид: . Подставляя в это уравнение значения =1,2, …, 16, найдем уровни для каждого момента времени (столбец E табл. 9.13).
Шаг 5. Найдем уровни ряда, умножив значения на соответствующие значения сезонной компоненты (столбец F табл. 9.13 Например, в ячейку F29 поместим «=C29*E29».
На одном графике (рис. 9.51 построим фактические значения уровней временного ряда и теоретические полученные по мультипликативной модели.
Вычислим абсолютные ошибки (столбец G табл. 3.8): Например, в ячейку G29 поместим =B29/F29.
В столбце H найдем
Рис. 9.51. Фактические и теоретические значения уровней временного ряда, полученные по мультипликативной модели
Для оценки качества построенной мультипликативной модели вычислим:
Следовательно, мультипликативная модель объясняет 98,05% общей вариации уровней временного ряда средней заработной платы населения по кварталам за 4 года.
Сравнивая показатели детерминации аддитивной и мультипликативной моделей, делаем вывод, что они примерно одинаково аппроксимируют исходные данные.
Шаг 6. Прогнозирование по мультипликативной модели. Прогнозное значение уровня временного ряда в мультипликативной модели есть произведение трендовой и сезонной компонент. Для определения трендовой компоненты воспользуемся уравнением тренда . Получим:
;
.
Значения сезонных компонент за соответствующие кварталы равны: и . Таким образом,
;
.
Т.е. в первые два квартала 2012 г. следует ожидать среднюю заработную плату населения 23,76 тыс. руб и 25,26 тыс. руб. соответственно.
Таким образом, аддитивная и мультипликативная модели дают примерно одинаковый результат по прогнозу.