Как найти анализ abc

ABC-анализ — это способ классификации ресурсов по степени их влияния на прибыль компании. Чаще всего метод ABC применяют к товарному ассортименту, чтобы понять рентабельность групп товаров или отдельных наименований и скорректировать ассортимент. Поэтому дальше мы будем рассказывать о методе именно в контексте анализа товаров.

Что такое ABC-анализ

В основе метода лежит принцип Парето: 20% усилий обеспечивают 80% результата. Применительно к товарному ассортименту его можно сформулировать так: 20% товаров делают 80% оборота компании.

Суть метода в том, чтобы отранжировать позиции с точки зрения вклада в общую прибыльность. Все товары в результате будут разделены на три группы:

• Группа А — наиболее ценные позиции. Эти товары составляют 20% от общего ассортимента и обеспечивают 80% продаж.
• Группа В — промежуточные позиции. Составляют 30% ассортимента и делают 15% продаж.
• Группа С — наименее ценные. Эти товары составляют 50-60% ассортимента и дают 5% продаж.

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

Помимо корректировки ассортимента продукции, ABC-анализ можно использовать для оптимизации запасов. Разделяя товары на различные группы в зависимости от их важности, компания может лучше отслеживать наличие наиболее ценной продукции на складе.

Где применяют ABC-анализ

ABC-анализ применяют для:

• разработки стратегии развития компании, а также при ее пересмотре;
• построения маркетинговой стратегии;
• выстраивания стратегии продаж;
• планирования бюджета;
• управления запасами компании.

Нет смысла проводить ABC-анализ разово. Нужно анализировать ресурсы минимум один раз в год, а лучше раз в квартал и оценивать результаты за несколько периодов. Так вы сможете отследить рентабельность товаров в динамике.

Виды ABC-анализа

ABC-анализ можно проводить для оценки:

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

Как сделать ABC-анализ

Для проведения ABC-анализа понадобится любая таблица. Удобно использовать Excel- или Google-таблицу, но можно и нарисовать от руки.

1. Занесите в первый столбец все позиции, которые хотите отранжировать.
2. Во втором столбце определите критерий, по которому будете оценивать позицию. Это может быть прибыль, объём продаж, стоимость сырья, уровень затрат или инвестиций. Выразите значимость позиции в процентном соотношении к общему объёму. Например:

Выручка по товару за период / Общая выручка компании * 100%

Получается, если общая выручка составила 500 тыс. руб., а выручка по товару 1 — 260 тыс. руб., значит, вклад товара 1 в общую прибыль —  52%.

1. Отсортируйте позиции по убыванию процентных значений.
2. В третьем столбце посчитайте совокупный процент по каждой позиции, начиная с верхней строки. Совокупный процент в первой строке будет равен проценту от общего объёма. Начиная со второй строки совокупный процент считается по формуле:

Совокупный процент = Процент от общего объёма + Совокупный процент предыдущей позиции

1. В четвёртом столбце обозначьте принадлежность к соответствующей группе. Позиции, совокупный процент которых составляет до 80%, относятся к группе А, 80-95% — к группе В, 95-100% — к группе С.

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

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

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

Пример ABC-анализа

Допустим, мы продаём декоративную косметику и хотим понять, какие группы товаров продавать выгодно, а какие нет.

Шаг 1. Заносим в таблицу все интересующие нас группы товаров: тени, тональные средства, помады, пудры, туши и так далее. Проставляем суммы продаж за последние три месяца (квартал) и сортируем по убыванию итоговой суммы продаж.

Шаг 2. Вычисляем процент от общего объёма продаж по каждой группе и сортируем по убыванию процентного значения.

Например, за последние три месяца мы продали тональных кремов на 22 000 рублей и это составляет 22% от суммы общего дохода за этот период (от 100 000 рублей).

Шаг 3. Вычисляем совокупный процент по каждой категории товаров.

Шаг 4. Обозначаем принадлежность к соответствующей группе.

