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

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

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

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

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

Ниже на рисунке представлена таблица E1:F13, которая содержит номера месяцев вместе со соответствующими им коэффициентами сезонности. Данный коэффициент отображает, как сильно продажи в данным месяце отличаются от среднего общего показателя. Таблица иллюстрирует, что показатель продаж за январь является равной более-менее половине среднему значению (55%), а в апреле этот же показатель более чем двукратно больше (215%). Для вычисления коэффициента сезонности используется следующая формула:

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

Выше на рисунке показано, что если бы продажи в каждым месяце не подвергались сезонности, тогда в январе 2008г. они бы составили 127 213,66.



Формула расчета коэффициента сезонности продаж в Excel

Формула вычисляющая коэффициент сезонности использует две функции СУММПРОИЗВ вычисляющие средний показатель только для месяца указанном в ячейке с левой стороны. Средние продажи для данного месяца делятся на средний показатель общего объема продаж.

Первая функция СУММПРОИЗВ суммирует все значения в диапазоне B2:B25, для которых ячейки в диапазоне A2:A25 содержат месяц с номером равным числу в ячейке E2. Формула в ячейке F2 суммирует показатель продаж за январь каждого года.

Вторая функция СУММПРОИЗВ считывает количество повторений месяца января в диапазоне ячеек A2:A25. Функция СУММПРОИЗВ возвращает массив из логических значений ИСТИНА или ЛОЖЬ. Двойной знак вычитания позволяет преобразовать эти значения в 1 (для ИСТИНА) и 0 (для ЛОЖЬ). Функция СУММПРОИЗВ суммирует все единицы и возвращает в своем результате вычислений количество повторений месяца январь в целом диапазоне общего набора данных.

Деление итогового результата вычисления функции СУММПРОИЗВ на итоговый результат второй функции — значит деление суммы за каждый месяц январь на количество повторений этого месяца. Таким способом вычисляется средний показатель для каждого месяца. Этот же средний показатель далее делится на среднее значение общих продаж без учета месяцев, а результат в итоге возвращается как коэффициент.

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

И наконец показатель продаж в каждом месяце делится на коэффициент сезонности. Для поиска коэффициента в таблице применена функция ВПР. Искомое значение вычисляется с помощью функции МЕСЯЦ, которая возвращает номер месяца.

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

Это значение сравнивается со значениями в первом столбце таблицы.