Иванов А.Н. Построение эконометрических моделей и прогнозирование в MS Excel: сборник лабораторных работ
Подождите немного. Документ загружается.
Тема лабораторной работы: Нелинейные регрессионные модели: идентификация, прогноз, графическое представление.
7из9
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
x
2
x
1
36,49 18,55 14,00 83,93 15,52 8,92 10,78
0,6 201 127 103 334 111 71 83
1,0 190 116 91 323 100 59 72
1,8 172 98 74 305 83 42 55
2,1 167 93 69 300 77 36 49
1,6 176 102 78 309 86 46 58
1,0 190 116 91 323 100 59 72
0,7 198 124 100 331 108 68 80
Рис. 5.6
6.2. В верхнюю левую ячейку введем формулу и автоматически заполним
всю таблицу (протягиванием ячейки с формулой на область заполнения).
=-17,337145247768+42,6868016051168*КОРЕНЬ(«левая ячейка строки
значений фактора »)-50,8957451072794*(«верхняя ячейка столбца зна-
чений фактора »)
1
x
2
x
6.3. С помощью Мастера диаграмм строим поверхность двухфакторной
нелинейной регрессии, предварительно выделив вспомогательную таблицу
(рис. 5.7).
3
6
,
4
9
1
8
,
5
5
1
4
,
0
0
8
3
,
9
3
1
5
,
5
2
8
,9
2
1
0
,
7
8
0
,
6
1
,
8
1
,
6
0
,
7
0
50
100
150
200
250
300
350
ВРП на душу населения,
тыс. руб.
Инвестиции в основной
капитал, тыс. руб.
Уровень
безработицы,
%
Модель двухфакторной нелинейной регрессии
300-350
250-300
200-250
150-200
100-150
50-100
0-50
Рис. 5.7
Тема лабораторной работы: Нелинейные регрессионные модели: идентификация, прогноз, графическое представление.
8из9
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
К
К
К
о
о
о
н
н
н
т
т
т
р
р
р
о
о
о
л
л
л
ь
ь
ь
н
н
н
ы
ы
ы
е
е
е
в
в
в
о
о
о
п
п
п
р
р
р
о
о
о
с
с
с
ы
ы
ы
и
и
и
у
у
у
п
п
п
р
р
р
а
а
а
ж
ж
ж
н
н
н
е
е
е
н
н
н
и
и
и
я
я
я
1. Какими приемами можно свести модель нелинейной регрессии (нели-
нейной по объясняющим переменным, но линейной по параметрам и нели-
нейной внутренне линейной по параметрам) к модели линейной регрессии?
2. Можно ли графически представить трехфакторную нелинейную рег-
рессионную модель? Как это сделать?
3. Сколько будет содержать алгебраических уравнений система нормаль-
ных уравнений для трехфакторной нелинейной регрессионной модели, отно-
сящейся к классу линейных моделей?
4. Как строится вспомогательная таблица для построения поверхности с
помощью Мастера диаграмм?
5. Сформулируйте необходимое условие существования экстремума
функции многих переменных.
6. Можно ли применять метод наименьших квадратов к нелинейной рег-
рессионной модели, не сводя ее к линейной?
7. Приведите примеры нелинейных регрессионных моделей внутренне
линейных по параметрам. Какие линеаризирующие преобразования к ним
необходимо применять? (примеры с лекций не использовать)
8. Что такое поверхность (плоскость) регрессии?
9. Напишите нелинейную регрессионную модель спроса (предложения).
10. Аргументируйте следующий тезис: «На любую экономическую сис-
тему влияют множество факторов, а поэтому исходные данные (значения
экономических показателей) будут выражать нелинейную зависимость. Сле-
довательно, построение линейных регрессионных моделей недопустимо ни
при каких условиях».
В
В
В
а
а
а
р
р
р
и
и
и
а
а
а
н
н
н
т
т
т
ы
ы
ы
и
и
и
н
н
н
д
д
д
и
и
и
в
в
в
и
и
и
д
д
д
у
у
у
а
а
а
л
л
л
ь
ь
ь
н
н
н
ы
ы
ы
х
х
х
з
з
з
а
а
а
д
д
д
а
а
а
н
н
н
и
и
и
й
й
й
1.
ε
+++=
22
2
110
xaxaay
;
2.
ε
+++=
22
1
1
0
xa
x
a
ay ;
3.
ε
+++=
2
2
110
ln
x
a
xaay
;
4.
;
ε
+++=
2210
ln
1
xaeaay
x
5.
ε
+++=
22
1
1
0
ln
ln
xa
x
a
ay ;
6.
ε
⋅⋅⋅=
2
1
2
1
0
1
a
a
x
xay
;
15.
ε
+++=
2
2
1
1
0
x
a
x
a
ay
;
16.
ε
+++=
2
2
2
110
ln
x
a
xaay
;
17.
ε
+++=
2
2
2
1
1
0
ln
x
a
x
a
ay
;
18.
ε
+++=
2
2
3
110
x
a
xaay
;
19.
ε
+++=
2
2
1
1
0
ln
x
a
x
a
ay
;
20.
ε
+++=
2
2
1
1
0
ln x
a
x
a
ay
;
Тема лабораторной работы: Нелинейные регрессионные модели: идентификация, прогноз, графическое представление.
9из9
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
7.
()
ε
⋅⋅=
2
1
2
1
0
a
a
x
x
a
y ;
8.
ε
+
++=
22110
lnln xaxaay
;
9.
ε
+++
=
22110
ln
1
xaxaa
y
;
10.
ε
+++
=
22110
4
xaxaa
y
;
11.
ε
+++
=
22110
lnln
1
xaxaa
y
;
12.
ε
+++=
2
22
2
1
1
0
xa
x
a
ay ;
13.
ε
+++
=
22
2
110
1
xaxaa
y
;
14.
ε
+++
=
2
22110
ln
1
xaxaa
y
;
21.
ε
+++=
2
2
1
1
0
x
a
x
a
ay
;
22.
ε
+++=
2
2
1
1
0
lnln x
a
x
a
ay
;
23.
ε
+++=
2210
1
xaeaay
x
;
24.
ε
+++=
2
2
10
1
x
a
eaay
x
;
25.
ε
+++=
2
2
10
ln
1
x
a
eaay
x
;
26. ;
ε
+++=
21
210
xx
eaeaay
27.
ε
⋅⋅⋅=
3
2
2
1
0
2
1
a
a
xxay .
Тема лабораторной работы: Регрессионные модели с фиктивными объясняющими переменными.
1из13
Мультиколлинеарность, автокорреляция.
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
Л
Л
Л
А
А
А
Б
Б
Б
О
О
О
Р
Р
Р
А
А
А
Т
Т
Т
О
О
О
Р
Р
Р
Н
Н
Н
А
А
А
Я
Я
Я
Р
Р
Р
А
А
А
Б
Б
Б
О
О
О
Т
Т
Т
А
А
А
№
№
№
6
6
6
Т
Т
Т
е
е
е
м
м
м
а
а
а
:
:
:
Регрессионные модели с фиктивными объясняющими переменны-
ми. Мультиколлинеарность
, автокорреляция.
Ц
Ц
Ц
е
е
е
л
л
л
ь
ь
ь
:
:
:
1. Научиться строить модель с фиктивными объясняющими пере-
менными, описывающую экономическую систему и производить ее иденти-
фикацию. 2. Научиться проверять эконометрические модели на наличие:
мультиколлинеарности факторов и автокорреляции случайной составляю-
щей.
Ф
Ф
Ф
о
о
о
р
р
р
м
м
м
а
а
а
о
о
о
т
т
т
ч
ч
ч
е
е
е
т
т
т
н
н
н
о
о
о
с
с
с
т
т
т
и
и
и
.
.
.
Представить преподавателю подробное решение за-
дачи с выводами в MS Excel. Оформление должно быть аналогично разо-
бранному примеру. Письменно ответить на контрольные вопросы на рабочем
листе MS Excel после решения задачи. Вариант работы определяет препо-
даватель.
Замечание 6.1. Ячейка,
содержащая формулу, будет отмечена серым цве-
том.
Задача 6.1. Имеются данные зависимости заработной платы группы лю-
дей от их потребительских расходов, пола и образования (рис. 6.1).
№
пп
Пол
Заработная
плата, тыс. руб.
Потребительские
расходы
12 4 5
1 жен. 9,12 6,1
2 муж. 8,09 6,3
3 жен.7,48
4 жен. 8,47 4,9
5 муж. 10,87 5,4
6 муж. 10,74 6,2
7 жен. 10,08 6,7
8 муж. 6,82 4,3
9 муж. 6,97 4,2
10 жен. 12,51 7,5
11 жен. 9,67 6,3
12 жен. 8,98 5,2
13 муж. 12,63 9,1
14 муж. 30,27 18,6
15 жен. 21,23 14,3
среднее специальное
высшее
начальное профессионально
5
е
высшее
среднее специальное
начальное профессионально
е
начальное профессионально
е
среднее специальное
высшее
высшее
Образование
3
начальное профессионально
е
среднее специальное
начальное профессионально
е
среднее специальное
высшее
Рис. 6.1
Необходимо:
1) определить фиктивные переменные и вид модели с переменной струк-
турой;
2) произвести идентификацию модели;
Тема лабораторной работы: Регрессионные модели с фиктивными объясняющими переменными.
2из13
Мультиколлинеарность, автокорреляция.
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
3) рассчитать общую, факторную и остаточную дисперсии;
4) вычислить коэффициент детерминации;
5) вычислить стандартную ошибку регрессии;
6) вычислить стандартные ошибки параметров регрессии;
7) проверить общее качество модели при уровне значимости, равном 0,05;
8) проверить существенность влияния фактора «пол» на размер заработ-
ной платы человека;
9) проверить модель на наличие мультиколлинеарности факторов;
10) проверить модель на наличие автокорреляции случайной составляю-
щей.
А
А
А
л
л
л
г
г
г
о
о
о
р
р
р
и
и
и
т
т
т
м
м
м
р
р
р
е
е
е
ш
ш
ш
е
е
е
н
н
н
и
и
и
я
я
я
з
з
з
а
а
а
д
д
д
а
а
а
ч
ч
ч
и
и
и
1. Определим фиктивные переменные и вид модели с переменной струк-
турой.
1.1. В качестве фиктивных переменных будем использовать булевы пере-
менные.
1.2. Качественный фактор «пол» имеет
2=
k
градации (мужской и жен-
ский), следовательно, вводим
1121 =−=−
k
фиктивную переменную ,
принимающую значение нуль, если данный признак отсутствует, и единицу,
если он присутствует.
1i
z
⎩
⎨
⎧
=
женщина. человек й- если ,0
мужчина; человек й- если ,1
1
i
i
z
i
1.3. Качественный фактор «образование» имеет 3=
k
градации (началь-
ное профессиональное, среднее специальное, высшее), следовательно, вво-
дим 2131 =−=−
k
фиктивные переменные , .
2i
z
3i
z
⎩
⎨
⎧
=
случаях.остальных всех о ,0
е;образовани высшее имеет человек й- если ,1
2
в
i
z
i
⎪
⎩
⎪
⎨
⎧
=
случаях.остальных всех о ,0
е;образовани еспециально среднее
имеет человек й- если ,1
3
в
i
z
i
1.4. Записываем регрессионную модель с учетом качественного признака
и без его учета.
Пусть переменная – потребительские расходы, тогда регрессионная
модель для заработной платы будет иметь вид:
1
x
ε
++=
110
xaay
;
Тема лабораторной работы: Регрессионные модели с фиктивными объясняющими переменными.
3из13
Мультиколлинеарность, автокорреляция.
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
регрессионная модель для заработной платы с переменной структурой:
ε
+++++=
332211110
zczczcxaay
.
2. Произведем идентификацию модели (найдем оценки параметров рег-
рессионной модели).
2.1. Представим таблицу исходных данных для удобства расчетов сле-
дующим образом (рис. 6.2).
№
пп
Заработная
плата (y),
тыс. руб.
Потреби-
тельские
расходы (x
1
)
Пол (z
1
)
Высшее
образо-
вание (z
2
)
Среднее
специальное
образование
(z
3
)
12 3 45 6
1 9,12 6,1 0 1 0
2 8,09 6,3 1 0 1
37,48 5 0 0 0
4 8,47 4,9 0 0 0
5 10,87 5,4 1 1 0
6 10,74 6,2 1 0 1
7 10,08 6,7 0 1 0
8 6,82 4,3 1 0 0
9 6,97 4,2 1 0 0
10 12,51 7,5 0 0 1
11 9,67 6,3 0 0 0
12 8,98 5,2 0 0 1
13 12,63 9,1 1 0 1
14 30,27 18,6 1 1 0
15 21,23 14,3 0 1 0
Рис. 6.2
2.2. Представим регрессионную модель с переменной структурой в мат-
ричной форме
eXa
Y
+= .
2.2.1. Составим матрицы ,
Y
X
, по таблице исходных данных
(рис. 6.3).
a
Тема лабораторной работы: Регрессионные модели с фиктивными объясняющими переменными.
4из13
Мультиколлинеарность, автокорреляция.
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
9,12 1 6,1 0 1 0
8,09 1 6,3 1 0 1
7,48 15000
8,47 1 4,9 0 0 0
10,87 1 5,4 1 1 0
10,74 1 6,2 1 0 1
a
0
10,08 1 6,7 0 1 0
a
1
Y
=6,82
X
=14,3100
a
=
с
1
6,97 1 4,2 1 0 0
с
2
12,51 1 7,5 0 0 1
с
3
9,67 1 6,3 0 0 0
8,98 1 5,2 0 0 1
12,63 1 9,1 1 0 1
30,27 1 18,6 1 1 0
21,23 1 14,3 0 1 0
Рис. 6.3
2.3. Вектор-столбец оценок параметров модели найдем по формуле a
(
)
YXXXa
TT
1−
=
.
2.3.1. Сначала проверим выполнимость предпосылки множественного
регрессионного анализа:
(
)
0det ≠XX
T
.
2.3.2. Находим матрицу , транспонированную к матрице
T
X
X
(рис. 6.4).
111111111111111
6,1 6,3 5 4,9 5,4 6,2 6,7 4,3 4,2 7,5 6,3 5,2 9,1 18,6 14,3
X
T
=
010011011000110
100010100000011
010001000101100
Рис. 6.4
2.3.2. Находим матрицу
(
)
XX
T
и вычисляем ее определитель (рис. 6.5).
15 110,1 7 5 5
110,1 1030,77 54,1 51,1 34,3
det
(
X
T
X
)
=
66765,4
(
X
T
X
)
=
754,17 2 3
551,12 5 0
534,33 0 5
Рис. 6.5
Тема лабораторной работы: Регрессионные модели с фиктивными объясняющими переменными.
5из13
Мультиколлинеарность, автокорреляция.
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
Видим, что
(
)
0det ≠XX
T
, следовательно, вектор-столбец оценок пара-
метров модели существует.
2.3.3. Находим матрицу
(
)
1−
XX
T
(рис. 6.6).
0,3861 -0,0309 -0,0836 -0,0369 -0,1239
-0,0309 0,00674 -0,006 -0,0356 -0,0117
(
X
T
X
)
-1
=
-0,0836 -0,006 0,2831 0,03163 -0,0451
-0,0369 -0,0356 0,03163 0,5879 0,262
-0,1239 -0,0117 -0,0451 0,262 0,43157
Рис. 6.6
2.3.4. Находим матрицу
(
)
TT
XXX
1−
(рис. 6.7).
0,161 -0,016 0,232 0,235 0,099 -0,013 0,142 0,17 0,173 0,03 0,191 0,101 -0,103 -0,309 -0,093
-0,025 -0,006 0,003 0,002 -0,036 -0,007 -0,021 -0,008 -0,009 0,008 0,012 -0,008 0,013 0,053 0,03
(
X
T
X
)
-1
X
T
=
-0,089 0,117 -0,114 -0,113 0,199 0,117 -0,092 0,174 0,174 -0,174 -0,121 -0,16 0,1 0,12 -0,138
0,334 0,033 -0,215 -0,211 0,391 0,036 0,313 -0,158 -0,155 -0,042 -0,261 0,04 -0,067 -0,079 0,042
0,066 0,189 -0,183 -0,181 0,03 0,19 0,059 -0,22 -0,218 0,22 -0,198 0,247 0,156 -0,125 -0,03
Рис. 6.7
2.3.5. Находим матрицу
(
)
YXXXa
TT
1−
=
(рис. 6.8).
0,30546
a
0
=
0,30546
1,50172
a
1
=
1,50172
a
=
(
X
T
X
)
-1
X
T
Y
=
0,39514
с
1
=
0,39514
0,50293
с
2
=
0,50293
-0,2543
с
3
=
-0,2543
Рис. 6.8
Замечание 6.2. Для выделения нужного числа из матрицы, используем
функцию
ИНДЕКС в MS Excel.
Замечание 6.3. Найденные значения параметров модели , , ,
есть оценки данных параметров, которые обозначаем с крышкой.
0
a
1
a
2
a
3
a
Вывод. Четырехфакторное уравнение регрессии с переменной структу-
рой имеет вид
4321
25430502930395140501721305460 x,-x,x,x,,y +++=
.
Тема лабораторной работы: Регрессионные модели с фиктивными объясняющими переменными.
6из13
Мультиколлинеарность, автокорреляция.
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
3. Для нахождения общей, факторной и остаточной дисперсии дополним
таблицу исходных данных еще четырьмя столбцами: y-оценка, общей, фак-
торной и остаточной суммами квадратов (рис. 6.9).
№
пп
Заработная плата
(y), тыс. руб.
Потребительские
расходы (x
1
)
Пол (z
1
)
Высшее
образование (z
2
)
Среднее
специальное
образование (z
3
)
y-оценка
Общая сумма
квадратов
Факторная сумма
квадратов
Остаточная сумма
квадратов
12345678910
1 9,12 6,1 0 1 0 9,9689 6,1273 2,6454 0,7206
2 8,09 6,3 1 0 1 9,9071 12,287 2,8502 3,3018
3 7,48 5 0 0 0 7,814 16,936 14,298 0,1116
4 8,47 4,9 0 0 0 7,6639 9,7677 15,456 0,6498
5 10,87 5,4 1 1 0 9,3128 0,5261 5,2099 2,4249
6 10,74 6,2 1 0 1 9,7569 0,7316 3,3798 0,9664
7 10,08 6,7 0 1 0 10,87 2,2962 0,5263 0,6239
8 6,82 4,3 1 0 0 7,158 22,804 19,69 0,1142
9 6,97 4,2 1 0 0 7,0078 21,394 21,045 0,0014
10 12,51 7,5 0 0 1 11,314 0,8366 0,0791 1,4304
11 9,67 6,3 0 0 0 9,7663 3,7069 3,3454 0,0093
12 8,98 5,2 0 0 1 7,8601 6,84 13,952 1,2543
13 12,63 9,1 1 0 1 14,112 1,0705 6,3331 2,196
14 30,27 18,6 1 1 0 29,135 348,74 307,66 1,2871
15 21,23 14,3 0 1 0 22,283 92,827 114,23 1,1087
546,89 530,69 16,201
Рис. 6.9
3.1. Находим среднее значение , используя функцию
СРЗНАЧ. y
Вызов функции:
MS Excel – Вставка – Функция… – Статистические
y-среднее = 11,6
3.2. В столбец «y-оценка» вставляем формулу:
=«фиксированная ячейка оценки параметра »+«фиксированная
ячейка оценки параметра »*x1+«фиксированная ячейка оценки пара-
метра »*z1+«фиксированная ячейка оценки параметра
»*z2+«фиксированная ячейка оценки параметра »*z3
0
a
1
a
1
c
2
c
3
c
3.3. В столбец «общая сумма квадратов» вставляем формулу:
=СТЕПЕНЬ(y – «y-среднее»;2)
Тема лабораторной работы: Регрессионные модели с фиктивными объясняющими переменными.
7из13
Мультиколлинеарность, автокорреляция.
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС
3.4. В столбец «факторная сумма квадратов» вставляем формулу:
=СТЕПЕНЬ(«y-оценка» - «y-среднее»;2)
3.5. В столбец «остаточная сумма квадратов» вставляем формулу:
=СТЕПЕНЬ(y – «y-оценка»;2)
3.6. Суммированием по трем столбцам (8, 9, 10) находим общую, фактор-
ную и остаточную суммы квадратов соответственно. Используем функцию
СУММ.
3.7. Определяем число степеней свободы общей, факторной и остаточной
сумм квадратов соответственно.
Число степеней свободы общей суммы квадратов: . 141151 =−=−n
Число степеней свободы факторной суммы квадратов: 4=
k
.
Число степеней свободы остаточной суммы квадратов:
1014151 =−−=−−
k
n .
3.7. Дисперсии находим делением соответствующих сумм квадратов на
соответствующие им числа степеней свободы.
39,06
132,7
1,62
общая дисперсия =
факторная дисперсия =
остаточная дисперсия =
4. Вычисляем коэффициент детерминации по формуле:
=1 – «остаточная сумма квадратов»/«общая сумма квадратов»
0,97
коэффициент детерминации =
5. Вычисляем стандартную ошибку регрессии по формуле:
=КОРЕНЬ(1/(n-k-1)*«остаточная сумма квадратов»)
1,273стандартная ошибка регрессии =
6. Вычислим стандартные ошибки параметров регрессии по формуле:
=КОРЕНЬ(1/(n-k-1)*«остаточная сумма квадра-
тов»*ИНДЕКС(«диапазон матрицы
(
)
1−
XX
T
»;i+1;i+1))
i
– номер коэффициента регрессии.