Применение Microsoft Excel для вычисления линейной регрессии с двумя переменными и множественной регрессии

Размещено на http://www.allbest.ru

Размещено на http://www.allbest.ru

Применение Microsoft Excel для вычисления линейной регрессии с двумя переменными и множественной регрессии



1. Линейная регрессия с двумя переменными

Постановка задачи

Имеем данные о стоимости автомобиля (тыс. долларов) в зависимости от мощности мотора (США, 2002). MSRP = manufacturer's suggested retail price (2002 model year). horse = horsepower.

Табл. 1

horse	MSRP
160	25525
394	70545
215	26995
320	68665
181	17494
270	31660
165	23420
150	21960
194	26094
236	46750
125	13023
240	32860
345	53000
275	39647
125	16905
285	36728
130	16975
130	16985
157	25980
140	17570
210	35135
190	24825
240	39915
130	18695
141	14999

Требуется:

Провести анализ регрессии и построить линию регрессии (линию прогноза).

Построить доверительные интервалы прогноза для среднего значения Y.

Провести проверку модели регрессии.

Замечание: Значение t* принять равным 2.07.

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

Копируем в Excel свой вариант. Называем книгу Расчеты_вар5.xls и сохраняем ее.

2. Вычисление параметров регрессии «вручную», т.е., не используя «Пакет анализа»

Используя входные данные, построим точечную диаграмму и линию регрессии:

Задаем: Вставка-Диаграмма

В появившемся окне выбираем Точечная и нажимаем кнопку Готово.

Кликаем правой мышкой по полю диаграммы и в выпавшем меню выбираем Исходные данные

В появившемся окне выбираем опцию столбцах. Нажимаем Ряд.

В окне Ряд нажимаем Добавить и заполняем окно, после чего нажимаем ОК.

Появляется диаграмма.

Ставим мышку на одну из точек диаграммы. Нажимаем правую кнопку мышки. В выпавшем окне выбираем Добавить линию тренда.

В выпавшем окне выбираем Линейная, а затем нажимаем кнопку Параметры.

Окончательно получаем:



Рис. 1. Построение точечной диаграммы и линии регрессии

R2 = 0,8105 - как видим, информативность модели по отношению к данным не очень высокая, поскольку полученное значение коэффициента детерминации достаточно далеко от единицы.

Уравнение регрессии имеет вид: , где b - коэффициент наклона, a - коэффициент сдвига, которые вычисляются по формулам:

, .

Для нахождения коэффициентов регрессии вычислим все необходимые параметры выборки:

Среднее значение (по переменным X)

= 205,92

Среднее значение (по переменным Y)



=30494

Вычисляем стандартное отклонение выборки X и стандартное отклонение выборки Y:

=15,03

=3128,88

Найдём коэффициент корреляции:

=0.925.

Видим, что значение коэффициента корреляции достаточно велико. Это говорит о высокой степени зависимости стоимости автомобиля от мощности мотора.

Теперь можно вычислить коэффициенты наклона b и сдвига a регрессии

:

= 192,66

= -9177,57



Найдём коэффициент детерминации (R-квадрат).

=r2 = 0,9252 = 0,856.

Запишем полученное уравнение регрессии:

Видим, что полученные , и того же порядка, что выводятся на точечной диаграмме (Рис. 1).

Стандартную ошибку находим по формуле:

=1213,181

Стандартную ошибку коэффициента наклона b находим по формуле:

= 16,481.

Значение t -статистики находим по формуле:

= 11,689.

Критическое значение t* находится по таблице t-распределения для двухстороннего интервала при доверительной вероятности 95% и степени свободы n-2 = 25-2 = 23. t*-критическое=2,07 (задано по условию)

Заполним второй столбец таблицы:

Табл. 2

	Ручной счет	Автоматический счет
b=	192,66
a=	-9177,57
R2	0,856
Se	1213,181
Sb	16,481
Xсредн	295,92
n=	25
t-статистика=	11,689
t*-статистика=	2,07

3. Автоматический расчет параметров регрессии, используя «Пакет анализа».

С помощью пакета «Анализ данных » строим отчет Excel:

Задаем: Сервис-Анализ Данных.

В диалоговом окне выбираем Регрессия.

В поле Входной интервал Y вводим диапазон ячеек C2:C27.

