Лабораторные и практические задания по эконометрике

Введение

Эконометрика наряду с макро- и микроэкономикой, является фундаментальной дисциплиной современного экономического образования.

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

Эконометрика − это дисциплина, объединяющая совокупность теоретических результатов, методов и приемов, позволяющих на базе экономической теории, экономической статистики и математико-статистического инструментария получать количественное выражение качественным закономерностям. Курс эконометрики призван научить различным способам выражения связей и закономерностей через эконометрические модели и методы проверки их адекватности, основанные на данных наблюдений. Знания, полученные при ее изучении, будут использованы в процессе дальнейшей подготовки студентов экономических направлений.

Цель преподавания курса − дать студентам научное представление о методах, моделях и приемах, позволяющих получать количественные выражения закономерностям экономической теории на базе экономической статистики с использованием математико-статистического инструментария.

Эконометрики дает теоретические основы и практические навыки расчетно-экономической, информационно – аналитической деятельности.

Курс «Эконометрика» рассчитан на бакалавров второго курса направления экономика, более глубокое изучение предмета предполагается в программе магистратуры.

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

Изменение уровней факторов (желательное и нежелательное) имеет различное влияние на процесс хозяйственной деятельности. В условиях рыночной экономики практические работники должны уметь оценивать влияние факторов на результаты их деятельности. Однако эффективное применение эконометрических методов для оценки взаимосвязей между социально – экономическими явлениями и разработки на ее основе прогнозов и экономических гипотез зависит от знания сущности метода и его возможностей в оценке информации.

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

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

Изучение эконометрики при получении экономического образования имеет целью:

овладение совокупностью математических методов, используемых для количественной оценки экономических явлений и процессов;

обучение эконометрическому моделированию, т. е. построению экономико-математических моделей, параметры которых оцениваются средствами математической статистики;

обучение эмпирическому выводу экономических законов;

подготовку к прикладным исследованиям в области экономики, при этом причинно-следственными связями занимается экономическая теория, а связями вообще без выявления их причин, – эконометрика. Поэтому в задачи курса входит:

обучение построению эконометрических моделей; обучение оценке качества моделей обучение прогнозированию с помощью эконометрических моделей.

Требования к студентам: курс «Эконометрика» рассчитан на студентов, прослушавших курсы математического анализа, линейной алгебры, экономической статистики. Необходимый предшествующий уровень образования студента, приступающего к изучению дисциплины «Эконометрика», так же подразумевает твердые познания в области экономической теории, теории вероятностей и математической статистики, знакомство с экономической статистикой, определенный навык использования программных средств для решения экономико-математических задач, а также владеет математическим анализом в пределах стандартного вузовского курса для экономических направлений.

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

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

Курс обеспечивает овладение следующими компетенциями:

способен понимать сущность и значение информации в развитии современного информационного общества;

для расчетно-экономической деятельности

способен собрать и проанализировать исходные данные, необходимые для расчета экономических и социально-экономических показателей, характеризующих деятельность хозяйствующих субъектов (ПК-1);

владеет основными методами, способами и средствами получения, хранения, переработки информации, имеет навыки работы с компьютером как средством управления информацией, способен работать с информацией в глобальных компьютерных сетях (ОК-13);

способен осуществлять сбор, анализ и обработку данных, необходимых для решения поставленных экономических задач (ПК-4);

способен выбрать инструментальные средства для обработки экономических данных в соответствии с поставленной задачей, проанализировать результаты расчетов и обосновать полученные выводы (ПК-5);

способен на основе описания экономических процессов и явлений строить стандартные теоретические и эконометрические модели, анализировать и содержательно интерпретировать полученные результаты (ПК-6);

способен анализировать и интерпретировать финансовую, бухгалтерскую и иную информацию, содержащуюся в отчетности предприятий различных форм собственности, организаций, ведомств и использовать полученные сведения для принятия управленческих решений (ПК-7);

способен использовать для решения аналитических и исследовательских задач современные технические средства и информационные технологии (ПК-10).

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

В результате освоения дисциплины обучающийся должен иметь представление о:

