Скользящие средние в excel (примеры) — как рассчитать?

Среднее значение по условию

Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().

Найти среднее арифметическое чисел, которые больше или равны 10.

Результат использования функции СРЗНАЧЕСЛИ по условию «>=10»:

Третий аргумент – «Диапазон усреднения» — опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.

Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку

Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».

Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово «столы»). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.

В результате вычисления функции получаем следующее значение:

Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно. «Период»«Сдвиг»

«Период»«Сдвиг»

Модели «скользящих средних» в корректировках прогнозных цен

Прогнозы цен нужны как в плане продаж, так и в плане закупок

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

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

О практике применения моделей, подробно – по шагам, с практическими примерами, рассчитанными в Budget-Plan Express, смотрите в разделе «Корректировки рядов цен с применением скользящих средних». А в этом разделе вы узнаете, как можно самостоятельно, используя модели средних скользящих, корректировать ряды в Excel, о различных модификациях моделей и какие формулы используются в каждом конкретном случае.

В Budget-Plan Express используются три наиболее распространенные модели скользящих средних и их модификации: простое скользящее среднее – SMA (simple moving average), взвешенное скользящее среднее – WMA (weighted moving average WMA) и экспоненциально взвешенное скользящее среднее, экспоненциальное скользящее среднее – EMA (exponentially weighted moving average — EWMA, exponential moving average).

Для модификации ряда могут быть выбраны любые из 3-х моделей (SMA, WMA и EMA), в зависимости от типа расчетов и данных. Например, при расчете модели WMA в качестве весов может быть выбран номер очередности элемента ряда или показатель смежного ряда (например, объем продаж).

1. Простое скользящее среднее (SMA) вычисляется по формуле:

SMA_t = ( P_t + P_t-1 + P_t-2 +… P_t-n+1 ) / n

где
SMA_t – значение скользящего среднего в точке t;
n – количество значений ряда, или – сглаживающий интервал.

2. Взвешенное скользящее среднее (WMA) вычисляется по формуле:

WMA_t = ( W_n*P_t + W(n-1)*P_t-1 + W(n-2)*P_t-2 +… W(n-i+1)*P_t-n+1 ) /

где
WMA_t – значение скользящего среднего в точке t;
n – количество значений исходного ряда;
W_i – вес взвешенного компонента.
По сути WMA является модификацией модели SMA с добавлением компоненты веса.

3. Экспоненциальное скользящее среднее (EMA) вычисляется по формуле:

EMA_t = a * Pt + (1 – a) * EMA_t-1

где
EMA_t – значение скользящего среднего в точке t;
EMA_t-1 – значение скользящего среднего в точке t-1;
a – константа сглаживания EMA (smoothing constant), коэффициент изменяющий степень сглаживания, иногда его называют коэффициент определяющий скорость уменьшения весов, он принимает значение от 0 и до 1 (а ≠ 0).

При сглаживании рядов и прогнозировании, применяются эти же формулы, с той разницей, что в первом случае расчетный период для SMAt является средним периодом, во втором он – последний, т. е. в случае прогнозирования, расчет основан на предшествующих периодах.

Метод экспоненциального сглаживания

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

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

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

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

Алгоритм расчета экспоненциально сглаженных значений в любой точке ряда 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 веса. В случае некоторых собранных статистических данных более актуальным значениям присваивается больший вес. Поэтому экспоненциальное сглаживание применятся для того, чтобы придать самым актуальным данным большего веса. Таким образом решается данная статистическая проблема.

Скользящие средние и краткосрочные прогнозы в рамках адаптивной модели

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

Экстраполяция тенденции как метод прогнозирования – основа большинства методов прогнозирования, в том числе – в адаптивных моделях на основе скользящих средних с коротким прогнозным интервалом. В Budget-Plan Express не более 3-х прогнозных периодов. По умолчанию установлен один период.

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

1. Определение интервала сглаживания

Определение интервала сглаживания (числа, входящих в него уровней) зависит от задачи:

1. если необходимо сгладить беспорядочные колебания, то интервал сглаживания берут, как правило, большим (максимальное число уровней «6»);
2. если же есть необходимость сохранить периодически повторяющиеся колебания, то интервал сглаживания уменьшают до 3-х уровней.

По умолчанию установлен интервал равный «3».

2. Алгоритм вычисления при сглаживании

Сглаживание методом скользящей средней заключается в том, что вычисляется средний уровень из 3, 4 и т.д. интервалов. В результате, расчет средней как бы «скользит» от начала ряда динамики к его концу. В моделях SMA и WMA, их модификациях, степень сглаживания определяется шагом – чем больше шаг, тем выше степень сглаживания. В EMA:
– При нечетном шаге каждая вычисленная скользящая средняя соответствует реальному интервалу (моменту) времени, находящемуся в середине шага (интервала), а число сглаженных уровней, меньше первоначального числа уровней на величину шага скользящей средней, уменьшенного на единицу.
– При четном шаге, две средние скользящие центрируются. Операция центрирования заключается в повторном скольжении с шагом, равным двум. Число уровней сглаженного ряда будет меньше на величину шага скользящей средней.
Пример. Пусть интервал равен «4». Расчет для элемента «3» будет следующим:

№	Ряд	Формула	Предварительный расчет	Окончательный расчет
1	20,00
2	21,00
3	19,00	SMAt = (Pt-2+Pt-1+Pt+ Pt+1) / n	(20+21+19+24)/4=21	(21+22,5)/2=21,75
4	24,00	SMAt+1=(Pt-1+Pt+Pt+1+ Pt+2) / n	(21+19+24+26)/4=22,5	26,00
5	20,00

Тогда, более удобная формула для реализации алгоритма:

SMA_t = ½ *(P_t-2+2*(P_t-1+P_t+P_t+1) + P_t+2) / 4

Где:
SMAt – значение простого скользящего среднего в точке t;
n = 4 – сглаживающий интервал.

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

Рассмотрим ВПР в подробностях.

По ходу статьи мы:

1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;
2. Рассчитаем скользящую среднюю к 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% и выше.

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

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

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

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

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

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

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

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

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

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

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

Виды моделей

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

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

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

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

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

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

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

Пример:

Диаграмма

Для отображения рядов MS EXCEL создал диаграмму типа график. Сглаженный ряд на диаграмме называется «Прогноз» (ряд красного цвета), хотя он, по большому счету, прогнозом не является. Первые 2 значения сглаженного ряда, которые равны ошибке #Н/Д, отражаются как 0, и могут ввести в заблуждение (особенно, если последующие значения ряда близки к 0). Поэтому их лучше удалить в столбце D. Примечание : Значения #Н/Д, которые вернула надстройка в ячейках D7 и D8, являются просто текстовыми значениями, что принципиально отличается от результата возвращаемого формулами, например, функцией НД() или ВПР() , когда данные не найдены. Если Формат ячейки указан как Общий, то их можно различить визуально: текстовое значение будет выравнено по левому краю, а значение ошибки выравнивается по центру. Кроме того, другие инструменты MS EXCEL не воспринимают #Н/Д, которое вернула надстройка, как ошибку. Например, Условное форматирование не выделит ячейку с текстовым #Н/Д как ячейку содержащую ошибку. Примечание : При построении диаграммы текстовые значения всегда отображаются как 0. Но, если ошибка #Н/Д является результатом формулы, то воспринимается диаграммой как пустая ячейка и на ней не отображается. Это показано в следующем разделе «Скользящее среднее с настраиваемым количеством периодов усреднения». Диаграмма позволяет визуально определить «выбросы», т.е. значения исходного ряда, которые существенно отличаются от средних значений. Такие «выбросы» могут быть следствием ошибки, но они оказывают существенное влияние на вид сглаженного ряда.

Простые скользящие средние против взвешенных скользящих средних

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

Например, если вы используете три периода времени для расчета скользящего среднего, то вес, присвоенный каждому периоду времени, будет равен 0,333. Или, если вы используете четыре периода времени для расчета скользящей средней, тогда вес, присвоенный каждому периоду, будет равен 0,25.

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

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

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

Дополнительные ресурсы

В следующих руководствах объясняется, как рассчитать другие распространенные показатели в Excel:

Как рассчитать экспоненциальную скользящую среднюю в ExcelКак рассчитать кумулятивное среднее в Excel

Использование скользящих средних в Excel

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

Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике.

Например, нужно спрогнозировать продажи на ноябрь. Исследователь выбирает количество предыдущих месяцев для анализа (оптимальное число m членов скользящего среднего). Прогнозом на ноябрь будет среднее значение параметров за m предыдущих месяца.

Задача.
Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.

Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.

Относительные отклонения:

Средние квадратичные отклонения:

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

После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).

Прогнозное значение выручки на 12 месяц – 9 430 у.е.

Инструменты сглаживания программы MS EXCEL

В программе EXCEL имеется всего два инструмента анализа, используемые для сглаживания временного ряда. Элементы диалогового окна «Скользящее среднее» представлены на рис. 3.1.

Рис. 3.1. Инструмент анализа «Скользящее среднее»

Необходимо ввести следующие аргументы:

• «Входной интервал» — анализируемый ряд (должен состоять из одного столбца или одной строки).
• «Интервал» — «размер окна» (по умолчанию используется 3).
• «Метки в первой строке» — необходимо установить флажок, если первая строка (или столбец) входного интервала содержит заголовок.
• «Выходной диапазон» — должен находиться на одном листе с исходными данными. По этой причине параметры «Новый лист» и «Новая книга» недоступны. Необходимо ввести ссылку на левую верхнюю ячейку выходного диапазона.
• «Стандартные погрешности» — если установлен флажок, то выходной диапазон состоит из двух столбцов, и значения стандартных погрешностей содержатся в правом столбце.
• «Вывод графика» — если установлен флажок, то создаётся встроенная диаграмма на листе, содержащем выходной диапазон.

Элементы диалогового окна «Экспоненциальное сглаживание» представлены на рис. 3.2.

Как сделать схему для вязания в excel?

Здесь имеется ранее не представленный аргумент «Фактор затухания», представляющий собой константу экспоненциального сглаживания — корректировочный фактор, минимизирующий нестабильность данных генеральной совокупности. По умолчанию значение аргумента «Фактор затухания» равно 0,3. Наиболее подходящим интервалом значений этого параметра сглаживания считается промежуток от 0,2 до 0,3.

Обнаружение и анализ тренда

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

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

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

Показатели динамики

Наличие или отсутствие тренда обычно хорошо видно по графику временного ряда (см., например, рис. 8.1) или по специальным аналитическим «показателям динамики ВР».

Показатели динамики разделяются на следующие важнейшие виды:

«абсолютный прирост» равен разности Δ двух сравниваемых уров­ней и характеризует изменение показателя за определенный про­межуток текущей переменной.

1. «темп роста» Т (всегда положителен) характеризует отношение двух сравниваемых уровней ряда, как правило, выраженное в процентах.
2. «темп прироста» K.
3. Причем каждый из указанных видов показателей может быть трех типов:
4. «цепной» — если сравнение осуществляется при переменной базе, и каж­дый последующий уровень сравнивается с предыдущим
5. «базисный» — если сравнение осуществляется с одним и тем же уровнем, принятым за базу сравнения;
6. «средний».

Например, «средний абсолютный прирост» — это обобщающая характеристика скорости изменения исследуемого показателя во времени (ско­ростью будем называть прирост в единицу времени). Для его оп­ределения за весь период наблюдения используется формула про­стой средней арифметической «цепного абсолютного прироста».

«Средний темп роста» — обобщающая характеристика, отражающая интенсивность изменения уровней ряда. Он показывает, сколько в среднем процентов последующий уровень со­ставляет от предыдущего на всем периоде наблюдения. Этот показатель рассчитывается по формуле средней геометрической 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. Фактические объёмы реализации продукции

Шаг 4

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

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

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