ABC-анализ в Excel: пошаговый расчёт через сводные таблицы

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

Короткий ответ

• Соберите данные о продажах за 3-6 месяцев в единый список.
• Создайте сводную таблицу для суммирования итогов по каждому артикулу.
• Отсортируйте товары по убыванию выручки.
• Вычислите долю каждого товара в общем обороте и накопительный итог.
• Присвойте категории: A (лидеры), B (средние), C (аутсайдеры).

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

Анализ запасов - это не просто табличка с буквами, а сигнал к действию: что закупать срочно, а что выводить из ассортимента.

Объяснение на пальцах

Термины простыми словами:

• ABC анализ - рейтинг товаров, где вверху самые ценные, а внизу - кандидаты на вылет.
• Сводная таблица - инструмент Excel, который превращает тысячи строк продаж в компактный отчет.
• Накопительный итог - сумма процентов текущего товара и всех предыдущих сверху вниз.
• SKU - уникальный код товара или артикул.

Представьте свой гардероб.

• Группа А - это джинсы и любимая футболка. Вы носите их 80% времени, они всегда должны быть чистыми и под рукой.
• Группа B - рубашки для офиса и выходные платья. Нужны регулярно, но не каждый день.
• Группа C - лыжный костюм или старый свитер для дачи. Занимают 50% шкафа, а надеваете вы их раз в год.

Если вы будете тратить все время на стирку лыжного костюма, вам нечего будет надеть на работу. Так и в бизнесе: нельзя тратить деньги на группу C, пока не обеспечена группа А.

Что нужно знать перед расчетом

Чтобы расчет дал честную картину, проверьте исходные данные:

• Период анализа. Берите минимум 3, а лучше 6 месяцев. Один месяц может быть не показательным из-за сезонности или случайных крупных сделок.
• Критерий оценки. Чаще всего считают по выручке или валовой прибыли. Анализ по количеству штук нужен реже, например, для оценки нагрузки на склад.
• Качество данных. В столбце с названиями не должно быть дублей. "Стул синий" и "Стул синий (акция)" - это разные строки для программы, но один товар для вас.
• Нет пустых ячеек. Инструменты анализа могут работать некорректно, если в заголовках или данных есть разрывы.
• Единый список. Нельзя смешивать услуги и товары. Доставка всегда будет в топе по марже, но она не занимает место на полке. Уберите услуги из выборки.

Как посчитать: пошаговая схема

Разберем, как сделать классификацию стандартными средствами, без программирования.

Шаг 1: Подготовка источника

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

• Артикул (или Название).
• Сумма продаж (выручка).

Убедитесь, что числа сохранены именно как числа, а не как текст.

Шаг 2: Создание сводной таблицы

Выделите всю таблицу с данными. В меню выберите "Вставка" - "Сводная таблица". В конструкторе перетащите поля:

• Артикул/Название - в область "Строки".
• Сумма продаж - в область "Значения".

Теперь у вас есть сжатый список: каждый товар упоминается один раз, а напротив стоит общая сумма продаж.

Шаг 3: Сортировка данных

Это критически важный этап. Кликните правой кнопкой мыши по любой сумме в новой таблице. Выберите "Сортировка" - "По убыванию". Самые прибыльные товары должны оказаться в первой строке, товары с нулевыми продажами - в самом низу.

Шаг 4: Расчет доли

Лучше скопировать данные на новый лист как значения, чтобы формулы не сбивались при обновлении. Добавьте столбец "Доля". Формула простая: разделите выручку конкретного товара на общую сумму выручки всех товаров. Переведите формат ячейки в проценты. В сумме этот столбец должен давать строго 100%.

Шаг 5: Накопительный итог

Добавьте следующий столбец "Накопительная доля".

• Для первой строки накопительная доля равна просто доле этого товара.
• Для второй строки: доля второй строки плюс накопительная доля первой.
• Для третьей строки: доля третьей строки плюс накопительная доля второй.

Протяните формулу до самого низа. У последнего товара накопительный итог должен быть 100%.

Шаг 6: Присвоение групп

Теперь проставляем буквы. Классические границы групп такие:

• Группа А: накопительный итог от 0% до 80%.
• Группа B: накопительный итог от 80% до 95%.
• Группа C: накопительный итог от 95% до 100%.

Это можно сделать формулой с условием "ЕСЛИ" или проставить вручную, ориентируясь на столбец накопительного итога.

Частые ошибки

Даже опытные менеджеры иногда спотыкаются на простых вещах. Проверьте себя:

• Слишком короткий период. Анализ по данным за неделю покажет не лидеров, а тех, кому просто повезло быть проданными сегодня.
• Игнорирование дефицита. Товар попал в группу C, потому что его мало покупали. Но возможно, его мало покупали, потому что его не было на складе. Такой товар нельзя выводить, его нужно вернуть в наличие.
• Грязные данные. Дубликаты артикулов размывают статистику. Один и тот же товар, записанный по-разному, попадет в группу C двумя строчками, хотя вместе мог быть в B.
• Слепое следование правилу 80/20. Границы 80% и 95% условны. В вашем бизнесе группа А может давать 50% выручки, и это нормально. Смотрите на реальный разрыв в цифрах.
• Отсутствие действий. Сделать таблицу и сохранить файл - это не работа. Результат анализа - это изменение заявок поставщику. Группу А заказываем с запасом, группу C сокращаем.
• Смешивание новинок и старья. Новинка, пришедшая неделю назад, всегда будет в аутсайдерах, потому что не успела накопить статистику. Не удаляйте новинки на основе этого метода.