Всё для Учёбы — студенческий файлообменник
1 монета
docx

Лабораторная № 2 «Множественный корреляционно-регрессионный анализ» по Эконометрике (Горбатков С. А.)

Министерство образования Российской Федерации

Всероссийский заочный финансово-экономический институт филиал в г.Уфе

Кафедра математики и информатики

ОТЧЕТ ПО ЛАБОРАТОРНОЙ РАБОТЕ №2

По дисциплине: "Эконометрика"

Тема: "Множественный корреляционно-регрессионный анализ".

Вариант 1.7

Выполнила: Специальность: Ф и К

Группа:

Преподаватель: Хусаинова З.Ф.

Уфа-2008

Содержание

1. Задание лабораторной работы.3

2. Решение задачи в Олимп: СтатЭксперт5

3. Решение задачи в SPSS.18

4. Решение задачи в MS Excel31

Список использованной литературы.41

1. Задание лабораторной работы

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

x1 - объемом выполненных работ, млн. руб.

x2 - численностью рабочих, чел.

x3 - фондом зарплаты, млн. руб.

Таблица 1.1. Исходные данные

№ Накладные расходы, млн. руб.

Объем работ, млн. руб.

Численность рабочих, чел.

Фонд заработной платы рабочих, млн. руб.

10 3,40

15,00 670,00 5,76

11 4,10 14,70 622,00

6,10 12 4,10 13,30

566,00

6,06 13 3,10 14,60

518,00 4,92 14

2,80 11,70 510,00

4,13

15 2,10 10,60 452,00

4,38 16 2,50 10,00

447,00 4,16 17

2,00

9,00 497,00 4,32

18 2,40 9,50 428,00

4,02 19 2,30 7,00

381,00

3,32 20 2,40 9,10

385,00 3,62 21

2,50 6,80 412,00

3,46

22 2,20 5,50 293,00

2,14 23 1,60 5,10

284,00 2,24 24

3,40

12,20 514,00 3,96

25 2,70 11,00 407,00

3,34 26 3,20 9,30

577,00

3,68 27 2,90 5,90

265,00 2,12 28

4,80 25,90 977,00

10,65

29 3,70 23,50 724,00

6,81 30 4,40 19,80

983,00 9,24 31

3,70

18,80 828,00 8,86

32 4,80 19,10 766,00

7,35 33 3,70 18,80

615,00

5,29 34 3,60 17,40

583,00 5,83 35

4,00 14,10 591,00

6,27

36 3,80 13,80 593,00

5,40 Анализ накладных расходов:

1. Построить уравнение множественной регрессии в линейной форме с полным набором факторов. Отобрать информативные факторы в модель по t-критерию для коэффициентов регрессии.

2. Построить уравнение множественной регрессии только со значимыми факторами. Рассчитать индекс корреляции R и оценить качество полученного уравнения регрессии с помощью коэффициента детерминации R2.

3. Оценить статистическую значимость уравнения регрессии, используя критерий Фишера F (? =0,05) и статистическую значимость параметров регрессии, используя критерий Стьюдента.

4. Дать сравнительную оценку силы связи факторов с результатом с помощью коэффициентов эластичности, ? - и ? - коэффициентов.

5. Рассчитать прогнозное значение результата, если прогнозные значения факторов составляют 70% от их максимальных значений.

2. Решение задачи в Олимп: СтатЭксперт

2.1. Для построения матрицы парной корреляции всех переменных с помощью программы Олимп: СтатЭксперт выполним следующие действия:

1. Инициализируем программу, указываем включение макросов, щелкаем ОК.

2. Вводим исходные данные - результативный признак (у) и факторные признаки (х1, х2, х3).

3. Выделим блок данных (рис.2.1).

Рис.2.1. Блок данных

4. В меню СтатЭкс выбираем функцию Корреляция.

5. Устанавливаем шаблон данных: указываем ориентацию таблицы либо по колонкам (зависит от того, как был осуществлен ввод данных) и наличие наименований таблицы, наблюдений (рис.2.2.). Установить.

Рис.2.2. Диалоговое окно Установка блока данных

6. В окне Корреляционный анализ (рис.2.3.) в список выбранных переменных добавим все показатели. В поле структура отчета отметим необходимые итоги.

7. Кнопка Вычислить.

Рис.2.3. Диалоговое окно Корреляционный анализ

После выполнения этой последовательности действий программа осуществит расчет и построит матрицы парных корреляций, максимальных корреляций, оптимальных лагов, частных корреляций и множественные корреляции. Отчет по вычислениям представлен в табл. 2.1.-2.5.

Таблица 2.1. Матрица парных корреляций

Переменная

Показатель-A

Показатель-B

Показатель-C

Показатель-D

Показатель-A

1,000 0,830 0,835

0,826

Показатель-B

0,830 1,000 0,907

0,910 Показатель-C

0,835

0,907 1,000 0,966

Показатель-D

0,826 0,910 0,966

1,000

Критическое значение на уровне 90% при 2 степенях свободы = +0.2552

Парный коэффициент корреляции является показателем тесноты связи линейной зависимости между переменными. Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная, т.е. накладные расходы, имеет тесную связь с численностью рабочих (ryx2= 0,835), с объемом выполненных работ (ryx1 = 0,830) и с фондом заработной платы (ryx3 = 0,826), т.е. со всеми факторами. Однако эти факторы также тесно связаны между собой, что свидетельствует о наличии мультиколлинеарности.

