Как рассчитать сезонность продаж в Excel
Обновлено: 05.07.2024
Чрезвычайная изменчивость, вызванная сезонными распродажами, затрудняет отслеживание и прогнозирование основных тенденций продаж. Вот как решить эту проблему.
На приведенной ниже диаграмме Excel показана типичная пилообразная модель сезонных распродаж.
Сезонные распродажи происходят примерно одинаково каждый год, каждую неделю или и то, и другое. В определенные месяцы или дни недели продажи всегда выше среднего; а в другие определенные периоды продажи ниже среднего.
Сезонные распродажи обычно трудно анализировать. В хорошие периоды трудно понять, лучше ли хорошие продажи, чем обычно; в плохие периоды трудно понять, хуже ли плохие продажи, чем обычно.
Прогнозировать сезонные распродажи может быть так же сложно. Трудно представить себе какой-либо метод, который мог бы генерировать надежный прогноз продаж по схеме, подобной показанной здесь.
Несколько лет назад я столкнулся с этими проблемами, когда был финансовым директором небольшой компании, занимающейся садоводством и садоводством, чьи ежемесячные продажи варьировались от низкого уровня продаж в зимние месяцы (около 50 000 долларов США) до максимума весной (около 1 млн долларов США в месяц).
Чтобы справиться с аналитическими трудностями, я разработал несколько методов работы с электронными таблицами, которые действительно помогли. Наиболее полезным методом было составление отчетов, анализ и прогнозирование десезонированных продаж.
Десезонализированные продажи
Темно-серая линия на этой диаграмме Excel показывает продажи за последние четыре с половиной года, при этом влияние сезонности исключено из данных, показанных синей линией.
То есть темная линия показывает десезонализованные продажи.
Глядя на десезонализованные данные, мы легко видим, что продажи упали в 2010 году и в первой половине 2011 года, немного упали в 2012 году и в целом выросли в остальные периоды.
Чтобы спрогнозировать продажи, мы оцениваем общее направление продаж без учета сезонности, а затем анализируем эту тенденцию по сезонам, чтобы получить конкретные оценки продаж.
Давайте посмотрим, как это делается…
Как десезонировать продажи
Идею десезонных продаж легко понять. Для каждого месяца года мы рассчитываем сезонный индекс (столбец C на этом рисунке). Это значение говорит нам, в среднем, насколько ежемесячные продажи отличаются от средних продаж за год. Затем, чтобы десезонировать продажи, мы делим продажи за каждый месяц на его сезонный индекс.
Чтобы рассчитать сезонность для дней недели, мы создаем сезонный индекс для дней недели.
Самым важным шагом является расчет правильного значения среднего объема продаж. В частности, вам необходимо рассчитать «центрированное скользящее среднее» (CMA) для каждого периода в ваших исторических данных. Чтобы найти ежедневную CMA в течение недели, период усреднения составляет семь дней. Чтобы найти месячный CMA в течение года, период усреднения составляет 12 месяцев… ну, вроде того.
Найти CMA за семь дней несложно. Это связано с тем, что семь — нечетное количество дней, поэтому у него есть центральный день.
Но в году четное количество месяцев. Итак, когда мы пытаемся рассчитать CMA для двенадцати месяцев с января по декабрь, у нас возникает следующая проблема:
Это проблема, поскольку среднее значение продаж с января по декабрь не относится ни к июню, ни к июлю. (И нет, мы не можем обмануть, произвольно назначив среднее значение одному из двух месяцев.)
Чтобы найти CMA за август, мы просто копируем формулу Excel для CMA за июль в следующую ячейку. И мы делаем то же самое для каждого последующего месяца.
Что это за формулы Excel? Что ж, посмотрим…
Как рассчитать центрированные скользящие средние и коэффициенты продаж
В столбце A на этом рисунке показан порядковый номер для каждой строки данных. В столбцах B и C показаны фактические продажи вымышленной компании по месяцам. Пунктирная линия показывает, где я спрятал строки, чтобы вы могли видеть верхнюю и нижнюю часть области данных.
Для начала введите метки в строку 3, а затем отформатируйте ячейки серого цвета в строке 4. Скопируйте строку ячеек серого цвета в строку 65.
Выберите диапазон A3:E65. Затем выберите «Формулы», «Определенные имена», «Создать из выделенного». Или выберите Alt+INC или Ctrl+Shift+F3
В диалоговом окне «Создание имен» убедитесь, что отмечена только верхняя строка, затем нажмите «ОК».
Скопируйте и вставьте свои данные о продажах за несколько лет в столбец C, а соответствующие месячные даты — в столбец B. Убедитесь, что вы вставляете данные между верхней и нижней серыми границами.
Введите текст, показанный для ячейки A2, а затем введите эту формулу для показанной ячейки:
Эта формула сообщает нам фактическое количество периодов данных, даже если строк в таблице больше, чем данных для их заполнения.
Чтобы назвать эту ячейку, выберите диапазон A2:B2, снова откройте диалоговое окно «Создание имен», убедитесь, что установлен флажок только в левом столбце, затем нажмите «ОК».
Ячейка D11 содержит формулу, которая делит продажи в ячейке C11 на среднее скользящее по центру за август 2005 года. Вот формула для показанной ячейки:
Функция ЕСЛИ в этой формуле возвращает нулевую строку ("" в конце формулы), когда у нас заканчиваются данные в нижней части рисунка. Функция СРЗНАЧ в этой формуле вычисляет центрированное скользящее среднее путем вычисления среднего значения двух диапазонов из двенадцати ячеек: C5:C16 и C6:C17.
Скопируйте формулу из ячейки D11 вниз по столбцу в ячейку D64.
Теперь, когда у нас есть коэффициент продаж, нам нужно заполнить еще одну таблицу, прежде чем мы сможем рассчитать продажи без учета сезонности, показанные в столбце E.
Как рассчитать сезонный индекс
На рисунке ниже показан сезонный индекс в столбце C. Чтобы настроить этот показатель, сначала перейдите на неиспользуемый рабочий лист в своей книге. Добавьте текст, показанный в строках 1 и 2, и числа, показанные в столбце A.
Введите эту формулу массива для показанной ячейки:
Подчеркнем, что это формула массива. Вы вводите его в строку формул, как и любую другую формулу. (Не вводите фигурные скобки.) Затем, удерживая нажатыми клавиши Ctrl+Shift, нажмите Enter, чтобы ввести формулу. Когда вы это сделаете, Excel добавит начальную и конечную фигурные скобки, как показано.
Эта формула находит среднее значение всех коэффициентов продаж за январь в столбце D предыдущего рисунка… но только для коэффициентов продаж больше нуля.
Чтобы рассчитать аналогичные средние значения для других одиннадцати месяцев, скопируйте эту формулу из ячейки B3 в диапазон B4:B14.
Введите формулу для показанной ячейки:
Суммарное значение в ячейке B15 равно почти 12. Но нам нужно, чтобы значения сезонного индекса в сумме равнялись ровно 12. Поэтому мы должны масштабировать средние значения, чтобы они были такими. Поэтому введите эту формулу для показанной ячейки:
Скопируйте формулу в диапазон C4:C14.
Скопируйте формулу СУММ из ячейки B15 в ячейку C15. Когда вы это сделаете, вы увидите, что сумма столбца SeasIndex теперь равна ровно 12.
Чтобы закончить этот рисунок, назначьте заголовок в ячейке C2 как название двенадцати ячеек под этой ячейкой. Для этого выберите диапазон C2:C14. Затем в:
- Классический Excel: выберите «Вставить», «Имя», «Создать».
- Новый Excel: выбор формул, определенные имена, создание из выделенного.
- Либо Excel: выберите Alt+INC или Ctrl+Shift+F3.
В диалоговом окне «Создание имен» убедитесь, что отмечена только верхняя строка, затем нажмите «ОК».
Настройте десезонированные формулы продаж
Введите формулу для показанной ячейки:
Скопируйте формулу вниз по столбцу, как показано. (Здесь SeasIndex — это столбец в предыдущей таблице.)
Формула в ячейке E5 десезонализует продажи за январь 2005 г. путем деления этих продаж на индекс сезонности за январь. Формула в ячейке E6 делит продажи за февраль на сезонный индекс за февраль и т. д.
Ключом к этой формуле является функция ИНДЕКС, которая использует эту версию функции:
Здесь «массив» — это имя диапазона SeasIndex. И номер строки, рассчитанный функцией МЕСЯЦ. Когда эта функция ссылается на порядковый номер даты, она возвращает 1 для января, 2 для февраля и так далее. Мы можем использовать МЕСЯЦ таким образом в формуле, потому что первая строка SeasIndex содержит данные за январь; во второй строке — данные за февраль и т. д.
Часто задаваемые вопросы о сезонном указателе
Когда вы начнете работать с сезонным индексом, у вас наверняка возникнет несколько вопросов. Вот некоторые из наиболее вероятных:
Теперь, когда у меня есть сезонный индекс и десезонные продажи, что мне делать с этой информацией?
Вы можете составить график продаж без учета сезона, чтобы лучше понять тенденцию изменения объема продаж.
Можно спрогнозировать месячные продажи, умножив предполагаемые продажи на следующий год на сезонный индекс для каждого месяца. Или вы можете оценить 12-месячную тенденцию продаж без учета сезона, а затем применить сезонный индекс для прогнозирования фактических объемов продаж.
Вы можете более точно планировать укомплектование персоналом и расходы, используя таблицу среднего сезонного индекса, чтобы оценить изменения объема продаж в течение года.
Вы можете рассчитать сезонные индексы для разных лет истории продаж, а затем сравнить их, чтобы увидеть, влияют ли изменения на вашем рынке на вашу сезонность с течением времени. (Например, пятьдесят лет назад Джексон-Хоул, штат Вайоминг, летом был переполнен туристами, а зимой сократился до 100 постоянных жителей. Однако теперь зима приносит лыжников. Таким образом, сезонные модели значительно изменились. сезонные модели также могут меняться.)
Сколько месяцев истории мне нужно?
Для расчета сезонного индекса за январь 2008 г. необходимо начать с июля 2007 г. А для расчета сезонного индекса за декабрь 2008 г. необходимо закончить июнем 2009 г. Таким образом, вам потребуется как минимум два года данные о продажах для расчета сезонных индексов за один год.
Однако, поскольку сезонные индексы учитывают повторяющиеся закономерности, лучше всего усреднять сезонные индексы как минимум за два года. Это означает, что вам нужны данные как минимум за 36 месяцев.
Как мне добавить новые строки в таблицу, когда мне это нужно?
Когда вам понадобятся дополнительные строки, вы вставите их между строками 64 и 65 в этой таблице. Но все формулы в шести ячейках над ячейкой D65 ссылаются на эту ячейку. Это означает, что при вставке строк для новых данных вы испортите эти шесть формул в столбце D. Это не проблема, если вы выполните следующие действия:
<р>1. Вставляйте строки непосредственно над нижней серой границей. (Здесь между строками 64 и 65.) Можно вставить достаточно строк для данных за несколько лет. <р>2. Скопируйте формулы из диапазона D11:E11 в ячейку D12 через последнюю несерую ячейку в таблице. (Здесь к диапазону D12:D64.)При копировании из верхней части таблицы вы копируете формулы, которые, как вы знаете, не были искажены при вставке строк. Вставив эти формулы в существующие формулы и в новые пустые ячейки, вы убедитесь, что все формулы в столбцах D и E ссылаются на правильные строки данных.
Почему бы вам просто не использовать функцию СМЕЩ в формулах в столбце D? Это устранило бы проблемы, которые вы только что описали?
В этом вопросе говорится, что я могу заменить эту формулу…
…с этой формулой…
…где OFFSET возвращает ссылку, используя эти аргументы…
=СМЕЩЕНИЕ(ссылка, строки, столбцы, высота, ширина)
Возможно, это хорошая идея. Более длинная версия, безусловно, безопаснее. Но если вы редко используете OFFSET, более длинную версию, вероятно, будет труднее понять. Это ваш выбор.
Сезонный индекс показывает, как периодическая сумма (обычно за месяц) сравнивается со средним значением за все периоды длительного периода, например года. Поскольку сезонные индексы измеряют колебания цен, они обычно используются при прогнозировании продаж, но сезонные индексы можно использовать для анализа любой деятельности, на которую влияет сезон или определенное время года. Microsoft Excel — отличный инструмент для расчета сезонных индексов.
Откройте книгу Excel
Откройте книгу Excel, содержащую ваши данные. Ваши данные должны располагаться в соседних столбцах или строках, чтобы упростить функции и их вычисления.
Итоги и средние значения
В ячейке под последней записью сумм за период введите функцию =СУММ(. ) , заменив многоточие ссылками на ячейки, которые вы хотите суммировать для всех сумм за период. Под итогом введите функцию =СРЗНАЧ(. ), используя те же ссылки на ячейки, чтобы вычислить среднюю сумму за период. В показанном примере две записи =СУММ(B2:B13) и =СРЗНАЧ(B2:B13).
Рассчитать индексы
Сезонный индекс каждого значения рассчитывается путем деления суммы за период на среднее значение за все периоды. Это создает связь между суммой за период и средним значением, которая отражает, насколько период выше или ниже среднего.
Формула для расчета индекса
=Сумма за период / Средняя сумма или, например, =B2/$B$15.
Сумма индекса представляет собой десятичную дробь, указывающую отношение суммы за период к среднему значению за все периоды. Например, индекс за январь равен 0,76. Это означает, что январь составляет около 76 процентов от среднего. Август имеет индекс 1.83 – это около 183% от среднего.
Рон Прайс, обладатель степени магистра делового администрирования, имеет большой опыт работы на руководящих должностях в сфере бизнеса, информационных технологий и образования. Под псевдонимом Рон Гилстер Прайс написал более 40 книг для нескольких ведущих издателей на самые разные темы: от бизнеса и финансов до сертификации в области информационных технологий и недвижимости.
Функция Excel FORECAST.ETS.SEASONALITY возвращает продолжительность во времени сезонного шаблона на основе существующих значений и временной шкалы.
- значения – существующие или исторические значения (значения y).
- временная шкала — числовые значения временной шкалы (значения x).
- data_completion – [необязательно] Обработка отсутствующих данных (0 – считать нулевым, 1 – среднее значение). По умолчанию – 1.
- агрегация – [необязательно] поведение агрегации. По умолчанию 1 (СРЕДНИЙ). См. другие варианты ниже.
Функция FORECAST.ETS.SEASONALITY возвращает продолжительность во времени сезонного шаблона на основе существующих значений и временной шкалы. FORECAST.ETS.SEASONALITY можно использовать для расчета продолжительности сезона для числовых значений, таких как продажи, запасы, расходы и т. д., которые демонстрируют сезонный характер. Если шаблон не может быть обнаружен, ПРОГНОЗ.ETS.СЕЗОННОСТЬ возвращает ноль.
Пример
В приведенном примере формула в ячейке H16 выглядит следующим образом:
где C5:C16 содержит существующие значения, а B5:B16 содержит временную шкалу. С этими входными данными функция ПРОГНОЗ.ETS.СЕЗОННОСТЬ возвращает 4. Результат равен 4, поскольку значения в C5:C16 представляют квартальные данные о продажах, а продолжительность сезона составляет 1 год, то есть 4 квартала.
На диаграмме справа эти данные представлены в виде точечной диаграммы.
Примечания к аргументам
Аргумент values содержит зависимый массив или диапазон данных, также называемый значениями y. Это существующие исторические значения, на основе которых будет рассчитана продолжительность сезона.
Аргумент временная шкала – это независимый массив или диапазон значений, также называемых значениями x. Временная шкала должна состоять из числовых значений с постоянным интервалом шага. Например, временная шкала может быть годовой, квартальной, месячной, ежедневной и т. д. Временная шкала также может представлять собой простой список числовых периодов, как показано в примере.
Аргумент data_completion является необязательным и указывает, как FORECAST.ETS.SEASONALITY должен обрабатывать отсутствующие точки данных. Возможные варианты: 1 (по умолчанию) и ноль. По умолчанию FORECAST.ETS.SEASONALITY предоставляет отсутствующие точки данных путем усреднения соседних точек данных. Если для data_completion указано нулевое значение, FORECAST.ETS будет рассматривать отсутствующие точки данных как нулевое.
Аргумент aggregation является необязательным и определяет, как функция должна агрегировать точки данных, когда временная шкала содержит повторяющиеся метки времени. Значение по умолчанию равно 1, что означает СРЕДНИЙ. Другие варианты приведены в таблице ниже.
Операционные менеджеры часто лучше всех понимают спрос на товары и услуги и стоимость их доставки. Тем не менее, многие из них не обладают навыками для составления прогнозов на уровне, требуемом высшим руководством. Специалисты по финансам могут предложить такой анализ, но зачастую они недостаточно хорошо разбираются в операциях, чтобы в них разобраться.
Поскольку в составлении бюджета есть искусство, компромисс, необходимо объективноее прогнозирование. Под этим я подразумеваю что-то, что может быть сконструировано таким образом, что если кто-то будет следовать тому же процессу, он получит одинаковые цифры. Это должен быть механический, объективный процесс. Таким образом, аналитики могут подготовить эти данные за считанные минуты, не чувствуя эмоциональной привязанности к результату. Кроме того, операционные менеджеры могут проанализировать тенденцию и указать, где будущие цифры неверны, и все, что им нужно сделать, это объяснить отклонение, т. е. провести поэтапное бюджетирование. Нет необходимости в разногласиях или конфронтации: обе стороны могут работать вместе как одна команда.
Теперь все готово для простого механического подхода к составлению бюджета. Используя мой образец файла Excel (нажмите, чтобы загрузить), давайте посмотрим, как Excel может сделать это за вас. Представьте, что у меня есть исторические данные:
Данные за период с сентября 2012 г. по июль 2017 г., и я хочу экстраполировать их до конца 2020 г. (мне нужен прогноз на 2020 г.!)
Есть несколько функций, которые могут помочь, одна из самых простых — TREND. TREND(known_y's,known_x's,new_x's,[константа]) предполагает наличие связи между переменными x (независимая переменная - здесь даты) и y (зависимая переменная - продажи) по формуле y = βx + c, т.е. , уравнение прямой линии (β — это градиент линии, а c — точка пересечения с осью y).
Прежде чем игнорировать линейную регрессию, имейте в виду, что многие нелинейные отношения могут стать линейными при логарифмировании переменных. Однако это не всегда работает.
Здесь время — это наша независимая переменная (x), а продажи — это наша зависимая переменная (y). Мы указываем константу только в том случае, если хотим принудительно включить c в уравнение (необычно — обычно это поле остается пустым). Точно так же предпочтительнее оставить константу пустой в функции TREND. Например:
Здесь мы можем экстраполировать данные, используя функцию TREND а именно.
Возможно, вы слышали о проекциях хоккейной клюшки; ну, это рыба-меч. Если вы экстраполируете линейно, вы получите прямую линию. А теперь кто хотел бы представить этот прогноз высшему руководству?
Этого недостаточно. Нам нужно выявить цикличность данных. Кажется, он проходит цикл один раз в 12 месяцев. Это может быть не всегда так, но концепция остается неизменной, даже если периодичность не ежегодная.
Я хочу объективно рассчитать периодическую скорость роста. Я могу сделать это разными способами. Вы можете со мной поспорить. Вот в чем проблема — это субъективно, пока ваша организация не определит, как его следует измерять. Затем все следуют этому процессу, и он становится объективным.
В моем примере я собираюсь сравнить сумму продаж за 12 месяцев, закончившихся 31 июля 2017 года, с прогнозом продаж, рассчитанным с помощью TREND, за 12 месяцев, закончившихся 31 июля 2018 года:
Именно этот процент я буду использовать для увеличения прогнозов. (Обратите внимание, что пример файла Excel допускает различную периодичность, если цикл остается постоянным.)
Затем я увеличиваю значение каждого периода на соответствующее значение в предыдущем периоде на этот процент (здесь 7,82%). Это дает мне гораздо лучшую диаграмму:
С практикой этот подход не требует много времени для подготовки. Цифры могут отличаться от этого прогноза, и операционный менеджер должен объяснить эти отклонения. Это упрощает жизнь во всех отношениях.
После согласования метода оценки предполагаемых темпов роста на основе функции ТРЕНД и того, какие нормализации к историческим данным следует ввести, процесс становится более простым. Конечно, этот метод следует использовать для всех входных данных прогноза отдельно, а не только для их агрегирования; в противном случае возникает путаница из-за изменений в ассортименте продаж, новых продуктов, периодов отсечения и т. д.
Но есть еще более быстрый способ — если у вас есть Excel 2016.
Экспоненциальное тройное сглаживание (ETS) звучит как молочный процесс, но на самом деле оно использует средневзвешенное значение прошлых значений для прогнозирования. Он популярен в статистике, поскольку учитывает сезонные колебания данных, как в приведенном выше примере. Для тех, кому действительно нужно знать, Excel использует вариант алгоритма ETS Холта Винтерса.
В Excel 2016 функция ETS стала «родной», т. е. стала стандартной функцией. Сюда входят как набор новых функций, таких как FORECAST.ETS, так и другие вспомогательные функции для дополнительной статистики. Ваш набор данных не обязательно должен быть идеальным, так как функции восполнят до 30 % отсутствующих данных.
Не беспокойтесь об использовании этих функций. Просто выделите фактические данные и нажмите кнопку «Лист прогнозов» в группе «Прогноз» на вкладке «Данные» ленты (ALT A + FC):
Все, что вам нужно сделать, это указать окончательный период прогноза в подсказке, и все. Он создает лист необработанных данных вместе с доверительными интервалами (чтобы продемонстрировать потенциальный разброс ошибки прогноза), который выглядит примерно так:
Слово мудрым
Вручную или автоматически?
- «Ручной» метод, использующий TREND, предполагает некоторую линейную зависимость (возможно, на уровне производной) и требует некоторой первоначальной субъективности в отношении нормализации фактических данных и того, как определить, какой темп роста использовать в течение какой продолжительности. После согласования процесс становится простым для понимания и обслуживания.
- «Автоматический» метод с использованием таблицы прогнозов позволяет выполнить все операции одним нажатием кнопки после нормализации исторических данных. Однако он доступен только в Excel 2016, для многих это довольно «черный ящик», и вам придется объяснять своему линейному руководителю, что такое экспоненциальное тройное сглаживание.
Присяжные остаются в составе. Что бы вы ни решили, для вашего же здравого смысла я рекомендую использовать объективный подход к прогнозированию.
Читайте также: