Метод скользящей средней в Microsoft Excel. Скользящее среднее и экспоненциальное сглаживание в MS Excel

24.09.2019

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

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

Способ 1: Пакет анализа

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

После этого действия пакет «Анализ данных» активирован, и соответствующая кнопка появилась на ленте во вкладке «Данные» .

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

Переходим во вкладку «Данные» и жмем на кнопку «Анализ данных» , которая размещена на ленте инструментов в блоке «Анализ» .

Открывается перечень инструментов, которые доступны в Пакете анализа . Выбираем из них наименование «Скользящее среднее» и жмем на кнопку «OK» .

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

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

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

Также следует установить галочку около параметра «Стандартные погрешности» .

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

После того, как все настройки внесены, жмем на кнопку «OK» .

Программа выводит результат обработки.

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

В поле «Интервал» ставим цифру «2» .

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

Остальные настройки оставляем прежними. После этого жмем на кнопку «OK» .

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

Способ 2: использование функции СРЗНАЧ

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

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

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

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

Активируется окно Мастера функций . В категории «Статистические» ищем значение «СРЗНАЧ» , выделяем его и щелкаем по кнопке «OK» .

Запускается окно аргументов оператора СРЗНАЧ . Синтаксис у него следующий:
СРЗНАЧ(число1;число2;…)

Обязательным является только один аргумент.

В нашем случае, в поле «Число1» мы должны указать ссылку на диапазон, где указан доход за два предыдущих периода (январь и февраль). Устанавливаем курсор в поле и выделяем соответствующие ячейки на листе в столбце «Доход» . После этого жмем на кнопку «OK» .

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

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

Теперь выделяем ячейку в следующем пустом столбце в строке за апрель. Вызываем окно аргументов функции СРЗНАЧ тем же способом, который был описан ранее. В поле «Число1» вписываем координаты ячеек в столбце «Доход» с января по март. Затем жмем на кнопку «OK» .

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

Итак, значения мы подсчитали. Теперь, как и в предыдущий раз, нам нужно будет выяснить, какой вид анализа более качественный: со сглаживанием в 2 или в 3 месяца. Для этого следует рассчитать среднее квадратичное отклонение и некоторые другие показатели. Для начала рассчитаем абсолютное отклонение, воспользовавшись стандартной функцией Excel ABS , которая вместо положительных или отрицательных чисел возвращает их модуль. Данное значение будет равно разности между реальным показателем выручки за выбранный месяц и прогнозируемым. Устанавливаем курсор в следующий пустой столбец в строку за май. Вызываем Мастер функций .

В категории «Математические» выделяем наименование функции «ABS» . Жмем на кнопку «OK» .

Запускается окно аргументов функции ABS . В единственном поле «Число» указываем разность между содержимым ячеек в столбцах «Доход» и «2 месяца» за май. Затем жмем на кнопку «OK» .

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

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

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

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

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

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

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

После этого высчитываем средние значения для обеих колонок с относительным отклонением, как и ранее используя для этого функцию СРЗНАЧ . Так как для расчета в качестве аргументов функции мы берем процентные величины, то дополнительную конвертацию производить не нужно. Оператор на выходе выдает результат уже в процентном формате.

Теперь мы подошли к расчету среднего квадратичного отклонения. Этот показатель позволит нам непосредственно сравнить качество расчета при использовании сглаживания за два и за три месяца. В нашем случае среднее квадратичное отклонение будет равно корню квадратному из суммы квадратов разностей фактической выручки и скользящей средней, деленной на количество месяцев. Для того, чтобы произвести расчет в программе, нам предстоит воспользоваться целым рядом функций, в частности КОРЕНЬ , СУММКВРАЗН и СЧЁТ . Например, для расчета среднего квадратичного отклонения при использовании линии сглаживания за два месяца в мае будет в нашем случае применяться формула следующего вида:
КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))

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

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

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

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

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

Мы произвели расчет прогноза при помощи метода скользящей средней двумя способами. Как видим, данную процедуру намного проще выполнить с помощью инструментов Пакета анализа . Тем не менее некоторые пользователи не всегда доверяют автоматическому расчету и предпочитают для вычислений использовать функцию СРЗНАЧ и сопутствующие операторы для проверки наиболее достоверного варианта. Хотя, если все сделано правильно, на выходе результат расчетов должен получиться полностью одинаковым.

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

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

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

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

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

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

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

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

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

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

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

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

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

Применение надстройки «Пакет анализа»

Для примера возьмем ту же задачу.