Таблица 2.2. Матрица максимальных корреляций

Переменная

Показатель-A

Показатель-B

Показатель-C

Показатель-D

Показатель-A

1,000 0,830 0,835

0,826 Показатель-B

0,830

1,000 0,907 0,910

Показатель-C

0,835 0,907 1,000

0,966

Показатель-D

0,826 0,910 0,966

1,000 Таблица 2.3. Матрица оптимальных лагов

Переменная

Показатель-A

Показатель-B

Показатель-C

Показатель-D

Показатель-A

0 0 0 0 Показатель-B

0 0 0 0 Показатель-C

0 0

0 0 Показатель-D

0 0 0 0 Таблица 2.4. Матрица частных корреляций

Переменная

Показатель-A

Показатель-B

Показатель-C

Показатель-D

Показатель-A

1,000 0,290 0,183

0,044 Показатель-B

0,290 1,000 0,194

0,284

Показатель-C

0,183 0,194 1,000

0,782 Показатель-D

0,044

0,284 0,782 1,000

Критическое значение на уровне 90% при 4 степенях свободы = +0.2557

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

Таблица 2.5. Множественные корреляции

Переменная

Коэффициент

F-значение

%точка F-распред.

Показатель-A

0,853 14,709 99,999

Показатель-B

0,924

31,959 100,000 Показатель-C

0,969 85,335 100,000

Показатель-D

0,969

85,143 100,000 Число степеней свободы = 4 и 21

Определение тесноты связи одной случайной величины с совокупностью остальных (m-1) величин, включенных в анализ осуществляется с помощью выборочного коэффициента множественной корреляции. Квадрат коэффициента множественной корреляции принято называть выборочным множественным коэффициентом детерминации.

Проверку значимости коэффициента множественной корреляции осуществим путем сравнения расчетного значения критерия Фишера (3-ий столбец табл.2.5.) с табличным Fтабл = 0,26. То есть коэффициент значим.

Таким образом, выявлена целесообразность построения трехфакторного регрессионного уравнения Y=f(X1, X2, Х3).

2.2. На тех факторах, которые остались после корреляционного анализа построим регрессионную модель. Воспользуемся методом исключения факторов до тех пор пока не получим регрессионную модель со всеми значимыми коэффициентами регрессии.

Выполним следующую последовательность действий:

1. Выделим блок данных.

2. В меню СтатЭкс выбираем функцию Регрессия.

3. Устанавливаем шаблон данных: указываем ориентацию таблицы по колонкам (рис.2.4.). Установить.

4. В окне Регрессионный анализ (рис.2.5.) в список выбранных переменных добавим показатели B, C и D, соответствующих значениям "х".

5. Осуществим выбор зависимой переменной, для этого щелкаем Выбор и выбираем показатель А, соответствующий значениям "у". Установить.

6. Установим вид регрессии - Линейная. Вычислить.

Рис.2.4. Диалоговое окно Установка блока данных

Рис.2.5. Диалоговое окно Регрессионный анализ

После выполнения этой последовательности действий программа осуществит расчет параметров заданной модели, прогнозных значений и построение графиков. Отчет по вычислениям представлен в табл. 2.6.-2.8.

Таблица 2.6. Оценки коэффициентов линейной регрессии

Пере менная

Коэф-т Среднекв

отклоне ние

t- значение

Нижняя

оценка Верх няя

оценка Эластич

ность Бета-

коэф-т

Дельта коэф-т

Св. член 1,176

0,343 3,428 0,812

1,540

0,000 0,000 0,000

Показатель-B

0,063 0,043 1,452

0,017

0,109 0,257 0,328

0,413 Показатель-C

0,002 0,002 0,893

0,000

0,004 0,314 0,388

0,489 Показатель-D

0,038 0,181 0,209

-0,154

0,230 0,060 0,077

0,097 Кpитическое значения t-pаспpеделения пpи 23 степенях свободы (p=85%) = +1.062

Таблица 2.7. Таблица остатков

номер

Факт Расчет

Ошибка абс.

Ошибка относит.

1 3,400

3,559 -0,159 -4,670

2 4,100 3,465 0,635

15,483 3 4,100

3,273

0,827 20,159 4

3,100 3,225 -0,125

-4,041 5 2,800

2,998

-0,198 -7,065 6

2,100 2,832 -0,732

-34,881 7 2,500

2,777

-0,277 -11,078 8

2,000 2,811 -0,811

-40,555 9 2,400

2,706

-0,306 -12,740 10

2,300 2,436 -0,136

-5,902 11 2,400

2,587

-0,187 -7,793 12

2,500 2,485 0,015

0,599 13 2,200

2,137

0,063 2,885 14

1,600 2,099 -0,499

-31,181 15 3,400

3,030

0,370 10,880 16

2,700 2,736 -0,036

-1,343 17 3,200

2,951

0,249 7,781 18

2,900 2,110 0,790

27,237 19 4,800

4,990

-0,190 -3,956 20

3,700 4,233 -0,533

-14,402 21 4,400

4,563

-0,163 -3,694 22

3,700 4,203 -0,503

-13,599 23 4,800

4,052

0,748 15,576 24

3,700 3,681 0,019

0,523 25 3,600

3,555

0,045 1,262 26

4,000 3,377 0,623

15,567 27 3,800

3,329

0,471 12,391 Таблица 2.8. Характеристики остатков

Характеристика

Значение

Среднее значение

0,000 Дисперсия

0,201 Приведенная дисперсия

0,236 Средний модуль остатков

0,360

Относительная ошибка

12,120 Критерий Дарбина-Уотсона

1,487 Коэффициент детерминации

0,982

F - значение ( n1 = 3, n2 = 23)

408,934 Критерий адекватности

74,975 Критерий точности

21,474

Критерий качества

34,849 Уравнение значимо с вероятностью 0.95

Графики остатков изображены на рис.2.6.-2.7.

Рис.2.6. График остатков в абсолютном выражении

Рис.2.7. График остатков в относительном выражении

Оценим значимость всех коэффициентов регрессии с помощью t-критерия Стьюдента: их значения приведены в 4-ом столбце табл. 2.6. протокола Регрессии. Табличное значение t-критерия составило 1,062.

Итак, показатели C (tрасч = 0,893) и D (tрасч = 0,209) незначимы, поэтому исключаем данные факторы. То есть для дальнейшего анализа остался только показатель B, а именно объем выполненных работ, млн. руб.

2.3. Построим новую регрессионную модель из значимых факторов. Для этого проделываем следующие действия:

1. Из исходного блока данных выделяем только y -накладные расходы и х1 - объем выполненных работ.

2. В меню СтатЭкс выбираем функцию Регрессия.

3. Устанавливаем шаблон данных: указываем ориентацию таблицы по колонкам (рис.2.8.). Установить.

4. В окне Регрессионный анализ (рис.2.9.) в список выбранных переменных добавляем только значимый показатель B, соответствующий значениям "х". В качестве зависимой переменной выбираем показатель А, соответствующий значениям "у". Установить.

5. Установим вид регрессии - Линейная. Вычислить.

Рис.2.8. Диалоговое окно Установка блока данных

Рис.2.9. Диалоговое окно Регрессионный анализ

Отчет по вычислениям представлен в табл. 2.9.-2.11.

Таблица 2.9. Оценки коэффициентов линейной регрессии

Переменная

Коэффи циент

Среднекв

отклоне ние

t- значение

Нижняя оценка

Верх няя оценка

Эластич ность

Бета-

коэф-т Дельта

коэф-т Св. член

1,463 0,251 5,828

1,197

1,729 0,000 0,000

0,000 Показатель-B

0,133 0,018 7,453

0,114

0,152 0,542 0,690

1,000 Кpитическое значения t-pаспpеделения пpи 25 степенях свободы (p=85%) = +1.060

Таблица 2.10. Таблица остатков

номер

Факт Расчет

Ошибка абс.

Ошибка относит.

Фактор

X 1 3,400 3,456

-0,056 -1,642 15,000

2 4,100 3,416 0,684

16,684

14,700 3 4,100

3,230 0,870 21,220

13,300 4 3,100

3,403

-0,303 -9,764 14,600

5 2,800 3,017 -0,217

-7,765 11,700 6

2,100

2,871 -0,771 -36,729

10,600 7 2,500

2,792 -0,292 -11,664

10,000

8 2,000 2,659 -0,659

-32,937 9,000 9

2,400 2,725 -0,325

-13,549

9,500 10 2,300

2,393 -0,093 -4,046

7,000 11 2,400

2,672

-0,272 -11,335 9,100

12 2,500 2,366

0,134 5,340 6,800

13 2,200

2,194 0,006 0,282

5,500 14 1,600

2,141 -0,541 -33,791

5,100

15 3,400 3,084

0,316 9,298 12,200

16 2,700 2,924

-0,224

-8,313 11,000 17

3,200 2,699 0,501

15,669 9,300 18

2,900

2,247 0,653 22,520

5,900 19 4,800

4,904 -0,104 -2,163

25,900

20 3,700 4,585

-0,885 -23,919 23,500

21 4,400 4,093

0,307

6,966 19,800 22

3,700 3,961 -0,261

-7,044 18,800 23

4,800

4,000 0,800 16,656

19,100 24 3,700

3,961 -0,261 -7,044

18,800

25 3,600 3,775

-0,175 -4,851 17,400

26 4,000 3,336

0,664

16,593 14,100 27

3,800 3,296 0,504

13,252 13,800 Таблица 2.11. Характеристики остатков

Характеристика

Значение

Среднее значение

0,000 Дисперсия

0,229

Приведенная дисперсия

0,248 Средний модуль остатков

0,403 Относительная ошибка

13,372

Критерий Дарбина-Уотсона

1,743 Коэффициент детерминации

0,979 F - значение ( n1 = 1, n2 = 25)

1166,079

Критерий адекватности

87,384 Критерий точности

16,293 Критерий качества

34,066

Графики остатков изображены на рис.2.10.-2.12.

Рис.2.10. График остатков в абсолютном выражении

Рис.2.11. График остатков в относительном выражении

Рис.2.12. График фактических и расчетных значений линейной модели

Уравнение регрессии зависимости объема выполненных работ и суммы накладных расходов можно записать в виде:

Yр= 1,463+ 0,133X.

Коэффициент регрессии а1=0,133 говорит о том, что при увеличении объема выполненных работ на 1 млн. руб. накладные расходы в среднем увеличатся на 0,133 млн. руб.

2.4. Оценим значимость коэффициентов регрессии с помощью t-критерия Стьюдента: их значения приведены в 4-ом столбце табл. 2.8. протокола Регрессии. Табличное значение t-критерия составило 1,060.

