Методический пример выполнения лабораторной работы №1

На примере Варианта № 8 – СМУ 1, 2, 4, 6.

Ход работы: необходимо в новой книге Excel создать на отдельном листе таблицу с исходными данными следующего вида:

Основная таблицаВспомогательная табл.

Для расчета Y, Х1 и Х2 целесообразно составить справа от основной таблицы вспомогательную таблицу. Шапка основной таблицы, номера СМУ, месяцы, значения Х2, Х3, Х4, Х5, Х6 набираются вручную. Для расчета Y и Х1 в первую ячейку основной таблицы необходимо впечатать следующие формулы:

- для столбца Y: [ =V.смр/ ср.числ.раб. всего];

- для столбца Х1: [ = ср.числ.раб.сдел./ ср.числ.раб.].

Далее необходимо скопировать формулы для столбцов Y и X1 следующим образом. Выделив одним щелчком левой кнопкой мышки ячейку с формулой, необходимо левой кнопкой мышки захватить правый нижний угол ячейки (появится черный крестик) и протащить вертикально вниз по столбцу до последнего наблюдения. Повторить данную операцию для Y и X1.

1. Проведение многошагового корреляционно-регрессионного анализа с применением средств Excel.

Примечание: при выполнении работы используются инструменты «Пакета анализа» Excel «Корреляция» и «Регрессия». При этом, в учебных целях, в работе используются не все показатели, рассчитанные с помощью названных инструментов. При использовании инструмента «Корреляция» в работе используются коэффициенты корреляции, рассчитанные с помощью данного инструмента. При использовании инструмента «Регрессия» в работе используются коэффициенты регрессии и прогнозные значения результирующего признака. Все остальные показатели, необходимые для достижения поставленных в работе целей, должны быть рассчитаны с помощью формул, которые приведены в методических указаниях к выполнению работы.

Для целей анализа используется линейная модель множественной регрессии вида:

Вм = а₀ + а₁х₁ + а₂х₂ + … а_nx_n, где

Вм – месячная выработка рабочего;

а₀ – свободный член уравнения (характеризует постоянную «нераспределенную» часть Вм вне зависимости от воздействия факторных признаков);

а₁…а_n – коэффициенты регрессии, характеризующие изменение выработки от изменения каждого факторного признака на единицу фиксированных значений остальных факторов;

x₁…x_n – факторные признаки (независимые переменные).

1.1. Построение корреляционной матрицы с помощью средств пакета анализа программного продукта Excel.

Для построения корреляционной матрицы необходимо зайти в меню Сервис выбрать Анализ данных*:

* Если эта команда недоступна, загрузите пакет анализа:

1. В меню Сервис выберите команду Надстройки (см. предыдущий рисунок).

2. В списке надстроек выберите Пакет анализа и нажмите кнопку OK.

3. Выполните инструкции программы установки, если это необходимо.

Далее в Анализ данных выбрать Корреляция и нажать ОК:

Нажмите на кнопку справа от ячейки Входной интервал:

Мышкой выделите весь массив данных Y и Х (нажать левой кнопкой мыши на верхнее левое значение Y и не отпуская протащить до последнего значения X6):

и нажмите Ввод, после чего в окне Корреляция появится входной интервал:

Нажмите ОК. После этого на новом рабочем листе (Лист 1) появится таблица следующего вида:

Столбцу 1 соответствует Y, Стобец 2 – Солбец 7 соответствует Х1 – Х6 соответственно.

Переименуйте столбцы и строки:

Переименуйте Лист 1 в Корреляция (быстрый двойной щелчок левой кнопкой мыши внизу на закладке Лист 1, и впечатать новое название закладки):

2. На основе расчетных значений коэффициентов корреляции корреляционной матрицы, полученных в ходе выполнения пункта 1., следует оценить их статическую значимость и проверку наличия мультиколлинеарности. Отбор независимых переменных для исключения их из дальнейшего анализа производится если:

· теснота связи Rxx≥ 0, 85 (в этом случае одна из независимых переменных, связь которой с зависимой слабее, исключается);

В данном примере такие связи отсутствуют

Знак коэффициента корреляции Ryx не отвечает известному экономическому содержанию связи между зависимой и независимой переменными (или студент испытывает затруднения с интерпретацией полученной связи);

В данном примере исключаем Х5, так как связь XY должна быть со знаком «минус», в расчетной таблице – плюс

· теснота статистической связи Ryx статически несущественна.

Для оценки значимости коэффициента корреляции r используют t-критерий Стьюдента, который применяется при t-распределении, отличном от нормального.

При линейной однофакторной связи t-критерий можно рассчитать по формуле:

,

где (n - 2) – число степеней свободы при заданном уровне значимости α и объеме выборки n.

