Владимир Брюков - Как предсказать курс доллара. Расчеты в Excel для снижения риска проигрыша

Остаток для наблюдения 1 = 33,6306-32,2236=1,4070 рублей.

Таблица 2.5. Вывод остатка

Источник: расчеты автора

Теперь нам нужно оценить относительную точность уравнения регрессии с учетом величины полученных остатков. С этой целью построим таблицу 2.6, в которую возьмем данные из таблицы 2.5. Плюс правее раздела «Наблюдение» добавим в новую таблицу раздел «Фактический курс доллара к рублю». Кроме того, для последующих расчетов в таблицу 2.6 добавим правее раздела «Остатки» еще разделы «Остатки по модулю» и «Средняя ошибка аппроксимации, в %».

«Остатки по модулю» в Excel легко найти с помощью функции ABS. Сделать это можно двумя способами.

Первый способ. В пустой ячейке (ячейка D2), расположенной в первом ряду под заголовком столбца «Остатки по модулю» (ячейка C2), нужно напечатать английскими буквами = ABS. Сразу всплывет подсказка этой функции, которую нужно щелкнуть левой кнопкой мышки, а затем навести курсор на ячейку с остатком для наблюдения 1 и закрыть скобку этой функции. Таким образом получим следующий результат: ABS(1,4070)= 1,4070, то есть «Остаток по модулю» для наблюдения 1 равен 1,4070.

Второй способ. Нужно навести курсор на ту же пустую ячейку (ячейка D2). Потом надо щелкнуть левой кнопкой мышки иконку ФОРМУЛЫ, расположенную вверху рабочего листа Excel, а затем щелкнуть появившуюся иконку Fx. После этого на экране возникнет диалоговое окно МАСТЕР ФУНКЦИЙ, в котором внизу надписи ВЫБЕРИТЕ ФУНКЦИЮ: остается только выбрать функцию ABS ‑ см. рис. 2.3.

Рис.2.3

В результате появится окно АРГУМЕНТЫ ФУНКЦИИ ABS – см. рис. 2.4. Сразу после этого надо навести курсор мышки на ячейку с численным значением остатка для наблюдения 1 (ячейка D2), равное 1,4070. Это нам, как и при первом способе работы с функцией, позволит получить тот же результат ABS(1,4070)= 1,4070.

Рис. 2.4

Далее, перемещая курсор мышки вниз по столбцу «Остатки по модулю» (ячейки D2:D110), найдем с помощью функции ABS остатки по модулю для всех остальных 109 наблюдений – см. таблицу 2.6. Прежде чем тянуть курсор мышки вниз, нужно сначала его навести на правый угол ячейки C35 и при этом убедиться, что он приобрел форму черного крестика.

Для тех, кто не силен в математике, скажу, что функция ABS, меняя знак у отрицательных остатков, у положительных остатков его не меняет. Например, отрицательный «Остаток по модулю» для наблюдения 105 становится положительным: ABS(-0,2392)= 0,2392. Если же суммировать все остатки, не беря их по модулю, то сумма их отрицательных и положительных значений будет взаимно погашаться и будет равна нулю для уравнений регрессии со свободным членом, а без свободного члена – близка к нулю. Все это не позволяет найти среднюю ошибку аппроксимации без преобразования остатков по модулю.

Чтобы найти среднюю ошибку аппроксимации (в %) для каждого наблюдения надо относящийся к нему «Остаток по модулю» поделить на «Фактический курс доллара к рублю», а полученный результат умножить на 100. Так, для наблюдения 1, «Средняя ошибка аппроксимации»= 1,4070/33,6306*100=4,2%.

После того как мы найдем для всех наблюдений средние ошибки аппроксимации, их нужно сложить. В результате получим итоговую сумму = 311,1– см. таблицу 2.6. Потом эту сумму нужно поделить на общее количество наблюдений, то есть в данном случае на 109.

В результате выясним, что средняя ошибка аппроксимации равна 2,9%. Для справки замечу, что в статистической литературе рекомендуется делать прогнозы по статистическим моделям лишь в том случае, если средняя ошибка аппроксимации у них не превышает 7%-10%.

Таблица 2.6. Оценка средней ошибки аппроксимации, в %

Источник: расчеты автора и данные Банка России

Однако прежде чем использовать данное уравнение регрессии для прогнозирования курса доллара к рублю нужно посмотреть – нет ли автокорреляции в остатках. Автокорреляция в остатках – это наличие статистической связи между значениями полученных остатков со сдвигом (лагом или отставанием), например, в один день (или любой другой отрезок времени). При наличии автокорреляции каждый последующий остаток зависит от предшествующего.