Выявление закономерностей в данных
Есть способ испытать прогностическую модель на прочность — сравнить погрешности сами с собой, сдвинутыми на шаг (или несколько шагов). Если отклонения случайны, то улучшить модель нельзя. Однако, возможно, в данных о спросе есть сезонный фактор. Концепция погрешности, коррелирующей с собственной версией за другой период, называется автокорреляцией (подробнее об автокорреляции см. Простая линейная регрессия). Чтобы рассчитать автокорреляцию, начните с данных об ошибке прогноза за каждый период (столбец F на рис. 7 переносим в столбец В на рис. 10). Далее определите среднюю ошибку прогноза (рис. 10, ячейка В39; формула в ячейке: =СРЗНАЧ(B3:B38)). В столбце С рассчитайте отклонение ошибки прогноза от среднего; формула в ячейке С3: =B3-B$39. Далее последовательно сдвигайте столбец С на столбец вправо и строку вниз. Формулы в ячейках D39: =СУММПРОИЗВ($C3:$C38;D3:D38), D41: =D39/$C39, D42: =2/КОРЕНЬ(36), D43: =-2/КОРЕНЬ(36).
Рис. 10. Расчет автокорреляции
Что может значить для одного из столбцов D:O «синхронное движение» со столбцом С. Например, если столбцы С и D синхронны, то число, отрицательное в одном из них, должно быть отрицательным и в другом, положительное в одном, положительное – в другом. Это означает, что сумма произведений двух столбцов будет значительной (отличия накапливаются). Или, что тоже самое, чем ближе значение в диапазоне D41:О41 к нулю, тем ниже корреляция столбца (соответственно от D до О) со столбцом С (рис. 11).
Рис. 11. Диаграмма автокорреляции
Одна автокорреляция выше критического значения. Погрешность, сдвинутая на год, коррелирует сама с собой. Это означает 12-месячный сезонный цикл. И это неудивительно. Если вы посмотрите на график спроса (рис. 2), то окажется, что есть пики спроса на каждое Рождество и провалы в апреле-мае. Рассмотрим технику прогнозирования, учитывающую сезонность.
Метод экспоненциального сглаживания
Освоение новых и анализ известных управленческих технологий, которые позволяют повысить эффективность управления бизнесом, становится особенно актуальным для российских предприятий в настоящее время. Один из наиболее популярных инструментов — система бюджетирования, которая базируется на формировании бюджета предприятия с последующим контролем исполнения. Бюджет представляет собой сбалансированные краткосрочные коммерческие, производственные, финансовые и хозяйственные планы развития организации. Бюджет предприятия содержит целевые показатели, которые рассчитываются на основании прогнозных данных. Наиболее значимым прогнозом при составлении бюджета для любого предприятия является прогноз продаж. В предыдущих статьях был проведен анализ аддитивной и мультипликативной модели и рассчитан прогнозный объем продаж на следующие периоды.
При анализе временных рядов использовался метод скользящей средней, в котором все данные независимо от периода их возникновения являются равноправными. Существует другой способ, в котором данным приписываются веса, более поздним данным придается больший вес, чем более ранним.
Метод экспоненциального сглаживания в отличие от метода скользящих средних еще и может быть использован для краткосрочных прогнозов будущей тенденции на один период вперед и автоматически корректирует любой прогноз в свете различий между фактическим и спрогнозированным результатом. Именно поэтому метод обладает явным преимуществом над ранее рассмотренным.
Название метода происходит из того факта, что при его применении получаются экспоненциально взвешенные скользящие средние по всему временному ряду. При экспоненциальном сглаживании учитываются все предшествующие наблюдения — предыдущее учитывается с максимальным весом, предшествующее ему — с несколько меньшим, самое ранее наблюдение влияет на результат с минимальным статистическим весом.
Алгоритм расчета экспоненциально сглаженных значений в любой точке ряда i основан на трех величинах
:
фактическое значение Ai в данной точке ряда i,
прогноз в точке ряда Fi
некоторый заранее заданный коэффициент сглаживания W, постоянный по всему ряду.
Новый прогноз можно записать формулой:
Расчет экспоненциально сглаженных значений
При практическом использовании метода экспоненциального сглаживания возникает две проблемы: выбор коэффициента сглаживания (W), который в значительной степени влияет на результаты и определение начального условия (Fi). С одной стороны, для сглаживания случайных отклонений величину нужно уменьшать. С другой стороны, для увеличения веса новых измерений нужно увеличивать.
Хотя, в принципе, W может принимать любые значения из диапазона 0
Выбор коэффициента постоянной сглаживания является субъективным. Аналитики большинства фирм при обработке рядов используют свои традиционные значения W. Так, по опубликованным данным в аналитическом отделе Kodak, традиционно используют значение 0,38, а на фирме Ford Motors — 0,28 или 0,3.
Ручной расчет экспоненциального сглаживания требует крайне большого объема монотонной работы. На примере рассчитаем прогнозный объем на 13 квартал, если имеются данные объема продаж за последние 12 кварталов, используя метод простого экспоненциального сглаживания.
Предположим, что на первый квартал прогноз продаж составил 3. И пусть коэффициент сглаживания W =0,8.
Заполним в таблице третий столбец, подставляя для каждого последующего квартала значение предыдущего по формуле:
Для 2 квартала F2 =0,8*4 (1-0,8)*3 =3,8
Для 3 квартала F3 =0,8*6 (1-0,8)*3,8 =5,6
Аналогично, рассчитывается сглаженное значение для коэффициента 0,5 и 0,33.
Расчет прогноза
объема
продаж
Прогноз объема продаж при W = 0.8 на 13 квартал составил 13.3 тыс.руб.
Эти данные можно представить в графической форме:
Экспоненциальное сглаживание
Скользящая средняя позволяет прекрасно сглаживать данные. Но ее главный недостаток заключатся в том, что каждое значение в исходных данных для нее имеет одинаковый вес. Например, для средней скользящей использующей период шести недель каждому значению для каждой недели уделяется 1/6 веса. В случае некоторых собранных статистических данных более актуальным значениям присваивается больший вес. Поэтому экспоненциальное сглаживание применятся для того, чтобы придать самым актуальным данным большего веса. Таким образом решается данная статистическая проблема.
Инструменты сглаживания программы MS EXCEL
В программе EXCEL имеется всего два инструмента анализа, используемые для сглаживания временного ряда. Элементы диалогового окна «Скользящее среднее» представлены на рис. 3.1.
Рис. 3.1. Инструмент анализа «Скользящее среднее»
Необходимо ввести следующие аргументы:
- «Входной интервал» — анализируемый ряд (должен состоять из одного столбца или одной строки).
- «Интервал» — «размер окна» (по умолчанию используется 3).
- «Метки в первой строке» — необходимо установить флажок, если первая строка (или столбец) входного интервала содержит заголовок.
- «Выходной диапазон» — должен находиться на одном листе с исходными данными. По этой причине параметры «Новый лист» и «Новая книга» недоступны. Необходимо ввести ссылку на левую верхнюю ячейку выходного диапазона.
- «Стандартные погрешности» — если установлен флажок, то выходной диапазон состоит из двух столбцов, и значения стандартных погрешностей содержатся в правом столбце.
- «Вывод графика» — если установлен флажок, то создаётся встроенная диаграмма на листе, содержащем выходной диапазон.
Элементы диалогового окна «Экспоненциальное сглаживание» представлены на рис. 3.2.
Как сделать схему для вязания в excel?
Здесь имеется ранее не представленный аргумент «Фактор затухания», представляющий собой константу экспоненциального сглаживания — корректировочный фактор, минимизирующий нестабильность данных генеральной совокупности. По умолчанию значение аргумента «Фактор затухания» равно 0,3. Наиболее подходящим интервалом значений этого параметра сглаживания считается промежуток от 0,2 до 0,3.
Обнаружение и анализ тренда
Обычно анализ временного ряда начинается с выявления тренда.Выделение тренда очень важно, т.к. его исключение позволяет перейти к дальнейшей идентификации других компонент ряда
Окончательная проверка реализаций на наличие трендов может быть выполнена различными способами.
При этом желательно знание закона распределения, например, нормального, или применение непараметрических критериев, при использовании которых не требуется знание выборочных распределений оценок.
Показатели динамики
Наличие или отсутствие тренда обычно хорошо видно по графику временного ряда (см., например, рис. 8.1) или по специальным аналитическим «показателям динамики ВР».
Показатели динамики разделяются на следующие важнейшие виды:
«абсолютный прирост» равен разности Δ двух сравниваемых уровней и характеризует изменение показателя за определенный промежуток текущей переменной.
- «темп роста» Т (всегда положителен) характеризует отношение двух сравниваемых уровней ряда, как правило, выраженное в процентах.
- «темп прироста» K.
- Причем каждый из указанных видов показателей может быть трех типов:
- «цепной» — если сравнение осуществляется при переменной базе, и каждый последующий уровень сравнивается с предыдущим
- «базисный» — если сравнение осуществляется с одним и тем же уровнем, принятым за базу сравнения;
- «средний».
Например, «средний абсолютный прирост» — это обобщающая характеристика скорости изменения исследуемого показателя во времени (скоростью будем называть прирост в единицу времени). Для его определения за весь период наблюдения используется формула простой средней арифметической «цепного абсолютного прироста».
«Средний темп роста» — обобщающая характеристика, отражающая интенсивность изменения уровней ряда. Он показывает, сколько в среднем процентов последующий уровень составляет от предыдущего на всем периоде наблюдения. Этот показатель рассчитывается по формуле средней геометрической n последовательных цепных темпов роста.
Формулы расчёта всех видов и типов показателей динамики представлены в табл. 8.1.
Таблица 8.1. Основные показатели динамики ВР
| Вид показателя | Абсолютный прирост | Темп роста % | Темп прироста % |
| Цепной | |||
| Базисный | |||
| Средний |
Алгоритм прогнозирования объёма продаж в MS Excel
На сегодняшний день наука достаточно далеко продвинулась в разработке технологий прогнозирования. Специалистам хорошо известны методы нейросетевого прогнозирования, нечёткой логики и т.п. Разработаны соответствующие программные пакеты, но на практике они, к сожалению, не всегда доступны рядовому пользователю, а в то же время многие из этих проблем можно достаточно успешно решать, используя методы исследования операций, в частности имитационное моделирование, теорию игр, регрессионный и трендовый анализ, реализуя эти алгоритмы в широко известном и распространённом пакете прикладных программ MS Excel.
В данной статье представлен один из возможных алгоритмов построения прогноза объёма реализации для продуктов с сезонным характером продаж. Сразу следует отметить, что перечень таких товаров гораздо шире, чем это кажется. Дело в том, что понятие “сезон” в прогнозировании применим к любым систематическим колебаниям, например, если речь идёт об изучении товарооборота в течение недели под термином “сезон” понимается один день. Кроме того, цикл колебаний может существенно отличаться (как в большую, так и в меньшую сторону) от величины один год. И если удаётся выявить величину цикла этих колебаний, то такой временной ряд можно использовать для прогнозирования с использованием аддитивных и мультипликативных моделей.
Аддитивную модель прогнозирования можно представить в виде формулы:
где: F – прогнозируемое значение; Т – тренд; S – сезонная компонента; Е – ошибка прогноза.
Применение мультипликативных моделей обусловлено тем, что в некоторых временных рядах значение сезонной компоненты представляет собой определенную долю трендового значения. Эти модели можно представить формулой:
На практике отличить аддитивную модель от мультипликативной можно по величине сезонной вариации. Аддитивной модели присуща практически постоянная сезонная вариация, тогда как у мультипликативной она возрастает или убывает, графически это выражается в изменении амплитуды колебания сезонного фактора, как это показано на рисунке 1.
Рис. 1. Аддитивная и мультипликативные модели прогнозирования.
Алгоритм построения прогнозной модели
Для прогнозирования объема продаж, имеющего сезонный характер, предлагается следующий алгоритм построения прогнозной модели:
1.Определяется тренд, наилучшим образом аппроксимирующий фактические данные. Существенным моментом при этом является предложение использовать полиномиальный тренд, что позволяет сократить ошибку прогнозной модели.
2 .Вычитая из фактических значений объёмов продаж значения тренда, определяют величины сезонной компоненты и корректируют таким образом, чтобы их сумма была равна нулю.
3.Рассчитываются ошибки модели как разности между фактическими значениями и значениями модели .
4.Строится модель прогнозирования:
где: F – прогнозируемое значение; Т – тренд; S – сезонная компонента; Е – ошибка модели.
5.На основе модели строится окончательный прогноз объёма продаж. Для этого предлагается использовать методы экспоненциального сглаживания, что позволяет учесть возможное будущее изменение экономических тенденций, на основе которых построена трендовая модель. Сущность данной поправки заключается в том, что она нивелирует недостаток адаптивных моделей, а именно, позволяет быстро учесть наметившиеся новые экономические тенденции.
F пр t = a F ф t-1 + (1-а) F м t
где: F пр t – прогнозное значение объёма продаж; F ф t- 1 – фактическое значение объёма продаж в предыдущем году; F м t – значение модели; а – константа сглаживания
Практическая реализация данного метода выявила следующие его особенности:
- для составления прогноза необходимо точно знать величину сезона. Исследования показывают, что множество продуктов имеют сезонный характер, величина сезона при этом может быть различной и колебаться от одной недели до десяти лет и более;
- применение полиномиального тренда вместо линейного позволяет значительно сократить ошибку модели;
- при наличии достаточного количества данных метод даёт хорошую аппроксимацию и может быть эффективно использован при прогнозировании объема продаж в инвестиционном проектировании.
Применение алгоритма рассмотрим на следующем примере.
Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого “Пломбир” одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.
Таблица 1. Фактические объёмы реализации продукции
Шаг 5
Осталось оценить точность модели. Для этого будем использовать среднюю ошибку аппроксимации, которая поможет рассчитать ошибку в относительном выражении. Иными словами, это среднее отклонение расчетных значений от фактических, которое вычисляется по формуле:
yi — спрогнозированные уровни ряда,
yi* — фактические уровни ряда,
n — количество складываемых элементов.
Модель может считаться адекватной, если:
Итак, рассчитываем ошибку аппроксимации для нашего случая. Так как в основе нашего тренда лежит полином третьей степени, прогнозные значения начинают хорошо повторять фактические значения к концу 2016 года, думаю, я думаю, поэтому корректнее было бы рассчитать ошибку аппроксимации для значений 2017 года.
Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.
Не забывайте, что прогнозы не бывают точными на 100%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении
Качество прогнозирования
Проверка качества прогнозирования возможна в случае наличия достаточной выборки и является важной проверкой на достоверность
прогноза, для проверки и оптимизации значений α, β и γ необходимо построить прогноз на существующие данные,
например, если у нас в наличии данные за пять лет и мы хотим предсказать следующий год, то необходимо построить модель на первых
четырёх годах, проверить и оптимизировать коэффициенты для минимизации ошибки между прогнозом и данными на 5й год. После оптимизации
модель может быть перестроена с учётом последнего периода для повышения точности, далее следует построение прогноза.. Методы оптимизации будут описаны в отдельной статье, ниже представлен пример прогнозирования методом Хольт Винтерса.
Методы оптимизации будут описаны в отдельной статье, ниже представлен пример прогнозирования методом Хольт Винтерса.
График 9. Данные о посещаемости сайта за четыре недели
| # | Данные | s | t | p | s | t | p |
|---|---|---|---|---|---|---|---|
| 1 | 93 | 93 | 93 | ||||
| 2 | 91 | 92 | -0.1 | -0.5 | 92 | -0.1 | 0.99 |
| 3 | 72 | 84 | -0.89 | -6 | 84 | -0.89 | 0.93 |
| 4 | 75 | 80 | -1.2 | -2.5 | 80 | -1.2 | 0.97 |
| 5 | 75 | 77 | -1.38 | -1 | 77 | -1.38 | 0.99 |
| 6 | 57 | 68 | -2.14 | -5.5 | 68 | -2.14 | 0.92 |
| 7 | 66 | 66 | -2.13 | 66 | -2.13 | 1 | |
| 8 | 123 | 88 | 0.28 | 17.5 | 38 | -4.72 | 1.62 |
| 9 | 85 | 87 | 0.15 | -1.25 | 54 | -2.65 | 1.28 |
| 10 | 85 | 89 | 0.34 | -5 | 67 | -1.09 | 1.1 |
| 11 | 91 | 91 | 0.51 | -1.25 | 77 | 0.02 | 1.08 |
| 12 | 102 | 96 | 0.96 | 2.5 | 87 | 1.02 | 1.08 |
| 13 | 73 | 90 | 0.26 | -11.25 | 85 | 0.72 | 0.89 |
| 14 | 60 | 78 | -0.97 | -9 | 75 | -0.35 | 0.9 |
| 15 | 99 | 79 | -0.77 | 18.75 | 69 | -0.92 | 1.53 |
| 16 | 108 | 91 | 0.51 | 7.88 | 75 | -0.23 | 1.36 |
| 17 | 98 | 96 | 0.96 | -1.5 | 80 | 0.29 | 1.16 |
| 18 | 104 | 100 | 1.26 | 1.38 | 87 | 0.96 | 1.14 |
| 19 | 83 | 93 | 0.43 | -3.75 | 84 | 0.56 | 1.03 |
| 20 | 68 | 88 | -0.11 | -15.63 | 81 | 0.2 | 0.86 |
| 21 | 62 | 81 | -0.8 | -14 | 76 | -0.32 | 0.86 |
| 22 | 59 | 64 | -2.42 | 6.88 | 61 | -1.79 | 1.25 |
| 23 | 80 | 66 | -1.98 | 10.94 | 59 | -1.81 | 1.36 |
| 24 | 121 | 87 | 0.32 | 16.25 | 76 | 0.07 | 1.38 |
| 25 | 112 | 97 | 1.29 | 8.19 | 85 | 0.96 | 1.23 |
| 26 | 85 | 94 | 0.86 | -6.38 | 85 | 0.86 | 1.02 |
| 27 | 106 | 106 | 1.97 | -7.82 | 101 | 2.37 | 0.95 |
| 28 | 82 | 103 | 1.47 | -17.5 | 100 | 2.03 | 0.84 |
График 9. Пример предсказания посещаемости сайта на основе данных за четыре недели. Жёлтая линия — исходные данные, красная — прогноз на пятую
неделю на основе первых четырёх. Закрашена линия сглаженного уровня при α=0.4, β=0.1, γ=0.5
График экспоненциального сглаживания
Ниже на рисунке изображен график спроса, среднее скользящие и прогноз методом экспоненциального сглаживания, который построен на основе исходных значений:
Обратите внимание, что прогноз с экспоненциальным сглаживанием более активно реагирует на изменения спроса чем скользящая средняя линия. Данные для очередных предыдущих недель умножаются на коэффициент альфа, а результат добавляется к оставшейся части процентов веса умноженный на предыдущее прогнозируемое значение
Данные для очередных предыдущих недель умножаются на коэффициент альфа, а результат добавляется к оставшейся части процентов веса умноженный на предыдущее прогнозируемое значение.
Экстраполяция
— это метод научного исследования, который основан на распространении прошлых
и настоящих тенденций, закономерностей, связей на будущее развитие объекта прогнозирования. К методам экстраполяции
относятся
метод скользящей средней, метод экспоненциального сглаживания, метод наименьших квадратов.
Метод экспоненциального сглаживания
наиболее эффективен при разработке среднесрочных прогнозов.
Он приемлем при прогнозировании только на один период вперед. Его основные достоинства простота процедуры вычислений и возможность
учета весов исходной информации. Рабочая формула метода экспоненциального сглаживания:
При прогнозировании данным методом возникает два затруднения:
- выбор значения параметра сглаживания α;
- определение начального значения Uo.
От величины α зависит
, как быстро снижается вес влияния предшествующих наблюдений. Чем больше α, тем
меньше сказывается влияние предшествующих лет. Если значение α близко к единице, то это приводит к учету при прогнозе в основном влияния
лишь последних наблюдений. Если значение α близко к нулю, то веса, по которым взвешиваются уровни временного ряда, убывают медленно,
т.е. при прогнозе учитываются все (или почти все) прошлые наблюдения.
Таким образом, если есть уверенность, что начальные условия, на основании которых разрабатывается прогноз, достоверны, следует
использовать небольшую величину параметра сглаживания (α→0). Когда параметр сглаживания мал, то исследуемая функция ведет себя как
средняя из большого числа прошлых уровней. Если нет достаточной уверенности в начальных условиях прогнозирования, то следует использовать
большую величину α, что приведет к учету при прогнозе в основном влияния последних наблюдений.
Точного метода для выбора оптимальной величины параметра сглаживания α нет. В отдельных случаях автор данного метода профессор Браун
предлагал определять величину α, исходя из длины интервала сглаживания. При этом α вычисляется по формуле:
где n – число наблюдений, входящих в интервал сглаживания.
Задача выбора Uo
(экспоненциально взвешенного среднего начального) решается следующими
способами:
- если есть данные о развитии явления в прошлом, то можно воспользоваться средней арифметической и приравнять к ней Uo;
- если таких сведений нет, то в качестве Uo используют исходное первое значение базы прогноза У1.
Также можно воспользоваться экспертными оценками.
Отметим, что при изучении экономических временных рядов и прогнозировании экономических процессов метод экспоненциального сглаживания
не всегда «срабатывает». Это обусловлено тем, что экономические временные ряды бывают слишком короткими (15-20 наблюдений), и в случае,
когда темпы роста и прироста велики, данный метод не «успевает» отразить все изменения.
Рассмотрим ВПР в подробностях.
По ходу статьи мы:
- Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;
- Рассчитаем скользящую среднюю к 3-м месяцам по позициям;
- Скорректируем скользящую среднюю сезонностью по группе. Коэффициенты сезонности подтянем с помощью ВПР и разберем функция по частям.
1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;
Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе 1 и 2 с помощью Forecast4AC PRO (Как самостоятельно рассчитать коэффициенты сезонности к 3-м месяцам можете прочитать в статье «Расчет прогноза по методу скользящей средней!»)
Для этого установим курсор в начало продаж по товарным группам:
Выберите в настройках «Сезонность» «к 3-м месяцам»:
Нажимаем кнопку «Рассчитать». Получаем в продолжении ряда коэффициенты сезонности к 3-м месяцам:
Копируем сезонность на отдельный лист «к 3-м» получаем табличку, в которой в первом столбце названия товарных групп, а в столбцах со 2-го по 13-й — коэффициенты сезонности для 1 — 12 месяцев:
2. Рассчитаем скользящую среднюю к 3-м месяцам по позициям.
Используем стандартную функцию =срзнач(продажи за 3 последних месяца):
Протянем среднюю на все позиции на 24 месяца вперед:
3. Скорректируем скользящую среднюю сезонностью по группе и разберем ВПР.
Теперь средние продажи умножим на коэффициент сезонности по товарной группе, который подтянем с помощью функции ВПР.
В ВПР передаем (искомое значение (название товарной группы); таблицу, в которой ищем искомое значение; номер столбца, из которого возвращаем коэффициент сезонности для соответствующего месяца; и интервальный просмотр (ставим «0» — т.к
нам важно точно совпадения названия товарной группы)). 1
В искомое значение передаем название товарной группы и фиксируем столбец:
1. В искомое значение передаем название товарной группы и фиксируем столбец:
=СРЗНАЧ(BD3:BF3)*впр($C3 (передаем название товарной группы и фиксируем столбец с помощью значка «$»);’к 3-м’!$A$3:$M$4;данные!BG$2+1;0)
Подробнее о фиксировании ссылок читайте в статье «Как зафиксировать ссылку в Excel».
2. В таблицу передаем таблицу с коэффициентами сезонности для товарных групп и фиксируем таблицу:
=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4(передаем таблицу с товарными группами и фиксируем таблицу с помощью значка «$»);данные!BG$2+1;0)
В первом столбце таблицы содержатся искомые значения — названия товарных групп. Фиксируем таблицу, чтобы формула имела такой вид ‘к 3-м’!$A$3:$M$4 и ссылки не поехали, когда мы будем протягивать формулу.
3. Далее в ВПР передаем номер столбца, в котором содержится искомый коэффициент сезонности соответствующего месяца сезонности в прогнозе
=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1(передаем номер столбца в котором содержится искомый коэффициент сезонности для соответствующего месяца и фиксируем строку с номерами столбцов месяца с помощью значка «$»);0)
Т.к. номер столбца в таблице с сезонностью для первого месяца будет вторым, то прибавляем «1»
=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1(прибавляем 1, т.к. номер столбца в таблице с сезонностью для первого месяца 2, в первом столбце название товарных групп);0)
4. =СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1;0 (ищем точное соответствие названий товарных групп))
Протягиваем полученную формулу, получаем средние продажи за 3 предыдущие месяца по товарной позиции скорректированные сезонностью по товарной группе к 3-м месяцам:
=СРЗНАЧ(BD3:BF3)*ВПР($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1;0)
Получаем расчет прогноза по методу скользящей средней к 3-м месяцам по товарным позициям, используя сезонность по товарной группе.
Данный подход может значительно увеличить точность расчета прогноза по товарным позициям внутри группы. Попробуйте рассчитать прогноз по методу скользящей средней к 2-м и 4-м месяцам, используя функцию ВПР и Forecast4AC PRO на текущий год, и сравните прогнозы с фактическими продажами. Выберите модель, которая была максимально близка к факту.
Точных вам прогнозов!
- Novo Forecast Lite — автоматический расчет прогноза в Excel.
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Метод экспертных оценок
Экспертный метод позволяет получить самую субъективную оценку будущего денежного потока компании, поскольку основан на субъективных оценках экспертов (в роли которых выступают обычно сотрудники соответствующих подразделений компании). Преимуществом данного метода является то, что он может быть применен в условиях, когда исторических данных или технических средств для построения объективного прогноза не хватает, или в условиях полной неопределенности. В таком случае, например, сотрудники, отвечающие за собираемость дебиторской задолженности, составляют прогноз поступлений денежных средств от операционной деятельности с учетом качества дебиторской задолженности, сроков ее погашения и классифицируют ожидаемые поступления по курируемым клиентам и степени вероятности (базовый прогноз, оптимистичный и пессимистичный).
Как рассчитать прогноз по методу экспоненциального сглаживания в Excel?
Формула расчета прогноза проста:
Ŷt+1=k*Yt +(1-k)* Ŷt
Где:
- Ŷt+1 – прогноз на следующий период t+1;
- Yt – данные для прогноза за текущий период t (например, продажи по месяцам);
- k – коэффициент сглаживания ряда , k задается вами вручную и находится в диапазоне от 0 до 1, 0 < k < 1
- Ŷt – значение прогноза на текущий период t. Причем в первый период (месяц, день…) Ŷ1=Y1, т.е. Ŷt в первый период равны продажам в этот период.
Прогноз по методу экспоненциального сглаживания = коэффициент сглаживания * последнее фактическое значение продаж + (1- коэффициент сглаживания)*предыдущий прогноз по методу экспоненциального сглаживания.
Важно отметить, что данная модель предполагает регулярный пересчет прогноза по окончании последнего периода и появлении новых данных для прогноза за последний период
пример
Страховая компания решила расширить свой рынок до крупнейшего города страны, обеспечивая страхование транспортных средств.
В качестве первоначального действия компания хочет спрогнозировать, сколько автостраховок приобретут жители этого города.
Для этого они будут использовать в качестве исходных данных сумму страховки автомобиля, приобретенную в другом меньшем городе.
Прогноз спроса на период 1 составляет 2 869 договорных транспортных средств, но реальный спрос в этот период составлял 3 200.
По усмотрению компании присваивает коэффициент сглаживания 0,35. Прогнозируемый спрос на следующий период: P2 = (3200 * 0,35) + 2869 * (1-0,35) = 2984,85.
Такой же расчет был произведен для всего года, в результате чего была получена следующая сравнительная таблица между тем, что было фактически получено, и тем, что было прогнозом на этот месяц.
По сравнению с методами усреднения экспоненциальное сглаживание может лучше предсказывать тренд. Однако этого все еще не хватает, как показано на графике:
Можно увидеть, как серая линия прогноза может быть значительно ниже или выше синей линии спроса, не имея возможности полностью следовать ей.
Идентификация выбросов временных рядов
Выбросы в данных временных рядов — это значения, которые существенно отличаются от закономерностей и тенденций других значений во временных рядах. Например, большое количество покупок в интернете во время праздников или большое количество дорожно-транспортных происшествий во время сильных ливней, скорее всего, будут обнаружены как выбросы в их временных рядах. Простые ошибки ввода данных, такие как пропуск десятичной части числа, являются еще одним распространенным источником выбросов
Выявление выбросов в прогнозе временных рядов важно, поскольку выбросы влияют на прогнозную модель, которая используется для прогнозирования будущих значений, и даже небольшое количество выбросов во временных рядах местоположения может значительно снизить точность и надежность прогнозов. Местоположения с выбросами, особенно в начале или в конце временного ряда, могут давать неверные прогнозы, и идентификация этих местоположений помогает определить, насколько вы должны быть уверены в прогнозируемых значениях в каждом местоположении
Выбросы определяются не просто их исходными значениями, а тем, насколько их значения отличаются от установленных значений прогнозной модели. Это означает, что определение того, является ли значение выбросом, является контекстуальным и зависит как от его места, так и от времени. Прогнозная модель определяет, какое значение ожидается на основе всего временного ряда, а выбросы – это значения, которые сильно отклоняются от этого базового уровня. Например, рассмотрим временной ряд среднегодовой температуры. Поскольку средние температуры увеличились за последние несколько десятилетий, подходящая прогнозная модель температуры также будет увеличиваться с течением времени, чтобы отразить это увеличение. Это означает, что значение температуры, которое будет считаться типичным, а не выбросом в 1950 году, скорее всего, будет считаться выбросом, если такая же температура произойдет в 2020 году. Другими словами, типичная температура 1950 года будет считаться очень низкой по стандартам 2020 года.
Вы можете выбрать обнаружение выбросов временных рядов в каждом местоположении с помощью параметра Идентифицировать выбросы. Если указано, то для каждого местоположения выполняется обобщенный тест экстремальных отклонений (ESD) для проверки выбросов временных рядов. Уровень достоверности теста можно задать с помощью параметра Уровень достоверности, и по умолчанию используется 90 — процентная достоверность. Обобщенный тест ESD итеративно проверяет наличие одного выброса, двух выбросов, трех выбросов и т. д. в каждом местоположении вплоть до значения параметра Максимального числа выбросов (по умолчанию 5 процентов от числа временных шагов, округленных вниз), и возвращается наибольшее статистически значимое число выбросов. Количество выбросов в каждом местоположении можно увидеть в таблице атрибутов выходных объектов, а отдельные выбросы – во всплывающих диаграммах временных рядов, которые рассматриваются в следующем разделе.