Построение аддитивной модели в excel

Обновлено: 07.01.2025

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

Общий вид аддитивной модели следующий:

Эта модель предполагает, что каждый уровень временного ряда может быть представлен как сумма трендовой (), сезонной () и случайной () компонент.

Общий вид мультипликативной модели выглядит так:

Эта модель предполагает, что каждый уровень временного ряда может быть представлен как произведение трендовой (), сезонной () и случайной () компонент.

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

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

Процесс построения модели включает в себя следующие шаги.

1) Выравнивание исходного ряда методом скользящей средней.

2) Расчет значений сезонной компоненты .

3) Устранение сезонной компоненты из исходных уровней ряда и получение выровненных данных () в аддитивной или () в мультипликативной модели.

4) Аналитическое выравнивание уровней () или () и расчет значений с использованием полученного уравнения тренда.

5) Расчет полученных по модели значений () или ().

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

Методику построения аддитивной модели рассмотрим в данном разделе методического пособия.

Пример. Построение аддитивной модели временного ряда. Обратимся к данным об объеме правонарушений на таможне за четыре года, представленным в табл. 2.1.

Как видно из табл. 2.1, данный временной ряд содержит сезонные колебания периодичностью 4, т. к. количество правонарушений в первый-второй кварталы ниже, чем в третий-четвертый. Рассчитаем компоненты аддитивной модели временного ряда.

Шаг 1. Проведем выравнивание исходных уровней ряда методом скользящей средней. Для этого:

1.1. Просуммируем уровни ряда последовательно за каждые четыре квартала со сдвигом на один момент времени и определим условные годовые объемы потребления электроэнергии (гр. 3 табл. 2.2).

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

Мультипликативная сезонность измеряется в относительных единицах – коэффициентах и в среднем равна 1. Т.е. коэффициент января у нас может получится - 0,9, февраля - 1,1…

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

Как рассчитать аддитивную сезонность в Excel?

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

Для расчета аддитивной сезонности:

Выделим линейный тренд из данных;
Рассчитаем разницу «фактические продажи минус тренд»;
Определим аддитивную сезонность по месяцам - среднее отклонение продаж от тренда для каждого месяца.

1. Выделим линейный тренд из данных.

Для расчета значений тренда для каждого периода времени пронумеруем значения временного ряда – продажи по месяцам:

С помощью функции Excel =предсказ() рассчитаем значения тренда по месяцам:

D5 – X – номер периода, для которого рассчитываем значение тренда;
$C$5:$C$40 – известные значения y — фиксированная ссылка на диапазон с объемами продаж;
$D$5:$D$40 – известные значения X – фиксированная ссылка на диапазон с номерами периодов.

Рассчитали значения тренда:

2. Рассчитываем разницу значений ряда и тренда — объем продаж минус тренд:

3. Определим аддитивную сезонность по месяцам - среднее отклонение продаж от тренда для каждого месяца.

Определяем среднее отклонение для каждого месяца:

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

=СУММЕСЛИ - формула суммирует отклонения по заданным месяцам
$B$5:$B$40; - ссылка на диапазон с номерами месяцев
B5; - номер конкретного месяца для суммирования
$F$5:$F$40 - ссылка на диапазон для суммирования
/ - делим сумму за определенный месяц на количество, получаем среднее по месяцам
СЧЁТЕСЛИ - формула считает количество месяцев в диапазоне
$B$5:$B$40; - диапазон с номерами месяцев
B5 – номер конкретного месяца для счета

Получаем среднее отклонение по месяцам – аддитивную сезонность:

Для расчета прогноза:

Продлеваем тренд в будущее;
К тренду прибавляем аддитивную сезонность соответствующего месяца.

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

Если есть вопросы, пожалуйста, обращайтесь!

Точных вам прогнозов!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast Lite - автоматический расчет прогноза в Excel .
4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

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

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

Регулярную составляющую
Случайную составляющую

В свою очередь регулярная составляющая состоит из:

Тренда
Сезонности
Циклической составляющей

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

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

