VII Международная студенческая научная конференция Студенческий научный форум - 2015

Корреляционно-регрессионный анализ расходов на содержание и строительство дорог на основании смет по заключенным контрактам.

(На примере организации ООО" Московская строительная компания" )

В проведенной работе преследовалась цель: провести корреляционный-регрессионный анализ расходов на выполнение работ по обеспечению безопасного дорожного движения на автомобильных дорогах регионального или межмуниципального значения Московской области на основании данных по сметам бюджетополучателя и заказчика работ ГБУ МО «Мосавтодор».

Для проведения данного анализа были взята информация у организации ООО «Московская строительная компания».

ООО «МСК» предоставила сметы за период 2012-2014 года по различным объектам Московской области :

Ярославское шоссе ст. Мамонтовская

Пушкино – Красноармейск

Правдинский – Тишково – Ельдигоно

Братовщиа – Ельдигино – ММК – Герасимиха – Рахманово

ММК – Центральная усадьба АО «Майское»

Красноармейск – Барково – Михайловское

Ярославское шоссе «заветы Ильича»

Софрино – Ашукино

Полные данные представлены в таблице 1.

Наименование объекта	№ п.п.	Стоимость строительства и содержания дорог по контракту	Устройство корыта(выемки)в земляном полотне экскаватором вместимостью ковша 0.25куб.м.с погрузкой в автосамосвалы (без планировки) (м3)	Устройство песчаного подстилающего слоя H=20 см (м3)	Устройство покрытия на посадочных площадках и тротуарах из песчаного асфальтобетона толщиной 5 см.(на основании из щебня) (м2)	Устройство основания пешеходных дорожек из известнякового щебня М-600 толщ.12 см. с укладкой и уплотнением вручную (м2)
код			20402	30801	60204	60302
переменные		Y	Х1	Х2	Х3	Х4
Пушкинское отделение "Ярославское шоссе''-ст.Мамонтовская км 1,120-1,160(право),км1,110-1,180(лево) Устройство тротуара,прот.110 м.шир.1,2м площ.132 м2	1	299889,00	5407,08	15853,60	54071,16	36260,40
"Ярославское шоссе''-ст.Мамонтовская км 0+20-км 0 +420 (лево) Устройство пешеходной дорожки отдельными участками,прот.183 м.шир.1,2м площ.220 м2	2	244359,96	9011,79	26422,66	90118,60	60434,00
Пушкино -Красноармейск,км5+070-км 5+220 лево(поворот на Левакино ) Устройство тротуара,прот.150 м.шир.1,5м площ.225 м2	3	463446,38	9216,61	27023,18	92166,75	60807,50
Правдинский -Тишково-7+120-км 7+605(лево) Устройство тротуара,прот.485 м.шир.1,2м площ.582 м2	4	710322,10	23840,29	69899,95	238404,66	159875,40
Братовщина-Ельдигино-ММК-Герасимиха-Рахманово,км 2+320-км 2+625 лево Устройство тротуара,прот.325 м.шир.1,5м площ.487,5 м2	5	960613,46	19969,32	58550,21	199694,63	133916,25
ММК-Усадьба АО ''Майское'',км 0+010-км 0+760 ,право,лево от улицы Мичурина до ММК .Устройство тротуара,прот.750 м.шир.1,2м площ.900 м2	6	2018159,90	36866,43	108092,70	368667,00	247230,00
Ярославское шоссе,км 53+770(лево) Устройство заездного кармана,площ.87,5 м2 посадочной площадки ,площ.50 м2 с автопавильоном	7	171191,79	5134,18	26272,53	34312,25	28105,88
а/д''Пушкино-Красноармейск Устройство тротуара S=1310 м2,км 8 (Жуковка-Зверосовхоз)	8	1154903,78	8770,78	159823,93	402340,30	316076,80
а/д''Пушкино-Красноармейск Устройство автобусной остановки,км 8 (Жуковка) Устройство заездных карманов 2 шт	9	231248,22	8869,55	43433,07	43200,30	50796,98
а/д''Пушкино-Красноармейск Устройство посадочной площадки S =65 м2 с установкой автопавильона-2шт	10	517054,30	48983,18	30988,76	39926,90	49210,20
а/д" Красноармейск-Барково-Михайловское"Устройство автобусной остановки,км 0+800 Устройство заездного кармана,площ.62,5 м2	11	100027,64	3269,29	19062,97	18947,50	22279,38
Устройство посадочной площадки S =30 м2	12	84091,40	15470,71	7320,18	9213,90	11356,20
а/д"Ярославское шоссе -Заветы Ильича'' Устройство заездных карманов на автобусных остановках,км 0+480 слева ;км 0+520 справа;км	13	772894,81	20544,16	119562,94	99540,56	139736,24
а/д"Ярославское шоссе -Заветы Ильича'' Устройство посадочной площадки(13м* 5м)-2 шт	14	451968,55	8780,65	17568,43	39926,90	49702,30
а/д"Софрино-Ашукино''-Ашукино Устройство тротуара S=487,5 м2,км 0+520-км 0+845	15	447486,48	12998,13	70626,08	149725,88	117624,00
а/д"Софрино-Ашукино'' Устройство тротуара,км 4,S=193,5 м2	16	420428,81	1017,33	28105,56	59429,66	46687,68
а/д"Софрино-Ашукино'' Устройство переходно-скоростной полосы ,км 0,040 - км 0,190	17	1241357,62	27201,26	164704,05	454740,00	193244,40
а/д Правдинский-Тишково Ельдигино Устройство тротуара (450пм),км7,550-км 8.800	18	832254,49	1027,21	97789,95	207312,75	162864,00
а/д"Братовщина-Ельдигино-ММК-Герасимиха-Рахманово' 'Устройство тротуара,3км(сан.Правда) 85 *1,5м	19	276406,53	671,64	18471,44	39159,08	30763,20