В поле Входной интервал X вводим диапазон ячеек B2:B27.

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

Выбираем переключатель Новый рабочий лист и вводим строку Параметры_регрессии.

В разделе Остатки устанавливаем флажки всех четырех параметров.

Нажимаем ОК.



Рис. 2

Получаем:

Рис. 3. Результат выполнения команды Регрессия

Коэффициенты регрессии b и a, стандартное отклонение коэффициента b, значение t- статистики находим из таблицы (см. рис. 4):

Рис. 4. Таблица 3. Результат выполнения команды Регрессии



Коэффициент детерминации , стандартную ошибку оценки и значение числа переменных n находим из таблицы 1 Регрессионная статистика (см. Рис. 5):

Рис. 5. Таблица 1. Регрессионная статистика

Выборочное среднее Xсредн определяем с помощью функции СРЗНАЧ(): Xсредн = 205,92.

Значение t*-критическое находится по таблице t-распределения для 95% доверительного интервала при n-2 = 25-2 = 23 свободы.

У нас t*-критическое=2,07 (задано по условию)

Заполним третий столбец таблицы:

Табл. 3

	Ручной счет	Автоматический счет
b=	192,66	192,66
a=	-9177,57	-9177,57
R2	0,856	0,856
Se	1213,181	6065,904
Sb	16,481	16,481
Xсредн	295,92	295,92
n=	25	25
t-статистика=	11,689	11,689
t*-статистика=	2,07	2,07

Из таблицы видим, что вычисления, полученные при ручном и автоматическом расчете, совпадают (кроме значения Se). То есть можно говорить о правильности полученных значений.

4. Проверка того, является ли связь между и реальной или случайной

Запишем полученное уравнение регрессии (линия прогнозирования):

(1)

Задача проверки состоит в том, является ли взаимосвязь (1) чистой случайностью или отражает реальную связь между X и Y. Эта задача называется задачей проверки гипотез.

Нулевая гипотеза утверждает, что между X и Y никакой взаимосвязи нет и что выявленная нами взаимосвязь в данных -- не что иное, как продукт случайного сочетания определенных пар значений X и Y. Единственный вариант, когда в рамках линейной модели Y не зависит от X имеет место лишь тогда, когда . В этом случае можно также сказать что X и Y независимы друг от друга.

Альтернативная гипотеза утверждает, что между X и Y действительно существует взаимосвязь, которая не является случайностью. Это возможно тогда, когда , т.е. в линейной модели для Y сохраняется составляющая, зависящая от X. Математическая запись этих гипотез имеет следующий вид.

Используем:

Правило проверки гипотез. Если то нулевая гипотеза отвергается и принимается альтернативная гипотеза

В нашем случае t = 11,689 и t* = 2,07 - следовательно, нулевая гипотеза отвергается. Принимается альтернативная гипотеза: с вероятностью 95% можно утверждать, что между Y и X существует реальная взаимосвязь ().

В таблице 2 (дисперсионный анализ) Значимость F = 3,72416E-11 < 0,05 - следовательно, нулевая гипотеза отвергается и принимается альтернативная гипотеза , которая говорит о том, что связь между X и Y действительно существует.

В таблице 3 P-Значение = 3,72416E-11 < 0,05 следовательно нулевая гипотеза отвергается и принимается альтернативная гипотеза .

5. Построение доверительных интервалов для линии регрессии

Формула стандартного (среднего) значения Y при заданном значении Xo:

На листе Прогноз введем заглавия новых пяти столбцов и для удобства перекопируем Таблицу 1 как значения .

2. В ячейку D2 вносим формулу .

3. В ячейку E2 вносим формулу стандартного (среднего) значения Y при заданном значении Xo

.

В ячейку F2 вносим (т.е. вычисляем корень квадратный из ячейки E2).

В ячейку G2 вносим нижнюю границу доверительного интервала

В ячейку H2 вносим верхнюю границу доверительного интервала

Выделяем ячейки D2-H2 и протягиваем до конца таблицы.

Результат представлен в таблице:

Табл. 4