То есть, в общем случае временной ряд представляет из себя наличие четырех составляющих:

Тренд (Tt)
Сезонность (St)
Цикличность (Ct)
Случайные возмущения (Et)

Циклическая компонента, по сравнению с сезонностью, имеет более длительный эффект и меняется от цикла к циклу. Поэтому, ее обычно объединяют с трендом.

Мультипликативная модель

Смешанная модель

При выборе необходимой модели временного ряда смотрят на амплитуду колебаний сезонной составляющей. Если ее колебания относительно постоянны, то выбирают аддитивную модель. То есть, амплитуда колебаний примерно одинакова:

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Выравниваем ряд с помощью скользящей средней, то есть сглаживаем ряд и отфильтровываем высокочастотные колебания.
Рассчитываем значение сезонной компоненты St.
Рассчитываем значения Tt с использованием полученного уравнения тренда.
Используя полученные значения St и Tt, находим прогнозные значения уровней временного ряда.
Оцениваем качество модели.

Итак, мы имеем на руках данные о продажах за 2016 и 2017 год и хотим спрогнозировать продажи на 2018 год.

Шаг 1

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

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

Иными словами, мы учитываем половины от крайних уровней ряда в диапазоне, в остальном формула не претерпела больше никаких изменений. Вот ее точный вид для нашей задачи:

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Шаг 2

Так как мы рассматриваем аддитивную модель вида:

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

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

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

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

Шаг 3

Теперь рассчитываем значения уровня тренда T(t) по тому уравнению, которое мы получили при построении сглаженного тренда на первом шаге.

T(t) = - 23294 + 34114 * t - 1593 *t^2 + 26,3 *t^3

Вместо t используем значения из столбца Период из соответствующей строки.

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

Вот мы и нашли спрогнозированные значения уровней продаж на 2018 год. Значения отражают возрастающую тенденцию и сезонные пики. Конечно, эти данные не дают 100% точности, ведь существует множество внешних воздействий, которые могут изменить направление тренда, поэтому к прогнозным значениям обычно строят доверительный интервал, это такой коридор, внутри которого могут колебаться прогнозные значения с заданной вероятностью (чаще всего выбирают 95%). Но об этом я расскажу в следующей статье.

Шаг 5

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

Модель может считаться адекватной, если:

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

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

Не забывайте, что прогнозы не бывают точными на 100%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении 🙂

На сегодняшний день наука достаточно далеко продвинулась в разработке технологий прогнозирования. Специалистам хорошо известны методы нейросетевого прогнозирования, нечёткой логики и т.п. Разработаны соответствующие программные пакеты, но на практике они, к сожалению, не всегда доступны рядовому пользователю, а в то же время многие из этих проблем можно достаточно успешно решать, используя методы исследования операций, в частности имитационное моделирование, теорию игр, регрессионный и трендовый анализ, реализуя эти алгоритмы в широко известном и распространённом пакете прикладных программ MS Excel.

Аддитивную модель прогнозирования можно представить в виде формулы:

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

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

Рис. 1. Аддитивная и мультипликативные модели прогнозирования

Алгоритм построения прогнозной модели

Для прогнозирования объема продаж, имеющего сезонный характер, предлагается следующий алгоритм построения прогнозной модели:

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

2 . Вычитая из фактических значений объёмов продаж значения тренда, определяют величины сезонной компоненты и корректируют таким образом, чтобы их сумма была равна нулю.

3. Рассчитываются ошибки модели как разности между фактическими значениями и значениями модели.

4. Строится модель прогнозирования:

5. На основе модели строится окончательный прогноз объёма продаж. Для этого предлагается использовать методы экспоненциального сглаживания, что позволяет учесть возможное будущее изменение экономических тенденций, на основе которых построена трендовая модель. Сущность данной поправки заключается в том, что она нивелирует недостаток адаптивных моделей, а именно, позволяет быстро учесть наметившиеся новые экономические тенденции.

F пр t = a F ф t-1 + (1-а) F м t