Итак, показатели В (tрасч = 7,453), а именно объем выполненных работ, млн. руб. значим. То есть дальше продолжать анализ данных не имеет смысла.

Рассмотрим подробнее табл. 2.11., в которой содержится характеристика остатков линейной модели. Значение дисперсии остатков:

0,229.

Средняя относительная ошибка аппроксимации:

11,372. Коэффициент детерминации:

0,979. То есть изменение величины накладных расходов Y на 97,9% объясняется по полученному уравнению изменением объема выполненных работ X1.

Значение F-критерия Фишера:

F= 1166,079. F= 1166,079 > Fтабл= 4,24 следовательно, уравнение модели является значимым, зависимая переменная Y достаточно хорошо описывается включенной в модель переменной X1.

Таким образом, уравнение регрессии следует признать адекватным, модель считается значимой.

3. Решение задачи в SPSS

3.1. Имеется файл данных в формате EXCEL (Рис.3.1). Импортируем данные в файл SPSS.

Рис.3.1. Файл исходных данных в формате EXCEL

1) Для импорта данных из файла формата EXCEL в SPSS выполняем следующие действия:

* После запуска программы SPSS и появления на экране пустого файла SPSS, выбираем в строке меню Файл-Открыть-Данные и задаем имя EXCEL файла с вашими данными, его тип, после чего нажимаем кнопку OPEN.

* В новом диалоговом окне задаем диапазон ячеек А2:D28. Щелкаем по кнопке ОК.

2) На экране вы увидите импортированные данные в формате SPSS (Рис. 3.2).

Рис.3.2. Данные задачи представлены в формате SPSS

3) Отредактируем данные с использования пакета SPSS. Для этого нажмем в нижней строке меню кнопку "Переменные". Это позволит просмотреть всю информацию о переменных, импортированных в SPSS файл, и внести нужные изменения и дополнения. В столбце "Метка" введем расширенное имя переменной (до 256 символов). Эта информация будет использована при создании отчетов (Рис. 3.3).

Рис.3.3. Редактирование данных SPSS

4) Для построения матрицы парной корреляции всех переменных с помощью пакета SPSS выполним следующие действия:

* Выбираем в верхней строке меню Анализ-Корреляция-Парные.

* Переменные, относительно которых проверяется степень корреляционной связи, поочередно перемещаем в поле тестируемых переменных справа (Рис.3.4).

* Начинаем расчет путем нажатия ОК.

Рис. 3.4. Построение матрицы парной корреляции в SPSS

В результате в выходной области появиться матрица парной корреляции всех переменных (Таблица 3.1). Полученные результаты содержат: коэффициент корреляции Пирсона, вероятность ошибки, соответствующая предположению о ненулевой корреляции, и количество использованных пар значений n=27.

Коэффициент корреляции R между двумя переменными указывает на силу связи между ними и принимает значения между -1 и +1. При этом, если значение находится ближе к 1, то это означает наличие сильной связи, а если ближе к 0, то слабой.

Таблица 3.1. Корреляции

Накладные расходы. млн. руб.

Объем работ, млн. руб.

Числен ность рабочих, чел.

Фонд зарплаты, млн. руб.

Накладные расходы. млн. руб.

Корреляция Пирсона

1 ,830(**) ,835(**)

,826(**)

Знч.(2-сторон)

,000 ,000 ,000

N 27 27 27

27 объем выполненных работ, млн. руб.

Корреляция Пирсона

,830(**) 1 ,907(**)

,910(**) Знч.(2-сторон)

,000

,000 ,000

N 27 27 27 27

численность рабочих, чел.

Корреляция Пирсона

,835(**) ,907(**)

1 ,966(**) Знч.(2-сторон)

,000 ,000 ,000

N

27 27 27 27 фонд зарплаты, млн. руб.

Корреляция Пирсона

,826(**) ,910(**)

,966(**)

1 Знч.(2-сторон)

,000 ,000 ,000

N 27 27 27

27 ** Корреляция значима на уровне 0.01 (2-сторон.)

Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная, т.е. накладные расходы, имеет тесную связь с численностью рабочих (ryx2= 0.835), с объемом выполненных работ (ryx1 = 0830) и с фондом заработной платы (ryx3 = 0.826), т.е. со всеми факторами. Однако эти факторы также тесно связаны между собой, что свидетельствует о наличии мультиколлинеарности.

Таким образом, на основе анализа матрицы коэффициентов парной корреляции делаем вывод о целесообразности построения трехфакторного регрессионного уравнения Y=f(X1, X2, Х3).

3.2. Построение линейного уравнения регрессии.

Оценка параметров регрессии осуществляется по методу наименьших квадратов. Для проведения регрессионного анализа с помощью пакета SPSS выполним следующие действия:

* Выберем в верхней строке меню Анализ - Регрессия - Линейная.

* Поместим переменную Y в поле для зависимых переменных, объявив переменные X1, X2, X3 независимыми (Рис. 3.5).

Рис. 3.5. Диалоговое окно Линейная регрессия

* Используем установленный по умолчанию в поле "Метод" Исключение.

* В полях панели Статистики следует отметим флажками Оценки, Согласие модели и критерий Дурбина-Уотсона (Рис. 3.6), затем нажимаем Продолжить.

Рис. 3.6. Диалоговое окно Линейная регрессия: Статистики.

* В полях панели Сохранить отметим необходимые поля (Рис. 3.7) и нажимаем Продолжить.

