Автоматизированный априорный анализ статистической совокупности в среде MS Excel

     Результаты  выполненных расчетов представлены в табл. 2.4 Рабочего файла.

     Вывод:

     Значение  коэффициента η =…0,90…………………, что в соответствии с оценочной шкалой Чэддока говорит о  весьма тесной…………………………степени связи изучаемых признаков.

     Задача 4. Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента Регрессия надстройки Пакет анализа и оценка тесноты связи на основе линейного коэффициента корреляции r.

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

      Инструмент  Регрессия на основе исходных данных (x_i , y_i), производит расчет параметров а₀ и а₁ уравнения однофакторной линейной регрессии , а также вычисление ряда показателей, необходимых для проверки адекватности построенного уравнения исходным (фактическим) данным.

      Примечание. В результате работы инструмента Регрессия получены четыре результативные таблицы (начиная с заданной ячейки А75). Эти таблицы выводятся в Рабочий файл без нумерации, поэтому необходимо присвоить им номера табл.2.5 – табл.2.8 в соответствии с их порядком.

     Вывод:

     Рассчитанные  в табл.2.7 (ячейки В91 и В92) коэффициенты а₀ и а₁ позволяют построить линейную регрессионную модель связи изучаемых признаков в виде уравнения …-496,86+1,08х………………….

     4.2. В случае линейности функции  связи для оценки тесноты связи признаков X и Y, устанавливаемой по построенной модели, используется линейный коэффициент корреляции r.

     Значение  коэффициента корреляции r приводится в табл.2.5 в ячейке В78 (термин "Множественный R").

     Вывод:

     Значение  коэффициента корреляции r =…0,91………… , что в соответствии с оценочной шкалой Чэддока говорит о ..….весьма тесной ………………………. степени связи изучаемых признаков.

     Задача 5. Анализ адекватности и практической пригодности построенной линейной регрессионной модели.

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

     Оценка  соответствия построенной регрессионной  модели исходным (фактическим) значениям  признаков X и Y выполняется в 4 этапа:

1. оценка статистической значимости коэффициентов уравнения а₀, а₁ и определение их доверительных интервалов для заданного уровня надежности;
2. определение практической пригодности построенной модели на основе оценок линейного коэффициента корреляции  r  и индекса детерминации R²;
3. проверка значимости уравнения регрессии в целом по F-критерию Фишера;
4. оценка погрешности регрессионной модели.
1. Оценка статистической значимости коэффициентов уравнения а₀, а₁ и определение их доверительных интервалов

     Так как коэффициенты уравнения а₀ , а₁ рассчитывались, исходя из значений признаков только для 30-ти пар (x_i , y_i), то полученные значения коэффициентов являются лишь приближенными оценками фактических параметров связи а₀ , а₁. Поэтому необходимо:

1. проверить значения коэффициентов на неслучайность (т.е. узнать, насколько они типичны для всей генеральной совокупности предприятий отрасли);
2. определить (с заданной доверительной вероятностью 0,95 и 0,683) пределы, в которых могут находиться значения а₀, а₁ для генеральной совокупности предприятий.

     Для анализа коэффициентов а₀, а₁ линейного уравнения регрессии используется табл.2.7, в которой:

         – значения  коэффициентов а₀, а₁ приведены в ячейках В91 и В92 соответственно;

         – рассчитанный  уровень значимости коэффициентов  уравнения приведен в ячейках  Е91 и Е92;

         – доверительные  интервалы коэффициентов с уровнем  надежности Р=0,95 и Р=0,683 указаны в диапазоне ячеек F91:I92.

     5.1.1. Определение значимости  коэффициентов уравнения

     Уровень значимости – это величина α=1–Р, где Р – заданный уровень надежности (доверительная вероятность).

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

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

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

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

     Вывод:

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

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

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

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

     Таблица 2.9

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

     Вывод:

     В  генеральной  совокупности  предприятий  значение  коэффициента  а₀ следует ожидать с надежностью Р=0,95 в пределах …1109,69 а₀ 115,95., значение коэффициента а₁ в пределах -0,901 а₁ 1,28… Уменьшение уровня надежности ведет к расширению (сужению) доверительных интервалов коэффициентов уравнения.

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

     Практическую  пригодность построенной модели можно охарактеризовать по величине линейного коэффициента корреляции 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,913….…….., R² =…0,833…..………. Поскольку и , то построенная линейная регрессионная модель связи пригодна (не пригодна) для практического использования.

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

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

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

     Вывод:

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

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

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

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

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

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

     Вывод:

     Погрешность линейной регрессионной модели составляет ^.100=^{__275,2427/_2999,97_.}100=…..9,17……..%, что подтверждает (не подтверждает) адекватность построенной модели -496,86+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……}^{.__3210/__2999,97_____} =…1,16……..%

     Вывод:

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

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

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

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

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

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

     Вывод:

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

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

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

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

     Таблица 2.10

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

