Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel

Режим работы инструмента Регрессия использует по умолчанию уровень надежности Р=0,95. Для этого уровня надежности уровень значимости равен α = 1 – 0,95 = 0,05. Этот уровень значимости считается заданным.

В инструменте  Регрессия надстройки Пакет анализа для каждого из коэффициентов а₀ и а₁ вычисляется уровень его значимости α_р, который указан в результативной таблице (табл.2.7 термин "Р-значение"). Если рассчитанный для коэффициентов а₀, а₁ уровень значимости α_р, меньше заданного уровня значимости α= 0,05, то этот коэффициент признается неслучайным (т.е. типичным для генеральной совокупности), в противном случае – случайным.

Примечание. В случае, если признается случайным свободный член а₀, то уравнение регрессии целесообразно построить заново без свободного члена а₀. В этом случае в диалоговом окне Регрессия необходимо задать те же самые параметры за исключением лишь того, что следует активизировать флажок Константа-ноль (это означает, что модель будет строиться при условии а₀=0). В лабораторной работе такой шаг не предусмотрен.

Если  незначимым (случайным) является коэффициент  регрессии а₁, то взаимосвязь  между признаками X и Y в принципе не может аппроксимироваться  линейной моделью.

Вывод:

Для свободного члена а₀ уравнения регрессии рассчитанный уровень значимости есть α_р =0,36. Так как он больше заданного уровня значимости α=0,05, то коэффициент а₀ признается случайным.

Для коэффициента регрессии  а₁  рассчитанный  уровень  значимости есть α_р = 0,001 Так как он меньше заданного уровня значимости α=0,05, то коэффициент а₁ признается типичным.

5.1.2. Зависимость доверительных  интервалов коэффициентов  уравнения от заданного  уровня надежности

Доверительные интервалы коэффициентов а₀, а₁ построенного уравнения регрессии при уровнях надежности Р=0,95 и Р=0,683 представлены в табл.2.7, на основе которой формируется табл.2.9.

Таблица 2.9

Границы доверительных интервалов коэффициентов уравнения

Вывод:

В  генеральной  совокупности  предприятий  значение  коэффициента  а₀ следует ожидать с надежностью Р=0,95 в пределах -36,05 а₀ 13,68, значение коэффициента а₁ в пределах 0,90 а₁ 1,27. Уменьшение уровня надежности ведет к сужению доверительных интервалов коэффициентов уравнения.

• 5.2. Определение практической пригодности построенной регрессионной модели.

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

• близость к единице свидетельствует о хорошей аппроксимации исходных (фактических) данных с помощью построенной линейной функции связи ;
• близость к нулю означает, что связь между фактическими данными Х и Y нельзя аппроксимировать как построенной, так и любой другой линейной моделью, и, следовательно, для моделирования связи следует использовать какую-либо подходящую нелинейную модель.

     Пригодность построенной регрессионной модели для практического использования  можно оценить и по величине индекса детерминации R², показывающего, какая часть общей вариации признака Y объясняется в построенной модели вариацией фактора X.

     В основе такой оценки лежит равенство R = r (имеющее место для линейных моделей связи), а также шкала Чэддока, устанавливающая качественную характеристику тесноты связи в зависимости от величины r.

     Согласно  шкале Чэддока высокая степень тесноты связи признаков достигается лишь при >0,7, т.е. при >0,7. Для индекса детерминации R² это означает выполнение неравенства R² >0,5.

     При недостаточно тесной связи признаков  X, Y (слабой, умеренной, заметной) имеет место неравенство 0,7, а следовательно, и неравенство .

     С учетом вышесказанного, практическая пригодность построенной модели связи  оценивается по величине R² следующим образом:

• неравенство R² >0,5 позволяет считать, что построенная модель пригодна для практического применения, т.к. в ней достигается высокая степень тесноты связи признаков X и Y, при которой более 50% вариации признака Y объясняется влиянием фактора Х;
• неравенство означает, что построенная модель связи практического значения не имеет ввиду недостаточной тесноты связи между признаками X и Y, при которой менее 50% вариации признака Y объясняется влиянием фактора Х, и, следовательно, фактор Х влияет на вариацию Y в значительно меньшей степени, чем другие (неучтенные в модели) факторы.

     Значение  индекса детерминации R² приводится в табл.2.5 в ячейке В79 (термин "R - квадрат").

     Вывод:

     Значение  линейного коэффициента корреляции r и значение индекса детерминации R² согласно табл. 2.5 равны: r = 0,91, R² =0,83. Поскольку и , то построенная линейная регрессионная модель связи пригодна для практического использования.

5.3. Общая оценка адекватности  регрессионной модели по F-критерию Фишера

     Адекватность  построенной регрессионной модели фактическим данным (x_i, y_i) устанавливается по критерию Р.Фишера, оценивающему статистическую значимость (неслучайность) индекса детерминации R².

     Рассчитанная  для уравнения регрессии оценка значимости R² приведена в табл.2.6 в ячейке F86 (термин "Значимость F"). Если она меньше заданного уровня значимости α=0,05, то величина R² признается неслучайной и, следовательно, построенное уравнение регрессии может быть использовано как модель связи между признаками Х и Y для генеральной совокупности предприятий отрасли.

       Вывод:

     Рассчитанный  уровень значимости α_р индекса детерминации R² есть α_р=0,00001.Так как он меньше заданного уровня значимости α=0,05, то значение R² признается типичным и модель связи между признаками Х и Y  -11,18+1,08х применима для генеральной совокупности предприятий отрасли в целом.

 


5.4. Оценка погрешности регрессионной модели

     Погрешность регрессионной модели можно оценить  по величине стандартной ошибки построенного линейного уравнения регрессии . Величина ошибки оценивается как среднее квадратическое отклонение по совокупности отклонений исходных (фактических) значений y_i признака Y от его теоретических значений , рассчитанных по построенной модели.

     Погрешность регрессионной модели выражается в  процентах и рассчитывается как  величина ^.100.

     В адекватных моделях погрешность  не должна превышать 12%-15%.

     Значение  приводится в выходной таблице "Регрессионная статистика" (табл.2.5) в ячейке В81 (термин "Стандартная ошибка"), значение    – в таблице описательных  статистик (ЛР-1, Лист 1, табл.3,ячейка В48).

     Вывод:

     Погрешность линейной регрессионной модели составляет

       ^.100=(11,967074/6080)*100=1, что подтверждает адекватность построенной модели - 11,18+1,08х.

 
 


Задача 6. Дать экономическую интерпретацию:

1) коэффициента  регрессии а₁;

3) остаточных  величин  _i.

2) коэффициента  эластичности К_Э;

6.1. Экономическая интерпретация коэффициента регрессии а₁

     В случае линейного уравнения регрессии  =a₀+a₁x величина коэффициента регрессии a₁ показывает, на сколько в среднем (в абсолютном выражении) изменяется значение результативного признака Y при изменении фактора Х на единицу его измерения. Знак при a₁ показывает направление этого изменения.

     Вывод:

Коэффициент регрессии а₁ =1,08. показывает, что при увеличении факторного признака Среднегодовая стоимость основных производственных фондов на 1 млн руб. значение результативного признака Выпуск продукции увеличивается (уменьшается) в среднем на 1,08 млн руб.

6.2. Экономическая интерпретация  коэффициента эластичности.

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

     Средние значения и приведены в таблице описательных статистик (ЛР-1, Лист 1, табл.3).

     Расчет  коэффициента эластичности:

      =_{1,08*(6080/5673,85)=1,15%}

     Вывод:

     Значение  коэффициента эластичности К_э=1,15 показывает, что при увеличении факторного признака Среднегодовая стоимость основных производственных фондов на 1% значение результативного признака Выпуск продукции увеличивается в среднем на 1,15%.

6.3. Экономическая интерпретация  остаточных величин  ε_i

     Каждый  их остатков характеризует отклонение фактического значения y_i от теоретического значения , рассчитанного по построенной регрессионной модели и определяющего, какого среднего значения    следует ожидать, когда фактор Х принимает значение x_i.

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

     Значения  остатков _i (таблица остатков из диапазона А98:С128) имеют как положительные, так и отрицательные отклонения от ожидаемого в среднем объема выпуска продукции (которые в итоге уравновешиваются, т.е. ).

     Экономический интерес представляют наибольшие расхождения между фактическим объемом выпускаемой продукции y_i и ожидаемым усредненным объемом .

     Вывод:

     Согласно  таблице остатков максимальное превышение ожидаемого среднего объема выпускаемой  продукции  имеют три предприятия - с номерами 6, 27, 20,  а максимальные отрицательные отклонения - три предприятия с номерами 8, 24, 26 . Именно эти шесть предприятий подлежат дальнейшему экономическому анализу для выяснения причин наибольших отклонений объема выпускаемой ими продукции от ожидаемого среднего объема и выявления резервов роста производства.

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

      Уравнения регрессии и их графики построены  для 3-х видов нелинейной зависимости  между признаками и представлены на диаграмме 2.1 Рабочего файла.

      Уравнения регрессии и соответствующие  им индексы детерминации R² приведены в табл.2.10 (при заполнении данной таблицы коэффициенты уравнений необходимо указывать не в компьютерном формате, а в общепринятой десятичной форме чисел).

     Таблица 2.10

     Регрессионные модели связи

Вид уравнения	Уравнение регрессии	Индекс детерминации  R2
Полином 2-го порядка	3Е-05х2+0,6702х+271,71	0,8353
Полином 3-го порядка	4Е-08х3-0,0007х2+5,0549х-8150,3	0,8381
Степенная функция	0,1939х1,1795	0,8371

      Выбор наиболее адекватного уравнения  регрессии определяется максимальным значением индекса детерминации R²: чем ближе значение R² к единице, тем более точно регрессионная модель соответствует фактическим данным.

      Вывод:

      Максимальное  значение индекса детерминации R² =0,8381. Следовательно, наиболее адекватное исходным данным нелинейное уравнение регрессии имеет вид 4Е-08х³-0,0007х²+5,0549х-8150,3.

 


ПРИЛОЖЕНИЕ 

								таблица 2.1
			исходные  данные
Номер предприятия			Среднегодовая стоимость основных производственных фондов, млн.руб.					Выпуск  продукции, млн. руб.
5			80,00					70,00
23			87,00					93,00
27			95,00					80,00
1			98,00					103,00
8			102,00					110,00
32			104,00					116,00
22			112,00					99,00
19			115,00					95,00
2			117,00					113,00
3			121,00					126,00
13			122,00					134,00
26			125,00					123,00
9			127,00					129,00
4			128,00					140,00
28			131,00					125,00
17			132,00					128,00
6			135,00					120,00
14			135,00					146,00
25			135,00					130,00
7			139,00					162,00
30			145,00					130,00
18			147,00					152,00
10			148,00					161,00
20			149,00					130,00
24			152,00					149,00
29			153,00					137,00
15			156,00					177,00
11			163,00					170,00
21			167,00					175,00
16			180,00					190,00
								таблица 2.2
Зависимость выпуска продукции от среднегодовой стоимости основных фондов
Номер группы		Группы  предприятий по стоимости основеных  фондов				Число предприятий				Выпуск  продукции
										Всего						В среднем  на одно  предприятие
1		3905-4775				4				346,00						86,50
2		4775-5645				5				533,00						106,60
3		5645-6515				11				1463,00						133,00
4		6515-7385				7				1036,00						148,00
5		7385-8255				3				535,00						178,33
Итого						30				3913,00						130,43
										таблица 2.3
Показатели  внутригрупповой вариации
Номер группы		Группы  предприятий по стоимости основеных  фондов				Число предприятий				Внутригрупповая дисперсия
1		3905-4775				4				157,25
2		4775-5645				5				127,25
3		5645-6515				11				186,19
4		6515-7385				7				42,50
5		7385-8255				3				70,50
Итого						30
										Таблица 2.4
Показатели  дисперсии и эмпирического корреляционного  отношения
Общая дисперсия		Средняя из внутригрупповых дисперсия				Межгрупповая  дисперсия				Эмпирическое  корреляционное отношение
804,7788889		127,4104167				677,3684722				0,917432671
Выходные  таблицы
ВЫВОД ИТОГОВ
				Таблица 2.5
Регрессионная статистика
Множественный R				0,91318826
R-квадрат				0,833912798
Нормированный R-квадрат				0,827981112
Стандартная ошибка				11,96707419
Наблюдения				30
Дисперсионный анализ					Таблица 2.6.
	df				SS				MS			F					Значимость F
Регрессия	1				20133,46245				20133,46245			140,5861384					1,97601E-12
Остаток	28				4009,904212				143,2108647
Итого	29				24143,36667

Таблица 2.7

		Коэффициенты		Стандартная ошибка		t-статистика		P-Значение
Y-пересечение		-11,18284019		12,14197063		-0,92100702		0,364914527
Переменная X 1		1,089355181		0,09187519		11,85690257		1,97601E-12
Нижние 95%	Верхние 95%		Нижние 68,3%		Верхние 68,3%
-36,05453922	13,68885884		-23,55361756		1,187937182
0,901157387	1,277552975		0,995748668		1,182961694

 


		Таблица 2.8
	ВЫВОД ОСТАТКА
Наблюдение	Предсказанное Y	Остатки
1	75,96557429	-5,965574286
2	83,59106055	9,408939448
3	92,305902	-12,305902
4	95,57396754	7,426032457
5	99,93138827	10,06861173
6	102,1100986	13,88990137
7	110,8249401	-11,82494008
8	114,0930056	-19,09300562
9	116,271716	-3,271715981
10	120,6291367	5,370863295
11	121,7184919	12,28150811
12	124,9865574	-1,986557429
13	127,1652678	1,83473221
14	128,254623	11,74537703
15	131,5226885	-6,522688514
16	132,6120437	-4,612043695
17	135,8801092	-15,88010924
18	135,8801092	10,11989076
19	135,8801092	-5,880109238
20	140,23753	21,76247004
21	146,773661	-16,77366105
22	148,9523714	3,047628591
23	150,0417266	10,95827341
24	151,1310818	-21,13108177
25	154,3991473	-5,399147314
26	155,4885025	-18,4885025
27	158,756568	18,24343196
28	166,3820543	3,617945695
29	170,739475	4,260524972
30	184,9010924	5,098907619

 


               Рис.2.1.  Уравнения регрессии и их графики.

 
 
 
 
 
 
 
 
 
 


   ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ  ИНСТИТУТ

   КАФЕДРА СТАТИСТИКИ

 


   О Т Ч Е Т 

   о результатах выполнения компьютерной лабораторной работы

   Автоматизированный  корреляционно-регрессионный  анализ взаимосвязи  статистических данных в среде MS Excel

   Вариант №85

 
 
 
 
 