* Установленные по умолчанию в полях панели Параметры значения не меняем. Они будут использованы для определения критерия Фишера с доверительной вероятностью 0,95.

Рис.3.7. Диалоговое окно Линейная регрессия: Сохранить

* Начнем вычисления нажатием ОК.

Результаты регрессионного анализа приведены в следующих таблицах.

При последовательном подборе переменных в SPSS предусмотрена автоматизация, основанная на значимости включения и исключения переменных.

В табл. 3.2 перечислены переменные, которые были последовательно исключены на каждом шаге.

Таблица 3.2. Включенные/исключенные переменные

Модель Включенные переменные

Исключенные переменные

Метод 1 фонд зарплаты, млн. руб., объем выполненных работ, млн. руб., численность рабочих, чел.(a)

. Принудительное включение

2 . фонд зарплаты, млн. руб.

Исключение (критерий: вероятность F-исключения >= ,100).

3 . объем выполненных работ, млн. руб.

Исключение (критерий: вероятность F-исключения >= ,100).

a Включены все запрошенные переменные

b Зависимая переменная: Накладные расходы. млн. руб.

В табл. 3.3 приведены значения коэффициента детерминации, коэффициента множественной корреляции, стандартная ошибка, коэффициент Дарбина-Уотсона последовательно для всех моделей. В последней строке приводятся данные для окончательной модели.

Таблица 3.3. Сводка для модели

Модель R

R квадрат

Скорректированный R квадрат

Стд. ошибка оценки

Дурбин-Уотсон

1 ,853(a) ,728

,692 ,48597 2

,853(b)

,727 ,705 ,47619

3 ,835(c) ,697

,685 ,49167 1,352

a Предикторы: (константа) фонд зарплаты, млн. руб., объем выполненных работ, млн. руб., численность рабочих, чел.

b Предикторы: (константа) объем выполненных работ, млн. руб., численность рабочих, чел.

c Предикторы: (константа) численность рабочих, чел.

d Зависимая переменная: Накладные расходы. млн. руб.

В табл. 3.4 приведены результаты дисперсионного анализа и значения F-критерия, полученные на каждом шаге.

Таблица 3.4. Дисперсионный анализ

Модель

Сумма квадратов

ст.св. Средний квадрат

F Знч. 1 Регрессия

14,527

3 4,842 20,504

,000(a) Остаток

5,432 23 ,236

Итого

19,959 26

2 Регрессия

14,516 2 7,258

32,009

,000(b) Остаток

5,442 24 ,227

Итого

19,959

26 3 Регрессия

13,915 1 13,915

57,561 ,000(c)

Остаток

6,044 25 ,242

Итого

19,959 26

a Предикторы: (константа) фонд зарплаты, млн. руб., объем выполненных работ, млн. руб., численность рабочих, чел.

b Предикторы: (константа) объем выполненных работ, млн. руб., численность рабочих, чел.

c Предикторы: (константа) численность рабочих, чел.

d Зависимая переменная: Накладные расходы. млн. руб.

В табл. 3.5 в первом столбце указан номер модели, во втором - перечисляются используемые в модели независимые переменные, а в третьем столбце содержаться коэффициенты уравнения регрессии. В четвертом столбце содержаться стандартные ошибки коэффициентов уравнения регрессии, в пятом - стандартизованные коэффициенты, а в шестом - t-статистика, используемая для проверки значимости коэффициентов уравнения регрессии. Таблица 3.6. показывает исключенные переменные и их коллинеарность.

Таблица 3.5. Коэффициенты (a)

Модель

Нестандартизован ные коэффициенты

Стандартизованные коэффициенты

t Знч.

B Стд. ошибка

Бета 1

(Константа)

1,176 ,343 3,428

,002

объем выполненных работ, млн. руб.

,063 ,043 ,394

1,452 ,160 численность рабочих, чел.

,002

,002 ,388 ,893

,381 фонд зарплаты, млн. руб.

,038 ,181 ,092

,209

,836 2 (Константа)

1,142 ,298 3,836

,001 объем выполненных работ, млн. руб.

,066

,040 ,412 1,629

,116 численность рабочих, чел.

,002 ,001 ,461

1,822

,081 3 (Константа)

1,035 ,300 3,452

,002 численность рабочих, чел.

,004

,001 ,835 7,587

,000 a Зависимая переменная: Накладные расходы. млн. руб.

Таблица 3.6. Исключенные переменные(c)

Модель

Бета включения

t Знч. Частная корреляция

Статистики коллинеарности

Толерантность

2 фонд зарплаты, млн. руб.

,092(a) ,209 ,836

,044 ,061 3 фонд зарплаты, млн. руб.

,291(b)

,679 ,504 ,137

,067 объем выполненных работ, млн. руб.

,412(b) 1,629 ,116

,315

,177 a Предикторы в модели: (конст) объем выполненных работ, млн. руб., численность рабочих, чел.

b Предикторы в модели: (конст) численность рабочих, чел.

c Зависимая переменная: Накладные расходы. млн. руб.

В таблице 3.7. приведены вычисленные (предсказанные) по модели значения зависимой переменной Y и значения остаточной компоненты.

Таблица 3.7. Статистики остатков(a)

Минимум

Максимум

Среднее Стд. отклонение

N Предсказанное значение

2,0717 4,8817 3,1926

,73157 27 Остаток

-,97967 ,85030 ,00000

,48212

27 Стандартиз. предсказанное значение

-1,532 2,309 ,000

1,000 27 Стандартиз. остаток

-1,993

1,729 ,000 ,981

27 a Зависимая переменная: Накладные расходы. млн. руб.

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

y = 1,035+ 0,004х2.

Итак, коэффициенты уравнения регрессии показывают, что при увеличении численности рабочих на 1 человека накладные расходы увеличатся на млн. руб.

3.3. Оценим качество построенной модели.

Оценка качества уравнения регрессии производится с помощью коэффициентов детерминации R и множественной корреляции R. Их значения можно найти в табл. 3.3 Сводка для модели.

Коэффициент детерминации R= 0,697 показывает долю вариации результативного признака под воздействием изучаемых факторов. Следовательно, около 70% вариации зависимой переменной учтено в модели и обусловлено влиянием включенных факторов.

Коэффициент множественной корреляции R = 0,835 показывает тесноту связи зависимой переменной Y со всеми включенными в модель объясняющими факторами.

Проверку значимости уравнения регрессии произведем на основе F-критерия Фишера. Значение критерия Фишера F = 57,561 можно найти в табл. 3.4 Дисперсионный анализ.

Вероятность ошибки , соответствующая расчетному значению F-критерия, выводится в правой колонке под заголовком "Значимость". Ее величина свидетельствует о значимости уравнения регрессии (р < 0,001).

Таким образом, уравнение регрессии следует признать адекватным, модель считается значимой.

3.4. Построим прогноз. Для этого вначале вводим прогнозные значения выбранных факторов Х1=18,13, Х2=688,10 и Х3= 7,46 (рис. 3.8.).

Рис. 3.8. Введены прогнозные значения объясняющих факторов

Затем запускаем процедуру Анализ - Регрессия - Линейная и в качестве независимых переменных указываем объем выполненных работ, численность рабочих и фонд заработной платы (рис. 3.9).

Рис. 3.9. Диалоговое окно Линейная регрессия.

В полях панели Сохранить отмечаем необходимость сохранения Интервалов предсказания для отдельных значений с вероятностью 95% (Рис. 3.10.) и нажимаем Продолжить.

Рис. 3.10. Диалоговое окно Линейная регрессия: Сохранить

На рис. 3.11. приведены результаты прогнозирования по модели регрессии: точечный прогноз, верхняя и нижняя границы.

Рис.3.11. Результаты прогнозирования

Итак, с вероятностью 95% накладные расходы в прогнозируемом месяце составят от 2,68617 млн. руб. до 4,76892 млн. руб.

4. Решение задачи в Excel

4.1. Для отбора информативных факторов в модель воспользуемся инструментом Корреляция в Excel 2007. Для проведения корреляционного анализа выполняем следующие действия:

1. Данные для корреляционного анализа должны располагаться в смежных диапазонах ячеек.

2. Выбираем команду Сервис-Анализ данных.

3. В диалоговом окне Анализ данных выбираем инструмент Корреляция (рис.4.1), а затем щелкаем на кнопке ОК.

4. В диалоговом окне Корреляция (рис.4.2) в поле Входной интервал необходимо ввести диапазон ячеек, содержащих исходные данные. Так как выделены заголовки столбцов, то установим флажок Метки в первой строке.

5. Выберем параметры вывода. В данном примере Новый рабочий лист и нажимаем ОК.

Рис.4.1. Выбор инструмента Корреляция

Рис.4.2. Диалоговое окно Корреляция подготовлено к выполнению анализа данных

В результате корреляционного анализа получаем матрицу (табл. 4.1).

Таблица 4.1. Корреляция

Накладные расходы

Объем работ

Численность рабочих

Фонд заработной платы

Накладные расходы

1,000 Объем работ

0,830 1,000

Численность рабочих

0,835

0,907 1,000 Фонд заработной платы

0,826 0,910 0,966

1,000 Анализ матрицы коэффициентов парной корреляции показывает, что накладные расходы имеют тесную связь с численностью рабочих (ryx2=0,835), с фондом заработной платы (ryx3=0,826) и с объемом работ (ryx2=0,830). Однако эти факторы также тесно связаны между собой, что свидетельствует о наличии мультиколлинеарности.

4.2. С помощью инструмента Регрессия (Анализ данных в Excel 2007) построим уравнение множественной регрессии в линейной форме с полным набором факторов.

1. Выбираем команду Сервис/Анализ данных/Регрессия.

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

3. Так как выделены и заголовки столбцов, то установим флажок Метки.

4. Выберем параметры ввода: Новый рабочий лист.

5. В поле Остатки поставим необходимый флажки.

6. Нажимаем ОК.

Рис.4.3. Диалоговое окно Регрессия подготовлено к выполнению анализа данных

Результат регрессионного анализа содержится в таблицах 4.2. - 4.4.:

Таблица 4.2. Регрессионная статистика

Множественный R

0,853173 R-квадрат

0,727905 Нормированный R-квадрат

0,692414

Стандартная ошибка

0,485915 Наблюдения

27 Таблица 4.3. Дисперсионный анализ

Дисперсионный анализ

df SS MS F Значимость F

Регрессия

3,000 14,528 4,843

20,510

0,000 Остаток

23,000 5,431 0,236

Итого 26,000

19,959

Таблица 4.4.

Коэффициенты

Стандартная ошибка

t-статистика

P-Значение

Нижние 95%

