Реализация задания на компьютере с помощью ППП Ехсеl. Здесь так же, как и в парной регрессии необходимо выполнить задание в двух вариантах:

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

ВНИМАНИЕ ! Каждый студент должен выполнить индивидуальное задание с использованием компьютера в двух вариантах:

3) Реализовать формулы (2.1) – (2.19) с помощью одиночных функций ППП Ехсеl.

4) Использовать «комплексные» функции, выходом которых являются не только коэффициенты регрессии, но и дополнительная регрессионная статистика (среднеквадратические отклонения, коэффициент детерминации и т.д.).

3) Реализация регрессионных формул (2.1) – (2.19) с помощью одиночных функций.

В первую очередь необходимо представить данные наблюдений в матричной форме (см. рис.2.1). Затем используя матричные функции из Мастер функций: МОБР, МУМНОЖ, ТРАНСП реализуем формулу (2.8), результатом которой будет вектор оценок коэффициентов регрессии В.

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

Для вычисления дисперсий необходимо вычислить S² в соответствие с формулой (2.10). На основании Т-статистик делается вывод о значимости коэффициентов регрессии их доверительные интервалы. Значения t_кр можно получить, используя статистическую функцию СТЬЮДРАСПОБР. По соответствующим формулам вычисляются коэффициент детерминации R² и F – критерий, на основании которых делается вывод о значимости уравнения регрессии в целом. Для нахождения критической точки f_кр нужно воспользоваться функцией FРАСПОБР.

Проверка соответствия предпосылкам МНК осуществляется по критерию Дарбина – Уотсона. Критические значения распределения определяются из таблицы (электронного варианта таблицы нет).

Примерный вид реализации задачи на компьютере представлен на рис.2.2.

Значение Y   Матрица Х

Рис.2.1

Рис.2.2

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

Значение Y	Yмод
	22,48852
	23,7304085
	31,009917
	28,6979627
	33,4936941
	37,0475369
	39,531314
	38,4612482
	45,7407567
	51,7783766
	53,0202652

Рис.2.3

На рис.2.2 в ячейке с названием «S(Yp)» была вычислена стандартная ошибка прогноза объясняемой переменной по формуле:

S(Y_р) = S ,

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

4) Использование «Комплексных» функций.

В качестве такой функции может быть использована встроенная статистическая функция ЛИНЕЙН.

Дополнительная регрессионная статистика (в случае ее инициализации) будет выводиться в порядке, указанном на рис.2.4.

Рис.2.4.

Обозначения на рисунке следующие: b – свободный коэффициент линейной регрессии; m_i – коэффициенты при х_i ; Se – стандартные ошибки коэффициентов регрессии; r2 - коэффициент детерминации; Sey - стандартная ошибка для оценки у; F – F- статистика; d_f – количество степеней свободы; Ssрег – регрессионная сумма квадратов; Ssост – остаточная сумма квадратов.

Для лучшей наглядности можно нужные значения из этой таблицы выбирать индивидуально и размещать в нужных форматах документа. Для этого можно воспользоваться функцией ИНДЕКС из категории Ссылки и массивы. Выделите ячейку, в которую хотите поместить отдельный элемент массива и введите формулу, например: Индекс(Линейн(Y;Х;1;1);1;2). В результате в данную ячейку будет записан элемент (1,2) регрессионной таблицы. Таким образом, можно создать более наглядную таблицу.

Пример решения задания на компьютере с использованием функции ЛИНЕЙНпредставлен на рисунках 2.5, 2.6.

Значения X1 Значения X2 Значения Y                                                                                   Результаты вычислений параметров модели     y = b0+b1*x1+b2*x2                 b0 b1 b2 Sb0 Sb1 Sb2 2,9619489 0,1241889 3,5538428 1,89297977 0,021231 1,01465             Sy r2 F -статист Кол.ст.св Ss рег Ss ост 1,7407109 0,9777126 175,47354 1063,396 24,2406               Определение Ттабл и Fтабл                 Al = 0,05   Выводы:1)Если Тbi > Tтабл,       то коэффициент bi - статис-       тически значим.   Fтабл Ттабл   2)Если F-статист > Fтабл,       то коэффициент детерми- 4,4589683 2,3060056   нации r2- cтатистически зна-       чим. Общее качество моде-       ли высокое.

Рис.2.5

	№ изм.	Yфакт	Yмод
			22,48852
			23,73041
			31,00992
			28,69796
			33,49369
			37,04754
			39,53131
			38,46125
			45,74076
			51,77838
			53,02027
	Определение Т-стат. для коэффициентов bi
		и доверительных интервалов
		b0	b1	b2
Т-статистика	1,56470182	5,84952	3,5025331
Нижн.гран.дов.инт.	-1,4032731	0,075231	1,2140558
Верх.гран.дов.инт.	7,32717092	0,173147	5,8936299

