Сезонная декомпозиция временного ряда План занятия

Сезонная декомпозиция временного ряда

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

Постановка задачи • В этом тренинге мы исследуем структуру временного ряда – оборот розничной торговли и выделим закономерные компоненты ряда • Будет использован метод стандартной декомпозиции временного ряда • Для выделения тренда будет использовано уравнение прямой линии • С помощью модели закономерных компонентов ряда мы построим прогноз на горизонте 12 месяцев • Для оценки качества модели будут рассчитаны показатели ошибки прогноза 3

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

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

Представление даты в Excel RTRD_M 2000 1500 1000 500 ❷ 1 01 1/ 1 /2 10 20 1/ 1/ 09 20 1/ 1/ /2 00 8 7 1/ 1 00 /2 1/ 1 /2 00 6 5 1/ 1 00 /2 1/ 1 00 ❶ 4 0 /2 3. Добавьте на график уравнение тренда (команда Формат линии тренда в контекстном меню линии тренда) Угловой коэффициент прямой очень маленький – средний темп роста составляет ~0, 44 млрд/период. Это не соответствует действительности: за ~7, 5 лет (92 месяца) средний уровень ряда увеличился на ~1200 млрд руб, т. , что соответствует среднему темпу роста на ~13 млрд/период Причина в том, что уравнение построено на основе дат (независимая переменная x) – Для представления дат и времени в Excel используются действительные числа – Целая часть – число дней с 01. 1900, дробная часть – прошедшую долю текущих суток. Например, 12: 00 7 февраля 2012 г соответствует числу 40946, 5 – Значения аргумента (x) – это очень большие числа, это искажает интерпретацию коэффициентов модели – Нельзя использовать даты для построения моделей трендов, ❸ особенно когда используются нелинейные модели тренда 1/ 1 1. 2. 6

Оценка коэффициентов тренда • • Для моделирования временного ряда необходимо строить тренд не по дате, а по номеру периода (1…N) Для оценки коэффициентов тренда удобно использовать функцию ЛИНЕЙН() – линейная регрессия – – Y b 1 = tg(a) b 0 Y = b 1 * X + b 0 Аргументами функции ЛИНЕЙН() являются диапазоны исходных данных – значения Y и X Если диапазон X не указан, то используются номера периодов 1. . N С помощью дополнительных параметров можно построить уравнение прямой, проходящей через начало координат (B 0 = 0), а также вывести статистики для оценки качества модели тренда Функция возвращает массив коэффициентов тренда – b 1 и b 0, поэтому необходимо вводить ее как формулу массива и заранее выделить диапазон ячеек, в который будут помещены коэффициенты 1. Добавьте на лист подписи в соответствии с рисунком и выделите диапазон из двух смежных ячеек (например, C 5: D 5) 2. Введите в строке формулу: = ЛИНЕЙН(укажите весь диапазон значений оборота) и нажмите CTRL-Shift-Enter (ввод формулы массива). Фигурные скобки в формуле добавляются автоматически и обозначают, что это формула массива. Их вводить не следует. ❶ ❷ 7 X

Моделирование тренда • Мы получили уравнение закономерного компонента ряда - тренда - с помощью функции ЛИНЕЙН(). Теперь, чтобы выделить второй закономерный компонент – сезонность, необходимо исключить тренд из данных. Для этого нам потребуются значения тренда (среднего уровня) ряда для каждого периода, которые можно рассчитать по уравнению тренда 1. Добавьте на лист столбец t, содержащий номер периода (1. . 92) – воспользуйтесь функцией автозаполнения 2. Добавьте для ячеек, содержащих значения коэффициентов тренда, имена: b_1 и b_0 3. Рассчитайте значения среднего уровня для каждого периода по уравнению тренда ( = b_0 + b_1 * номер текущего периода в столбце t) ❶ ❷ ❸ 8

Выделение сезонного компонента • На этапе предварительного анализа данных мы выяснили, что ряд имеет мультипликативную сезонность (амплитуда сезонных колебаний увеличивается с увеличением среднего уровня). Следовательно, для удаления тренда необходимо разделить исходные данные на значения тренда (для аддитивной сезонности тренд нужно вычитать) 1. 2. 3. ❶ Для удобства задайте имена диапазонов: • выделите все фактические значения и задайте для диапазона имя: Факт • аналогично, для диапазона значений тренда задайте имя: Тренд Добавьте на лист столбец Отношение, рассчитанный по формуле: = Факт / Тренд Хотя имена Факт и Тренд соответствуют диапазонам из нескольких значений, использование формулы Факт/Тренд корректно, т. к. Excel автоматически использует функцию неявного пересечения. Это означает, что операция деления выполняется не для всего диапазона, а только для текущей строки. Т. е. обрабатываются ячейки на пересечении диапазона и текущей строки. Задайте процентный формат для значений в столбце Отношение, поскольку сезонные коэффициенты характеризуют процентное отношение данного месяца к среднему уровню продаж ❷ ❸ 9

