Постановка задачи. Исследовать зависимость объема экспорта (y, усл

Исследовать зависимость объема экспорта (y, усл. ед.) от валового внутреннего продукта (ВВП) (x, усл. ед.), используя данные 20 наблюдений, приведенные в таблице 1. Построитьрегрессионную модель Спрогнозировать объем экспорта при ВВП, равном 2 500 ед.

Таблица 1 – Исходные данные для построения модели

ВВП	1 000	1 090	1 150	1 230	1 300	1 360	1 400	1 470	1 500	1 580
Экспорт

ВВП	1 600	1 630	1 700	1 780	1 800	1 850	1 910	1 990	2 010	2 100
Экспорт

Технология вычислений в MS Excel
для построения и анализа парной регрессии

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

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

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

Подготовьте данные для расчетов. В ячейку А1 введите название «ВВП», в ячейку В1 – название «Экспорт». В ячейки А2,…, А21 введите значения ВВП, в ячейки В2,…, В21 – соответствующие значения экспорта.

Переименуйте лист 1 в «Исходные данные», щелкнув правой кноп-
кой мыши на ярлыке «Лист 1» и выбрав команду Переименование или в режиме редактирования после двойного щелчка левой кнопкой мыши на ярлыке «Лист 1». Сохраните открытый файл «Книга 1» под названием «Фамилия-регрессия», например, в папке «Мои документы» (Файл ® Сохранить как ® Папка Мои документы ® Имя файла ® Фамилия-регрессия ® Сохранить).

2. Спецификация: выбор в общем виде формулы связи между переменными, обозначающими выделенные факторы

Вид и сила функциональной зависимости (линейная или нелинейная) определяются по ковариации и коэффициенту корреляции.

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

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

Выделите анализ Ковариация, нажмите кнопку ОК. Установите значения параметров в появившемся диалоговом окне следующим образом:

· Входной интервал – введите ссылки на диапазон ячеек А1:В21 (курсор установите в поле Входной интервал, указатель мыши поместите в ячейку А1, удерживая нажатой левую кнопку, протяните указатель мыши до ячейки В21).

· Группирование – флажок по столбцам устанавливается автоматически.

· Метки в первой строке – установите флажок щелчком левой кнопки мыши.

· Параметры вывода – установите флажок на Выходной интервал, поставьте курсор в поле напротив, выделите ячейку D2. Нажмите кнопку ОК.

В ячейке D1 введите слово Ковариация – название таблицы.

Примечание – В приложении А приведены примеры оформления вычислений на листах MS Excel.

В меню Сервис выберите команду Анализ данных и выделите анализ Корреляция. Установите следующим образом значения параметров:

· Входной интервал – введите ссылки на ячейки, содержащие исходные данные А1:В21 (курсор установите в поле Входной интервал, указатель мыши поместите в ячейку А1, удерживая нажатой левую кнопку, протяните указатель мыши до ячейки В21).

· Группирование – флажок по столбцам устанавливается автоматически.

· Метки в первой строке – установите флажок.

· Параметры вывода – установите флажок на Выходной интервал, поставьте курсор в поле напротив, выделите ячейку D7. Нажмите кнопку ОК.

В ячейке D6 введите слово Корреляция – название таблицы.

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

· В ячейку D11 введите обозначение tнабл.

· В ячейку E11 введите формулу

= Е9*КОРЕНЬ(20 – 2)/КОРЕНЬ(1 – Е9^2) для вычисления tнабл, где 20 – число наблюдений, 2 – число факторов.

· В ячейку D12 введите tкр.

· В ячейке E12 вычислите критическое значение tкр следующим образом:

– нажмите на f_x (вставка функций);

– в поле Категория окна Мастер функций выберите статистические, из предложенных ниже функций выделите СТЬЮДРАСПОБР и нажмите кнопку ОК. Откроется окно Аргументы функции. Заполните поля:

¨ Вероятность – наберите значение альфа, равное 0,05;

¨ Степени свободы – введите 20–2, где 20 – число наблюдений,
2 – число факторов (x, y). Нажмите кнопку ОК.

Примечания:

1. Выводы о существовании зависимости и выборе вида функции связи подробно описаны в разделе «Эконометрический анализ построения модели парной регрессии».

2. Предполагаем, что между ВВП (х) и экспортом (у) существует линейная зависимость. Далее находится уравнение парной линейной регрессии. Если доказана нелинейность зависимости, то проводится процедура линеаризации [6], [9].

3. Параметризация модели: нахождение оценок значений параметров выбранной функции связи

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

В меню Сервис выберите команду Анализ данных, укажите анализ Регрессия. Нажмите кнопку ОК. Установите в появившемся окне значения параметров следующим образом:

· Входной интервал Y – введите ссылки на ячейки В1:В21.

· Входной интервал X – введите ссылки на ячейки А1:А21.

· Метки – установите флажок.

· Уровень надежности – установите флажок.

· Константа-ноль – не активизируйте.

· Параметры вывода – установите переключатель на Новый рабочий лист и в поле напротив введите слово Регрессия.

· Остатки – установите флажок.

· Стандартизированные остатки – оставьте пустым.

· График остатков – установите флажок.

· График подбора – установите флажок.

· График нормальной вероятности – оставьте пустым.

Нажмите кнопку ОК. Расположите диаграммы рядом (на поле диаграммы нажмите левую кнопку мыши, затем поместите курсор на белое поле и при нажатой левой кнопке передвигайте диаграмму вниз) и растяните (на поле диаграммы нажмите левую кнопку мыши, нижнюю линию границы диаграммы при нажатой левой кнопке протяните вниз).

Примечание – Выводы о значениях оценок параметров уравнения регрессии подробно описаны в разделе «Эконометрический анализ построения модели парной регрессии».

4. Верификация модели: проверка адекватности модели

4.1. Общее качество уравнения: проверка значимости коэффициента детерминации

Для проверки гипотезы о значимости коэффициента детерминации сравниваются наблюдаемое значение статистики Фишера, найденное с помощью анализа Регрессия, и критическое значение которое вычислите на листе «Регрессия» в ячейке E15 следующим образом:

· нажмите на f_x (вставка функций);

· в поле Категория окна Мастер функций выберите статистические, из предложенных ниже функций выделите FРАСПОБР и нажмите кнопку ОК. Откроется окно Аргументы функции. Заполните поля:

– Вероятность – наберите значение 0,05;

– Степени свободы 1 – установите курсор в поле и выделите ячейку В12 столбца df таблицы «Дисперсионный анализ»;

– Степени свободы 2 – установите курсор в поле и выделите ячейку В13 столбца df таблицы «Дисперсионный анализ». Нажмите кнопку ОК. В ячейку D15 введите обозначение Fкр.

Примечание – Выводы о качестве уравнения подробно описаны в разделе «Эконометрический анализ построения модели парной регрессии».

4.2. Нормальность распределения остатков: устанавливается для возможности использования статистики Стьюдента при проверке гипотез (визуально по гистограмме, по асимметрии и эксцессу, с помощью проверки параметрической гипотезы)

В меню Сервис выберите команду Анализ данных, укажите инструмент анализа Гистограмма. Нажмите кнопку ОК. Значения параметров в появившемся диалоговом окне установите следующим образом:

· Входной интервал – введите ссылки на ячейки С24:С44 (ячейки со значениями остатков и названием «Остатки»).

· Интервал карманов – не заполняйте.

· Метки – установите флажок.

· Параметры вывода – установите переключатель на Новый рабочий лист и в поле напротив введите его название «Условие 1-нор-
мальность».

· Парето – оставьте пустым.

· Интегральный процент – оставьте пустым.

· Вывод графика – установите флажок. Нажмите кнопку ОК. Растяните гистограмму вниз. При необходимости ее можно перемещать.

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

Скопируйте ячейки С24:С44 листа «Регрессия» со значениями остатков и названием «Остатки» на лист «Условие 1-нормальность» в ячейки С12:С32.

Выберите в меню Сервис ® Анализ данных ® Описательная статистика, нажмите кнопку ОК. Значения параметров в диалоговом окне установите следующим образом:

· Входной интервал – введите ссылки на ячейки С12:С32.

· Группирование – установите флажок по столбцам.

· Метки в первой строке – установите флажок.

· Параметры вывода – установите переключатель на Выходной интервал, курсор установите в поле напротив, выделите ячейку А12.

· Установите флажки Итоговая статистика, Уровень надежности (95%). Нажмите кнопку ОК.

Для проверки гипотезы о нормальности распределения остатков с помощью критерия хи-квадрат Пирсона сравниваются наблюдаемое и критическое значения статистики хи-квадрат.

Рассчитайте наблюдаемое значение хи-квадрат статистики c² = , выполнив действия, приведенные ниже.

На листе «Условие 1-нормальность» удалите слово Еще в ячейке А6 и в этой же ячейке введите формулу = В24*3, нажмите клавишу Enter (т. е. значение максимума остатков увеличили в три раза).

В ячейку С1 введите значение 0 – значение функции распределения на минус бесконечности.

В ячейки С2:С6 введите формулу массива:

1) выделите ячейки С2:С6;

