Выполнение работы в MS Excel

Порядок выполнения работы рассмотрим на примере построения линейной регрессионной зависимости расходов на жилье (Y, млрд. дол.) от располагаемого личного дохода (X, млрд. дол.) и индекса реальных цен (Р) относительно 1972 г. по данным США за 1959-1978 г. Эти данные приведены на рис 14.

Ввод данных. В ячейках A1-А21 расположим имя фактора Х (располагаемый личный доход) и его значения, в ячейках B1-В21 имя фактора Р (индекса реальных цен) и его значения, в ячейках C1-С21 имя зависимой переменной Y (расходы на жилье) и его значения.

Рис.14. Множественная регрессия

Построение корреляционной матрицы. Следуя работе №1, построим корреляционную матрицу для величин X, P, Y. Расположим ее в ячейках G5-I7, см. рис.14. Парные коэффициенты корреляции , , говорят о тесной парной линейной корреляционной зависимости рассматриваемых величин.

Оценка уравнения множественной линейной регрессии . Откроем вкладку «Данные», в группе «Анализ» выберем надстройку «Анализ данных». В открывшемся окне «Инструменты анализа» выберем функцию «Регрессия». В появившемся окне "Регрессия" укажем входные данные для оценки параметров уравнения регрессии, выводимые результаты и их расположение. Заполнение окна "Регрессия" для рассматриваемого примера приведено на рис. 15. В части "Входные данные" в поле ввода "Входной интервал Y" указываем диапазон ячеек, содержащий значения зависимой переменной, в нашем примере это С1:С21. В поле ввода "Входной интервал X" – диапазон ячеек, содержащий значения независимых переменных, в примере это A1:В21. Значения объясняющих переменных должны располагаться в последовательных столбцах. В поле "Метки" устанавливаем флажок , он указывает на то, что первые строки диапазонов данных содержат имена этих данных (заголовки). В "Константа-ноль" флажок не устанавливаем. При установке флажка в левом поле "Уровень надежности", наряду с используемым по умолчанию стандартным уровнем надежности 95% ( ), можно задать и другое его значение, в этом случае будут выведены интервальные оценки параметров регрессии для двух уровней надежности.

В части "Параметры вывода" выбираем "Выходной интервал" – для помещения результатов на текущем рабочем листе, положение результатов на листе указываем заданием верхней левой ячейки, начиная с которой располагаются результаты, в нашем примере выбрана ячейка А25. Далее, выставляя флажки, указываем какую дополнительную информацию, предлагаемую функцией "Регрессия", мы хотим иметь в результатах:

· "Остатки" – для выдачи прогнозов и остатков регрессии ;

· "График остатков" – для вывода точечной диаграммы остатков ;

· "График подбора" – для вывода наложенных на диаграмму рассеяния точек линии регрессии. По ОКполучаем результаты регрессии, которые включают в себя таблицу регрессионной статистики, таблицу дисперсионного анализа, таблицу коэффициентов регрессии, таблицу остатков и графики остатков и подбора. Результаты регрессии приведены на рис.16-17. Пояснения к выводимым результатам см. в работе № 3.

Рис. 15. Заполнение окна «Регрессия»

Рис. 16. Итоги выполнения «Регрессии»

Из таблицы коэффициентов регрессии имеем следующие МНК-оценки параметров уравнения регрессии , , . Их стандартные ошибки равны . 95%-е доверительные интервалы коэффициентов регрессии: ; ; . В таблице «ВЫВОД ИТОГОВ» величина «Стандартная ошибка» является оценкой стандартного отклонения зависимой переменной (ошибки регрессии), т.е. .

Построенное уравнение регрессии: .

Рис. 17. Графики остатков и подбора множественной регрессии

Графики остатков и подбора в множественной регрессии в MS Excel выдаются отдельно по каждому фактору. Приведенные на рис. 17 графики остатков регрессии имеют колебательный характер, а графики подбора говорят о хорошем качестве подгонки построенной модели к наблюдаемым данным.

Верификация модели. Значения t-статистик для коэффициентов уравнения регрессии соответственно превышают по модулю критическое значение . Для вычисления при n=20, р=3 и выделим ячейку G45, в вкладке «Формулы» выберем «Другие функции», в группе «Статистические» выберем функцию «СТЬЮДЕНТ.ОБР.2Х». В окне этой функции в поле «Вероятность» введем значение , равное 0,05, в поле «Степени свободы» зададим число степеней свободы n-р, равное 17. По «ОК» в ячейке G45получим значение , в рассматриваемом примере оно равно 2,1098 (см. рис. 16). Следовательно, при уровне значимости коэффициенты уравнения регрессии значимо отличаются от нуля. О значимом влиянии располагаемого личного дохода X и индекса реальных цен Р на расходы на жилье Y говорят также р-значения, которые меньше заданного уровня значимости , а также доверительные интервалы для коэффициентов уравнения регрессии, которые не содержат нуля.

Большие значения скорректированного коэффициента детерминации и F-статистики, , говорят о статистической значимости построенного уравнения регрессии и хорошем качестве подгонки модели к выборочным данным. 98,99% вариации зависимой переменной объясняется вариацией объясняющих переменных.

