Пример решения эконометрической задачи в Excel

Ниже приведено условие задачи и текстовая часть решения.  Закачка полного решения, файлы word+Excel в архиве rar, начнется автоматически через 10 секунд. 

Задание №1. Имеются следующие данные: Q(Y) – объем продаж (ежемесячный); P(X1) – цена за единицу изделия. X2 – структурная фиктивная переменная.

Месяц	Q(Y) (шт.)	P(X1) (руб.)	X2	Месяц	Q (шт.)	P (руб.)	X2
1	98	10,0	0	8	113	13,0	1
2	100	11,0	0	9	116	13,0	1
3	103	12,5	0	10	118	13,8	1
4	105	12,5	0	11	121	14,2	1
5	80	14,6	0	12	123	14,4	1
6	87	14,6	0	13	126	15,0	1
7	94	14,9	0	14	128	16,1	1

В период с 5 по 7 месяц на фирме проходило реформирование. С помощью регрессии Q(t) на P(t) определить, сказалась или нет реформа на деятельности фирмы.

Решение.

Определим вектор оценок коэффициентов регрессии. Согласно методу наименьших квадратов, вектор s получается из выражения: s = (X^TX)^-1X^TY

К матрице с переменными X_j добавляем единичный столбец:

Матрица Y

Матрица X^T

Умножаем матрицы, (X^TX)

В матрице,  (X^TX) число 14, лежащее на пересечении 1-й строки и 1-го столбца, получено как сумма произведений элементов 1-й строки матрицы X^T и 1-го столбца матрицы X

Умножаем матрицы,  (X^TY)

Находим обратную матрицу (X^TX)^-1

Вектор оценок коэффициентов регрессии равен

Уравнение регрессии (оценка уравнения регрессии)

Y = 109.2376-1.0839X₁ + 26.8841X₂

Проверим значимость структурной переменной Х2.

Для несмещенной оценки дисперсии проделаем следующие вычисления:

Несмещенная ошибка ε = Y - Y(x) = Y - X∙s (абсолютная ошибка аппроксимации)

Оценка дисперсии равна:

s_e²=(Y-Y(X))^T(Y-Y(X))=628.264

Несмещенная оценка дисперсии равна:

Оценка среднеквадратичного отклонения (стандартная ошибка для оценки Y):

Найдем оценку ковариационной матрицы вектора k = S² • (X^TX)^-1

Дисперсии параметров модели определяются соотношением S²_i = K_ii, т.е. это элементы, лежащие на главной диагонали

t-статистика

T_табл (n-m-1;α/2) = (11;0.025) = 2.593

Статистическая значимость коэффициента регрессии b₂ подтверждается.

 Следовательно, подтверждаются структурные изменения после реформы, начиная с 8-го месяца деятельности фирмы.

 

Задание №2. Имеются данные по заработной плате Y, возрасту X и полу работников 20 предприятий.

№ п/п	Y, (тыс. руб.)	X, (лет)	Пол, (м/ж)	№ п/п	Y, (тыс. руб.)	X, (лет)	Пол, (м/ж)
1	30	29	ж	11	25	28	ж
2	40	40	м	12	35	30	м
3	30	36	ж	13	20	25	м
4	32	32	ж	14	40	48	м
5	20	23	м	15	22	30	ж
6	35	45	ж	16	32	40	м
7	35	38	ж	17	39	40	м
8	40	40	м	18	36	38	м
9	38	50	м	19	26	29	ж
10	40	47	м	20	25	25	м

Постройте:

1.   Эконометрическую модель зависимости заработной платы от возраста только для мужчин.

2.   Эконометрическую модель зависимости заработной платы от возраста только для женщин.

3.   Эконометрическую модель зависимости заработной платы от возраста и пола работника.

4.   Различаются ли функции заработка для мужчин и женщин?

5.   Выполните тест Чоу.

6.   Прокомментируйте результат.

Решение.

1)    Построим конометрическую модель зависимости заработной платы от возраста только для мужчин.