2) нажмите функциональную F2;

3) введите формулу = HOPMPACП(А2:А6;В14;В18;ИСТИНА);

4) нажмите комбинацию клавиш Ctrl + Shift + Enter.

Если появилось только одно значение, то нажать F2 и снова Ctrl + Shift + Enter. В строке формул при активизации любой ячейки диапазона С2:С6 появится формула в фигурных скобках:

{= HOPMPACП(А2:А6;В14;В18;ИСТИНА)}.

Примечание – В дальнейшем фраза «введите формулу массива» предполагает выполнение четырех действий: 1) выделить заполняемый диапазон ячеек; 2) нажать на клавиатуре кнопку F2; 3) ввести формулу; 4) нажать комбинацию клавиш Ctrl + Shift + Enter.

В ячейки D2:D6 введите формулу массива {= С2:C6 – С1:C5} (обратите внимание: во втором массиве ячейку C6 не включать).

В ячейки Е2:E6 введите формулу массива {= В26*D2:D6}.

В ячейки F2:F6 введите формулу массива {= (B2:В6 – Е2:Е6)^2/Е2:E6}.

В ячейку E7 введите обозначение хи-квадрат набл.

В ячейку F7 введите формулу = СУММ(F2:F6) для вычисления хи-квадрат набл.

Найдите критическое значение статистики Пирсона.

В ячейку E8 введите обозначение хи-квадрат кр.

В ячейку F8 введите формулу для вычисления хи-квадрат кр (используя Мастер функций – статистические) =ХИ2ОБР(0,05; 6 – 2 – 1), где 6 – число интервалов (6 получено как сумма числа значений в кармане и 1).

Примечание – Выводы о нормальности распределения остатков подробно описаны в разделе «Анализ эконометрической модели парной регрессии».

4.3. Значимость коэффициентов регрессии: проверка соответствующих гипотез

Для проверки гипотез о значимости коэффициентов регрессии сравниваются наблюдаемые значения t -статистики, найденные с помощью анализа Регрессия, и критическое значение, которое уже найдено на листе «Исходные данные». Для удобства дальнейшего анализа скопируйте содержимое ячеек D12:E12 листа «Исходные данные» в ячейки C19:D19 листа «Регрессия».

Примечание – Выводы о значимости коэффициентов регрессии подробно описаны в разделе «Эконометрический анализ построения модели парной регрессии».