для составления прогноза необходимо точно знать величину сезона. Исследования показывают, что множество продуктов имеют сезонный характер, величина сезона при этом может быть различной и колебаться от одной недели до десяти лет и более;
применение полиномиального тренда вместо линейного позволяет значительно сократить ошибку модели;
при наличии достаточного количества данных метод даёт хорошую аппроксимацию и может быть эффективно использован при прогнозировании объема продаж в инвестиционном проектировании.

Применение алгоритма рассмотрим на следующем примере.

Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого “Пломбир” одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.

Объем продаж (руб.)

Задача: составить прогноз продаж продукции на следующий год по месяцам.

Реализуем алгоритм построения прогнозной модели, описанный выше. Решение данной задачи рекомендуется осуществлять в среде MS Excel, что позволит существенно сократить количество расчётов и время построения модели.

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

Рис. 2. Сравнительный анализ полиномиального и линейного тренда

На рисунке показано, что полиномиальный тренд аппроксимирует фактические данные гораздо лучше, чем предлагаемый обычно в литературе линейный. Коэффициент детерминации полиномиального тренда (0,7435) гораздо выше, чем линейного (4E-05). Для расчёта тренда рекомендуется использовать опцию “Линия тренда” ППП Excel.

Рис. 3. Опция "Линии тренда"

логарифмический R 2 = 0,0166;
степенной R 2 = 0,0197;
экспоненциальный R 2 = 8Е-05.

2. Вычитая из фактических значений объёмов продаж значения тренда , определим величины сезонной компоненты , используя при этом пакет прикладных программ MS Excel (рис. 4).

Рис. 4. Расчёт значений сезонной компоненты в ППП MS Excel

Значение тренда

Сезонная компонента

Скорректируем значения сезонной компоненты таким образом, чтобы их сумма была равна нулю.

Сезонная компонента

3. Рассчитываем ошибки модели как разности между фактическими значениями и значениями модели.

Значение модели

Находим среднеквадратическую ошибку модели (Е) по формуле:

Е= 0,003739 или 0.37 %

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

Построим модель прогнозирования:

Построенная модель представлена графически на рис. 5.

5. На основе модели строим окончательный прогноз объёма продаж. Для смягчения влияния прошлых тенденций на достоверность прогнозной модели, предлагается сочетать трендовый анализ с экспоненциальным сглаживанием. Это позволит нивелировать недостаток адаптивных моделей, т.е. учесть наметившиеся новые экономические тенденции:

F пр t = a F ф t-1 + (1-а) F м t

Рис. 5. Модель прогноза объёма продаж

Таким образом, прогноз на январь третьего сезона определяется следующим образом.

Определяем прогнозное значение модели:

F м t = 1 924,92 + 162,44 = 2087 ± 7,8 (руб.)

Фактическое значение объёма продаж в предыдущем году (F ф t-1 ) составило 2 361 руб. Принимаем коэффициент сглаживания 0.8. Получим прогнозное значение объёма продаж:

F пр t = 0,8*2 361 + (1-0.8)*2087 = 2306,2 (руб.)

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

Кроме того, для повышения надёжности прогноза рекомендуется строить все возможные сценарии прогноза и рассчитывать доверительный интервал прогноза.

Юрий
Перед таблицой №3 написано: Скорректируем значения сезонной компоненты таким образом, чтобы их сумма была равна нулю.

Как это сделать?

