ABC-анализ товаров в Excel — описание и предпросмотр, скачать — по подписке
Делит товары на группы A, B и C по вкладу в выручку: доля, накопленная доля, место в рейтинге и итоги по группам. Формулы, выпадающие списки и параметры уже настроены; файл проверен пересчётом.
Формулник сам поймёт, что нужно: формула, таблица, документ или правка файла — и проверит результат.
.xlsx для Excel 2016–365, LibreOffice, Р7-Офис; открывается в Яндекс и Google Таблицах.
Скачать .xlsx — по подпискеГлавное в книге
- Лист «Товары»: 6 столбцов; считаются сами — «Доля», «Накопленная доля», «Место», «Группа»
- Строка «Итого» по «Выручка»
- Подсветка: «Группа», «Группа» — заметно, где проблема
- Лист «Группы»: 4 столбцов; считаются сами — «Товаров», «Выручка», «Доля выручки»
- Строка «Итого» по «Товаров», «Выручка»
- Лист «Параметры»: 2 параметра — ставки и настройки меняются в одном месте, формулы берут их оттуда
Что внутри
Лист «Товары»
| Товар | Выручка | Доля | Накопленная доля | Место | Группа |
|---|---|---|---|---|---|
| Бумага А4 | 600000 | формула | формула | формула | формула |
| Картриджи | 200000 | формула | формула | формула | формула |
| Папки и файлы | 150000 | формула | формула | формула | формула |
| Скрепки | 50000 | формула | формула | формула | формула |
Лист «Группы»
| Группа | Товаров | Выручка | Доля выручки |
|---|---|---|---|
| A | формула | формула | формула |
| B | формула | формула | формула |
| C | формула | формула | формула |
Лист «Параметры»
- Группа A — товары, дающие первые, % выручки: 80
- Группа B — до этой накопленной доли, %: 95
Как пользоваться
- Вставьте список товаров и выручку каждого за один период (месяц, квартал, год) — сортировать не нужно.
- A — главные товары (первые 80 % выручки), B — следующие 15 %, C — остальные; границы — на листе «Параметры».
- Так же можно анализировать прибыль или количество — просто вставьте эти числа в столбец «Выручка».
- Новые строки добавляйте прямо под таблицей — формулы и проверки продолжатся сами.
Похожие шаблоны
ABC-анализ в Excel без сортировки: группы A, B, C по накопленной доле выручки
ABC-анализ делит товары по вкладу в выручку: группа A — товары, которые дают первые 80 % выручки, B — следующие 15 %, C — оставшиеся 5 %. Шаблон считает долю каждого товара, накопленную долю, место в рейтинге и группу прямо формулами, поэтому список не нужно сортировать — достаточно вставить названия и выручку за один период. Лист «Группы» показывает, сколько товаров в каждой группе и какую долю выручки они дают.
По шагам
- Выгрузите из учётной программы продажи по товарам за один период — квартал или год. Месяц для ABC обычно слишком короткий: сезонные товары попадут не в ту группу.
- Вставьте на лист «Товары» два столбца: название и выручку. Порядок строк не важен.
- Проверьте границы на листе «Параметры»: 80 и 95 %. Для узкого ассортимента иногда берут 70/90.
- Посмотрите на «Группу». В образце бумага А4 (600 000 ₽) и картриджи (200 000 ₽) — A, папки — B, скрепки — C.
- Откройте лист «Группы»: 2 товара из 4 дают 80 % выручки. В реальном ассортименте обычно 15–25 % позиций дают 80 % денег.
- Сделайте выводы по группам: A — держать в наличии всегда и следить за ценой, B — обычный контроль, C — сокращать запас, пересматривать или выводить.
Как книга обходится без сортировки
Накопленная доля товара — это выручка всех товаров, которые продаются не хуже него, делённая на общую выручку. СУММЕСЛИМН с условием «больше или равно» складывает такие товары в любом порядке строк. Товар идёт в A, если до него накоплено меньше 80 %: так позиция, которая пересекает границу, остаётся в A.
=СУММЕСЛИМН([Выручка];[Выручка];">="&[@Выручка])/СУММ([Выручка])ABC по прибыли, а не по выручке
Дорогой товар с маленькой наценкой может быть в A по выручке и в C по прибыли. Вставьте в столбец «Выручка» валовую прибыль по товарам — формулы работать не перестанут. Сравнение двух анализов показывает товары, которые крутят оборот, но почти ничего не зарабатывают.
ABC + XYZ
XYZ делит товары по стабильности спроса: коэффициент вариации продаж по месяцам до 10 % — X, до 25 % — Y, выше — Z. Добавьте рядом столбцы с продажами по месяцам и посчитайте =СТАНДОТКЛОН.Г(C2:N2)/СРЗНАЧ(C2:N2). Пара «AX» — главные и предсказуемые товары, «CZ» — первые кандидаты на вывод.
Образец: выручка 1 000 000 ₽
| Товар | Доля | Накопленная | Место | Группа |
|---|---|---|---|---|
| Бумага А4 | 60 % | 60 % | 1 | A |
| Картриджи | 20 % | 80 % | 2 | A |
| Папки и файлы | 15 % | 95 % | 3 | B |
| Скрепки | 5 % | 100 % | 4 | C |
Частые ошибки
- Выручка товаров за разные периоды — новинка, которая продаётся месяц, окажется в C рядом с годовыми продажами старых товаров. Берите один период или анализируйте новинки отдельно.
- В список попали возвраты и услуги доставки — строки с отрицательной или служебной выручкой искажают доли. Перед вставкой уберите доставку, скидки и возвраты одной позицией.
- Товары с одинаковой выручкой — у них будет одна накопленная доля и одно место, и оба попадут в одну группу. Это нормально для формул без сортировки, просто учитывайте при чтении рейтинга.
Вопросы и ответы
Как сделать ABC-анализ в Excel?
Посчитайте долю каждого товара в выручке, затем накопленную долю по убыванию и присвойте группы по границам 80 и 95 %. В шаблоне накопленная доля считается СУММЕСЛИМН, поэтому сортировка не нужна.
Какие границы групп в ABC-анализе?
Классические — 80/15/5 % выручки по правилу Парето. Границы можно сдвинуть, например 70/20/10, если ассортимент маленький.
Как часто делать ABC-анализ?
Раз в квартал или полгода — по продажам за последние 12 месяцев. Чаще результаты скачут из-за сезонности.
Что делать с товарами группы C?
Сократить запас, перевести под заказ или вывести из ассортимента, если они не нужны для продажи товаров A. Сопутствующие товары иногда держат и в C.
Можно ли сделать ABC-анализ клиентов?
Да: вставьте вместо товаров клиентов с суммой покупок. В Формулнике можно собрать ABC и XYZ сразу по выгрузке продаж.