Как сделать ABC-анализ в Excel и зачем он нужен

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

ABC-анализ — это метод классификации, при котором объекты делят на три группы в зависимости от того, как они влияют на результат.

В основе метода ABC-анализа лежит принцип Парето: 20% усилий приносят 80% результата. Задача анализа — найти эти 20% и сфокусировать усилия на них.

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

Метод ABC-анализа классифицирует объекты так:

• Группа A — самые ценные позиции. Они в меньшинстве, но приносят большую часть результата.
• Группа B — объекты средней значимости. Они составляют примерно 30% и дают около 15% эффекта.
• Группа C — наименее значимые позиции. Это более половины от общего числа позиций, но они приносят минимальные результаты.

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

Зачем нужен ABC-анализ

Метод подходит для любой области, в которой компании нужно расставить приоритеты.

• Управление ассортиментом и складом. Анализ показывает, какие товары компании стоит постоянно держать в запасе, а какие заказывать реже. Например, объекты группы A требуют частых поставок: если они заканчиваются, бизнес теряет ощутимую часть выручки и лояльность клиентов. Товары группы C занимают место, но почти не влияют на продажи. Можно заказывать их реже и направлять освободившиеся ресурсы на первую группу. Подробнее о том, как анализировать продажи товаров и услуг, рассказывали в отдельном материале.
• Обслуживание клиентов. Анализ позволяет понять, кто приносит компании больше денег. Менеджеры уделяют покупателям из группы A максимум внимания: согласовывают персональные условия и предоставляют лучший уровень сервиса. А покупателей группы C обслуживают по общей схеме: называют стандартные цены и типовые сроки, присылают шаблонные уведомления о статусе заказа.
• Подбор поставщиков. Тех, кто попал в группу A, компания воспринимает как стратегических партнеров и заключает с ними долгосрочные договоры. Поставщиков группы C бизнес может легко заменить без серьезных последствий.
• Оптимизация рекламного бюджета. Компания делит каналы на группы по количеству заявок, стоимости клика или выручке и направляет большую долю бюджета на каналы группы A. По каналам, которые оказались в группе C, можно пересмотреть стратегию и либо доработать подход, либо отключить канал и сэкономить деньги без потери результата.

Как провести ABC-анализ в Excel: пошаговый план с примером

Для ABC-анализа в экселе достаточно стандартной программы Microsoft Excel, которой владеют сотрудники большинства компаний.

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

Шаг 1. Подготовьте данные

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

• Удалите дубли и новинки, по которым еще не накопилась статистика.
• Уберите пробелы и другие лишние символы из числовых значений.
• Расположите позиции от наибольшего значения к наименьшему. Выделите столбец с показателем выручки, откройте вкладку «Данные» и выберите «Сортировку по убыванию». Самые весомые позиции окажутся вверху списка.

Шаг 2. Рассчитайте долю каждой позиции

Создайте третий столбец. В нем посчитайте, какой процент от общей суммы дает позиция. Формула ABC-анализа в Excel для первой строки выглядит так:

Знак $ фиксирует диапазон суммы и позволяет протянуть формулу на все строки так, чтобы она считалась корректно. Установите процентный формат ячеек на главной в меню выше.

Шаг 3. Рассчитайте накопленный итог

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

Для второй строки значение совпадает с долей. Для третьей строки и далее используйте формулу:

Протяните формулу вниз. Последний товар в списке должен показать 100% — это проверка корректности расчета.

Шаг 4. Присвойте группы A, B, C

Границы групп исходят из пропорций, которые мы разобрали выше, в разделе о сути метода:

• группа A — позиции, которые в сумме дают до 80% результата;
• группа B — от 80% до 95%;
• группа C — все остальное.

В формуле эти пороги записаны как 0,8 и 0,95. Используйте формулу ЕСЛИ:

Вставьте ее во вторую строку и протяните на остальные. Для наглядности закрасьте группы цветом: A — зеленым, B — желтым, C — красным. Если распределение отличается от типичного, компания может сдвинуть границы под специфику своего бизнеса.

Шаг 5. Сделайте выводы

Проанализируем таблицу с примером ABC-анализа в Excel для нашего онлайн-магазина бытовой техники. Она помогает прийти к конкретным решениям:

• Группа A — телевизоры, холодильники, стиральные и посудомоечные машины. Эти четыре позиции приносят три четверти выручки, поэтому компания вкладывает в них максимум ресурсов: поддерживает постоянный запас на складе, запускает рекламу именно на эти товары, размещает на главных позициях в каталоге и на витрине.
• Группа B — тостеры, микроволновки, блендеры и чайники. Эти позиции стабильно добирают пятую часть оборота. Компания стимулирует их продажи точечно: запускает акции, предлагает скидку при покупке вместе с товаром из группы A или добавляет их в сезонные подборки. Цель — подтянуть часть этих товаров в группу A к следующему кварталу.
• Группа C — утюги и фены. Выручка минимальная, но выводить их из ассортимента — плохая идея: покупатель, который не нашел нужный товар, уйдет к конкуренту. Компания сокращает затраты на эту группу: закупает реже, не выделяет отдельный рекламный бюджет, не тратит время менеджеров. При этом помогает им продаваться за счет группы A — например, предлагает фен в подарок к стиральной машине или добавляет утюг в комплект со скидкой.

ABC-анализ показывает, на что делать ставку. Следующий шаг — выстроить работу с данными внутри одной системы: от сбора заявок до отчетов по продажам. В Битрикс24 CRM есть BI-конструктор, который объединяет в удобном пространстве разные данные и помогает действовать на основе цифр, а не интуиции.

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

У ABC-анализа есть как сильные стороны, так и ограничения.

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

• Расчет ABC-анализа в Excel занимает 20-30 минут. Его можно выполнить с помощью таблицы с данными о продажах и четырех формул.
• Алгоритм работает для любого направления. Один и тот же порядок действий подходит для товаров, покупателей, поставщиков, рекламных каналов и задач команды. Компания меняет только критерий — выручку, прибыль, количество заказов — и получает готовую картину приоритетов для каждого направления. SWOT-анализ и план-фактный анализ дополняют ABC-подход: первый показывает сильные и слабые стороны бизнеса, второй — отклонения реальных показателей от плановых.
• Цифры заменяют догадки. Руководитель строит выводы на реальных числах и видит четкую иерархию — какие товары, покупатели, каналы и другие сущности дают основную выручку, а какие расходуют ресурсы компании, но приносят крайне мало.

Недостатки

• Результаты быстро устаревают. ABC-анализ опирается на данные за конкретный период и не учитывает сезонность и рыночные изменения. Допустим, зимние куртки попадают в группу C по итогам летнего квартала. Компания сокращает по ним закупку, а в сентябре спрос резко вырастает. Товара на складе нет, и покупатели идут к конкурентам. Чтобы учесть стабильность спроса, компании дополняют ABC-анализ XYZ-анализом — он показывает график спроса за год и позволяет прогнозировать колебания.
• Один критерий не дает полной картины. Компания проводит ABC-анализ по выручке, видит, что часть товаров попала в группу C и делает вывод, что можно перестать их закупать. Но клиенты могут приходить в компанию именно за этими товарами, потому что у конкурентов их нет. И однажды они могут купить товар и из группы А. Чтобы не терять лояльную аудиторию, нужно проводить анализ по нескольким параметрам и сравнивать результаты.
• Таблица не показывает связи между позициями. Например, ABC-анализ показывает, что расходники приносят мало выручки и формально попадают в группу C. Но они идут в комплекте с дорогой техникой из группы A — например, фильтры к кофемашинам, картриджи к принтерам или чехлы к смартфонам. Если ориентироваться только на таблицу и сократить закупку расходников, покупатели основного товара начнут искать комплектующие у конкурентов — и в следующий раз купят там и сам товар.
• Данные в Excel нужно собирать вручную. Для этого надо каждый раз выгружать свежие цифры из учетной системы, заново сортировать строки, пересчитывать формулы и сверять итоги. На десяти позициях это занимает полчаса, на тысяче — больше. Если компания проводит анализ регулярно, ручной сбор данных становится ощутимым узким местом.

Как автоматизировать ABC-анализ

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

CRM-системы и платформы бизнес-аналитики автоматизируют этот процесс. Они собирают данные по продажам, сделкам и покупателям в режиме реального времени и строят отчеты без ручных выгрузок.

Например, в Битрикс24 CRM все данные по сделкам и покупателям уже внутри системы — руководителю не нужно ничего выгружать. Он открывает раздел «CRM-аналитика», фильтрует сделки по сумме и сразу видит, какие покупатели приносят основную выручку, а какие — единичные мелкие заказы. Это готовая основа для распределения по группам A, B и C.

BI-аналитика Битрикс24 позволяет строить отчеты по нужным показателям: видеть динамику продаж по категориям товаров, отслеживать конверсию на каждом этапе воронки и сравнивать результаты за разные периоды. Руководитель видит, как покупатели перемещаются между группами A, B и C из квартала в квартал, и вовремя реагирует на изменения. А еще можно подключить сквозную аналитику — она покажет, какие рекламные каналы приносят покупателей из группы A, а какие тратят бюджет на группу C.

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

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

Часто задаваемые вопросы