На мой взгляд, авторы увлеклись использованием доступного инструмента (Эксель), что само по себе очень хорошо, но очень заблуждаются в прогнозировании. Они подробно описывают, как можно дать АНАЛИТИЧЕСКОЕ ОПИСАНИЕ ИМЕЮЩИХСЯ ДАННЫХ (за те 2 года продаж мороженного нашим русским братьям). Да, так можно построить модель. Кстати, не только так - методов существует множество. И выбирать модель лучше с учетом СУТИ описываемого процесса. Например, если в его основе лежат периодические колебания, может оказаться очень полезной модель в виде ряда Фурье. Но это отдельная тема.
Итак, авторы описали "прошлое". Посчитали ошибку - и восхитились. И тут же - фантастический вывод:
"Величина полученной ошибки позволяет говорить, что построенная модель хорошо аппроксимирует фактические данные, т.е. она вполне отражает экономические тенденции, определяющие объём продаж, и является предпосылкой для построения прогнозов высокого качества."
Да откуда же следует, что полученная модель что-то там отражает? И является предпосылкой для прогнозов?
Скажу иначе. Какой смысл с точностью до долей % описывать то, что было, если разница в "одноименных" месяцах парвого и второго года составляет порядка 10%? Мы имеем процесс с сильной случайной составляющей, которая ограничивает возможную точность прогноза и уж конечно не позволит выйти на погрешности в доли процента. Совершенствование прогнозов в таких случаях основано на увеличении статистики - до той степени, пока не начинает мешать нестационарность процесса. В нашем случае, привлечение данных еще по нескольким годам может только навредить, т.к. за такой период произошли существенные изменения в данном процессе и считать его стационарным нельзя ни в каком приближении. Вот и получается, что с имеющейся случайной составляющей мы можем довольно приблизительно кое-что предсказать. Но описывать процесс так старательно вовсе не обязательно. Есть метод и попроще - будут желающие, поделюсь.

Мои слова подтверждаются и замечанием Алексея.
И еще одно. Сама матоснова прогнозирования также должна опираться на предположения о характере процесса. Поэтому "универсального" алгоритма и не существует. Где-то можно и пренебречь случайной составляющей. Но не в приведенном примере, ИМХО.

D!G.ua
В примере фактические данные одного года по месяцам уж оччень пропорциональны факту другого года в тех же месяцах. Другими словами, в примере слишком подавляющее влияние сезонного фактора. А в таком случае прогнозы делать очень легко на факте предыдущих лет. Так что пример не показателен. А вот если бы скажем, в 9-10й месяцы одного года был "нетипичный" провал/пик (на 20%)- погрешность сразу увеличивается до неприемлемых размером. И статистические методы очень плохо работают.

Это не мой метод. В одном из дипломных курсов Школы Бизнеса Лондонского Открытого университета нам приводили нечто подобное, я немного исказил для своих нужд. Более того, использование его в условиях моего
предприятия дает мало пользы. У нас крупнодискретная реализация, за
месяц может быть около десятка продаж. И очень велика случайная
составляющая. Поэтому и точность прогноза невелика.
Однако я допускаю, что в условиях квазинепрерывного потока продаж и
несколько меньшего влияния случайных факторов может быть польза.

Идея же состоит в том, что мы пытаемся прогнозировать по такой
матмодели, которая, НА НАШ ВЗГЛЯД, соответствует реальным
зависимостям. Уже отсюда следует, что УНИВЕРСАЛЬНОГО метода быть не
может. И как ни описывай (очччень точно!) полиномиальными моделями
процесс, у которого налицо периодическая составляющая - для прогноза
это не годится. Аналогично, если в процессе очень сильна случайная
составляющая, то лучше, чем фильтрация ничего нет. Мы просто фильтруем
и берем это ("среднее") значение в качестве прогнозного. Все. Это даст
наименьшуюю ошибку. Естественно, если другие факторы (кроме случайного
шума) слабы. И т.д.

Нужен пример. Пусть мы предполагаем в процесс просто шум. Т.е. модель
того, что мы прогнозируем: постоянная составляющая + шум (с нулевым
средним значением). Тогда алгоритм прогноза: по всем имеющимся данным
находим среднее и считаем это прогнозом.

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

Пусть теперь нам пришло в голову, что в процессе есть периодичность
(продажи очень часто имеют недельную, месячную или годовую
периодичность). Тогда следует решить, какой функцией описать эту
периодичность, найти параметры данной функции по известным данным - и
затем продолжить ее в область будущего. Если функция близка к синусу -
можно просто определить ее частоту, амплитуду и фазу. Но если функция
искажена, то для ее хорошего описания потребуется находить параметры
нескольких гармоник (спектр). Это может быть громоздко. Тогда можно
просто найти т.н. "структуру" в пределах периода. Например, период -
неделя. Тогда можно найти, что в среднем продажи в понедельник
отличаются от среднего значения за предыдущие 7 дней на минус 14%, во
вторник - на минус 15%, а в субботу - на плюс 33% от этого же
среднего. Эти числа назовем коэффициентами структуры. Дальше просто -
продлеваем среднее (просто усреднением или как линию тренда - см.
предыдущие примеры) на будущее, а к ней прибавляем произведение этого
же среднего на коэффициент структуры.

Путано? Пожалуй. Кто сам пробовал - поймет. Кто знает больше -
поправит или предложит свое. Главное в описанном подходе - изучаем
исходные данные не для точного их описания, а для решения об
адекватной модели. Затем ищем параметры модели - и продлеваем эту
функцию в будущее. Угадали модель - прогноз лучше. Шумы мешают. Но
увеличение длительности наблюдения чревато тем, что для построения
модели мы начинаем использовать такие старые данные, что поведение
процесса уже существенно изменилось. Что ж, подбираем оптимум,
набиваем шишки, влетаем в убытки, банкротим предприятие, переходим в
другое, но уже с чуством огромного опыта за плечами :)

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

С уважением и благодарностью к заинтересовавшимся и в надежде на конструктивную критику.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Любому бизнесу интересно заглянуть в будущее и правильно ответить на вопрос: «А сколько денег мы заработаем за следующий период?» Ответить на такого рода вопросы позволяют различные методики прогнозирования. В данной статье мы с вами рассмотрим несколько таких методик и произведем все необходимые расчеты в Excel. Еще больше про анализ данных в Excel мы рассказываем на нашем открытом курсе «Аналитика в Excel».

Постановка задачи

Исходные данные

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

Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.

Составляющие прогноза

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

Изменение нашей пронозируемой величины (например, продаж) подчиняется некоторому закону. Другими словами, в временном ряде можно проследить некую тенденцию. В математике такая тенденция называется трендом.
Изменение значений в временном ряде может зависить от промежутка времени. Другими словами, при построении модели необходимо будет учесть коэффициент сезонности. Например, продажи арбузов в январе и августе не могут быть одинаковыми, т.к. это сезонный продукт и летом продажи значительно выше.
Изменение значений в временном ряде периодически повторяется, т.е. наблюдается некоторая цикличность.

Эти три пункта в совокупность образуют регулярную составляющую временного ряда.

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

Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.

Виды моделей

Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”

Обычно выделяют два основных вида:

Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения

Иногда также выделают смешанную модель в отдельную группу:

Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения

С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»

Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.

Решение задачи с помощью Excel

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

с использованием линейного тренда
с использованием полиномиального тренда

Во всех руководствах, как правило, разбирается только линейный тренд, поэтому полиномиальная модель будет крайне полезна для вас и вашей работы!

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Модель с линейным трендом

Пусть у нас есть исходная информация по продажам за 2 года:

Учитывая, что мы используем линейный тренд, то нам необходимо найти коэффициенты уравнения

Рассчитать коэффициенты данного уравнения можно с помощью формулы массива и функции ЛИНЕЙН. Нам необходимо будет сделать следующую последовательность действий:

Выделяем две ячейки рядом
Ставим курсор в поле формул и вводим формулу =ЛИНЕЙН(C4:C27;B4:B27)
Нажимаем Ctrl+Shift+Enter, чтобы активировать формулу массива

Теперь нам нужно рассчитать для каждого периода значение линейного тренда. Сделать это крайне просто — достаточно в полученное уравнение подставить известные номера периодов. Например, в нашем случае, мы прописываем формулу =B4*$F$4+$G$4 в ячейке I4 и протягиваем ее вниз по всем периодам.

Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Можем сделать следующим образом: в ячейке J4 прописываем формулу =(C4+C16)/СРЗНАЧ($C$4:$C$27)/2 и протягиваем вниз на 12 месяцев (т.е. до J15).

Что нам это дало? Мы посчитали, сколько суммарно продавалось каждый январь/каждый февраль и так далее, а потом разделили это на среднее значение продаж за все два периода.

То есть мы выяснили, как продажи двух январей отклонялись от средних продаж за два года, как продажи двух февралей отклонялись и так далее. Это и дает нам коэффициент сезонности. В конце формулы делим на 2, т.к. в расчете фигурировало 2 периода.

Примечание. Рассчитали только 12 коэффициентов, т.к. один коэффициент учитывает продажи сразу за 2 аналогичных периода.

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

Сначала создаем столбец, в котором прописываем номера будущих периодов. В нашем случае нумерация начинается с 25 периода.

Далее, для расчета значения тренда просто прописываем уже известную нам формулу =L4*$F$4+$G$4 и протягиваем вниз на все 12 прогнозируемых периодов.

И последний штрих — умножаем полученное значение на коэффициент сезонности. Вуаля, это и есть итоговый ответ в данной модели!

Модель с полиномиальным трендом

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

Посмотрите сами, какая модель более точно аппроксимирует наши точки — линейный тренд (прямая зеленая линия) или полиномиальный тренд (красная кривая)? Ответ очевиден. Поэтому сейчас мы с вами и разберем, как построить полиномиальную модель в Excel.

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

Для начала давайте определимся, чем полиномиальный тренд отличается от обычного линейного. Правильно — формой уравнения. У линейного тренда мы разбирали обычный график прямой:

У полиномиального тренда же уравнение выглядит иначе:

где конечная степень определяется степенью полинома.

Т.е. для полинома 4 степени необходимо найти коэффициенты уравнения:

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

Ставим в ячейку F4 курсор и вводим формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^);1;1). Функция ЛИНЕЙН позволяет произвести расчет коэффициентов, а с помощью функции ИНДЕКС мы вытаскиваем нужный нам коэффициент. В данном случае за выбор коэффициента отвечает самый последний аргумент. У нас стоит 1 — это коэффициент при самой высокой степени (т.е. при 4 степени, коэффициент). Кстати, узнать о самых полезных математических формулах Excel можно в нашем бесплатном гайде «Математические функции Excel».
Аналогично прописываем формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^);1;2) в ячейке ниже.
Делаем такие же действия, пока не найдем все коэффициенты.

Кстати говоря, мы можем легко сами себя проверить. Давайте построим график наших продаж и добавим к нему полиномиальный тренд.

Выделяем столбец с продажами
Выбираем «Вставка» → «График» → «Точечный» → «Точечная диаграмма»
Нажимаем на любую точку графика правой кнопкой мыши и выбираем «Добавить линию тренда»
В открывшемся справа меню выбираем «Полиномиальная модель», меняем степень на 4 и ставим галочку на «Показывать уравнение на диаграмме»

Теперь вы наглядно можете видеть, как рассчитанный тренд аппроксимирует исходные данные и как выглядит само уравнение. Можно сравнить уравнение на графике с вашими коэффициентами. Сходится? Значит сделали все верно!

Помимо всего прочего, вы можете сразу оценить точность аппроксимации (не полностью, но хотя бы первично). Это делается с помощью коэффициента R^2. Тут у вас снова есть два пути:

Вы можете вывести коэффициент на график, поставив галочку «Поместить на диаграмму величину достоверности аппроксимации»
Вы можете рассчитать коэффициент R^2 самостоятельно по формуле =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^;;1);3;1)

Заключение

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

Если вас интересуют еще какие-то модели прогнозирования — напишите нам об этом, и мы постараемся осветить эти темы в дальнейших своих статьях! Или запишитесь на курс «Excel Academy» от SF Education, где мы рассказываем про возможности Excel, необходимые для анализа.

Автор: Алексанян Андрон, эксперт SF Education

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Читайте также: