XYZ анализ - коэффициент вариации - подготовка данных к прогнозу
Из данной статьи вы узнаете:
- Как рассчитать коэффициент вариации в 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 анализ.
Если коэффициент вариации больше 10 - 25% или для Y и Z рядов, то изучаем данные (например, продажи товара по месяцам в разрезе направлений продаж) и определяем факторы, повлиявшие на отклонение.
Добавляем фильтр на столбец XYZ анализ и анализируем ряды.
Сначала отфильтруем ряды с коэффициентом вариации больше 25% или Z
Изучаем ряды с большими отклонениями фактических данных за последние 4-5 месяцев. Определяем причины провалов или резких подъёмов продаж. Готовим данные для прогноза. Очищаем данные от влияния случайных факторов или корректируем дефицит.
Также, если в ряду большая неоднородность, то имеет смысл группировать временной ряд. Например,
- Неоднородные продажи по месяцам свернуть до продаж по кварталам,
- Продажи по неделям свернуть до продаж по месяцам,
- Продажи по товарам свернуть до товарных групп...
Сделать прогноз по однородной группе более высокого уровня, а затем распределить пропорционально логики внутри группы.
О том, как сгруппировать временной ряд, читайте статью "Как сделать сводную и сгруппировать временные ряды?"
Затем выделяем ряды с коэффициентом вариации Y
Аналогично просматриваем каждый ряд, и в случае, если замечаете нестандартное поведение ряда, выявляете причины и в случае необходимости очищаете данные.
Рекомендуем создать список факторов (например, акции по стимулированию сбыта, отсутствие товара на складе, спец клиенты...), и для каждого из факторов определить показатель, который вычитаем или прибавляем к данным для прогноза.
После того, как данные очищены от факторов, которые в будущем не повторятся и подготовлены для прогноза, мы рассчитываем прогноз продаж.
Скачать файл с примером расчета коэффициента вариации и XYZ анализом.
Теперь при расчете прогноза на большом количестве временных рядов, вы можете придерживаться следующей схемы:
- Рассчитываем коэффициент вариации;
- Делаем XYZ анализ;
- Готовим данные для прогноза (очищаем от случайных факторов или группируем временные ряды);
- Строим прогноз;
- Учитываем дополнительные факторы в прогнозе;
Точных вам прогнозов!
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite - автоматический расчет прогноза в Excel.
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Зарегистрируйтесь и скачайте решения
Статья полезная? Поделитесь с друзьями