Простое экспоненциальное сглаживание v 2 План занятия

Простое экспоненциальное сглаживание v 2

План занятия • • Реализация метода экспоненциального сглаживания Подбор константы сглаживания Расчет показателей ошибки прогноза Прогнозирование ex-post 2

Постановка задачи • В этом тренинге мы используем метод простого экспоненциального сглаживания для выделения тренда временного ряда – оборот розничной торговли • Для определения константы сглаживания на основе критерия – наименьший средний квадрат ошибки – будут использованы инструменты Excel – Поиск решения и Таблица данных • Для оценки качества модели будут рассчитаны показатели ошибки прогноза и выполнен прогноз ex-post 3

Описание набора данных • • Для выполнения тренинга необходим файл retail. xlsx Работу можно выполнять в новой копии исходного файла, либо продолжить в файле с сезонной декомпозицией. В этом случае запустите диспетчер имен и убедитесь, что в книге нет имен alpha, beta, gamma. Если они есть – удалите их. На листе Данные содержатся выгруженные из Единого архива экономических и социологических данных исторические значения оборота розничной торговли в ежемесячных показателях (ряд RTRD_M) Единицей измерения является 1 млрд. руб (в текущих ценах) 4

Предварительный анализ данных • • • Установите курсор в таблицу и постройте (на отдельном листе) график изменения оборота во времени (команда Вставка>График на ленте) Добавьте на график линию линейного тренда (контекстное меню ряда данных) Ряд содержит очевидные закономерные компоненты: – – – – Средний уровень ряда непрерывно растет (есть тренд) Линейная модель тренда достаточно хорошо передает тенденцию в данных ряда Хорошо различимы сезонные колебания продаж – пиковые продажи в декабре и спад продаж в январе каждого года, длительность сезонного цикла – 12 месяцев (год) Амплитуда сезонных колебаний растет вместе со средним уровнем, тип сезонности – мультипликативная В 2006, 2008, 2010 г. средний уровень ряда систематически отклонялся от тренда в большую или меньшую сторону, что означает неадекватность линейной модели тренда данным За исключением влияния кризиса, отобранный исторический период не содержит заметных изменений в закономерных компонентах ряда Модель экспоненциального сглаживания непригодна для прогнозирования ряда с трендом и сезонностью, т. к. она не учитывает эти закономерные компоненты, но она может с успехом использоваться для выделения тренда (среднего уровня ряда). В отличие от прямой линии, модель экспоненциального сглаживания может адаптироваться к изменению тренда в 5 данных

Подготовка данных 1. 2. 3. 4. 5. Скопируйте исходные данные (столбцы T и RTRD_M) на новый лист и назовите лист: Простое. ЭС. Дальнейшие действия выполняются на листе Простое. ЭС. Добавьте справа от таблицы заголовок столбца – L. Задайте для столбцов RTRD_M и L числовой формат – с разделителями групп разрядов и 0 знаков после запятой. Установите выравнивание по центру ячейки. Перед таблицей добавьте 2 строки и задайте константу сглаживания a = 0, 3 С помощью строки адреса задайте для ячейки, содержащей значение константы сглаживания, имя: Простое. ЭС!alpha. Данное имя будет локальным для листа Простое. ЭС. Если при создании имени не предварять его именем листа, то имя будет глобальным для всей рабочей книги. При выборе ячейки со значением константы сглаживания в строке адреса выводится имя alpha. Однако данное имя – локальное для листа Простое. ЭС. В этом можно убедиться с помощью Диспетчера имен (см. раздел Формулы на ленте). В формулах на листе Простое. ЭС можно указывать просто alpha. В формулах, размещенных на других листах книги, необходимо обращаться к этой ячейке с указанием имени листа: Простое. ЭС!alpha. ❹ ❸ ❺ ❷ ❶ 6

Формула экспоненциального сглаживания • • • Для применения модели экспоненциального сглаживания необходимо задать константу сглаживания a и начальное значение сглаженного ряда – L 1. Значение константы подбирается произвольно в диапазоне [0; 1] исходя из необходимой степени подавления случайных колебаний. Значение a = 0 означает, что модель не реагирует ни на какие изменения в данных (полное сглаживание). Значение a = 1 означает, что модель полностью повторяет любые изменения в данных (полное отсутствие сглаживания). В этом примере мы уже задали (произвольно) значение a = 0, 3. Инициализировать модель (задать начальное значение сглаженного ряда L 1) можно различными способами, в зависимости от поведения исходных данных. Распространенные способы: L 1 = 0, L 1 = Факт1, L 1 = среднее значение факта за некоторый период. 1. В данном примере мы используем начальное значение факта в качестве L 1. Для сезонных рядов такой способ инициализации может работать плохо, если первый период данных соответствует «нетипичному» сезону (для розницы такими периодами являются ноябрь и декабрь, когда продажи существенно выше средних). В этом случае можно в качестве L 1 использовать среднее за первый год. Однако здесь это не нужно. Приравняйте первое значение в столбце L первому значению факта. При большом количестве данных почти нет разницы в том, какое начальное значение использовать, т. к. модель со временем скорректирует это значение. 2. Во второй и последующих ячейках столбца L сглаженное значение рассчитывается по формуле: = alpha * (значение факта) + (1 -alpha) * предыдущее сглаженное значение 7

Влияние константы a на степень сглаживания • • Постройте по таблице график фактических и сглаженных значений. На рисунке показан график для alpha = 0, 3 Измените alpha на указанные значения и посмотрите, что происходит со сглаженным рядом: 2, 000 – – – • alpha = 0, 1 alpha = 0, 5 alpha = 0, 9 alpha = 1 Верните исходное значение alpha = 0, 3 1, 800 1, 600 1, 400 1, 200 1, 000 800 600 400 200 0 1/1/2004 1/1/2005 1/1/2006 1/1/2007 1/1/2008 RTRD_M 1/1/2009 1/1/2010 1/1/2011 L 8

Прогноз на 1 шаг вперед и расчет показателей ошибки • • Значение параметра a можно подобрать таким образом, чтобы минимизировать показатели ошибки прогнозе на 1 шаг вперед. Необходим именно прогноз, а не сглаженные значения, т. к. в последнем случае при a = 1 модель полностью повторяет факт и ошибка равна 0. Прогнозом на следующий (и последующие) периоды в методе простого экспоненциального сглаживания является последнее сглаженное значение. 1. 2. 3. Добавьте в таблицу столбец с заголовком F[1]. Первое значение в столбце F[1] – пустое, второе и последующие равны значениям в столбце L для предыдущего периода. Т. е. «прогнозом» на февраль 2004 г. является сглаженное значение для января 2004 г. Используйте формулу: = ссылка на предыдущее значение L Задайте для диапазона фактических данных с 02. 2004 до 01. 08. 2011 имя: Простое. ЭС!Факт1. В столбце F[1] для аналогичного диапазона значения задайте имя: Простое. ЭС!Прогноз 1. Добавьте перед таблицей строки и рассчитайте показатели ошибки прогноза. При необходимости, обратитесь к тренингу по стандартной декомпозиции. ❶ ❷ ❸ 9

Предварительная оценка константы сглаживания • • Оптимальное значение константы a найдем по критерию минимума среднего квадрата ошибки (MSE). Приближенно оптимальное значение можно найти перебором с помощью инструмента Excel: Таблица данных позволяет подставить значения одного или двух параметров из указанного множества значений в формулу и вывести результат расчета для каждого значения параметра в виде таблицы. 1. 2. 3. 4. 5. Задайте множество перебираемых в таблице данных значений alpha: от 0, 1 до 0, 9 с шагом 0, 1 Слева от значения 0, 1 введите формулу: =alpha (текущее значение alpha) Сразу под ячейкой с текущим значением alpha в таблице подстановки поставьте ссылку на ячейку, в которой содержится рассчитанное значение MSE (формула: = ячейка со значением MSE) Выделите таблицу подстановки целиком (2 строки – начиная от текущей alpha и заканчивая ячейкой под значением 0, 9) и выберите в разделе Данные на ленте команду Анализ “что если”>Таблица данных… ❺ В диалоговом окне Таблица данных укажите, что значения по столбцам (т. е. числа 0, 1. . 0, 9) нужно подставлять в ячейку alpha. Вместо имени можно также указать адрес ячейки. ❹ ❷ ❸ 10 ❶

Область наименьшей ошибки • В таблице данных выводятся значения среднего квадрата ошибки для различных a. Для быстрого поиска области оптимальных значений в большой таблице данных можно использовать функцию условного форматирования, которая позволяет выделять ячейки в зависимости от их значения. 1. 2. 3. Выделите диапазон значений MSE, соответствующий alpha = 0, 1. . 0, 9 Выберите в разделе ленты Главная команду Условное форматирование>Цветовые шкалы. Выберите шкалу, в которой наибольшим значениям соответствует красный цвет, а наименьшим – зеленый. Учитываются только значения из выделенного диапазона. Результат показан на рисунке. Видно, что оптимальное по критерию минимума MSE значение a близко к 0, 5 ❶ ❷ ❸ 11

Зависимость MSE от a • Постройте зависимость MSE от alpha по таблице данных. Для этого добавьте точечную диаграмму с соединением точек отрезками прямых линий. Значения по оси абсцисс – alpha (0, 1. . 0, 9), по оси ординат – соответствующие им значения MSE 1. Результат показан на рисунке 2. Имя ряда MSE задано вручную (команда Изменить данные в контекстном меню ряда) ❶ ❷ 12

Оптимальное значение a • Точно оптимальное значение параметра a можно найти с помощью инструмента «Поиск решения» . Эта надстройка позволяет найти минимум/максимум некоторой функции с учетом ограничений на переменные решения 1. 2. 3. 4. 5. ❷ Выберите на в разделе Данные на ленте команду Поиск решения. Если вы не видите эту команду, необходимо активировать надстройку «Поиск решения» в параметрах программы (см. Файл>Надстройки, выберите Надстройки Excel и нажмите Перейти…) Укажите в качестве целевой функции ссылку на ячейку, где содержится значение MSE (для текущего значения alpha). ❶ Изменяемой ячейкой является alpha. Задайте ограничения для переменной alpha: alpha >=0 и alpha <= 1 (кнопка Добавить ограничение). Можно вводить имя ячейки alpha, или абсолютную ссылку на нее. Убедитесь, что метод решения указан: Поиск решения нелинейных задач методом ОПГ. Данная задача – нелинейная, т. к. минимизируется квадратичная функция – MSE. Выберите команду: ❸ Найти решение. Подтвердите сохранение. ❹ ❺ 13

Прогноз ex-post • • В отдельном столбце (Ex-Post) рассчитайте прогноз ex-post. Обучающий период – от начала данных до августа 2010 г. Тестовый период – от сентября 2010 до августа 2011 г. В обучающем периоде формула такая же, как в столбце L. В тестовом периоде формула – последнее сглаженное значение для обучающего периода (т. е. значение в столбце Ex-Post для августа 2010 г). Не забудьте заблокировать ссылку на последнее сглаженное значение. Постройте график с исходными данными и прогнозом ex-post и рассчитайте показатели ошибки для тестового периода Хотя модель показала достаточно низкие показатели ошибки на обучающем периоде, прогноз ex-post демонстрирует ее неадекватность данным. Ряд сезонный и имеет тренд, а модель прогнозирует постоянное значение во всех будущих периодах. Необходимы методы прогнозирования, которые учитывают присутствующие в ряде закономерные компоненты – тренд и сезонность. 2, 000 RTRD_M Ex-Post 1, 800 1, 600 1, 400 1, 200 1, 000 800 600 400 200 0 14 1/1/2005 1/1/2006 1/1/2007 1/1/2008 1/1/2009 1/1/2010 1/1/2011