Вид уравнения	Уравнение регрессии	Индекс детерминации R2
Полином 2-го порядка	7Е-05х2+0,670х+146,8	0,835
Полином 3-го порядка	1Е-0,7х3-0,001х2+5,042х-4285	0,838
Степенная функция	0,221х1,777	0,837

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

      Вывод:

      Максимальное  значение индекса детерминации R² =…0,838………............ Следовательно, наиболее адекватное исходным данным нелинейное уравнение регрессии имеет вид  …_{1Е-0,7х³-0,001х²+5,042х-4285}………………………

 
 


       ПРИЛОЖЕНИЕ 

Результативные таблицы и графики

 


		Таблица 2.1		Номер варианта
Исходные  данные				44
Номер предприятия	Среднегодовая стоимость основных производственных фондов, млн.руб.	Выпуск  продукции, млн. руб.
1	2060,00	1610,00
2	2221,00	2139,00
3	2405,00	1840,00
4	2474,00	2369,00
5	2566,00	2530,00
6	2612,00	2668,00
7	2796,00	2277,00
8	2865,00	2185,00
9	2911,00	2599,00
10	3003,00	2898,00
12	3026,00	3082,00
13	3095,00	2829,00
14	3141,00	2967,00
15	3164,00	3220,00
16	3233,00	2875,00
17	3256,00	2944,00
18	3325,00	2760,00
19	3325,00	3358,00
20	3325,00	2990,00
21	3417,00	3726,00
22	3555,00	2990,00
23	3601,00	3496,00
24	3624,00	3703,00
25	3647,00	2990,00
26	3716,00	3427,00
27	3739,00	3151,00
28	3808,00	4071,00
29	3969,00	3910,00
31	4061,00	4025,00
32	4360,00	4370,00
				Таблица 2.2
Зависимость выпуска продукции от среднегодовой  стоимости основных фондов
Номер группы	Группы  предприятий по стоимости основеных  фондов	Число предприятий	Выпуск  продукции
			Всего	В среднем  на одно  предприятие
1	2060-2520	4	7958,00	1989,50
2	2520-2980	5	12259,00	2451,80
3	2980-3440	11	33649,00	3059,00
4	3440-3900	7	23828,00	3404,00
5	3900-4360	3	12305,00	4101,67
Итого		30	89999,00	2999,97
			Таблица 2.3
Показатели  внутригрупповой вариации
Номер группы	Группы  предприятий по стоимости основеных  фондов	Число предприятий	Внутригрупповая дисперсия
1	2060-2520	4	83185,25
2	2520-2980	5	35252,56
3	2980-3440	11	71751,64
4	3440-3900	7	135726,29
5	3900-4360	3	38205,56
Итого		30
			Таблица 2.4
Показатели  дисперсии и эмпирического корреляционного  отношения
Общая дисперсия	Средняя из внутригрупповых дисперсия	Межгрупповая  дисперсия	Эмпирическое  корреляционное отношение
425728,0322	78765,74889	346962,2833	0,902765617
Выходные  таблицы
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,91318826
R-квадрат	0,833912798
Нормированный R-квадрат	0,827981112
Стандартная ошибка	275,2427064
Наблюдения	30
Дисперсионный анализ
	df	SS	MS	F
Регрессия	1	10650601,64	10650601,64	140,5861384
Остаток	28	2121239,328	75758,54744
Итого	29	12771840,97
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	-496,8634642	299,1700429	-1,660806207	0,107911312
Переменная X 1	1,089355181	0,09187519	11,85690257	1,97601E-12
ВЫВОД ОСТАТКА
Наблюдение	Предсказанное Y	Остатки
1	1747,208209	-137,2082086
2	1922,594393	216,4056073
3	2123,035746	-283,035746
4	2198,201253	170,7987465
5	2298,42193	231,5780699
6	2348,532268	319,4677315
7	2548,973622	-271,9736218
8	2624,139129	-439,1391292
9	2674,249468	-75,24946756
10	2774,470144	123,5298558
11	2799,525313	282,4746866
12	2874,690821	-45,69082086
13	2924,801159	42,19884082
14	2949,856328	270,1436717
15	3025,021836	-150,0218358
16	3050,077005	-106,077005
17	3125,242512	-365,2425125
18	3125,242512	232,7574875
19	3125,242512	-135,2425125
20	3225,463189	500,5368109
21	3375,794204	-385,7942041
22	3425,904542	70,09545758
23	3450,959712	252,0402884
24	3476,014881	-486,0148807
25	3551,180388	-124,1803882
26	3576,235557	-425,2355574
27	3651,401065	419,5989351
28	3826,787249	83,21275099
29	3927,007926	97,99207434
30	4252,725125	117,2748752