Методика выполнения регрессионного анализа в программной среде Microsoft Excel

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

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

1. На основании данных о динамике прироста курса акций у за 10 месяцев, приведенных в таблице и предположения, что генеральное уравнение регрессии имеет вид у = 0+ 1х + , требуется:

а) Найти оценку и проверить на 5% уровне значимости уравнения регрессии, то есть гипотезу Н0:1=0;

б) Построить таблицу дисперсионного анализа для расчета F-критерия Фишера;

в) Найти коэффициент детерминации R2;

г) Найти интервальную оценку для прогноза при x=11;

Таблица 1

х	Задача 2у
1	3
2	5
3	8
4	9
5	7
6	4
7	2
8	1
9	2
10	5

Решение.

Расчеты проведем в программе Excel на листе 1 рабочей книги.

В столбец А занесем данные задачи по переменной х (месяц). В столбец В - данные переменной у (прирост курса акций) (см. рис. 1.1).

Исходные данные будут записываться по столбцам. В частности, значения х будут располагаться в ячейках А2:А11, значения у - в ячейках В2:В11.



Рис. 1.1. Данные задачи

Для того чтобы проверить, существует ли зависимость между признаками, построим диаграмму рассеивания с помощью Мастера диаграмм (см. рис. 1.2).

Рис. 1.2. Поле корреляции

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

Проведем линеаризацию модели, прологарифмируем и получим:

lny = ln0 + 1 x+ln.

Таблица 2. Исходные данные

x	y	ln(y)
1	3	1,099
2	5	1,609
3	8	2,079
4	9	2,197
5	7	1,946
6	4	1,386
7	2	0,693
8	1	0,000
9	2	0,693
10	5	1,609

Построим уравнение регрессии по табл. 2, используя Анализ данных. Для этого необходимо провести преобразование переменной у на lny, используя Мастер функции fx.

Построим регрессию Сервис>Анализ данных >Регрессия. В качестве зависимой переменной следует указать переменную lny.

Таблица 3. Итоги регрессии

Регрессионная статистика
Множественный R	0,449261
R-квадрат	0,201835
Нормированный R-квадрат	0,102065
Стандартная ошибка	0,669008
Наблюдения	10
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	1	0,905437	0,905437	2,022996	0,19273
Остаток	8	3,580577	0,447572
Итого	9	4,486014
	Коэффиц.	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%
Y-пересечение	1,907454	0,45702	4,17368	0,003106	0,853565	2,961343
x	-0,10476	0,073655	-1,42232	0,19273	-0,27461	0,065088

Получили уравнение регрессии:

lgy = 1,907-0,105 x

.

Само уравнение и коэффициенты регрессии значимо отличаются от нуля. Коэффициент при переменной x означает, что с каждым месяцем снижение курса акций составляет 0,105 д.ед.

Найдем прогнозные значения yпр. Для этого в уравнение регрессии вместо х подставляем значения месяцев. Сумма фактических и прогнозных значений у почти полностью совпала (разница за счет округления коэффициентов регрессии) (см. табл. 4).

Таблица 4. Нахождение прогнозных значений у

x	y	ln(y)	упр
1	3	1,099	6,733
2	5	1,609	6,260
3	8	2,079	5,999
4	9	2,197	5,821
5	7	1,946	5,686
6	4	1,386	5,578
7	2	0,693	5,489
8	1	0,000	5,412
9	2	0,693	5,346
10	5	1,609	5,287

Проведем анализ полученного уравнения. Найдем остатки , оценку остаточной дисперсии , стандартных ошибок коэффициентов.

Для оценки значимости уравнения регрессии и для нахождения значения F критерия, рассчитаем Qобщ,Qост и Qрегр, то есть рассчитаем таблицу дисперсионного анализа (табл. 5).

Таблица 5. Расчет таблицы однофакторного дисперсионного анализа

x	y	ln(y)	упр	(у-упр)2	(у-уср)2	(уср-упр)2
1	3	1,099	6,733	13,934	2,560	4,549
2	5	1,609	6,260	1,588	0,160	2,756
3	8	2,079	5,999	4,003	11,560	1,958
4	9	2,197	5,821	10,107	19,360	1,490
5	7	1,946	5,686	1,727	5,760	1,179
6	4	1,386	5,578	2,491	0,360	0,957
7	2	0,693	5,489	12,171	6,760	0,790
8	1	0,000	5,412	19,468	12,960	0,660
9	2	0,693	5,346	11,194	6,760	0,556
10	5	1,609	5,287	0,082	0,160	0,472
55	46	13,313	57,611	76,764	66,400	15,367
				Qост	Qобщ	Qрегр

