Построение модели множественной регрессии в 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,185566

b2=?3/?= 0,582028

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

y=18,51583+0,185566x1+0,582028x2

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

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

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

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

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

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

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

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

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

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

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



Таблица 8

Таблица 9

• 5. Оценка качества модели. Значение F-критерия Фишера
• В таблице 10 приведены вычисленные по модели значения Y и значения остаточной компоненты.
• Рисунок 1. График остатков
• Стандартная ошибка коэффициента корреляции рассчитывается по формуле:
• Ser_k=
• Ser_{к =} 0,3162278
• Вычисляем для модели коэффициент детерминации:
• Он показывает долю вариации результативного признака под воздействием изучаемых факторов, т.е. в 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.00374	-0.00252
-0.0696	-0.00252	0.00214

b11=4.13

b22=0.00374

b33=0,00214

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факт.