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

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

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

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

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

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

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

Например:

Как рассчитать коэффициент вариации в 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 анализ.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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