Табл.1

Осуществим Выбор факторных признаков для построения регрессионной модели с помощью корреляционного анализа данных.

Стоимость строительства и содержание дорог является зависимой переменной Y ( руб.).

В качестве независимых, объясняющих переменных выбраны:

X1 – Устройство корыта (выемки)в земляном полотне экскаватором вместимостью ковша 0.25куб.м.с погрузкой в автосамосвалы (без планировки);

X2 – Устройство песчаного подстилающего слоя H=20 см.;

X3 – Устройство покрытия на посадочных площадках и тратуарах из песчаного асфальтобетона толщиной 5 см.(на основании из щебня) .;

X4 – Устройство основания пешеходных дорожек из известнякового щебня М-600 толщ.12 см. с укладкой и уплотнением вручную;

Количество наблюдений n=19, количество объясняющих переменных m=4.

Для проведения корреляционного анализа используем инструмент Корреляция (надстройка Анализ данных Excel).

В результате получаем матрицу коэффициентов парной корреляции (табл. 2).

	Y	Х1	Х2	Х3	Х4
Y	1
Х1	0,526567	1
Х2	0,7785	0,310405	1
Х3	0,867996	0,354602	0,893864	1
Х4	0,870233	0,320289	0,912954	0,927595	1

Табл. 2

Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная имеет наибольшую связь с тремя факторами: Х2 «Устройство песчаного подстилающего слоя H=20 см» (ryx1=0,7785) , Х3 «Устройство покрытия на посадочных площадках и тротуарах из песчаного асфальтобетона толщиной 5 см.(на основании из щебня)» (ryx2=0,867996) и Х4 «Устройство основания пешеходных дорожек из известнякового щебня М-600 толщ.12 см. с укладкой и уплотнением вручную» (ryx3=0,870233). Заметим, что факторы Х2 и Х3 тесно связаны с фактором Х4 (rx2x4=0,912954 и rx2x4=0,927595), т.е. установлено явление мультиколлинеарности.

Для выявления мультиколлинеарности факторов дополнительно выполним тест Фаррара-Глоубера по факторам Х2, Х3 и Х4. Результаты представлены в таблице 3.

	Х2	Х3	Х4
Х2	1	0,893864	0,912954
Х3	0,893864	1	0,927595
Х4	0,912954	0,927595	1

Табл.3

Найдем определитель матрицы с помощью функции МОПРЕД. Он равен 0,02102976. Мы видим, что определитель матрицы R стремится к нулю, что позволяет сделать предположение об общей мультиколлинеарности факторов.

Из этих трех факторов оставим Х4, так как у него самая тесная связь с У - ryx3=0,870233.

Построим модель зависимости расходов на строительство и содержание дорог Y от фактора Х4 «Устройство основания пешеходных дорожек из известнякового щебня М-600 толщ.12 см. с укладкой и уплотнением вручную». Используем инструмент «Регрессия» надстройки «Анализ данных». В поле Входной интервал Y введем адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал Х введем адрес одного из диапазонов, которые содержат значения независимых переменных (Х4). Представим итоги в таблице 4.