Рис.2.6

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

Установка пакета анализа достаточно подробно описана в п. 1.2. В диалоговом окне Анализ данных в списке Инструменты анализа выберите строку Регрессияи заполните диалоговое окно ввода данных и параметров вывода. Результаты регрессионного анализа для данных выше использованного примерапредставлены на рис.2.7.

Рис.2.7

2.3. Контрольные задания

Задача 1. Предполагается, что объем Q предложения некоторого блага для функционирующей в условиях конкуренции фирмы зависит линейно от цены Р данного блага и заработной платы W сотрудников фирмы, производящих данное благо:

Q = ₀ + ₁ Р + ₂ W + .

Статистические данные, собранные за 12 месяцев, занесены в таблицу.

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i = 0, 1, 2;

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b _i , i =0, 1, 2;

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных P и W на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Q_прогн для прогнозных значений Р_прогн , W_прогн и определить доверительный интервал для Q_прогн;

10. Сделать обобщающие выводы по регрессионной модели.

Вариант 1.1

Q

P

W

Р_прогн = 60, W_прогн = 2, = 0,01.

Вариант 1.2

Q

P

W

Р_прогн = 75, W_прогн = 1, = 0,02.

Вариант 1.3

Q

P

W

Р_прогн = 53, W_прогн = 4, = 0,03.

Вариант 1.4

Q

P

W

Р_прогн = 28, W_прогн = 9, = 0,04.

Вариант 1.5

Q

P

W

Р_прогн = 25, W_прогн = 12, = 0,05.

Задача 2. Для объяснения изменения ВНП за 10 лет строится регрессионная модель с объясняющими переменными – потреблением (С) и инвестициями (I):

ВНП = ₀ + ₁ С + ₂ I + .

Статистические данные приведены в таблице.

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i = 0, 1, 2;

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2;

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных С и I на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной ВНП _прогн для прогнозных значений С _прогн , I _прогн и определить доверительный интервал для ВНП_прогн;

10. Сделать обобщающие выводы по регрессионной модели.

Вариант 2.1

С, млрд $	8,0	9,5	11,0	12,0	13,0	14,0	15,0	16,5	17,0	18,0
I, млрд $	1,65	1,8	2,0	2,1	2,2	2,4	2,65	2,85	3,2	3,55
ВНП, млрд $	14,0	16,0	18,0	20,0	23,0	23,5	25,0	26,5	28,5	30,5

С _прогн = 20, I _прогн = 4, = 0,02.

Вариант 2.2

С, млрд $	5,0	6,5	6,9	7,5	8,0	10,0	11,0	12,0	14,0	17,0
I, млрд $	1,2	1,4	1,6	1,9	2,0	2,4	2,5	2,7	3,0	3,4
ВНП, млрд $	10,0	10,5	11,0	13,0	14,0	15,0	18,0	21,0	24,0	28,0

С _прогн = 20, I _прогн = 4, = 0,03.

Вариант 2.3

С, млрд $	7,0	8,0	9,0	10,5	11,0	12,5	13,0	14,0	15,0	16,0
I, млрд $	1,2	1,4	1,5	1,6	1,8	2,0	2,2	2,4	2,7	2,9
ВНП, млрд $	12,0	14,0	15,0	16,0	18,0	22,0	23,0	23,5	25,0	26,0

С _прогн = 14,5, I _прогн = 3, = 0,04.

Вариант 2.4

С, млрд $	6,0	7,5	9,0	10,0	11,0	12,0	13,0	14,0	15,6	16,8
I, млрд $	1,4	1,5	1,7	1,8	2,0	2,1	2,2	2,4	2,6	3,0
ВНП, млрд $	11,0	13,0	15,0	16,0	18,0	20,0	23,0	23,5	25,0	26,5

С _прогн = 18,0, I _прогн = 3,2, = 0,05.

Вариант 2.5

С, млрд $	9,0	10,5	12,0	13,0	14,0	15,0	16,5	17,5	18,0	18,5
I, млрд $	1,4	1,6	2,1	2,2	2,4	2,7	2,9	3,3	3,6	4,0
ВНП, млрд $	13,0	16,0	20,0	23,0	23,5	25,0	27,0	29,0	31,0	33,0

С _прогн = 19,0, I _прогн = 4,0, = 0,06.

Задача 3. В таблице даны статистические данные о расходах на питание Y, душевом доходе Х₁ и размере семьи Х₂ для девяти групп семей. Построить регрессионную модель:

Y= ₀ + ₁ Х₁ + ₂ Х₂ + .

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i = 0, 1, 2.

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2.

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных Х₁ и Х₂ на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Y_прогн для прогнозных значений Х_{1 прогн} , Х_{2 прогн} и определить доверительный интервал для Y_прогн;

