Статистико-экономический анализ себестоимости зерна в Воронежской области
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%.