Верхние 95%

Нижние 95,0%

Верхние 95,0%

Y-пересе чение

1,178 0,343 3,434

0,002

0,468 1,887 0,468

1,887 Объем работ

0,063 0,043 1,448

0,161

-0,027 0,153 -0,027

0,153 Численность рабо

чих 0,002 0,002

0,884

0,386 -0,002 0,006

-0,002 0,006 Фонд заработной платы

0,040 0,181 0,221

0,827

-0,334 0,414 -0,334

0,414 Уравнение регрессии выглядит следующим образом:

y= 1,178+ 0,063x1+ 0,002x2+0,040x3.

В табл. 4.5. приведены вычисленные (предсказанные) по модели значения зависимой переменной Y и значения остаточной компоненты е1.

Оценим значимость всех коэффициентов регрессии с помощью t-критерия Стьюдента: их значения приведены в 4-ом столбце табл. 4.4. протокола Регрессии.

Табличное значение t-критерия Стьюдента можно найти с помощью функции СТЬЮДРАСПОБР. Табличное значение t-критерия составляет 1,062.

Итак, численность рабочих (tХ2 = 0,884) и фонд заработной платы (tХ3 = 0,221) незначимы, поэтому исключаем данные факторы. То есть для дальнейшего анализа остался только фактор х1, а именно объем выполненных работ, млн. руб.

Таблица 4.5. Вывод остатка

Наблюдение

Предсказанное

Накладные расходы

Остатки

1,000 3,557 -0,157

2,000 3,466 0,634

3,000 3,275 0,825

4,000

3,225 -0,125 5,000

2,997 -0,197 6,000

2,833 -0,733 7,000

2,778

-0,278 8,000 2,811

-0,811 9,000 2,706

-0,306 10,000 2,436

-0,136

11,000 2,588 -0,188

12,000 2,485 0,015

13,000 2,136 0,064

14,000

2,099 -0,499 15,000

3,029 0,371 16,000

2,736 -0,036 17,000

2,948

0,252 18,000 2,110

0,790 19,000 4,991

-0,191 20,000 4,232

-0,532

21,000 4,562 -0,162

22,000 4,205 -0,505

23,000 4,052 0,748

24,000

3,679 0,021 25,000

3,555 0,045 26,000

3,379 0,621 27,000

3,329

0,471 4.3. С помощью инструмента Регрессия (Анализ данных в Excel) построим уравнение множественной регрессии только со значимыми факторами. Результат регрессионного анализа содержится в таблицах 4.6.-4.12.:

Таблица 4.9. Регрессионная статистика

Множественный R

0,830419

R-квадрат

0,689595 Нормированный R-квадрат

0,677179 Стандартная ошибка

0,497804

Наблюдения

27 Таблица 4.10. Дисперсионный анализ

df SS MS F

Значимость F

Регрессия

1 13,76329 13,76329

55,53992 8,35E-08

Остаток

25 6,195225 0,247809

Итого 26

19,95852

Таблица 4.11.

Коэффициенты

Стандартная ошибка

t-статистика

P-Значение

Нижние 95%

Верх ние 95%

Нижние 95,0%

Верх ние 95,0%

Y-пере сечение

1,463 0,251 5,828

0,000 0,946 1,980

0,946

1,980 Объем работ

0,133 0,018 7,453

0,000 0,096 0,170

0,096

0,170 Итак, уравнение регрессии зависимости объема выполненных работ и суммы накладных расходов можно записать в виде:

Yр= 1,463+ 0,133X.

Коэффициент регрессии а1=0,133 говорит о том, что при увеличении объема выполненных работ на 1 млн. руб. накладные расходы в среднем увеличатся на 0,133 млн. руб.

4.4. Оценим значимость коэффициентов регрессии с помощью t-критерия Стьюдента: их значения приведены в 4-ом столбце табл. 4.11. протокола Регрессии. Табличное значение t-критерия составило 1,060.

Таким образом, фактор "объем выполненных работ" (tХ2 = 7,453) значим. Все не значимые факторы исключены. Рассматриваемые предприятия находятся в процессе выполнения работ работниками, когда еще фонд заработной платы не сформулирован.

В табл. 4.12. приведены вычисленные (предсказанные) по модели значения зависимой переменной Y и значения остаточной компоненты е1.

Таблица 4.12. Вывод остатка

Наблюдение

Предсказанное Накладные расходы

Остатки 1

3,456

-0,056 2 3,416

0,684 3 3,230 0,870

4 3,403 -0,303

5 3,017

-0,217 6 2,871

-0,771 7 2,792

-0,292 8 2,659

-0,659

9 2,725 -0,325

10 2,393 -0,093

11 2,672 -0,272

12 2,366

0,134 13 2,194

0,006 14 2,141

-0,541 15 3,084

0,316

16 2,924 -0,224

17 2,699 0,501

18 2,247 0,653

19 4,904

-0,104 20 4,585

-0,885 21 4,093

0,307 22 3,961

-0,261

23 4,000 0,800

24 3,961 -0,261

25 3,775 -0,175

26 3,336

0,664 27 3,296

0,504 График остатков изображен на рис.4.4.

Рис. 4.4. График остатков

Рассмотрим коэффициент детерминации (R-квадрат) и индекс корреляции (множественный R) , их значения содержатся в табл. 4.9. вывода итогов Регрессии:

R2 = 0,689595.

