Прогнозирование на основе анализа временных рядов.

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

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

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

1. Линейная

2. Логарифмическая

1. Полиномиальная
2. Степенная
3. Экспоненциальная

Линейная аппроксимация - это прямая линия, наилучшим образом описывающая набор данных. Она применяется в самых простых случаях, когда точки данных расположены близко к прямой. Говоря другими словами, линейная аппроксимация хороша для величины, которая увеличивается или убывает с постоянной скоростью. Формула: , где m – угол наклона и b - координата пересечения оси абсцисс.

Логарифмическая аппроксимация хорошо описывает величину, которая вначале быстро растет или убывает, а затем постепенно стабилизируется. Описывает как положительные, так и отрицательные величины. Формула: , где c и b – константы.

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

Формула: , где c и b – константы.

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

Формула: , где c и b - константы.

Экспоненциальное приближение следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Однако для данных, которые содержат нулевые или отрицательные значения, этот вид приближения неприменим.

Формула: , где c и b - константы, e - основание натурального логарифма.

На диаграмме можно выделить любой ряд данных и добавить линию тренда, если выделенные данные представляют собой диаграмму с областями, график, гистограмму, линейчатую диаграмму или точечную диаграмму. Когда линия тренда добавляется к ряду данных, она связывается с ним, и поэтому при изменении значений любых точек ряда данных линия тренда автоматически пересчитывается и обновляется на диаграмме.

Любой тренд – это упрощение реальной действительности. По одной выборке можно построить до 9 уравнений тренда. Задача менеджера (исследователя) состоит в отборе наилучшего тренда, по которому можно будет сделать надежный прогноз. Наиболее надежна линия тренда, для которой значение R-квадрат равно или близко к 1. R в квадрате - это число от 0 до 1, которое отражает близость значений линии тренда к фактическим данным. Чем больше величина этого показателя, тем достовернее линия тренда. И, тем не менее, при анализе тренда с наивысшим коэффициентом детерминации нужно следить за тем, чтобы прогноз не имел абсурдный характер. Нужно также помнить, что когда на полной совокупности данных нельзя построить тренд единого типа, то делят всю совокупность на участки, каждый из которых хорошо аппроксимируется какой-либо линией тренда.

Пример 2

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

период
показа- тель

На лист в Excel введите в ячейки данные из таблицы (в столбец А введите периоды, в столбец В – исследуемый показатель).

По данным В1:В11 постройте точечный график. Щелкните правой кнопкой мыши на линии построенного графика. В появившемся контекстном меню выберите Добавить линию тренда. Выберите тип линии тренда Линейная. Щелкните по вкладке Параметры. В поле Вперед на введите количество желаемых периодов для прогноза (5). Установите флажки: показывать уравнение на диаграмме и поместить величину достоверности аппроксимации. Нажмите ОК. В результате на графике будет построена линия тренда вашего временного ряда, продленная вперед на то количество периодов, которое вы выбрали (на 5 периодов), а также помещено уравнение и значение R². Аналогично можно построить другие тренды, например экспоненциальный и полиномиальный. Наилучшей моделью для прогноза является та, для которой коэффициент детерминации R² является наибольшим.

Применение функций Excel ПРЕДСКАЗ, ТЕНДЕНЦИЯ и РОСТ.

Для применения метода регрессии в процессе прогнозирования показателей можно использовать стандартные функции Excel: ПРЕДСКАЗ, ТЕНДЕНЦИЯ и РОСТ. Методы регрессии оценивают взаимосвязь между фактическими данными наблюдений и другими параметрами, например, порядковыми номерами периодов наблюдений или датами наблюдений.

Рассмотрим применение функции ТЕНДЕНЦИЯ. Предположим, данные наблюдений конкретного показателя внесены на рабочем листе в столбец В с 1-ой по n-ю ячейки. Значения порядковых номеров наблюдений внесены в столбец A, рядом со значениями соответствующих показателей. На основании имеющихся данных, вы можете построить прогноз исследуемого показателя на последующие периоды временного ряда, по которым еще нет результатов наблюдений. Например, у вас имеются результаты одиннадцати наблюдений (пример выше), и вы хотите определить прогнозные показатели на следующие девять аналогичных периодов времени.

Пример 3. Воспользуемся данными Примера 2.

На новом листе Excel в ячейки B1:B11 введите значения показателя, а в ячейки A1:A11 - порядковые номера периодов. Чтобы определить прогнозное значение одного периода (только двенадцатого), в ячейку А12 внесите порядковый номер этого периода 12. Далее, в ячейке С12 найдите значение функции ТЕНДЕНЦИЯ следующим образом. Выделите ячейку С12. В операционном меню щелкните пункт Вставка, выберите Функция. В появившемся диалоговом окне в разделе Категория выберите Статистические, а в разделе Функция - ТЕНДЕНЦИЯ. Нажмите ОК. Появится диалоговое окно функции ТЕНДЕНЦИЯ. В строке Известные_значения_у выделите диапазон ячеек В1:В11. В строке Известные_значения_х выделите диапазон ячеек А1:А11. В строке Нов_знач_х выделите ячейку А12. Нажмите ОК. В ячейке С12 появится прогнозное значение показателя на 12-ый период.

Для того чтобы определить прогнозные значения сразу до двадцатого периода, нужно найти значения функции ТЕНДЕНЦИЯ в диапазоне ячеек С13:С20. Для этого внесите в ячейки А13:А20 порядковые номера прогнозируемых периодов 13, 14,…, 20.

Функция ПРЕДСКАЗ - частный случай функции ТЕНДЕНЦИЯ, а ТЕНДЕНЦИЯ – частный случай ЛИНЕЙН. Функция ПРЕДСКАЗ, вычисляет прогнозы, основанные на линейной связи между результатом наблюдений и временем наблюдений. При этом возможно сделать прогноз на те периоды, которые наблюдались. Т.о., можно сравнить фактические данные и прогнозируемые и оценить точность прогноза по отклонениям.

Обратитесь к функции ПРЕДСКАЗ для нашего случая. Введите предсказанные значения в столбец D. Поставьте курсор в ячейку D1, вызовите функцию ПРЕДСКАЗ и введите необходимые данные: ПРЕДСКАЗ(A1;$B$1:$B$11;$A$1:$A$11). Рассчитайте её значение для периодов с первого по двадцатый, используя функцию автозаполнения. Оцените точность прогноза.

Постройте точечную диаграмму предсказанных значений, полученных по функции ПРЕДСКАЗ. Укажите ошибки прогноза. Для этого установите курсор на линии точечной диаграммы, нажмите правую кнопку мыши. Выберите Формат рядов данных, закладку Y-погрешности, Показать планки погрешностей по Y – Обе. В поле Величина погрешности установите переключатель на стандартную погрешность. Нажмите ОК.

Если же взаимосвязь показателей носит нелинейный, а, экспоненциальный характер, то более точный прогноз вы можете построить, используя функцию РОСТ из Excel.