ABC и XYZ анализ в Excel с примером расчета товарного ассортимента
Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).
В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.
Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.
Благодаря анализу ABC пользователь сможет:
- выделить позиции, имеющие наибольший «вес» в суммарном результате;
- анализировать группы позиций вместо огромного списка;
- работать по одному алгоритму с позициями одной группы.
Значения в перечне после применения метода ABC распределяются в три группы:
- А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
- В – средние по важности (30% – 15%).
- С – наименее важные (50% – 5%).
Указанные значения не являются обязательными. Методы определения границ АВС-групп будут отличаться при анализе различных показателей. Но если выявляются значительные отклонения, стоит задуматься: что не так.
Условия для применения ABC-анализа:
- анализируемые объекты имеют числовую характеристику;
- список для анализа состоит из однородных позиций (нельзя сопоставлять стиральные машины и лампочки, эти товары занимают очень разные ценовые диапазоны);
- выбраны максимально объективные значения (ранжировать параметры по месячной выручке правильнее, чем по дневной).
Для каких значений можно применять методику АВС-анализа:
- товарный ассортимент (анализируем прибыль),
- клиентская база (анализируем объем заказов),
- база поставщиков (анализируем объем поставок),
- дебиторов (анализируем сумму задолженности).
Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.
Общая схема проведения:
- Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
- Выполнить сортировку параметров по убыванию.
- Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
- Найти долю каждого параметра в общей сумме.
- Посчитать долю нарастающим итогом для каждого значения списка.
- Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
- Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
- Для С – все, что ниже.
- Посчитать число значений для каждой категории и общее количество позиций в перечне.
- Найти доли каждой категории в общем количестве.
Составим учебную таблицу с 2 столбцами и 15 строками. Внесем наименования условных товаров и данные о продажах за год (в денежном выражении). Необходимо ранжировать ассортимент по доходу (какие товары дают больше прибыли).
- Отсортируем данные в таблице. Выделяем весь диапазон (кроме шапки) и нажимаем «Сортировка» на вкладке «Данные». В открывшемся диалоговом окне в поле «Сортировать по» выбираем «Доход». В поле «Порядок» – «По убыванию».
- Добавляем в таблицу итоговую строку. Нам нужно найти общую сумму значений в столбце «Доход».
- Рассчитаем долю каждого элемента в общей сумме. Создаем третий столбец «Доля» и назначаем для его ячеек процентный формат. Вводим в первую ячейку формулу: =B2/$B$17 (ссылку на «сумму» обязательно делаем абсолютной). «Протягиваем» до последней ячейки столбца.
- Посчитаем долю нарастающим итогом. Добавим в таблицу 4 столбец «Накопленная доля». Для первой позиции она будет равна индивидуальной доле. Для второй позиции – индивидуальная доля + доля нарастающим итогом для предыдущей позиции. Вводим во вторую ячейку формулу: =C3+D2. «Протягиваем» до конца столбца. Для последних позиций должно быть 100%.
- Присваиваем позициям ту или иную группу. До 80% – в группу А. До 95% – В. Остальное – С.
- Чтобы было удобно пользоваться результатами анализа, проставляем напротив каждой позиции соответствующие буквы.
Вот мы и закончили АВС-анализ с помощью средств Excel. Дальнейшие действия пользователя – применение полученных данных на практике.
XYZ-анализ: пример расчета в Excel
Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.
За аббревиатурой XYZ скрывается уровень прогнозируемости анализируемого объекта. Этот показатель принято измерять коэффициентом вариации, который характеризует меру разброса данных вокруг средней величины.
Коэффициент вариации – относительный показатель, не имеющий конкретных единиц измерения. Достаточно информативный. Даже сам по себе. НО! Тенденция, сезонность в динамике значительно увеличивают коэффициент вариации. В результате понижается показатель прогнозируемости. Ошибка может повлечь неправильные решения. Это огромный минус XYZ-метода. Тем не менее…
Возможные объекты для анализа: объем продаж, число поставщиков, выручка и т.п. Чаще всего метод применяется для определения товаров, на которые есть устойчивый спрос.
Алгоритм XYZ-анализа:
- Расчет коэффициента вариации уровня спроса для каждой товарной категории. Аналитик оценивает процентное отклонение объема продаж от среднего значения.
- Сортировка товарного ассортимента по коэффициенту вариации.
- Классификация позиций по трем группам – X, Y или Z.
Критерии для классификации и характеристика групп:
- «Х» – 0-10% (коэффициент вариации) – товары с самым устойчивым спросом.
- «Y» – 10-25% – товары с изменчивым объемом продаж.
- «Z» – от 25% – товары, имеющие случайный спрос.
Составим учебную таблицу для проведения XYZ-анализа.
- Рассчитаем коэффициент вариации по каждой товарной группе. Формула расчета изменчивости объема продаж: =СТАНДОТКЛОНП(B3:H3)/СРЗНАЧ(B3:H3).
- Классифицируем значения – определим товары в группы «X», «Y» или «Z». Воспользуемся встроенной функцией «ЕСЛИ»: =ЕСЛИ(I3
Источник: https://exceltable.com/otchety/abc-i-xyz-analiz-v-excel
Анализируем ассортимент товара с помощью ABC-анализа
Не умеете проводить ABC-анализ? Не беда. В статье мы расскажем о том, как нужно правильно провести ABC-анализ ассортимента на складе.
Люди, работающие в логистике и управлении запасами, часто сталкиваются с таким понятием, как АВС-анализ.
Этот инструмент используется для анализа товаров, находящихся на складе, выявления наиболее конкурентоспособных и наименее востребованных позиций.
АВС-анализ подчиняется знаменитому «принципу Парето», который гласит, что 20 % затрат дают 80 % результата. В случае с товарным запасом на складе говорят, что 20 % продукции дают 80 % оборота.
АВС-анализ применяется для оптимизации остатков и снижения затрат на хранение. Как следствие, результат использования новой стратегии — минимизация рисков, сокращение расходов и даже увеличение прибыли.
Почему применяется АВС-анализ?
АВС-анализ определяет востребованные ресурсы, которыми лучше запасаться в прок и количество каких стоит увеличить. Методика четко определяет какие позиции — «мертвый груз», который мешает развитию компании и от которого лучше избавиться как можно скорее.
Исследуемые ресурсы делят на 3 группы:
- Группа А. К данной группе относятся товары, которые покупатель приобретает чаще всего. Они приносят наибольшую прибыль предприятию (80 %), но таких товаров сравнительно мало (20%).
- Группа В. Спрос на товары этой группы ниже, чем на товары группы А, и эти товары характеризуются средним уровнем продаж.
- Группа С. В группу входит большинство товаров, присутствующих на складе. Помимо всего прочего, выручка с этих товаров минимальна. Скорее, от них стоит избавиться. Однако группа включает новые, еще не раскрученные товары, которым надо время, чтобы те нашли покупателя и перешли в другую группу.
С чего начать?
Первый шаг для проведения АВС-анализа — выбор объекта исследования. К нему могут относится не только товары на складе, но поставщик, клиент, запас. Вторым шагом выбираются параметры, по которым будет проводится анализ. Это — доходность, прибыльность, продаваемость, оборачиваемость и т.д. Полный план проведения АВС-анализа представляют следующим образом:
- Определение цели анализа
- Определение действий по завершению исследования
- Выбор объекта и параметра анализа
- Составление рейтингового списка объектов по убыванию значения параметра
- Вычисление доли параметра от общей суммы параметров с накопительным итогом. Доля с накопительным итогом высчитывается путём прибавления параметра к сумме предыдущих параметров.
- Определение какие товары относятся к какой группе: А, В или С.
Преимущества АВС-анализа
Для того, чтобы понять, насколько эффективен АВС-анализ, необходимо выделить его преимущества. Первое преимущество — относительная простота в использовании. По сравнении с другими методами АВС-анализ в результате несложных вычислений приводит к решениям, которые смогут в разы увеличить прибыль компании и сократить издержки.
Во-вторых, АВС-анализ универсален. Исследует объекты и параметры и при этом каждый раз рассматривать работу организации с различных сторон.
Помимо прочего выделяют комплексный АВС-анализ. В этом случае анализ проводится сразу по нескольким параметрам, например, прибыльности и оборотности. Сначала делают анализ по каждому из параметров по-отдельности, а потом сопоставляют результаты.
Поподробнее рассмотрим товары, которые попадают в группы АА, АС, СА и СС. АА – товары с большим оборотом и прибыльностью, делайте ставки на них – это ваши «звезды».
Пристальное внимание уделите безопасности и закупочной цене, попробуйте договориться с поставщиками о скидке за объем.
Снижение себестоимости «звезд» вашего ассортимента сделает их еще популярнее. Оптимизации на складе требуют товары, относящиеся к категории АС. Компания получает с них большую прибыль, но несет маленькую оборачиваемость.
В качестве решения предложите клиентам гибкие условия доставки и возврата. Если же товар относится к группе СА, стоит быть аккуратнее при предоставлении на него скидки – затраты на обработку превысят прибыль с продажи.
С группой СС проще: как можно скорее избавьтесь от этих товаров.
Комплексный АВС-анализ дает четкую картину о том, что в дальнейшем делать с товарными запасами на складе и каким образом лучше оптимизировать продажу и хранение ресурсов.
Однако, как и каждый метод, комплексный АВС-анализ неидеален. В случае с огромным ассортиментом есть вероятность попадания каждой категории в одну группу. Поэтому для каждой категории проводиться отдельный анализ.
Исказить картину могут товары, на которые на момент проведения анализа проводилась акция. Их спрос был искусственно завышен, а значит вероятно попадание в категорию А.
Товары же, которые на самом деле принадлежат этой категории попадут в группу В.
Методы АВС-анализа
Для АВС-анализа применяются различные методы. Суммарное количество таких методов десять, но самые популярные – это эмпирический метод, метод касательных и метод суммы. В эмпирическом методы товары распределяются по группа согласно пропорции 80/15/5.
Для метода суммы складывается доля объектов и их совокупная доля в результате. В этом случае значение суммы лежит в промежутке от 0 до 200 %. Выделение групп происходит следующим образом: группа А – 100%, группа В – 45 %, группа С – остальное.
Метод касательных же подразумевает построение касательной, которая отделяет сначала группу А, а потом группу С.
Выполняем анализ
Как уже было сказано выше, АВС-анализ состоит из 6 этапов. Остановимся поподробнее на каждом из этапов и рассмотрим их на конкретных примерах.
Первый этап АВС-анализа — определение цели анализа. Нельзя пропускать этот пункт анализа, так как тот влияет на успешность реализации последующих этапах.
Важно понимать, что АВС-анализ не самоцель, а лишь инструмент, который при правильном использовании способен дать намеченный результат. Объекты делят на группы по-разному, если проводимые анализы преследуют разные цели.
Что еще не менее важно – это правильно интерпретировать результат анализа, иначе эффект будет равен нулю.
Следующим шагом АВС-анализа является определение объектов анализа. Ниже представлены пути снижения запасов на складе:
- Для пересмотра объема заказов по товарам в качестве объекта анализа выбираем ассортиментные позиции.
- Для пересмотра условий работы или смены поставщиков в качестве объекта анализа — поставщики компании.
- Для пересмотра условий работы с покупателями в качестве объекта анализа — покупатели компании.
- Для анализа складского запаса и принятия решение об активизации продаж по позициям со сверхнормативным остатком в качестве объекта анализа — опять ассортиментные позиции.
Затем определяют параметры, на основе которых будет происходить дифференциация объектов анализа. Этап неразрывно связан со вторым: параметра определяются выбранными для анализа объектами. Для предыдущего примера используют следующие факторы:
- Если объект анализа — ассортиментные позиции, возможными параметрами могут быть: объем продаж по себестоимости, объем продаж в количественном выражении, доход от позиции, величина текущего или среднего остатка на складе
- Если объект анализа — поставщики компании, возможными параметрами могут быть: величина текущего или среднего остатка на складе в разрезе поставщиков; объем оборотных средств, вложенных в работу с тем или иным поставщиком, доходность средств, вложенных в работу с поставщиком
- Если объект анализа — покупатели компании, возможными параметрами могут быть: объем продаж в разрезе покупателей, доход по каждому покупателю.
Четвертый шаг связан с составлением рейтингового списка. Лучше представлять информацию в Excel. В качестве параметра исследования возьмем объем продаж товара, а объектом нашего АВС-анализа станет краска.
Пятым шагом вычисляем доли параметра от общей суммы параметров с накопительным итогом. Доля с накопительным итогом высчитывается путём прибавления параметра к сумме предыдущих параметров.
Наконец определяем, к какой группе относится каждая из позиций в списке. Определяем по последнему столбцу «Доля с накопительным итогом»: А — до 50%, В — 50-80% и С — 80-100%.
На основании данных, полученных после АВС-анализа, делаются выводы и принимаются дальнейшие меры. В нашем примеры последние 4 позиции относятся к группе С и из объёма продаж минимален. Их стоит хранить в наименьшем количестве или вовсе отказаться.
Таким образом, АВС-анализ — полезный инструмент, применение которого благотворно сказывается на работе компании, оптимизации издержек и увеличении прибыли.
Источник: https://holodsklad.ru/blog/abc-analiz-sklada/