Y, тыс. р.	X, (лет)	Пол, (м/ж)
40	40	м
20	23	м
40	40	м
38	50	м
40	47	м
35	30	м
20	25	м
40	48	м
32	40	м
39	40	м
36	38	м
25	25	м

Линейное уравнение регрессии имеет вид y = bx + a

Выборочные средние.

Выборочные дисперсии:

=

=

Среднеквадратическое отклонение

Коэффициент корреляции b можно находить по формуле, не решая систему непосредственно:

y = 1.0364 x + 2.1882

Остаточная сумма квадратов:

С¹_ост = 244,034

2)    Постпроим эконометрическую модель зависимости заработной платы от возраста только для женщин.

Y, тыс. р.	X, (лет)	Пол, (м/ж)
30	29	ж
30	36	ж
32	32	ж
35	45	ж
35	38	ж
25	28	ж
22	30	ж
26	29	ж

Выборочные средние.

Выборочные дисперсии:

=

=

Среднеквадратическое отклонение

Коэффициент корреляции b можно находить по формуле, не решая систему непосредственно:

y = 0.9615 x + 5.1307

Остаточная сумма квадратов:

С²_ост =99,769

3)    Построим эконометрическую модель зависимости заработной платы от возраста и пола работника.

Y, тыс. р.	X, (лет)	Пол, (м/ж)
40	40	1
20	23	1
40	40	1
38	50	1
40	47	1
35	30	1
20	25	1
40	48	1
32	40	1
39	40	1
36	38	1
25	25	1
30	29	0
30	36	0
32	32	0
35	45	0
35	38	0
25	28	0
22	30	0
26	29	0

 

Умножаем матрицы, (X^TX)

В матрице,  (X^TX) число 20, лежащее на пересечении 1-й строки и 1-го столбца, получено как сумма произведений элементов 1-й строки матрицы X^T и 1-го столбца матрицы X

Умножаем матрицы,  (X^TY)

Находим обратную матрицу (X^TX)^-1

Вектор оценок коэффициентов регрессии равен

Уравнение регрессии (оценка уравнения регрессии)

Y = 6.0708 + 0.6983X₁ + 1.7275X₂

Несмещенная оценка дисперсии равна:

Оценка среднеквадратичного отклонения (стандартная ошибка для оценки Y):

Найдем оценку ковариационной матрицы вектора k = S² • (X^TX)^-1

4)    Дисперсии параметров модели определяются соотношением S²_i = K_ii, т.е. это элементы, лежащие на главной диагонали

t-статистика

T_табл (n-m-1;α/2) = (17;0.025) = 2.458

Статистическая значимость коэффициента регрессии b₂ не подтверждается.

Следовательно, различия в заработной плате мужчин и женщин не подтверждается.

5)    Строим зависимость заработной платы от возраста без учёта пола работников.

Y, тыс. р.	X, (лет)
40	40
20	23
40	40
38	50
40	47
35	30
20	25
40	48
32	40
39	40
36	38
25	25
30	29
30	36
32	32
35	45
35	38
25	28
22	30
26	29

Выборочные средние.

Выборочные дисперсии:

=

Среднеквадратическое отклонение

Коэффициент корреляции b можно находить по формуле, не решая систему непосредственно:

y = 1.0066 x + 3.4399

С_ост = 346,51

Введем гипотезу Н₀: тенденция изучаемого ряда структурно стабильна.

Остаточная сумма квадратов по кусочно-линейной модели:

С^кл_ост = С¹_ост + С²_ост = 244,034 + 99,769 = 343,803.

Сокращение остаточной дисперсии при переходе от единого уравнения тренда к кусочно-линейной модели:

∆С_ост = С_ост – С^кл_ост = 346,511 – 343,803 = 2,708

Так как число параметров в уравнениях Y, Y1 и Y2 одинаково и равно k=1, то фактическое значение F – критерия находим по формуле:

           

 

F_факт = (2,708 /1)/( 343,803 /(20 - 2)) = 0,142.

 

Критическое (табличное) значение критерия Фишера для доверительной вероятности g = 0,95 и числа степеней свободы v₁ = k = 1 и  v₂ = n - 2∙k = 18: F_кр. = F_{0,05; 1; 18} = 3,009.