о роли эконометрики в экономических исследованиях, а также место и роль эконометрики в экономике, финансах, менеджменте.

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

Уметь: решать типовые задачи в пределах изучаемого программного материала; использовать основные приемы эконометрического исследования эмпирических данных.

Уметь использовать: основы корреляционно - регрессионного анализа; основы применения эконометрических методов для оценки взаимосвязей между социально – экономическими явлениями.

Владеть

- навыками самостоятельной работы с учебно-методической литературой и электронными учебно-методическими комплексами; основными эконометрическими моделями и методами;

- основами теории прогнозирования экономических гипотез, применять при этом высшую математику, основы теории вероятностей и математической статистики.

- практическими (с использованием статистики; нормативно-правовых актов) и теоретическими навыками анализа процессов, происходящих в стране и в исследуемых регионах.

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

1. Парный регрессионный анализ. Модели регрессии

1.1. Методические рекомендации по составлению модели на основе линейной и нелинейной регрессии

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

Расходы на продукты питания, y , тыс. руб.	0,9	1,2	1,8	2,2	2,6	2,9	3,3	3,8
Доходы семьи, x , тыс. руб.	1,2	3,1	5,3	7,4	9,6	11,8	14,5	18,7

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

Поэтому уравнение линейной регрессии будем искать в виде . Для нахождения параметров регрессии составим и решим следующую систему уравнений:

(*)

Рис. 1.1. Поле корреляции

По графику видно, что точки выстраиваются в некоторую прямую линию. Для нахождения параметров линейной регрессии составим таблицу. На ее основе составим систему уравнений (*).

Таблица 1.2

	x	y	x∙y	x2	y2				Ai, %
1	2	3	4	5	6	7	8	9	10
1	1,2	0,9	1,08	1,44	0,81	1,038	-0,138	0,0190	15,33
2	3,1	1,2	3,72	9,61	1,44	1,357	-0,157	0,0246	13,08
3	5,3	1,8	9,54	28,09	3,24	1,726	0,074	0,0055	4,11
4	7,4	2,2	16,28	54,76	4,84	2,079	0,121	0,0146	5,50
5	9,6	2,6	24,96	92,16	6,76	2,449	0,151	0,0228	5,81
6	11,8	2,9	34,22	139,24	8,41	2,818	0,082	0,0067	2,83
7	14,5	3,3	47,85	210,25	10,89	3,272	0,028	0,0008	0,85
8	18,7	3,8	71,06	349,69	14,44	3,978	-0,178	0,0317	4,68
Итого	71,6	18,7	208,71	885,24	50,83	18,717	-0,017	0,1257	52,19
Среднее значение	8,95	2,34	26,09	110,66	6,35	2,34	–	0,0157	6,52
σ	5,53	0,935	–	–	–	–	–	–	–
σ2	30,56	0,874	–	–	–	–	–	–	–

Рассчитаем параметры линейного уравнения парной регрессии . Для этого можно составить и решить систему уравнений или воспользоваться формулами:

Получили уравнение: . Т.е. с увеличением дохода семьи на 1000 руб. расходы на питание увеличиваются на 168 руб.

Уравнение линейной регрессии всегда дополняется показателем тесноты связи – линейным коэффициентом корреляции rxy :

Близость коэффициента корреляции к 1 по шкале Чеддока указывает на тесную линейную связь между признаками.

Коэффициент детерминации r2xy=0.987 показывает, что уравнением регрессии объясняется 98,7 % дисперсии результативного признака, а на долю прочих факторов приходится лишь 1,3 %.

Оценим качество уравнения регрессии в целом с помощью F-критерия Фишера. Вычислим фактическое значение F-критерия:

Табличное значение (k1=1 , k2=n-2=6 , a=0.05 ): Fтабл=5.99 . Так как Fфакт > Fтабл , то признается статистическая значимость уравнения в целом.

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

Фактические значения t-статистик: . Табличное значение t-критерия Стьюдента при a=0.05 и числе степеней свободы v=n-2=6 есть tтабл=2.447 . Так как tb > tтабл , ta > tтабл и tr > tтабл , то признаем статистическую значимость параметров регрессии и показателя тесноты связи. Рассчитаем доверительные интервалы для параметров регрессии a и b: a±t∙ma и b±t∙mb . Получим, что aϵ[0.597;1.075] и bϵ[0.145;0.191] .

