Известны статистические данные по 36 строительным бригадам.
Необходимо выяснить влияние различных факторов на величину накладных расходов в строительстве. Известно, что к накладным расходам относятся административно-хозяйственные, коммунальные расходы, дополнительная заработная плата и другие расходы. На качественном уровне выявлено, что фактический уровень накладных расходов оказался наиболее тесно связан со следующими факторами: объемом выполненных работ, численностью рабочих, занятых на строительно-монтажных работах, фондом заработной платы. Остальные факторы были признаны незначимыми.
На основании имеющихся данных необходимо при помощи использования функций Excel:
1. Рассчитать параметры множественной линейной регрессии, проводя процедуру стандартного регрессионного исследования до получения удовлетворительной модели. Провести полный анализ полученного уравнения регрессионной связи.
К числу рассчитываемых и анализируемых параметров относятся:
а) коэффициенты регрессии (и их значимость);
б) коэффициент корреляции (и его значимость);
в) коэффициент детерминации;
г) стандартные ошибки коэффициентов регрессии;
д) доверительные интервалы для коэффициентов регрессии;
е) величины общей, объясненной и остаточной дисперсии.
2. На основании реальных и расчетных значений накладных расходов построить графики и сравнить их.
Статистические данные по исследуемым показателям приведены в таблице:
№ Накладные расходы Объем работ (куб. м.) Численность рабочих (чел.) Фонд заработной платы (руб.)
1 79761 218 6 26174
2 124107 374 10 53546
3 85270 242 6 28464
4 56397 139 4 24222
5 145995 420 9 59824
6 103661 305 7 37174
7 80047 217 6 28834
8 97654 281 9 38014
9 60585 160 5 20403
10 117824 312 10 40341
11 132570 379 10 54202
12 102816 285 7 46941
13 193171 561 14 62277
14 162639 455 10 60092
15 127066 349 9 55156
16 182923 508 11 54765
17 101445 289 8 38557
18 52198 130 5 23901
19 49938 139 4 18324
20 71518 201 7 22452
21 84552 232 7 30746
22 65016 187 4 20813
23 87197 238 7 29172
24 102038 315 9 39224
25 146157 408 12 47225
26 68673 206 7 24905
27 121496 360 10 41073
28 69200 195 5 26462
29 99268 287 6 38714
30 83586 247 7 25425
31 94654 238 6 35580
32 68479 210 5 25021
33 90667 258 6 28711
34 64674 192 5 24476
35 102939 321 9 30938
36 87036 252 8 32599
Решение
1. Рассчитать параметры множественной линейной регрессии, проводя процедуру стандартного регрессионного исследования до получения удовлетворительной модели. Провести полный анализ полученного уравнения регрессионной связи.
На первом этапе включим в модель все факторы. В качестве программного средства реализации анализа воспользуемся пакетом прикладных программ Microsoft Excel, функцией «Анализ данных», инструмент «Регрессия». Применение инструмента «Регрессия»
(Анализ данных EXCEL)
Для проведения регрессионного анализа необходимо выполнить следующие действия:
Выбрать команду «Сервис»→ «Анализ данных».
В диалоговом окне «Анализ данных» выбрать инструмент «Регрессия», а затем щёлкнуть по кнопке ОК.
В диалоговом окне «Регрессия» в поле «Входной интервал » ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле «Входной интервал Х» ввести адреса одного или нескольких диапазонов, которые содержат значения независимых переменных.
Если введены и заголовки столбцов, то следует установить флажок «Метки в первой строке».
Выбрать параметры вывода. В данном случае «Новая рабочая книга».
ОК.
Результаты представлены в таблице 1.
Таблица 1
Без проверки значимости коэффициентов а и b уравнение регрессии было бы записано в следующем виде:
у = 2188,633 + 305,358х1 – 90,976х2 + 0,326х3.
Однако необходимо проверить, все ли из включенных в уравнение параметров действительно оказывают влияние на у.
К числу рассчитываемых и анализируемых параметров относятся:
а) коэффициенты регрессии (и их значимость):
Значимость коэффициентов регрессии оценим с помощью критерия Стьюдента.
Расчетные значения критерия Стьюдента следующие: t1=11,48; и t3=2,13. Табличное значение критерия при уровне значимости и числе степеней свободы равно 2,037.
Таким образом, признается статистическая значимость параметров и ,т.к. tb1=11,48>tтабл=2,04 и tb3=2,13>tтабл=2,04.
Таким образом, признается статистическая не значимость параметра т.к. и tb2=1,17<tтабл=2,04 .
В результате выполнения регрессионного анализа в пакете Excel получены оценки а и b и их Р – значения:
Коэффициенты Р-значение
а 2188,633 0,40
b1 305,358 0,00
b2 –90,976 0,91
b3 0,326 0,04
Для коэффициента b1 вероятность его не влияния на у равна 0,00 (0%), что меньше порогового значения в 5%, поэтому коэффициент b1 признается значимым и оставляется в модели.
Для коэффициента а вероятность его не влияния на у равна 0,40 (40%), что больше порогового значения в 5%, поэтому коэффициент а признается не значимым и должен быть удален из модели. Для коэффициента b2 вероятность его не влияния на у равна 0,91 (91%), что больше порогового значения в 5%, поэтому коэффициент признается незначимым и должен быть удален из модели
. Для коэффициента b3 вероятность его не влияния на у равна 0,04 (4%), что меньше порогового значения в 5%, поэтому коэффициент признается значимым и должен присутствовать в модели.
В первую очередь из модели будет исключена переменная х2 поскольку вероятность их не влияния на у, определяемая соответствующим коэффициентам b2 выше, чем для константы а. После этого процедура регрессионного анализа проводится заново, для чего в опции Сервис - Анализ данных - Регрессия в строке «Входной интервал Х» задается уже не 3 столбца данных, а два - соответствующий переменным х1 и х3.
Определение значимости коэффициента корреляции
б) коэффициент корреляции (и его значимость);
Множественный коэффициент корреляции R, равный 0,993, свидетельствует о тесной связи между признаками.
При выполнении регрессионного анализа в пакете Excel вероятность выполнения нулевой гипотезы для коэффициента корреляции выводится как “Значимость F”.
Если Значимость F меньше 0,05, то количество наблюдений считается достаточным для признания полученных результатов регрессионного анализа достоверными. Если Значимость F меньше 0,05, то коэффициент корреляции незначим, и количество наблюдений необходимо увеличить.
В результате выполнения регрессионного анализа в пакете Excel получено значение R и Значимости F:
Множественный R0,993
Значимость F0,00
Вероятность незначимости (недостоверности) коэффициента корреляции достаточно мала: 0% (по сравнению с пороговым значением 5%), значит, количество наблюдений достаточно.
Определение коэффициента детерминации
Коэффициент детерминации рассчитывается следующим образом:
Значения TSS, RSS и ESS выдаются в качестве результатов выполнения регрессионного анализа в Excel в таблице «Дисперсионный анализ»
Дисперсионный анализ
SS
Регрессия 42687361245 RSS (объясн)
Остаток 631376242 ESS (остат)
Итого 43318737487 TSS (общая)
Коэффициент детерминации можно рассчитать также как квадрат коэффициента корреляции. При выполнении регрессионного анализа в Excel коэффициент детерминации выводится в таблице «Вывод итогов» как величина R-квадрат.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,992685685
R-квадрат 0,98542487
Нормированный R-квадрат 0,984058451
Стандартная ошибка 4441,903597
R2 = RSS / TSS =42687361245 / 43318737487= 0,985
г) стандартные ошибки коэффициентов регрессии:
д) доверительные интервалы для коэффициентов регрессии:
Определение доверительных интервалов для и
Коэффициенты Нижние 95% Верхние 95%
а 2188,633 –3026,66 7403,93
b1 305,358 251,16359,55
b2 –90,976 –1696,53 1514,57
b2 0,326 0,0140,638
Значит, –3026,66< < 7403,93; 251,16< 1 < 359,55; –1696,53< 2 < 1514,57 и 0,014< 3 < 0,638.
е) величины общей, объясненной и остаточной дисперсии.
Таблица дисперсионного анализа для простой линейной регрессии
Источник дисперсии Сумма квадратов Число степеней свободы
Регрессия 42687361245
Отклонение от регрессии 631376242
Полная дисперсия 43318737487
Нормированный коэффициент детерминации
При выполнении регрессионного анализа в Excel коэффициент детерминации и стандартная ошибка уравнения регрессии выводятся в таблице «Вывод итогов» как величины R-квадрат и Стандартная ошибка.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,992685685
R-квадрат 0,98542487
Нормированный R-квадрат 0,984058451
Стандартная ошибка 4441,903597
На втором этапе включим в модель только значимые факторы
Задать вопрос
на новый заказ в Автор24.