Для оценки значимости уравнения регрессии проверим гипотезу H0: в1=0.

По таблице F-распределения находят Fкр с числом степеней свободы н1=1, н2=n-2=10-2=8. Найдем его с помощью математических функций: определим критическое значение распределения Фишера:

Fкрит=FРАСПОБР(0,05;1;8) = 5,318.

Так как Fнабл = 2,023 < Fкр=5,318, то гипотеза не принимается и уравнение считается значимым (см. табл. 3).

Проверим значимость каждого коэффициента регрессии.

Для проверки гипотезы H0: в0=0 рассчитали t-статистику. Находим критическое значение распределения Стьюдента с помощью статистической функции СТЮДРАСПОБР(0,05;8) = 2,306, где вероятность (уровень значимости) равна 0,05 и число степеней свободы n-2=10-2=8. В таблице 1.2 t-статистика параметра регрессии b1 меньше критического значения, следовательно, параметр регрессии статистически не значим, а поправочный коэффициент регрессии b0 значим, поскольку его P-вероятность меньше 0,05.

Рассчитаем несмещенную оценку остаточной дисперсии и стандартные ошибки коэффициентов регрессии:

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

дисперсионный однофакторный регрессия

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

Множественный коэффициент корреляции равен 0,449, что говорит о слабой обратной зависимости между признаками. Коэффициент детерминации показывает, что прирост курса акций на 15,6% обусловлен временем.

Найдем 95% доверительные интервалы для каждого коэффициента регрессии, а затем для прогнозного значения х=11.

Интервальная оценка для параметра в0:

Следовательно, коэффициент b0 изменяется в интервале от -2,972 до 6,786 (табл. 6).

Аналогично интервальная оценка для коэффициента в1:

в1=[-0,105±2,306*0,341]

В ячейках D22 - D25 Листа 1 рабочей книги приведены расчеты для нижней и верхней границ коэффициентов регрессии.

Таблица 6. Расчет доверительных интервалов

Доверительный интервал
b0	Верхняя граница	6,786
	Нижняя граница	-2,972
b1	Верхняя граница	0,681
	Нижняя граница	-0,891
для х=11	Верхняя граница	10,114
	Нижняя граница	0,354

Интервальная оценка для прогноза yпрогноз при x=x0 находится следующим образом:

Интервальная оценка для уравнения регрессии у при х=11:



Получено, что доверительный интервал для прогноза прироста курсовой стоимости акций при приросте фондового индекса х=11 находится в пределах от 0,354 до 10,114 с 95% уровнем надежности.

Таким образом, по полученному уравнению регрессии:

,

можно сформулировать следующие выводы:

- с каждым месяцем снижение курса акций составляет 0,105 д.ед.;

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

- уравнение регрессии не значимо, а, соответственно, его не следует применять при прогнозировании;

- поправочный коэффициент регрессии статистически значим, а коэффициент b1 - не значим;

- в 11 месяце прирост курса акций составит 5,234 д.ед.;

- доверительный интервал для прогноза прироста курсовой стоимости акций при х=11 находится в пределах 0,354 до 10,114 с 95% уровнем надежности.

Построим в поле корреляции уравнение линейной регрессии (рис. 1.3).

Рис. 1.3. Полулогарифмическая регрессия

Таким образом, полученное уравнение регрессии неточно описывает реальные данные.

2. Обозначения и наименование показателей: У - производительность труда (тыс.руб./чел.); Х1 - коэффициент платежеспособности предприятия; Х2 - удельный вес рабочих в составе промышленно-производственного персонала (%); Х6 - удельный вес потерь от брака (%); Х7 - фондоотдача (тыс.р. на 1 р.); Х9 - коэффициент ликвидности.

Таблица 7. Исходные данные

пп	У1	X1	X2	Х6	Х7	Х9
1	9,26	204,2	13,26	1,37	1,23	1,45
2	9,38	209,6	10,16	1,49	1,04	1,30
3	12,11	222,6	13,72	1,44	1,80	1,37
4	10,81	236,7	12,85	1,42	0,43	1,65
5	9,35	62,0	10,63	1,35	0,88	1,91
6	9,87	53,1	9,12	1,39	0,57	1,68
7	8,17	172,1	25,83	1,16	1,72	1,94
8	9,12	56,5	23,39	1,27	1,70	1,89
9	5,88	52,6	14,68	1,16	0,84	1,94
10	6,30	46,6	10,05	1,25	0,60	2,06
11	6,22	53,2	13,99	1,13	0,82	1,96
12	5,49	30,1	9,68	1,10	0,84	1,02
13	6,50	146,4	10,03	1,15	0,67	1,85
14	6,61	18,1	9,13	1,23	1,04	0,88
15	4,32	13,6	5,37	1,39	0,66	0,62
16	7,37	89,8	9,86	1,38	0,86	1,09
17	7,02	62,5	12,62	1,35	0,79	1,60
18	8,25	46,3	5,02	1,42	0,34	1,53
19	8,15	103,5	21,18	1,37	1,60	1,40
20	8,72	73,3	25,17	1,41	1,46	2,22
21	6,64	76,6	19,10	1,35	1,27	1,32
22	8,10	73,01	21,01	1,48	1,58	1,48
23	5,52	32,3	6,57	1,24	0,68	0,68
24	9,37	199,6	14,19	1,40	0,86	2,30
25	13,17	598,1	15,81	1,45	1,98	1,37
26	6,67	71,2	5,23	1,40	0,33	1,51
27	5,68	90,8	7,99	1,28	0,45	1,43
28	5,22	82,1	17,50	1,33	0,74	1,82
29	10,02	76,2	17,16	1,22	1,03	2,62
30	8,16	119,5	14,54	1,28	0,99	1,75
31	3,78	21,9	6,24	1,47	0,24	1,54
32	6,48	48,4	12,08	1,27	0,57	2,25
33	10,44	173,5	9,49	1,51	1,22	1,07
34	7,65	74,1	9,28	1,46	0,68	1,44
35	8,77	68,6	11,42	1,27	1,00	1,40
36	7,00	60,8	10,31	1,43	0,81	1,31
37	11,06	355,6	8,65	1,50	1,27	1,12
38	9,02	264,8	10,94	1,35	1,14	1,16
39	13,28	526,6	9,87	1,41	1,89	0,88
40	9,27	118,6	6,14	1,47	0,67	1,07
41	6,70	37,1	12,93	1,35	0,96	1,24
42	6,69	57,7	9,78	1,40	0,67	1,49
43	9,42	51,6	13,22	1,20	0,98	2,03
44	7,24	64,7	17,29	1,15	1,16	1,84
45	5,39	48,3	7,11	1,09	0,54	1,22
46	5,61	15,0	22,49	1,26	1,23	1,72
47	5,59	87,5	12,14	1,36	0,78	1,75
48	6,57	108,4	15,25	1,15	1,16	1,46
49	6,54	267,3	31,34	1,87	4,44	1,60
50	4,23	34,2	11,56	2,17	1,06	1,47
51	5,22	26,8	30,14	1,61	2,13	1,38
52	18,00	43,6	19,71	1,34	1,21	1,41
53	11,03	72,0	23,56	1,22	2,20	1,39

Решение

Задачу построения модели множественной регрессии решим с помощью пакета «Анализ данных» в Excel (Рис. 2.1).



Рис. 2.1 Диалоговое окно «Регрессия»

При использовании инструмента «Регрессия» входным интервалом для Y будут ячейки B2:B54, для X - ячейки C2:G54. Результат регрессионного анализа представим в табл. 8

Таблица 8. Вывод итогов регрессионного анализа

Регрессионная статистика
Множественный R	0,571621
R-квадрат	0,326751
Нормированный R-квадрат	0,255129
Стандартная ошибка	2,252779
Наблюдения	53
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	5	115,7646	23,15293	4,562143	0,001793
Остаток	47	238,5256	5,075012
Итого	52	354,2902
	Коэффиц.	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%