Шаг 5. Делаем выводы. Нам выгодно продавать тональные кремы, пудры и тени. На них и будем делать ставку: больше закупим; сделаем выкладку на прилавке, привлекающую внимание к этим товарам; устроим акцию, чтобы увеличить продажи. Помадами и тушью можно поддерживать ассортимент и, возможно, есть смысл расширить их цветовую палитру. А вот со скульпторами и консилерами у нас не заладилось: их плохо покупают. А так как у нас на складе скопилось много скульпторов и консилеров, мы не будем их закупать в ближайшие полгода и в дальнейшем выведем из ассортимента неходовые оттенки.

Преимущества и недостатки ABC-метода

Преимущества:

• Простота. С помощью готовых шаблонов и надстроек анализ можно сделать меньше чем за часа, при этом методу легко научиться.
• Надёжность результатов. Чем проще алгоритм, тем меньше шансов сделать что-то не так, а так как каждый этап расчётов прозрачен и понятен, то и выявить ошибку легко.
• Помогает оптимизировать ресурсы. Результаты ABC-анализа наглядно показывают, на чём стоит сконцентрироваться, а от чего отказаться.
• Универсальность. Можно анализировать практически всё: товары, сырьё, деньги, людей — результаты будут справедливы.

Недостатки:

• Одномерность. Анализу подвергается только одна сторона ресурса. То есть если вам нужно оценить товары только с точки зрения объёмов продаж, то метод подходит. А если хотите одновременно изучить два параметра, например, объёмы продаж и регулярность спроса, то нет.
• Предполагает, что прошлые тенденции определяют будущие показатели. Но это не всегда верно. Именно поэтому ABC-анализ должен проводиться на регулярной основе, что позволяет лучше понимать динамику.
• Не учитывает дополнительные издержки, связанные с расширением группы A или сокращением группы C. Например, на поиск новых поставщиков и связанные с этим риски.  
• Нельзя проанализировать качественные характеристики. Ранжировать товары можно только по количественным показателям. Если в ассортименте намешаны товары разного потребительского назначения, например, продукты питания и бытовая химия, то придется сначала сгруппировать их, а уже потом анализировать отдельно каждую группу.
• Разделение на группы А, В и С не всегда подходит. Например, метод не показывает убыточные позиции. Чтобы избежать этого недостатка, анализ трансформируют в ABCD-матрицу, где D — это группа нерентабельных товаров. Другой вариант — в группу А попали не 20%, а 40% позиций. В этому случае придется добавлять группу А+, куда войдут наиболее рентабельные товары.
• Не отражает влияние внешних факторов: сезонности, колебаний спроса, покупательской способности, конкурентов и т.д.

Совмещённый ABC/XYZ анализ

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

XYZ-анализ также сортирует позиции по трем группам X, Y и Z, но здесь оценивается устойчивость спроса, то есть его отклонения за конкретный период времени (коэффициент вариации).

Чтобы провести совмещённый анализ, нужно сначала сделать ABC-анализ, а затем XYZ-анализ этих же товаров за этот же период, например, по количеству ежемесячной реализации за год. После этого результаты совмещаются.

При совмещении определяется девять групп товаров:

• товары из групп АХ и ВХ хорошо продаются, и спрос легко прогнозировать;
• из групп AY и BY хорошо продаются, но спрос сложно спрогнозировать;
• из групп AZ и BZ хорошо продаются, но спрос невозможно спрогнозировать;
• из группы СХ приносят немного прибыли, но спрос стабильный;
• из группы CY приносят немного прибыли, спрос нестабильный;
• товары из группы CZ — это все новые товары, товары спонтанного спроса, поставляемые под заказ и т. п.

Вернемся к нашему примеру с продажей косметики. Вот как выглядит совмещённый анализ для наших ассортиментных групп.

XYZ-анализ дополнил результаты ABC-анализа. Теперь мы видим стабильность спроса на позиции: чем выше коэффициент вариации, тем больше колебания спроса.

