Эконометрическое моделирование стоимости квартир в Московской области

• Выбераем команду СервисÞАнализ данных.
• В диалоговом окне Анализ данных выбираем инструмент Регрессия.
• В диалоговом окне Регрессия в поле Входной интервал Y вводим вместе с надписями адрес одного диапазона ячеек, который  представляет зависимую переменную. В поле Входной интервал Х вводим с надписями адреса одного или нескольких диапазонов, которые содержат значения независимых переменных. По очереди вводим Х3,Х5,Х6, три модели.
• Так как выделены и заголовки столбцов, то устанавливаем флажок Метки в первой строке. отмечаем галочками: уровень надежности 95%, новый рабочий лист, остатки, график подбора и график остатков -ОК
• Результат: три протокола с выводом остатков (Таблицы 4,5,6), три уравнения парной регрессии, графики подбора (Графики 3,5,7) и графики остатков для каждого Х (Графики 4,6,8).

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

Линейное уравнение связи двух переменных (парную регрессию) представим в виде:

уi=α+β*хi+εi

где α – постоянная величина (или свободный член уравнения);

β – коэффициент регрессии, определяющий наклон линии, вдоль которой рассеяны данные наблюдений.

εi – случайная составляющая отражает тот факт, что изменение уi будет неточно описываться изменением Х, поскольку присутствуют другие факторы, неучтённые в данной модели.

Систематическую часть можно представить в виде уравнения:

ŷi=α+β*хi

Коэффициент регрессии β характеризует изменение переменной уi при изменении значения хi на единицу. Если β>0, переменные хi и уi положительно коррелированны и имеют прямую связь, если β<0 – отрицательно коррелированны и имеют обратную связь.

Оценки наименьших квадратов:

Коэффициент регрессии β вычисляется по формуле:

 

 

При   ≠ 0

Вычислим Коэффициент регрессии β для фактора Х3 используя Exсel:

• Функция - ЛИНЕЙН - (известные_значения_у: выделяем значения столбца Y; известные_значения_х: выделяем значения столбца Х3; константа: выделяем значение коэффициента Y-пересечение из протокола; статистика: выделяем значение t-статистика для Y-пересечение из протокола)- ОК.

 

или используем следующие формулы:

Таблица 7

Наименование показателя в отчёте Excel	Принятые наименование	Формула
Множественный R	Коэффициент множественной корреляции, индекс корреляции	R=
R – квадрат	Коэффициент детерминации R2	R2=1-
Нормированный R – квадрат	Скорректированный R2
Стандартная ошибка	Среднеквадратическое отклонение от модели	Se=
Наблюдения	Количество наблюдений n	n

 

Таблица 8

	df – число степеней свободы	SS – сумма квадратов	MS – среднее значение	F – критерий Фишера
Регрессия	k=1		/k	F=
Остаток	n-k-1=38		/(n-k-1)
Итого	n-1=39

 

4. Оцените качество каждой модели через коэффициент детерминации, среднюю ошибку аппроксимации и F-критерий Фишера. Выберите лучшую модель.

Ответ:

Лучшая модель парной регрессии фактора Х3; y3=-13,1088+1,5426*Х3. Поскольку только для этой модели Fрасч > Fтабл (Fyx3=95,3132216>4,098172), уравнение регрессии следует признать адекватным. Коэффициент детерминации (R2=0,714957) высокий, близкий к 1, хорошее качество модели. 71% вариации зависимой переменной Y учтено в модели и обусловлено влиянием включенного фактора Х3 (общая площадь квартиры). Самое меньшее значение средней ошибки аппроксимации =28 % , то есть самое меньшее рассеяние эмпирических точек вокруг теоретической линии.

В нашей задаче β3=1,5426 (коэффициент при Х3) показывает, что при увеличении общей площади квартиры на 1 м2, цена квартиры увеличится на 1,5426 тыс. долл.

Критерии	Первая модель Для Х3	Вторая модель Для Х5	Третья модель Для Х6	Выводы
R2-коэффициент детерминации	0,714957	0,021427	0,076881	Модель Х3 лучше
- средняя ошибка аппроксимации	28%	46 %	49 %	Модель Х3 лучше
F-критерий Фишера	95,3132216	0,832088977	3,164784713	Fтабл.=  4,098172 Модель Х3 адекватна, остальные нет

 

Решение:

4.1 Оценим качество моделей через коэффициенты детерминации R2 для всех факторов (Х).

 

Коэффициент детерминации R2 рассчитывается по формуле:

• Оценим качество первой модели для фактора Х3 (общей площади квартиры):

 y3=-13,1088+1,5426*Х3

Рассчитаем коэффициент детерминации R2 по формуле:

R2 = = 73931,13791/103406,4111 = 0,714957

 рассчитываем, подставляя в модель yх3=-13,1088+1,5426*Х3 значения Х3i.

Также R2 можно посчитать по остаткам еi (остатки берем из протокола регрессионного анализа) (Таблица 4) по формуле:

 

R2 = 1-

 

Или взять величину коэффициента детерминации R2 из протокола регрессионного анализа) (Таблица 4)

Регрессионная статистика
R-квадрат	0,714957

 

Коэффициент детерминации высокий, близкий к 1, хорошее качество модели.

Коэффициент детерминации показывает, что около 71% вариации зависимой переменной Y учтено в модели и обусловлено влиянием на него включённых факторов.

 

• Оценим качество второй модели для фактора Х5 (этажа квартиры):

y5=80,3429+1,8876*Х5

R2 = = 2215,779194/103406,4111 = 0,021427

