Содержание

Как эффективно закупать сезонные товары: автоматический расчет сезонности в 1с

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

Такие факторы называют сезонными колебаниями и мы должны учитывать их при закупках.

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

Коэффициент сезонности рассчитывается:

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

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

Если возможно, исключаются внутренние факторы: промо-акции, недопоставки и т.д.

Игнорируются магазины, которые только открылись или отсутствовали в предыдущие периоды продаж.

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

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

Система автозаказа min-max.pro использует классический метод расчёта сезонности: на основании истории продаж, вычисляется коэффициент, который показывает величину продаж в конкретный месяц относительно среднегодовых продаж. Сумма всех коэффициентов за год равна 12.

Фрагмент отчёта с выведенными сезонными коэффициентами и помесячными продажами

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

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

Вы можете попробовать сделать закупки с учетом сезонного коэффициента на своей базе, воспользовавшись 14 дневным тестовым периодом. Узнать подробнее о сезонных коэффициентах в min-max.pro и оставить заявку на демо-версию можно здесь.

Подписывайтесь на наши новостные рассылки, а также на каналы Telegram , Vkontakte , Яндекс.Дзен чтобы первым быть в курсе главных новостей Retail.ru.
Добавьте «Retail.ru» в свои источники в Яндекс.Новости

Такие факторы называют сезонными колебаниями и мы должны учитывать их при закупках.

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

Коэффициент сезонности рассчитывается:

Если возможно, исключаются внутренние факторы: промо-акции, недопоставки и т.д.

Игнорируются магазины, которые только открылись или отсутствовали в предыдущие периоды продаж.

Фрагмент отчёта с выведенными сезонными коэффициентами и помесячными продажами

Как эффективно закупать сезонные товары: автоматический расчет сезонности в 1с

[expert_bq id=»1570″]Коэффициент вариации показывает отношение стандартного отклонения к среднему арифметическому, а результат отображается в процентах. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В этой статье при обозначении элемента таблицы символом у, первый индекс указывает номер строки, а второй — номер столбца, в соответствии с правилом обозначения элементов матриц, принятым в Советском Союзе. Замечу, что в английской традиции принята обратная запись, то есть сначала пишут столбец, а затем строку, а в современной российской литературе встречаются оба варианта.

Скользящее среднее в Excel.

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

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

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

Коэффициент сезонности — это величина, на которую увеличиваются / уменьшаются продажи по сравнению со средними в определенный период времени.

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

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

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

Прежде всего нужно взять статистику продаж за несколько последних лет (в идеале 3-4 года) для каждой товарной группы свою.

После чего делим продажи каждого месяца на средние продажи соответствующего года и получаем коэффициент для каждого месяца:

Затем выводим среднеарифметические коэффициенты для каждого месяца за 4 года:

ИТОГО:
	Январь	Февраль	Март	Апрель	Май	Июнь	Июль	Август	Сентябрь	Октябрь	Ноябрь	Декабрь
	0.49	0.94	1.02	1.05	0.87	1.09	1.06	1.12	0.96	1.12	1.09	1.2

Добавляем экспертное мнение (возможно когда-то был большой период дефицита товара, где- то сделана супер большая проектная поставка, где-то появился новый продукт и продажи резко возросли, в конце 2008 на продажи мог повлиять кризис — одним словом, экспертное мнение):

Коэффициент

0.5

0.85

1.05

0.95

1.15

1.05

0.95

1.1

1.35

ВАЖНО! При дальнейшей экспертной оценке обязательно нужно следить, чтобы сумма коэффициентов за год не превысила число 12. В противном случае можно недопланировать или наоборот, перепланировать.

[expert_bq id=»1570″]также мы находили продукты, в которых оптимальные циклы составляли 3, 9, 11 дней и во всех случаях это объяснялось внутренними ивентами в продукте в частности, турнирами. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] «Диапазон значений» указывает с исходными данными. Можете указать его вручную, либо просто выделив нужный диапазон ячеек. Вместо оператора СТАНДОТКЛОН также можно ввести СТАНДОТКЛОН .В или СТАНДОТКЛОН .Г соответственно(для новых версий Excel).

Коэффициент детерминации

Коэффициент детерминации рассчитывается для оценки качества подбора уравнения регрессии. Для приемлемых моделей предполагается, что коэффициент детерминации должен быть хотя бы не меньше 50%. Модели с коэффициентом детерминации выше 80% можно признать достаточно хорошими. Значение коэффициента детерминации R 2 = 1 означает функциональную зависимость между переменными.

ИТОГО:
	Январь	Февраль	Март	Апрель	Май	Июнь	Июль	Август	Сентябрь	Октябрь	Ноябрь	Декабрь
	0.49	0.94	1.02	1.05	0.87	1.09	1.06	1.12	0.96	1.12	1.09	1.2

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

