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

 

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

 

Факторы, исследуемые в моей работе: Y (цена квартиры), X1 (город области), X2 (число комнат квартиры), X4 (жилая площадь квартиры); номера наблюдений: 1-40.

 

 

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

На панели задач выбираем «Сервис» - «Анализ данных» - «Корреляция».

 

 

В поле «Входной интервал» выделяем диапазон В6:Е45. Расчеты выводим на новый лист.

 

 

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

 

 

Для оценки тесноты связи коэффициентов Y с Xi используем специальную шкалу.

Таким образом:

Y и X1 – r = 0,403334 – связь умеренная;

Y и X2 – r = 0,68821 – связь заметная;

Y и X4 – r = 0,82639 – связь высокая.

 

Коэффициенты X2 и X4 можно считать коллинеарными, так как соответствующий им коэффициент корреляции (0,918996) больше 0,7. Из дальнейшего анализа исключим фактор X2 так как корреляция фактора X4 с результатом Y (0,82639) сильнее, чем у X2 (0,68821), а межфакторная связь X4 с X1 (0,107318) слабее, чем X2 с X1 (0,155006).

 

2. Постройте поле корреляции  результативного признака и наиболее  тесно связанного с ним фактора.

 

Результативный признак Y (цена квартиры) наиболее тесно связан с фактором X4 (жил.площадь).

Построим поле корреляции Y с X4.

Выбираем на панели задач «Вставка» - «Мастер диаграмм: ТОЧЕЧНАЯ».

Диапазоны, соответственно, столбы Y и X4.

 

 

 

 

3. Рассчитайте параметры линейной  парной регрессии для фактора X, наиболее тесно связанного с Y.

 

На панели задач выбираем «Сервис» - «Анализ данных» - «Регрессия».

Выделяем соответствующие диапазоны – столбцы Y и X4.

 

 

Получаем:

 

 

Линейная регрессия:

 

Y^ = a + b*X4.

 

После подстановки получаем:

 

Y^ = -1,3 + 2,4.

 

Добавляем к исходным данным еще один столбец Y^ и производим соответствующие вычисления:

 

 

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

 

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

 

R2 = r2yx4.

 

Соответственно, чем больше значение коэффициента, тем теснее связь. В нашем случае R2 = 0,6829, это означает, что на 70% вариация стоимости квартиры обусловлена площадью ее жилой части и на 30% - влиянием прочих факторов.

 

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

 

A` = n-1 * ∑|(Y – Y^) / Y| * 100%.

 

Добавляем к исходным данным еще один столбец |(Y – Y^) / Y| и производим соответствующие вычисления:

 

 

Полученные данные просуммируем, подставим в формулу (при n = 40):

 

A` = (10,49811 / 40) * 100% ≈ 26%.

 

Это означает, что полученные значений от фактических в среднем отклоняются на 26%. Так как A` > 10%, то построенная модель недостаточно хорошо подобрана к исходным данным.

 

F-критерий Фишера:

 

F = ,

Таким образом:

 

F = 81,84.

 

При уровне значимости α=0,05 Fтабл(0,05;1;38) = 4,08.

 

Так как F > Fтабл, то уравнение регрессии в целом признается значимым по критерию Фишера.

 

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

 

Прогнозное значение X4 по условию составляет 80% от максимального значения.

Рассчитаем максимальное X4 в EXCEL с помощью функции МАКС. Оно равно 84.

80% от максимально  значения будет равняться 67,2.

Для получения прогнозных оценок показателя Y поставим полученное значение в равнение линейной регрессии:

 

Y^ = -1,3 + 2,4 * 67,2 = 159,98.

 

Теперь представим это графически.

В EXCEL создадим еще один лист, куда скопируем исходные данные и столбец значений уравнения регрессии, отсортируем таблицу по возрастанию колонки X4.

Получаем:

 

 

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

 

Построим модель множественной линейной регрессии на основе факторов X1 и X4 (фактор X2 был исключен ранее, как коллинеарный X4), используя Метод Наименьших Квадратов (МНК). Для этого построим систему:

 

 

Y2^ = a + b1*X1 + b2*X4.

 

Посчитаем известные коэффициенты и перепишем систему уравнений с тремя неизвестными:

 

 

 

 

Решим систему с помощью EXCEL с помощью функции МОПРЕД:

 

 

Таким образом, мы построили уравнение множественной линейной регрессии:

 

Y2^ = 124,8 + 51,53*X1 – 2,64*X4.

 

Коэффициенты b1=51,53 и b2=(-2,64) – коэффициенты регрессии – среднее изменение результата Y при изменении факторов X1 и X4 на единицу.

 

7. Оцените качество построенной  модели. Улучшилось ли качество  модели по сравнению с однофакторной моделью?

 

Найдем среднюю ошибку аппроксимации:

 

A` = n-1 * ∑|(Y – Y^) / Y| * 100%.

 

 

A` = (32,0848*100%)/40 = 80%.

 

Средняя ошибка аппроксимации у новой модели значительно выше у однофакторной (80% > 26%). Поэтому качество построенной двухфакторной модели хуже однофакторной.