XYZ анализ продаж. Пример расчета в Excel

XYZ анализ ­(англ. XYZ-analysis) – это метод классификации ресурсов компании по трем группам на основе их изменчивости (устойчивости). Объектами применения XYZ-анализа могут выступать различные экономические показатели компании: объем продаж, выручка, материальные затраты, количество поставщиков и т.д. Одним из самых распространенных направлений применения данного метода является определение товаров, которые имеют устойчивый спрос (объемы продаж), сезонный и случайный. Это позволяет произвести оптимизацию складских запасов и высвободить дополнительные ресурсы.

XYZ анализ ассортимента продукции. Формула расчета

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

На первом этапе осуществляется расчет коэффициента вариации объема продаж (уровня спроса) по каждой товарной группе. Изменчивость продаж оценивается с помощью коэффициента вариации. Формула расчета коэффициента вариации представляет собой отношение стандартного отклонения к математическому ожиданию объема продаж. Формула имеет следующий вид:

σ – стандартное отклонение объема продаж;

xi – объем продаж в период в i-й период;

n – количество рассматриваемых периодов продаж товаров;

х * – среднеарифметическое значение продаж товаров.

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

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

Группа Коэффициент вариации Характеристика класса Группа «X» 0<V<10% Товары с наиболее устойчивыми объемами продаж Группа «Y» 10%<V<25% Товары с прогнозируемыми, но изменчивыми объемами продаж Группа «Z» V>25% Товары, обладающие случайным спросом

Видео-урок: «Оценка продаж. Пример XYZ-анализа в Excel»

Пример XYZ анализа объемов продаж продукции в Excel

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

Ассортимент и объем продаж продукции

Далее необходимо рассчитать изменчивость объема продаж по каждой товарной группе. На рисунке ниже показан итог расчета коэффициента вариации по продукции. Формула расчета в Excel будет иметь следующий вид:

Коэффициент вариации объемов продаж =СТАНДОТКЛОН(B5:G5)/СРЗНАЧ(B5:G5)

Расчет коэффициента вариации продаж товаров

Сейчас необходимо классифицировать товары в группу – «X»,»Y» или «Z». Для этого напишем формулу определяющую класс товара, и воспользуемся встроенной формулой «ЕСЛИ» в Excel. Формула будет иметь следующий вид:

Группировка продукции по классам XYZ в Excel

Чтобы было наглядно видно структуру устойчивости продаж необходимо провести группировку по классам: главное меню Excel → «Данные» → «Сортировка».

XYZ анализ продаж продукции в Excel. Пример расчета

В результате мы получим следующую классификацию товаров. Телефоны «Lenovo» имеют самый устойчивый спрос, так как коэффициент вариации составляет всего 8%. То есть объем продаж в среднем по месяцам отклоняется на 8%. Компании следует уделить особое внимание складским запасам данных товаров. В группу «Y» попали довольно много различных видов продукции, компании следует оперативно отслеживать складские запасы. Товары группы «Z» продаются не регулярно, их запасы могут быть сокращены и фирма может работать по предзаказу. XYZ анализ хорошо себя зарекомендовал на практике управления запасами и ассортиментом товаров, также его часто объединяют с ABC анализом, который позволяет провести двухкритериальную группировку товаров. Более подробно о ABC анализе вы можете узнать в моей статье: «ABC анализ продаж. Пример расчета в Excel«.

Резюме

Метод XYZ анализа позволяет прогнозировать устойчивость спроса на продукции, объемы продаж и запасов. Использование метода высвобождает дополнительные ресурсы компании и оптимизирует их бизнес-процессы.