ABC-аналіз товарів: як зробити в Excel на прикладі 10 товарів

Зміст
  1. Коли роблять ABC-аналіз
  2. Що таке ABC-аналіз і на чому він ґрунтується
  3. За чим рахувати: виручка, прибуток чи кількість
  4. Як зробити ABC-аналіз в Excel: 4 кроки і формули
  5. Приклад ABC-аналізу: 10 товарів за виручкою і за прибутком
  6. Що робити з групами A, B і C
  7. Де ABC-аналіз обманює
  8. Коли ABC-аналіз краще рахувати в програмі
  9. Часті запитання

ABC-аналіз — це поділ товарів на три групи за внеском у результат: група A — товари, що разом дають перші 80 % прибутку чи виручки, B — наступні 15 %, C — останні 5 %. Робиться за чотири кроки: відсортувати товари від більшого результату до меншого, порахувати частку кожного, додати частки наростаючим підсумком і за межами 80 і 95 % присвоїти групу.

Найчастіша помилка — рахувати ABC за виручкою і вирішувати за ним, які товари важливі. Дорогий товар із тонкою маржею буде першим за виручкою і далеко не першим за грошима, які лишаються магазину. Нижче — формули для Excel, приклад на десяти товарах, порахований двома способами, і що робити з кожною групою.

Коли роблять ABC-аналіз

Зазвичай товарів стало кілька сотень, гроші заморожені в складі, а відповісти, які позиції справді годують магазин, не виходить. Людина шукає «abc аналіз», потім «abc аналіз приклад», «як зробити abc аналіз в excel» і «як читати abc аналіз».

Ключове питання тут не «як порахувати» — це чотири формули, — а «за чим рахувати». Від цього вибору залежить, які товари опиняться в A, і помилка тут коштує більше, ніж помилка у формулі.

Що таке ABC-аналіз і на чому він ґрунтується

В основі ABC-аналізу — принцип Парето: невелика частина позицій дає більшу частину результату. Метод не стверджує, що це завжди рівно 20 і 80 %: він лише впорядковує товари і показує, де саме у вашому асортименті проходить межа.

Найпоширеніші межі — 80 і 95 % накопиченої частки:

  • Група A — товари, які разом дають до 80 % результату. Їх мало, і кожен важливий.
  • Група B — наступні, до 95 %. Середня важливість.
  • Група C — решта, останні 5 %. Їх зазвичай найбільше.
ABC-аналіз: 4 товари групи A дають 80 % прибутку, 3 товари групи B — 15 %, 3 товари групи C — 5 %

Межі умовні: їх обирає той, хто рахує, і головне — не міняти їх від звіту до звіту, інакше групи не порівняти. ABC так само застосовують до клієнтів (хто дає основну виручку) і до постачальників (у кого основні закупівлі) — формули ті самі.

За чим рахувати: виручка, прибуток чи кількість

Для рішень про асортимент рахуйте за прибутком: він показує, які товари заробляють. Виручка і кількість відповідають на інші питання.

КритерійНа яке питання відповідаєДе обманює
Прибуток (валовий)Які товари заробляють грошіПотрібна собівартість кожного товару; без неї розрахунок неможливий
ВиручкаЯкі товари дають оборот і завантажують склад і доставкуДорогий товар із малою маржею потрапляє в A
Кількість продажівЩо найчастіше пакують і відвантажуютьДешевий товар-«копійка» потрапляє в A

У прикладі нижче робот-пилосос дає 30 % виручки магазину і лише 6 % прибутку: за виручкою він найважливіший товар, за прибутком — середній.

Робот-пилосос: 30 % виручки, але лише 6 % прибутку магазину

Як зробити ABC-аналіз в Excel: 4 кроки і формули

Потрібна таблиця з двома колонками — товар і прибуток за період, — і чотири дії. Нехай товари в рядках 2–11, назва в колонці A, прибуток у колонці B.

  1. Відсортуйте від більшого до меншого. «Дані» → «Сортування» за колонкою B, за спаданням.
  2. Порахуйте частку кожного товару в колонці C і задайте їй відсотковий формат.
  3. Порахуйте накопичену частку в колонці D — частку товару плюс частки всіх вище.
  4. Присвойте групу в колонці E за межами 80 і 95 %.
