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

Инструмент «Скользящее среднее» можно вызвать в диалоговом окне команды «Анализ данных» из меню «Сервис».

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

Таблица 3 .1 ― Оценка тенденции поведения показателей исследуемого динамического ряда методом скользящего среднего

Примечание – Источник: .

На основании данных таблицы строю график скользящей средней.

Рисунок 3.1 – Скользящее среднее

Примечание – Источник: .

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

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

    1. Составление линейных прогнозов средствами Excel

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

      1. Использование функции линейн для создания модели тренда

Функция рабочего листа ЛИНЕЙН помогает определить характер линейной связи между результатами наблюдений и временем их фиксации и дать ей математическое описание, наилучшим образом аппроксимирующее исходные данные. Для построения модели она использует уравнение вида y=mx+b, гдеy– исследуемый показатель;x=t– временной тренд;b,m– параметры уравнения, характеризующие соответственноy-пересечение и наклон линии тренда. Расчет параметров модели ЛИНЕЙН производят на основе метода наименьших квадратов.

Вызвать функцию ЛИНЕЙН можно в диалоговом окне «Мастер функций» (категория «Статистические»), расположенном на панели инструментов «Стандартные».

Таблица 3.2 ― Расчет и оценка линейной модели тренда с помощью функции ЛИНЕЙН

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

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

Чаще всего в практике маркетинговых исследований прогнозируются следующие величины:

  • Объемы продаж
  • Размер и емкость рынка
  • Объемы производства
  • Объемы импорта
  • Динамика цен
  • И проч.

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

1. Сбор вторичной информации по проблеме (желательно как количественной, так и качественной). Так, например если Вы прогнозируете размер своего рынка, нужно собрать статистическую информацию по рынку (объемы производства, импорта, динамику цен, объемы продаж и проч.) так и тенденции, проблемы или возможности рынка. Если вы прогнозируете объем продаж, тогда вам нужны данные о продажах за период. Для прогнозирования, чем больше исторических данных вы рассмотрите, тем лучше. Желательно прогнозирование дополнить анализом влияющих на прогнозируемое явление факторов (можно SWOT, PEST анализ или любой другой). Это позволит понимать логику развития, и вы сможете таким образом проверять правдоподобность той или иной модели тренда.

2. Далее желательно проверить количественные данные . Для этого нужно сравнить значения одних и тех же показателей, но полученных из разных источников. Если все сходиться можно «загонять» данные в Excel. Также данные должны соответствовать следующим требованиям:

  • Базовая линия включает в себя результаты наблюдений - начиная с самых ранних и заканчивая последними.
  • Все временные периоды базовой линии имеют одинаковую продолжительность. Не следует смешивать данные, например, за один день со средними трехдневными показателями.
  • Наблюдения фиксируются в один и тот же момент каждого временного периода. Например трафик замеряться должен в одно и то же время.
  • Пропуск данных не допускается. Пропуск даже одного результата наблюдений нежелателен при прогнозировании» поэтому, если в ваших наблюдениях отсутствуют результаты за незначительный отрезок времени, постарайтесь восполнить их хотя бы приблизительными данными.

3. Проверив данные, можно применять различные методики прогнозирования . Начать я бы хотел с самого простого метода – МЕТОДА СКОЛЬЗЯЩЕГО СРЕДНЕГО

МЕТОД СКОЛЬЗЯЩЕГО СРЕДНЕГО

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

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

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

Итак, как это делать в Excel

1. Допустим, что у Вас есть объемы месячных продаж за последние 29 месяцев. И вы хотите определить, какой объем продаж будет в 30 месяце. Но, если честно, вовсе не обязательно при расчете прогнозных значений оперировать 30 историческими значениями, ведь этот метод будет использовать для расчета среднего лишь несколько последних месяцев. Поэтому для расчета достаточно лишь несколько прошлых месяцев.

2. Приводим эту таблицу в вид понятный Excel, т.е. чтобы все значения были в одном ряду.

3. Далее вводим формулу расчета среднего по предыдущим трем (четырем, пяти? как сами выберите) значениям (см. в ). Наиболее удобно все-таки использовать для расчета последние 3 значения, т.к. если учитывать больше, данные будут чересчур усредняться, если меньше – не будут точными.

4. Используя функцию автозаполнения для всех последующих значений вплоть до 30, прогнозного месяца. Таким образом, функция рассчитает прогноз на июнь 2010 г. Согласно прогнозным значениям в июне продажи составят около 408 единиц товара. Но обратите внимание, что если тенденция падения постоянна, как в нашем примере, расчет прогноза по средней будет немного завышенным, или будет как бы «отставать» от реальных значений.

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

Скользящее среднее или просто МА (Moving Average) , является среднеарифметическим ценового ряда. Общая формула скользящего среднего следующая:

Где:
МА – скользящее среднее;
n- период усреднения;
Х – значения цены акции.

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


Спрогнозируем с помощью модели скользящего среднего стоимость акций компании Аэрофлот (AFLT) . Для этого экспортируем котировки акции с сайта finam.ru за половину 2009 года. Всего будет 20 значений.

График стоимости акций Аэрофлота за выбранный промежуток времени представлен ниже.



Выбор периода усреднения n в модели скользящего среднего
Использование большего в модели МА(n) приводит к сильному искажению данных, в результате чего существенные значения ценового ряда усредняются, и в результате теряется четкость прогноза, можно сказать что он становится “размытым”. Использование слишком мелкого периода усреднения добавляет в прогноз больше шумовой компоненты. Как правило, период усреднения подбирается эмпирическим путем на исторических данных.

Построим скользящее среднее с периодом усреднения в три месяца MA(3). Для расчета значения скользящего среднего для акции, воспользуемся формулой Excel.

СРЗНАЧ(C2:C4)

В колонке “D” рассчитаны значения скользящего среднего с периодом усреднения 3.

После расчета скользящего среднего построим прогноз на 3 периода вперед (на три месяца вперед). Воспользуемся формулой для определения значения цены акции, первое прогнозное значение будет равно последнему значению скользящего среднего. Оранжевая область это область прогнозов. С22 будет равна значению скользящего среднего, то есть:

С22 =D21 С23=D22 и т.д.

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

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

Выберите в меню Сервис пункт Анализ данных , появится окно с одноименным названием, главным элементом которого является область Инструменты анализа . В данной области представлен список реализованных в Microsoft Excel методов статистической обработки данных. Каждый из перечисленных методов реализован в виде отдельного режима работы, для активизации которого необходимо выделить соответствующий метод указателем мыши и щелкнуть по кнопке ОК. После появления диалогового окна вызванного режима можно приступать к работе.

Режим работы «Скользящее среднее » служит для сглаживания уровней эмпирического динамического ряда на основе метода простой скользящей средней.

Режим работы «Экспоненциальное сглаживание » служит для сглаживания уровней эмпирического динамического ряда на основе метода простого экспоненциального сглаживания.

В диалоговых окнах данных режимов (рисунок 2 и 3) задаются следующие параметры:

2. Флажок Метки – устанавливается активное состояние, если первая строка (столбец) во входном диапазоне содержит заголовки. Если заголовки отсутствуют, флажок следует деактивизировать. В этом случае будут автоматически созданы стандартные названия для данных выходного диапазона.

3. Интервал (только в диалоговом окне Скользящее среднее) – вводится размер окна сглаживания р . По умолчанию р=3 .

Рисунок 2 – Диалоговое окно скользящего среднего

4. Фактор затухания (только в диалоговом окне Экспоненциальное сглаживание) – вводится значение коэффициента экспоненциального сглаживания p . По умолчанию, p=0,3 .

5. Выходной интервал / Новый рабочий лист / Новая рабочая книга – в положении Выходной интервал активизируется поле, в которое необходимо ввести ссылку на левую верхнюю ячейку выходного диапазона. Размер выходного диапазона будет определен автоматически, и на экране появится сообщение в случае возможного наложения выходного диапазона на исходные данные. В положении Новый рабочий лист открывается новый лист, в который начиная с ячейки А1 вставляются результаты анализа. Если необходимо задать имя в поле, расположенное напротив соответствующего положения переключателя. В положении Новая рабочая книга открывается новая книга, на первом листе которой начиная с ячейки А1 вставляются результаты анализа.



6. Вывод графика – устанавливается в активное состояние для автоматической генерации на рабочем листе графиков фактических и теоретических уровней динамического ряда.

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

Рисунок 3 – Диалоговое окно экспоненциального сглаживания

Пример 1.

Данные о реализации (млн. руб.) продуктов сельскохозяйственного производства магазинами потребительской кооперации города приведены в таблице, сформированной на рабочем листе Microsoft Excel (рисунок 4). В указанном периоде (2009 – 2012 гг.) требуется выявить основную тенденцию развития данного экономического процесса.

Рисунок 4 – Исходные данные

Для решения задачи используем режим работы «Скользящее среднее ». Значения параметров, установленных в одноименном диалоговом окне, представлены на рисунке 5, рассчитанные в данном режиме показатели – на рисунке 6, а построенные графики – на рисунке 7.

Рисунок 5 – Заполнение диалогового окна

Рисунок 6 – Результаты анализа

Рисунок 7– Скользящее среднее

В столбце D (рисунок 5) вычисляются значения сглаженных уровней. Например, значение первого сглаженного уровня рассчитывается в ячейке D5 по формуле =СРЗНАЧ(С2:С5), значение второго сглаженного уровня – в ячейке D6 по формуле =СРЗНАЧ(С5:С8) и т.д.

