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

Для этого воспользуемся инструментом построения точечной диаграммы программы Excel.

В результате получаем поле корреляции цены квартиры, тыс. долл. и жилой площади квартиры, кв.м. (рисунок 1.1.)

Рисунок 1.1.

3. Рассчитаем параметры линейной парной  регрессии для каждого фактора Х.

Для расчета параметров линейной парной регрессии воспользуемся инструментом Регрессия, входящим в настойку Анализ данных.

В диалоговом окне Регрессия в поле Входной интервал Y вводим адрес диапазона ячеек, которые  представляет зависимую переменную, т.е. стоимость квартир. В поле Входной интервал Х вводим адрес диапазона, который содержит значения независимых переменных. Выполним поочередно вычисления параметры парной регрессии для каждого фактора.

Для Х3 получили следующие данные, представленные в таблице 1.2:

Таблица 1.2

Уравнение регрессии зависимости цены квартиры от общей площади квартиры имеет вид:

Y3=-13,109+1,542*X3

Для Х5 получили следующие данные, представленные в таблице  1.3:

Таблица 1.3

Уравнение регрессии зависимости цены квартиры от этажа квартиры имеет вид:

Y5=80,343+1,888*X5

Для Х6 получили следующие данные, представленные в таблице  1.4:

Таблица 1.4

Уравнение регрессии зависимости цены квартиры от площади кухни имеет вид:

Y6=33,373+5,993*X6

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

Коэффициент детерминации, среднюю ошибку аппроксимации мы получили в результате расчетов, проведенных в пункте 3.

А) Коэффициент детерминации  определяет, какая доля вариации признака У учтена в модели и  обусловлена влиянием не него фактора Х. Чем больше значение коэффициента детерминации, тем теснее связь между признаками в построенной математической модели

Из таблиц 1.2, 1.3, 1.4 видно, что наибольшее значение коэффициента детерминации имеет фактор X3 (0,715).

Исходя из данного критерия наиболее адекватной является модель уравнения регрессии зависимости цены квартиры от общей площади квартиры (Х3)

Б) Средняя ошибка аппроксимации:

Для вычисления средней относительной ошибки аппроксимации рассмотрим остатки модели Е_i = Y_i – Y_Тi, содержащиеся в столбце Остатки итогов программы РЕГРЕССИЯ (таблица «вывод остатка»). Дополним таблицу столбцом относительных погрешностей, которые вычислим по формуле Е_отн.i = 100 с помощью функции ABS.

Выполнение расчетов для модели (3):

По столбцу относительных погрешностей найдем среднее значение   _отн = 28,26% (с помощью функции СРЗНАЧ).

Выполнение расчетов для модели (5):

По столбцу относительных погрешностей найдем среднее значение   _отн = 40,17% (с помощью функции СРЗНАЧ).

Выполнение расчетов для модели (6):

По столбцу относительных погрешностей найдем среднее значение   _отн = 41,03% (с помощью функции СРЗНАЧ).

В)  Для проверки значимости модели регрессии используется F-тест. Для этого выполняется сравнение и критического (табличного)значений F-критерия Фишера. 

Расчетные значения приведены в таблицах 1.2, 1.3, 1.4 (обозначены буквой F).

Разнесем результаты в сводную таблицу:

Оценим точность построенных моделей в соответствии со схемой:

_отн1 = 28,26% > 15%, _отн2 = 40,17% > 15%, _отн3 = 41,03% > 15%. Точность всех трех моделей неудовлетворительная. Ближе к 15% _отн  модели (Х3).

Проверим значимость полученных уравнений с помощью F – критерия Фишера.

F – статистики определены программой РЕГРЕССИЯ (таблицы «Дисперсионный анализ») и представлены в последнем столбце сводной таблицы.

С помощью функции FРАСПОБР найдем значение F_кр = 4,10 для уровня значимости α = 5%, и чисел степеней свободы k₁ = 1, k₂ = 38.

Схема проверки:

F = 95,313 > F_кр = 4,10, следовательно уравнение модели (X3) является значимой и ее использование целесообразно.

F = 0,832 < F_кр = 4,10, следовательно уравнение модели (X5) не является значимой и ее использование нецелесообразно.

F = 3,165 < F_кр = 4,10, следовательно уравнение модели (X6) не является значимой и ее использование нецелесообразно.

Зависимая переменная Y достаточно хорошо описывается включенной в модель (Х3) факторной переменной Х₃.

Вывод: на основании оценки качества моделей по коэффициенту детерминации, средней ошибке аппроксимации и критерию Фишера наилучшей является модель (Х3) зависимости цены квартиры от ее общей площади. Однако эту модель нецелесообразно использовать для прогнозирования в реальных условиях, поскольку ее точность неудовлетворительная, и дальнейшие расчеты проведем в учебных целях.

5. Для выбранной модели зависимости цены квартиры от общей площади квартиры осуществим прогнозирование среднего значения показателя  при уровне значимости  ,  если прогнозное значения фактора  составит 80% от его максимального значения.

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

Рассчитаем Х_max в Excel с помощью функции МАКС.

= 169,5кв.м

=0,8 *169,5 = 135,6 кв.м

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

= -13,109+1,543*135,6 = 196,07 тыс.долл.

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

Для вычисления доверительного интервала для прогнозного значения рассчитываем величину отклонения от линии регрессии. Для модели парной регрессии величина отклонения рассчитывается:

, т.е. значение стандартной ошибки из таблицы 1.2.

(Так как число степеней свободы равно единице, то знаменатель будет равен n-2).

= 27,851

Для расчета коэффициента  воспользуемся функцией Excel СТЬЮДРАСПОБР, вероятность возьмем равную 0,1, число степеней свободы 38.

= 1,686

По столбцу данных Х₃ найдем среднее значение = 42,04 (функция СРЗНАЧ) и определим  = 31068,79 (функция КВАДРОТКЛ);

U=50,72

Определим верхнюю и нижнюю границы интервала.

U⁺=196,50+50,72= 246,79

U^-=196,50-50,72= 145,34

Таким образом, прогнозное значение = 196,07  тыс.долл., будет находиться между нижней границей, равной 145,34 тыс.долл. и верхней границей, равной 246,79 тыс.долл.

Фактические и модельные значения, точки прогноза представлены графически на рисунке 1.2.

Рисунок 1.2.

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

Для построения множественной регрессии воспользуемся функцией Регрессия программы Excel, включив в нее все факторы. В результате получаем результативные таблицы, из которых нам необходим t-критерий Стьюдента.

      Таблица 1.5

Получаем модель вида:

Y=14,038+1,696*X3+0,084*X5-3,815*X6

Поскольку <   (4,10 < 33,821), уравнение регрессии следует признать адекватным.

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

Поскольку 0,069 < 1,688 модель следует признать не адекватной.

Поскольку фактор X5 статистически не значим, то построим модель с учетом только факторов Х3 и Х6.

      Таблица 1.6

Получаем модель вида:

Y=14,040+1,696*X3-3,759*X6

Поскольку <   (4,10 < 52,131), уравнение регрессии следует признать адекватным.

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

Поскольку 1,807 > 1,688 модель следует признать адекватной.

Коэффициент парной корреляции независимых переменных X3 (общая площадь квартиры) и  X6 (площадь кухни) меньше 0,8 (см. табл. 1.1.) в исходных данных отсутствует мультиколлинеарность.

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

Для оценки качества выбранной множественной модели Y_Т = 14,040+1,696*X3-3,759*X6 аналогично п.4 данной задачи используем коэффициент детерминации R-квадрат, среднюю относительную ошибку аппроксимации и F – критерий Фишера.

Коэффициент детерминации R-квадрат выпишем из итогов РЕГРЕССИИ (таблица «Регрессионная статистика» для модели).

R² = 0,738, следовательно, вариация цены квартиры Y на 73,8% объясняется по данному уравнению вариацией общей площади квартиры Х₃ и площади кухни Х₆.

Используем исходные данные Y_i и найденные программой РЕГРЕССИЯ остатки Е_i (таблица «Вывод остатка» для модели). Рассчитаем относительные погрешности и найдем среднее значение _отн.

По столбцу относительных погрешностей найдем среднее значение   _отн = 25,46% (с помощью функции СРЗНАЧ).

Сравнение показывает, что 22,69% > 15%. Следовательно, точность модели неудовлетворительная.

С помощью F – критерия Фишера проверим значимость модели в целом. Для этого выпишем из итогов РЕГРЕССИИ (таблица «дисперсионный анализ» для модели ) F = 52,131.

С помощью функции FРАСПОБР найдем значение F_кр = 3,25 для уровня значимости α = 5%, и чисел степеней свободы k₁ = 2, k₂ = 37.

F = 61,01 > F_кр = 3,25, следовательно, уравнение модели является значимым, его использование целесообразно, зависимая переменная Y достаточно хорошо описывается включенными в модель факторными переменными Х₃ и Х₆.

Дополнительно с помощью t – критерия Стьюдента проверим значимость отдельных коэффициентов модели.

t – статистики для коэффициентов уравнения регрессии приведены в итогах программы РЕГРЕССИЯ. Для выбранной модели получены следующие значения (табл. 1.6).

Критическое значение t_кр найдено для уровня значимости α = 5% и числа степеней свободы k = 40 – 2 – 1 = 37. t_кр = 2,03 (функция СТЬЮДРАСПОБР).

Для свободного коэффициента a= 14,04 определена статистика t(a) =        0,74.

|t(a)| = 0,74 < t_кр = 2,03, следовательно, свободный коэффициент a не является значимым, его можно исключить из модели.

Для коэффициента регрессии b₁ = -3,76 определена статистика t(b₁)= -1,81.