Вывод итогов:

Регрессионная статистика
Множественный R	0,870233203
R-квадрат	0,757305827
Нормированный R-квадрат	0,743029699
Стандартная ошибка	245352,4365
Наблюдения	19

Дисперсионный анализ
	df		MS	F	Значимость F
Регрессия	1	3,19331E+12	3,19331E+12	53,04700514	1,2783E-06
Остаток	17	1,02336E+12	60197818087
Итого	18	4,21668E+12
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	97956,79091	88982,10961	1,10085939	0,28630174	-89779,0501	285692,632	-89779,05009	285692,6
Х4	4,974998129	0,683065714	7,2833375	1,2783E-06	3,53385545	6,41614081	3,533855445	6,416141

Табл. 4

На основании полученных данных построим линейную однофакторную модель, ее уравнение будет иметь вид: y=97956,79+4,975Х4.

Проведем анализ модели.

Коэффициент регрессии β = 4,975, следовательно, при увеличении расходов на устройство основания пешеходных дорожек из известнякового щебня М-600 на 1 кв. м в среднем на 4,975 . увеличивается расход на строительство и содержание дорог по контракту.

Оценим качество модели через коэффициент детерминации, среднюю ошибку аппроксимации и F- критерий Фишера.

Значение коэффициента детерминации можно найти в таблице Регрессионная статистика.

R² = 0,757

Коэффициент детерминации принимает значения от 0 до 1. Чем ближе значение коэффициента к 1, тем сильнее зависимость. Для приемлемых моделей предполагается, что коэффициент детерминации должен быть хотя бы не меньше 50 % (в этом случае коэффициент множественной корреляции превышает по модулю 70 %). Модели с коэффициентом детерминации выше 80 % можно признать достаточно хорошими, как в нашем случае.

Коэффициент детерминации (R – квадрат): R2=0,757305827 – следовательно, около 75,7% вариаций зависимых переменных учтено в модели и обусловлено влиянием включенного фактора.

Оценку значимости уравнения регрессии проводим с помощью F-критерия Фишера (F табл из таблицы Дисперсионный анализ).

F табл = 53,047005

Fфакт R² / (1- R² ) *( n-2) = 0,76/0,24 * 17 = 53,83

Так как F таблF факт , то уравнение регрессии значимо.

Рассчитанный индекс корреляции (множественный R) представлен в таблице Регрессионная статистика равен R=0,870233203. Он показывает тесноту связи зависимой переменной с включенным в модель объясняющим факторами. В данном случае еще раз подтверждается, что связь достаточно сильная.

Для сравнительной оценки силы связи факторов с результатом найдем

коэффициенты эластичности, β- и ∆- коэффициенты для каждого фактора.

Построим поле корреляции результативного признака и наиболее тесно связанного с ним фактора. Поле корреляции строим в EXCEL с помощью Мастера диаграмм, тип – точечная.

Результативным признаком является расход на строительство и содержание дорог по контракту (Y), а наиболее тесно связанным с ним фактором является расход на устройство основания пешеходных дорожек из известнякового щебня М-600 (Х4). Поле корреляции представлено на рисунке 1.

Рис. 1

На основании рассчитанных коэффициентов делаем вывод-наиболее сильную связь с результатом имеет фактор Х4. Построенная модель достаточно значима.

В 2015 году ожидается подорожание строй.материалов и строительных работ. К примеру, укладка и уплотнение вручную известнякового щебня подорожает по мнению экспертов на 20 % по сравнению с прошлым периодом. По построенной модели осуществим прогнозирование среднего значения показателя Y при уровне значимости α=0,1, если прогнозное значение фактора X составит 120% от его среднего значения.

X_сред = 100893,2

X_прогн = 100893,2 * 1,2 = 121071,84

Используя уравнение модели: y=97956,79+4,975Х4, получаем

y_прогн = 97956,79 +4,975 * 121071,84 = 700289,194 руб.

Точечный прогноз расходов на строительство: (121071,84; 700289,194).

Найдем интервальный прогноз расходов на строительство и содержание дорог:

Нижняя граница прогноза: y_прогн - U

Верхняя граница прогноза: y_прогн + U

Найдем ошибку прогнозирования по формуле:

Табличный критерий Стьюдента:

