С помощью ППП Ехсеl

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

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

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

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

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

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

Рис. 2.1

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

Для вычисления дисперсий необходимо вычислить S² в соответствие с формулой (2.10). На основании Т-статистик делается вывод о значимости коэффициентов регрессии и их доверительных интервалов.

Значения t_кр можно получить, используя статистическую функцию СТЬЮДРАСПОБР.

По соответствующим формулам вычисляются коэффициент детерминации R² и F – критерий, на основании которых делается вывод о значимости уравнения регрессии в целом. Для нахождения критической точки f_кр нужно воспользоваться функцией FРАСПОБР.

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

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

Рис. 2.2

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

S(Y_р) = S ,

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

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

Рис. 2.3

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

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

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

Рис. 2.4

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

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

Рис. 2.5

Рис. 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

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

Вариант 1.2

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

Вариант 1.3

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

Вариант 1.4

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

Вариант 1.5

Р_прогн = 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

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

Вариант 2.2

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

Вариант 2.3

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

Вариант 2.4

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

Вариант 2.5

С _прогн = 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

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

Вариант 3.2

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

Вариант 3.3

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

Вариант 3.4

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

Вариант 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

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

Вариант 5.2

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

Вариант 5.3

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

Вариант 5.4

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

Вариант 5.5

Х_{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

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

Вариант 6.2

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

Вариант 6.3

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

Вариант 6.4

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

Вариант 6.5

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

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

Y=