Иванов А.Н. Построение эконометрических моделей и прогнозирование в MS Excel: сборник лабораторных работ

Подождите немного. Документ загружается.

Тема лабораторной работы: Построение и анализ качества модели парной линейной регрессии.

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

Стандартная ошибка точечного прогноза.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

10из16

=«стандартная ошибка регрессии»*

КОРЕНЬ(1/n+СТЕПЕНЬ((«округленное значение фактора» – «x-сред-

нее»);2)/СУММПРОИЗВ((«диапазон значений x» – «x-среднее в фиксиро-

ванной ячейке»);(«диапазон значений x» – «x-среднее в фиксированной

ячейке»)))

0,044стандартная ошибка точечного прогноза =

14. Осуществим интервальный прогноз при значении фактора, равного

120 % от среднего числа работников.

14.1. Доверительный интервал для истинного точечного прогноза есть

интервал вида

()

(

)

(

)

()

txyxytxy

ˆˆˆˆˆ

αα

−−

− .

=«y-прогнозное» – «t-квантиль»*«стандартная ошибка точечного

прогноза y-прогнозное»

=«y-прогнозное» + «t-квантиль»*«стандартная ошибка точечного

прогноза y-прогнозное»

1,53397 1,75097<=истинное значение точечного прогноза<=

1. Что означает точечный прогноз по уравнению регрессии?

2. Будет ли доверительный интервал, накрывающий истинное значение

точечного прогноза, накрывать оценку точечного прогноза?

3. Получены интервальные оценки параметров парной линейной регрес-

сионной модели. Будут ли они содержать оценки этих параметров?

4. Каково качество построенной модели и по какому показателю его надо

определять?

5. Равносильны

ли гипотезы о статистической значимости коэффициента

регрессии

a и о наличии регрессионной зависимости? Ответ обосновать.

6. Что позволяет определить функция СТЬЮДРАСПОБР ?

7. Для чего необходима функция СУММПРОИЗВ ?

8. Всегда ли графически изображается прямая для модели парной линей-

ной регрессии?

9. Предпринимателю необходимо ответить на вопрос: в каком случае це-

лесообразно принимать на работу нового работника?

Тема лабораторной работы: Построение и анализ качества модели парной линейной регрессии.

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

Стандартная ошибка точечного прогноза.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

11из16

1. Предположим, что

исходные данные описываются моделью парной ли-

нейной регрессии зависимости ВВП от объёма продаж. Оцените параметры

этой модели и определите показатели качества модели (коэффициент детер-

минации, среднюю ошибку аппроксимации).

78 82 87 79 89 106 67 88 73 87

133 148 134 154 162 195 139 158 152 162

ВВП, млн руб.

Объём продаж, т

2. Предположим, что исходные данные описываются моделью парной ли-

нейной регрессии зависимости производительности труда от заработной пла-

ты. Оцените параметры этой модели, определите показатели качества модели

(коэффициент детерминации, среднюю ошибку аппроксимации).

1851 1965 2065 1723 1522 1525 1239 1240 1156 1158

1811050 1053 832 8331723 1721 1832 1180

Производительность

труда, шт.

ЗП, тыс. руб.

879

3. Предположим, что исходные данные описываются моделью парной ли-

нейной регрессии зависимости спроса от предложения. Оцените параметры

этой модели, определите показатели качества модели (коэффициент детер-

минации, среднюю ошибку аппроксимации).

1,82,83,54,65,36,17,17,6 8 9,3

2,6 3,9 5,3 6,5 7,3 8 8,5 8,3 8,6 9,2

Спрос, шт.

Предложение, шт.

4. Предположим, что исходные данные описываются моделью парной ли-

нейной регрессии зависимости производительности труда от ВНП. Оцените

параметры этой модели, определите показатели качества модели (коэффици-

ент детерминации, среднюю ошибку аппроксимации).

0,9 1,6 2,4 3,3 4 4,4 5 5,5 6,3 7,1

6,8 8,1

Производительность

труда, шт.

ВНП, млн руб.

0,91,72,43,23,84,35,15,8

5. Предположим, что исходные данные описываются моделью парной ли-

нейной регрессии зависимости спроса от цены. Оцените параметры этой мо-

дели, определите показатели качества модели (коэффициент детерминации,

среднюю ошибку аппроксимации).

3,1 4,3 5,4 6,6 8,4 9 9,8 9,9 10,3 11,5

2,44,15,26,27,17,98,78,38,69,7

Спрос, шт.

Цена, тыс. руб.

Тема лабораторной работы: Построение и анализ качества модели парной линейной регрессии.

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

