Построение модели множественной регрессии в MS Excel

Целью работы является построение модели множественной регрессии в MS Excel и построение прогнозов, принятие решений о спецификации и идентификации модели, интерпретация результатов.

Задачи:

1) Построение системы показателей.

2) Проведение корреляционного анализа.

3) Нахождение уравнения регрессии зависимости объема продаж от ставки по депозитам и среднегодовой ставки по кредитам.

4) Проведение регрессионного анализа. Оценивание качества построенной модели.

5) Вычисление коэффициентов детерминации и F-критерия Фишера.

6) Оценка статистической значимости коэффициентов уравнения множественной регрессии с помощью t-критерия Стьюдента при уровне значимости б = 0,05.

1. Построение системы показателей (факторов)

По десяти объектам экономической эффективности развития банков получены данные, характеризующие зависимость объема прибыли (Y) от среднегодовой ставки (Х1), ставки по депозитам (Х2) и размера внутрибанковских расходов (Х3).

Необходимо:

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

2. Провести анализ коэффициентов парной корреляции.

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

4. Выбрать вид модели и оценить ее параметры.

5. Применить инструмента Регрессия (Анализ данных в EXCEL).

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

7. Определить значение F-критерия Фишера.

8. Оценить с помощью t-критерия Стьюдента статистическую значимость коэффициентов уравнения множественной регрессии.

Таблица 1. Статистические данные по всем переменным Приведем промежуточные результаты при вычислении коэффициента корреляции:

Формула для вычисления r_y,x1:

Таблица 2

Таблица 3

Таблицы 2−4. Промежуточные результаты при вычислении коэффициента.

Средние значения:

Дисперсия:

Коэффициент корреляции:

2. Анализ матрицы коэффициентов парной корреляции. Выбор факторных признаков для построения двухфакторной регрессионной модели

Использование инструмента Корреляция (Анализ данных в EXCEL):

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

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

3. В диалоговом окне Анализ данных выберем инструмент Корреляция, а затем щелкнем на кнопку ОК.

4. В диалоговом окне Корреляция в поле Входной интервал вводим диапазон ячеек, содержащий исходные данные. Если и выделены и заголовки столбцов, то установим флажок Метки в первой строке.

Таблица 5. Результаты корреляционного анализа

Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная, т. е. объем прибыли имеет тесную связь с размером внутрибанковских расходов (0,865), с расходами на среднегодовую ставку (0,549) и с наблюдением (0,912). В данном примере n=10, m=4, после исключения незначимых факторов n=10, m=2.

3. Выбор вида модели и оценка ее параметров

Оценка параметров регрессии осуществляется по методу наименьших квадратов. Используем данные, приведенные в таблице.

Таблица 6. Статистические данные по всем переменным.

Уравнение может иметь вид:

Решим данную систему уравнений по формулам Крамера:

Найдем определители матриц:

Таблица 7. Нахождение определителей матриц Найдем коэффициенты уравнения:

a=?1/?= 18,5158

b1=?2/?= 0,185 566

b2=?3/?= 0,582 028

Уравнение регрессии составит:

y=18,51 583+0,185 566×1+0,58 2028x2

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

корреляция регрессионный определитель excel

4. Применение инструмента Регрессия (Анализ данных в EXCEL)

Регрессионный анализ — это статистический метод исследования зависимости случайной величины от переменных (аргументов), рассматриваемых в регрессионном анализе как неслучайные величины независимо от истинного закона распределения.

Для проведения регрессионного анализа выполним следующие действия:

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

2. В диалоговом окне Анализ данных выбираем инструмент Регрессия, ОК.

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

4. Если выделены и заголовки столбцов, то устанавливаем флажок Метки в первой строке.

5. Выбираем параметры вывода.

6. В поле Остатки ставим необходимые флажки. ОК.

Таблица 8

Таблица 9

