Министерство образования и науки РФ

                                        Федеральное агентство по образованию

                                 Государственное образовательное  учреждение

                                      Высшего профессионального образования

                        Всероссийский заочный финансово-экономический институт

                                                          Филиал в г. Туле

О Т Ч Е Т

                                 о результатах выполнения

                      компьютерной лабораторной работы №2

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

         Вариант № 24

                                                                               Выполнил: студент третьего курса

                                                                                       Факультета УС

                                                                                       Специальность БУА и А

                                                                                        Вечерняя группа №311

 

                                                                                     Проверил: Шелобаева  И.С.

Тула, 2007 г.

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

Корреляционно-регрессионный анализ взаимосвязи признаков является составной частью проводимого статистического исследования двух экономических показателей ста­тистической совокупности 32 предприятий и частично использует результаты Лабораторной работы № 1.

В Лабораторной работе № 2 изучается взаимосвязь между факторным признаком Среднегодовая стоимость основных производственных фондов (признак Х) и результативным признаком Выпуск продукции (признак Y), значениями которых являются исходные данные Лабораторной работы № 1 после исключения из них аномальных значений.

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

Номер предприятия

Среднегодовая стоимость основных производственных фондов, млн.руб.

Выпуск продукции, млн. руб.

1

1394,00

1339,00

2

1641,00

1469,00

3

1693,00

1638,00

4

1784,00

1820,00

5

1160,00

910,00

6

1875,00

1560,00

7

1927,00

2106,00

8

1446,00

1430,00

9

1771,00

1677,00

10

2044,00

2093,00

11

2239,00

2210,00

13

1706,00

1742,00

14

1875,00

1898,00

15

2148,00

2301,00

16

2460,00

2470,00

17

1836,00

1664,00

18

2031,00

1976,00

19

1615,00

1235,00

20

2057,00

1690,00

21

2291,00

2275,00

22

1576,00

1287,00

23

1251,00

1209,00

24

2096,00

1937,00

25

1875,00

1690,00

26

1745,00

1599,00

27

1355,00

1040,00

28

1823,00

1625,00

29

2109,00

1781,00

30

2005,00

1690,00

32

1472,00

1508,00

В процессе статистического исследования необходимо ре­шить ряд задач.

1. Установить наличие статистической связи между фактор­ным признаком Х и результативным признаком Y:

а) графическим методом;

б) методом сопоставления параллельных рядов.

2. Установить наличие корреляционной связи между призна­ками Х и Y методом аналитической группировки.

3. Оценить тесноту связи признаков Х и Y на основе:

а) эмпирического корреляционного отношения ;

б) линейного коэффициента корреляции r.

4. Построить однофакторную линейную регрессионную мо­дель связи признаков Х и Y, используя инструмент Регрессия над­стройки Пакет анализа.

5. Оценить адекватность и практическую пригодность пост­роенной линейной регрессионной модели, указав:

а) доверительные интервалы коэффициентов а0, a1  ;

б) степень тесноты связи признаков Х и Y;

в) погрешность регрессионной модели.

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

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

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

в) остаточных величин ε i.

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

2. Расположение рабочего файла с результативными таблицами и      графиками

Таблица 2.1

Номер варианта

Исходные данные

24

Номер предприятия

Среднегодовая стоимость основных производственных фондов, млн.руб.

Выпуск продукции, млн. руб.

5

1160,00

910,00

23

1251,00

1209,00

27

1355,00

1040,00

1

1394,00

1339,00

8

1446,00

1430,00

32

1472,00

1508,00

22

1576,00

1287,00

19

1615,00

1235,00

2

1641,00

1469,00

3

1693,00

1638,00

13

1706,00

1742,00

26

1745,00

1599,00

9

1771,00

1677,00

4

1784,00

1820,00

28

1823,00

1625,00

17

1836,00

1664,00

6

1875,00

1560,00

14

1875,00

1898,00

25

1875,00

1690,00

7

1927,00

2106,00

30

2005,00

1690,00

18

2031,00

1976,00

10

2044,00

2093,00

20

2057,00

1690,00

24

2096,00

1937,00

29

2109,00

1781,00

15

2148,00

2301,00

11

2239,00

2210,00

21

2291,00

2275,00

16

2460,00

2470,00

Таблица 2.2

Зависимость выпуска продукции от среднегодовой стоимости основных фондов

Номер группы

Группы предприятий по стоимости основных фондов

Число предприятий в группе

Выпуск продукции

Всего

В среднем на одно предприятие

1

1160-1420

4

4498,00

1124,500

2

1420-1680

5

6929,00

1385,800

3

1680-1940

11

19019,00

1729,000

4

1940-2200

7

13468,00

1924,000

5

2200-2460

3

6955,00

2318,333

Итого

 

30

50869,00

8481,633

Таблица 2.3

Показатели внутригрупповой вариации

Номер группы

Группы предприятий по стоимости основных фондов

Число предприятий в группе

Внутригрупповая дисперсия Y

1

1160-1420

4

26575,250

2

1420-1680

5

11262,160

3

1680-1940

11

22922,545

4

1940-2200

7

43360,571

5

2200-2460

