XYZ анализ - коэффициент вариации - подготовка данных к прогнозу

Автор: Алексей Батурин.

xyz анализ и коэффициент вариации расчет в Excel

Из данной статьи вы узнаете:

  • Как рассчитать коэффициент вариации в Excel;
  • Как сделать XYZ анализ в Excel;
  • Применение XYZ анализа при подготовке данных к прогнозу.


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

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

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

Если коэффициент равен 0%, то ряд абсолютно однородный, т.е. все значения между собой равны.

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

Например:

Ряд

Oct-12

Nov-12

Dec-12

Коэффициент вариации

Однородный ряд

100

100

100

0%

Неоднородный ряд

150

1

300

81%

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

Коэффициент вариации = отношение стандартного отклонения к среднему

В Excel коэффициент вариации можно рассчитать с помощью следующей формулы:

=СТАНДОТКЛОНПА(ссылка на ряд)/(СУММ(ссылка на ряд)/СЧЁТЕСЛИ(ссылка на ряд;">0"))

где

  • СТАНДОТКЛОНПА(J6:M6) — формула для расчета значения стандартного отклонения в Excel за анализируемый период;
  • (СУММ(J6:M6)/СЧЁТЕСЛИ(J6:M6;">0")) — среднее за анализируемый период;

Вводим формулу в ячейку, получаем расчет коэффициента вариации

Коэффициент вариации

Протягиваем формулу на весь массив данных.

Скачать Excel файл с примером расчета коэффициента вариации

Как сделать XYZ анализ?

Теперь сегментируем наши коэффициенты вариации и присваиваем каждому одну из 3-х букв X Y и Z

  • X — для рядов с коэффициентом вариации от 0% до 10%
  • Y — для рядов с коэффициентом вариации от 10% до 25%
  • Z — для рядов с коэффициентом вариации от 25% и больше

Вводим в ячейку Excel формулу

=ЕСЛИ(N3<=0,1;"X";ЕСЛИ(N3<=0,25;"Y";"Z"))

N3 — ссылка на коэффициент вариации

XYZ анализ

Применение XYZ анализа при подготовке данных к прогнозу

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

Если коэффициент вариации больше 10 - 25% или для Y и Z рядов, то изучаем данные (например, продажи товара по месяцам в разрезе направлений продаж) и определяем факторы, повлиявшие на отклонение.

Добавляем фильтр на столбец XYZ анализ и анализируем ряды.

Сначала отфильтруем ряды с коэффициентом вариации больше 25% или Z

 XYZ анализ и коэффициент вариации

Изучаем ряды с большими отклонениями фактических данных за последние 4-5 месяцев. Определяем причины провалов или резких подъёмов продаж. Готовим данные для прогноза.  Очищаем данные от влияния случайных факторов или корректируем дефицит. 

Также, если в ряду большая неоднородность, то имеет смысл группировать временной ряд. Например,

  • Неоднородные продажи по месяцам свернуть до продаж по кварталам,
  • Продажи по неделям свернуть до продаж по месяцам,
  • Продажи по товарам свернуть до товарных групп...

Сделать прогноз по однородной группе более высокого уровня, а затем распределить пропорционально логики внутри группы. 

О том, как сгруппировать временной ряд, читайте статью "Как сделать сводную и сгруппировать временные ряды?"

Затем выделяем ряды с коэффициентом вариации Y

XYZ подготовка данных к прогнозу

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

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

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

Скачать файл с примером расчета коэффициента вариации и XYZ анализом.

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

  1. Рассчитываем коэффициент вариации;
  2. Делаем XYZ анализ;
  3. Готовим данные для прогноза (очищаем от случайных факторов или группируем временные ряды);
  4. Строим прогноз;
  5. Учитываем дополнительные факторы в прогнозе;

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite - автоматический расчет прогноза в Excel.
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

 

Комментарии   

#11 Алексей Батурин 21.09.2020 17:11
Цитирую Сергей Скориков:
В вашем варианте и ABC и XYZ считается по количеству, но разве, в случае с товарами, не нужно считать ABC по выручке, а XYZ по количеству? Ведь логично оценивать долю в сумме, а не в количестве, иначе, можно получить ситуацию, когда в группу A попадут товары с копеечной ценой, и мизерной долей в выручке и объеме склада, а крупные узлы при небольшом количестве штук могут давать огромный объем выручки и объем запасов.


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

Описания модели использования ABC анализа и XYZ анализа для анализа запасов, остатков, продаж, планирования поставок... - это темы отдельной статьи.

