ABC-анализ ассортимента: 6 шагов в Google Таблицах против 4 строк кода

Каждый, кто хоть раз работал с товарным ассортиментом, слышал про правило Парето (80/20). Оно гласит: 20% товаров приносят 80% выручки. И это не просто теория — в нашем небольшом магазине из 20 позиций 12 товаров (60% ассортимента) действительно приносят почти 78% денег.

ABC-анализ — это способ разложить весь ваш ассортимент по полочкам в зависимости от вклада в общую выручку:

Главная цель ABC-анализа — управление запасами и оборотным капиталом. Товары группы А мы закупаем бесперебойно, потому что у них высокая оборачиваемость (деньги вкладываются и возвращаются быстро, принося прибыль). А вот с товарами группы С ситуация интереснее.

Почему «С» — это не всегда «выбросить с полки»? Если бы речь шла о складе стройматериалов, товары группы С можно было бы просто убрать. Но в продуктовом магазине (особенно формата «у дома») товары группы «С» часто играют роль товаров-маячков для хорошего ассортимента.

Покупатель редко приходит в магазин только за солью или спичками. Но если их нет в наличии, он делает вывод: «Да тут даже базовых продуктов нет, схожу-ка я в супермаркет» — и уходит, оставляя там деньги за курицу (группа А) и сыр (группа А). Присутствие дешевых товаров создает ощущение полноценного магазина.

Мы обращаем на них внимание после самых важных, но всё равно обращаем. Разница в подходе: товары группы С мы закупаем минимальными партиями (30-50 штук на полку). Почему? Потому что у них долгая оборачиваемость. Если закупить «Соль» вагоном, деньги «заморозятся» на складе на несколько месяцев. А нам нужно, чтобы оборотный капитал работал. Мы держим их ради ассортимента, но не позволяем им замораживать наши финансы.

Сегодня я покажу два способа провести такой анализ на примере продуктового магазина (20 позиций): как это делает большинство в Google Таблицах, и как эту же задачу решить в ArrayVator.

Шаг 1. Открываем исходную таблицу с данными: товар, категория, количество и цена. Столбец «Итого» считаем формулой =C2*D2 (Количество × Цена).

Шаг 2. Сортируем таблицу по столбцу «Итого» по убыванию (от самых прибыльных к самым дешевым).

Шаг 3. Создаем общую сумму всех продаж: =SUM(E2:E21).

Шаг 4. Считаем долю каждого товара в общей выручке: =E2/SUM($E$2:$E$21).

Шаг 5. Считаем накопительный процент: =SUM($F$2:F2).

Шаг 6. В последний столбец записываем логику ABC: =ЕСЛИ(G2<=0,8;"A";ЕСЛИ(G2<=0,95;"B";"C")).

Итог: Мы получили отличный результат.

Способ 2. Альтернативный: ArrayVator

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

Смотрите: вся логика с сортировкой и накопительным процентом уже встроена в функцию abc(). Нам не нужно писать формулу ЕСЛИ — язык сам разложит товары по группам. А чтобы отфильтровать товары-маячки, достаточно одной команды:

Шаг 1. Откроем файл через графическое окно и запишем данные в переменную m:

m = OpenExcelShow()

Шаг 2. Выведем таблицу в консоль, чтобы убедиться, что мы открыли то, что нужно:

print(m)

Шаг 3. Пишем функцию ABC и говорим ей, что нужно проанализировать столбец «Итого»:

m = abc(m[:, "Итого"], %)

Шаг 4. Выведем готовую таблицу в графическом режиме (show):

printshow(m)

Все! Скрипт работает.
И самое главное — ArrayVator позволяет упаковать этот скрипт в обычный исполняемый файл.exe (меню Инструменты → Экспорт в EXE). Теперь, чтобы обновить отчет за следующий месяц, вам не нужно открывать редактор и вспоминать код. Вы просто запускаете готовую программку, выбираете новый файл, и она моментально выдает результат.

Если завтра вы добавите 2000 новых товаров — вы просто запустите этот же скрипт, и он посчитает всё за секунду. Никаких протягиваний мышкой.

ABC-анализ не обязательно делать только по выручке. Иногда полезнее посмотреть на количество проданных штук.

В ArrayVator для этого достаточно поменять один столбец в коде. Вместо «Итого» передаем «Кол-во»:

m = abc(m[:, "Кол-во (шт)"], %)

Зачем это нужно?

Выручка может вводить в заблуждение. Дорогой сыр (200 руб.) может давать много денег, но покупают его редко. А дешевый хлеб (20 руб.) могут брать каждый день. Сравнив ABC по деньгам и ABC по штукам, вы найдете те самые «товары-маячки», которые имеют высокую оборачиваемость, но низкую маржинальность. И поймете, что они жизненно необходимы для трафика.

Скачать ArrayVator