Прогноз по модели Хольта-Винтерса скачать пример в Excel

Excel - это инструмент для проведения различных видов анализа, таких как прогнозы. В данном примере рассмотрим, как использовать Excel для составления прогноза с использованием модели Хольта-Уинтерса. Готовый файл с прогнозом можно скачать в конце примера.

Как сделать модель Хольта-Винтерса для расчета прогноза в Excel

Мы будем строить нашу модель прогноза по Хольту-Винтерсу на основе статистических данных, взятых из аэропорта. Используя модель, мы попытаемся предсказать, сколько пассажиров будет обслуживаться в иностранном регулируемом воздушном сообщении в 2016-2018 годах. В нашей таблице будут столбцы, указывающие: C(Yt) - количество обслуживаемых пассажиров, D(Ft) - оценка случайных вариаций модели, E(St) - оценка тренда для модели, F(Yt*) - истекший и реальный прогнозы, G(yt-yt*);H(|yt-yt*|);I((yt-yt*)2) - расчеты, необходимые для расчета показателей MAE, MSE, RMSE, MN - альфа и бета параметры для модели.

Составим прогноз с учетом всех правил модели Хольта-Винтерса используя формулы Excel и на основе полученных данных составим график для визуального анализа.

Метод прогноза по модели Хольта-Винтерса в Excel

Модель Хольта-Винтерса является одним из методов прогнозирования с использованием так называемых экспоненциальное сглаживание. Сглаживание состоит в создании взвешенного скользящего среднего, вес которого определяется по схеме - чем старше информация об изучаемом явлении, тем меньше значение для текущего прогноза. Чтобы построить модель, примите следующие предположения и формулы.

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

Первые значения F1 и S1 обычно:

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

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

Средняя квадратическая ошибка - это средняя разница в квадрате отклонений между фактическими реализациями прогнозируемой переменной и прогноза.

где Yt* - прогнозы истекли.

Среднеквадратичная ошибка (RMSE - Root Mean Square Error) - измеряет, насколько отклонение реализации прогнозируемой переменной от рассчитанных прогнозов.

Созданную модель можно считать хорошей, если отношение RMSE / фактического прогнозирования составляет менее 10%. Однако на практике лучшим тестом для оценки эффективности модели будет сравнение прогнозов, которые она создает, с фактическими значениями.

Формулы для модели составления прогноза Хольта-Винтерса в Excel

Теперь, согласно сделанным в начале предположениям, приведем начальные значения для параметров F1 и S1. В нашем случае это будет y1 = F1 и y2-y1 = S1. Далее мы вводим альфа и бета параметры, временно принимая их значения равными 0,4 (позже мы будем оптимизировать данные с помощью инструмента «Поиск решения» Солвера).

На следующем шаге мы вычисляем Ft и St одновременно в соответствии с приведенными выше формулами, перетаскивая формулы вниз.

Перейдем к столбцу F, в котором с помощью формул рассчитываем, что для 1998-2015 годов прогнозы истекли, а для 2016-2018 годов - реальные прогнозы.

Мы заполняем столбцы GI, которые в данный момент будут использоваться для расчета фактической ошибки прогнозов с истекшим сроком (для получения абсолютного значения в столбце H мы можем использовать функцию Excel =ABS()).

Затем, используя функцию =СРЗНАЧ() и =КОРЕНЬ(), мы вычисляем вышеуказанные индикаторы ошибок MAE, MSE и RMSE.

Когда мы уже рассчитали показатели, мы можем приступить к оптимизации альфа- и бета-параметров, чтобы индекс MAE был как можно меньше. Для этого мы будем использовать аналитический инструмент в Excel «Поиск решения», доступный на вкладке «ДАННЫЕ» в группе «Анализ». Параметры поиска решения устанавливаются так, чтобы минимизировать индекс MAE, изменяя ячейки, помеченные как альфа и бета, закрытые в интервале [0-1].

После нажатия кнопки «Найти решение» и сохранения полученных результатов параметры альфа- и бета-сглаживания должны быть a = 1 и b = 0.228657122399511. На этом этапе мы можем предварительно проверить, может ли наша модель использоваться в качестве эффективного инструмента прогнозирования. Для этой цели мы рассчитываем коэффициент приемлемости прогноза, определенный формулой RMSE / фактическим прогнозом для последующих периодов T16-T18. В нашем случае на 2016 год это 5%, поэтому прогноз можно считать достоверным. Наконец, стоит визуализировать весь анализ на графике, принимая реальные значения и прогнозы в виде серии данных.

Скачать пример прогноза по модели Хольта-Винтерса в Excel

Когда у нас есть прогноз, ничего не остается делать, кроме как следить за новыми данными, чтобы проверить, имеет ли смысл использовать модель. Из того, что можно найти на одном из информационных порталов о авиаперелетах данной компании, общее количество ее обслуженных пассажиров в 2017 году составило более 4,6 млн. человек. Поэтому высокая вероятность, что прогнозируемые нами значения работают на практике.