Расчет сезонных коэффициентов • Сезонные коэффициенты, полученные на предыдущем шаге, содержат случайную составляющую и различаются от года к году (сравните, например, сезонное отношение для января 2004 и 2005 года). Чтобы устранить влияние случайности, необходимо усреднить значение сезонного коэффициента для каждого месяца. Это можно сделать различными способами – например, при помощи функции условного усреднения СРЗНАЧЕСЛИ(), либо при помощи сводной таблицы. Мы воспользуемся вторым способом, поскольку он проще. 1. Добавьте на лист столбец Месяц, который рассчитывается по формуле: = Месяц (дата текущего периода – столбец T) Функция Месяц() возвращает номер месяца, соответствующий указанной дате 2. Выделите всю таблицу и добавьте на текущий лист отчет сводной таблицы (названия строк – месяцы, значения – среднее по полю Отношение, формат значений - процентный) 3. Отключите вывод итогов для сводной таблицы (вкладка на ленте Работа со сводными таблицами>Конструктор) ❶ ❷ ❸ 10

Нормировка сезонных коэффициентов • Для сезонных коэффициентов должно выполняться условие нормировки: – при аддитивной сезонности – сумма сезонных коэффициентов = 0 – при мультипликативной сезонности – сумма сезонных коэффициентов = S (число периодов в одном сезонном цикле) • В данном случае это условие не выполняется (хотя ошибка небольшая) и необходимо нормировать коэффициенты 1. Рассчитайте сумму сезонных коэффициентов на основе данных сводной таблицы 2. Рассчитайте поправку ( = 12 / сумма сезонных коэффициентов ) 3. Рассчитайте нормированные сезонные коэффициенты ( = сезонный коэффициент * поправка ) Обратите внимание, что для расчета нормированного сезонного коэффициента используется обычная ссылка на ячейку: по умолчанию Excel подставляет функцию ПОЛУЧИТЬ. ДАННЫЕ. СВОДНОЙ. ТАБЛИЦЫ() ❸ ❶ ❷ 11

Визуализация сезонных коэффициентов • На основе нормированных сезонных коэффициентов постройте график (месяц – нормированный сезонный коэффициент) – Мы видим, что наибольший оборот – в предпраздничные периоды (ноябрь – декабрь) – В январе наблюдается спад продаж – В первую половину года продажи ниже среднего уровня, начиная с августа – выше среднего уровня Нормир. СК 130% 120% 110% 100% 90% 80% 1 2 3 4 5 6 7 8 9 10 11 12 12

Расчет прогноза • Объединяя выделенные закономерные компоненты ряда (тренд и сезонность), можно получить прогноз. Значения, полученные по модели в историческом периоде, когда есть факт, можно использовать для оценки качества модели (согласия с данными) 1. С помощью функции автозаполнения добавьте в таблицу еще 12 периодов – до августа 2012 г. Необходимо добавить даты, номера периодов, тренд и месяцы 2. Добавьте столбец Прогноз, в котором необходимо перемножить значение тренда и нормированного сезонного коэффициента для данного периода. Значения нормированных сезонных коэффициентов можно получить из сводной таблицы при помощи функции ВПР() с ключом: номер месяца ❶ ❷ 13

Визуализация прогноза • Чтобы визуально сравнить согласие модели с данными, постройте на одной диаграмме графики фактических данных и прогноза (на отдельном листе) – Видно хорошее согласие модели с фактом (отклонения небольшие в масштабе значений ряда) – В 2008 году наблюдается систематическое превышение прогноза фактом. Это вызвано проявлением кризиса: спад продаж в 2009 году вызвал отклонение тренда, уравнение прямой линии в данном случае неадекватно. – Лучший прогноз можно получить по модели, которая может адаптироваться к экономическим циклам и изменению тренда. 2500 2000 1500 RTRD_M Прогноз 1000 500 0 1/1/2004 1/1/2005 1/1/2006 1/1/2007 1/1/2008 1/1/2009 1/1/2010 1/1/2011 1/1/2012 14