Прогнозирование в Excel методом скользящего среднего

Прогнозирование в Excel

методом скользящего среднего

Теоретическая справка. При моделировании различных экономических процессов на практике широко используются возрастающие возможности со- временных компьютерных технологий, а также эффективные способы прогнози- рования. Так, для разработки прогнозов в пакете Exсelможно воспользоваться такими инструментами [1–4], как:

• построениерегрессий;
• экспоненциальное сглаживание;
• скользящее среднее.

В данной работе процесс разработки прогноза средствами Excel осуществля- ется с помощью метода скользящего среднего. Заметим, что методика прогнози- рования с помощью регрессий достаточно подробно описана авторами в [2].

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

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

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

• прибыли предприятия по месяцам в качестве прогноза выбрать скользящее среднее за три месяца ( m=3), то прогнозом на июнь будет среднее значениепо-

казателей за три предыдущих месяца (март, апрель, май). Если же выбрать 4-х месячное скользящее среднее ( m= 4 ), то прогнозом на июнь будет среднее зна- чение показателей за четыре предыдущих месяца (февраль, март, апрель, май).

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

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

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

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

Задача. Учитывая представленные в таблице данные ежемесячной прибыли фирмы за 11 месяцев текущего года, составить прогноз о прибыли фирмы на 12-й месяц.[pic 1]

Рис.1. Таблица значений прибыли фирмы по месяцам

Решение задачи

В дальнейшем при решении сформулированной задачи для удобства пред- ставления полученных результатов расчетов будут использоваться рабочие лис- ты Z1, Z2, Z3, Z4:

• лист Z1 – для формирования сглаженных временных рядов на основе метода скользящего среднего с помощью функции СРЗНАЧ( ) и вычисления их сред- них отклонений от исходного временного ряда;
• лист Z2 – для реализации процесса сглаживания исходного временного ряда с помощью инструмента Скользящее среднее надстройки Пакетанализа;
• лист Z3 – для визуального представления сглаженного временного ряда, по- строенного с помощью линии тренда типа Линейная фильтрация на основе диаграммы для исходного временногоряда;
• лист Z4 – для сравнительного анализа результатов, полученных с помощью выбранных выше инструментов: на основе исходного временного ряда стро- ятся сглаженные временные ряды значений 2-х месячного скользящего сред- него с помощью функции СРЗНАЧ( ), инструмента Скользящее среднее над- стройки"Пакетанализа"илиниитрендатипаЛинейнаяфильтрация.

Применение встроенной функции СРЗНАЧ( )

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