Печать
PDF

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. Учитываем дополнительные факторы в прогнозе;

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

Зарегистрируйтесь и протестируйте Forecast4AC PROскачать программу для прогнозирования Forecast4AC PRO

график модель прогноза + трендграфик сезенностьграфик прогноз + границы прогноза + трендграфик прогноз к предыдущим периодам

Forcast4AC Pro 4ddabd6bbee26 250x250Подписка "Прогноз с точностью 90% и выше!"

Присоединяясь к нам Вы получаете:

  • Более 10 рекомендаций по прогнозированию продаж!
  • Практические примеры!
  • Ссылку на Forecast4AC PRO - 40 дней бесплатно!
  • + советы по работе с программой!

Forecast4AC PRO - прогноз одним нажатием клавиши!

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

Ваши комментарии и вопросы:

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


Защитный код
Обновить