Коэффициент детерминации очень низкий, близкий к 0, фактор почти не влияет на Y (стоимость квартиры).

• Оценим качество третьей модели для фактора Х6 (площади кухни):

y6=33,3729+5,9947*Х6

R2 = = 7949,975478/103406,4111 = 0,076881

Коэффициент детерминации очень низкий, близкий к 0, фактор почти не влияет на Y (стоимость квартиры).

2. Для оценки качества регрессионных моделей рассчитаем величину средней ошибки аппроксимации для всех факторов.

Средняя относительная ошибка аппроксимации вычисляется по формуле:

 

 

Подставляя в уравнения регрессии фактические значения факторов Хi, найдем ŷi

Вычисляем остаток ei , который представляет собой отклонение фактического значения зависимой переменной от ее значения, полученного расчетным путем.

ei= yi − ŷi

Или берем остатки ei из протокола регрессионного анализа (для первой модели Таблица 4, для второй Таблица 5, для третьей модели Таблица 6)

Рассчитаем Ei по формуле:

 

Для того чтобы получить значение ei/yi по модулю │ei/yi│*100 необходимо воспользоваться функцией Exсel – функция -ABS (выделяем значение e1, / на y1*100), а затем суммировать столбец и разделить на n.

 

• Для первого фактора Х3; y3=-13,1088+1,5426*Х3

 

= 27,87 = 28 %

 

• Для второго фактора Х5 ; y5=80,3429+1,8876*Х5

 

= 45,78= 46 %

• Для третьего фактора Х6;; y6=33,3729+5,9947*Х6

 

= 48,63 = 49%.

 

< 7% свидетельствует о хорошем качестве модели. Чем меньше рассеяние эмпирических точек вокруг теоретической линии регрессии, тем меньше средняя ошибка аппроксимации.

Наиболее удачная модель для фактора Х3; y3=-13,1088+1,5426*Х3

3. Проверку значимости уравнений регрессии произведем на основе вычисления F-критерия Фишера:

F= ,

 

n- количетво наблюдений.= 40

k – количество факторов, включенных в модель = 1

α – уровнень значимости = 0,05

Табличное значение F-критерия можно найти EXCEL: Fтабл.= 4,098172

• Функция – FРАСПОБР - при доверительной вероятности 0,05;
• Степень свободы 1 = k =1;
• Степень свободы 2 = n – k -1= 40 - 1 - 1=38

 

 

4.3.1 Рассчитаем F-критерий Фишера для первой модели

фактор Х3; y3= -13,11+1,54*Х3

F yx3= = = 95,3132216

 

F-критерий Фишера для фактора Х3 можем взять из протокола регрессионного анализа Excel (F) для фактора Х3 (Таблица 4).

Дисперсионный анализ
	df	SS	MS	F
Регрессия	1	73931,13794	73931,13794	95,31322

 

4.3.2 Рассчитаем F-критерий Фишера для второй модели

фактор Х5; y5=80,3429+1,8876*Х5

Fyx5= = = 0,832088977

Также F-критерий Фишера для фактора Х5 можем взять из протокола регрессионного анализа Excel (F) для фактора Х5 (Таблица 5).

Дисперсионный анализ
	df	SS	MS	F
Регрессия	1	2215,779194	2215,779194	0,832088977

 

4.3.3 Рассчитаем F-критерий Фишера для третьей модели

фактор Х6; yх6=33,3729+5,9947*Х6

Fyx6= = = 3,164784713

Также F-критерий Фишера для фактора Х6 можем взять из протокола регрессионного анализа Excel (F) для фактора Х6 (Таблица 6).

Дисперсионный анализ
	df	SS	MS	F
Регрессия	1	7949,975478	7949,975478	3,164784713

 

Fyx3 =95,3132216 > Fтабл.= 4,098172

Fyx5=0,832088977< Fтабл.= 4,098172

Fyx6=3,164784713 < Fтабл.= 4,098172

Поскольку только для модели фактора Х3; y3=-13,1088+1,5426*Х3 Fрасч > Fтабл, уравнение регрессии следует признать адекватным.

 

5. Осуществить прогнозирование для лучшей модели среднего значения показателя Y при уровне значимости α=0,1, если прогнозное значение фактора Х составит 80% от его максимального значения. Представить графически: фактические и модельные значения точки прогноза.

 

Ответ:

Прогнозное значение для модели yх3i=-13,1088+1,5426*Х3i

= 196,07 с вероятностью 80% будет находиться  между верхней границей, равной 196,07 + 50,72 =246,79 и нижней границей, равной 196,07 - 50,72 =145,35

Фактические и модельные значения точки прогноза представлены на Графике 9 (изображены треугольниками, черный треугольник – модельное значение, белые треугольники – фактическое значение точки прогноза, черный ромб – среднее значение Yсред=93,65, Хсред=69,21).

Координаты точки прогноза модельной: Yпрогн=196,07, Хпрогн=135,6;

Координаты точки прогноза фактические:

верхний предел: Yпрогн=246,79, Хпрогн=135,6;

нижний предел: Yпрогн=145,35, Хпрогн=135,6

 

Решение: Прогнозирование по регрессионной модели: прогнозируемое значение переменной Y получается при подстановке в уравнение регрессии прогнозируемой величины фактора Хпрогн..

Определяем Хпрогн. : выбираем самое большое значение Х3max с помощью Excel:

• функция - МАКС(выделяем диапазон значений Х3)- ОК.

Зная Х3max можно рассчитать Х прогн.:

Х3max=169,5

Хпрогн. = 169,5*80% /100=169,5*0,8=135,6 м2

Yсред=93,65 тыс. долл.

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