Всё для Учёбы — студенческий файлообменник
1 монета
docx

Лабораторная № 2 «Множественный корреляционный анализ с использованием Microsoft Excel» по Эконометрике (Горбатков С. А.)

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ ГОУ ВПО

Всероссийский заочный финансово-экономический институт

Кафедра математики и информатики

О Т Ч Е Т

по дисциплине "Эконометрика"

о результатах выполнения

компьютерной лабораторной работы

по теме: Множественный корреляционный анализ

(используя Microsoft Excel)

Исполнитель

Факультет

Специальность

Группа

№ зачетной книжки

Руководитель

Уфа 2010 г.

Задача.

По данным о рынке жилья в Московской области, представленным в табл. 1, исследуется зависимость между ценой квартиры Y (тыс. долл.) и следующими основными факторами:

X1 - город области (1- Подольск, 2-Люберцы);

X2 - число комнат в квартире;

X3 - общая площадь квартиры (м2);

X4 - жилая площадь квартиры (м2);

X5 - этаж квартиры;

X6 - площадь кухни (м2).

Y-цена квартиры, тыс. долл.

Исходные данные взяты из журнала "Недвижимость и цены" 1-7 мая 2006 г.

Таблица 1

Исходные данные

№ Y

X1 X2 X3 X4 X5

X6 16 60 1 2

64,5 32 11 12

17 100

1 4 93,8 58 1

9 18 51 1 2 64

36 6 12 19 157

2 4

98 68 2 11 20

123,5 1 4 107,5

67,5 12 12,3 21

55,2

2 1 48 15,3 9

12 22 95,5 1 3

80 50 6 12,5 23

57,6

2 2 63,9 31,5

5 11,4 24 64,5

1 2 58,1 34,8

10 10,6

25 92 1 4 83

46 9 6,5 26 100

1 3 73,4 52,3

2 7

27 81 2 2 45,5

27,8 3 6,3 28

65 1 1 32 17,3

5 6,6

29 110 2 3 65,2

44,5 10 9,6 30

42,1 1 1 40,3

19,1

13 10,8 31 135

2 2 72 35 12

10 32 39,6 1 1

36 18

5 8,6 33 57 1

2 61,6 34 8 10

Продолжение таблицы 1

№ Y

X1 X2 X3 X4 X5

X6 34 80 2 1

35,5 17,4 4 8,5

35 61

1 2 58,1 34,8

10 10,6 36 69,6

1 3 83 53 4 12

37 250

1 4 152 84 15

13,3 38 64,5 1

2 64,5 30,5 12

8,6 39

125 2 2 54 30

8 9 40 152,3 2

3 89 55 7 13

41 38

1 1 41,9 19 12

9,5 42 62,2 1

2 69 36 9 10

43 125

2 3 67 41 11

8 44 61,1 1 2

58,1 34,8 10 10,6

45 67

2 1 32 18,7 2

6 46 93 2 2 57,2

27,7 1 11,3 47

118 1

3 107 59 2 13

48 132 2 3 81

44 8 11 49 92,5

2 3

89,9 56 9 12 50

105 1 4 75 47

8 12 51 42 1

1 36

18 8 8 52 125

1 3 72,9 44 16

9 53 170 2 4

90 56

3 8,5 54 38 2

1 29 16 3 7 55

130,5 2 4 108

66 1

9,8 Задание:

1. Составьте матрицу парных коэффициентов корреляции. Установите, какие факторы коллинеарны.

2. Постройте уравнение регрессии, характеризующее зависимость цены от всех факторов.

3. Оцените значимость полученного уравнения. Какие факторы значимо воздействуют на формирование цены квартиры в этой модели?

4. Постройте модель формирования цены квартиры за счет значимых факторов.

5. Существует ли разница в ценах квартир, расположенных в городах Подольске и Люберцы.

6.Оцените качество построенной модели.

Решение:

1. Рассчитаем матрицу парных коэффициентов корреляции и оценим статистическую значимость коэффициентов корреляции.