Если есть желание поделится опытом, напишите, пожалуйста, статью, опубликуем и поделимся вашим опытом с нашей аудиторией.

Спасибо за обратную связь!
Цитировать
#10 Сергей Скориков 21.09.2020 17:00
В вашем варианте и ABC и XYZ считается по количеству, но разве, в случае с товарами, не нужно считать ABC по выручке, а XYZ по количеству? Ведь логично оценивать долю в сумме, а не в количестве, иначе, можно получить ситуацию, когда в группу A попадут товары с копеечной ценой, и мизерной долей в выручке и объеме склада, а крупные узлы при небольшом количестве штук могут давать огромный объем выручки и объем запасов.
Цитировать
#9 Алексей Батурин 13.02.2018 08:19
Цитирую Алексей:
Цитирую Артем:
Добрый день Алексей,

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


Артем, добрый вечер.

Можете результаты прислать на ?


Артем, добрый день!

Файл получил, пропустили скобки в формуле, поэтому не правильно коэф-т считается.

Исправленный вариант отправил ответным письмом.

Рекомендую познакомится с нашей бесплатной программой для ABC-XYZ анализа и анализа выбросов в Excel

https://4analytics.ru/o-4analytics-abc-xyz-analiz/o-4analytics-abc-xyz-analiz.html

Хорошего дня!
Цитировать
#8 Алексей Батурин 12.02.2018 14:08
Цитирую Артем:
Добрый день Алексей,

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


Артем, добрый вечер.

Можете результаты прислать на ?
Цитировать
#7 Артем 12.02.2018 13:25
Добрый день Алексей,

Спасибо большое за статью, у меня есть статистика по продаже товарной категории за год. При расчете кооэффициентов вариации получаю максимальные значения у отдельных артикулов 6,55%. Смущает факт что в статистике есть резкие всплески продажи от месяца к месяцу, но не пойму почему тогда такие низкие коэффициенты вариации?
Цитировать
#6 -1 Алексей Батурин 12.02.2015 09:21
Цитирую Konstantin:
Если в ряду данных встречаются "минуса" (возвраты - поэтому значение с минусом) то нужно ли очищать ряд от них? Или же можно применять эту формулу? =СТАНДОТКЛОНПА(J3:M3)/(СУММ(J3:M3)/СЧЁТЕСЛИ(J3:M3;">0"))

Константин, здравствуйте.
Если в ряде встречаются возвраты, то такие ряды лучше сгруппировать - если это дневные продажи до месячных, месячные до квартальных, продажи по товарам до продаж до товарной группы... Т.е. надо сделать ряд стабильным. Рассчитывать прогноз по верхней группе, а потом распределять (если надо) прогноз по позициям внутри группы.
Если ситуация связана дефицитом, то ряд надо подготовить - скорректировать дефицит.
Или можете воспользоваться моделью экспоненциально го сглаживания, которая как раз для таких рядов подходит:
http://www.4analytics.ru/prognozirovanie/prognoz-po-metodu-eksponencialnogo-sglajivaniya-s-trendom-i-sezonnostyu-xolta-vintersa.html
Цитировать
#5 Konstantin 12.02.2015 06:50
Если в ряду данных встречаются "минуса" (возвраты - поэтому значение с минусом) то нужно ли очищать ряд от них? Или же можно применять эту формулу? =СТАНДОТКЛОНПА( J3:M3)/(СУММ(J3 :M3)/СЧЁТЕСЛИ(J 3:M3;">0"))
Цитировать
#4 Алексей Батурин 09.02.2015 08:08
Цитирую Konstantin:
Это коэффициент (вариации) можно применять не только к штукам, но и к денежным единицам (рублям)?

Константин, здравствуйте.
Коэффициент вариации можно применять к любым рядам.
Цитировать
#3 -1 Konstantin 09.02.2015 07:45
Это коэффициент (вариации) можно применять не только к штукам, но и к денежным единицам (рублям)?
Цитировать
#2 +1 Алексей Батурин 07.05.2013 10:00
Вадим, количество значений в выборке зависит от модели прогноза, которую вы используете. Если прогноз по скользящей средней к 3-м месяцам (как в примере), то коэффициент вариации рассчитываем за 4 последних месяца. Если к 2-м месяцам, то достаточно за 3 последних месяца.

Если модель с трендом и сезонностью то за весь период расчета тренда для прогноза.

Рассчитав коэффициент вариации, готовим данные к прогнозу. А подготовка данных напрямую влияет на точность.
Цитировать

Добавить комментарий