Построение точечного и интервального прогнозы среднего зависимой переменной. Построение прогноза среднего зависимой переменной для значений факторов равных их выборочным средним, т.е. для . Для нахождения выборочных средних факторов и зависимой переменной последовательно выделяя, например, ячейки А23, В23, С23 и вводя соответственно в строке формул =СРЗНАЧ(А2:А21),=СРЗНАЧ(В2:В21),=СРЗНАЧ(С2:С21), получим значения выборочных средних , , . Для вычисления прогноза среднего при заданных значениях факторов выделим, например, ячейку Н102 и, учитывая расположение значений факторов и коэффициентов уравнения регрессии, в строке формул введем =В41+В42*А23+В43*В23. По ОК в Н102получим искомое значение , совпадающее с выборочным средним , см. рис. 18.

Построение интервальной оценки, надежности , среднего зависимой величины. Доверительный интервал надежности для среднего зависимой величины при заданном векторе значений факторов определяется неравенством

где – квантиль уровня распределения Стьюдента с числом степеней свободы . Оценка дисперсии для заданного вектора значений факторов определяется как . Предварительно вычислим оценку дисперсии (см. рис. 18).

Для этого в ячейках А100-С119 создадим матрицу X, первый столбец которой состоит из единиц, второй – из значений фактора Х, третий – из значений фактора Р. В ячейках В123-U125разместим транспонированную матрицу . В ячейках F98-H98 расположим вектор ( )=(1; 713,035; 100,405) значений факторов для которых вычисляется интервальный прогноз. В ячейках K98-K100 расположим транспонированный вектор . Выделим ячейку Н105 и, учитывая расположение величины s в ячейке В31, в строке формул введем

=B31^2*(1+МУМНОЖ(МУМНОЖ(F98:H98;МОБР(МУМНОЖ(B123:U125;A100:C119)));K98:K100)).

Рис. 18 Построение точечного и интервального прогноза среднего

По Enter в этой ячейке получим искомое значение оценки дисперсии среднего, равное 7,734. Выделим под нижнюю границу доверительного интервала ячейку К108и в строке формул введем

=H102-СТЬЮДЕНТ.ОБР(0,975;17)*КОРЕНЬ(H105)

По Enter в ячейке К108 получим значение нижней границы доверительного интервала, равное 93, 0426. Аналогично, выделив ячейку N108 и введя в строке формул

=H102+СТЬЮДЕНТ.ОБР(0,975;17)*КОРЕНЬ(H105),

Получим в ней значение верхней границы доверительного интервала, равное 104,777.

Таким образом, доверительный интервал надежности среднего зависимой величины для значений факторов и p задается неравенством

.

Общее заключение об оцененной модели и ее интерпретация.

Построенная модель хорошо согласуется с имеющейся выборкой. Об этом свидетельствует высокое значение нормированного коэффициента детерминации , т.е. 98,99% вариации Y относительной ее средней объясняется изменениями X и P. Большое значение -статистики, , и ее уровень значимости, равный , свидетельствует о наличии значимой линейной корреляционной зависимости Y от X и P. Оценка среднеквадратического отклонения ошибок регрессии мала по сравнению с , что также свидетельствует о малом разбросе выборочных данных относительно линии регрессии. Значения t-статистик коэффициентов уравнения регрессии и их p-значения, равные соответственно 0,0197, и 0,00091 говорят об их значимом отличии от нуля. Следовательно, располагаемый личный доход и индекс цен значимо влияют на расходы на жилье и построенная регрессионная модель статистически значима.

Интерпретация построенной модели . Коэффициент при индексе цен p имеет отрицательный знак, что согласуется с теоретическим положением о снижении спроса на жилье с ростом цены. Коэффициент при величине располагаемых доходов x положительный, что согласуется с положением о росте спроса с ростом доходов. Значения коэффициентов при x и p говорит о возрастании расходов на жилье в среднем на 0,13388 млрд. дол при росте располагаемых доходов на 1млрд. дол, расходы на жилье сокращаются на 1,31194 млрд. дол при росте индекса цен на 1%.

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

Контрольные вопросы.

1. В чем заключается спецификация модели множественной регрессии?

2. Как находятся оценки параметров линейной множественной регрессии?

3. Может ли быть линейная множественная регрессия быть нелинейной по объясняющим переменным?

4. Сформулируйте критерии значимости параметров множественной регрессии.

5. Приведите предпосылки линейной множественной регрессии.

6. Сформулируйте Теорему Гаусса-Маркова.

7. С помощью каких критериев проверяется значимость линейного уравнения множественной регрессии?

8. В чем отличие ошибок регрессии от остатков регрессии?

9. Что характеризует скорректированный коэффициент детерминации?

10. Как определяется средняя ошибка аппроксимации, что она характеризует?

11. Как интерпретируются коэффициенты линейной множественной регрессии?

12. Что характеризует частный коэффициент эластичности для линейной множественной регрессии?

13. В чем заключается прогноз значений зависимой переменной? Как определяется дисперсия прогноза?

14. Как строится интервальный прогноз среднего зависимой переменной?

15. С увеличением надежности интервального прогноза он увеличивается или уменьшается?