Средняя ошибка аппроксимации (находим с помощью столбца 10 таблицы 1.3; ) говорит о хорошем качестве уравнения регрессии, т.е. свидетельствует о хорошем подборе модели к исходным данным.

И, наконец, найдем прогнозное значение результативного фактора при значении признака-фактора, составляющем 110% от среднего уровня , т.е. найдем расходы на питание, если доходы семьи составят 9,85 тыс. руб.

(тыс. руб.)

Значит, если доходы семьи составят 9,845 тыс. руб., то расходы на питание будут 2,490 тыс. руб.

Найдем доверительный интервал прогноза. Ошибка прогноза

а доверительный интервал :

Т.е. прогноз является статистически надежным.

Теперь на одном графике изобразим исходные данные и линию регрессии:

Рис. 1.2. Поле корреляции и линия регрессии

Рассмотрим пример решения этой же задачи на основе нелинейной модели регрессии. Рассмотрим логарифмическую зависимость между фактором и результатом .

Для нахождения параметров регрессии делаем замену z=lnx и составляем вспомогательную таблицу .

Таблица 1.3

	x	z	y	z∙y	z2	y2		ε	ε2	Ai
1	2	3	4	5	6	7	8	9	10	11
1	1,2	0,182	0,9	0,164	0,033	0,81	0,499	0,401	0,1610	44,58
2	3,1	1,131	1,2	1,358	1,280	1,44	1,508	–0,308	0,0947	25,64
3	5,3	1,668	1,8	3,002	2,781	3,24	2,078	–0,278	0,0772	15,43
4	7,4	2,001	2,2	4,403	4,006	4,84	2,433	–0,233	0,0541	10,57
5	9,6	2,262	2,6	5,881	5,116	6,76	2,709	–0,109	0,0119	4,20
6	11,8	2,468	2,9	7,157	6,092	8,41	2,929	–0,029	0,0008	0,99
7	14,5	2,674	3,3	8,825	7,151	10,89	3,148	0,152	0,0232	4,62
8	18,7	2,929	3,8	11,128	8,576	14,44	3,418	0,382	0,1459	10,05
Итого	71,6	15,315	18,7	41,918	35,035	50,83	18,720	–0,020	0,5688	116,08
Среднее значение	8,95	1,914	2,34	5,240	4,379	6,35	–	–	0,0711	14,51
σ	–	0,846	0,935	–	–	–	–	–	–	–
σ2	–	0,716	0,874	–	–	–	–	–	–	–

Найдем параметры уравнения регрессии:

Т.е. получаем следующее уравнение регрессии: . Теперь заполняем столбцы 8-11 нашей таблицы.

Находим индекс корреляции:

а индекс детерминации p2xy=0.918 , который показывает, что 91,8% вариации результативного признака объясняется вариацией признака-фактора, а 8,2% приходится на долю прочих факторов.

Средняя ошибка аппроксимации: , что недопустимо велико.

F -критерий Фишера:

значительно превышает табличное Fтабл=5.99 .

Изобразим на графике исходные данные и линию регрессии:

Рис. 1.3. Поле корреляции и линия регрессии

Следующая нелинейная модель – модель с квадратным корнем .

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

Таблица 1.4

	x	z	y	z∙y	z2	y2		ε	ε2	Ai