В столбце E вычисляются значения стандартных погрешностей с помощью формулы =КОРЕНЬ (СУММАКВРАЗН (блок фактических значений; блок прогнозных значений) / размер окна сглаживания).

Например, значение в ячейке Е10 рассчитывается по формуле =КОРЕНЬ(СУММКВРАЗН(С7:С10;О7:В10)/4).

Вместе с тем, как отмечалось выше, если размер окна сглаживания является четным числом (р=2m ), то рассчитанное усредненное значение нельзя сопоставить какому-либо определенному моменту времени t, поэтому необходимо применять процедуру центрирования.

Для рассматриваемого примера р=4 , поэтому процедура центрирования необходима. Так, первый сглаженный уровень (265,25) записывается между II и III кв. 2009 г. и т.д. Применяя процедуру центрирования (для этого используем функцию СРЗНАЧ), получаем сглаженные уровни с центрированием. Для III кВ. 2009 г. определяется серединное значение между первым и вторым сглаженными уровнями: (265,25 + 283,25)/2 = 274,25; для IV кв. 2009 г. центрируются второй и третий сглаженные уровни: (283,25 + 292,00)/2 = 287,6 и т.д. Рассчитанные значения представлены в таблице 1. Скорректированный график скользящей средней представлен на рисунке 8.

Таблица 1 – Динамика сглаженных уровней реализации продукции

Год Квартал Размер реализации, млн. руб. Сглаженные уровни с центрированием
274,25
287,63
297,00
307,50
334,63
374,13
402,88
421,00
429,00
430,75
435,38
446,63

Рисунок 8 – Скорректированный график скользящего среднего

Пример 2.

Рассмотренная задача может быть решена и с помощью метода простого экспоненциального сглаживания. Для этого необходимо использовать режим работы «Экспоненциальное сглаживание». Значения параметров, установленных в одноименном диалоговом окне, представлены на рисунке 9, рассчитанные в данном режиме показатели – рисунок 10, а построенные графики – на рисунке 11.

Рисунок 9 – Заполнение диалогового окна «Экспоненциальное сглаживание»

Рисунок 10 – Результаты анализа

Рисунок 11 – Экспоненциальное сглаживание

В столбце D (рисунок 10) вычисляются значения сглаженных уровней на основе рекуррентных соотношений.

В столбце E рассчитываются значения стандартных погрешностей с помощью формулы =КОРЕНЬ(СУММКВРАЗН (блок фактических значений; блок прогнозных значений) / 3). Как легко заметить (сравните рисунок 8 и 11), при использовании метода простого экспоненциального сглаживания, в отличие от метода простой скользящей средней, сохраняются мелкие волны.

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

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

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

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

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

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

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

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

Пример применения метода скользящей средней для разработки прогноза

Задача . Имеются данные, характеризующие уровень безработицы в регионе, %

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

Решение методом скользящей средней

Для расчета прогнозного значения методом скользящей средней необходимо:

1. Определить величину интервала сглаживания, например равную 3 (n = 3).

2. Рассчитать скользящую среднюю для первых трех периодов
m фев = (Уянв + Уфев + У март)/ 3 = (2,99+2,66+2,63)/3 = 2,76
Полученное значение заносим в таблицу в средину взятого периода.
Далее рассчитываем m для следующих трех периодов февраль, март, апрель.
m март = (Уфев + Умарт + Уапр)/ 3 = (2,66+2,63+2,56)/3 = 2,62
Далее по аналогии рассчитываем m для каждых трех рядом стоящих периодов и результаты заносим в таблицу.

3. Рассчитав скользящую среднюю для всех периодов, строим прогноз на ноябрь по формуле:

где t + 1 – прогнозный период; t – период, предшествующий прогнозному периоду (год, месяц и т.д.); Уt+1 – прогнозируемый показатель; mt-1 – скользящая средняя за два периода до прогнозного; n – число уровней, входящих в интервал сглаживания; Уt – фактическое значение исследуемого явления за предшествующий период; Уt-1 – фактическое значение исследуемого явления за два периода, предшествующих прогнозному.

У ноябрь = 1,57 + 1/3 (1,42 – 1,56) = 1,57 – 0,05 = 1,52
Определяем скользящую среднюю m для октября.
m = (1,56+1,42+1,52) /3 = 1,5
Строим прогноз на декабрь.
У декабрь = 1,5 + 1/3 (1,52 – 1,42) = 1,53
Определяем скользящую среднюю m для ноября.
m = (1,42+1,52+1,53) /3 = 1,49
Строим прогноз на январь.
У январь = 1,49 + 1/3 (1,53 – 1,52) = 1,49
Заносим полученный результат в таблицу.

Рассчитываем среднюю относительную ошибку по формуле:

ε = 9,01/8 = 1,13% точность прогноза высокая.

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