10. Сделать обобщающие выводы по регрессионной модели.

Вариант 3.1

№	Расход на питание Y, у.е.	Душевой доход Х1, у.е.	Размер семей Х2
			1,4
			2,0
			2,5
			3,1
			3,2
			3,4
			3,6
			3,9
			3,6

Х_{1 прогн} = 8000, Х_{2 прогн} = 4,0, = 0,01.

Вариант 3.2

№	Расход на питание Y, у.е.	Душевой доход Х1, у.е.	Размер семей Х2
			1,2
			2,0
			2,4
			2,4
			3,0
			3,2
			3,4
			3,6
			3,6

Х_{1 прогн} = 10000, Х_{2 прогн} =3,0, = 0,02.

Вариант 3.3

№	Расход на питание Y, у.е.	Душевой доход Х1, у.е.	Размер семей Х2
			1,0
			1,6
			2,0
			2,0
			2,3
			2,8
			3,0
			3,2
			3,6

Х_{1 прогн} = 8000, Х_{2 прогн} =3,0, = 0,03.

Вариант 3.4

№	Расход на питание Y, у.е.	Душевой доход Х1, у.е.	Размер семей Х2
			1,1
			1,8
			2,0
			2,1
			2,4
			2,4
			2,6
№	Расход на питание Y, у.е.	Душевой доход Х1, у.е.	Размер семей Х2
			3,0
			3,0

Х_{1 прогн} = 13000, Х_{2 прогн} =3,2, = 0,04.

Вариант 3.5

№	Расход на питание Y, у.е.	Душевой доход Х1, у.е.	Размер семей Х2
			1,7
			2,0
			2,4
			2,8
			2,9
			3,0
			3,1
			3,0
			3,5

Х_{1 прогн} = 11000, Х_{2 прогн} =3,0, = 0,05.

Задача 4. Изучается влияние стоимости основных и оборотных средств на величину валового дохода торговых предприятий. Для этого по 12 торговым предприятиям были получены данные приведенные в таблице. Построить регрессионную модель:

Y= ₀ + ₁ Х₁ + ₂ Х₂ + .

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i = 0, 1, 2.

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2.

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных Х₁ и Х₂ на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Y_прогн для прогнозных значений Х_{1 прогн} , Х_{2 прогн} и определить доверительный интервал для Y_прогн;

10. Сделать обобщающие выводы по регрессионной модели.

Вариант 4.1

Номер предприятия	Валовый доход за год, млн.руб.	Среднегодовая стоимость, млн.руб.
Основных фондов	Оборотных средств

Х_{1 прогн} = 140, Х_{2 прогн} =58, = 0,01.

Вариант 4.2

Номер предприятия	Валовый доход за год, млн.руб.	Среднегодовая стоимость, млн.руб.
Основных фондов	Оборотных средств

Х_{1 прогн} = 150, Х_{2 прогн} =100, = 0,02.

Вариант 4.3

Номер предприятия	Валовый доход за год, млн.руб.	Среднегодовая стоимость, млн.руб.
Основных фондов	Оборотных средств

Х_{1 прогн} = 100, Х_{2 прогн} =80, = 0,03.

Вариант 4.4

Номер предприятия	Валовый доход за год, млн.руб.	Среднегодовая стоимость, млн.руб.
Основных фондов	Оборотных средств

Х_{1 прогн} = 90, Х_{2 прогн} =50, = 0,04.

Вариант 4.5

Номер предприятия	Валовый доход за год, млн.руб.	Среднегодовая стоимость, млн.руб.
Основных фондов	Оборотных средств

Х_{1 прогн} = 50, Х_{2 прогн} =60, = 0,05.

Задача 5. Имеются данные по странам за 2005 год. Построить регрессионную модель:

Y= ₀ + ₁ Х₁ + ₂ Х₂ + .

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i= 0, 1, 2.

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2.

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных Х₁ и Х₂ на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Y_прогн для прогнозных значений Х_{1 прогн} , Х_{2 прогн} и определить доверительный интервал для Y_прогн;

10. Сделать обобщающие выводы по регрессионной модели.

Вариант 5.1

Страна	Индекс человеческого развития, Y	Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х1	Суточная калорийность питания населения, ккал на душу, Х2
Австрия	0,904	77,0
Австралия	0,922	78,2
Аргентина	0,827	72,9
Белоруссия	0,763	68,0
Бельгия	0,923	77,2
Бразилия	0,739	66,8
Великобритания	0,918	77,2
Венгрия	0,795	70,9
Германия	0,906	77,2

Х_{1 прогн} = 80, Х_{2 прогн} =3500, = 0,01.

Вариант 5.2