1	2	3	4	5	6	7	8	9	10	11
1	1,2	1,10	0,9	0,99	1,2	0,81	0,734	0,166	0,0276	18,46
2	3,1	1,76	1,2	2,11	3,1	1,44	1,353	–0,153	0,0235	12,77
3	5,3	2,30	1,8	4,14	5,3	3,24	1,857	–0,057	0,0033	3,19
4	7,4	2,72	2,2	5,98	7,4	4,84	2,247	–0,047	0,0022	2,12
5	9,6	3,10	2,6	8,06	9,6	6,76	2,599	0,001	0,0000	0,05
6	11,8	3,44	2,9	9,96	11,8	8,41	2,912	–0,012	0,0001	0,42
7	14,5	3,81	3,3	12,57	14,5	10,89	3,259	0,041	0,0017	1,20
8	18,7	4,32	3,8	16,43	18,7	14,44	3,740	0,060	0,0036	1,58
Итого	71,6	22,54	18,7	60,24	71,6	50,83	18,700	–0,001	0,0619	39,82
Среднее значение	8,95	2,82	2,34	7,53	8,95	6,35	–	–	0,0077	4,98
σ	–	1,00	0,935	–	–	–	–	–	–	–
σ2	–	1,00	0,874	–	–	–	–	–	–	–

Найдем уравнение регрессии:

Т.е. получаем следующее уравнение регрессии: . Теперь заполняем столбцы 8-11 нашей таблицы.

Индекс корреляции находим по формуле:

а индекс детерминации p2=0.991 , который показывает, что 99,1% вариации результативного признака объясняется вариацией признака-фактора, а 0,9% приходится на долю прочих факторов.

Средняя ошибка аппроксимации: показывает, что линия регрессии хорошо приближает исходные данные.

F -критерий Фишера:

значительно превышает табличное Fтабл=5.99 .

Изобразим на графике исходные данные и линию регрессии:
Рис. 1.4. Поле корреляции и линия регрессии

Для нахождения параметров регрессии y=a∙xb∙ε необходимо провести ее линеаризацию логарифмированием:

Y=A+b∙X+E ,

где Y=lny , X=lnx , A=lna , E=lnε .

Составляем вспомогательную таблицу для преобразованных данных:

Таблица 1.5

	X	Y	X∙Y	X2	Y2		ε	ε2	Ai
1	2	3	4	5	6	7	8	9	10
1	0,182	–0,105	–0,019	0,033	0,011	0,8149	0,0851	0,0072	9,46
2	1,131	0,182	0,206	1,280	0,033	1,3747	–0,1747	0,0305	14,56
3	1,668	0,588	0,980	2,781	0,345	1,8473	–0,0473	0,0022	2,63
4	2,001	0,788	1,578	4,006	0,622	2,2203	–0,0203	0,0004	0,92
5	2,262	0,956	2,161	5,116	0,913	2,5627	0,0373	0,0014	1,43
6	2,468	1,065	2,628	6,092	1,134	2,8713	0,0287	0,0008	0,99
7	2,674	1,194	3,193	7,151	1,425	3,2165	0,0835	0,0070	2,53
8	2,929	1,335	3,910	8,576	1,782	3,7004	0,0996	0,0099	2,62
Итого	15,315	6,002	14,637	35,035	6,266	18,608	0,0919	0,0595	35,14
Среднее значение	1,914	0,750	1,830	4,379	0,783	–	–	0,0074	4,39
σ	0,846	0,470	–	–	–	–	–	–	–
σ2	0,716	0,221	–	–	–	–	–	–	–

Найдем уравнение регрессии:

,
.

Т.е. получаем следующее уравнение регрессии: Yx=-0.305+0.551∙X. После потенцирования находим искомое уравнение регрессии:

Теперь заполняем столбцы 7–10 нашей таблицы.

Вычислим индекс корреляции:

а индекс детерминации p2=0.967 , который показывает, что 96,7% вариации результативного признака объясняется вариацией признака-фактора, а 3,3% приходится на долю прочих факторов. Средняя ошибка аппроксимации: показывает, что линия регрессии хорошо приближает исходные данные.

F -критерий Фишера:

значительно превышает табличное Fтабл=5.99 .

Изобразим на графике исходные данные и линию регрессии:

Рис. 1.5. Поле корреляции и линия регрессии

Сравним построенные модели по индексу детерминации и средней ошибке аппроксимации:

Таблица 1.6

Модель	Индекс детерминации,R2 (r2xy,p 2xy )	Средняя ошибка аппроксимации, , %
Линейная модель,	0,987	6,52
Полулогарифмическая модель,	0,918	14,51
Модель с квадратным корнем,	0,991	4,98
Степенная модель,y=a∙xb∙ε	0,967	4,39

Наиболее хорошо исходные данные аппроксимирует модель с квадратным корнем. Но в данном случае, так как индексы детерминации линейной модели и модели с квадратным корнем отличаются всего на 0,004, то вполне можно обойтись более простой линейной функцией.

1.2. Лабораторные работы

Для того, чтобы более эффективно применять при исследованиях эконометрические модели, мы предлагаем для их построения использовать средства табличного процессора Microsoft Excel. Для этого предлагаем к выполнению ниже представленных лабораторных работ. Ход работ и методические рекомендации представлены на диске.

Лабораторная работа №1.

Тема «Реализация модели парной регрессии средствами табличного процессора Microsoft Excel».

Цель работы: освоение основных навыков вычислений коэффициентов парной регрессионной модели и ее последующая оценка средствами Microsoft Excel.

Теоретический блок

Практический блок

Рассмотрим последовательность действий для вычисления коэффициентов линейной модели парной регрессии и оценки полученной модели на следующем примере:

По группе предприятий, выпускающих однородную продукцию известны следующие значения двух признаков:

№ предприятия	Объем производства, млн. руб.	Оптовая цена за единицу продукции, руб.
1	0,9	25
2	1,2	23,1
3	1,3	23,6
4	1,5	15,6
5	2,1	11,2
6	2,2	12,5
7	2,5	10
8	2,8	9,4
9	2,9	5,6

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

Занесем данные из таблицы в книгу Microsoft Excel. Затем построим поле корреляции результата и фактора. Для построения диаграммы выбираем тип диаграммы - точечная, приводим ее к виду, предложенному на рисунке.

Построим расчетную таблицу для вычисления параметров линейной регрессионной модели. Заполним таблицу расчетными значениями, добавив строки: Итого, Среднее значение

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

Применяя формулы вычисления параметров парной линейной регрессии вычислим коэффициенты а и b

Проверим полученные значения коэффициентов при помощи функции ЛИНЕЙН. Выделим область пустых ячеек (1 на 2) для вывода оценок коэффициентов. Вставим функцию ЛИНЕЙН, заполним ее аргументы следующим образом:

Известные значения х - диапазон, содержащий признак фактор х.

Известные значения у - диапазон, содержащий результирующий признак у.

Константа - значение 1.

Статистика - значение 0.

Получим оценки коэффициентов, рассчитанные программой Microsoft Excel.

Таким образом, управление регрессии имеет следующий вид:

ŷ=33,12-9,31х

Рассчитаем оставшиеся графы таблицы:

В итоге получим следующие данные:

Рассчитаем ошибку аппроксимации:

В среднем расчетные значения отклоняются от фактических на 10,8%.

Рассчитаем линейный коэффициент корреляции и критерий Фишера по соответствующим формулам:

В итоге получим

r=-0,95952, значит можно говорить о высокой тесноте связи между y и x.

Fфакт =81,24958, Fфакт сравнивается с критическим значением Fк, которое определяется по таблице F-критерия и равно 5,59. Так как Fфакт>Fк уравнении регрессии в целом признается существенным.

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

Для этого введем следующие данные:

В столбце х введем числа от наименьшего эмпирического значения х до наибольшего эмпирического значения х.

В графе ŷ введем формулу уравнения полученной линейной регрессии, где вместо аргумента х будем вводить имя соответствующей ячейки.

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

По рассчитанным данным построим диаграмму, установив следующие параметры:

Тип диаграммы	График
Диапазон данных	Столбцы со значениями переменных у и ŷ
Подписи оси ох	Столбец со значениями х
Имя ряда 1	Модель регрессии
Имя ряда 2	Эмпирические данные
Название диаграммы	Графическая иллюстрация модели

Затем в полученной диаграмме щелкните левой кнопкой мыши на графике, иллюстрирующем эмпирические данные. Выберите команду Формат ряда данных. Установите во вкладке Вид Следующие параметры: Тип линии - отсутствует, тип маркера - обычный. Получим следующую диаграмму, иллюстрирующую нашу модель:

Задания для самостоятельного выполнения