• 5. Оценка качества модели. Значение F-критерия Фишера
• В таблице 10 приведены вычисленные по модели значения Y и значения остаточной компоненты.
• Рисунок 1. График остатков
• Стандартная ошибка коэффициента корреляции рассчитывается по формуле:
• Ser_k=
• Ser_{к =} 0,3 162 278
• Вычисляем для модели коэффициент детерминации:
• Он показывает долю вариации результативного признака под воздействием изучаемых факторов, т. е. в 83% случаев изменения х приводят к изменению y. Другими словами — точность подбора уравнения регрессии — высокая.
• Проверку значимости уравнения регрессии можно произвести на основе вычисления F-критерия Фишера.
• С помощью критерия Фишера оценивают качество регрессионной модели в целом и по параметрам. Для этого выполняется сравнение полученного значения F и табличного F значения. F фактический определяется из отношения значений факторной и остаточной дисперсий, рассчитанных на одну степень свободы:
• F=9,3
• где n — число наблюдений, а m — число параметров при факторе х. F табличный — это максимальное значение критерия под влиянием случайных факторов при текущих степенях свободы и уровне значимости а=0,05.
• Значение F-критерия Фишера можно найти в таблице 4.2 протокола EXCEL.
• Табличное значение F-критерия при доверительной вероятности 0,95 при V1=k=2 и V1=n-k=7 составляет 4,74. табличное значение F-критерия можно найти с помощью FРАСПОБР
• Рисунок 2. Табличное значение F-критерия Фишера
• 6. Оценивание с помощью t-критерия Стьюдента статистической значимости коэффициентов уравнения множественной регрессии
• Значимость коэффициентов уравнения регрессии а0, а1, а2 оценим с использованием t-критерия Стьюдента.
• Наиболее часто t — критерий используется в двух случаях. В первом случае его применяют для проверки гипотезы о равенстве генеральных средних двух независимых, несвязанных выборок (так называемый двухвыборочный t-критерий). В этом случае есть контрольная группа и опытная группа, состоящая из разных пациентов, количество которых в группах может быть различно. Во втором же случае используется так называемый парный t-критерий, когда одна и та же группа объектов порождает числовой материал для проверки гипотез о средних. Поэтому эти выборки называют зависимыми, связанными.
• Находим обратную матрицу (X^TX)^-1

4.13	0.0445	— 0.0696
0.0445	0.374	— 0.252
— 0.0696	— 0.252	0.214

b11=4.13

b22=0.374

b33=0,214

ta0=20,669/15,03=1.375

ta1=0,176/0,384=0.458

Расчетные значения t-критерия Стьюдента для коэффициентов уравнения регрессии а1, а2 приведены в четвертом столбце 4.3 протокола EXCEL. Табличное значение t-критерия при 5% уровне значимости и степенях свободы 7 составляет 2,36, его можно найти с помощью СТЬЮДРАСПОБР.

Рисунок 3. Табличное значение t-критерия Стьюдента

Заключение

Делаем следующие выводы:

1) Коэффициент множественной корреляции показывает на весьма сильную связь всего набора факторов с результатом

2) Сравнивая Fтабл. и Fфакт мы видим, что Fтабл. =4,74< Fфакт. = 9.3. С вероятностью 0,95 делаем заключение о статистической значимости уравнения в целом и показателя тесноты , которые сформировались под неслучайным воздействием факторов x_{1 и x2..}

3) Общий вывод состоит в том, что множественная модель с факторами x₁ и x₂ с = 0,83 содержит информативный фактор х1 и х2.

4) Уравнение регрессии зависимости объема продаж от ставки по депозитам и среднегодовой ставки по кредитам:

y=18,51 583+0,185 566×1+0,582 028×2.

1) Кремер, Н. Ш. Эконометрика / Н. Ш. Кремер, Б. А. Путко. — М.: ЮНИТИ-ДАНА, 2005.

2) Под ред. И. И. Елисеевой — М. — Финансы и статистика, 2003.

3) В. П. Носко Эконометрика — «Дело» РАНХиГС, 2011

4) Практикум по эконометрике / Под ред. И. И. Елисеевой. — М.: Финансы и статистика, 2005.

5) Магнус Я. Р. — Эконометрика, 2009

6) Айвазян С. А., Бухштабер В. М., Енюков С. А., Мешалкин Л. Д. Прикладная статистика. Классификация и снижение размерности. — М.: Финансы и статистика, 1989.

7) Мартьянова М. Н., Сафронова Т. П. Основы статистики промышленности: Учебное пособие. — М.: Финансы и статистика, 1983