Пример решения эконометрической задачи в 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 = (XTX)-1XTY

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

1

10

0

1

11

0

1

12.5

0

1

12.5

0

1

14.6

0

1

14.6

0

1

14.9

0

1

13

1

1

13

1

1

13.8

1

1

14.2

1

1

14.4

1

1

15

1

1

16.1

1

Матрица Y

98

100

103

105

80

87

94

113

116

118

121

123

126

128

Матрица XT

1

1

1

1

1

1

1

1

1

1

1

1

1

1

10

11

12.5

12.5

14.6

14.6

14.9

13

13

13.8

14.2

14.4

15

16.1

0

0

0

0

0

0

0

1

1

1

1

1

1

1

Умножаем матрицы, (XTX)

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

Умножаем матрицы,  (XTY)

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

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

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

Y = 109.2376-1.0839X1 + 26.8841X2

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

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

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

Y

Y(x)

ε = Y - Y(x)

ε2

(Y-Yср)2

98

98.398

-0.398

0.159

100

100

97.314

2.686

7.213

64

103

95.688

7.312

53.461

25

105

95.688

9.312

86.707

9

80

93.412

-13.412

179.883

784

87

93.412

-6.412

41.114

441

94

93.087

0.913

0.834

196

113

122.03

-9.03

81.55

25

116

122.03

-6.03

36.367

64

118

121.163

-3.163

10.007

100

121

120.73

0.27

0.073

169

123

120.513

2.487

6.185

225

126

119.863

6.137

37.667

324

128

118.67

9.33

87.044

400

 

 

 

628.264

2926

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

se2=(Y-Y(X))T(Y-Y(X))=628.264

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

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

Найдем оценку ковариационной матрицы вектора k = S2 • (XTX)-1

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

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

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

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

 Следовательно, подтверждаются структурные изменения после реформы, начиная с 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

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

С1ост = 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

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

С2ост =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

 

Умножаем матрицы, (XTX)

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

Умножаем матрицы,  (XTY)

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

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

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

Y = 6.0708 + 0.6983X1 + 1.7275X2

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

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

Найдем оценку ковариационной матрицы вектора k = S2 • (XTX)-1

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

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

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

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

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

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

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

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

Склост = С1ост + С2ост = 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 и числа степеней свободы v1 = k = 1 и  v2 = n - 2∙k = 18: Fкр. = F0,05; 1; 18 = 3,009.

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

 

 

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

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

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

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

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

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

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

-       наличие гаража, x6 (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)    Рассчитайте параметры линейного уравнения множественной регрессии с включением только количественных переменных.

 

Умножаем матрицы, (XTX)

60

1653

17728

152

1029

1653

64673

431806

3988

30085

17728

431806

5820966

47668

307517

152

3988

47668

414

2642

1029

30085

307517

2642

20033

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

Умножаем матрицы,  (XTY)

462.9732

11052.514

152705.8944

1256.5624

8232.5856

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

0.4652

-0.00338

-0.000565

-0.0533

-0.00312

-0.00338

8.6E-5

1.0E-5

-0.000282

-7.3E-5

-0.000565

1.0E-5

4.0E-6

-0.000331

-9.0E-6

-0.0533

-0.000282

-0.000331

0.06444

-0.000263

-0.00312

-7.3E-5

-9.0E-6

-0.000263

0.000489

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

Y(X) = (XTX)-1XTY =

-0.882

-0.03032

0.02119

0.5359

0.1059

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

Y = -0.882-0.03032X1 + 0.02119X2 + 0.5359X3 + 0.1059X4

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

se2=(Y-Y(X))T(Y-Y(X))=97.511

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

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

Найдем оценку ковариационной матрицы вектора k = S2 • (XTX)-1

0.825

-0.006

-0.001

-0.0945

-0.00552

-0.006

0.000152

1.8E-5

-0.000499

-0.00013

-0.001

1.8E-5

8.0E-6

-0.000586

-1.6E-5

-0.0945

-0.000499

-0.000586

0.114

-0.000467

-0.00552

-0.00013

-1.6E-5

-0.000467

0.000867

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

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

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

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

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

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

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

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

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

 

Умножаем матрицы, (XTX)

60

1653

17728

152

1029

52

24

1653

64673

431806

3988

30085

1462

591

17728

431806

5820966

47668

307517

15576

7051

152

3988

47668

414

2642

133

59

1029

30085

307517

2642

20033

916

416

52

1462

15576

133

916

52

22

24

591

7051

59

416

22

24

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

Умножаем матрицы,  (XTY)

462.9732

11052.514

152705.8944

1256.5624

8232.5856

431.054

202.1932

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

0.5342

-0.00351

-0.000541

-0.05748

-0.00223

-0.06712

-0.04731

-0.00351

9.0E-5

1.1E-5

-0.000241

-7.3E-5

-0.000492

0.000503

-0.000541

1.1E-5

4.0E-6

-0.000328

-8.0E-6

-9.5E-5

3.3E-5

-0.05748

-0.000241

-0.000328

0.06495

-0.000307

-0.000826

0.00619

-0.00223

-7.3E-5

-8.0E-6

-0.000307

0.000501

-0.00104

-0.000486

-0.06712

-0.000492

-9.5E-5

-0.000826

-0.00104

0.1557

-0.01542

-0.04731

0.000503

3.3E-5

0.00619

-0.000486

-0.01542

0.07419

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

Y(X) = (XTX)-1XTY =

-3.1917

-0.03345

0.01954

0.6173

0.07406

3.3265

0.8496

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

Y = -3.1917-0.03345X1 + 0.01954X2 + 0.6173X3 + 0.07406X4 + 3.3265X5 + 0.8496X6

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

se2=(Y-Y(X))T(Y-Y(X))=7.3

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

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

Найдем оценку ковариационной матрицы вектора k = S2 • (XTX)-1

0.0736

-0.000483

-7.5E-5

-0.00792

-0.000307

-0.00925

-0.00652

-0.000483

1.2E-5

1.0E-6

-3.3E-5

-1.0E-5

-6.8E-5

6.9E-5

-7.5E-5

1.0E-6

1.0E-6

-4.5E-5

-1.0E-6

-1.3E-5

5.0E-6

-0.00792

-3.3E-5

-4.5E-5

0.00895

-4.2E-5

-0.000114

0.000853

-0.000307

-1.0E-5

-1.0E-6

-4.2E-5

6.9E-5

-0.000144

-6.7E-5

-0.00925

-6.8E-5

-1.3E-5

-0.000114

-0.000144

0.0214

-0.00212

-0.00652

6.9E-5

5.0E-6

0.000853

-6.7E-5

-0.00212

0.0102

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

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

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

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

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

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

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

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

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

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

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(количество этажей).

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

Имя файла: ekonom15.rar

Размер файла: 90.53 Kb

Если закачивание файла не начнется через 10 сек, кликните по этой ссылке