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

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

Введение

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

Задачи:

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

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

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

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

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

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

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

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

Необходимо:

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

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

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

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

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

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

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

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

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

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

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

Таблица 2

Таблица 3

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

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

Дисперсия:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица 7. Нахождение определителей матриц

Найдем коэффициенты уравнения:=∆1/∆= 18,5158=∆2/∆= 0,185566=∆3/∆= 0,582028

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица 8

Таблица 9

5. Оценка качества модели. Значение F-критерия Фишера

В таблице 10 приведены вычисленные по модели значения Y и значения остаточной компоненты.

Рисунок 1. График остатков

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

Ser_k=

_{к =} 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

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факт. = 9.3. С вероятностью 0,95 делаем заключение о статистической значимости уравнения в целом и показателя тесноты , которые сформировались под неслучайным воздействием факторов x₁ и x_2..

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

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

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

Список литературы

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

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

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

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

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

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

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