6)    F_факт < F_табл – уравнения Y1 и Y2 описывают одну и ту же тенденцию, а различия численных оценок их параметров а₁ и а₂, а так же b₁ и b₂ соответственно, статистически  не значимы. Следовательно, можно утверждать, что нет различия в зарабртных платах между мужчинами и женщинами.

 

 

Задание №3. В таблице приведены данные для 60 объектов недвижимости в Московской области по следующим показателям:

-       стоимость дома, Y ( (млн. р.);

-       расстояние от МКАД, x₁ (км);

-       площадь дома, x₂ (кв.м.)

-       количество этажей в доме, x₃;

-       площадь участка, x₄ (в сотках);

-       наличие центральных коммуникаций, x₅ (1: есть; 0: нет);

-       наличие гаража, x₆ (1: есть; 0: нет).

1.   Рассчитайте параметры линейного уравнения множественной регрессии с включением только количественных переменных.

2.    Рассчитайте параметры линейного уравнения множественной регрессии с включением фиктивных переменных.

3.   Сравните качественные характеристики моделей, полученных в 1 и во 2 пунктах. Выберите лучшую.

4.   Выполните тест Чоу.

5.   Оформите результаты в виде аналитической записки.

№	Y	x1	x2	x3	x4	x5	x6
1	4,004	28	139	2	12	1	0
2	9,958	14	333	3	15	1	1
3	10,816	18	387	4	15	1	0
4	10,816	22	405	2	19	1	1
5	3,640	14	234	3	15	0	0
6	5,616	67	226	2	21	1	1
7	5,356	34	126	3	19	1	1
8	8,632	45	274	3	28	1	1
9	9,958	24	315	3	4	1	1
10	14,534	14	486	4	26	1	1
11	9,828	12	360	3	14	1	0
12	7,228	21	270	2	16	1	0
13	6,344	14	229	2	12	1	0
14	3,458	9	270	2	9	0	0
15	6,084	14	360	3	14	0	0
16	10,608	21	360	3	28	1	0
17	11,622	6	405	3	14	1	1
18	11,960	19	540	4	14	1	0
19	13,780	9	467	4	19	1	1
20	6,032	25	235	2	12	1	0
21	8,372	14	288	2	14	1	1
22	3,224	36	121	1	10	1	1
23	8,190	10	241	3	14	1	1
24	4,810	55	196	2	21	1	0
25	5,720	45	234	2	18	1	0
26	10,452	14	345	3	18	1	1
27	1,1612	60	137	2	18	0	1
28	12,402	14	432	2	28	1	1
29	5,694	48	200	3	20	1	0
30	10,166	6	382	3	10	1	0
31	14,040	12	540	4	14	1	0
32	9,880	12	360	2	14	1	1
33	4,914	8	270	2	18	0	1
34	7,488	55	328	3	14	1	0
35	7,020	54	243	2	36	1	0
36	5,720	55	229	2	24	1	0
37	3,406	50	168	2	8	1	0
38	2,418	64	217	2	16	0	0
39	8,138	25	298	2	14	1	1
40	6,006	33	225	2	18	1	0
41	6,916	33	270	2	18	1	0
42	11,544	14	450	3	14	1	0
43	7,514	8	234	2	14	1	1
44	8,398	8	261	2	18	1	1
45	6,786	13	394	3	14	0	0
46	7,254	20	270	2	16	1	0
47	3,848	30	135	2	12	1	0
48	9,594	28	360	3	19	1	0
49	6,344	30	252	2	14	1	0
50	8,658	33	326	3	18	1	0
51	10,296	43	373	4	24	1	0
52	4,108	75	153	2	24	1	1
53	8,346	22	298	2	15	1	1
54	7,254	24	252	2	14	1	1
55	9,568	26	324	3	18	1	1
56	6,942	48	270	3	18	1	0
57	12,220	9	360	3	42	1	0
58	9,724	24	405	2	15	1	0
59	3,458	9	270	2	9	0	0
60	4,706	56	196	2	20	1	0

 

Решение.

1)    Рассчитайте параметры линейного уравнения множественной регрессии с включением только количественных переменных.

 