Сам метод скользящего среднего рассмотрен в статье Скользящее среднее в MS EXCEL, в которой показано как для этого использовать инструмент MS EXCEL Пакет анализа, а также линию тренда и формулы.

Построение исходного и сглаженного ряда

Для построения рядов можно использовать диаграмму типа График или Точечная. Выберем последний тип – Точечная (ниже будет пояснено почему Точечная в данном случае удобнее).

Для исходных рядов нам понадобится 4 столбца с данными (2 «постоянных» процесса, ряд с цикличностью и тренд). В файле примера на листе Исходный и сглаженный ряд это столбцы T:W.

Примечание: Про функцию СЛУЧМЕЖДУ() можно почитать здесь. Эта функция генерирует непрерывное равномерное распределение, чтобы сгенерировать выборку из нормального или любого другого распределения см. эту статью.

Выбор нужно типа процесса организован с помощью группы переключателей, которая связана с ячейкой I11.

Значения выбранного исходного ряда подставляются в столбце В с помощью формулы = СМЕЩ(T13;;$I$11-1) . Подробнее про функцию СМЕЩ() см. здесь.

Сглаженный ряд разместим рядом в столбце С, этот ряд будет формироваться для заданного периода усреднения (ячейка A7) с помощью формулы = ЕСЛИ(A13<$A$7;НД();СРЗНАЧ(СМЕЩ(B13;-$A$7+1;;$A$7)))

Примечание: Про построение сглаженного ряда см. Скользящее среднее в MS EXCEL.

Осталось сформировать данные для линии среднего значений исходного ряда. Для этого понадобится только 2 точки (см. диапазон F43:G44).

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

Для тренда сглаженный ряд будет выглядеть так:

Расчет прогнозного значения

Напомним, что метод скользящего среднего состоит в вычислении средних значений на основе предшествующих значений исследуемого числового ряда. Пусть последнее значение ряда произошло в момент i.
В случае усреднения за 3 периода скользящее среднее в момент i равно:
Y_скол.i=(Y_i+ Y_i-1+ Y_i-2)/3

Именно так считает инструмент Пакета Анализа «Скользящее среднее». Понятно, что нас интересует прогноз в будущий момент времени i+1. Положим, что прогнозное значение ряда в момент i+1 равно Y_{прогнозн.i+1}= Y_скол.i
В итоге получаем эквивалентную формулу
Y_{прогнозн.i+1}=(Y_i+ Y_i-1+ Y_i-2)/3

Для наглядности прогнозное значение на диаграмме изобразим в виде горизонтальной линии зеленого цвета (длина линии ничего не значит). Для этого понадобится только 2 точки (см. диапазон F8:G9).

Хотя использование метода скользящего среднего для прогнозирования носит скорее академический, нежели практический интерес, все же покажем как построить что-то типа «интервала предсказания». Для построения интервала воспользуемся ошибкой, которая вычисляется в надстройке Пакет анализа по формуле:

= КОРЕНЬ(СУММКВРАЗН(ИР;СР)/m)
Где m – количество периодов усреднения
ИР — m последних значений Исходного Ряда (ИР)
СР — m последних значений Сглаженного Ряда (СР)

Т.е. данная стандартная ошибка вычисляется по формуле:

RMSE – это Root Mean Squared Error (среднеквадратическая ошибка).

В файле примера на листе «Прогнозное значение» эта ошибка вычислена по формуле
=КОРЕНЬ(СУММКВРАЗН(СМЕЩ($B$112;-A7;;A7);СМЕЩ($C$112;-A7;;A7))/$A$7)

A7 содержит количество периодов усреднения
СМЕЩ($B$112;-A7;;A7) – это ссылка на диапазон исходного ряда (последние m значений)
СМЕЩ($C$112;-A7;;A7) – это ссылка на диапазон сглаженного ряда
СУММКВРАЗН() вычисляет сумму квадратов разностей

Если вычислить ошибки прогнозирования в отдельном столбце D, то формула для RMSE упростится:
= КОРЕНЬ(СУММКВ(СМЕЩ($D$112;-A7;;A7))/$A$7)

Границы интервала (для заданного уровня значимости альфа) вычисляются как:

Верхняя граница = Y_{прогнозн.i+1} + RMSE*t_{m-1,1-альфа/2}
Нижняя граница = Y_{прогнозн.i+1} — RMSE*t_{m-1,1-альфа/2}

t_{m-1,1-альфа/2} — верхний α/2-квантиль распределения Стьюдента с m-1 степенью свободы (это просто число, которое показывает сколько ошибок RMSE нужно, чтобы «интервал предсказания» накрыл прогнозное значение с вероятностью 1-альфа).