horse - X	MSRP - Y	bX+a	S(Y|X)2	S(Y|X)	Интервал нижний=	Интервал верхний=
160	25525	21648	5076909	2253,2	16983,906	26312,1535
394	70545	66730,5	2671804	1634,57	63346,92	70114,0203
215	26995	32244,3	1837475	1355,53	29438,373	35050,2871
320	68665	52473,6	216372	465,158	51510,753	53436,5075
181	17494	25693,9	3646095	1909,48	21741,277	29646,5034
270	31660	42840,6	241362	491,286	41823,669	43857,5911
165	23420	22611,3	4714509	2171,29	18116,755	27105,9046
150	21960	19721,4	5842453	2417,12	14717,999	24724,8613
194	26094	28198,5	2880411	1697,18	24685,313	31711,6269
236	46750	36290,2	1034111	1016,91	34185,182	38395,1984
125	13023	14904,9	7993981	2827,36	9052,2887	20757,5713
240	32860	37060,8	908251	953,022	35088,074	39033,5858
345	53000	57290,1	713171	844,495	55542,026	59038,234
275	39647	43803,9	177747	421,601	42931,216	44676,6438
125	16905	14904,9	7993981	2827,36	9052,2887	20757,5713
285	36728	45730,5	91262,4	302,097	45105,19	46355,8702
130	16975	15868,2	7536513	2745,27	10185,519	21550,9411
130	16985	15868,2	7536513	2745,27	10185,519	21550,9411
157	25980	21070,1	5300868	2302,36	16304,162	25835,9381
140	17570	17794,8	6662320	2581,15	12451,855	23137,8045
210	35135	31281	2064063	1436,68	28307,093	34254,9673
190	24825	27427,8	3106227	1762,45	23779,56	31076,1
240	39915	37060,8	908251	953,022	35088,074	39033,5858
130	18695	15868,2	7536513	2745,27	10185,519	21550,9411
141	14999	17987,5	6577889	2564,74	12678,479	23296,5009

Таблица данных для построения доверительных интервалов

Из таблицы видно, что прогнозируемое значение MSRP попадает в границы доверительного интервала (то есть находится между нижним и верхним его значением).

Нанесём доверительные интервалы на исходную диаграмму (см. рис. 1):

Рис. 6. Линия регрессии и 95% доверительный интервал для прогнозных значений

Вывод: С вероятностью 95% можно утверждать, что прогнозируемые значения MSRP будут лежать в построенном интервале.

Проверка модели регрессии

При анализе регрессии для заданного набора данных применимы следующие допущения:

- справедлива линейная модель;

- ошибка имеет нормальное распределение со средним 0;

- ошибка имеет постоянную дисперсию;

- ошибки не зависят друг от друга.

Для проверки справедливости этих допущений выполним ряд диагностических тестов.

Проверка допущения о линейности

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

Проверка соответствия остатков нормальному распределению

Чтобы убедиться в том, что остатки удовлетворяют нормальному распределению, используем модуль StatPlus.

Запускаем программу StatPlus.

1.Возвращаемся к рабочему листу Параметры_регрессии, копируем его и вставляем как лист StatPlus.

2.Выбираем команду меню Статистика-Проверка нормальности.

3.Указываем диапазон ячеек С25:С49 и щелкаем на кнопке ОК.

4. Щелкаем на кнопке ОК.

Получаем:

Рис. 7

регрессия программный выборка корреляция

Нормальное распределение остатков подтверждается.

Проверка постоянства дисперсии

Диаграмма horse_График_Остатков уже имеется на листе Параметры_регрессии.

Для лучшей обозримости переместим этот файл на отдельный лист Остатки horse.

Получаем:



Рис. 8. Диаграмма остатков как функция horse

Как видно из диаграммы, дисперсия остатков различна и лишь приближенно можно считать, что поверка модели по этому критерию успешна.

Проверка зависимости ошибок друг от друга

Для определения степени корреляции ошибок модели между собой используем статистику Дарбина-Уотсона, которая вычисляется по формуле:

Находим значения остатков (Лист Регрессия), нужные суммы и получаем: DW=2,52.

Как видим, остатки между собой независимы, поскольку статистика .

Выводы по работе:

В ходе выполнения данной работы был проведён анализ регрессии и построена линия регрессии (линия прогноза), а также доверительные интервалы прогноза для среднего значения Y и сделана проверка модели регрессии. Вычисление параметров регрессии производилось «вручную», т.е. не используя «Пакет анализа» и автоматически (с использованием «Пакета анализа»). Результаты, полученные при ручном и автоматическом расчете, совпали, что говорит о правильности вычислений.