Умножаем матрицы, (X^TX)

В матрице,  (X^TX) число 60, лежащее на пересечении 1-й строки и 1-го столбца, получено как сумма произведений элементов 1-й строки матрицы X^T и 1-го столбца матрицы X

Умножаем матрицы,  (X^TY)

Находим обратную матрицу (X^TX)^-1

Вектор оценок коэффициентов регрессии равен

Y(X) = (X^TX)^-1X^TY =

Уравнение регрессии (оценка уравнения регрессии)

Y = -0.882-0.03032X₁ + 0.02119X₂ + 0.5359X₃ + 0.1059X₄

Оценка дисперсии равна:

s_e²=(Y-Y(X))^T(Y-Y(X))=97.511

Несмещенная оценка дисперсии равна:

Оценка среднеквадратичного отклонения (стандартная ошибка для оценки Y):

Найдем оценку ковариационной матрицы вектора k = S² • (X^TX)^-1

Дисперсии параметров модели определяются соотношением S²_i = K_ii, т.е. это элементы, лежащие на главной диагонали

) t-статистика

T_табл (n-m-1;α/2) = (55;0.025) = 2.299

Статистическая значимость коэффициента регрессии b₀ не подтверждается.

Статистическая значимость коэффициента регрессии b₁ подтверждается.

Статистическая значимость коэффициента регрессии b₂ подтверждается.

Статистическая значимость коэффициента регрессии b₃ не подтверждается.

Статистическая значимость коэффициента регрессии b₄ подтверждается.

2)      Рассчитаем параметры линейного уравнения множественной регрессии с включением фиктивных переменных.

 

Умножаем матрицы, (X^TX)

В матрице,  (X^TX) число 60, лежащее на пересечении 1-й строки и 1-го столбца, получено как сумма произведений элементов 1-й строки матрицы X^T и 1-го столбца матрицы X

Умножаем матрицы,  (X^TY)

Находим обратную матрицу (X^TX)^-1

Вектор оценок коэффициентов регрессии равен

Y(X) = (X^TX)^-1X^TY =

Уравнение регрессии (оценка уравнения регрессии)

Y = -3.1917-0.03345X₁ + 0.01954X₂ + 0.6173X₃ + 0.07406X₄ + 3.3265X₅ + 0.8496X₆

Оценка дисперсии равна:

s_e²=(Y-Y(X))^T(Y-Y(X))=7.3

Несмещенная оценка дисперсии равна:

Оценка среднеквадратичного отклонения (стандартная ошибка для оценки Y):

Найдем оценку ковариационной матрицы вектора k = S² • (X^TX)^-1

Дисперсии параметров модели определяются соотношением S²_i = K_ii, т.е. это элементы, лежащие на главной диагонали

t-статистика

T_табл (n-m-1;α/2) = (53;0.025) = 2.299

Статистическая значимость коэффициента регрессии b₀ подтверждается.

Статистическая значимость коэффициента регрессии b₁ подтверждается.

Статистическая значимость коэффициента регрессии b₂ подтверждается.

Статистическая значимость коэффициента регрессии b₃ подтверждается.

Статистическая значимость коэффициента регрессии b₄ подтверждается.

Статистическая значимость коэффициента регрессии b₅ подтверждается.

Статистическая значимость коэффициента регрессии b₆ подтверждается.

3),4),5) Сравнивая модели пункта 1 и 2, приходим к выводу. Что лучшая модель будет – модель 2.

Модель 1:

R2	0,82652319
Количество переменных m	4
Степеней свободы k	55
F	65,5113128
Fkp	2,53968863

Модель 2:

R2	0,987012
Количество переменных m	6
Степеней свободы k	53
F	671,3053
Fkp	2,275388

В модели 2 выше коэффициент детерминации и составляет 0,987, следовательно, модель на 98,7% объясняет изменение цены дома. Во второй модели все коэффициенты регрессии статистически значимы, включая коэффициенты при фиктивных переменных. В первой модели не значим коэффициент при переменной Х3(количество этажей).

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