Построение модели множественной регрессии в MS Excel
Введение
Целью работы является построение
модели множественной регрессии в MS Excel и построение прогнозов, принятие
решений о спецификации и идентификации модели, интерпретация результатов.
Задачи:
) Построение системы
показателей.
) Проведение корреляционного
анализа.
) Нахождение уравнения
регрессии зависимости объема продаж от ставки по депозитам и среднегодовой
ставки по кредитам.
) Проведение регрессионного
анализа. Оценивание качества построенной модели.
) Вычисление коэффициентов
детерминации и F-критерия Фишера.
) Оценка статистической
значимости коэффициентов уравнения множественной регрессии с помощью t-критерия
Стьюдента при уровне значимости α =
0,05.
1.
Построение системы показателей (факторов)
По десяти объектам экономической
эффективности развития банков получены данные, характеризующие зависимость
объема прибыли (Y) от среднегодовой ставки (Х1), ставки по депозитам (Х2) и
размера внутрибанковских расходов (Х3).
Необходимо:
. Построить систему
показателей.
. Провести анализ
коэффициентов парной корреляции.
. Выбрать признаки для
построения двухфакторной регрессионной модели.
. Выбрать вид модели и
оценить ее параметры.
. Применить инструмента
Регрессия (Анализ данных в EXCEL).
. Оценить качество модели.
. Определить значение F-критерия Фишера.
. Оценить с помощью t-критерия Стьюдента статистическую
значимость коэффициентов уравнения множественной регрессии.
Таблица 1. Статистические данные по
всем переменным
Приведем промежуточные результаты
при вычислении коэффициента корреляции:
Формула для вычисления ry,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. График остатков
Стандартная ошибка коэффициента
корреляции рассчитывается по формуле:
Serk=
к = 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-критерий, когда одна и та же группа объектов порождает
числовой материал для проверки гипотез о средних. Поэтому эти выборки называют
зависимыми, связанными.
Находим обратную
матрицу (XTX)-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факт.
= 9.3. С вероятностью 0,95 делаем заключение о статистической
значимости уравнения в целом и показателя тесноты , которые
сформировались под неслучайным воздействием факторов x1
и x2..
) Общий вывод
состоит в том, что множественная модель с факторами x1 и x2
с = 0,83 содержит
информативный фактор х1 и х2.
) Уравнение
регрессии зависимости объема продаж от ставки по депозитам и среднегодовой
ставки по кредитам:
=18,51583+0,185566x1+0,582028x2.
Список литературы
1) Кремер, Н.Ш. Эконометрика / Н.Ш. Кремер, Б.А. Путко. - М.:
ЮНИТИ-ДАНА, 2005.
) Под ред. И.И. Елисеевой - М. - Финансы и статистика, 2003.
) В.П. Носко Эконометрика - «Дело» РАНХиГС, 2011
) Практикум по эконометрике / Под ред. И.И. Елисеевой. - М.:
Финансы и статистика, 2005.
) Магнус Я.Р. - Эконометрика, 2009
) Айвазян С.А., Бухштабер В.М., Енюков С.А., Мешалкин Л.Д.
Прикладная статистика. Классификация и снижение размерности. - М.: Финансы и
статистика, 1989.
) Мартьянова М.Н., Сафронова Т.П. Основы статистики
промышленности: Учебное пособие. - М.: Финансы и статистика, 1983