В группу AX попали тональные основы — их выгоднее всего продавать и спрос стабильный. Расширим ассортимент тональников и подумаем, как максимально поднять продажи. Пудры и тени попали в группу AY — их хорошо покупают, но спрос то высокий, то низкий. Выясним, что влияет на колебания спрос. Если это сезонные факторы (пудрами больше пользуются летом), то будем это учитывать и закупать эти товары в меньшем объеме ближе к холодам. Туши и помады пользуются наиболее стабильным спросом. Хоть выгода от них небольшая, стоит расширить их ассортимент и стимулировать продажи. Консилеры и скульпторы — самые нерентабельные категории с непредсказуемым спросом. Оставим самые универсальные оттенки, а от остальных откажемся.

Мы подготовили шаблон совмещенного ABC/XYZ-анализа. В нём можно провести анализы как по отдельности, так и вместе. Все формулы уже «зашиты» в нужных ячейках.

Метод ABC может использоваться как самостоятельный инструмент для анализа рентабельности товаров или других ресурсов компании, но не отражает внешние факторы, влияющие на уровень продаж. Чтобы увидеть колебания спроса, нужно провести совмещённый ABC/XYZ-анализ, который позволяет более эффективно скорректировать ассортимент товаров, услуг, работу отделов и отдельных людей, перераспределить затраты.

Также совместно с ABC-анализом можно использовать:

• VEN-анализ, который применяют для разделения товаров на жизненно важные, необходимые и второстепенные;
• FMR-анализ, который помогает определить, с какой частотой продаются разные товары;
• матрицу BCG, которая помогает классифицировать товары по доле рынка и темпам роста.

Главные мысли

ABC XYZ анализ — удобный метод оценки эффективности работы «жизненно важных» отделов компании: продаж, маркетинга, склада, финансов. Представляем подробную инструкцию, как выполнить ABC и XYZ-анализ в Excel.

С общими принципами проведения ABC и XYZ-анализа можно ознакомиться здесь. А ниже — пошаговая инструкция, как сделать ABC-анализ в Excel.

Содержание:

ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами

Ассортиментный ABC анализ проведем на примере компании по продаже запасный частей для сельскохозяйственной техники.

Количество товара — более 5 000 позиций. Объединяем их в группы по видам номенклатуры.

Из учетной системы выгружаем данные за 2020 год:

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

Сортировка выручки по убыванию

Выделяем диапазон ячеек: вся таблица вместе с заголовками без строки «Итого».

В ниспадающем меню выбираем:

Данные — Сортировка — Сортировать по:

• столбец «Выручка»
• сортировка «Значения»
• порядок «По убыванию»

Нажимаем «Ок».

Система выстраивает таблицу по убыванию размера выручки в столбце D.

Доля каждой строки в общем параметре

Определяем долю каждой номенклатуры в выручке:

• добавляем графу Доля (Е). Формат ячеек процентный;
• в строку 2 для товара 6 вводим формулу: выручка товара 6 / выручка итого;
• протягиваем формулу вниз по всем товарам.

Добавляем графу F и рассчитываем Долю накопительным итогом: складываем текущее значение со всеми предыдущими.

Символ & предупреждает Excel, что формулу нельзя двигать:

• & перед буквой — по столбцам;
• & перед цифрой — по строкам.

Перед тем как создавать ABC-таблицу проверьте долю каждого товара в общем значении (выручки, запасах, себестоимости и пр.). Проводить ABC аналитику бессмысленно, если объект распределяется примерно в равных долях. Каждый показатель вносит одинаковый вклад в результат.

Определяем группу

Создаем графу Группа. Каждому товару присваиваем значения А, В, С в зависимости от доли в выручке.

Руководство утвердило матрицу:

Группа	Диапазон
A	до 70%
B	70-90%
C	90-100%

В ячейке G2 прописываем формулу =ЕСЛИ(F2<=70%;"A";ЕСЛИ(F2>=90%;"C";"В")). Протягиваем формулу вниз по всем товарам.

В примере для наглядности проценты заданы цифрами. 

В рабочем файле Excel вместо процентов ссылки на ячейки со значениями матрицы. При изменении параметров матрицы формула будет автоматически пересчитываться по всем товарам.

