Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Excel и EViews

b — линейный коэффициент регрессии, показывающий, как изменение величины независимой переменной (фактора) Т в среднем способствует изменению зависимой переменной (результативного признака) Y,

Т_расч — расчетное значение результативного признака, вычисляемое по формуле 2.2.

Минимизируем сумму квадратов отклонений (остатков) Y_факт от Y_pасч, т. е. фактических значений курса доллара от его расчетных значений. В результате формулу МНК (2.1.1) для линейной регрессии можно представить в следующем виде:

Уравнение 2.3, в принципе, можно решить самостоятельно, если найти его параметры согласно формулам (2.1.4) и (2.1.5), но в целях ускорения этого процесса будем его решать с помощью Пакета анализа Excel. Кстати, желающие лучше усвоить суть МНК могут сначала самостоятельно в «ручном режиме» решить уравнение регрессии, а затем сверить свои результаты с теми, что мы получим в Excel.

Чтобы подготовить исходные данные к решению уравнения регрессии, разместим в Excel два столбца исходных данных. В первом столбце, который озаглавим Time, поместим порядковые номера месяцев, начиная с июня 1992 г. (с номером 1) и кончая апрелем 2010 г. (с номером 215). Во втором столбце, который озаглавим USDollar, поместим данные по курсу доллара на конец месяца, начиная с июня 1992 г. и заканчивая апрелем 2010 г.[3] Таким образом, столбец Time представляет собой независимую переменную, которая в формуле (2.2) обозначена символом Т, а столбец USDollar является зависимой переменной Y_факt. Далее переходим к решению уравнения регрессии в Пакете анализа Excel согласно алгоритму действий № 3.

Алгоритм действий № 3

Как решить уравнение регрессии в Excel

Шаг 1. Ввод в уравнение исходных данных

Сначала в Microsoft Excel 2007 в верхней панели инструментов выбирается опция ДАННЫЕ (в Microsoft Excel 1997–2003 нужно выбрать опцию СЕРВИС), потом в появившемся окне АНАЛИЗ ДАННЫХ — опция РЕГРЕССИЯ. После чего появляется новое окно РЕГРЕССИЯ (рис. 2.1), в котором в графе ВХОДНОЙ ИНТЕРВАЛ У выделяем (с помощью мышки) столбец данных USDollar (ячейки $С$1:$С$216). Здесь же в графе ВХОДНОЙ ИНТЕРВАЛ Xвыделяем столбец данных Time (ячейки $В$1:$В$216), т. е. независимую переменную Т из нашего уравнения регрессии (2.2).

Шаг 2. Дополнительные опции

Если бы мы хотели получить уравнение регрессии без свободного члена, который в формуле (2.2) обозначен символом а, то тогда нам следовало бы выбрать еще и опцию КОНСТАНТА-НОЛЬ. Но пока в использовании этой опции нет необходимости.

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

Опцию МЕТКИ применяют, чтобы переменные, включенные в уравнение регрессии, в ВЫВОДЕ ИТОГОВ были обозначены в виде заголовков соответствующих столбцов.

По умолчанию оценка в Excel параметров уравнения регрессии делается с 95 %-ным уровнем надежности. Однако в случае необходимости в опции УРОВЕНЬ НАДЕЖНОСТИ можно поставить цифру 99, что означает задание для программы оценить коэффициенты регрессии с 99 %-ным уровнем надежности. В результате в ВЫВОДЕ ИТОГОВ мы получим данные, характеризующие как в целом уравнение регрессии, так и верхние и нижние интервальные оценки коэффициентов уравнения с 95 %-ным и 99 %-ным уровнями надежности. При 95 %-ном уровне надежности существует риск, что в 5 % случаях оценки коэффициентов уравнения регрессии могут оказаться статистически незначимыми, а при 99 %-ном уровне надежности этот риск равен 1 %.

Шаг 3. Вывод итогов