Стандартная ошибка точечного прогноза.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

12из16

6. Предположим, что исходные данные описываются моделью парной ли-

нейной регрессии зависимости производительности от условий труда. Оце-

ните параметры этой модели, определите показатели качества модели (коэф-

фициент детерминации, среднюю ошибку аппроксимации).

26,1 25,8 0,3 20,2 139 69,3 6,7 14 14 11

Производительность,

шт.

Условия труда (коэф.)

29,4 26,7 14 72,7 14 126 68,3 23,6 15

7. Предположим, что исходные данные описываются моделью парной ли-

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

параметры этой модели, определите показатели качества модели (коэффици-

ент детерминации, среднюю ошибку аппроксимации).

91,5 92,8 104 102 97,9 98,7 101 104 105 102

96,6 97,1 98,5 106103 107 92,5 110

Доход на душу

населения, тыс. руб.

Спрос, шт.

79,5 100

8. Предположим, что исходные данные описываются моделью парной ли-

нейной регрессии зависимости производительности труда от возраста трудя-

щегося. Оцените параметры этой модели, определите показатели качества

модели (коэффициент детерминации, среднюю ошибку аппроксимации).

102 106 96 92 100 108 80 96 106 97

31 36 28 40 30 34 55 23 28 43

Произв. труда, шт.

Возраст, г.

9. Предположим, что исходные данные описываются моделью парной ли-

нейной регрессии зависимости товарооборота от дохода населения. Оцените

параметры этой модели, определите показатели качества модели (коэффици-

ент детерминации, среднюю ошибку аппроксимации).

63 107 103 10881 107 69 122

80 96 106 9796 92 100 108102 106

104 96

Товарооборот,

млн руб.

Доходы населения,

тыс. руб.

10. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости прибыли предприятия от мировых цен на

энергоносители. Оцените параметры этой модели, определите показатели ка-

чества модели (коэффициент детерминации, среднюю ошибку аппроксима-

ции).

Тема лабораторной работы: Построение и анализ качества модели парной линейной регрессии.

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

Стандартная ошибка точечного прогноза.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

13из16

156 110 105 102

77 69

Прибыль предпр.,

млн руб.

Цена на энергоносит.,

тыс. руб.

101 120 158 196 180 190

96 103 95 9370 98 97 99

11. Предположим, что исходные данные описываются моделью парной

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

Оцените параметры этой модели, определите показатели качества модели

(коэффициент детерминации, среднюю ошибку аппроксимации).

7,8 5,6 2,2 17,7 215 75,1 35,6 23 15 6

31,9 29,3 2,6 7,9 230 103 15,6 11,2 10,3 2,8

Экспорт, млн руб.

Производит. труда, шт.

12. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости выпуска продукции от оснащённости про-

изводства. Оцените параметры этой модели, определите показатели качества

модели (коэффициент детерминации, среднюю ошибку аппроксимации).

6,3 7,12,4 3,3 4 4,4

Выпуск продукции,

тыс. руб.

Оснащ-ть произ-ва,

млн руб.

0,9 1,7

0,9 1,6

2,43,23,84,35,15,86,88,1

55,5

13. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости импорта от производительности труда.

Оцените параметры этой модели, определите показатели качества модели

(коэффициент детерминации, среднюю ошибку аппроксимации).

21 49 13 45 28 34 45 26 24 41

Производительность

труда, шт.

Импорт, млн руб.

20 49 13 44 26 33 22 4044 23

14. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости численности занятых от товарооборота.

Оцените параметры этой модели, определите показатели качества модели

(коэффициент детерминации, среднюю ошибку аппроксимации).

106214913462834452124

21 2446 28 34 46 43

Численность, чел.

Товарооборот,

млн руб.

21 50 13

15. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости занятых от оснащённости производства.

Тема лабораторной работы: Построение и анализ качества модели парной линейной регрессии.

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

Стандартная ошибка точечного прогноза.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

14из16

Оцените параметры этой модели, определите показатели качества модели

(коэффициент детерминации, среднюю ошибку аппроксимации).

44 41 34 28 146 71 18 16 12 62

Занятые, чел.

Оснащ-ть произ-ва,

млн руб.

130 60 9 715 12 14 28 15

16. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости прибыли предприятия от оснащённости

производства. Оцените параметры этой модели, определите показатели каче-

ства модели (коэффициент детерминации, среднюю ошибку аппроксимации).

6,5 6,3

Прибыль, млн руб.

Оснащ-ть произ-ва,

млн руб.

43,8

19,2 16,3 12,7 12,6 12,5 11,5 8,9 7,2

7,67,45,44,411,7 9,9 7,7 7,5

17. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости миграций от дохода населения. Оцените

параметры этой модели, определите показатели качества модели (коэффици-

ент детерминации, среднюю ошибку аппроксимации).

1548 470 2068 3181 386 1202 228 1111 247 145

933 633 1635 1276 229 183 145 249 89 70

Миграции, чел.

Доход, тыс. руб.

18. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости уровня инвестиционных вложений от ВНП

страны. Оцените параметры этой модели, определите показатели качества

модели (коэффициент детерминации, среднюю ошибку аппроксимации).

31 36 28 40 30 34 55 23 28 43

Инвест. вложения,

млн руб.

Возраст, лет

631221 1918 11252079 5441 2298 959860 94

19. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости ВВП от объёма продаж. Оцените парамет-

ры этой модели, определите показатели качества модели (коэффициент де-

терминации, среднюю ошибку аппроксимации).

504 316 474 102 315 152 279 806 388 121

490 311 484 985 408 156 588 125 321 87

Объём продаж, т.

ВВП, млн руб.

20. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости бюджета от уровня продаж предприятия.

Тема лабораторной работы: Построение и анализ качества модели парной линейной регрессии.

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

Стандартная ошибка точечного прогноза.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

15из16

Оцените параметры этой модели, определите показатели качества модели

(коэффициент детерминации, среднюю ошибку аппроксимации).

2194 2224 5424 2499 1003 3100 9937 9935 1497 1423

2747 1063 13041517 1170 176 1088

Уровень продаж,

тыс. кг

2612 6975 2972

Бюджет, млн руб.

21. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости миграций от уровня занятости населения.

Оцените параметры этой модели, определите показатели качества модели

(коэффициент детерминации, среднюю ошибку аппроксимации).

102 106 96 92 100 108 80 96 106 97

832 833 879 181

Уровень занятости,

чел.

1723 1721 1832 1180 1050 1053

Миграции, тыс. чел.

22. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости производительности от условий труда. Оце-

ните параметры этой модели, определите показатели качества модели (коэф-

фициент детерминации, среднюю ошибку аппроксимации).

826 836 679 566 496 493 407 401 378 365

496 510 413 342 301 295 248 242 236 228

Условия труда (коэф.)

Произв., шт.

23. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости цены на товар от предложения. Оцените

параметры этой модели, определить показатели качества модели (коэффици-

ент детерминации, среднюю ошибку аппроксимации).

29,4 26,7 2,7 14 126 68,3 23,6 15 14 7

26,1 25,8 0,3 20,2 139 69,3 6,7 14 14 11

Цена, млн руб.

Предложение, шт.

24. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости эффективности использования рабочей си-

лы от заработной платы. Оцените параметры этой модели, определите пока-

затели качества модели (коэффициент детерминации, среднюю ошибку ап-

проксимации).

307 309 253 224 195 197 171 168 153 145

3044 3048 2655 2006 1770 1771 1393 1593 1487 1760

Эффект-ть, млн руб.

ЗП, тыс. руб.

25. Предположим, что исходные данные описываются моделью парной

линейной регрессии зависимости заработной платы от производительности

Тема лабораторной работы: Построение и анализ качества модели парной линейной регрессии.

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

Стандартная ошибка точечного прогноза.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

16из16

труда. Оцените параметры этой модели, определите показатели качества мо-

дели (коэффициент детерминации, среднюю ошибку аппроксимации).

1440 1438 1151 968 830 850 696 685 649 625

1003 993 856 696 614 634 496 454 460 423

ЗП, тыс. руб.

Произв. труда, шт.

Тема лабораторной работы: Матричная алгебра в идентификации модели множественной линейной регрессии.

1из17

Анализ качества модели множественной линейной регрессии.

Интервальное оценивание параметров уравнения множественной линейной регрессии.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

№

Матричная алгебра в идентификации модели множественной ли-

нейной

регрессии. Анализ качества модели множественной линейной регрес-

сии. Интервальное оценивание параметров уравнения множественной линей-

ной регрессии.

1. Научиться строить модель множественной линейной регрессии,

описывающей экономическую систему. 2. Уметь производить анализ качест-

ва модели множественной линейной регрессии.

Представить преподавателю подробное решение за-

дачи с выводами в MS Excel. Оформление должно быть аналогично разо-

бранному примеру. Письменно ответить на контрольные вопросы на рабочем

листе MS Excel после решения задачи. Вариант работы определяет препо-

даватель.

Замечание 4.1. Ячейка, содержащая формулу, будет отмечена серым цве-

