Известны статистические данные по 36 строительным бригадам.
Необходимо выяснить влияние различных факторов на величину накладных расходов в строительстве. Известно, что к накладным расходам относятся административно-хозяйственные, коммунальные расходы, дополнительная заработная плата и другие расходы. На качественном уровне выявлено, что фактический уровень накладных расходов оказался наиболее тесно связан со следующими факторами: объемом выполненных работ, численностью рабочих, занятых на строительно-монтажных работах, фондом заработной платы. Остальные факторы были признаны незначимыми.
На основании имеющихся данных необходимо при помощи использования функций Excel
1. Рассчитать параметры множественной линейной регрессии, проводя процедуру стандартного регрессионного исследования до получения удовлетворительной модели. Провести полный анализ полученного уравнения регрессионной связи.
К числу рассчитываемых и анализируемых параметров относятся:
а) коэффициенты регрессии (и их значимость);
б) коэффициент корреляции (и его значимость);
в) коэффициент детерминации;
г) стандартные ошибки коэффициентов регрессии;
д) доверительные интервалы для коэффициентов регрессии;
е) величины общей, объясненной и остаточной дисперсии.
2. На основании реальных и расчетных значений накладных расходов построить графики и сравнить их.
Статистические данные по исследуемым показателям приведены в таблице:
№ Накладные расходы ( руб. ) Объем работ ( куб. м. ) Численность рабочих (чел.) Фонд заработной платы (руб.)
1 79949 226 5 35115
2 125358 360 11 38580
3 81469 248 7 27953
4 57425 135 4 18917
5 150553 452 10 58030
6 111542 306 8 42057
7 78841 216 6 33895
8 104803 259 7 40892
9 59876 155 6 21407
10 123958 334 9 51248
11 138340 363 10 51106
12 102106 292 9 34325
13 188352 584 14 68394
14 157352 466 11 58393
15 129227 338 12 39612
16 170755 501 13 72423
17 103794 264 9 43263
18 56012 141 4 18040
19 50595 152 5 16622
20 73075 205 5 26751
21 81645 246 6 34503
22 65574 178 5 25864
23 85281 227 8 37026
24 100850 312 7 40336
25 153282 397 11 46487
26 73734 204 6 29242
27 114042 339 8 43027
28 66791 196 5 20646
29 102282 299 9 43076
30 82280 252 7 30188
31 94302 248 7 34911
32 68202 194 6 32206
33 97786 271 6 41208
34 62993 206 6 24624
35 99282 324 8 33518
36 80925 243 7 36666
Решение
Рассчитаем параметры множественной линейной регрессии. Оценивается парная линейная регрессия у на х, задаваемая уравнением
у = а + b1х1+ b2х2+ b3х3.
В результате выполнения регрессионного анализа в пакете Excel получены оценки а и b и их Р-значения:
Коэффициенты P-Значение
а 486,776 0,885
b1 182,310 0,000
b2 3227,064 0,003
b3 0,603 0,007
Без проверки значимости коэффициентов а и b уравнение регрессии было бы записано в следующем виде:
у = 486,776 + 182,310 х1 + 3227,064 х2 - 0,603 х3.
Однако необходимо проверить, все ли из включенных в уравнение параметров действительно оказывают влияние на у.
Для коэффициента b1 вероятность его не влияния на у равна 0,00 (0%), что меньше порогового значения в 5%, поэтому коэффициент b1 признается значимым и оставляется в модели.
Для коэффициента а вероятность его не влияния на у равна 0,885 (8,85%), что больше порогового значения в 5%, поэтому коэффициент а признается не значимым и должен быть удален из модели.
Для коэффициента b2 вероятность его не влияния на у равна 0,003 (3%), что меньше порогового значения в 5%, поэтому коэффициент признается значимым и оставляется в модели.
Для коэффициента b3 вероятность его не влияния на у равна 0,007 (7%), что меньше порогового значения в 5%, поэтому коэффициент признается значимым и оставляется в модели.
В первую очередь из модели будет исключена константа, поскольку вероятность ее не влияния на у, определяемая соответствующим ей коэффициентом а выше, чем для прочих факторов
. После этого процедура регрессионного анализа проводится заново, для чего в опции Сервис - Анализ данных - Регрессия в строке «Входной интервал Х» задается те же 3 столбца данных, а значение константы задается нулевым. По полученным результатам вновь оценивается значимость коэффициентов регрессии.
На втором этапе полученные результаты могут иметь следующие значения:
Коэффициенты P-Значение
а 0,000 #Н/Д
b1 181,117 0,000
b2 3288,403 0,001
b3 0,611 0,004
Для коэффициента b1 вероятность его не влияния на у равна 0,00 (0%), что меньше порогового значения в 5%, поэтому коэффициент b1 признается значимым и оставляется в модели.
Для коэффициента b2 вероятность его не влияния на у равна 0,001 (0,1%), что меньше порогового значения в 5%, поэтому коэффициент b2 признается значимым и оставляется в модели.
Для коэффициента b3 вероятность его не влияния на у равна 0,004 (0,4%), что меньше порогового значения в 5%, поэтому коэффициент b3 признается значимым и оставляется в модели.
Окончательное уравнение регрессии запишется в виде:
у = 181,117 х1 + 3288,403 х2 + 0,611 х3.
Определение доверительных интервалов для
При выполнении регрессионного анализа в пакете Excel доверительные интервалы автоматически выводятся (наряду с другими результатами) для 95-%-го уровня надежности.
В результате выполнения регрессионного анализа в пакете Excel получены оценки коэффициентов регрессии и их доверительные интервалы:
Коэффициенты Нижние 95% Верхние 95%
b1 181,117 112,858 249,375
b2 3288,403 1461,378 5115,429
b3 0,611 0,203 1,018
Значит, 112,858 < 1 < 249,375 при 95-%-м уровне надежности;
1461,378 < 2 < 5115,429 при 95-%-м уровне надежности;
0,203 < 4 < 1,018 при 95-%-м уровне надежности.
Определение коэффициента корреляции и его значимости
В результате выполнения регрессионного анализа в пакете Excel получено значение R и Значимости F:
Множественный R0,999
Значимость F0,000
Коэффициент корреляции R = 0,999 0,7 - связь между переменными модели тесная.
Вероятность незначимости (недостоверности) коэффициента корреляции низкая: 0% (по сравнению с пороговым значением 5%), значит, количество наблюдений считается достаточным для признания полученных результатов регрессионного анализа достоверными.
Определение коэффициента детерминации
Коэффициент детерминации рассчитывается следующим образом:
Дисперсионный анализ
SS
Регрессия 395154914033,456 RSS (объясн)
Остаток 1131550161,544 ESS (остат)
Итого 396286464195,000 TSS (общая)
R2 = TSS / RSS = 396286464195,000 / 395154914033,456 = 0,997
Таким образом, 99,7% вариации значений накладных расходов объясняется включенными в модель факторами, качество уравнения регрессии – высокое
Задать вопрос
на новый заказ в Автор24.