1. Администрация страховой компании приняла решение о введении нового вида услуг – страхование на случай пожара. С целью определения тарифов по выборке из 10 случаев пожаров анализируется зависимость стоимости ущерба, нанесенного пожаром от расстояния до ближайшей пожарной станции:

№ п/п	1.	2.	3.	4.	5.	6.	7.	8.	9.	10.
Общая сумма ущерба, млн.руб.	26,2	17,8	31,3	23,1	27,5	36,0	14,1	22,3	19,6	31,3
Расстояние до ближайшей станции, км	3,4	1,8	4,6	2,3	3,1	5,5	0,7	3,0	2,6	4,3

Расчетную таблицу, диаграмму, иллюстрирующую полученную модель и анализ полученных результатов оформить в виде документа программы Microsoft Word.

2. Для данной модели провести расчет параметров экспоненциальной кривой (с помощью функции ЛГРФПРИБЛ). Оформить графическую иллюстрацию полученной модели. Сделать выводы.

Лабораторная работа №2.

Тема «Реализация модели множественной регрессии средствами табличного процессора Microsoft Excel».

Цель работы: освоение основных навыков вычислений коэффициентов множественной регрессионной модели и ее последующая оценка средствами Microsoft Excel.

Теоретический блок

Практический блок

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

Имеются следующие данные о выработке литья на одного работающего Х1 (т), браке литья Х2 (%) и себестоимость литья Y (руб.) :

№	Х1	Х2	Y
1	14,6	4,2	239
2	13,5	6,7	254
3	21,5	5,5	262
4	17,4	7,7	251
5	44,8	1,2	158
6	111,9	2,2	101
7	20,1	8,4	259
8	28,1	1,4	186
9	22,3	1,2	204

Необходимо:

1. Построить уравнение множественной регрессии в стандартизованной и естественной форме; рассчитать частные коэффициенты эластичности, сравнить их с b1 и b2, пояснить различия между ними.

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

3. Рассчитать общий и частные F-критерии Фишера.

Занесем данные из таблицы в книгу Microsoft Excel.

Построим расчетную таблицу для вычисления параметров множественной регрессионной модели. Заполним таблицу расчетными значениями, добавив строки: Итого, Среднее значение

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

Применяя формулы вычисления параметров парной линейной регрессии вычислим коэффициенты а, b1 и b2

Предварительно вычислим определители D, Da1, Db1,Db2

Определители Da1, Db1,Db2 получаем заменой первого, второго и третьего столбца определителя D соответственно на столбец-матрицу В

Напомним, что определитель матрицы в программе Microsoft Excel можно вычислить с помощью встроенной функции МОПРЕД

Получим следующие результаты

С помощью полученных определителей вычислим по формулам коэффициенты а, b1 и b2

Проверим полученные значения коэффициентов при помощи встроенных функций. Вычислим параметры линейного уравнения множественной регрессии.

Эта операция проводится с помощью инструмента Анализа данных - Регрессия
(Данный инструмент в 2007 версии требуется добавить на панель "Данные" следующим образом: кнопка "Office"(или нажатием alt+A)--Параметры excel--Надстройки--выбрать пакет анализа--нажать Перейти--поставить все галочки и установить дополнения. После установки инструменты появятся на панели Данные--Анализ данных--Регрессия).
Она аналогична расчету параметров парной линейной регрессии, описанной ранее, только в отличие от парной регрессии в диалоговом окне при заполнении параметра входной интервал X следует указать не один столбец, а все столбцы, содержащие значения факторных признаков.

Известные значения х - диапазон, содержащий признаки факторы х1 и х2.

Известные значения у - диапазон, содержащий результирующий признак у.

Получим оценки коэффициентов, рассчитанные программой Microsoft Excel.

Таким образом, управление регрессии имеет следующий вид:

ŷ=215-1,22Х1+8,78Х2

Рассчитаем коэффициенты стандартизированной модели:

Расчет параметра σ проведем по формуле:

Получим следующие данные

Получим уравнение

Для характеристики относительной силы влияния Х1 и Х2 на Y рассчитаем средние коэффициенты эластичности:

Рассчитаем линейные коэффициенты частной корреляции по формулам:

получим следующие данные

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

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

1) последовательно выберите пункты ленты Данные / Анализ данных / Корреляция. Щелкните по кнопке ОК (Помните, для того чтобы инструмент корелляция появился, вы должны были указать все галочки во время предыдущей установки инструмента Регрессия);

2) заполните диалоговое окно ввода данных и параметров вывода:

Входной интервал – диапазон, содержащий анализируемые данные, это может быть одна или несколько строк или столбцов;

Группирование – по столбцам или по строкам – необходимо указать дополнительно (выбрать по столбцам);

Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

Получим следующую таблицу:

Таким образом коэффициенты, рассчитанные нами и рассчитанные программой совпадают.

Рассчитаем линейный коэффициент множественной корреляции:

Получим Ryx1x2=0,98, то есть тесная зависимость результирующего признака от признаков - факторов

Рассчитаем общий и частные F-критерии по следующим формулам:

Сравним данные значения с табличными: Fфакт=67,55> Fтабл=5,14; Fх1факт=53,25> Fтабл=5,99; Fх2факт=24,01> Fтабл=5,99. Следовательно можно сделать вывод о статистической значимости полученной модели и обоснованности включения в модель как переменной х1, так и переменной х2

Задания для самостоятельного выполнения

Лабораторная работа №3

Тема «Сравнительный анализ линейной и нелинейных моделей парной регрессии с использованием средств табличного процессора Microsoft Excel»

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

Теоретический блок

Практический блок

Построим для предложенной задачи несколько моделей парной регрессии, а затем оценим каждую модель через индекс детерминации и среднюю ошибку аппроксимации.

Задача.

По группе предприятий, выпускающих однородную продукцию известны следующие значения двух признаков:

№ предприятия	Объем производства, млн. руб.	Оптовая цена за единицу продукции, руб.
1	0,9	25
2	1,2	23,1
3	1,3	23,6
4	1,5	15,6
5	2,1	11,2
6	2,2	12,5
7	2,5	10
8	2,8	9,4
9	2,9	5,6

ЭТАП 1: построение парной линейной модели.

Парную линейную модель для данной задачи мы уже строили в лабораторной работе №1. В итоге получили следующие результаты:

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

ЭТАП 2: построение парной полулогарифмической модели.

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

Для этого сначала на имеющемся листе рядом с расчетной графой нашей таблицы добавляем столбец переменной z, и заполняем его значениями LN(x)

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

Теперь заменим столбец значение Х на столбец значений Z. Для этого скопируем нужный столбец с Листа 1 и вставим на место столбца Х на Листе 2. При вставке выбрать вкладку Только значения.

Единственный параметр, который требует новой формулы для расчета - это индекс корреляции ρxy (в предыдущем случае обозначался rxy). Он рассчитывается по следующим формулам:

, где

Получим следующие данные:

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

ЭТАП 3: построение парной модели .

Для нахождения параметров регрессии делаем замену и составляем вспомогательную таблицу аналогично 3-му этапу.

Получим следующие результаты:

ЭТАП 4: построение парной показательной модели .

Для нахождения параметров регрессии необходимо провести ее линеаризацию, для чего необходимо прологарифмировать левую и правую части: , где .

Теперь составим вспомогательную таблицу, аналогично предыдущим этапам заменяя значения столбцов х и у на соответствующие значения LN(x) и LN(y).

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

Сначала поменяем формулы для расчета теоретических значений у.

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

Затем скорректируем остальные формулы, где встречалась переменная у.

Аналогично предыдущим случаям построим графическую иллюстрацию.

Получим следующие результаты:

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

Задания для самостоятельного выполнения

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

Вариант 1.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	78,8	55,1
2	61,2	69,0
3	69,9	67,2
4	66,7	71,8
5	65,0	68,8
6	64,3	57,2
7	59,3	65,2

Вариант 2.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	78,8	45,1
2	61,2	59,0
3	69,9	67,2
4	66,7	71,8
5	65,0	58,8
6	64,3	57,2
7	59,3	65,2

Вариант3.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	78,8	55,1
2	51,2	69,0
3	69,9	67,2
4	56,7	71,8
5	65,0	68,8
6	44,3	57,2
7	49,3	65,2

Вариант 4.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	68,8	55,0
2	67,2	69,2
3	67,7	67,2
4	56,7	71,3
5	65,0	68,0
6	54,3	57,2
7	59,3	65,0

Вариант 5.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	55,0	78,8
2	69,2	61,2
3	67,2	69,9
4	71,3	66,7
5	68,0	65,0
6	57,2	64,3
7	65,0	59,3

Вариант 6.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	58,8	55,1
2	51,2	69,0
3	59,9	67,2
4	66,7	71,8
5	65,0	68,8
6	64,3	57,2
7	59,3	65,2

Вариант 7.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	58,8	55,1
2	51,2	59,0
3	69,9	77,2
4	66,7	71,8
5	65,0	68,8
6	64,3	67,2
7	59,3	65,2

Вариант 8.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	68,8	45,1
2	67,2	59,0
3	67,7	67,2
4	56,7	71,8
5	65,0	58,8
6	54,3	57,2
7	59,3	65,2

Вариант 9.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	75,3	55,1
2	61,2	69,0
3	60,2	67,2
4	66,7	71,8
5	62,3	68,8
6	54,3	57,2
7	59,3	65,2

Вариант 10.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	78,8	75,1
2	61,2	69,0
3	69,9	75,4
4	66,7	71,8
5	65,0	70,8
6	64,3	67,2
7	59,3	68,2

Вариант 11.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	75,1	65,1
2	59,0	69,0
3	65,4	67,2
4	71,8	77,8
5	70,8	68,8
6	67,2	69,4
7	68,2	75,1

Вариант 12.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	68,8	55,1
2	67,2	69,0
3	67,7	67,2
4	56,7	71,8
5	65,0	68,8
6	54,3	57,2
7	59,3	65,2

Вариант 13.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	78,8	55,0
2	51,2	69,2
3	69,9	67,2
4	56,7	71,3
5	65,0	68,0
6	44,3	57,2
7	49,3	65,0

Вариант 14.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	78,2	55,5
2	61,2	49,0
3	69,4	67,2
4	66,7	71,8
5	65,0	68,8
6	64,5	57,2
7	59,0	65,2

Вариант 15.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	88,8	65,1
2	61,2	49,0
3	79,9	67,2
4	66,7	51,8
5	55,0	68,8
6	64,3	57,2
7	49,3	35,2

Вариант 16.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	75,3	55,1
2	61,2	65,0
3	65,4	67,2
4	56,7	67,8
5	65,0	65,8
6	64,3	57,2
7	59,3	61,2

Вариант 17.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	78,0	55,0
2	61,2	69,2
3	69,5	67,2
4	66,1	71,3
5	65,0	68,0
6	64,1	57,2
7	59,3	65,0

Вариант 18.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	70,8	55,1
2	60,2	55,0
3	69,9	67,2
4	66,7	70,8
5	65,0	48,8
6	50,3	57,2
7	59,3	65,2

Вариант 19.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	78,8	45,1
2	51,2	59,0
3	69,9	67,2
4	56,7	71,8
5	65,0	58,8
6	44,3	57,2
7	49,3	65,2

Вариант 20.

Район	Расходы на покупку продовольственных товаров в общих расходах, %, y	Среднедневная заработная плата одного работающего, руб., x
1	68,8	45,1
2	61,2	69,0
3	69,9	67,2
4	56,7	71,8
5	65,0	68,8
6	64,3	57,2
7	39,3	55,2

1.3. Задания для самопроверки

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

– Проведите оценку существенности связи.

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

– Оцените существенность коэффициента корреляции с помощью критериев Стьюдента или Фишера.

– Сделайте краткие выводы о полученных значениях и о модели в целом.

Вариант 1

Показатель	1	2	3	4	5	6	7	8	9	10
Потреблено материалов на единицу продукции, кг	9	6	5	4	3,7	3,5	6	7	3,5	3,6
Выпуск продукции, тыс.ед.	100	200	300	400	500	600	700	150	120	250