Прогнозирование динамики временных рядов

 

Цель задачи: ознакомиться с технологиями построения трендов c помощью мастера диаграмм и реализации метода абсолютных отклонений, реализованными в Excel.

Предлагается, пользуясь встроенными функциями Excel и инструментарием мастера диаграмм:

1) построить графически (используя мастер диаграмм) уравнения трендов для Y за 2000-2008 г.г.:

· для линейного тренда;

· для полиномиального тренда 4 порядка;

· для логарифмического тренда;

· для степенного тренда.

2) вычислить на основании полученных уравнений трендов прогнозные значения Y для 2009 года для всех видов трендов и сравнить их с имеющимся значением Y из таблицы 1.2 для 2009 года. Оценить достоверность полученных результатов в процентах от имеющегося в таблице 1.2 значения Y для 2009 года с помощью метода абсолютных отклонений.

Исходные данные для задания 3 представлены в таблице 1.2:

Таблица 1.2

Год	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009
хi	1	2	3	4	5	6	7	8	9	10
Объем выпуска продукции, млн. руб. (yi)	2910	2890	2933	2941	2945	2952	2957	2964	2968	2970

Для расчетов понадобятся следующие функции Excel:

· ABS() – для нахождения абсолютного значения числа;

· СТЕПЕНЬ() – для нахождения степени числа;

· EXP() – для нахождения экспоненты числа;

· LN() – для нахождения натурального логарифма числа.

ПРИМЕЧАНИЕ: Порядок использования функций – см. в мастере функций, который находится на листе Excel в закладке Вставка в подзакладке Функция (f_x).

Алгоритм расчетов

Для получения уравнения линейного тренда проделаем следующие операции:

1) с помощью мастера диаграмм построим график изменения объема выпуска продукции для х_i, i изменяется от 1 до 9;

2) щелкнув мышкой (правой кнопкой) на полученный график и используя закладку Добавить линию тренда, войдем в закладку Линия тренда;

3) выберем в подзакладке Тип интересующий вид тренда и построим его на графике;

4) поставим в подзакладке Параметры метку в окне показывать уравнение на диаграмме.

Для линейного тренда таким уравнением является:

y=8,55x+2897,3.

С помощью полученного таким образом уравнения тренда вычислим в Excel прогнозное значение у за 2009 год, подставив х=10, по формуле:

=8,55*A14+2897,3,

где A14 – ячейка Excel, в которой хранится значение количества х₁₀ (х₁₀=10).

Полученное расчетное значение Y₁₀ (Y₁₀=2982,8) сравниваем с исходным у₁₀ (у₁₀=2970) с помощью метода абсолютных отклонений, которое вычислим в Excel по формуле:

=ABS(D17-$C14)/$C14*100,

где D17 – ячейка Excel, в которой хранится расчетное значение Y₁₀;

$C14 – ячейка Excel, в которой хранится исходное значение у₁₀ (для удобства дальнейших расчетов в Excel адрес этой ячейки фиксируется с помощью значка «$»).

По этому же алгоритму осуществляются расчеты для остальных типов тренда (см. рис. 3).

Алгоритм расчетов на листе Excel с указанием используемых формул и полученными результатами представлен на рисунке 3.

Результаты расчетов позволяют сделать следующие выводы:

· Малая величина относительной погрешности прогнозирования (0,43%) для линейной аппроксимации позволяют утверждать, что составленное уравнение тренда (y=8,55x+2897,3) для рассматриваемого временного ряда имеет очень высокую степень достоверности прогноза;

 

Рис. 3. Алгоритм расчетов по 3 заданию

с примером построения логарифмического тренда

· Невысокая величина относительной погрешности прогнозирования (1,26%) для полиномиальной аппроксимации позволяют утверждать, что составленное уравнение тренда (y=0,1792x^4-3,6824x^3+24,721x^2-50,917x+2935; значок ^ означает операцию возведения в степень) для рассматриваемого временного ряда имеет высокую степень достоверности прогноза;

· Малая величина относительной погрешности прогнозирования (0,06%) для логарифмической аппроксимации позволяют утверждать, что составленное уравнение тренда (y=32,159Ln(x)+2894,3) для рассматриваемого временного ряда имеет чрезвычайно высокую степень достоверности прогноза;

· Малая величина относительной погрешности прогнозирования (0,43%) для экспоненциальной аппроксимации позволяют утверждать, что составленное уравнение тренда (y=2897,4e^(0,0029x)) для рассматриваемого временного ряда имеет очень высокую степень достоверности прогноза;

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