Примечание: «Интервал предсказания» вычислен лишь по аналогии с построением доверительного интервала для оценки среднего, для которого у нас была статистическая модель. Для случая скользящего среднего корректность такого построения обосновывается отдельно. В данной статье «Интервал предсказания» построен лишь с целью демонстрации самого процесса построения интервалов предсказания.

Верхний α/2-квантиль вычислим по формуле = СТЬЮДЕНТ.ОБР.2Х(C8;A7-1)
в ячейке С8 находится альфа – уровень значимости (обычно 5%).

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

Как и следовало ожидать, для растущего тренда картинка существенно отличается: доверительный интервал уже в 2 раза меньше и прогнозное значение находится вдали от глобального среднего.

Автокорреляция исходного ряда

Исследуем исходный ряд на наличие автокорреляции. Подробно об автокорреляции см. отдельную статью .

Примечание: Диаграмма построена на листе «Автокорреляция ИР» в файле примера . ИР – Исходный Ряд.

В нашем случае будем исследовать корреляционную зависимость между двумя рядами данных, сдвинутых на лаг k относительно друг друга (см. диаграмму выше).

Для лага k=4 диаграмма рассеяния, очевидно, демонстрирует наличие линейной положительной корреляции.

С помощью инструмента диаграммы «Линия тренда» построена линия регрессии и вычислим коэффициент детерминации R2. Ниже мы вычислим R2 с помощью формул, т.к. это просто квадрат коэффициента автокорреляции.

Вычислим коэффициенты автокорреляции для лагов от 1 до 15.

Два массива в аргументах функции КОРРЕЛ () – это просто 2 ряда, которые сдвинуты на лаг k (ячейка K68) относительно друг друга:
СМЕЩ($B$13;;;$B$10-K68)
СМЕЩ($B$13;K68;;$B$10-K68)

Зависимость коэффициента автокорреляции от лага – это функция автокорреляции (ACF). График ACF – это коррелограмма. Для стационарного процесса (у нас это «постоянный» процесс, фактически белый шум) коррелограмма имеет следующий вид:

Для другого стационарного процесса (с апериодической цикличностью) коррелограмма имеет совершенно другой вид:

Все коэффициенты автокорреляции, которые выше границ доверительного интервала, являются статистически значимыми (про расчет доверительного интервала для ACF см. статью про Автокорреляцию ). Диаграмма рассеяния для выбранного лага (столбец гистограммы, который выделен цветом) также подтверждает отсутствие автокорреляции.

Коэффициент детерминации R2, указанный на диаграмме рассеяния можно рассчитать возведя в квадрат коэффициент корреляции или применив формулу
=КВПИРСОН(СМЕЩ($B$13;;;$B$10-K68);СМЕЩ($B$13;K68;;$B$10-K68))
для тех же массивов, полученных из исходного ряда.

Вычисление ошибок модели

Все ошибки вычислены в файле примера на листе Ошибки модели в диапазоне M7:Q11.

Проверка адекватности модели

На листе Ошибки модели построена диаграмма разброса ошибок и гистограмма ошибок. Эти диаграммы автоматически перестраиваются в зависимости от выбранного лага или типа исходного ряда.

Диаграмму разброса ошибок можно построить на основе диаграммы MS EXCEL типа График. Специальных знаний построения диаграмм практически не требуется. Впрочем, как и для построения Гистограммы. Несколько сложнее построить таблицу исходных данных для гистограммы. Об этом подробно рассказано в статье Гистограмма распределения

Диаграмма разброса ошибок должна демонстрировать колебания ошибок около 0, а гистограмма — типичную выборку из нормального распределения. Проверить распределение ошибок на нормальность можно построить соответствующий график.

Подробнее о проверке распределения на нормальность см. в этой статье.

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

На картинке выше приведены диаграмма рассеяния и коррелограмм для ошибок «постоянного» процесса.

Примечание: На листе «скользящее среднее» объединены все диаграммы, о которых рассказывалось выше в статье.

[expert_bq id=»1570″]Рядом видим коэффициент детерминации R 2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи Y. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Другим распространенным подходом к оценке репрезентативности является обоснованность выборки с позиций той или иной задачи. Например, при изучении спроса на автомобили стоимостью от миллиона рублей выборка, сделанная из лиц с доходом 8-10 тыс. руб. , будет всегда нерепрезентативной.

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

Скользящее среднее в Excel

Что такое скользящая средняя в Excel

Скользящее среднее — это широко используемый метод анализа временных рядов, который используется для прогнозирования будущего. Скользящие средние во временном ряду в основном строятся путем усреднения различных последовательных значений данных другого временного ряда.

Существует три типа скользящих средних, а именно простая скользящая средняя, взвешенная скользящая средняя и экспоненциальная скользящая средняя в Excel.

# 1 — Простая скользящая средняя в Excel

Простое скользящее среднее помогает вычислить среднее за последнее количество периодов ряда данных. Предположим, что даны цены за n период. Тогда простая скользящая средняя имеет вид

Простая скользящая средняя = [P1+P2+………….+Pn]/ п

# 2 — Взвешенная скользящая средняя в Excel

Средневзвешенная скользящая средняя представляет собой средневзвешенное значение за последние n периодов. Вес уменьшается с каждой точкой данных за предыдущий период времени.

Средневзвешенное скользящее среднее = (Цена * весовой коэффициент) + (Цена предыдущего периода * весовой коэффициент-1)

# 3 — Экспоненциальная скользящая средняя в Excel

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

Экспоненциальная скользящая средняя = (K x (C — P)) + P

K = константа экспоненциального сглаживания
C= текущая цена
п= экспоненциальная скользящая средняя за предыдущие периоды (простая скользящая средняя, используемая для расчета первых периодов)

Как рассчитать скользящую среднюю в Excel?

Вы можете скачать этот шаблон Excel для скользящего среднего здесь — Шаблон для скользящего среднего в Excel

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

Для расчета простого скользящего среднего мы взяли данные о продажах компании с января по декабрь за 2018 год. Наша цель — сгладить данные и узнать показатель продаж в январе 2019 года. Здесь мы будем использовать трехмесячное скользящее среднее.

Шаг 1: Скользящее среднее за январь, февраль и март рассчитывается путем деления суммы продаж за месяцы на 3.

Шаг 2: Выбор в углу ячейки D5, а затем просто перетаскивание вниз даст скользящее среднее для оставшихся периодов. Это функция инструмента заливки в Excel.

Шаг 3: Теперь мы нанесем на линейный график показатель продаж и скользящее среднее, чтобы понять разницу в тенденции. Это можно сделать на вкладке вставки. Сначала мы выбрали ряд данных, а затем из раздела «Диаграммы» под вставкой мы использовали линейный график.

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

Пример # 2 — Простое скользящее среднее на вкладке анализа данных в Excel

На этом графике показана разница между фактической и прогнозируемой скользящей средней.

Пример № 3 — Взвешенная скользящая средняя в Excel

Мы используем средневзвешенную скользящую среднюю за три года, формула которой приведена на скриншоте.

После использования формулы мы получили скользящую среднюю за период.

Мы получили скользящее среднее для всех остальных периодов, перетаскивая значения в следующие ячейки.

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

Пример # 4 — Экспоненциальная скользящая средняя в Excel

Формула экспоненциальной скользящей средней: S_т= α.Y_т-1+ (1- α) S_т-1…… (1)

И_т-1= фактическое наблюдение в t-1-м периоде
S_т-1= простая скользящая средняя за период t-1
а = коэффициент сглаживания, и он варьируется от 0,1 до 0,3. Чем больше значение α приближает график к фактическим значениям, и чем меньше значение α, тем более гладким будет график.

Сначала мы вычисляем простую скользящую среднюю, как показано ранее. После этого применяем формулу, приведенную в уравнении (1). Чтобы зафиксировать значение α для всех следующих значений, мы нажали F4.

Мы получаем значения, перетаскивая их в следующие ячейки.

Теперь мы хотим увидеть сравнение между фактическими значениями, простой скользящей средней и экспоненциальной скользящей средней в Excel. Мы сделали это, построив линейный график.

На приведенном выше снимке экрана мы видим разницу между фактическим показателем продаж, простой скользящей средней и экспоненциальной скользящей средней в Excel.

[expert_bq id=»1570″]А если этот коэффициент максимален для 7-го порядка, это значит, что ряд содержит циклические колебания с перио дичностью в 7 дней. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Предварительно исходные данные нужно очистить от выбросов — нетипично высоких или низких значений показателя, которые находятся за пределами ожидаемого диапазона. Часто на графике такие данные выглядят как значительные пики или наоборот падения практически до нуля, которые превосходят обычные значения в несколько раз.

Как рассчитать коэффициент вариации в Эксель » Компьютерная помощь

K = константа экспоненциального сглаживания
C= текущая цена
п= экспоненциальная скользящая средняя за предыдущие периоды (простая скользящая средняя, используемая для расчета первых периодов)

Для этого нужно определить отклонение значений метрики от линии тренда (столбец K), а затем найти среднее значение этих отклонений в зависимости от дня цикла. Эти средние значения и есть искомые коэффициенты.

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции