Формула ПРЕДСКАЗ для прогноза продаж с учетом сезонности в Excel

Главной целью десезонализации данных является прогнозирование тренда объема продаж в будущем. Десезонализированные данные и функция ПРЕДСКАЗ, которая их обрабатывает, вместе поставляют необходимую информацию для прогнозирования объема реализации на целый следующий год.

Пример формул таблицы для анализа прогноза продаж в Excel

Ниже на рисунке представлены исходные данные. Допустим по этим данным необходимо составить прогноз продаж на 2020-й год, не смотря на то что собранные статистические данные заканчиваются в декабре 2019-го года. Первым шагом является использование функции ПРЕДСКАЗ и расширение десезонализированных данных на очередные 12 месяцев. Формулы в таблицах:

  1. Расчет коэффициента сезонности для каждого месяца в году:
    2. таблицы для анализа прогноза продаж.
  2. Расчет десезонализации на основе коэффициентов:
    3. Расчет десезонализации.
  3. Формула для прогнозирования показателей реализации в январе 2020-го года находится в том же столбце в ячейке C26 и выглядит следующим образом:
    4. прогнозирование показателей реализации.
  4. Формула прогноза сезонности на 2020-й год:
    5. прогноз сезонности.

Для прогнозирования будущих значений функция ПРЕДСКАЗ использует метод линейной регрессии. Функция содержит 3 аргумента:

функция ПРЕДСКАЗ.
  1. X – в данном аргументе будет указан месяц, для которого следует получить текущее прогнозируемое значение.
  2. Известные значения y – аргумент содержит десезонализированные данные столбца C.
  3. Известные значения x – здесь указаны месяца соответствующие данным по продажам в столбце A.

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



График прогноза продаж с учетом сезонности

В результате всех вычислений и полученных данных можно составить график прогноза продаж с учетом сезонности в будущем году:

График прогноза продаж.

Скачать прогноз продаж с учетом сезонности в Excel

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

Читайте начало статьи: Пример формулы для расчета десезонализации продаж в Excel

После восстановления показателей сезонности заметный прогнозируемый активный рост во втором квартале.


en ru