Полученное значение t_расч сравнивают с табличным (Приложение №4) значением t-критерия (для α = 0, 05 с n-2 степенями свободы). Если рассчитанное значение t_расч превосходит табличное значение критерия t_табл, то практически невероятно, что найденное значение обусловлено только случайными колебаниями (то есть отклоняется гипотеза о его случайности).

На закладке Корреляция 1 вводим формулу для значений t-критерия кроме исключенных ранее факторов, как показано на рисунке:

Затем копируем формулу для остальных значений t-критерия (аналогично копированию в исходных данных для Y и Х1).

Сравните полученные значения t c t табличным (для данного варианта – 1, 68).

Таким образом исключаются Х4 и Х5.

После проведения корреляционного анализа, в соответствии с пунктами 1- 2 количество факторных признаков, которые будут использоваться в дальнейшей работе может сократиться.

3. Построение множественной регрессии с помощью средств пакета анализа программного продукта Excel.

Для построения множественной регрессии необходимо исключить из исходных данных исключенные факторы. Необходимо в закладке с исходными данными выделить исключаемые столбцы (нажать на названия столбцов мышкой) и удалить их (правой кнопкой на названии столбцов - Удалить)

или захватив оба столбца за шапку левой кнопкой мыши перетащить их правее, и затем удалить пустые столбцы как показано выше.

В результате будет получена таблица исходных данных:

Зайдите в меню Сервис – Анализ данных – Регрессия

Нажмите кнопу справа от ячейки Входной интервал Y, выберите мышкой все значения Y, нажмите Ввод;

Нажмите кнопу справа от ячейки Входной интервал Х, выберите мышкой все значения Х, нажмите Ввод.

Во входных интервалах появились значения как показано на рисунке:

Нажмите ОК

Откроется новый лист следующего вида

Переименуйте полученный лист Регрессия 1 (двойной щелчок на закладке внизу экрана).

4. В целях возможного отсева грубых погрешностей, анализируются отклонения фактических значений зависимой переменной Y от прогноза по регрессии. Если данное отклонение превышает тройное среднеквадратическое отклонение уравнения регрессии, то данное наблюдение необходимо исключить, после чего вновь выполняется построение регрессии.

Среднеквадратическое отклонение необходимо рассчитать по формуле:

,

где n – количество наблюдений,

y – значения результирующего признака в соответствии с исходными данными,

- прогнозное значения результирующего признака.

Исключение наблюдений производится до тех пор, пока фактическое значение переменной Y от прогноза по регрессии не будет превышать тройное среднеквадратическое отклонение по уравнению регрессии.

Дополните лист Регрессия 1 таблицей следующего вида:

Данная таблица предназначена для расчета тройнго среднеквадратического отклонения уравнения регрессии (3 СКВО) и сравнения его с отклонениями фактических значений зависимой переменной Y (Отклонение)

Далее представлены формулы для каждого столбца данной дополнительной таблицы:

Столбец Y факт заполняется из исходных данных согласно показанной ниже формуле. Затем формула копируется вертикально вниз:

Столбец Y прогноз заполняется по формуле Вм = а₀ + а₁х₁ + а₂х₂ + … а_nx_n. Коэффициенты были рассчитаны ранее в результате расчета регрессии и находятся на данном листе выше.

Столбец Отклонение – разница между столбцами Y факт и Y прогноз

Столбец Отклонение в квадрате:

Затем суммируйте Отклонение в квадрате

Рассчитайте среднеквадратичное отклонение:

Рассчитайте тройное среднеквадратичное отклонение

Сравните полученное тройное среднеквадратичное отклонение с отклонениями. В результате отсеивается наблюдение №35 (61> 49, 79):

Удалите из исходных данных наблюдение № 35 и повторите построение регрессии и проверку:

Новый массив исходных данных с удаленным наблюдением № 35 необходимо разместить на новом листе Регрессия 2 (скопировать с листа исходные данные и удалить наблюдение № 35).

Выполнить регрессию на том же листе (Регрессия 2), что и новые исходные данные, для чего при построении регрессии выбрать выходной интервал на данном листе, выбрать Интервал Y и Интервал Х как показано на рисунке:

Будет получена регрессия следующего вида:

Добавьте столбцы между исходными данными и регрессией:

Проведите проверку отклонения фактических значений зависимой переменной Y от прогноза по регрессии как было показано выше (закладка Регрессия 1).

В указанном примере отклонение фактических значений зависимой переменной Y от прогноза по регрессии не превышает тройное среднеквадратическое отклонение. Поэтому переходим к окончательно проверке (п. 5).

5. Окончательная проверка значимости всех факторов проводится в ходе многошагового регрессионного анализа, где проверяется значимость каждого фактора в отдельности при одновременном совершенствовании каждого коэффициента регрессии, путем вычисления отношения:

,

где ak – коэффициент множественной регрессии k-го фактора;

- среднеквадратическое отклонение k-го фактора.

Среднеквадратическое отклонение каждого факторного признака множественной регрессии рассчитывается по формуле:

,

где x - значение факторного признака в соответствии с исходными данными;

- среднеарифметическое значение факторного признака.

Рассчитайте средние значения для факторных признаков по формуле (Лист Регрессия 1). Скопируйте данную формулу для остальных признаков.

Рассчитайте отклонения значения факторного признака в соответствии с исходными данными от среднеарифметического значения факторного признака. При вводе формулы среднее значение необходимо зафиксировать (нажать F4). Скопируйте формулу для всех наблюдений и факторных признаков.

Ниже дополните таблицу таблицей отклонений в квадрате. Скопируйте формулу для всех наблюдений и факторных признаков.

Рассчитайте сумму отклонений.

Рассчитайте среднеквадратическое отклонение k-го фактора. Скопируйте формулу для остальных факторов.

Рассчитайте значение t для каждого фактора по формуле:

Значение рассчитывается для всех факторов, входящих в состав уравнения множественной регрессии, за исключением тех факторов, которые были исключены из уравнения регрессии на предыдущих этапах работы по разным причинам:

· если отношение меньше табличного tα, ν, найденного по таблице t-распределения Стьюдента (Приложение №4) с α = 0, 05 и ν = n-m-1 (n – число наблюдений, m - число объясняющих переменных), то с вероятностью 95% рассматриваемый факторный признак является в уравнении регрессии незначимым с n-m-1 степенями свободы;

· если отношение меньше табличного одновременно для нескольких факторов, то незначимые факторы исключаются из уравнения множественной регрессии поочередно, начиная с того факторного признака у которого отношение минимальное. После исключения каждого факторного признака заново необходимо построить регрессию, поскольку ранее незначимые факторы могут стать значимыми после исключения одного из них;

· процесс исключения повторяется до тех пор, пока для всех факторных признаков будет выполняться неравенство ≥ tα, ν, что свидетельствует о том, что все факторы значимы.

В представленном примере значение t фактора Х6 меньше табличного, в связи с чем фактор Х6 исключается:

Исходные данные копируются на новый лист Регрессия 3

при этом столбец Х6 удаляется.

Проводится проверка как показано выше в данном пункте с вычислением значений .

В данном примере факторы Х1, Х2, Х3 прошли проверку.

6. Окончательная оценка значимости уравнения регрессии в целом производится с учетом статистики F- распределения Фишера:

,

где B – коэффициент детерминации,

n – количество наблюдений,

m – количество учитываемых объясняющих переменных.

Коэффициент детерминации рассчитывается по формуле:

Расчет значения В производится на листе Регрессия 3 аналогично расчету среднеквадратичного отклонения в пункте 2.5 по формуле:

F-распределение Фишера рассчитывается на том же листе по формуле:

Полученное значение F сравнивается с табличным значением F-распределения Фишера (Приложение №4). Если F≥ Fα, то с вероятностью 95 % связь по уравнению регрессии является статистически значимой и нулевая гипотеза отвергается.

При получении статистически значимого уравнения регрессии определяются прогнозируемые значения факторных признаков, представленных в экономико-статистической модели, выявляются резервы роста производительности труда работников организации и их влияние на снижение затрат на производство СМР.

8. ПРИЛОЖЕНИЯ КЛАБОРАТОРНОЙ РАБОТЕ № 6

ПРИЛОЖЕНИЕ 10

Варианты индивидуального задания к работе № 6

* Примечания:

1.Номера вариантов (1-30) предназначены для студентов первой учебной группы, а вариантов (31 - 60) для второй учебной группы.

2.Значение индекса означает рост объёма СМР по сметной стоимости, соответствующего цене заказа (относительно данных базового квартала строительной организации).

ПРИЛОЖЕНИЕ 11

Макет таблицы 11.1

Отчетные данные деятельности строительной организации

за IV кв. 20__г.

Макет таблицы 11.2

Отчетные данные о наличии и рыночной стоимости строительных машин и механизмов по СМУ и в целом по строительной

организации за IV кв.20__г.

Макет таблицы 11.3

Отчетные данные о наличии и использовании основных групп строительных машин и механизмов по строительной организации

за квартал (IV кв. 20__г.)

Макет таблицы 11.4

Отчетные данные о фактическом использовании рабочего

времени машин и механизмов по СМУ и организации за квартал (IV кв. 20__г.)

Макет таблицы 11.5

Данные о целосменных и внутрисменных потерях рабочего

времени по организации за отчетный квартал (IV кв. 20__г.)

Макет таблицы 11.6

Данные о потребности (расходе) основных групп материалов

для производства СМР на планируемый квартал (I кв. 20__г.)

Макет таблицы 11.7

Отчетные данные о остатках строительных материалов на складах организации на начало планируемого квартала (I кв. 20__г.)

ПРИЛОЖЕНИЕ 12

Отчетные данные строительной организации за квартал

(IV кв. 20__г.)

Окончание прил. 13