R = 0,830419. Таким образом, изменение величины накладных расходов Y почти на 70% объясняется по полученному уравнению изменением объема выполненных работ X1.

Проверку значимости уравнения регрессии произведем на основе вычисления F-критерия Фишера. Значение F-критерия Фишера можно найти в табл. 4.10. протокола Регрессии.

F = 55,53992. Табличное значение F-критерия можно найти с помощью функции FРАСПОБР (рис. 4.5.). Табличное значение при доверительной вероятности 0,95 при k1=p=1, k2=n-p-1=25 составляет Fкр=4,24.

Рис.4.5. Определение табличного значения F-критерия Фишера

F=55,54>Fтабл=4,24 следовательно, уравнение модели является значимым, зависимая переменная Y достаточно хорошо описывается включенной в модель переменной X1.

Таким образом, уравнение регрессии следует признать адекватным, модель считается значимой.

4.5. Проанализируем влияние факторов на зависимую переменную по модели. Используем коэффициент эластичности (Э) и ?-коэффициент. Произведем необходимые расчеты (табл.4.13.).

Таблица 4.13.

№ Накладные расходы

Объем работ

y-yср (y-yср)2

x-xср

(x-xср)2 (y-yср)*(x-xср)

Y X1

1 3,40 15,00

0,21

0,04 1,98 3,93

0,41 2 4,10 14,70

0,91 0,82 1,68

2,83

1,53 3 4,10 13,30

0,91 0,82 0,28

0,08 0,26 4 3,10

14,60

-0,09 0,01 1,58

2,50 -0,15 5 2,80

11,70 -0,39 0,15

-1,32

1,74 0,52 6 2,10

10,60 -1,09 1,19

-2,42 5,85 2,64

7 2,50

10,00 -0,69 0,48

-3,02 9,11 2,09

8 2,00 9,00 -1,19

1,42

-4,02 16,15 4,79

9 2,40 9,50 -0,79

0,63 -3,52 12,38

2,79

10 2,30 7,00 -0,89

0,80 -6,02 36,22

5,37 11 2,40 9,10

-0,79

0,63 -3,92 15,35

3,11 12 2,50 6,80

-0,69 0,48 -6,22

38,67

4,31 13 2,20 5,50

-0,99 0,99 -7,52

56,53 7,46 14 1,60

5,10

-1,59 2,54 -7,92

62,70 12,61 15

3,40 12,20 0,21

0,04

-0,82 0,67 -0,17

16 2,70 11,00 -0,49

0,24 -2,02 4,07

0,99

17 3,20 9,30 0,01

0,00 -3,72 13,83

-0,03 18 2,90 5,90

-0,29

0,09 -7,12 50,67

2,08 19 4,80 25,90

1,61 2,58 12,88

165,93

20,71 20 3,70 23,50

0,51 0,26 10,48

109,86 5,32 21

4,40

19,80 1,21 1,46

6,78 45,99 8,19

22 3,70 18,80 0,51

0,26

5,78 33,43 2,93

23 4,80 19,10 1,61

2,58 6,08 36,98

9,78

24 3,70 18,80 0,51

0,26 5,78 33,43

2,93 25 3,60 17,40

0,41

0,17 4,38 19,20

1,79 26 4,00 14,10

0,81 0,65 1,08

1,17

0,87 27 3,80 13,80

0,61 0,37 0,78

0,61 0,47 ? 86,20

351,50

19,96 779,88

103,60 ср. 3,19

13,02

Воспользуемся следующими формулами:

, Э1(для X1)=0,133*11,86/3,19=0,542.

?j=, где Sxj -среднеквадратическое отклонение фактора j.

5,477; 0,876;

?1=0,133*5,477/0,876=0,831.

При изменении объема работ на 1% накладные расходы изменятся на 54,2%. При увеличении объема работ на 5,477 млн. руб. накладные расходы увеличатся на 0,728 млн. руб. (0,831*0,876).

4.6. Осуществим прогнозирование среднего значения показателя Х, если прогнозное значение факторов составит 70% от его максимального значения.

Прогнозное значение переменной Х1: X1*= 0,7*maxX= 18,13.

По уравнению модели рассчитаем Yр*= 1,463+ 0,133*18,13= 3,8743.

Представим графически фактические и модельные значения Y, точки прогноза (рис.4.6).

Рис.4.6. График линейной модели и результатов прогноза

Список использованной литературы

1. Эконометрика: Учебник / Под ред. И.И.Елисеевой. - 2-е изд.; перераб. и доп. - М.: Финансы и статистика, 2005. - 576с.

2. Орлова И.В. Экономико-математическое моделирование. Практическое пособие по решению задач - М.: ВЗФЭИ. Вузовский учебник, 2004. - 144с.

3. Орлова И.В. Экономико-математические методы и модели. Выполнение расчетов в среде Excel. Практикум: Учебное пособие для вузов. - М.: Финстатинформ, 2000. - 136с.

4. Практикум по эконометрике: Учеб. пособие/Под ред. И.И.Елисеевой - М.: Финансы и статистика, 2001.

5. Эконометрика. Методические указания по изучению дисциплины и выполнению контрольной работы и аудиторной работы на ПЭВМ. - М.: Вузовский учебник, 2005. -122с.

6. Задания для выполнения контрольной и лабораторной работ (дополнение к изд. номеру 1/5-05). - М.: ВЗФЭИ, 2006. - 39с.

7

Показать полностью…
Похожие документы в приложении