Авторизация

Рубрики блога


Рекомендуем



Последние комментарии

Облако тегов


Устами великих

"Странный этот мир, где двое смотрят на одно и то же, а видят полностью противоположное." © Агата Кристи

Реклама



MS Office и VBA Рубрика содержит интересные решения, малоизвестные функции и возможности, надстройки и макросы, в общем, все то, что может сделать вашу работу в пакете программ MS Office (в первую очередь - Excel, Word, Access) более эффективной.
19
Май

Авторегрессия - моделирование и прогнозирование в Excel

рейтинг материал 5.0 (10) | количество просмотров 35354 | количество коментариев 0
Урок по построению линейной авторегрессионной модели первого-третьего порядков для динамических рядов с целью прогнозирования.
Download source

Что такое авторегрессия?

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

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

Структура модели

Общий вид модели авторегрессии:
Yi = a0 + Ʃai*Yi-1i

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

ai — коэффициенты, которые описывают степень зависимости итогового Y от влияющих факторов, в данном случае, от того каким был Y в прошлом периоде регрессии;

Yi-1 — влияющие факторы, которые в данном случае и есть итоговый Y, но тот, каким он был раньше.

Ɛi — случайная компонента или как еще ее принято называть погрешность модели (по сути, это разница между расчетным значением модели за известные периоды и между самими известными значениями, то есть Yрасч. - Y).

AR I - Авторегрессия первого порядка

Yi = a0 + ai*Yi-1i

Как видно из формулы выше, линейная модель авторегрессии первого порядка состоит только из одного влияющего фактора, а именно из Y-1, то есть изучается наиболее тесная зависимость только от того каким был итоговый показатель периодом с шагом назад.

Рассмотрим построение модели с помощью "пакета анализа" в эксель (вся процедура и поочередность шагов аналогичны описанным в статье Линейная регрессия в Excel через Анализ данных) на примере ряда динамики ВВП Украины с 2004 по 2012 гг.

Исходные данные предварительно нужно подготовить, а именно прописать x(Yi-1) указав предыдущий Y - это, как мы уже выяснили, и будет нашим влияющим фактором. Таким образом наша совокупность, то есть динамический ряд который будет использоваться для регрессии сократился на одну позицию (обратите внимание на столбец t), то есть с 2005 по 2012 гг.

Далее в экселе активируем вкладку "Данные" и нажимаем "Анализ данных", указываем диапазон исходных данных по примеру как на скриншоте выше и жмем кнопку ОК. (Если по указанному пути нет кнопки "Анализ данных", то пакет анализа нужно активировать, как это сделать описано в статье Линейная регрессия в Excel через Анализ данных)

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

Y = 113436,67 + 1,033*X
или
Y = 113436,67 + 1,033*Yi-1

Возвращаемся в нашу табличку с исходными данными и подставляем полученное уравнение в столбец в качестве формулы, таким образом, получаем расчетные значения по модели, что мы и сделали в столбце Y(расчетный), протягивая формулу на период ниже, получаем прогноз (в табличке строка выделена желтым).

Сравнить реальные данные с смоделированными можно с помощью графика:

AR II - Авторегрессия второго порядка

Yi = a0 + ai*Yi-1 + ai*Yi-2i

Модель авторегрессии второго порядка отличается от первой тем, что она включает в себя еще один влияющий фактор Yi-2, то есть показывается зависимость от того каким был Y не только один период назад, но и от того каким он был два периода назад. Порой это позволяет выявить большую взаимосвязь и соответственно построить более точный прогноз.

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

В нашем случае, полученная модель Y=151395,987+0,724*x1+0,32*x2 или Y=151395,987+0,724*Yi-1+0,32*Yi-2, имеет показатель детерминации R2 ниже чем у модели первого порядка (0,927 против 0,94) да и среднее отклонение у нее больше (64 837,91 против 58 139,90), что значит что модель первого порядка более точная. Это может быть связано с тем, что диапазон исходных данных достаточно мал, чтобы его сокращение на один период имело значительные последствия для точности модели.

AR III - Авторегрессия третьего порядка

Yi = a0 + ai*Yi-1 + ai*Yi-2 + ai*Yi-3i

Модель авторегрессии третьего порядка наиболее тесно описывает зависимость от того каким был итоговый показатель раньше, так как в качестве влияющих факторов используется три отправные точки - каким Y был 1 период назад, 2 периода назад и 3 периода назад. То есть, она больше актуальна для анализа тех сфер деятельности, где полученный результат влияет на размер долгосрочных инвестиций, к примеру - ВВП, доходы по отраслям, продажи крупных корпораций и т.п.

В то же время требования к размаху исследуемого динамического ряда у этой модели выше - так как диапазон исходных данных сокращается на три периода, то чтобы не пострадало качество модели, необходимо расширять исследуемый период.

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

В нашем случае, коэффициент детерминации R2 наиболее низкий (0,89), да и среднее отклонение больше чем в модели первого порядка, опять таки, это обьясняется тем, что исследуемый период достаточно короткий, чтобы его уменьшение давало значительное влияние на качество построения модели.

Итоги

ПЛЮСЫ:

1. Получение высококачественной модели с адекватным прогнозом при минимуме временных затрат и требований к исходным данным.

МИНУСЫ:

1. Прогноз по исходным данным возможен только на один период вперед. Если нужно сделать прогноз на более длительный срок, то в качестве влияющих факторов для расчета придется брать не реально существующий Y, а тот который рассчитан по модели, что в итоге даст прогноз на прогнозе, а значит адекватность такого прогноза, как минимум, в два раза меньше.

2. С увеличением разрядности авторегрессии возникает необходимость расширять диапазон исходных данных.

Download source
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]