Реализация задания на компьютере с помощью ППП Ехсеl 1 страница

 

Здесь на примерах будут показаны возможности ППП Ехсеl при синтезе моделей временных рядов.

Пример 5.1. В табл. 5.1 приведены данные, отражающие спрос на некоторый товар за восьмилетний период (усл.ед.).

Таблица 5.1

Год, t
Спрос, уt

Необходимо создать адекватную модель временного ряда и оценить прогноз индивидуального значения спроса на товар на момент t = 9 (девятый год) с надежностью 0,95.

Предварительный анализ данных.

1) Выявление аномальных наблюдений.

Из графика (рис.5.1) видно, что под «подозрение» в смысле аномальности попадает второй уровень (по отношению к третьему уровню).

 

Рис. 5.1

 

Проверка с помощью критерия Ирвина (5.3) показала, что этот уровень действительно является аномальным, т.к. . Для вычисления S_y была использована функция СТАНДОТКЛОНП. Поэтому этот уровень заменяется средним значением соседних уровней (первого и третьего), т.е. принимается у ₃ = 252.

2) Проверка наличия тренда.

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

Гипотезу о равенстве дисперсий проверим с помощью F -теста, который можно найти среди инструментов Анализа данных (рис.5.2)

 

Рис. 5.2

Вводим данные для выполнения F -теста, указывая интервал для первой и второй переменных (рис.5.3). Результат выполнения теста приведен на рис.5.4. Анализируя результаты выполнения двухвыборочного F -теста для проверки гипотезы о равенстве дисперсий, приходим к выводу, что выборочные дисперсии ( и ) различаются незначительно: F_набл (1,34) < F_кр (9,28).

 

Рис. 5.3

 

Рис. 5.4

 

При использовании процедуры «Двухвыборочный F-тест для дисперсии» следует иметь ввиду, что если F_набл > 1, то F_кр = и рассматривается правосторонняя критическая область. При этом в качестве альтернативной гипотезы рассматривается гипотеза Н ₁: > , т.е. различие дисперсий утверждается при F_набл > F_кр. Если же F_набл < 1, то F_кр = и рассматривается левосторонняя альтернативная гипотеза: Н ₁: < , при этом дисперсии выборок различаются, если F_набл < F_кр.

Проверка гипотезы о равенстве средних этих подвыборок осуществляется с помощью t -статистики Стьюдента (5.5). Для чего можно воспользоваться инструментом Анализа данных: Двухвыборочный t-тест с одинаковыми дисперсиями (рис. 5.5). Вводим данные. Результат выполнения t-теста приведен на рис. 5.6. Так как , то делаем вывод о том, что тренд присутствует.

 

Рис. 5.5

 

Рис. 5.6

 

В качестве подтверждения наличия тенденции можно воспользоваться коррелограммой (рис. 5.7). Так как наиболее высоким оказался коэффициент автокорреляции первого порядка, то временной ряд содержит тенденцию. Кроме того с увеличением лага корреляция монотонно убывает, что свидетельствует о стационарности ряда, вероятностные свойства которого не изменяются во времени. Коэффициенты автокорреляции были получены с помощью статистической функции КОРРЕЛ.

 

Рис. 5.7

 

3) Сглаживание временных рядов.

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

Для механического сглаживания применим метод простой скользящей средней с интервалом сглаживания m = 3 года. Результаты сглаживания представлены на рис. 5.8.

 

Рис. 5.8

Из графика сглаженного ряда видно, что наиболее подходящим трендом является линейный тренд. Уравнение линейного тренда, полученного с помощью функции ЛИНЕЙН (рис. 5.9), имеет вид: . Высокий коэффициент детерминации R ² = 0,98 и его статистическая значимость (F (211,7) > F_кр (7,7)) свидетельствуют о высоком качестве уравнения тренда.

 

Рис. 5.9

 

На рисунке также представлено прогнозное значение спроса на товар на 9-ый год и его доверительный интервал, который был получен по формуле (5.15).

 

Пример 5.2. Имеются условные данные об объемах потребления электроэнергии (млн кВт ч) жителями региона за 16 кварталов (табл. 5.2).

Таблица 5.2

t
yt	6,0	4,4	5,0	9,0	7,2	4,8	6,0	10,0	8,0	5,6	6,4	11,0	9,0	6,6	7,0	10,8

 

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

Нанесем эти значения на график (рис. 5.10).

 

Рис. 5.10

Из графика видно, что во временном ряду присутствует сезонная составляющая и есть тенденция увеличения расхода. Подтвердим эти предварительные выводы статистическими методами.

Для этого построим коррелограмму (рис. 5.11). Построение было выполнено на компьютере по аналогии примера 5.1.

 

Рис. 5.11

 

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

По графику (рис. 5.10) можно установить наличие приблизительно равной амплитуды колебаний, что свидетельствует о возможной аддитивной модели этого ряда.

Построение аддитивной модели.

Шаг 1. Проведем выравнивание исходных уровней ряда методом простой скользящей средней. Длина интервала сглаживания должна быть равна периоду колебаний, т.е. m = 4. В этом случае в формуле скользящей средней будут использоваться пять уровней с весовыми коэффициентами: (1/2, 1, 1, 1, 1/2) (см.п. 5.1.2), т.е. первое сглаженное значение будет для третьего уровня, второе для четвертого и т.д.

 

Рис. 5.12

 

Шаг 2. Найдем оценки сезонной компоненты (4 столбец табл. рис. 5.12), как разность между фактическими уровнями ряда y_t (2 столбец) и центрированными скользящими средними (3 столбец).

Используем эти оценки для расчета сезонной компоненты S (рис. 5.13). Для этого найдем средние за каждый квартал (по всем годам) оценки сезонной компоненты S_i. Обычно предполагается, что сезонные воздействия за период взаимопогашаются. В аддитивной модели это выражается в том, что сумма значений сезонной компоненты по всем кварталам должна быть равна нулю.

Для данной модели имеем: 0,6 – 1,958 – 1,275 + 2,708 = 0,075. Определим корректирующий коэффициент: k = 0,075/4 = 0,01875.

Скорректированное значение сезонной компоненты (последняя строка табл. рис. 5.13) вычисляется по формуле: , i = 1, 2, 3, 4.

 

 

Рис. 5.13

 

Шаг 3. Вычтем сезонные компоненты из каждого уровня временного ряда, получим F + E = Y – S (4 столбец табл. рис. 5.14).

 

Рис. 5.14

 

Шаг 4. Определим компоненту F. Для этого по значениям F + E рассчитаем линейный тренд с использованием функции ЛИНЕЙН: . Подставляя в это уравнение t = 1, 2, … получим 5 столбец.

 

Шаг 5. Найдем значения уровней ряда, полученных по аддитивной модели. Для этого прибавим к уровням F их сезонные составляющие Y_{t мод} = F + S (6 столбец).

 

Шаг 6. Найдем ошибки (7 столбец). Из рис. 5.14 следует, что коэффициент детерминации R ² = 0,985. Это свидетельствует о высоком качестве аддитивной модели, т.е. 98,5% общей вариации уровней временного ряда объясняется этой моделью.

Спрогнозируем потребление электроэнергии в течение первого полугодия следующего (пятого) года, т.е. необходимо найти Y (17) + Y (18):

;

.

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

(9,458 + 7,086) = 16,544 млн кВт ч.

 

Пример 5.3. Задачу примера 5.2 решим с использованием фиктивных переменных.

Модель будет иметь вид:

, (5.17)

где - линейный тренд; х ₁, х ₂, х ₃ – фиктивные переменные.

Фиктивные переменные принимают следующие значения:

 

Результаты оценивания уравнения множественной регрессии (5.17) МНК представлены на рисунке 5.15. Для определения коэффициентов была использована функция ЛИНЕЙН. Из рисунка видно, что адекватность модели статистическим данным достаточно высокая. Подтверждением высокого качества модели также является близость коэффициента детерминации R ² к единице (R ² = 0,985). Кроме того, все коэффициенты статистически значимы.

 

Пример 5.4. Поквартальные данные о прибыли компании (тыс. у.е.) представлены в таблице 5.3.

Таблица 5.3

Год	Квартал
I	II	III	IV

 

Рис. 5.15

 

Из графика (рис. 5.16) видно, что ряд имеет сезонные колебания (период = 4) и общую убывающую тенденцию. Причем амплитуда колебаний также уменьшается. Это свидетельствует о том, что здесь необходимо использовать мультипликативную модель: .

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

 

Шаг 1. Выравнивание исходных уровней ряда методом простой скользящей средней. Этот шаг полностью совпадает с шагом 1 в аддитивной модели. Результаты выравнивания представлены на рис. 5.17.

Рис. 5.16

 

Рис. 5.17

 

Шаг 2. Оценка сезонной компоненты (4 столбец табл. рис. 5.17), как отношение значений фактических уровней ряда y_t (2 столбец) и центрированных скользящих средних (3 столбец). Средние квартальные значения сезонной компоненты представлены на рис. 5.18. Для определения корректирующего коэффициента воспользуемся соотношением: k = 4/ = 0,9948. Скорректированные значения сезонной компоненты (последняя строка таблицы) были получены по формуле: , i = 1, 2, 3, 4.

Шаг 3. Удалим сезонную компоненту из ряда: = Y / S (4 столбец табл. рис. 5.19).

 

Рис. 5.18

 

 

Рис. 5.19

 

Шаг 4. Определим компоненту F. Для этого по значениям рассчитаем линейный тренд с использованием функции ЛИНЕЙН: . Подставляя в это уравнение t = 1, 2, … получим 5 столбец.

 

Шаг 5. Найдем значения уровней ряда, полученных по мультипликативной модели. Для этого прибавим к уровням F их сезонные составляющие Y_{t мод} = (6 столбец).

Из рис. 5.20 видно, что совпадение модельных и экспериментальных данных достаточно высокое.

Рис. 5.20

Свидетельством высокого качества модели также является близкое к единице значение коэффициента детерминации R ² = 0,96.

 

5.3. Контрольные задания

 

Задача 1. Экспорт, импорт, внешнеторговый оборот Австралии и Бельгии за 1961 – 1995 гг. характеризуются данными, представленными в таблице.

Год	Австралия, млн шиллингов	Бельгия, млн франков
	Экспорт	Импорт	Внешнеторговый оборот	Экспорт	Импорт	Внешнеторговый оборот
Год	Австралия, млн шиллингов	Бельгия, млн франков
	Экспорт	Импорт	Внешнеторговый оборот	Экспорт	Импорт	Внешнеторговый оборот

 

 

Задание.

 

11. Постройте график временного ряда;

12. Проверьте на аномальность уровней ряда и при их наличии произвести корректировку;

13. Проверьте наличие тренда;

14. Постройте коррелограмму. Охарактеризуйте структуру этого ряда;

15. Осуществите механическое сглаживание ряда с помощью скользящей средней;

16. Проведите расчет параметров трендов разной формы;

17. Оцените статистическую значимость трендов через F – критерий, значимость параметров трендов – через t – критерий;

18. Выберите лучшую форму тренда и выполните по ней интервальный прогноз для t = t _прогн.

 

Вариант 1.1

y_t - экспорт Австралии;

t _прогн – 1996 год; Уровень значимости = 0,01.

 

Вариант 1.2

y_t - импорт Австралии;

t _прогн – 1997 год; Уровень значимости = 0,03.

 

Вариант 1.3

y_t - внешнеторговый оборот Австралии;

t _прогн – 1998 год; Уровень значимости = 0,05.

 

Вариант 1.4

y_t - экспорт Бельгии;

t _прогн – 1996 год; Уровень значимости = 0,01.

 

Вариант 1.5

y_t - импорт Бельгии;

t _прогн – 1997 год; Уровень значимости = 0,03.

 

Вариант 1.6

y_t - внешнеторговый оборот Бельгии;

t _прогн – 1998 год; Уровень значимости = 0,05.

 

Задача 2. Реальный объем произведенного в России ВВП по кварталам в среднегодовых ценах 1995 года (трлн.рублей) представлен в таблице (данные Федеральной службы государственной статистики: www.gks.ru).

 

 

1 квар-тал	2 квар-тал	3 квар-тал	4 квар-тал	1 квар-тал	2 квар-тал	3 квар-тал	4 квар-тал
Валовой внутренний продукт в рыночных ценах	330,1	341,6	395,7	361,1	322,8	330,1	374,0	350,1
в основных ценах (без снятия косвенно-измеряемых услуг финансового посредничества)	304,6	318,0	371,0	341,5	300,9	309,6	353,7	333,9
в том числе:
Производство товаров	126,0	136,0	187,3	147,6	121,8	127,7	170,2	141,7
из них:
Пpомышленность	93,3	90,3	93,2	95,3	92,7	88,3	87,4	94,0
Сельское хозяйство	5,5	11,7	57,5	20,9	5,2	11,0	53,5	20,9
Стpоительство	25,4	32,0	34,5	29,4	22,2	26,5	27,4	24,8
1 квар-тал	2 квар-тал	3 квар-тал	4 квар-тал	1 квар-тал	2 квар-тал	3 квар-тал	4 квар-тал
Производство услуг	178,6	182,0	183,7	193,9	179,1	181,9	183,5	192,2
в том числе:
Рыночные услуги	142,4	145,5	146,9	156,8	141,6	144,3	145,8	154,6
из них:
Транспорт и связь	40,6	41,2	41,4	39,7	39,1	39,2	39,2	38,1
Торговля (оптовая, розничная), общественное питание и заготовки	64,7	67,3	71,0	79,4	66,1	69,3	72,8	79,4
Нерыночные услуги	36,2	36,5	36,8	37,1	37,5	37,6	37,7	37,6

 

 

1 квар-тал	2 квар-ал	3 квар-тал	4 квар-тал	1 квар-тал	2 квар-тал	3 квар-тал	4 квар-тал
Валовой внутренний продукт в рыночных ценах	321,4	327,3	384,7	362,6	316,7	324,2	350,8	329,7
в основных ценах (без снятия косвенно-измеряемых услуг финансового посредничества)	301,2	307,0	362,9	345,3	297,5	305,0	331,8	314,9
в том числе:
Производство товаров	118,4	124,0	176,7	146,4	119,2	122,2	152,1	130,4
из них:
Пpомышленность	92,0	88,0	91,0	99,7	94,3	86,5	81,3	90,9
Сельское хозяйство	4,8	10,4	57,1	20,6	4,6	10,5	43,5	16,8
Стpоительство	20,0	23,9	26,9	24,6	18,9	23,7	25,6	21,2
Производство услуг	182,8	183,0	186,2	198,9	178,3	182,8	179,7	184,5
в том числе:
Рыночные услуги	145,9	146,2	149,3	161,9	140,5	145,3	141,7	148,3
из них:
Транспорт и связь	38,5	38,0	38,3	37,9	37,4	36,5	37,3	36,3
Торговля (оптовая, розничная), общественное питание и заготовки	70,1	71,8	75,8	84,9	69,1	70,7	69,2	73,4
Нерыночные услуги	36,9	36,8	36,9	37,0	37,8	37,5	38,0	36,2