Известны статистические данные по 36 строительным
бригадам. Необходимо выяснить влияние различных факторов на величину накладных расходов в строительстве. Известно, что к накладным расходам относятся административно-хозяйственные, коммунальные расходы, дополнительная заработная плата и другие расходы. На качественном уровне выявлено, что фактический уровень накладных расходов оказался наиболее тесно связан со следующими факторами: объемом выполненных работ, численностью рабочих, занятых на строительно-монтажных работах, фондом заработной платы. Остальные факторы были признаны незначимыми.
На основании имеющихся данных необходимо при помощи использования функций Excel
1. Рассчитать параметры множественной линейной регрессии, проводя процедуру стандартного регрессионного исследования до получения удовлетворительной модели. Провести полный анализ полученного уравнения регрессионной связи.
К числу рассчитываемых и анализируемых параметров относятся:
а) коэффициенты регрессии (и их значимость);
б) коэффициент корреляции (и его значимость);
в) коэффициент детерминации;
г) стандартные ошибки коэффициентов регрессии;
д) доверительные интервалы для коэффициентов регрессии;
е) величины общей, объясненной и остаточной дисперсии.
2. На основании реальных и расчетных значений накладных расходов построить графики и сравнить их.
Статистические данные по исследуемым показателям приведены в таблице:
Вариант 225410%10%20%20%
№ Накладные
расходы
( руб. ) Объем
работ
( куб. м. ) Численность
рабочих
( чел. ) Фонд заработной
платы
( руб. )
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
Решение
Введем в рассмотрение переменные:
У – накладные расходы (руб.),
Х1 – объем работ (куб.м.),
Х2 – численность рабочих (чел.),
Х3 – фонд заработной платы (руб.).
1. Рассмотрим регрессионную модель, включив в нее все исходные показатели. Для построения уравнения линейной регрессии используем функцию «Сервис. Анализ данных. Регрессия».
Задав соответствующие диапазоны данных в окне (рис 1.1) получим набор таблиц 1, 2, 3, 4.
Таблица 1. Регрессионная статистика
Множественный R 0,986361
R-квадрат 0,972908
Нормированный R-квадрат 0,970368
Стандартная ошибка 5944,538
Наблюдения 36
Таблица 2. Дисперсионный анализ
df
SS MS F Значимость F
Регрессия 3 4,06E+10 1,35E+10 383,0513 3,84E-25
Остаток 32 1,13E+09 35337530
Итого 35 4,17E+10
Таблица 3. Данные для уравнения регрессии.
Коэффициенты Стандартная ошибка t-статистика P-Значение Нижние 95% Верхние 95%
Y-пересечение 486,7756 3343,087 0,145607 0,885145 -6322,87 7296,421
Х1 182,3099 35,03089 5,204262 1,1E-05 110,9543 253,6655
Х2 3227,064 1004,264 3,213364 0,00299 1181,446 5272,683
Х3 0,602618 0,21097 2,856413 0,007467 0,172886 1,032349
Таблица 4. Прогнозные значения и остатки.
Наблюдение Предсказанное У Остатки
1 78985,06 963,9434
2 124865 492,9565
3 85134,06 -3665,06
4 49406,59 8018,41
5 150131,4 421,5962
6 107434,4 4107,585
7 79653,83 -812,828
8 94936,73 9866,265
9 61007,43 -1131,43
10 121304,8 2653,185
11 129733,3 8606,704
12 103449,7 -1343,7
13 193350,1 -4998,1
14 156129,6 1222,443
15 124703,2 4523,809
16 177419,3 -6664,26
17 103731,2 62,78085
18 49971,95 6040,046
19 54349,92 -3754,92
20 70116,26 2958,745
21 85489,52 -3844,52
22 64659,37 914,6349
23 90000,16 -4719,16
24 104264,1 -3414,11
25 136375,4 16906,59
26 74662,13 -928,13
27 114035,2 6,818059
28 64796,49 1994,514
29 109999,4 -7717,38
30 87210,15 -4930,15
31 89327,07 4974,93
32 74625,19 -6423,19
33 94087,82 3698,183
34 72243,86 -9250,86
35 105570,2 -6288,24
36 89473,11 -8548,11
Из табл
. 3 следует, что уравнение регрессии имеет вид:
Y=486,7756 + 182,3099x1 + 3227,064х2 + 0,602618x3 (1)
(0,146) (5,204) (3,213) (2,856)
а)В скобках указаны tнабл(bj), расчетные значения t – критерия для проверки гипотезы о значимости коэффициента регрессии Н0: βj=0, j=1,...,3. Критическое значение tкр=2,04 найдено по таблице t – распределения при уровне значимости α=0,05 и числе степеней свободы ν=36-3-1=32. Из уравнения следует, что являются статистически значимыми все коэффициенты регрессии - при х1, х2 и х3 . Более того, коэффициенты регрессии bj (j=1,...,3) статистически значимы и на уровне значимости α=0,01, так как в столбце р –значение табл.3 числа 1,1E-05, 0,00299 и 0,007467 меньше 0,01.
б) Построим матрицу парных коэффициентов корреляции. Для этого последовательно выполним следующие команды: Сервис – Анализ данных – Корреляция – ОК. Вводим входной и выходной интервалы, нажимаем кнопку ОК, на листе появится матрица парных коэффициентов корреляции.
Y Х1 Х2 Х3
Y 1
Х1 0,9784 1
Х2 0,9354 0,9183 1
Х3 0,9421 0,9322 0,8555 1
Чем ближе значение коэффициента корреляции к единице, тем теснее корреляционная связь.
Полученные значения показывают:
- связь прямая, сильная;
- связь прямая, сильная;
- связь прямая, сильная.
Анализ матрицы парных коэффициентов корреляции показывает, что результативный показатель наиболее тесно связан с показателем x1, чуть менее с х2 и х3.
Вычислим t-критерии по формуле :
Фактические значения t-критериев сравниваем с табличным значением при степенях свободы (n – k – 1)=32 и уровне значимости α = 0,05:
tтабл = 2,04
Задать вопрос
на новый заказ в Автор24.
