Регрессивный анализ с помощью диаграмм

Во многих случаях диаграммы Excel бывают очень полезны при создании прогнозов. Они помогают визуально представить данные. Более того, некоторые методы статистики, используемые в прогнозировании, используют отклонения, т.е. значения, которые необычно далеко отстоят от среднего значения. Если представить данные в виде диаграммы, то гораздо легче определить, влияют ли отклонения на прогноз.

Иногда возникает необходимость провести регрессивный анализ непосредственно на графике, без введения в рабочий лист значений для прогноза. Это можно сделать с помощью графической линии тренда методом, во многом сходным с методом получения прогноза с применением скользящего среднего на основе графика. Постройте диаграмму на основе данных, содержащихся в ячейках А2:А25 (рис. 11).

Рис. 11 С помощью линий тренда можно создавать прогнозы, основанные на регрессии, непосредственно на диаграмме

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

• Выберите из контекстного меню команду Добавить линию тренда.

• Выберите тип линии тренда Линейная.

• Щёлкните на вкладке Параметры.

• В поле Вперед на введите количество желаемых периодов, на протяжении которых линия тренда будет прокладываться вперед.

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

• Щелкните на кнопке ОК

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

Прогнозирование с использованием функции экспоненциального сглаживания

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

Разработка перспективных оценок с применением метода сглаживания

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

F[£+l] - F[f] + a x e[f],

t — временной период (например, 1-й месяц, 2-й месяц и т.д.);

F[f] — это прогноз, сделанный в момент времени t;

F[£+l] отражает прогноз во временной период, следующий непосредственно за моментом времени t;

а — константа сглаживания;

e[t] — погрешность, т.е. различие между прогнозом, сделанным в момент времени t, и фактическими результатами наблюдений в момент времени t.

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

Рассмотрим пример, приведенный на рис. 9.13

Рис. 12. При прогнозе с использованием линейной линии тренда пропускается скачок функции базовой линии, тогда как при прогнозе с применением сглаживания он отслеживается

 

Заметьте, что уровень ряда в момент времени 8 резко увеличивается. Это явление известно под названием скачок функции. Сглаживание является очень полезным в тех случаях, когда во временном ряду наблюдаются существенные различия в уровнях данных. Как видим, на рис. 9.13 линейная линия тренда не отображает резкого скачка между моментами времени 7 и 8; она дает завышенные показатели относительно уровня в момент 7 и занижает уровень последующего ряда. При прогнозе, выполненном с помощью сглаживания, фактическая базовая линия отслеживается довольно точно.