С помощью надстройки "Анализ данных. Корреляция" строим матрицу парных коэффициентов корреляции между всеми исследуемыми переменными (меню "Сервис" ? "Анализ данных." ? "Корреляция"). На рисунке 1 изображена панель корреляционного анализа с заполненными полями.

. Рисунок 1 - Диалоговое окно ввода параметров инструмента Корреляция

Результаты корреляционного анализа приведены в таблице 2.

Таблица 2

Матрица парных коэффициентов корреляции

Y X1 X2 X3

X4 X5 X6 Y 1

X1

0,2715 1

X2 0,736181 -0,01088

1 X3 0,798262

-0,07553

0,873119 1

X4 0,787024 -0,05826

0,926889 0,964581

1

X5 0,077226 -0,32633

-0,04786 0,07946

-0,01528 1 X6

0,268403

-0,12853 0,300182

0,541997 0,454418

0,237896 1

Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная, т. е. цена квартиры, имеет тесную связь с Х2 - численностью комнат в квартире (ryx2=0,736), Х3 - общей площадью квартиры (м2) (ryx3=0,798) и Х4 - жилой площади квартиры (ryx4=0,787)

Коэффициенты интеркорреляции (т.е. корреляции между объясняющими переменными) позволяют исключать из модели дублирующие факторы. Считается, что две переменные явно коллинеарны, т.е. находятся между собой в линейной зависимости, если

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

Из таблицы, очевидно, что факторы Х2 и Х3, Х2 и Х4, Х3 и Х4 соответственно коррелированны друг с другом. В первом случае в уравнение целесообразно включить фактор Х3, так как корреляция Х2 с Y - слабее, чем корреляция фактора Х3 c Y. Во втором случае целесообразно включить фактор Х4, а не Х2, так как корреляция Х2 с Y - слабее, чем корреляция фактора Х4 с Y. В третьем случае целесообразно включить фактор Х3, а не Х4, так как корреляция Х4 с Y - слабее, чем корреляция фактора Х3 c Y. Итак, в уравнении множественной регрессии исключаются факторы Х2 и Х4.

2.Построим уравнение регрессии, характеризующее зависимость цены от всех факторов.

Для проведения регрессионного анализа, характеризующего зависимость цены от всех факторов, выполним следующие действия:

1. Выберем команду Сервис > Анализ данных.

2. В диалоговом окне Анализ данных выберем инструмент Регрессия, где Входной интервал Y - значения Y, Входной интервал Х - значения Х1, Х2, Х3, Х4, Х5, Х6 (рисунок 2).

3. ОК.

Рисунок 2 - Диалоговое окно ввода параметров инструмента Регрессия.

Результаты регрессионного анализа приведены в рисунке 3.

Уравнение регрессии, характеризующее зависимость цены от всех факторов имеет вид: Y = 33,376Х1 - 4,905Х2 + 1,222Х3 + 0,877Х4 + 2,154Х5 - 5,104Х6 - 26,474.

Рисунок 3 - Результаты регрессионного анализа, характеризующего зависимость цены от всех факторов.

3. Оценим значимость полученного уравнения Y = 33,376Х1 - 4,905Х2 + 1,222Х3 + 0,877Х4 + 2,154Х5 - 5,104Х6 - 26,474. Выявим, какие факторы значимо воздействуют на формирование цены квартиры в этой модели?

Коэффициент детерминации R является характеристикой силы общей линейной связи между переменными в регрессионной модели. Он показывает, насколько хорошо выбранные независимые переменные способны определять поведение зависимой переменной. Чем выше коэффициент детерминации (изменяющийся в пределах от 0 до 1), тем лучше выбранные независимые переменные подходят для определения поведения зависимой переменной. В нашем примере R = 0,898, что является хорошим показателем.

Также важной характеристикой регрессионной модели является коэффициент R2, показывающий, какая доля совокупной вариации в зависимой переменной описывается выбранным набором независимых переменных. Величина R2 изменяется от 0 до 1. Как правило, данный показатель должен превышать 0,5 (чем он выше, тем показательнее построенная регрессионная модель). В нашем примере R2 =0,806 - это значит, что регрессионной моделью описано только 80,6 % случаев (дисперсии в итоговой оценке цены). Таким образом, при интерпретации результатов регрессионного анализа следует постоянно иметь в виду существенное ограничение: построенная модель справедлива только для 80,6 % случаев.

Полученное значение Дарбина-Уотсона d=2,385>2, что свидетельствует об отрицательной корреляции. Перейдем к d? = 4 - d=1,615 и сравним с двумя критическими уровнями d1 = 0,82 и d2 = 1,32. d? =1,615 лежит в интервале от d2 = 1,32 до 2, следовательно, свойство независимости остаточной компоненты выполняется.

Критическое значение Fкр = 4,098 найдено для уравнения значимости и чисел степеней свободы , (функция FРАСПОБР в Excel).

Сравнение показывает: , следовательно, уравнение модели является значимым, ее использование целесообразно, зависимая переменная Y достаточно хорошо описывается включенными в модели факторными переменными Х1, Х3, Х5, Х6.

Стандартизированные ? - коэффициенты регрессии (дают возможность сравнить силу влияния параметров между собой. Знак (+ или -) перед ?-коэффициентом показывает направление связи между независимой и зависимой переменными. Положительные ?-коэффициенты свидетельствуют о том, что возрастание величины данного частного параметра увеличивает зависимую переменную. Так в нашем случае отрицательный коэффициент равный -0,237 и -0,118 имеют параметры Х6 - площадь кухни (м2) и Х2 - число комнат в квартире соответственно. Это говорит о том, что при возрастании данных частных параметров цена квартиры, тыс. долл., снижается.

T-критерия Стьюдента равно ¦-1,18¦, сравниваем его с табличным значением, которые рассчитываем в Excel, уровень значимости берем равным 0,10, число степеней свободы n-m-1=40-4=36: tтабл=1,306. Поскольку ¦-1,18¦< 1,306 модель следует признать неадекватной.

На основании проведенного анализа можно судить о том, что построенная регрессионная модель не является статистически значимой и практически приемлемой.

Параметры Х2 - число комнат в квартире и Х4 - жилая площадь квартиры (м2) являются незначимыми, так как соответствующие коэффициенты регрессии P-значение = 0,597 и P-значение = 0,404 больше значения 0,05 . Следовательно, такие факторы, как Х1 - город области (1- Подольск, 2-Люберцы), Х3 - общая площадь (м2), Х6 - площадь кухни (м2) и Х5 - этаж квартиры значимо воздействуют на формирование цены квартиры в этой модели.

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

Для проведения регрессионного анализа, характеризующего зависимость цены от значимых факторов, выполним следующие действия:

1. Выберем команду Сервис > Анализ данных.

2. В диалоговом окне Анализ данных выберем инструмент Регрессия, где Входной интервал Y - значения Y, Входной интервал Х - значения Х1, Х3, Х5, Х6 (рисунок 4).

3. ОК. Рисунок 4 - Диалоговое окно ввода параметров инструмента Регрессия.

Результаты регрессионного анализа приведены в рисунке 5.

Рисунок 5 - Результаты регрессионного анализа, характеризующего зависимость цены от значимых факторов.

Уравнение регрессии зависимости цены квартиры от Х1 - города области (1- Подольск, 2-Люберцы), Х3 - общей площади (м2), Х6 - площади кухни (м2) и Х5 - этажа квартиры можно записать в следующем виде:

Y = 32,67Х1 + 1,61Х3 + 1,935Х5 - 5,093Х6 - 27,851.

Чем больше величина коэффициента регрессии, тем значительнее влияние данного признака на моделируемый. Особое значение при этом имеет знак перед коэффициентом регрессии. Знаки коэффициентов регрессии говорят о характере влияния на результативный признак. Так как факторные признаки Х1, Х3 и Х5 имеют знак плюс, то с увеличением данных факторов результативный признак Y возрастает. Так как факторный признак Х6 со знаком минус, то с его увеличением результативный признак Y уменьшается.

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

5. Существует ли разница в ценах квартир, расположенных в городах Подольске и Люберцы.

Для того чтобы выяснить существует ли разница в ценах квартир, расположенных в городах Подольске и Люберцы выполним следующие действия: Данные - Сводные таблицы (рисунок 6)

Рисунок 6 - Мастер сводных таблиц и диаграмм.

Полученные результаты сравнения средних цен квартир, тыс. долл., в г. Люберцы и г. Подольск представлены в таблице 3.

Таблица 3

Отчет Среднее по полю Y

X1

Итог 1 82,026

2 105,947 Общий итог

92,193 Исходя из таблицы 3, видим, что средняя цена квартир, тыс. долл., в г. Люберцы выше по сравнению с г. Подольск.

6. Оценим качество построенной модели Y = 32,67Х1 + 1,61Х3 + 1,935Х5 - 5,093Х6 - 27,851.

Коэффициент детерминации R является характеристикой силы общей линейной связи между переменными в регрессионной модели. Он показывает, насколько хорошо выбранные независимые переменные способны определять поведение зависимой переменной. Чем выше коэффициент детерминации (изменяющийся в пределах от 0 до 1), тем лучше выбранные независимые переменные подходят для определения поведения зависимой переменной. В нашем примере R = 0,895, что является хорошим показателем.

Также важной характеристикой регрессионной модели является коэффициент R2, показывающий, какая доля совокупной вариации в зависимой переменной описывается выбранным набором независимых переменных. Величина R2 изменяется от 0 до 1. Как правило, данный показатель должен превышать 0,5 (чем он выше, тем показательнее построенная регрессионная модель). В нашем примере R2 =0,802 - это значит, что регрессионной моделью описано только 80,2 % случаев (дисперсии в итоговой оценке цены). Таким образом, при интерпретации результатов регрессионного анализа следует постоянно иметь в виду существенное ограничение: построенная модель справедлива только для 80,2 % случаев.

Полученное значение Дарбина - Уотсона , что свидетельствует об отрицательной корреляции. Перейдем к d? = 4 - d=1,628 и сравним с двумя критическими уровнями и . d? =1,628 лежит в интервале от до 2, следовательно, свойство независимости остаточной компоненты выполняется.

Критическое значение найдено для уравнения значимости и чисел степеней свободы , (функция FРАСПОБР в Excel).

Сравнение показывает: , следовательно, уравнение модели является значимым, ее использование целесообразно, зависимая переменная Y достаточно хорошо описывается включенными в модели факторными переменными Х1, Х3, Х5, Х6.

Стандартизированные ? - коэффициенты регрессии (дают возможность сравнить силу влияния параметров между собой. Знак (+ или -) перед ?-коэффициентом показывает направление связи между независимой и зависимой переменными. Положительные ? - коэффициенты свидетельствуют о том, что возрастание величины данного частного параметра увеличивает зависимую переменную. Так в нашем случае отрицательный коэффициент равный -0,236 имеет параметр Х6 - площадь кухни (м2). Это говорит о том, что при возрастании данного частного параметра цена квартиры, тыс. долл., снижается.

T-критерия Стьюдента равно ¦-1,321¦, сравниваем его с табличным значением, которые рассчитываем в Excel, уровень значимости берем равным 0,1, число степеней свободы n-m-1=40-4=36: =1,306. Поскольку ¦-1,321¦> 1,306 модель следует признать адекватной.

На основании проведенного анализа можно судить о том, что построенная регрессионная модель является статистически значимой и практически приемлемой.

Список используемой литературы.

1. Источник с Интернета http://office.microsoft.com/ru-ru/excel/HA010346331049.aspx.

2. Практикум по эконометрике: Учеб. пособие / И.И.Елисеева, С.В. Курышева, Н.М. Гордеенко и др.; Под ред. И.И.Елисеевой. - М.: Финансы и статистика, 2003.

Показать полностью…
Похожие документы в приложении