tтаб(0,1;19-1-1) = 1,74

Стандартная ошибка отклонений для фактора X4 находится в таблице Регрессионная статистика:

Se = 245352,4365

Верхняя граница прогноза: y_прогн - U = 700289,194 – 438659,8484= 261629,3456

Нижняя граница прогноза: y_прогн + U = 700289,194 + 438659,8484= 1138949,042

Интервальный прогноз расходов будет находится в следующих границах: (261629; 1138949)

С помощью построенное модели мы можем предсказать примерную стоимость контрактов в 2015 году.

Представим графически фактические и модельные значения, точки прогноза.

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

Построим показательную модель. Уравнение показательной кривой: ỹ=а*bx . Осуществим логарифмирование обеих частей уравнения: lgy=lg a +x*lgb . Обозначим Y=lgy', В=lgb, A=lga. В итоге – получим линейное уравнение регрессии: Y=A+Вх . Рассчитаем его параметры в экселе. Итоги представим в таблице 5.

у	Y=lg(y)	х	Ух	х2
84091,40	4,924751583	11356,20	55926,46393	128963278,4
100027,64	5,000120022	22279,38	111399,574	496370773,2
171191,79	5,233482933	28105,88	147091,6433	789940490,6
231248,22	5,364078399	50796,98	272478,9831	2580333177
244359,96	5,388030045	60434,00	325620,2078	3652268356
276406,53	5,441548299	30763,20	167399,4386	946374474,2
299889,00	5,476960536	36260,40	198596,7798	1314816608
420428,81	5,623692469	46687,68	262557,1544	2179739464
447486,48	5,650779918	117624,00	664667,3371	13835405376
451968,55	5,655108216	49702,30	281071,8851	2470318625
463446,38	5,665999494	60807,50	344535,2642	3697552056
517054,30	5,713536154	49210,20	281164,2569	2421643784
710322,10	5,851455327	159875,40	935503,761	25560143525
772894,81	5,888120391	139736,24	822783,8041	19526216769
832254,49	5,920256147	162864,00	964196,5971	26524682496
960613,46	5,982548668	133916,25	801160,483	17933562014
1154903,78	6,062545803	316076,80	1916230,077	99904543498
1241357,62	6,093896914	193244,40	1177611,453	37343398131
2018159,90	6,304955573	247230,00	1558774,166	61122672900
599900,2747	5,644308784	100893,2005	594145,7542	16969944516

Табл. 5

В итоге получим следующие значения :

В=	3,63351E-06
А=	5,277712292
	1,000008367
a	189544,9822

Полученные значения подставим в формулу.

В = (594145,75- 100893,2*5,6)/(16969944516-100893,2*100893,2) = 3,63351Е-06

А = 5,6- 3,63351Е-06 *100893,2 = 5,3

Уравнение будет иметь вид: Y=5,3+3,63351Е-06

Перейдем к исходным переменным x и y, выполнив потенциирование данного уравнения:

y=10^5,3+(10^3,63351Е-06)^x

y = 189544,9822+1,000008367^x

Представим график полученной функции:

Рассмотрим значимость нашей модели.

Общая дисперсия результативного признака y равна 1/19 *2,43 = 0,127742316

Остаточная дисперсия равна 1/19 *1023362907470,71 = 53861205656

Если рассчитать показатель тесноты связи (индекс корреляции), то он будет стремиться к 1, что говорит о том, что построенная нами показательная модель достаточно точная, и объясняет зависимость между объясняющим фактором Х4 (Устройство основания пешеходных дорожек из известнякового щебня М-600 толщ.12 см. с укладкой и уплотнением вручную ) и зависимой переменной У (расходы на строительства и содержание и безопасность дорог). Поэтому для прогнозирования значений можно применять обе модели или более простую линейную или показательную модель.

Список используемой литературы :

1. 1. Многомерный статистический анализ в экономических задачах: компьютерное моделирование в SPSS: Учебное пособие / Орлова И.В., Концевая Н.В., Уродовских В.Н., Филонова Е.С., Турундаевский В.Б. - М.: Вузовский учебник, 2009. – 309 .

2. Экономико-математические методы в примерах и задачах: Учеб. пос. / А.Н. Гармаш, И.В. Орлова, Н.В. Концевая и др.; Под ред. А.Н. Гармаша - М.: Вузовский учебник: НИЦ ИНФРА-М, 2014 - 416с.

Код для цитирования: Скопировать