Известны статистические данные по 36 строительным бригадам.
Необходимо выяснить влияние различных факторов на величину накладных расходов в строительстве. Известно, что к накладным расходам относятся административно-хозяйственные, коммунальные расходы, дополнительная заработная плата и другие расходы. На качественном уровне выявлено, что фактический уровень накладных расходов оказался наиболее тесно связан со следующими факторами: объемом выполненных работ, численностью рабочих, занятых на строительно-монтажных работах, фондом заработной платы. Остальные факторы были признаны незначимыми.
На основании имеющихся данных необходимо при помощи использования функций Excel:
1. Рассчитать параметры множественной линейной регрессии, проводя процедуру стандартного регрессионного исследования до получения удовлетворительной модели. Провести полный анализ полученного уравнения регрессионной связи.
К числу рассчитываемых и анализируемых параметров относятся:
а) коэффициенты регрессии (и их значимость);
б) коэффициент корреляции (и его значимость);
в) коэффициент детерминации;
г) стандартные ошибки коэффициентов регрессии;
д) доверительные интервалы для коэффициентов регрессии;
е) величины общей, объясненной и остаточной дисперсии.
2. На основании реальных и расчетных значений накладных расходов построить графики и сравнить их.
Статистические данные по исследуемым показателям приведены в таблице:
№ Накладные расходы Объем работ (куб. м.) Численность рабочих (чел.) Фонд заработной платы (руб.)
1 83467 222 7 31302
2 120742 366 10 54594
3 88868 243 8 31438
4 59724 132 5 17512
5 154567 430 9 59009
6 112629 292 8 44879
7 80563 219 5 28134
8 100042 266 7 30722
9 60882 160 4 18763
10 121955 315 8 52876
11 135495 360 11 48707
12 104517 296 9 42188
13 199648 576 15 78347
14 157640 433 14 68638
15 126565 331 9 50546
16 168667 527 14 61941
17 96499 275 9 33536
18 53588 137 4 18771
19 50344 145 5 19957
20 70423 202 5 26733
21 79892 251 5 30680
22 67847 186 5 27506
23 92770 219 8 38193
24 104795 290 9 38920
25 149675 417 13 45130
26 112629 292 8 44879
27 80563 219 5 28134
28 100042 266 7 30722
29 60882 160 4 18763
30 121955 315 8 52876
31 135495 360 11 48707
32 104517 296 9 42188
33 199648 576 15 78347
34 157640 433 14 68638
35 126565 331 9 50546
36 168667 527 14 61941
Решение
1. Рассчитать параметры множественной линейной регрессии, проводя процедуру стандартного регрессионного исследования до получения удовлетворительной модели. Провести полный анализ полученного уравнения регрессионной связи.
На первом этапе включим в модель все факторы. В качестве программного средства реализации анализа воспользуемся пакетом прикладных программ Microsoft Excel, функцией «Анализ данных», инструмент «Регрессия». Применение инструмента «Регрессия»
(Анализ данных EXCEL)
Для проведения регрессионного анализа необходимо выполнить следующие действия:
Выбрать команду «Сервис»→ «Анализ данных».
В диалоговом окне «Анализ данных» выбрать инструмент «Регрессия», а затем щёлкнуть по кнопке ОК.
В диалоговом окне «Регрессия» в поле «Входной интервал » ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле «Входной интервал Х» ввести адреса одного или нескольких диапазонов, которые содержат значения независимых переменных.
Если введены и заголовки столбцов, то следует установить флажок «Метки в первой строке».
Выбрать параметры вывода. В данном случае «Новая рабочая книга».
ОК.
Результаты пре дставлены в таблице 1.
Таблица 1
Без проверки значимости коэффициентов а и b уравнение регрессии было бы записано в следующем виде:
у = 9976,96 + 208,18х1 + 1025,59х2 + 0,675х3.
Однако необходимо проверить, все ли из включенных в уравнение параметров действительно оказывают влияние на у.
К числу рассчитываемых и анализируемых параметров относятся:
а) коэффициенты регрессии (и их значимость):
Значимость коэффициентов регрессии оценим с помощью критерия Стьюдента.
Расчетные значения критерия Стьюдента следующие: t1=7,05; и t3=3,96. Табличное значение критерия при уровне значимости и числе степеней свободы равно 2,037.
Таким образом, признается статистическая значимость параметров и ,т.к. tb1=7,05>tтабл=2,04 и tb3=3,96>tтабл=2,04.
Таким образом, признается статистическая не значимость параметра и ,т.к. и tb2=1,23<tтабл=2,04.
В результате выполнения регрессионного анализа в пакете Excel получены оценки а и b и их Р – значения:
Коэффициенты Р-значение
а 9976,96 0,00
b1 208,18 0,00
b2 1025,59 0,23
b3 0,675 0,00
Для коэффициента b1 вероятность его не влияния на у равна 0,00 (0%), что меньше порогового значения в 5%, поэтому коэффициент b1 признается значимым и оставляется в модели.
Для коэффициента а вероятность его не влияния на у равна 0,00 (0%), что меньше порогового значения в 5%, поэтому коэффициент а признается значимым и должен присутствовать в модели. Для коэффициента b2 вероятность его не влияния на у равна 0,23 (23%), что больше порогового значения в 5%, поэтому коэффициент признается незначимым и должен быть удален из модели
. Для коэффициента b3 вероятность его не влияния на у равна 0,00 (0%), что меньше порогового значения в 5%, поэтому коэффициент признается значимым и должен присутствовать в модели.
В первую очередь из модели будет исключена переменная х2, поскольку вероятность ее не влияния на у, определяемая соответствующим ей коэффициентом b2 выше, чем для константы а. После этого процедура регрессионного анализа проводится заново, для чего в опции Сервис - Анализ данных - Регрессия в строке «Входной интервал Х» задается уже не 3 столбца данных, а два - соответствующий переменным х1 и х3.
Определение значимости коэффициента корреляции
б) коэффициент корреляции (и его значимость);
Множественный коэффициент корреляции R, равный 0,992, свидетельствует о тесной связи между признаками.
При выполнении регрессионного анализа в пакете Excel вероятность выполнения нулевой гипотезы для коэффициента корреляции выводится как “Значимость F”.
Если Значимость F меньше 0,05, то количество наблюдений считается достаточным для признания полученных результатов регрессионного анализа достоверными. Если Значимость F меньше 0,05, то коэффициент корреляции незначим, и количество наблюдений необходимо увеличить.
В результате выполнения регрессионного анализа в пакете Excel получено значение R и Значимости F:
Множественный R0,992
Значимость F0,00
Вероятность незначимости (недостоверности) коэффициента корреляции достаточно мала: 0% (по сравнению с пороговым значением 5%), значит, количество наблюдений достаточно.
Определение коэффициента детерминации
Коэффициент детерминации рассчитывается следующим образом:
Значения TSS, RSS и ESS выдаются в качестве результатов выполнения регрессионного анализа в Excel в таблице «Дисперсионный анализ»
Дисперсионный анализ
SS
Регрессия 54309726071 RSS (объясн)
Остаток 932182212,2 ESS (остат)
Итого 55241908284 TSS (общая)
Коэффициент детерминации можно рассчитать также как квадрат коэффициента корреляции. При выполнении регрессионного анализа в Excel коэффициент детерминации выводится в таблице «Вывод итогов» как величина R-квадрат.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,991526829
R-квадрат 0,983125452
Нормированный R-квадрат 0,981543464
Стандартная ошибка 5397,285811
R2 = TSS / RSS = 55241908284/ 5430972607= 0,983
г) стандартные ошибки коэффициентов регрессии:
д) доверительные интервалы для коэффициентов регрессии:
Определение доверительных интервалов для и
Коэффициенты Нижние 95% Верхние 95%
а 9976,96 4813,7815140,15
b1 208,18 148,01268,36
b2 1025,59 –674,502725,67
b2 0,675 0,3281,023
Значит, 4813,78 < < 15140,15; 148,01< 1 < 268,36; –674,50< 2 < 2725,67 и 0,328< 3 < 1,023 .
е) величины общей, объясненной и остаточной дисперсии.
Таблица дисперсионного анализа для простой линейной регрессии
Источник дисперсии Сумма квадратов Число степеней свободы
Регрессия 54309726071
Отклонение от регрессии 932182212,2
Полная дисперсия 55241908284
Нормированный коэффициент детерминации
При выполнении регрессионного анализа в Excel коэффициент детерминации и стандартная ошибка уравнения регрессии выводятся в таблице «Вывод итогов» как величины R-квадрат и Стандартная ошибка.
Пример.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,991526829
R-квадрат 0,983125452
Нормированный R-квадрат 0,981543464
Стандартная ошибка 5397,285811
На втором этапе включим в модель только значимые факторы
Задать вопрос
на новый заказ в Автор24.