Страна	Индекс человеческого развития, Y	Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х1	Суточная калорийность питания населения, ккал на душу, Х2
Греция	0,867	78,1
Дания	0,905	75,7
Египет	0,616	66,3
Израиль	0,883	77,8
Индия	0,545	62,6
Испания	0.894	78,0
Италия	0,900	78,2
Канада	0,932	79,0
Казахстан	0,740	67,7

Х_{1 прогн} = 75, Х_{2 прогн} =3000, = 0,02.

Вариант 5.3

Страна	Индекс человеческого развития, Y	Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х1	Суточная калорийность питания населения, ккал на душу, Х2
Китай	0,701	69,8
Латвия	0,744	68,4
Нидерланды	0,921	77,9
Норвегия	0,927	78,1
Польша	0,802	72,5
Корея	0,852	72,4
Россия	0,747	66,6
Румыния	0,752	69,9
США	0,927	76,6

Х_{1 прогн} = 72, Х_{2 прогн} =3500, = 0,03.

Вариант 5.4

Страна	Индекс человеческого развития, Y	Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х1	Суточная калорийность питания населения, ккал на душу, Х2
Турция	0,728	69,0
Украина	0,721	68,8
Финляндия	0,913	76,8
Франция	0,918	78,1
Чехия	0,833	73,9
Швейцария	0,914	78,6
Швеция	0,923	78,5
ЮАР	0,695	64,1
Япония	0,924	80,0

Х_{1 прогн} = 76, Х_{2 прогн} =3100, = 0,04.

Вариант 5.5

Страна	Индекс человеческого развития, Y	Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х1	Суточная калорийность питания населения, ккал на душу, Х2
Австрия	0,904	77,0
Белоруссия	0,763	68,0
Греция	0,867	78,1
Казахстан	0,740	67,7
Китай	0,701	69,8
США	0,927	76,6
Турция	0,728	69,0
Франция	0,918	78,1
ЮАР	0,695	64,1

Х_{1 прогн} = 73, Х_{2 прогн} =3300, = 0,05.

Задача 6. Изучается зависимость средней ожидаемой продолжительности Y (лет) от ВВП в паритетах покупательной способности Х₁ и коэффициента младенческой смертности Х₂ (%). Построить регрессионную модель:

Y= ₀ + ₁ Х₁ + ₂ Х₂ + .

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i= 0, 1, 2.

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2.

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных Х₁ и Х₂ на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Y_прогн для прогнозных значений Х_{1 прогн} , Х_{2 прогн} и определить доверительный интервал для Y_прогн;

10. Сделать обобщающие выводы по регрессионной модели.

Вариант 6.1

Страна	Y	Х1	Х2
Мозамбик		3,0
Индия		5,2
Бенин		6,5
Шри-Ланка		12,1
Египет		14,2
Тунис		18,5
Белоруссия		15,6
Бразилия		20,0
Тринидад		31,9
Австралия		70,2
Италия		73,7
Швейцария		95,9

Х_{1 прогн} = 80, Х_{2 прогн} =5, = 0,01.

Вариант 6.2

Страна	Y	Х1	Х2
Бурунди		2,3
Того		4,2
Никарагуа		7,4
Конго		7,6
Индонезия		14,1
Парагвай		13,5
Перу		14,0
Мавритания		49,0
Малайзия		33,4
Израиль		61,1
Канада		78,3
Япония		82,0

Х_{1 прогн} = 76, Х_{2 прогн} =10, = 0,02.

Вариант 6.3

Страна	Y	Х1	Х2
Чад		2,6
Кения		5,1
Гана		7,4
Камерун		7,8
Страна	Y	Х1	Х2
Филиппины		10,6
Алжир		19,6
Таиланд		28,0
Мексика		23,7
Чили		35,3
Ирландия		58,1
Финляндия		65,8
Дания		78,7

Х_{1 прогн} = 81, Х_{2 прогн} =4, = 0,03.

Вариант 6.4

Страна	Y	Х1	Х2
Непал		4,3
Нигерия		4,5
Ангола		4,9
Китай		10,8
Марокко		12,4
Ямайка		13,1
Панама		22,2
ЮАР		18,6
Уругвай		24,6
Нов. Зеландия		60,6
Гонконг		85,1
СЩА		100,0

Х_{1 прогн} = 93, Х_{2 прогн} =7, = 0,04.

Вариант 6.5

Страна	Y	Х1	Х2
Буркина-Фасо		2,9
Мали		2,0
Пакистан		8,3
Гондурас		7,0
Новая Гвинея		9,0
Доминик. Респ.		14,3
Турция		20,7
Венесуэла		29,3
Страна	Y	Х1	Х2
Аргентина		30,8
Испания		53,8
Швеция		68,7
Австрия		78,8

Х_{1 прогн} = 84, Х_{2 прогн} =5, = 0,05.

Задача 7. Данные о деятельности крупнейших компаний США представлены в таблице. Построить регрессионную модель: