ABC-анализ товаров Ozon: как рассчитать в Excel

ABC-анализ Ozon в Excel: ячейки и формулы

Ниже — воспроизводимый учебный расчёт, а не данные действующего магазина. Он использует положительную прибыль по SKU за один и тот же период. Нулевые и убыточные позиции сначала вынесите на отдельный лист — их разберём ниже.

1. Подготовьте исходные данные

Одна строка должна соответствовать одному SKU. Объедините повторяющиеся строки артикула и посчитайте результат с одинаковым набором расходов: выручка за вычетом возвратов, себестоимость проданного, комиссии, логистика, реклама и остальные затраты выбранного периода. Если внешние расходы или налоги не учтены, подпишите показатель «прибыль до внешних расходов и налогов», а не «чистая прибыль».

В ячейки A1:E1 внесите заголовки: SKU, Прибыль, Доля, Накопительная доля, Группа. В A2:A6 запишите SKU-1…SKU-5, в B2:B6 — числа 50000, 25000, 15000, 7000, 3000. Знак ₽ задайте форматированием ячеек, не вводите его как текст.

2. Отсортируйте и протяните формулы

Выделите всю таблицу и отсортируйте по столбцу B от большего к меньшему. При равной прибыли используйте SKU как второй ключ сортировки. Это сохранит воспроизводимый порядок. Затем введите три формулы и протяните их до строки 6:

C2 — доля товара в положительной прибыли

=B2/СУММ($B$2:$B$6)

D2 — доля всех товаров от первой строки до текущей

=СУММ($B$2:B2)/СУММ($B$2:$B$6)

E2 — группа по накопительной доле

=ЕСЛИ(ИЛИ(СТРОКА()=2;D2<=80%);"A";ЕСЛИ(D2<=95%;"B";"C"))

Для C и D выберите процентный формат. Если товаров больше пяти, замените $B$6 на последнюю строку списка положительных значений. Знаки $ фиксируют границы общей суммы при копировании формулы.

3. Проверьте результат и правило границ

Сумма прибыли в примере — 100 000 ₽, сумма долей — 100%. Накопительные доли: 50%, 75%, 90%, 97%, 100%. Группы: A, A, B, C, C.

Здесь группу определяет накопительная доля после добавления строки: до 80% включительно — A, до 95% включительно — B, выше — C. Есть явное исключение: первый, самый прибыльный товар всегда A, даже если один даёт более 80%. Например, для 90 000, 5 000 и 5 000 ₽ результат будет A, B, C.

Поэтому группа A не обязана давать ровно 80%: в исходном примере она даёт 75%. Если у двух SKU одинаковая прибыль на границе, формула может отнести их к разным группам. Буква не означает различия в их экономике. Можно относить равные значения в одну группу, но тогда нужно отдельно зафиксировать это правило и пересчитать границы; приведённая формула так не делает.

Как учитывать убытки, нулевую прибыль и новые товары

Отрицательные значения ломают обычный расчёт накопительных долей. Если к пяти прибыльным товарам добавить SKU с убытком 10 000 ₽, общий результат станет 90 000 ₽. Деление положительных 100 000 ₽ на 90 000 ₽ даст 111,1% — это уже не подходящая база для порогов 80/95.

• Положительная прибыль: ABC рассчитывается отдельно, в примере от 100 000 ₽.
• Убыток: отдельный список с суммой −10 000 ₽ и проверкой причин; не прячьте его в группе C.
• Ноль: отдельная отметка «нулевой результат». Проверьте, были ли продажи и все ли расходы загружены.
• Нет положительных значений: обычный ABC по прибыли не строится. Начните с причин убытка и полноты данных; не подменяйте деление на ноль нулевыми долями.

Итог кабинета при этом остаётся 100 000 − 10 000 = 90 000 ₽. Список убытков исключён только из базы ABC, но не из финансового результата.

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

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