XYZ анализ, автоматический расчет в Excel

Печать
 
 

XYZ в excel, формулы расчета

XYZ анализ в Excel. Отличный инструмент для анализа динамики продаж, выявления проблемных участков в товарной матрице с большим количеством ассортимента. XYZ анализ лучше всего совмещать с АВС анализом. Как это сделать в Excel автоматически? Вот тема сегодняшней статьи.

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

 

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

XYZ анализ имеет множество применений. Я же остановлюсь на одном из них, которое используется в розничной торговли. И так, методика xyz анализа:

  1. Необходимо рассчитать коэффициент вариации;
  2. Распределение по категориям X, Y, Z.

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

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

Если Вы не сильны в математике, объясню на языке Excel.

Среднеквадратичное отклонение { =СТАНДОТКЛОНП(диапазон периода)} делим на среднее значение { =СРЗНАЧ(диапазон периода)}

Теперь необходимо расставить xyz, где

Х – Является стабильной величиной потребления, или в нашем случае показывает стабильность продаж. Значение категории Х находится в интервале от 0 до 10%

Y – Является переменной величиной потребления, продажи незначительно колеблется. Это могут быть сезонные колебания, нестабильные поставки товара, несвоевременная выкладка товара и т.д. Значение категории от 10 до 25%

Z – Является не регулярным потреблением. Нестабильные продажи, предмет для глубокого анализа товара. В данной категории нет тенденций, и если Вы используете xyz анализ для прогнозирования, то точность этого прогноза будет ничтожно мала. Значение категории выше 25%.

Теперь все эти категории в Excel:

Используем одну из самых распространенных функций «ЕСЛИ»

=ЕСЛИ(Коэффициент вариации <0,1;"X";ЕСЛИ(Коэффициент вариации <0,25;"Y";"Z"))

После чего протягиваем формулу по всем товарам.

А теперь совмещаем АВС и XYZ в одной ячейке при помои формулы:

1 вариант =СЦЕПИТЬ (значение АВС; значение XYZ)

2 Вариант =значение АВС & значение XYZ

 

Дорогие друзья!

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

Скачать шаблон АВС и XYZ

 

Понравилась статья? Отблагодари автора ;)