В столбце G каждой номенклатурной группе присвоен код А, В, С.

В группу А попали товары, которые приносят основную прибыль.

В группу В — продукция компании, на которую нерегулярный спрос.

Группа С — товары, которые зарабатывают только 10% от выручки.

XYZ-анализ в Excel: оценка динамики продаж

XYZ исследование позволит увидеть изменения спроса на продукцию компании.

Выгружаем данные из учётной системы

Создаем таблицу с количеством продаж за 2020 год по каждой товарной группе по каждому кварталу.

Рассчитываем коэффициент вариации

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

Создаем графу Средние продажи. В строку 3 вводим формулу =СРЗНАЧ(B3:E3) и копируем ее для всех товарных позиций.

Создаем графу Стандартное отклонение. Стандартное отклонение / Средние продажи.

В строку 3 вводим формулу =СТАНДОТКЛОН(B3:E3) и копируем ее для всех товарных позиций.

Создаем графу Вариация, %. Вводим формулу:

Столбец Стандартное отклонение / Столбец Средние продажи

Присваиваем значения XZY и соединяем с ABC

Руководство утвердило матрицу XYZ аналитики:

Группа	Диапазон
X — постоянный спрос	до 15%
Y — изменчивый спрос, сезональность	от 15% до 50%
Z — случайный спрос	больше 50%

Ранжируем полученные результаты с помощью функции Excel «ЕСЛИ».

В ячейку J3 вводим формулу: =ЕСЛИ(I3<=15%;"X";ЕСЛИ(I3>=50%;"Z";"Y")). Копируем формулу по всем товарным срокам.

• 
• 

Создаем графу Группа по методу ABC. Подтягиваем код группы из таблицы ABC анализа с помощью формулы: =ВПР(A3;ABC!$A$1:$G$12;7;0)

Как настроить формулу ВПР:

Задача функции: по коду товара в исходной таблице найти значение А, В или С и перенести его отчётную таблицу XYZ.

А3 — параметр, по которому ищем значение, например «Товар 6».

ABC!$A$1:$G$12 — ссылка на диапазон исходной таблицы. В ней строго в первом столбце должен быть параметр, по которому ищем значение «Товар 6».

7 — порядковый номер столбца, в котором в исходной находятся значения (коды А, В, С)

0 — значение ЛОЖЬ. Для Ecxel признак того, что искомый результат должен соответствовать всем 3-м предыдущим условиям.

• 
• 
• 

По каждому товару получаем двойную кодировку ABC и XYZ аналитики.

Для наглядности можно скрепить лва кода по каждому товару.

В столбец L для каждой строки вводим формулу =K&J.

Новости

Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).

В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.

ABC-анализ в Excel

Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.

Благодаря анализу ABC пользователь сможет:

• выделить позиции, имеющие наибольший «вес» в суммарном результате;
• анализировать группы позиций вместо огромного списка;
• работать по одному алгоритму с позициями одной группы.

Значения в перечне после применения метода ABC распределяются в три группы:

1. А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
2. В – средние по важности (30% – 15%).
3. С – наименее важные (50% – 5%).

Указанные значения не являются обязательными. Методы определения границ АВС-групп будут отличаться при анализе различных показателей. Но если выявляются значительные отклонения, стоит задуматься: что не так.

Условия для применения ABC-анализа:

• анализируемые объекты имеют числовую характеристику;
• список для анализа состоит из однородных позиций (нельзя сопоставлять стиральные машины и лампочки, эти товары занимают очень разные ценовые диапазоны);
• выбраны максимально объективные значения (ранжировать параметры по месячной выручке правильнее, чем по дневной).

Для каких значений можно применять методику АВС-анализа:

• товарный ассортимент (анализируем прибыль),
• клиентская база (анализируем объем заказов),
• база поставщиков (анализируем объем поставок),
• дебиторов (анализируем сумму задолженности).

Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.

Общая схема проведения:

1. Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
2. Выполнить сортировку параметров по убыванию.
3. Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
4. Найти долю каждого параметра в общей сумме.
5. Посчитать долю нарастающим итогом для каждого значения списка.
6. Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
7. Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
8. Для С – все, что ниже.
9. Посчитать число значений для каждой категории и общее количество позиций в перечне.
10. Найти доли каждой категории в общем количестве.



АВС-анализ товарного ассортимента в Excel

Составим учебную таблицу с 2 столбцами и 15 строками. Внесем наименования условных товаров и данные о продажах за год (в денежном выражении). Необходимо ранжировать ассортимент по доходу (какие товары дают больше прибыли).

1. Отсортируем данные в таблице. Выделяем весь диапазон (кроме шапки) и нажимаем «Сортировка» на вкладке «Данные». В открывшемся диалоговом окне в поле «Сортировать по» выбираем «Доход». В поле «Порядок» – «По убыванию».



2. Добавляем в таблицу итоговую строку. Нам нужно найти общую сумму значений в столбце «Доход».



3. Рассчитаем долю каждого элемента в общей сумме. Создаем третий столбец «Доля» и назначаем для его ячеек процентный формат. Вводим в первую ячейку формулу: =B2/$B$17 (ссылку на «сумму» обязательно делаем абсолютной). «Протягиваем» до последней ячейки столбца.



4. Посчитаем долю нарастающим итогом. Добавим в таблицу 4 столбец «Накопленная доля». Для первой позиции она будет равна индивидуальной доле. Для второй позиции – индивидуальная доля + доля нарастающим итогом для предыдущей позиции. Вводим во вторую ячейку формулу: =C3+D2. «Протягиваем» до конца столбца. Для последних позиций должно быть 100%.



5. Присваиваем позициям ту или иную группу. До 80% – в группу А. До 95% – В. Остальное – С.



6. Чтобы было удобно пользоваться результатами анализа, проставляем напротив каждой позиции соответствующие буквы.

Вот мы и закончили АВС-анализ с помощью средств Excel. Дальнейшие действия пользователя – применение полученных данных на практике.

XYZ-анализ: пример расчета в Excel

Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.

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

Коэффициент вариации – относительный показатель, не имеющий конкретных единиц измерения. Достаточно информативный. Даже сам по себе. НО! Тенденция, сезонность в динамике значительно увеличивают коэффициент вариации. В результате понижается показатель прогнозируемости. Ошибка может повлечь неправильные решения. Это огромный минус XYZ-метода. Тем не менее…

Возможные объекты для анализа: объем продаж, число поставщиков, выручка и т.п. Чаще всего метод применяется для определения товаров, на которые есть устойчивый спрос.

Алгоритм XYZ-анализа:

1. Расчет коэффициента вариации уровня спроса для каждой товарной категории. Аналитик оценивает процентное отклонение объема продаж от среднего значения.
2. Сортировка товарного ассортимента по коэффициенту вариации.
3. Классификация позиций по трем группам – X, Y или Z.

Критерии для классификации и характеристика групп:

1. «Х» – 0-10% (коэффициент вариации) – товары с самым устойчивым спросом.
2. «Y» – 10-25% – товары с изменчивым объемом продаж.
3. «Z» – от 25% – товары, имеющие случайный спрос.

Составим учебную таблицу для проведения XYZ-анализа.

1. Рассчитаем коэффициент вариации по каждой товарной группе. Формула расчета изменчивости объема продаж: =СТАНДОТКЛОНП(B3:H3)/СРЗНАЧ(B3:H3).



2. Классифицируем значения – определим товары в группы «X», «Y» или «Z». Воспользуемся встроенной функцией «ЕСЛИ»: =ЕСЛИ(I3<=10%;”X”;ЕСЛИ(I3<=25%;”Y”;”Z”)).

В группу «Х» попали товары, которые имеют самый устойчивый спрос. Среднемесячный объем продаж отклоняется всего на 7% (товар1) и 9% (товар8). Если есть запасы этих позиций на складе, компании следует выложить продукцию на прилавок.

Скачать примеры ABC и XYZ анализов

Запасы товаров из группы «Z» можно сократить. Или вообще перейти по этим наименованиям на предварительный заказ.