Y-пересечение	7,796727	2,913582	2,675993	0,010224	1,935356	13,6581
X1	0,013452	0,003359	4,004703	0,00022	0,006694	0,020209
X2	0,077412	0,108381	0,714256	0,478601	-0,14062	0,295446
Х6	-1,51268	1,922485	-0,78683	0,435329	-5,38022	2,354865
Х7	-0,38708	1,088867	-0,35549	0,723813	-2,5776	1,803437
Х9	0,044043	0,969469	0,04543	0,963957	-1,90628	1,994363

В столбце «Коэффициенты» получены коэффициенты уравнения регрессии.

Таким образом, получили уравнение регрессии:

Параметры регрессии показывают, что при изменении коэффициент платежеспособности предприятия на 1 единицу производительность труда увеличивается на 13 руб./чел., при увеличении удельного веса рабочих в составе ППП на 1% производительность труда увеличивается на 0,077 тыс. руб./чел., при увеличении удельного веса потерь от брака на 1% производительность труда снижается на 1,513 тыс. руб./чел., при увеличении фондоотдачи на 1 тыс. руб./чел. производительность труда снижается на 0,387 тыс. руб./чел., а при увеличении коэффициента ликвидности на 1 единицу производительность труда увеличивается на 0,044 тыс. руб./чел.

Стандартные ошибки коэффициентов составляют соответствующую графу в таблице 9.

Для проверки значимости коэффициентов регрессии рассчитаны t -статистики. Находим критическое значение распределения Стьюдента для вероятности (уровня значимости) 0,05 и число степеней свободы:

н = n-k-1=53-5-1=47.

Критическое значение находим из таблиц распределения Стьюдента или с помощью статистической функции:

СТЮДРАСПОБР(0,05;47) = 2,012.

Для проверки гипотезы H0: вj=0 сравниваем полученные значения для всех коэффициентов tнабл с tкр=2,012. Получим, что все коэффициенты не значимы, кроме b0 и b1. То есть на производительность труда значимо оказывает влияние параметр «Коэффициент платежеспособности предприятия».

Для проверки значимости коэффициентов также можно использовать Р-значения.

По величине Р-значения возможно определять значимость коэффициентов, не находя критическое значение t-статистики. Если значение t-статистики велико, то соответствующее значение вероятности значимости мало - меньше 0,05, и можно считать, что коэффициент регрессии значим. И наоборот, если значение t-статистики мало, соответственно вероятность значимости больше 0,05 - коэффициент считается незначимым. Результат проверки коэффициентов на значимость будет одинаковым.

Далее представлены доверительные интервалы (нижняя и верхняя границы), которые показывают, в каких пределах лежат коэффициенты полученного уравнения регрессии с 95%-ой надежностью.

В разделе Регрессионная статистика получили:

- множественный коэффициент корреляции (множественный R) равен 0,572, что говорит о заметной (по шкале Чеддока) степени связи между результативным и факторными признаками;

- коэффициент детерминации R2=0,327 показывает, что модель достаточно описывает данные, то есть 32,7% вариации производительности труда описывается факторами, входящими в полученную модель;

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

В Дисперсионном анализе вычисляются:

- df - число степеней свободы;

- SS - суммы квадратов разностей;

- МS - оценки дисперсий;

- F - вычисленное значение критерия Фишера;

- Значимость F.

Сумма квадратов регрессии вычисляется по формуле:

Qрегр = SS1;

сумма квадратов остатков:

Qост = SS2;

общая сумма квадратов:

Qобщ=SS.

Выполняется условие:

SS1+SS2=SS.

То есть 115,7646+238,5256=354,2902. Число степеней свободы df для SS1 равно df1=5 (k - число независимых переменных или факторов), для SS2: df2 = n - k - 1= 53-5-1 =47, для SS: df = n - 1= 53 - 1 =52.

Получены оценки средних квадратов:



наблюдаемое значение F-критерия:

Fнабл=4,562.

Сравним полученное значение Fнабл с критическим. Так как Fкрит=2,413<Fнабл=4,562, то гипотеза Н0: в1=в2=0 отвергается и уравнение считается значимым.

Значимость F - это вероятность значимости для F критерия. В нашем случае она равна 0,002, то есть гипотеза H0: в1=в2=0 отвергается и уравнение считается значимым.

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

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