3

12205,556

Итого

 

30

116326,082

Таблица 2.4

Показатели дисперсии и эмпирического корреляционного отношения

Общая дисперсия

Средняя из внутригрупповых дисперсия

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

Эмпирическое корреляционное отношение

136007,632

25163,349

110844,283

0,903

Таблица 2.5

Линейный коэффициент корреляции признаков

 

Столбец 1

Столбец 2

Столбец 1

1

Столбец 2

0,91318826

1

ВЫВОД ИТОГОВ

                 Таблица 2.6

Регрессионная статистика

Множественный R

0,91318826

R-квадрат

0,833912798

Нормированный R-квадрат

0,827981112

Стандартная ошибка

155,5719645

Наблюдения

30

Дисперсионный анализ

        Таблица 2.7

 

df

SS

MS

F

Регрессия

1

3402555,155

3402555,155

140,5861384

Остаток

28

677673,8119

24202,63614

Итого

29

4080228,967

 

 

        Таблица 2.8

 

Коэффициенты

Стандартная ошибка

t-статистика

P-Значение

Y-пересечение

-276,0995442

168,7023428

-1,636607647

0,112904938

Переменная X 1

1,089355181

0,09187519

11,85690257

1,97601E-12

ВЫВОД ОСТАТКА

                 Таблица 2.9

Наблюдение

Предсказанное Y

Остатки

1

987,5524657

-77,55246572

2

1086,683787

122,3162128

3

1199,976726

-159,976726

4

1242,461578

96,53842194

5

1299,108047

130,8919525

6

1327,431282

180,5687178

7

1440,724221

-153,724221

8

1483,209073

-248,209073

9

1511,532308

-42,53230775

10

1568,178777

69,82122284

11

1582,340395

159,6596055

12

1624,825247

-25,82524657

13

1653,148481

23,85151872

14

1667,310099

152,6899014

15

1709,794951

-84,79495069

16

1723,956568

-59,95656804

17

1766,44142

-206,4414201

18

1766,44142

131,5585799

19

1766,44142

-76,4414201

20

1823,08789

282,9121105

21

1908,057594

-218,0575936

22

1936,380828

39,61917168

23

1950,542446

142,4575543

24

1964,704063

-274,704063

25

2007,188915

-70,18891509

26

2021,350532

-240,3505324

27

2063,835384

237,1646155

28

2162,966706

47,03329404

29

2219,613175

55,38682463

30

2403,714201

66,28579905

                                                                                         Рис. 1

3.Выводы по результатам выполнения лабораторной работы.

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

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

 По точечному графику связи признаков - диаграмме рассеяния, полученной в Лабораторной работы №1 после удаления аномальных значений, можно судить о тесной связи между признаками, т.к. точки на графике близко расположены и группируются вокруг определенной линии – линии регрессии (рис. 3). Этот точечный график связи признаков и табл.2.1, представляющая два параллельных ряда значений признаков X и Y с ранжированными значениями Xj  показывают, что с увеличением значений факторного признака X увеличиваются значения результативного признака Y. Это позволяет сделать вывод, что имеет место статистическая связь.

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

Задача 2. Установление наличия корреляционной связи между признаками X и Y методом аналитической группировки.

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

  Вывод:

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

Задача 3.Произведем оценку тесноты связи признаков X и Y:

а) на основе эмпирического корреляционного отношения.

Расчет  - эмпирического корреляционного отношения, для анализа тесноты связи между факторным X и результативным Y признаками производят по формуле:

                                            

где  и - соответственно межгрупповая и общая дисперсии результативного признака.

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

Вывод:

Значение коэффициента = 0,903. Это в соответствии с оценочной шкалой   Чэддока   говорит о весьма высокой степени связи   изучаемых признаков    (0,9≤= 0,903≤0,99).

б) на основе линейного коэффициента корреляции признаков:

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

Вывод:

Значение    коэффициента    корреляции    r = 0,913. Это в соответствии с оценочной шкалой   Чэддока   говорит о весьма высокой степени связи   изучаемых признаков    (0,9≤ r = 0,913≤0,99).

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

Если | |≤0,1, то зависимость признака Y от фактора X можно считать прямолинейной.

Вывод:

При  = 0,903, r = 0,913,   | |≤ |0,815 – 0,834| = 0,019 , следовательно,  зависимость признака Y от фактора X можно считать прямолинейной.

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

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

Инструмент Регрессия производит расчет параметров а0 и a1 уравнения однофакторной   линейной   регрессии    = а0+а1х,   а   также   вычисление   показателей  для   проверки  адекватности  построенного  уравнения  фактическим данным.

В результате работы инструмента Регрессия надстройки Пакет анализа были получены четыре результативные таблицы 2.6 - 2.9.

Вывод:

Рассчитанные в табл.2.8 (ячейки В91 и В92) коэффициенты а0 и a1 позволяют построить однофакторную линейную регрессионную модель связи изучаемых признаков в виде уравнения   = -276,0995 + 1,0894х

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

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

а) Укажем доверительные интервалы коэффициентов уравнения регрессии а0, a1  :