На вкладке «Данные» находим команду «Анализ данных». В открывшемся диалоговом окне выбираем «Скользящее среднее»:

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

Установив флажок в поле «Стандартные погрешности», мы автоматически добавляем в таблицу столбец со статистической оценкой погрешности.

Точно так же находим скользящее среднее по трем месяцам. Меняется только интервал (3) и выходной диапазон.

Сравнив стандартные погрешности, убеждаемся в том, что модель двухмесячного скользящего среднего больше подходит для сглаживания и прогнозирования. Она имеет меньшие стандартные погрешности. Прогнозное значение выручки на 12 месяц – 9 430 у.е.

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

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

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

1. Рассчитаем коэффициенты сезонности для прогноза по методу скользящей средней.

Для этого рассчитываем коэффициенты сезонности очищенные от роста , как описано в статье «Как рассчитать коэффициенты сезонности, очищенные от роста?» . Затем определяем коэффициенты сезонности к предыдущим периодам , к 1 месяцу, к 2-м месяца, к 3-м месяцам и т.д. в зависимости от того, за какой период берем среднее значение для прогнозирования продаж. Например, рассчитаем месячные коэффициенты сезонности (см. вложенный файл лист "Расчет коэффициентов")

к 1 месяцу:

коэффициент января - отношение январского коэффициента сезонности очищенного от роста к декабрьскому;

февраля - февральского коэффициента к январскому;

марта - март к февралю;

к 2-м месяцам:

для января - отношение январского коэффициента сезонности к среднему значению декабря и ноября

для февраля - февраль делим на среднее значение коэффициентов января и декабря

для марта - март к среднему февральского и январского коэффициентов

к 3-м месяцам:

для определения январского коэффициента сезонности к 3-м месяцам мы январский коэффициент сезонности, очищенный от роста, делим на среднее значение коэффициентов сезонности, очищенных от роста, за декабрь, ноябрь, октябрь;

для февраля - коэффициент февраля делим на среднее значение коэффициентов ноября, декабря и января;

Для марта - отношение марта к среднему значению коэффициентов сезонности очищенных от роста декабря, января и февраля;

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

2. Выбираем период расчета среднего значения для прогноза по методу скользящей средней.

Для этого делаем прогноз для последнего и предпоследнего периодов, данные за который нам известны, тремя или более способами для определения подходящего периода расчета средней (см. вложенный файл лист «Выбор периода»). И смотрим, какой из вариантов делает более точный прогноз:

Рассчитаем прогноз продаж по методу скользящей средней к 1-му месяцу :

Декабрь = объём продаж ноября умножим на декабрьский коэффициент сезонности к предыдущему месяцу.

Рассчитаем прогноз продаж по методу скользящей средней к 2-ум месяцам:

Декабрь = средний объём продаж за октябрь и ноябрь умножим на декабрьский коэффициент сезонности к 2-м месяцам.

Рассчитываем прогноз по методу скользящей средней к 3-ем месяцам:

Декабрь = средний объём продаж за сентябрь, октябрь и ноябрь умножим на декабрьский коэффициент сезонности к 3-м месяцам.

Сейчас мы рассчитали прогноз тремя способами на декабрь. Аналогичным образом рассчитаем на ноябрь.

Теперь сравниваем фактические значения за ноябрь и декабрь с прогнозными рассчитанными 3-мя способами . Мы видим, что в нашем примере наиболее точно прогноз рассчитан по методу скользящей средней к 2-м месяцам , возьмём его за базу. В вашем случае более точный прогноз может оказаться к предыдущему периоду, к 3-м предыдущим или к 4-м предыдущим периодам.

3. Рассчитаем прогноз продаж по методу скользящей средней.

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

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

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

4. Дополнительные факторы, которые стоит учесть при расчете прогноза продаж.

Для повышения точности прогноза важно:

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

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

Программа Forecast4AC PRO рассчитает прогноз по методу скользящей средней одновременно более чем для 1000 временных рядов одним нажатием клавиши, значительно сэкономив ваше время, одним из 4-х способов:

К среднему за два предыдущих периода

К среднему за три предыдущих периода

К среднему за 4 предыдущих периода

Двойная средняя к 3 и 4 предыдущим периодам

Присоединяйтесь к нам!

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

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

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

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

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

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

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

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

При сглаживании временного ряда скользящими средними в расчетах участвуют все уровни ряда. Чем шире интервал сглаживания, тем более плавным получается тренд. Сглаженный ряд короче первоначального на (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% точность прогноза высокая.

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