Известны статистические данные по 36 строительным бригадам.
Необходимо выяснить влияние различных факторов на величину накладных расходов в строительстве. Известно, что к накладным расходам относятся административно-хозяйственные, коммунальные расходы, дополнительная заработная плата и другие расходы. На качественном уровне выявлено, что фактический уровень накладных расходов оказался наиболее тесно связан со следующими факторами: объемом выполненных работ, численностью рабочих, занятых на строительно-монтажных работах, фондом заработной платы. Остальные факторы были признаны незначимыми.
На основании имеющихся данных необходимо при помощи использования функций Excel:
1. Рассчитать параметры множественной линейной регрессии, проводя процедуру стандартного регрессионного исследования до получения удовлетворительной модели. Провести полный анализ полученного уравнения регрессионной связи.
К числу рассчитываемых и анализируемых параметров относятся:
а) коэффициенты регрессии (и их значимость);
б) коэффициент корреляции (и его значимость);
в) коэффициент детерминации;
г) стандартные ошибки коэффициентов регрессии;
д) доверительные интервалы для коэффициентов регрессии;
е) величины общей, объясненной и остаточной дисперсии.
2. На основании реальных и расчетных значений накладных расходов построить графики и сравнить их.
Статистические данные по исследуемым показателям приведены в таблице:
№ Накладные расходы Объем работ (куб. м.) Численность рабочих (чел.) Фонд заработной платы (руб.)
1 82131 209 7 24479
2 125520 366 9 53190
3 87500 251 7 36424
4 54727 135 4 24415
5 147009 427 12 62051
6 113787 295 7 38681
7 76619 222 5 25552
8 98316 273 7 41226
9 60273 156 6 23599
10 125749 325 8 51006
11 128742 391 10 48587
12 100606 299 8 45395
13 188795 569 13 65468
14 164337 453 14 62509
15 119722 348 11 51284
16 167289 504 14 73132
17 104454 288 8 33348
18 55872 136 4 22717
19 51447 147 4 21545
20 71093 190 6 26363
21 87356 251 6 32261
22 67517 177 4 27269
23 93300 232 7 39513
24 102265 308 8 39975
25 152681 402 10 59132
26 72820 210 6 26917
27 119453 343 9 44957
28 63436 199 5 21792
29 107980 297 9 38186
30 80760 259 6 34462
31 82131 209 7 24479
32 125520 366 9 53190
33 87500 251 7 36424
34 54727 135 4 24415
35 147009 427 12 62051
36 113787 295 7 38681
Решение
1. Рассчитать параметры множественной линейной регрессии, проводя процедуру стандартного регрессионного исследования до получения удовлетворительной модели. Провести полный анализ полученного уравнения регрессионной связи.
На первом этапе включим в модель все факторы. В качестве программного средства реализации анализа воспользуемся пакетом прикладных программ Microsoft Excel, функцией «Анализ данных», инструмент «Регрессия». Применение инструмента «Регрессия»
(Анализ данных EXCEL)
Для проведения регрессионного анализа необходимо выполнить следующие действия:
Выбрать команду «Сервис»→ «Анализ данных».
В диалоговом окне «Анализ данных» выбрать инструмент «Регрессия», а затем щёлкнуть по кнопке ОК.
В диалоговом окне «Регрессия» в поле «Входной интервал » ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле «Входной интервал Х» ввести адреса одного или нескольких диапазонов, которые содержат значения независимых переменных.
Если введены и заголовки столбцов, то следует установить флажок «Метки в первой строке».
Выбрать параметры вывода. В данном случае «Новая рабочая книга».
ОК.
Результаты представлены в таблице 1.
Таблица 1
Без проверки значимости коэффициентов а и b уравнение регрессии было бы записано в следующем виде:
у = 7611,728 + 252,109х1 + 949,357х2 + 0,372х3.
Однако необходимо проверить, все ли из включенных в уравнение параметров действительно оказывают влияние на у.
К числу рассчитываемых и анализируемых параметров относятся:
а) коэффициенты регрессии (и их значимость):
Значимость коэффициентов регрессии оценим с помощью критерия Стьюдента.
Расчетные значения критерия Стьюдента следующие: t1=6,726; и t3=1,558. Табличное значение критерия при уровне значимости и числе степеней свободы равно 2,037.
Таким образом, признается статистическая значимость параметров ,т.к. tb1=6,726>tтабл=2,04 .
Таким образом, признается статистическая не значимость параметра и ,т.к. и tb2=0,831<tтабл=2,04 и tb3=1,558<tтабл=2,04.
В результате выполнения регрессионного анализа в пакете Excel получены оценки а и b и их Р – значения:
Коэффициенты Р-значение
а 7611,728 0,00
b1 252,109 0,00
b2 949,357 0,41
b3 0,372 0,13
Для коэффициента b1 вероятность его не влияния на у равна 0,00 (0%), что меньше порогового значения в 5%, поэтому коэффициент b1 признается значимым и оставляется в модели.
Для коэффициента а вероятность его не влияния на у равна 0,00 (40%), что меньше порогового значения в 5%, поэтому коэффициент а признается значимым и должен присутствовать в модели. Для коэффициента b2 вероятность его не влияния на у равна 0,41 (41%), что больше порогового значения в 5%, поэтому коэффициент признается незначимым и должен быть удален из модели
. Для коэффициента b3 вероятность его не влияния на у равна 0,13 (13%), что больше порогового значения в 5%, поэтому коэффициент признается незначимым и должен быть удален из модели.
В первую очередь из модели будет исключена переменная х2 и х3 поскольку вероятность их не влияния на у, определяемая соответствующим коэффициентам b2 и b3 выше, чем для константы а. После этого процедура регрессионного анализа проводится заново, для чего в опции Сервис - Анализ данных - Регрессия в строке «Входной интервал Х» задается уже не 3 столбца данных, а один - соответствующий переменным х1.
Определение значимости коэффициента корреляции
б) коэффициент корреляции (и его значимость);
Множественный коэффициент корреляции R, равный 0,987, свидетельствует о тесной связи между признаками.
При выполнении регрессионного анализа в пакете Excel вероятность выполнения нулевой гипотезы для коэффициента корреляции выводится как “Значимость F”.
Если Значимость F меньше 0,05, то количество наблюдений считается достаточным для признания полученных результатов регрессионного анализа достоверными. Если Значимость F меньше 0,05, то коэффициент корреляции незначим, и количество наблюдений необходимо увеличить.
В результате выполнения регрессионного анализа в пакете Excel получено значение R и Значимости F:
Множественный R0,987
Значимость F0,00
Вероятность незначимости (недостоверности) коэффициента корреляции достаточно мала: 0% (по сравнению с пороговым значением 5%), значит, количество наблюдений достаточно.
Определение коэффициента детерминации
Коэффициент детерминации рассчитывается следующим образом:
Значения TSS, RSS и ESS выдаются в качестве результатов выполнения регрессионного анализа в Excel в таблице «Дисперсионный анализ»
Дисперсионный анализ
SS
Регрессия 42508711325 RSS (объясн)
Остаток 1102446150 ESS (остат)
Итого 43611157475 TSS (общая)
Коэффициент детерминации можно рассчитать также как квадрат коэффициента корреляции. При выполнении регрессионного анализа в Excel коэффициент детерминации выводится в таблице «Вывод итогов» как величина R-квадрат.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,987279599
R-квадрат 0,974721007
Нормированный R-квадрат 0,972351101
Стандартная ошибка 5869,535091
R2 = RSS / TSS =42508711325 / 43611157475= 0,987
г) стандартные ошибки коэффициентов регрессии:
д) доверительные интервалы для коэффициентов регрессии:
Определение доверительных интервалов для и
Коэффициенты Нижние 95% Верхние 95%
а 7611,728 1581,04513642,412
b1 252,109 175,758328,461
b2 949,357 –1377,131 3275,846
b2 0,372 – 0,1140,859
Значит, 1581,045< < 13642,412; 175,758< 1 < 328,461; –1377,131< 2 < 3275,846 и –0,114< 3 < 0,859.
е) величины общей, объясненной и остаточной дисперсии.
Таблица дисперсионного анализа для простой линейной регрессии
Источник дисперсии Сумма квадратов Число степеней свободы
Регрессия 42508711325
Отклонение от регрессии 1102446150
Полная дисперсия 43611157475
Нормированный коэффициент детерминации
При выполнении регрессионного анализа в Excel коэффициент детерминации и стандартная ошибка уравнения регрессии выводятся в таблице «Вывод итогов» как величины R-квадрат и Стандартная ошибка.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,987279599
R-квадрат 0,974721007
Нормированный R-квадрат 0,972351101
Стандартная ошибка 5869,535091
На втором этапе включим в модель только значимые факторы
Задать вопрос
на новый заказ в Автор24.