Выполнил: ст. III курса вечерняя        группа, специальности Финансы и Кредит   Хоромина Ю.В.

Проверил:  Новокупова И. Н.

 
 
 
 
 


Владимир  2010 г.

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

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

    Полученные  два ряда динамики представлены в  формате электронных таблиц процессора Excel, годовые данные -  в диапазоне ячеек A6:B12, а данные за 6-ой год по месяцам - в диапазоне D6:E19.

	Исходные  данные
Годы	Выпуск  продукции, млн. руб.		Месяцы	Выпуск  продукции, млн. руб.
1	15770		январь	1450
2	16010		февраль	1516
3	16400		март	1575
4	16280		апрель	1545
5	16515		май	1605
6	19377		июнь	1585
			июль	1641
			август	1616
			сентябрь	1695
			октябрь	1716
			ноябрь	1728
			декабрь	1705
			Итого	19377

Задание 1.

Расчёт  и анализ показателей  ряда динамики выпуска продукции за шестилетний период.

Выполнение  Задания 1 заключается в решении двух задач:

Задача 1. Расчет цепных и базисных показателей динамики: абсолютный прирост (сокращение); темп роста (снижения); темп прироста (сокращения) и абсолютное значение 1 % прироста.

Задача 2. Расчет средних показателей ряда динамики: средний уровень ряда динамики; средний абсолютный прирост; средний темп роста и средний темп прироста.

Полученные результаты представлены в таблицах:

 


Показатели  динамики выпуска продукции

Показатели  динамики выпуска продукции
Годы	Выпуск  продукции, млн. руб.	Абсолютный  прирост,   млн. руб.		Темп  роста,  %		Темп  прироста,  %		Абсолютное  значение  1% прироста
		цепной	базисный	цепной	базисный	цепной	базисный
1-й	15770,00
2-й	16010,00	240,00	240,00	101,5	101,5	1,5	1,5	157,7
3-й	16400,00	390,00	630,00	102,4	104,0	2,4	4,0	160,1
4-й	16280,00	-120,00	510,00	99,3	103,2	-0,7	3,2	164
5-й	16515,00	235,00	745,00	101,4	104,7	1,4	4,7	162,8
6-й	19377,00	2 862,00	3 607,00	117,3	122,9	17,3	22,9	165,15

 


     Средние показатели ряда динамики

Средний уровень ряда динамики,млн. руб.,	16725,33
Средний абсолютный прирост,млн. руб.,	721,40
Средний темп роста, %,	104,2
Средний темп прироста, %,	4,2

 


      Задание 2.

Прогноз показателя выпуска продукции на 7-ой год методом экстраполяции.

      Выполнение  Задания 2 заключается в решении двух задач:

Задача 1. Прогнозирование выпуска продукции предприятием на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста.

Прогноз выпуска продукции на 7-ой год
По  среднему абсолютному приросту,млн.руб.,	20098,40
По  среднему темпу роста, %,	20190,83

 


Задача 2. Прогнозирование выпуска продукции предприятием на год вперёд с использованием аналитического выравнивания ряда динамики по прямой, параболе и полиному 3-го порядка.

     

Задание 3.

Выявление тенденции развития изучаемого явления (тренда) по данным о выпуске продукции по месяцам за 6-ой год методами скользящей средней и аналитического выравнивания.

      Выполнение  Задания 3 заключается в решении двух задач:

Задача 1. Расчет скользящей средней, полученной на основе трёхчленной скользящей суммы.

Выпуск  продукции за 6-ой год
Месяцы	Выпуск  продукции, млн. руб.	Скользящее  среднее
январь	1450,00
февраль	1516,00	1513,67
март	1575,00	1545,33
апрель	1545,00	1575,00
май	1605,00	1578,33
июнь	1585,00	1610,33
июль	1641,00	1614,00
август	1616,00	1650,67
сентябрь	1695,00	1675,67
октябрь	1716,00	1713,00
ноябрь	1728,00	1716,33
декабрь	1705,00

 


     Задача  2. Аналитическое выравнивание по прямой и параболе.