Полученные коэффициенты регрессии: b=192,66; a = -9177,57.

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

Коэффициент детерминации: = 0,856 - то есть, информативность модели по отношению к данным не очень высокая, поскольку значение далеко от единицы.

Также была сделана проверка того, является ли связь между и реальной или случайной (с помощью механизма проверки гипотез). В результате выяснилось, что с вероятностью 95% можно утверждать, что между Y и X существует реальная взаимосвязь.

Были вычислены нижние и верхние границы доверительного интервала и сделан вывод, что с вероятностью 95% прогнозируемые значения MSRP находятся в построенном интервале.

При проверке модели регрессии был проведён ряд диагностических тестов, а именно: проверка допущения о линейности, проверка соответствия остатков нормальному распределению, проверка постоянства дисперсии, проверка зависимости ошибок друг от друга. Были получены такие выводы: модель в первом приближении можно считать линейной, остатки модели имеют нормальное распределение, дисперсия остатков различна и лишь приближенно можно считать, что поверка модели по этому критерию успешна. Также можно говорить об независимости ошибок друг от друга, поскольку значение статистики Дарбина-Уотсона приближается к идеальному значению.



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

Постановка задачи

Имеются данные по рейтингу компаний мира Forbes-2000 (за 2009 год). Эксперты определяют их положение в рейтинге на основе рыночной стоимости, активов, доходов и объёмов продаж. Требуется построить множественную регрессию зависимости рыночной стоимости компании от активов, доходов и объёмов продаж и провести её анализ.

Требуется:

Провести анализ регрессии и построить линию регрессии (линию прогноза);

Построить укороченную модель регрессии и сравнить ее с полной регрессией;

Провести проверку модели регрессии.

Входные данные (в соответствии с последовательностью переменных):

Табл. 5

Company	Country	Industry	Market Value ($bil)	Assets ($bil)	Profits ($bil)	Sales ($bil)
Nokia	Finland	Technology Hardware & Equip	35,32	52,29	5,55	70,63
Sony	Japan	Technology Hardware & Equip	17,12	124,12	3,7	88,89
CVS Caremark	United States	Retailing	37,46	60,96	3,21	87,47
Daimler	Germany	Consumer Durables	21,21	180,08	1,88	133,43
United Technologies	United States	Conglomerates	38,53	56,47	4,69	58,68
Saudi Basic Industries	Saudi Arabia	Chemicals	31,44	72,39	5,87	40,62
Iberdrola	Spain	Utilities	32,42	114,81	3,98	35,09
Nissan Motor	Japan	Consumer Durables	14,14	119	4,83	108,46
Panasonic	Japan	Technology Hardware & Equip	28,93	71,85	2,82	90,87
MetLife	United States	Insurance	15,1	501,68	3,21	50,99
Westpac Banking Group	Australia	Banking	31,4	346,22	3,05	25,9
GlaxoSmithKline	United Kingdom	Drugs & Biotechnology	79,06	52,67	6,72	35,55
Morgan Stanley	United States	Diversified Financials	21	658,81	1,71	62,26
Telecom Italia	Italy	Telecommunications Services	23,82	117,81	3,08	41,97
Intel	United States	Semiconductors	70,86	50,72	5,29	37,59
Zurich Financial Services	Switzerland	Insurance	19,6	325,04	3,04	32,35
Mitsui & Co	Japan	Trading Companies	17,12	97,15	4,11	57,5
Comcast	United States	Media	37,62	113,02	2,55	34,26
AXA Group	France	Insurance	19,47	936,92	1,28	156,95
Bayer Group	Germany	Chemicals	36,9	71,39	2,55	45,85

Выполнение

1. Анализ регрессии и построение линии регрессии (линии прогноза).

Создание таблицы с параметрами регрессии.

Копируем данные своего варианта на отдельный лист Excel - Множественная регрессия.

Множественная регрессия дает представление о точности предикторов при их совместном использовании. В данной работе используется следующая модель множественной регрессии:

Рыночная стоимость компании = + (Активы) + (Доходы) + ( Объём продаж) + .

Чтобы выполнить анализ множественной регрессии, прибегнем к команде Регрессия модуля Пакет анализа.