C2: =B2/SUM($B$2:$B$11)
D2: =SUM($C$2:C2)
E2: =IF(ROUND(D2;4)<=80%;"A";IF(ROUND(D2;4)<=95%;"B";"C"))
Далі: протягніть формули до рядка 11. У D11 має вийти 100 % — це перевірка, що жоден товар не загубився.

Навіщо ROUND: частки — дроби, і їхня сума на межі може дати не рівно 80 %, а 80,0000000001 %. Тоді товар, що закриває рівно 80 %, піде в B. Округлення до чотирьох знаків прибирає цю похибку.

Розділювач аргументів у формулах — крапка з комою, як в Excel з українськими регіональними налаштуваннями. Якщо таблиця налаштована на англійську локаль, Excel і Google Таблиці чекають кому. Назви функцій однакові в обох програмах.

Приклад ABC-аналізу: 10 товарів за виручкою і за прибутком

Умовний приклад для арифметики, цифри не є даними ринку. Магазин товарів для дому, десять позицій, продажі за квартал. Загальна виручка — 200 000 грн, загальний прибуток — 50 000 грн. Таблиця відсортована за прибутком.

ABC-аналіз 10 товарів за квартал (умовні цифри)
ТоварВиручка, грнABC за виручкоюПрибуток, грнЧастка прибуткуНакопиченоABC за прибутком
Набір каструль36 000A14 00028 %28 %A
Набір ножів24 000A12 00024 %52 %A
Сковорода20 000A10 00020 %72 %A
Термос12 000B4 0008 %80 %A
Робот-пилосос60 000A3 0006 %86 %B
Чайник18 000A2 5005 %91 %B
Рушники8 000C2 0004 %95 %B
Кавомолка14 000B1 0002 %97 %C
Контейнери для їжі5 000C1 0002 %99 %C
Губки3 000C5001 %100 %C

За виручкою накопичені частки йдуть так: пилосос 30 %, каструлі 48 %, ножі 60 %, сковорода 70 %, чайник 79 % — це група A з п'яти товарів. Кавомолка (86 %) і термос (92 %) — B, решта — C.

За прибутком група A — інша: каструлі, ножі, сковорода і термос разом дають рівно 80 % прибутку. Змінили групу п'ять товарів із десяти:

  • Робот-пилосос і чайник: A → B. Багато виручки, мало маржі: пилосос заробляє 3 000 грн із 60 000.
  • Термос: B → A. Середня виручка, але третина її — прибуток.
  • Рушники: C → B. Дешеві, але заробляють більше, ніж кавомолка вдвічі більшої виручки.
  • Кавомолка: B → C. 14 000 грн виручки дають лише 1 000 грн прибутку.
Перехід груп від виручки до прибутку: пилосос A → B, термос B → A, рушники C → B

Якби закупівельник орієнтувався на ABC за виручкою, він тримав би найбільший запас пилососів — товару, що заморожує найбільше грошей і заробляє 5 % від своєї ціни. Як рахувати маржу кожного товару — у статті що таке маржинальність.

Що робити з групами A, B і C

Група визначає, скільки уваги й грошей у запасі отримує товар: A — максимум, C — мінімум.

ГрупаЗапас і закупівляЦіна й просування
AНіколи не нуль: страховий запас, перевірка наявності щодня, запасний постачальникЦіну міняйте обережно — кожен відсоток помітний у прибутку; найкращі картки й реклама
BЗакупівля за графіком, перевірка раз на тижденьШукайте, хто з них може перейти в A: ціна, реклама, комплект з A-товаром
CМінімальний залишок або продаж під замовлення зі складу постачальникаПеревірте ціну; якщо товар не супутній до A, кандидат на виведення
Що робити з групами ABC: A — без нуля на складі, B — закупівля за графіком, C — мінімум або під замовлення

Не виводьте C-товари наосліп. Губки у прикладі заробляють 1 % прибутку, але якщо їх беруть разом зі сковородою, без них частину кошиків перенесуть в інший магазин. Друге, чого ABC не бачить, — стабільність продажів: товар групи A з продажами то 100, то 5 штук на місяць вимагає іншого запасу, ніж рівний. Для цього ABC поєднують з XYZ-аналізом — як це зробити і яке правило закупівлі дає кожна з дев'яти клітинок, розібрано в статті асортиментна матриця.

Де ABC-аналіз обманює

ABC показує минулий період, а рішення ви приймаєте про майбутній. П'ять ситуацій, де це розходиться:

  • Новинки. Товар, що з'явився в останній місяць кварталу, гарантовано в C. Аналізуйте новинки окремо або після повного періоду.
  • Сезон. Квартал з обігрівачами в січні і без них у квітні дає дві різні групи A. Порівнюйте сезонні товари з тим самим періодом минулого року.
  • Дефіцит. Товар, якого місяць не було в наявності, втратив продажі і впав у групу нижче. Перевірте, чи не був він у нулі, перш ніж скорочувати закупівлю.
  • Варіанти товару. Футболка в п'яти розмірах — це п'ять рядків, і кожен окремо може потрапити в C. Для рішень про модель зведіть розміри й кольори в один рядок.
  • Неповна собівартість. Товари без собівартості дають прибуток, рівний виручці, і штучно піднімаються в A. Перед аналізом заповніть закупівельні ціни або виключіть такі товари.

Окремий випадок — товари без продажів узагалі: в ABC вони навіть не потрапляють, бо мають нульовий прибуток, але заморожують гроші на складі. Як їх знайти і що з ними робити — у статті неліквідний товар.

Коли ABC-аналіз краще рахувати в програмі

Коли товарів сотні і рахувати ABC треба щомісяця. Тоді найдовше в Excel — не формули, а щоразу вивантажити продажі, підтягнути собівартість і звести варіанти товару.

В Elbuz звіт «Аналітика продажів» сам рахує ABC-аналіз за прибутком із межами 80 і 95 % за вибраний період: продажі беруться з проведених замовлень, собівартість — зі складу або картки товару, а товари можна групувати за моделлю, щоб розміри й кольори рахувались одним рядком. Як читати звіт — у довідці аналітика продажів.

Чесна оговорка: звіт бере в ABC до 100 найприбутковіших товарів періоду і пропускає товари без собівартості, тож закупівельні ціни треба заповнити заздалегідь, а повний розклад тисяч позицій однаково доведеться робити в таблиці. Якщо у вас кілька десятків товарів, таблиці з формулами вище цілком достатньо.

Склад і продажі в ElbuzЗалишки, замовлення й аналітика продажів в одній системі, 14 днів безкоштовно

Часті запитання

Що означають групи A, B і C в ABC-аналізі?

A — товари, які разом дають до 80 % прибутку чи виручки, B — наступні до 95 %, C — останні 5 %. Межі 80 і 95 % найпоширеніші, але їх можна задати інші.

Який принцип лежить в основі ABC-аналізу?

Принцип Парето: невелика частина товарів дає більшу частину результату. ABC-аналіз показує, де саме ця межа проходить у вашому асортименті.

Як розрахувати ABC-аналіз?

Відсортуйте товари за прибутком від більшого, порахуйте частку кожного в загальному прибутку, додайте частки наростаючим підсумком і присвойте групу: до 80 % — A, до 95 % — B, решта — C.

Як зробити ABC-аналіз в Excel?

Трьома формулами: частка =B2/SUM($B$2:$B$11), накопичена частка =SUM($C$2:C2) і група через IF з межами 80 і 95 %. Повні формули і приклад — у розділі як зробити в Excel.

За яким показником краще робити ABC-аналіз?

Для рішень про асортимент і закупівлі — за прибутком. Виручка показує оборот, кількість — навантаження на склад, але обидва показники піднімають у групу A товари, які мало заробляють.

Чого не можна зробити за допомогою ABC-XYZ-аналізу?

Оцінити новинки і товари, яких не було в наявності, а також зрозуміти причину низьких продажів: аналіз показує, що відбулося за період, але не чому. Ці випадки перевіряють окремо.

Збережи посилання на цю сторінку Telegram Facebook X LinkedIn Viber WhatsApp
Дізнайтеся більше про Elbuz за допомогою AI Поставте своє запитання — оберіть чат, і AI вивчить цю сторінку за вас:

Обговорення теми – ABC-аналіз товарів: як зробити в Excel на прикладі 10 товарів

0 коментарів

Немає коментарів.

Написати коментар

Ваша адреса електронної пошти не буде опублікована. Обов'язкові поля відмічені

Далі

Спробувати безкоштовно