Статистико-экономический анализ себестоимости зерна в Воронежской области

курсовая работа

4.2 Построение экономико-математической модели себестоимости 1 ц зерна

Для проведения корреляционно-регрессионного анализа используются исходные данные, которые представим в таблице 4.1.

Таблица 4.1 - Исходные данные для корреляционно-регрессионного анализа

Номер районов

Себестоимость 1 ц, руб.

Расчетные показатели

Уровень интенсификации, руб.

Урожайность зерновых культур, ц/га

Трудоёмкость 1 ц зерна, чел/час

Фондообеспеченность района, тыс. руб.

Уровень специализации, %

Уд. вес затрат на зерно в общих затратах, %

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

Уровень концентрации, га

Трудообеспеченность, чел

1

427,74

12960

30,3

0,52

2568

28,5

98,0

1618

617235

3,7

2

512,41

13949

27,2

0,50

2850

49,5

98,4

1570

1093508

2,0

3

500,30

7050

14,1

0,65

809

59,1

99,2

1315

446454

1,3

4

381,36

5703

15,0

0,58

1244

63,7

99,9

454

255989

0,8

5

576,61

11292

19,6

0,17

2264

43,8

98,7

928

540996

1,9

6

504,53

8028

15,9

0,49

1614

38,4

97,3

1221

315284

2,3

7

576,23

14671

25,5

0,68

3196

19,4

100,0

2979

239126

2,5

8

443,42

8534

19,2

0,23

1232

51,0

97,8

735

297133

1,5

9

468,00

8119

17,3

0,16

1487

37,6

100,0

1041

585770

1,3

10

549,01

9481

17,3

0,39

1089

31,7

99,2

1319

538285

2,2

11

341,91

8689

25,4

0,39

1283

36,7

99,0

870

159752

2,6

12

445,80

8943

20,1

0,31

690

56,0

98,5

1804

395365

1,5

13

357,20

9102

25,5

0,32

3667

82,8

99,6

1041

568577

2,2

14

697,99

22224

31,8

0,72

6532

8,7

99,3

2196

987250

4,3

15

548,67

10951

20,0

0,51

3041

17,5

99,1

2027

127878

2,9

16

589,46

14183

24,1

0,14

3837

27,7

99,8

2191

432412

3,4

17

434,90

7820

18,0

0,26

859

132,9

100,0

1446

710647

0,7

18

808,61

14710

18,2

0,43

1726

40,2

99,7

1404

236302

2,2

19

496,74

11118

22,4

0,26

820

36,7

99,9

2082

202983

1,5

20

617,24

12762

20,7

0,33

1458

44,7

99,2

2149

864863

2,9

21

571,17

17105

29,9

0,30

1485

79,7

99,6

2289

589764

1,3

22

497,88

6396

12,8

0,59

758

54,8

99,9

761

317210

1,1

23

578,56

7795

13,5

0,64

1558

329,6

97,9

229

298762

1,1

24

566,50

11323

20,0

0,19

1050

122,1

100,0

1969

568165

1,1

25

691,80

12592

18,2

0,30

1123

33,9

99,5

1620

333530

1,7

26

455,75

10045

22,0

0,24

1044

49,5

99,2

1981

282866

0,8

27

511,38

9871

19,3

0,62

1288

164,2

99,1

1585

711850

2,5

28

624,25

12527

20,1

0,52

1180

31,3

99,8

2022

463708

1,3

29

474,31

9449

19,9

0,51

1726

40,1

99,2

1285

504238

2,2

30

482,67

9054

18,8

0,21

2323

13,8

99,4

408

374120

2,2

31

368,94

10352

28,1

0,28

2413

38,6

98,8

1758

411774

1,7

32

402,21

9711

24,1

0,20

1485

68,0

98,5

1050

866037

1,5

Проведение корреляционно-регрессионного анализа осуществляется в MS Excel c помощью пакета анализа «Регрессия»

Пакет анализа представляет собой надстройку Microsoft Excel, т. е. программу, которая становится доступной при установке Microsoft Office или Excel. Однако чтобы использовать надстройку в Excel, необходимо сначала загрузить ее.

Для MS Excel 2003

1. Откройте вкладку Сервис и выберите пункт Надстройки

2. В окне Доступные надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

3. После загрузки пакета анализа в вкладке Сервис будет доступен пункт Анализ данных

Для MS Excel 2010

1. Откройте вкладку Файл и выберите пункт Параметры.

2. Выберите команду Надстройки, а затем в поле Управление выберите пункт Надстройки Excel. Нажмите кнопку Перейти.

3. В окне Доступные надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

4. После загрузки пакета анализа в группе Анализ на вкладке Данные становится доступной команда Анализ данных.

Последовательность проведения анализа в MS Excel

1. Откройте вкладку Сервис и выберите пункт Анализ данных

2. В открывшемся окне выберите пункт Регрессия

3. Нажмите ОК

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

5. В качестве входного интервала Х выделите массив Х1-Х9 с расчетными показателями в таблице 4.1

6. В качестве выходного интервала выберите любую ячейку

7. Поставьте галочку напротив пункта Остатки

8. Нажмите ОК

В поле листа MS Excel появится вывод итогов:

Таблица 4.2

ВЫВОД ИТОГОВ

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

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

0,983128026

R-квадрат

0,966540715

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

0,952852825

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

22,69501039

Наблюдения

32

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

df

SS

MS

F

Значимость F

Регрессия

9

327330,8556

36370,09507

70,61283765

3,80523E-14

Остаток

22

11331,39693

515,0634966

Итого

31

338662,2525

Таблица 4.3

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

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

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

P-Значение

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

684,7558319

677,0177713

1,011429627

0,322806247

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

0,045379274

0,002388056

19,00259801

3,87055E-15

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

-21,83763448

1,390090703

-15,7095033

1,93139E-13

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

-34,06803965

26,80163897

-1,271117774

0,216962828

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

-0,013039113

0,006280617

-2,076087974

0,049776292

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

0,111832769

0,083305223

1,342446069

0,193140905

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

-1,771447328

6,759813627

-0,262055646

0,795716612

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

-0,004606764

0,010281799

-0,44805042

0,658498055

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

-3,13362E-05

1,9566E-05

-1,60155877

0,123515831

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

14,48798772

8,419427029

1,720780722

0,099326622

Таблица 4.4

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

Наблюдение

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

Остатки

1

416,1746851

11,56677489

2

487,7887886

24,6162414

3

493,9289711

6,371168875

4

412,9077414

-31,55151136

5

570,2969489

6,309901071

6

513,6356922

-9,109612226

7

570,4428166

5,784783363

8

468,8963907

-25,47880066

9

472,4444768

-4,446796845

10

546,5717605

2,440379458

11

351,8195151

-9,908305099

12

465,1359974

-19,33274744

13

325,0417708

32,15522917

14

733,9213917

-35,92696175

15

543,6908755

4,980514469

16

599,997214

-10,53392402

17

445,2337368

-10,33004682

18

763,3145081

45,29904193

19

514,239298

-17,50311801

20

616,3296156

0,90687437

21

599,6303535

-28,46460347

22

496,6287859

1,249594061

23

571,4706942

7,0901658

24

567,7290551

-1,22988508

25

668,7433364

23,05350362

26

461,6396629

-5,886762853

27

522,2477906

-10,86337056

28

604,0799025

20,16638749

29

477,6991505

-3,391380471

30

492,7683515

-10,0943415

31

334,2373823

34,7004377

32

394,8445095

7,361170496

Коэффициент корреляции равен 0,98, значит связь факторов прямая и тесная. Коэффициент детерминации показывает, какая доля вариации результативного признака обусловлена изменением факторного признака. В нашем примере он равен 0,96. Коэффициент свидетельствует, что уровень себестоимости зерна на 96% зависит от всех вышеприведенных факторов.

Результаты корреляционно-регрессионного анализа представим в виде таблицы 4.5 где Р-значение соответствует уровню значимости:

Таблица 4.5- Корреляционно-регрессионная модель (исходная)

Условные обозн.

Название показателя (фактора)

Коэффициент регрессии

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

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

Уровень значимости

Constant

Себестоимость 1 ц, руб.