том.

Задача 4.1. Известны факторы, влияющие на цену выпускаемого продук-

та ООО «Окна», результаты наблюдений за которыми приведены в таблице

(рис. 4.1). Определите зависимость цены от затрат удельных и постоянных, а

также от рентабельности предприятия (от объема выпускаемой продукции),

используя модель множественной линейной регрессии, для этого необходи-

мо:

1) произвести идентификацию модели;

2) рассчитать общую, факторную и остаточную дисперсии;

3) вычислить коэффициент детерминации;

4) вычислить среднюю ошибку аппроксимации;

5) вычислить стандартную ошибку регрессии;

6) вычислить стандартные ошибки параметров регрессии;

7) проверить общее качество модели при уровне значимости, равном 0,05;

8) проверить значимость каждого параметра;

9) по результатам анализа качества модели (в случае некачественной мо-

дели) произвести ее усовершенствование;

10) произвести интервальное оценивание параметров регрессионной мо-

дели;

11) осуществить точечный прогноз (только в случае качественной моде-

ли, если модель оказалась некачественной, то прогноз осуществить по усо-

вершенствованной модели) при значениях соответствующих факторов:

, , ;

=x 103

5,8

12) в случае двухфакторной регрессионной модели, изобразить ее графи-

чески (построить поверхность).

Тема лабораторной работы: Матричная алгебра в идентификации модели множественной линейной регрессии.

2из17

Анализ качества модели множественной линейной регрессии.

Интервальное оценивание параметров уравнения множественной линейной регрессии.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

Представим результаты наблюдений за исследуемым показателем и фак-

торами в одни и те же моменты времени в виде таблицы (рис. 4.1)

20 30 21 25 23 18 22 24 29 27

Постоянные затраты

Объем выпускаемой

продукции

Цена

Удельные затраты

12 7 11 14

103

5869

100 120 90 94 91 80 93 95

8,3

101

12 8 7,5 7,9 13 7 7,7 6

Рис. 4.1

1. Построим трехфакторную регрессионную модель вида

++++=

3322110

xaxaxaay

1.1. Для удобства расчетов пред-

ставим таблицу исходных данных

следующим образом (рис. 4.2).

2. Произведем идентификацию

данной модели, то есть найдем оцен-

ки параметров модели , , ,

, используя функции матричной

алгебры в MS Excel.

Вызов функции: MS Excel –

Вставка – Функция… – Матема-

тические

2.1. Представим модель в мат-

ричной форме

eXaY +=

2.2. Составим матрицы , ,

по таблице исходных данных

(рис. 4.3).

Y X

№

п/п

Цена

(y)

Затраты

(удельные) (x

)

Постоянные

затраты (x

)

Объем (x

), шт.

1 20 10 100 12,00

2 30 12 120 8,00

3217907,50

4 2511947,90

5 23149113,00

6185807,00

7228937,70

8246956,00

9 29 9 103 5,00

10 27 13 101 8,30

Рис. 4.2

2.3. Вектор-столбец оценок параметров модели найдем по формуле

()

YXXXa

1−

2.3.1. Проверим выполнимость предпосылки множественного регресси-

онного анализа:

(

)

0det ≠XX

Тема лабораторной работы: Матричная алгебра в идентификации модели множественной линейной регрессии.

3из17

Анализ качества модели множественной линейной регрессии.

Интервальное оценивание параметров уравнения множественной линейной регрессии.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

20 1 10 100 12

30 1 12 120 8

21 1 7 90 7,5

25 1 11 94 7,9

=23

= 1 14 91 13

18 1 5 80 7

22 1 8 93 7,7

24 1 6 95 6

29 1 9 103 5

27 1 13 101 8,3

Рис. 4.3

2.3.2. Находим матрицу транспонированную к матрице .

X X

1111111111

101271114586913

100 120 90 94 91 80 93 95 103 101

12 8 7,5 7,9 13 7 7,7 6 5 8,3

2.3.2. Находим матрицу

(

)

10 95 967 82,4

(

)

95 985 9332 822,9

967 9332 94501 7960

82,4 822,9 7960 733,8

det(

16340586

Видим, что

(

)

0det ≠XX

, следовательно, вектор-столбец оценок па-

раметров модели существует, поэтому…

2.3.3. Находим матрицу

()

1−

15,4 0,382 -0,1572 -0,4526

(

)

-1

0,382 0,0333 -0,0051 -0,0251

-0,1572 -0,0051 0,0018 0,004

-0,4526 -0,0251 0,004 0,0371

2.3.4. Находим матрицу

()

XXX

1−