Рис. 9

Получаем:

Рис. 10. Результат выполнения команды Регрессия

Исходя из полученного отчета Excel, заполним столбец «Полная регрессия:



Табл. 6

	Полная регрессия	Урезанная регрессия
Множественный R	0,605020406
R-квадрат	0,366049692
Нормированный R-квадрат	0,247184009
Стандартная ошибка	14,80235174
Значимость F	0,057402359
X1(p-значение)	0,842185886
X2(p-значение)	0,093773318
X3(p-значение)	0,367380613

Получили зависимость:

Y (Рыночная стоимость компании) = 0,26 + 0,842Х1(Активы) + 0,094 Х2(Доходы) + 0,367 Х3( Объём продаж).

Значение R-квадрат = 0,366 говорит о слабой зависимости между переменными.

Интерпретация итоговых параметров регрессии

Для интерпретации итоговых параметров регрессии рассмотрим сначала таблицу 2 Дисперсионный анализ:

Табл. 7

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

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

¦ нулевая гипотеза : коэффициенты регрессии для всех трёх предикторов равны 0;

¦ альтернативная гипотеза : по крайней мере один из трёх коэффициентов регрессии не равен 0.

В данной работе нужно сконцентрироваться только на F-отношении и Р-значении (Значимость F), которые определяют статистическую значимость регрессии.

Значимость нулевой гипотезы может быть проверена двумя способами.

1 способ.

В ячейке Е12 приводится значение 3,08 для F-отношения. Для проверки нулевой гипотезы нужно сравнить вычисленное значение F-отношения с критическим значением F*. Для получения визуального представления этой гипотезы используем рабочий лист F-распределения из рабочей книги Распределения. xls и отобразим в нём распределение F (3,16) (значения 3 и 16 - это первые два элемента столбца df таблицы Дисперсионный анализ).

Рис. 11

Поскольку F = 3,08 < F* = 3,239 , то нулевая гипотеза не может быть отклонена.

2 способ.

В столбце Значимость F, в ячейке F12, приводится значение 0,057. Это значение выше 0,05, т.е. регрессия не имеет статистическую значимость на уровне 5%. Иными словами, нулевую гипотезу невозможно отвергнуть на уровне статистической значимости 5% и мы не можем принять альтернативную гипотезу.

Если F-отношение не имеет достаточную статистическую значимость, то нет смысла интересоваться остальными результатами анализа.

Построенная регрессия не состоятельна. На этом анализ заканчивается.

Выводы по работе:

В ходе выполнения данной работы был проведён анализ регрессии и построена множественная модель линейной регрессии - зависимости рыночной стоимости компании от активов, доходов и объёмов продаж:

Y (Рыночная стоимость компании) = 0,26 + 0,842Х1(Активы) + 0,094 Х2(Доходы) + 0,367 Х3( Объём продаж).

Значения множественного коэффициента корреляции R и коэффициента детерминации (R-квадрат) далеки от единицы, что говорит о слабой зависимости между переменными.

Дисперсионный анализ подтверждает, что построенная регрессия не состоятельна. Очевидно, зависимость является более сложной (нелинейной).



Список использованной литературы

1. Бородич С.А. Эконометрика: Учеб. Пособие для студ. эконом. спец. вузов/С.А. Бородич. - Минск: Новое знание, 2001. - 407с.

2. Магнус Я.Р., Катышев П.К., Пересецкий А.А. Эконометрика. Начальный курс: Учебник. - 7-е изд., испр. - М.: Дело, 2005.

3. Практикум по эконометрике: Учеб. Пособие /И.И. Елисеева, С.В. Курышева, Н.М. Гордеенко и др.; Под ред. И.И. Елисеевой. - 2-е изд., перераб. и доп. - М.: Финансы и статистика, 2006.

4. Д. Ханк, А. Райтс. Д. Уитчер, Бизнес-прогнозирование, Вильямс, Москва, Санкт-Перербург, Киев, 2003

5. Эконометрика: Учебник/ И.И. Елисеева, С.В. Курышева, Т.В. Костеева и др.; Под ред. И.И. Елисеевой. - 2-е изд., перераб. и доп. - М: Финансы и статистика, 2005.

Размещено на Allbest.ru

...