2.3. Решение уравнений регрессии в Excel графическим способом
Попробуем повысить точность нашего прогноза, используя алгоритм действий № 1 «Как строить диаграммы в Microsoft Excel». С этой целью обведем с помощью мышки столбец с ежемесячными данными (на конец месяца) по курсу пары рубль – доллар за период с июня 1992 г. по апрель 2010 г. и столбец с соответствующими обозначениями месяцев. Выбрав опцию График, строим соответствующую диаграмму, а затем щелкаем с помощью мышки по линии графика и выбираем в появившемся окне опцию ДОБАВИТЬ ЛИНИЮ ТРЕНДА (см. рис. 2.3).
Рис. 2.3. Построение линии тренда на основе графика динамики курса доллара
Далее появляется мини-окно ФОРМАТ ЛИНИИ ТРЕНДА, в котором мы можем выбрать соответствующие ПАРАМЕТРЫ ЛИНИИ ТРЕНДА (рис. 2.3), необходимые для построения прогностических моделей. При этом воспользуемся всеми имеющимися в Excel форматами тренда за одним единственным исключением: из полиномиальных трендов возьмем тренды не выше третьей степени. В научной литературе обычно не рекомендуют использовать для аппроксимации фактических данных более сложные полиномы, поскольку они плохо поддаются интерпретации и ‑ несмотря на высокий коэффициент детерминации (по включенной в статистическую модель базе данных) ‑ обладают низкой прогностической ценностью.
Рис. 2.4. Определение характера и параметров линии тренда
Сначала построим самый простой линейный тренд. С этой целью выберем в мини-окне ФОРМАТ ЛИНИИ ТРЕНДА в опции ПАРАМЕТРЫ ЛИНИИ ТРЕНДА формат тренда ‑ ЛИНЕЙНАЯ. При этом поставим галочку в опциях ПОКАЗЫВАТЬ УРАВНЕНИЕ НА ДИАГРАМММЕ, ПОМЕСТИТЬ НА ДИАГРАММУ ВЕЛИЧИНУ ДОСТОВЕРНОСТИ АППРОКСИМАЦИИ (R^2). В результате получим диаграмму на рис. 2.5, показывающую линейный тренд, то есть линейную зависимость роста курса доллара от времени (от порядкового номера месяца, при июне 1992 год =1).
Рис. 2.5. Ежемесячный курс доллара и его тренд: по оси абсцисс вместо названий месяцев даны их порядковые номера (июнь 1992 г. =1, июль 1992 г. =2 … апрель 2010 г. =215)
Поочередно, задавая различные параметры тренда и сравнивая коэффициенты детерминации, составим таблицу 2.7, в которой разместим – по мере роста коэффициента детерминации ‑ прогностические модели с различным форматом тренда. Причем, наиболее высокий коэффициент детерминации у нас получился у уравнения регрессии, полученного путем аппроксимации по степенному тренду. В этом случае R² оказался равен 0,919136, то есть данное уравнение регрессии объясняет 91,91 % всех ежемесячных колебаний курса доллара. Соответственно, доля случайной компоненты оказалась равна = 100% -91,91 %=8,09%.
Для того чтобы правильно интерпретировать уравнения регрессии, полученные графическим способом, необходимо иметь в виду, что в процессе построения тренда программа Excel автоматически задает в качестве зависимой переменной y – ежемесячный курс доллара, а в качестве независимой х – порядковый номер месяца. Например, экономическая интерпретация уравнения регрессии со степенной функцией y = 0,0443609x1,2807295 следующая: курс доллара в период с июня 1992 г. по апрель 2010 г. ежемесячно рос со средней скоростью 28,07 % при исходном уровне 4,44 коп.
Таблица 2.7 «Параметры тренда и величина коэффициента детерминации R2»
Как мы уже убедились, графический способ решения уравнения регрессии в программе Excel позволяет довольно существенно экономить время. Однако у этого способа есть и один весьма существенный недостаток, обусловленный тем, что в данном случае не проводится оценка статистической значимости, как в целом уравнения регрессии, так и его коэффициентов.
Таким образом, графический способ решения уравнения регрессии целесообразно использовать на этапе предварительного отбора уравнений регрессии, имеющих наиболее высокий коэффициент детерминации. После отбора уравнения регрессии с высоким коэффициентом детерминации, в Excel его нужно решить, используя в Пакете анализа опцию РЕГРЕССИЯ – см. алгоритм действий № 3. Однако решение уравнение регрессии, аппроксимирующего фактические данные степенным трендом, имеет определенную специфику. В отличие от линейного тренда уравнение регрессии решается не относительно имеющихся исходных данных, а по отношению к их логарифмам. Объясняется это тем, что уравнение регрессии со степенным трендом относится по оцениваемым параметрам к нелинейным моделям, но его можно привести к линейному виду.
В результате уравнение регрессии для степенного тренда (см. табл. 2.7) приобретет следующий вид (2.22):
Следует иметь в виду, что приведение нелинейной функции к линейному виду с помощью логарифмирования используется очень часто, хотя это и приводит к некоторым коллизиям. Вот что пишут по этому поводу Е. М. Четыркин и И.Л. Калихман: «Однако такое преобразование приводит к тому, что оценка параметров базируется не на минимизации суммы квадратов отклонений, а на минимизации суммы квадратов отклонений в логарифмах. …Следствием этого является некоторое смещение оценок параметров, получаемых обычным (линейным) МНК». (см. Четыркин Е. М., Калихман И.Л. Вероятность и статистика. – М.: Финансы и статистика, 1982, стр. 255).
Далее параметры этого уравнения регрессии находятся согласно формулам (2.1.4) и (2.1.5), либо решаются с помощью соответствующей компьютерной программы.
Поэтому прежде чем приступить к выполнению алгоритма действий № 3 «Как решить уравнение регрессии в Excel», нужно взять натуральные логарифмы (логарифмы, основанием которых служит число e= 2,71828), как от независимой переменной х – порядковый номер месяца, так и от зависимой переменной у – курс доллара. В Excel для этих целей можно воспользоваться функцией LN. Далее поступаем в полном соответствии с алгоритмом действий № 3, а данные, полученные после решения уравнения регрессии, занесем в таблицу 2.8.
Таблица 2.8. ВЫВОД ИТОГОВ уравнения регрессии, полученного после логарифмирования исходных данных
Согласно алгоритму действий № 4 «Оценка статистической значимости уравнения регрессии и его коэффициентов», проведем проверку статистической значимости данного уравнения регрессии. При этом выделим в таблице 2.8 все важнейшие пункты жирным шрифтом. В результате мы пришли к выводу, что у нас получилось статистически значимым как уравнение регрессии, так и его коэффициенты, как при 95% , так и 99% уровнях надежностях. Правда, поскольку данное уравнение регрессии мы решили относительно натуральных логарифмов, взятых от исходных данных, то в результате оно приобрело следующий вид:
Ln Y=-3,1154+1,28073 lnX
Согласно последнему уравнению регрессии, прогноз курса доллара рассчитывается на основе логарифмов, взятых от исходных данных. Например, прогноз относительно апреля 2010 г. вычисляется следующим образом:
Ln Y=-3,1154+1,28073* 5,370638= 3,762939; где 5,370638=ln(215) – натуральному логарифму от порядкового номера апреля 2010 г. =215.
Отсюда находим (в Excel потенцирование натуральных логарифмов производится с помощью функции EXP), прогноз курса доллара на апрель 2010 равен:
Y=EXP(3,762939)= 43,07482
После проведения соответствующих преобразований, вышеуказанное уравнение регрессии приобретет следующий вид:
Y=EXP(-3,1154 + 1,28073 lnX)= 0,044361*X^1,28073
С помощью последнего уравнения регрессии можно делать расчет прогнозов непосредственно от исходных данных, а не от их натуральных логарифмов. В результате прогноз курса на доллара на апрель 2010 г. можно вычислить следующим образом:
Y=0,044361*215^1,28073; где 215 – порядковый номер апреля 2010 г. (при июне 1992 г. =1).
Несмотря на то, что коэффициент детерминации у степенного уравнения регрессии выше, чем у линейного, однако, например, относительно апреля 2010 г. прогноз по данному уравнению регрессии весьма сильно отклоняется от фактического курса доллара, как впрочем, и во многих других случаях. Судя по таблице 2.9, с января 2009 г. по апреля 2010 г. отклонения от прогноза (остатки), сделанного по уравнению регрессии yрасч.= 0,044361*X^1,28073, колебались в диапазоне от -3,7954 руб. до -13,7862 руб., что свидетельствует о невысокой точности данной прогностической модели.
Таблица 2.9. Прогноз по степенному уравнению регрессии, фактический курс доллара и остатки с января 2009 г. по апрель 2010 г.
При этом средняя абсолютная ошибка прогноза по модулю для степенной статистической модели – см. формулу (2.20) – оказалась равна 5 рублям 92, 4 копейкам. Следовательно, этот показатель у данной модели оказался на 30 коп. выше, чем у линейной модели. В свою очередь, средняя относительная ошибка по модулю в процентах ‑ см. формулу (2.21) – для степенной модели оказалась равна 31,10 процентам, то есть на 7,78 процентных пункта ниже, чем у линейной модели. Более того, если построить график остатков по степенной прогностической модели (см. рис. 2.3), то легко обнаружить, что на нем наблюдается несколько локальных трендов. А это – как мы говорили ранее – наглядно свидетельствует о нестационарности остатков.
Рис. 2.6. Нестационарность остатков, полученных по степенной прогностической модели
Отсюда можно сделать вывод, что не только линейная модель, но и степенная модель, в которой в качестве независимой переменной использовался фактор времени (порядковый номер месяца), оказались непригодны для прогнозирования курса доллара. Все это заставляет нас продолжить поиск адекватной прогностической модели.