684,75583

677,01777

1,01

0,32

x1

Уровень интенсификации, руб.

0,04538

0,00239

19,00

0,00

x2

Урожайность зерновых культур, ц/га

-21,83763

1,39009

-15,71

0,00

x3

Трудоёмкость 1 ц зерна, чел/час

-34,06804

26,80164

-1,27

0,22

x4

Фондообеспеченность района, тыс. руб.

-0,01304

0,00628

-2,08

0,05

x5

Уровень специализации, %

0,11183

0,08331

1,34

0,19

x6

Уд. Вес затрат на зерно в общих затратах на растениеводство, %

-1,77145

6,75981

-0,26

0,80

x7

Стоимость внесенных удобрений на 1 га зерновых, руб.

-0,00461

0,01028

-0,45

0,66

x8

Уровень концентрации, га

-0,00003

0,00002

-1,60

0,12

x9

Трудообеспеченность, чел

14,48799

8,41943

1,72

0,10

Исходя из таблицы 4.5 построим уравнение регрессии:

y=684,75+0,04x1+(-21,83)x2+(-34,07)x3+(-0,01)x4+

+0,11x5+(-1,77)x6+(-0,01)x7+(-3,13362E-05)x8+14,48x9

Коэффициент регрессии показывает, насколько изменяется результативный признак при изменении факторного признака на 1 единицу. То есть при увеличении уровня интенсификации на 1 руб., себестоимость 1 ц зерна повысится на 0,04 руб. при увеличении специализации на 1% себестоимость повысится на 0,11 руб., а при увеличении трудообеспеченности, себестоимость повысится на 14,48 руб. Имеет место прямая связь. При повышении урожайности зерновых культур на 1 ц/га, себестоимость снижается на 21,83 руб., при повышении трудоемкости 1 ц зерна на 1 чел/час, себестоимость снижается на 34,06 руб., при повышении фондообеспеченности района на 1 тыс. руб., себестоимость снижается на 0,01 руб., при повышении удельного веса затрат на зерно в общих затратах на растениеводство на 1%, себестоимость снижается на 1,77 руб., при повышении стоимости внесенных удобрений на 1 га зерновых на 1руб, себестоимость снижается на 0,01 руб., при повышении уровня концентрации на 1 га, себестоимость снижается на 0,00003 руб. Между этими показателями обратная связь.

Для более рационального анализа исключим из модели незначимые факторы:

Для построения улучшенной корреляционно-регрессионной модели необходимо выбрать в графе вывода итогов Р-Значение наибольшее число. Затем следует удалим из исходной таблицы 4.1 столбец с соответствующим наибольшему показателю Р-Значение фактором и заново произведем регрессионный анализ. Далее заново в выводе итогов просмотрим колонку Р-Значение и снова удалим из таблицы 4.1 столбец с фактором, соответствующим наибольшему значению из этой колонки. Будем повторять, пока не останется 2-4 фактора.

Таблица 4.6

ВЫВОД ИТОГОВ

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

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

0,346502

R-квадрат

0,120064

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

0,059379

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

101,3701

Наблюдения

32

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

df

SS

MS

F

Значимость F

Регрессия

2

40661,08

20330,54

1,978468

0,15651

Остаток

29

298001,2

10275,9

Итого

31

338662,3

Таблица 4.7

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

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

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

P-Значение

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

596,3994

83,30557

7,159178

7,03E-08

Переменная X1

-7,10625

4,655697

-1,52635

0,137755

Переменная X2

0,037013

0,019051

1,942847

0,0618

Таблица 4.8

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

Наблюдение

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

Остатки

1

476,1238

-48,3823

2

508,437

3,96804

3

526,2011

-25,901

4

536,1646

-154,808

5

541,0528

35,55402

6

543,0656

-38,5395

7

533,7731

42,45451

8

505,2369

-61,8193

9

528,1505

-60,1528

10

513,982

35,03018

11

463,2985

-121,387

12

479,373

-33,5697

13

551,0549

-193,858

14

611,9157

86,07876

15

567,1405

-18,4691

16

567,4492

22,01413

17

500,4302

-65,5265

18

531,0032

277,6103

19

467,6979

29,03829

20

503,4259

113,8106

21

438,5492

132,6166

22

533,1509

-35,2725

23

558,3372

20,22362

24

493,2285

73,27068

25

508,6199

183,1769

26

478,4066

-22,6537

27

506,8844

4,499981

28

497,4509

126,7954

29

518,7327

-44,4249

30

549,0873

-66,4133

31

486,3072

-117,369

32

479,8002

-77,5945

Коэффициент корреляции равен 0,34, что говорит о том, что

Скопированные результаты улучшенного решения оформим в виде таблицы 4.3.

Таблица 4.3 - Корреляционно-регрессионная модель (улучшенная)

Условные обозн.

Название показателя (фактора)

Коэффициент регрессии

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

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

Уровень значимости

Constant

Себестоимость 1 ц, руб.

596,40

83,31

7,16

0,0000001

x1

Урожайность зерновых культур, ц/га

-7,11

4,66

-1,53

0,13776

x2

Фондообеспеченность хозяйства, тыс. руб.

0,04

0,02

1,94

0,06180

По улучшенной модели также составим уравнение регрессии:

y=684,76-21,84x1-0,01x2

По этой модели можно сделать вывод, что при повышении урожайности зерновых культур на 1 ц/га себестоимость снижается на 21,84 руб., а при повышении фондообеспеченности района на 1 тыс. руб., себестоимость снижается на 0,01 руб.

С целью оценки существенности влияния изучаемых факторов на результат построим отобразим данные из раздела «Дисперсионный анализ» вывода итогов, где Df - Число степеней свободы; SS - Величина вариации; MS - Дисперсия на одну степень свободы; F - Критерий Фишера; Значимость F - Уровень значимости. Результаты дисперсионного анализа представим в таблице 4.4:

Таблица 4.4 - Дисперсионный анализ вариации для всей модели

Источник вариации

Величина вариации

Число степеней свободы

Дисперсия на одну степень свободы

Критерий Фишера

Уровень значимости

Факторная

327330,856

9

36370,09507

70,61

3,8052E-14

Остаточная

11331,3969

22

515,0634966

Общая

338662,253

31

Полученное значение критерия Фишера сравним с теоретическим

Fфакт=70,61

Fтеор=2,35

, значит влияние фактора на результат существенно.

Расчет резервов снижения себестоимости 1 ц зерна определяется в таблице 4.5 на основе данных таблицы 4.3 и расчет средних показателей.

Пронумеруем столбцы этой таблицы.

1)В первый столбец таблицы выписываем факторы из улучшенной корреляционно-регрессионной модели (Таблица 4.3)

2)Во второй условное обозначение факторов (Х1, Х2 и т.д.)

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

4)Средний уровень факторов по передовым районам рассчитываем путем средней арифметической взвешенной по 10 районам, где показатель этого фактора наибольший,

5)По отстающим - по оставшимся районам по тому же принципу.

6)Отклонение среднего уровня факторов от средней по совокупности рассчитывается путем вычитания данных столбца 5 из данных 3 столбца (3-5).

7)Отклонение среднего уровня факторов от передовых районов - 4-5.

8)Коэффициент регрессии переносим из таблицы 4.3.

9) Перемножить данные столбцов 8 и 6 (8*6). По этому столбцу посчитать сумму итого. Сумма должна быть отрицательной. Если сумма не отрицательна, то исключить фактор с наибольшим коэффициентом регрессии. Также исключить его из таблицы 4.3 и пересчитать вывод итогов.

10) Чтобы найти резервы изменения фактора до среднего уровня в процентном соотношении, разделим значение из столбца 9 на среднее значение фактора по совокупности всех районов, которое мы рассчитывали в 3й главе курсовой работы и умножим на 100%.

11) 8*7. По этому столбцу посчитать сумму итого. Сумма должна быть отрицательной. Если сумма не отрицательна, то исключить фактор с наибольшим коэффициентом регрессии. Также исключить его из таблицы 4.3 и пересчитать вывод итогов.

12) По аналогии со столбцом 10: 11/среднее значение фактора по совокупности *100%.

Делись добром ;)