На заключительном этапе выбираем в параметрах вывода (окно РЕГРЕССИЯ) опцию ВЫХОДНОЙ ИНТЕРВАЛ, в которой указываем соответствующую ячейку Excel ($Н$2), далее щелкаем по надписи ОК и получаем ВЫВОД ИТОГОВ (см. рис. 2.1, где можно увидеть все заданные нами параметры уравнения регрессии). В случае необходимости вывод итогов можно получить на отдельном листе (опция НОВЫЙ РАБОЧИЙ ЛИСТ) или в новой книге Excel (опция НОВАЯ РАБОЧАЯ КНИГА).

Результаты решения уравнения регрессии, которые в программе Excel выдаются в виде единой таблицы под заголовком ВЫВОД ИТОГОВ, у нас представлены в виде трех блоков (табл. 2.2–2.4). Так, в табл. 2.2 сгенерированы результаты по регрессионной статистике, в табл. 2.3 дается дисперсионный анализ, а в табл. 2.4 оценивается статистическая значимость коэффициентов регрессии.

Параметры, представленные в табл. 2.2, характеризуют уровень аппроксимации фактических данных, полученный с помощью уравнения регрессии. Так, параметр МНОЖЕСТВЕННЫЙ R обозначает коэффициент множественной корреляции R, который характеризует тесноту связи между результативным признаком Y и факторами переменных Д, Х₂…., Х_n. Этот коэффициент изменяется в пределах от 0 до 1, причем чем ближе к 1, тем теснее корреляционная связь между переменными, включенными в уравнение регрессии. Коэффициент множественной корреляции равен квадратному корню, извлеченному из коэффициента детерминации R2, который также приводится в регрессионной статистике. Коэффициент множественной корреляции R находят по формуле:

Зная величину коэффициента корреляции R, можно дать качественную оценку силы связи между зависимой и независимыми переменными, включенными в уравнение (2.5). С целью классификации силы связи обычно используют шкалу Чеддока (табл. 2.1).

Если между переменными существует функциональная связь, то R= 1, а если корреляционная связь отсутствует, то R = 0. Поскольку в табл. 2.2 коэффициент множественной корреляции Нравен 0,8456, то, согласно шкале Чеддока, связь между переменными, включенными в уравнение регрессии, можно считать высокой. Следует также заметить, что если коэффициент множественной корреляции меньше 0,7, то это означает, что величина коэффициента детерминации R2 будет меньше 50 %, а потому регрессионные модели с таким коэффициентом детерминации не имеют большого практического значения.

Однако самым важным является другой параметр регрессионной статистики — R-КВАДРАТ (в табл. 2.2 он выделен шрифтом), обозначающий коэффициент детерминации R2. Коэффициент детерминации R2 характеризует долю дисперсии результативного признака У, объясняемую уравнением регрессии, в общей дисперсии результативного признака. Коэффициент детерминации R2 находится по следующей формуле:

Коэффициент детерминации R2, как и коэффициент множественной корреляции R, изменяется в пределах от нуля до единицы. Если R2 равен единице, то доля объясненной дисперсии составляет 100 %, а следовательно, связь между зависимой переменной Y и независимыми переменными Х₁, Х₂…., X₁ носит функциональный характер. В том случае, когда R2 равен нулю, какая-либо связь между переменными в этом уравнении регрессии отсутствует.

Величина коэффициента детерминации R2 является одним из важнейших критериев при оценке качества уравнения регрессии. Так, при выборе из нескольких уравнений регрессии предпочтение (при прочих равных условиях) отдается тому, у которого коэффициент детерминации R2 ближе к единице. И это вполне понятно: чем выше коэффициент детерминации уравнения регрессии, тем выше у него уровень аппроксимации и соответственно ниже доля необъясненной дисперсии. В нашем случае коэффициент детерминации R2 = 0,7151, а потому можно сделать вывод, что в период с июня 1992 г. по апрель 2010 г. 71,51 % ежемесячных колебаний курса доллара (зависимая переменная Y), согласно уравнению регрессии, объяснялись изменением порядкового номера месяца (независимая переменная 7).

Другой параметр регрессионной статистики — НОРМИРОВАННЫЙ R-КВАДРАТ. Дело в том, что при добавлении в уравнение регрессии дополнительных факторов (независимых переменных) величина коэффициента детерминации R2 соответственно растет. Поэтому чтобы сделать сравнения коэффициентов детерминации между уравнениями регрессии с разным числом факторов более сопоставимыми, используется нормированный R2, величина которого корректируется в сторону уменьшения при добавлении в уравнение дополнительных факторов. В Пакете анализа Excel нормированный R2 вычисляют по формуле:

В нашем случае

Еще один параметр регрессионной статистики — СТАНДАРТНАЯ ОШИБКА, или остаточное стандартное отклонение, которое можно найти по следующей формуле:

НАБЛЮДЕНИЯ — этот параметр регрессионной статистики показывает число наблюдений п, равное 215 (т. е. числу месяцев с июня 1992 г. по апрель 2010 г., по которым у нас есть данные).

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

При этом столбцы этой таблицы имеют следующую интерпретацию.

1. Столбец df (degrees of freedom) сообщает число степеней свободы. Причем для строки РЕГРЕССИЯ число степеней свободы равно

количеству факторов k_факт, включенных в уравнение регрессии. В нашем случае df_регр = k = 1.

Для строки ОСТАТОК число степеней свободы определяется числом наблюдений и количеством факторов, включенных в уравнении регрессии. При этом df_ост находится по следующей формуле:

где п — число наблюдений; к — количество факторов.

В нашем случае df_ост = 215 — (1 + 1) = 213.

Для строки ИТОГО число степеней свободы находится по следующей формуле:

В нашем случае df_итого = 1 + 213 = 214.

2. Столбец SS означает сумму квадратов отклонений.

Для строки РЕГРЕССИЯ этот столбец обозначает сумму квадратов отклонений рассчитанных (предсказанных) значений результативного признака от его среднего, рассчитанного по фактическим данным:

Для строки ОСТАТОК столбец SS обозначает сумму квадратов отклонений фактических данных от их расчетных значений:

Для строки ИТОГО столбец SS обозначает сумму квадратов отклонений фактических данных от их среднего:

SS2_итого можно также найти, сложив SS2_регр с SS2_ост: 21 779,45 + 8676,619 = 30 456,07.

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

Для строки РЕГРЕССИЯ — это факторная, или объясненная, дисперсия:

D_факт = МS_факт = 21 779,45/1 = 21 779,45.

Для строки ОСТАТОК — это остаточная дисперсия:

D_ост = MS_ост= 8676,619/213 = 40,7353.

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

Если нулевая гипотеза (об отсутствии связи между переменными, включенными в уравнение регрессии) справедлива, то факторная и остаточная дисперсия не отличаются друг от друга. Чтобы уравнение регрессии было признано значимым, требуется опровержение нулевой гипотезы, а для этого необходимо, чтобы факторная дисперсия превышала остаточную дисперсию в несколько раз. Статистиками разработаны соответствующие таблицы критических значений F-критерия при разных уровнях значимости нулевой гипотезы и различном числе степеней свободы. При этом следует иметь в виду, что табличное значение F-критерия — это максимальная величина отношения факторной дисперсии к остаточной дисперсии, которая может иметь место при случайном их расхождении для данного уровня вероятности наличия нулевой гипотезы. Если фактический (т. е. рассчитанный для этого уравнения регрессии) F-критерий больше его табличного значения, то нулевая гипотеза об отсутствии связи между результативным признаком и факторами отклоняется и делается вывод о существенности этой связи.

5. В столбце ЗНАЧИМОСТЬ F дается уровень значимости, который соответствует величине фактического F-критерия Фишера, вычисленного для этого уравнения регрессии. В нашем случае значимость F_факт практически равна нулю, т. е. F_факт больше F_табл (значения F-критерия Фишера при уровне значимости 0,05 или 5 % можно найти в любом учебнике по статистике) при 1 %-ном и 5 %-ном уровне значимости. Отсюда можно сделать вывод о статистической значимости уравнения регрессии, поскольку связь между включенными в него факторами в этом случае доказана.

В тех случаях, когда значимость F бывает больше, например, 0,01, но меньше 0,05, то тогда делается вывод, что F_факт меньшеF_табл при 1 %-ном уровне значимости, но больше F_табл при 5 %-ном уровне значимости. Следовательно, в этой ситуации нулевая гипотеза об отсутствии связи между результативным признаком и факторами, включенными в уравнение регрессии, на 1 %-ном уровне значимости не отклоняется, но отклоняется на 5 %-ном уровне значимости. Таким образом, в этом случае каждый исследователь должен сам решить, считать ли 5 %-ный уровень значимости F-критерия достаточным для того, чтобы сделать вывод о статистической значимости уравнения регрессии. При этом следует иметь в виду, что если значимость F-критерия выше 0,05, т. е. F_факт меньше F_табл при 5 %-ном уровне значимости, то в этой ситуации уравнение регрессии, как правило, считается статистически незначимым.

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

1. В столбце КОЭФФИЦИЕНТЫ представлены коэффициенты уравнения регрессии. На пересечении этого столбца со строкой Y-ПЕРЕСЕЧЕНИЕ дан свободный член, который в формуле линейного уравнения регрессии (2.2) обозначен символом а = 1,995805.

Во второй строке этого столбца, обозначенной как Time (независимая переменная — порядковый номер месяца), сгенерирован коэффициент уравнения регрессии, который в формуле (2.2) представлен символом b = 0,162166.

Таким образом, данные, представленные в столбце Коэффициенты, дают нам возможность составить путем подстановки соответствующих цифр в формулу (2.2) следующее уравнение линейной парной регрессии:

Y = 0,1622Х + 1,9958,

где независимая переменная X означает порядковый номер месяца (июнь 1992 г. — 1, а апрель 2010 г. — 215);

зависимая переменная Y — ежемесячное значение курса доллара.

При этом экономическая интерпретация этого линейного уравнения следующая: в период с июня 1992 г. по апрель 2010 г. курс доллара к рублю ежемесячно рос со средней скоростью 16,22 коп. при исходном уровне временного ряда в размере 1 руб. 99,58 коп. В свою очередь геометрическая интерпретация этого линейного уравнения следующая: свободный член уравнения 1,9958 показывает точку пересечения линии тренда с осью Y, а коэффициент уравнения 0,1622х равен углу наклона линии тренда к оси Х(см. рис. 2.5).

2. В столбце СТАНДАРТНАЯ ОШИБКА сгенерированы стандартные ошибки свободного члена и коэффициента регрессии, значения которых даны во втором столбце табл. 2.4. При этом стандартная ошибка свободного члена уравнения регрессии находится по следующей формуле:

где MS_ост = D_ост — остаточная дисперсия, приходящаяся на одну степень свободы.

Для нашего случая стандартная ошибка свободного члена уравнения регрессии равна

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

Для нашего случая стандартная ошибка коэффициента регрессии имеет следующее значение:

3. В столбце t-СТАТИСТИКА даны расчетные значения /-критерия. При этом для свободного члена /-статистика вычисляется по формуле

где а — свободный член уравнения.

В нашем случае t-статистика находится следующим образом:

Для коэффициента регрессии t-статистика рассчитывается по формуле

где b — коэффициент регрессии.

Тогда Z-статистика находится следующим образом:

4. В столбце Р-ЗНАЧЕНИЕ сгенерированы уровни значимости, соответствующие значениям t-статистики.

В Excel Р-значение находится с помощью следующей функции:

СТЬЮДРАСП (X = t_ст; df= п- к — 1; хвосты = 2),