Пример 4.1. Трендовые модели в задачах прогнозирования

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

При составлении прогнозов развития изучаемого явления оперировать оценкой, рассчитанной по уравнению тренда (точечной оценкой) можно только со знанием доверительного интервала прогноза. Границы доверительного интервала определяются по формуле

(4.1)

где - точечный прогноз, рассчитанный по уравнению тренда;

t – коэффициент Стьюдента при уровне значимости и числе степеней свободы f = n – 1;

s – среднее квадратическое отклонение тренда (ошибка аппроксимации).

Среднее квадратическое отклонение тренда вычисляется по формуле:

(4.2)

где - фактические и расчетные значения;

n – количество наблюдений;

l – число коэффициентов в уравнении тренда.

На практике выбор вида уравнения тренда может быть основан на анализе графического отображения динамического ряда в виде точек на диаграмме и на анализе сущности изучаемой закономерности (последнее чаще важнее). В Excel представлены все часто используемые трендовые модели. Рассмотрим их на конкретном примере (рис. 4.1).

В блоке ячеек A5:I6 необходимо ввести исходные данные. Далее требуется выделить блок ячеек B5:I6 и построить Точечную диаграмму с маркерами (Вставка – Точечная – Точечная с маркерами). Отформатируйте диаграмму в соответствии с рис. 4.1.

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

Выберите параметры соответственно с рис. 4.2. В результате вы получите график как на рис. 4.1, на котором показано уравнение линейной зависимости и величина достоверности аппроксимации R^2 (R² называют также коэффициентом детерминации).

По величине R² можно судить о качестве аппроксимации данных уравнением тренда.

При R² = 0 величины, для которых определяется уравнение тренда, являются независимыми.

При R² = 1 имеет место функциональная (а не статистическая) взаимосвязь.

При R² > 0,7 принято говорить о существовании достаточно тесной взаимосвязи.

С математической точки зрения коэффициент детерминации R² вычисляется по следующим формулам:

В рассматриваемом примере R² = 0,911, что свидетельствует о высокой точности аппроксимации данных линейным уравнением.

Рис. 4.1.

Рис. 4.2

Прогнозирование объема выпуска удобрений нВ следующие два года осуществляется в блоке ячеек A24:K28.

В блок ячеек A24:I24 потребуется вновь ввести исходные данные, например, методом копирования блока A5:I6.

В ячейке B26 провести расчет функции по уравнению тренда =348,1+21,35*B24 при аргументе, записанном в ячейке B24. Далее ячейка B26 копируется методом протаскивания до ячейки K26.

В строке 27 проведен расчет остатков.

В строке 28 проведен расчет квадратов остатков.

В ячейке J28 рассчитана сумма квадратов остатков.

В ячейке H31 рассчитано среднее квадратическое отклонение тренда.

В ячейке E35 рассчитан коэффициент Стьюдента.

Далее нам потребуется рассчитать доверительные интервалы прогноза. Рассчитанный ранее в ячейках J26:K26 точечный прогноз введен вновь в ячейках E43:F43. В блоке ячеек A42:F46 проведен расчет доверительных интервалов. Относительная ошибка прогноза около 7,5% является вполне приемлемой.

В правый нижний угол рабочего листа скопирован график линии тренда. Щелкните по линии тренда правой кнопкой мыши, в контекстном меню – Формат линии тренда, в открывшейся вкладке установите Прогноз вперед на 2 периода. На графике линия тренда будет автоматически продолжена на 9 и 10 значения оси времени.

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