Коэффициент сезонности: формула Excel

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

Результат

Пиковый месяц: декабрь
Коэффициент пика: 1,35
Самый слабый месяц: январь
Коэффициент спада: 0,61
Январь, коэффициент: 0,61
Февраль, коэффициент: 0,68
Март, коэффициент: 0,92
Апрель, коэффициент: 1,15
Май, коэффициент: 1,32
Июнь, коэффициент: 1,24
Июль, коэффициент: 1,08
Август, коэффициент: 1,01
Сентябрь, коэффициент: 0,98
Октябрь, коэффициент: 0,87
Ноябрь, коэффициент: 0,79
Декабрь, коэффициент: 1,35

За год продажи — 12 000 000 ₽, средний месяц — 1 000 000 ₽. Выше всего декабрь: коэффициент 1,35; ниже всего январь: 0,61.

Пик к спаду — 2,21: запасы и рекламный бюджет стоит распределять так же.

План на год 13 000 000 ₽ по месяцам: январь — 660 833,33 ₽, апрель — 1 245 833,33 ₽, июль — 1 170 000 ₽, октябрь — 942 500 ₽, декабрь — 1 462 500 ₽.

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

Что показывает коэффициент сезонности

Коэффициент сезонности — это отношение продаж месяца к среднему месяцу года. Единица значит, что месяц идёт как обычно, 1,2 — что продаж на пятую часть больше среднего, 0,8 — на пятую часть меньше.

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

Формула коэффициента сезонности для Excel

Разложите продажи по месяцам в столбец, например B2:B13. Средний месяц посчитает формула =СРЗНАЧ(B2:B13). Коэффициент месяца — =B2/СРЗНАЧ($B$2:$B$13), и она растягивается на все двенадцать строк. Знаки доллара закрепляют диапазон, чтобы при копировании он не съезжал.

Пик и спад находят формулами =МАКС(C2:C13) и =МИН(C2:C13), а название месяца к коэффициенту подтягивает =ИНДЕКС(A2:A13;ПОИСКПОЗ(МАКС(C2:C13);C2:C13;0)). Чтобы разложить годовой план, умножьте план на коэффициент месяца и разделите на сумму коэффициентов — она равна двенадцати: =план*C2/СУММ($C$2:$C$13).

Формулы

Средний месяц: Средний месяц = Сумма продаж за год / 12
Коэффициент месяца: Коэффициент = Продажи месяца / Средний месяц
План месяца: План месяца = План на год × Коэффициент месяца / Сумма коэффициентов
Пик к спаду: Пик к спаду = Коэффициент лучшего месяца / Коэффициент худшего месяца

Сумма коэффициентов за двенадцать месяцев всегда равна 12: каждый месяц сравнивается со средним, и отклонения вверх и вниз уравновешивают друг друга.

Два примера

Магазин с декабрьским пиком

За год магазин продал на 12 000 000 ₽, средний месяц — 1 000 000 ₽. Выше всего декабрь с коэффициентом 1,35: в этот месяц товара нужно заметно больше среднего.

Слабейший месяц — январь с коэффициентом 0,61. Пик к спаду — 2,21. По этому же соотношению раскладывается план на год: декабрь получает 1 462 500 ₽, ноябрь — 855 833,33 ₽.

Посчитать с этими числами

Сезонность без годового плана

Здесь сезон другой: продажи растут с весны и держатся до августа, а осенью падают. За год — 9 300 000 ₽, средний месяц — 775 000 ₽.

Пик — июль с коэффициентом 1,45, слабейший месяц — январь с коэффициентом 0,62. Закупку и рекламу выгоднее наращивать к апрелю, а не к декабрю.

Посчитать с этими числами

Частые вопросы

Как посчитать коэффициент сезонности в Excel?

Поставьте продажи по месяцам в столбец и разделите каждый месяц на среднее по году: =B2/СРЗНАЧ($B$2:$B$13). Получится двенадцать коэффициентов, сумма которых равна 12. По ним видно, какие месяцы сильнее и слабее среднего.

За сколько лет брать данные?

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

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

Умножьте план на коэффициент месяца и разделите на сумму коэффициентов, то есть на 12.

Чем коэффициент сезонности отличается от индекса сезонности?

Смысл один: отношение месяца к среднему. Коэффициентом чаще называют число около единицы, индексом — то же число в процентах: коэффициент 1,2 и индекс 120 — одно и то же. Главное — считать все месяцы по одной базе, среднему за тот же период.

Что делать, если продажи растут весь год?

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

Как сезонность влияет на закупку и рекламу?

Закупку и рекламный бюджет распределяют по тем же коэффициентам, что и план продаж.

Сезон виден, а прибыль по месяцам — нет

Посчитать, что приносит прибыль в пик и что съедает её в спад

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

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

Читать об услуге «Юнит-экономика и финансовая модель»