Доверительные интервалы коэффициентов уравнения регрессии а0, a1  при уровнях надежности Р=0,95 и Р=0,683, рассчитанные при помощи  инструмента Регрессия надстройки Пакет анализа (см. табл. 2.8), приведены в следующей таблице:

                                                                                                                    Таблица 2.10

Коэффициенты

Границы доверительных интервалов, млн. руб.

с надежностью Р=0,95

с надежностью Р=0,683

нижняя

верхняя

нижняя

верхняя

а0

-621,671

69,472

-447,981

-104,218

ai

0,901

1,278

0,996

1,183

Вывод: Из таблицы видно, что увеличение уровня надежности ведет к расширению доверительных интервалов коэффициентов уравнения.

б) Оценка степени тесноты связи признаков X и Y осуществляется на основе следующих показателей:

R2  - индекс детерминации (), показывающий какая  часть общей вариации расчетных (теоретических) значений признака Y объясняется вариацией фактора X;

R - индекс корреляции (), оценивающий степень тесноты связи между факторными значениями хi и расчетными результативными значениями;

r – линейный коэффициент корреляции, используемый для измерения тесноты связи признаков в регрессионной модели в случае линейной функции связи f(x).

В результате работы инструмента Регрессия надстройки Пакет анализа рассчитаны  эти показатели (см. табл. 2.6): R2 = 0,834; R = 0,913; r = 0,913.

Т.к. эти значения удовлетворяют неравенству R2 > 0,5 и отсюда R > 0,7 (или | r | > 0,7), это означает высокую степень тесноты связи признаков в уравнении регрессии. При этом более 50% вариации расчет­ных значений признака Y объясняется влиянием фактора X, что позволяет считать применение синтезированного урав­нения регрессии правомерным.

в)  Оценку  погрешности регрессионной модели можно произвести по величине средней квадратической ошибки  построенного уравнения регрессии.

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

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

Вывод:

Погрешность   линейной   регрессионной    модели    составляет    9,17 % (155,572/1695,633*100). Это значение не превышает  12%-15%, что подтверждает адекватность модели.

Практическая пригодность построенной линейной регрессионной модели оценивается по величине показателей r, R2 или  R.

Показатель R близок к единице (R = 0,913), это означает, что связь между признака­ми достаточно хорошо описывается избранным уравнением корреляционной зависимости  = а0+а1х.

Показатель |r| так же близок к единице (r = 0,913), это свидетельствует о хорошей аппрокси­мации фактических данных полученной линейной функци­ей связи  = а0+а1х.

Пригодность построенной регрессионной модели для прак­тического использования можно оценить и по величине индекса детерминации R2:

Показатель R2 = 0,834 > 0,5 , и отсюда R > 0,7 (или | r | > 0,7), это означает высокую степень тесноты связи признаков в уравнении регрессии. При этом более 50% вариации расчет­ных значений признака Y объясняется влиянием фактора X, что позволяет считать применение синтезированного урав­нения регрессии правомерным.

Все эти показатели свидетельствуют о пригодности построенной линейной регрессионной модели для практического использования.

 

Задача 6.

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

В     случае    линейного    уравнения    регрессии     = а0+а1х  величина коэффициента регрессии a1 показывает, на сколько в среднем (в абсолютном выражении) изменяется значения результативного признака Y при изменении фактора X на единицу его измерения. Знак при a1 показывает направление этого изменения.

Вывод:

Коэффициент  регрессии  a1 = 1,089  показывает, что при увеличении стоимости основных фондов на 1 млн. руб., выпуск продукции увеличится в среднем на 1,089  млн. руб.

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

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

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

Вывод:                                               

Коэффициента эластичности  КЭ =  1,162 (1,089 * 1810/1695,633), показывает, что при увеличении среднегодовой стоимости основных фондов на 1% выпуск продукции возрастет в среднем на 1,16%.

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

Каждый их остатков ε i = характеризует отклонение i-тых наблюдений от значений , рассчитанных по регрессионной модели, которые следует ожидать в среднем.

Значения остатков ε i  представлены в таблице 2.9.

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

Вывод:

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

Задача 7.

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

Уравнения регрессии и их графики построены для 4-х видов нелинейной зависимости между признаками и представлены на рис. 2.

                                                                                               Рис. 2

Уравнения регрессии и соответствующие им индексы детерминации R2 приведены в следующей таблице:

                                       Регрессионные модели связи       Таблица 2.11

Вид уравнения

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

Индекс детерминации R2

Полином 2-го порядка

 = 0,0001x2 + 0,6718x + 85,905

     R2 = 0,8353

Полином 3-го порядка

 = 5E-07x3 - 0,0024 x2 + 5,0218x - 2400,4

R2 = 0,8381

Степенное

 = 0,251x1,1746

R2 = 0,8371

Экспоненциальное

 = 487,96e0,0007x

R2 = 0,8272

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

Вывод:

Максимальное значение индекса детерминации R2 = 0,8381, следовательно наиболее адекватное нелинейное уравнение регрессии полином 3-его порядка ( = 5E-07x3 - 0,0024 x2 + 5,0218x - 2400,4).

Это уравнение и его график приведены на рис.3

                                                                                               Рис. 3