Как провести ABC-анализ в Excel: 5 простых шагов

ABC-анализ распределяет позиции запасов по трем классам в зависимости от стоимости их годового использования. Товары класса A — это немногие позиции, на которые приходится большая часть затрат. Товары класса C — это многочисленные позиции, на долю которых приходится мало средств, а класс B находится посередине. В Excel это можно сделать с помощью одной таблицы: годовая стоимость, доля в общем объеме, нарастающий итог и формула, которая присваивает каждый класс.

В этом руководстве объясняется значение классов, описываются пять шагов в Excel, приводится практический пример и рассказывается, как построить диаграмму по результатам. Также рассматривается, как выбрать пороговые значения и что делать с каждым классом после завершения анализа.

Что такое ABC-анализ

ABC-анализ — это способ определить, какие позиции заслуживают наибольшего внимания. Он опирается на простую закономерность: на небольшую долю товаров приходится львиная доля расходов.

В главе 2012 года об анализе и контроле расходов на фармацевтическую продукцию от организации Management Sciences for Health (MSH) это описывается предельно просто. В ней отмечается, что «относительно небольшое количество позиций составляет большую часть стоимости годового потребления». И добавляется: «Анализ этого явления известен как анализ Парето или, чаще, ABC-анализ».

В той же главе поясняется, что товары «могут быть классифицированы по трем категориям (A, B и C) на основе стоимости их годового использования». Метод остается прежним независимо от того, храните ли вы на складе медикаменты, запасные части или розничные товары.

Один момент легко упустить из виду. Классы не являются постоянными ярлыками. MSH отмечает, что «если структура использования меняется, при следующем проведении ABC-анализа товар может попасть в другую категорию». Поэтому ABC-анализ лучше всего работает как регулярная проверка, а не как разовый проект.

Что означают классы A, B и C

В главе MSH приводятся типичные диапазоны для каждого класса:

Класс	Доля позиций	Доля годовой стоимости	Что это обычно означает
A	от 10 до 20 процентов	от 75 до 80 процентов	Мало позиций, большая часть денег
B	от 10 до 20 процентов	от 15 до 20 процентов	Средняя группа
C	от 60 до 80 процентов	от 5 до 10 процентов	Много позиций, мало денег

Это типичные диапазоны, а не жесткие правила. MSH отмечает: «Эти границы в некоторой степени гибки». В ее примере класс A устанавливается для товаров, на долю которых в сумме приходится 70 процентов средств.

Величина, определяющая классы, — это стоимость годового потребления: количество единиц, использованных за год, умноженное на стоимость единицы. Дешевый товар, используемый в огромных объемах, может попасть в класс A. Дорогой товар, используемый раз в год, может оказаться в классе C.

В статье 2014 года в American Journal of Business Education ставится под сомнение использование только стоимости. Авторы утверждают, что учебники «сосредоточены на денежном объеме как единственном критерии», и рекомендуют добавить другие критерии. Для первого приближения стоимость — это метод, который используется в главе MSH.

Что вам понадобится перед началом работы

Для проведения ABC-анализа в Excel требуется всего несколько столбцов для каждой позиции:

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

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

Если ваши данные поступают из системы учета запасов или закупок, экспортируйте их в формате CSV или Excel. Удалите позиции, по которым не было движения за этот период, либо оставьте их, ожидая, что они попадут в класс C.

Как сделать ABC-анализ в Excel

Описанные ниже пять шагов соответствуют методу из главы MSH, адаптированному под формулы Excel. В примере название находится в строке 1, заголовки — в строке 2, а 10 позиций — в строках с 3 по 12. Столбцы A, B и C содержат название товара, годовое количество единиц и стоимость единицы.

Шаг 1: Составьте список позиций, количества единиц и стоимости единицы

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

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

Если было совершено несколько закупок одного и того же товара по разным ценам, используйте одну согласованную стоимость. MSH отмечает, что «средневзвешенное значение или среднее значение по методу FIFO» являются наиболее точными альтернативами, когда фактическую стоимость единицы сложно отследить.

Шаг 2: Рассчитайте годовую стоимость и ее долю в общем объеме

В столбце D умножьте количество единиц на стоимость, чтобы получить годовую стоимость каждого товара. В ячейку D3 введите =B3*C3 и протяните формулу вниз.

В столбце E разделите каждое значение на сумму всех значений, чтобы получить его долю. В ячейку E3 введите =D3/SUM($D$3:$D$12) и протяните вниз. Знаки доллара фиксируют диапазон суммирования при копировании формулы. Настройте формат столбца E как процентный с двумя знаками после запятой.

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

Шаг 3: Отсортируйте позиции по стоимости, от наибольшей к наименьшей

Выделите всю таблицу, включая заголовки, и отсортируйте по столбцу D от наибольшего к наименьшему. В Excel для этого выберите «Данные», затем «Сортировка», указав столбец D и порядок «От максимального к минимальному».

Если вы предпочитаете формулу, функция SORT возвращает отсортированную копию. Синтаксис Microsoft выглядит как =SORT(array,[sort_index],[sort_order],[by_col]), где порядок сортировки -1 означает убывание. Для этой таблицы формула =SORT(A3:E12,4,-1) выполняет сортировку по четвертому столбцу, начиная с наибольшего значения.

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

Шаг 4: Добавьте накопленный процент

В столбце F добавьте нарастающий итог долей. В ячейку F3 введите =SUM($E$3:E3) and протяните вниз. Первая часть диапазона остается фиксированной, а вторая увеличивается на одну строку при каждом копировании.

В последней строке должно быть 100 процентов. Если это не так, проверьте наличие пустых ячеек или текстовых значений в столбцах D и E.

Этот столбец — основа ABC-анализа. Он показывает, какую долю от общей стоимости составляют все вышележащие позиции вместе взятые.

Шаг 5: Присвойте классы A, B и C

В столбце G используйте формулу для маркировки каждого товара. При пороговых значениях 80 и 95 процентов введите следующую формулу в ячейку G3 и протяните ее вниз:

=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")

Функция IFS проверяет каждое условие по порядку и возвращает первое совпадение. В собственном примере Microsoft используется тот же шаблон, где TRUE служит финальным универсальным условием. Позиции с накопленным итогом до 80 процентов получают класс A, до 95 процентов — класс B, а остальные — класс C.

Наконец, подсчитайте количество позиций в каждом классе с помощью формулы =COUNTIF(G3:G12,"A") (и аналогично для B и C). Сравните полученные результаты с типичными диапазонами, указанными выше. Скорректируйте пороговые значения, если класс A окажется слишком большим или слишком маленьким для эффективного управления вашей командой.

Практический пример

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

Товар	Годовое количество	Стоимость единицы	Годовая стоимость	Доля	Накопленная доля	Класс
SKU-01	1,200	$45.00	$54,000	36.00%	36.00%	A
SKU-02	3,000	$12.00	$36,000	24.00%	60.00%	A
SKU-03	500	$40.00	$20,000	13.33%	73.33%	A
SKU-04	8,000	$1.50	$12,000	8.00%	81.33%	B
SKU-05	2,000	$4.00	$8,000	5.33%	86.67%	B
SKU-06	600	$10.00	$6,000	4.00%	90.67%	B
SKU-07	1,000	$5.00	$5,000	3.33%	94.00%	B
SKU-08	1,500	$3.00	$4,500	3.00%	97.00%	C
SKU-09	700	$5.00	$3,500	2.33%	99.33%	C
SKU-10	400	$2.50	$1,000	0.67%	100.00%	C

Общая годовая стоимость составляет $150,000. Три товара, то есть 30 процентов списка, составляют 73.33 процента стоимости и попадают в класс A. Четыре товара попадают в класс B, а последние три, на долю которых приходится 6 процентов стоимости, — в класс C.

Выделяются две детали. SKU-04 имеет безусловно наибольшее количество единиц, но из-за низкой стоимости этот товар попадает в класс B. Кроме того, при наличии всего 10 позиций доли классов не будут точно соответствовать типичным диапазонам, что вполне нормально для короткого списка.

Как построить диаграмму по результатам

Диаграмма позволяет легко продемонстрировать закономерность на совещании. MSH предлагает построить график зависимости накопленного процента от номера позиции, что дает привычную кривую ABC.

В Excel есть встроенный тип диаграммы для этих целей. Microsoft описывает диаграмму Парето как диаграмму, которая «содержит как столбцы, отсортированные в порядке убывания, так и линию, представляющую накопленный процент». Чтобы создать ее, выделите названия позиций и годовые значения, затем выберите «Вставка», «Вставить статистическую диаграмму» и «Парето».

Добавьте две горизонтальные линии или метки на уровне ваших пороговых значений, например 80 и 95 процентов, чтобы зрители могли видеть, где начинается каждый класс. В нашем руководстве по созданию диаграммы Парето с помощью ИИ эта тема раскрыта более подробно.

Выбор пороговых значений

Не существует единого правильного порогового значения. MSH объясняет, что выбор «зависит от того, как объем и стоимость распределены между позициями в списке». Он также зависит от того, «как будут использоваться результаты ABC-анализа».

Практическим ограничением являются возможности управления. MSH формулирует это прямо: «отнесение позиций к классу A должно основываться на возможностях управления». Если ваша команда может внимательно анализировать только 50 позиций в месяц, класс A из 300 позиций лишает анализ всякого смысла.

Несколько распространенных подходов:

• Пороговые значения по стоимости. Класс A — до 80 процентов стоимости, B — до 95 процентов, C — все остальные. Именно этот метод использовался выше.
• Пороговые значения по количеству позиций. Первые 20 процентов позиций по стоимости становятся классом A, следующие 30 процентов — классом B, а остальные — классом C.
• Фиксированные списки. Некоторые команды определяют класс A как первые 25 или 50 позиций, независимо от их доли в стоимости.

Какой бы вариант вы ни выбрали, зафиксируйте его и используйте постоянно. Сравнение классов этого квартала с классами предыдущего имеет смысл только в том случае, если пороговые значения остаются неизменными.

Что делать с каждым классом

Суть ABC-анализа заключается в том, чтобы направлять усилия туда, где сосредоточены основные деньги. В главе MSH приводится несколько способов использования результатов:

• Заказывайте товары класса A чаще. MSH отмечает, что заказ товаров класса A «чаще и меньшими партиями должен привести к снижению затрат на содержание запасов».
• Ведение переговоров по ценам на товары класса A в первую очередь. «Снижение цен на товары, классифицированные в ходе анализа как продукты класса A, может привести к значительной экономии», — говорится в главе.
• Проводите инвентаризацию класса A чаще. MSH отмечает, что «циклические инвентаризации запасов должны основываться на ABC-анализе, с более частым подсчетом товаров класса A».
• Следите за статусом заказов класса A. Внезапный дефицит товара класса A может привести к дорогостоящим экстренным закупкам.

Для товаров класса C можно установить более простые правила, например, более крупные и редкие заказы, а также менее частые инвентаризации. Класс B находится посередине. Если вас беспокоят неходовые товары, наше руководство по выявлению неходовых запасов отлично дополнит этот анализ.

Как сделать это быстрее с помощью ИИ

Шаги в Excel занимают всего несколько минут, когда данные уже очищены. Очистка экспортированных данных и повторение этой работы каждый квартал требуют гораздо больше времени.

Рабочее пространство с ИИ может выполнить все математические расчеты и сортировку за один запрос. Загрузите экспортированные данные о запасах или закупках в Powerdrill Bloom и попросите на обычном языке провести ABC-анализ с вашими пороговыми значениями. Запросите годовую стоимость, долю, накопленный процент и класс для каждого товара, а также диаграмму Парето.

Затем проверьте результаты, как в любой электронной таблице. Сверьте общую годовую стоимость со своей собственной суммой и выборочно проверьте по два товара в каждом классе. На нашей странице Excel AI assistant этот вид работы с таблицами описан более подробно. Для более широкого ознакомления с инструментами прогнозирования ознакомьтесь с этим обзором инструментов ИИ для прогнозирования запасов и спроса.

Распространенные ошибки, которых следует избегать

• Смешивание временных периодов. Двенадцать месяцев для одного товара и шесть для другого делают расчет долей бессмысленным.
• Использование количества единиц вместо стоимости. Классы зависят от произведения количества единиц на стоимость, а не от одного лишь количества.
• Забывание о сортировке перед расчетом нарастающего итога. Накопленный процент в неотсортированном списке приведет к тому, что товары попадут не в те классы.
• Отношение к классам как к постоянным. Запускайте анализ заново каждый квартал или год, так как товары перемещаются между классами.
• Пороговые значения без учета возможностей управления. Список класса A, который слишком велик для тщательного контроля, получит не больше внимания, чем класс B.
• Игнорирование критически важных дешевых товаров. Дешевый товар все равно может остановить работу, если он закончится. В главе MSH ABC-анализ сочетается с отдельной оценкой жизненно важных, важных и второстепенных позиций.

Если ваш список товаров получен из неструктурированного экспорта, вы можете попробовать Powerdrill Bloom, чтобы построить первую таблицу и диаграмму ABC.

Часто задаваемые вопросы

Что такое ABC-анализ в управлении запасами?

ABC-анализ распределяет товары по трем классам в зависимости от стоимости их годового потребления. Товары класса A — это немногие позиции, на которые приходится большая часть стоимости. Товары класса C — это многочисленные позиции, на долю которых приходится мало средств, а класс B находится посередине. Это помогает командам сосредоточить усилия по контролю там, где находятся основные деньги.

Как рассчитать ABC-анализ в Excel?

Умножьте годовое количество единиц на стоимость единицы для каждого товара, затем разделите на общую сумму, чтобы получить долю каждого товара. Отсортируйте по стоимости от наибольшей к наименьшей, добавьте нарастающий итог долей и присвойте классы с помощью формулы, например IFS. Пороговые значения 80 и 95 процентов укладываются в типичные диапазоны, описанные в главе MSH.

Каковы процентные соотношения для ABC-анализа?

Общепринятое руководство гласит, что класс A содержит от 10 до 20 процентов позиций и от 75 до 80 процентов стоимости. Класс B содержит еще от 10 до 20 процентов позиций и от 15 до 20 процентов стоимости. Класс C содержит от 60 до 80 процентов позиций и от 5 до 10 процентов стоимости.

Какова формула для ABC-классификации в Excel?

Если накопленный процент находится в столбце F, а данные начинаются со строки 3, используйте формулу =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Измените значения 0.8 и 0.95 в соответствии с вашими собственными пороговыми значениями. Вложенные формулы IF могут выполнять ту же задачу.

Почему важен ABC-анализ?

Он показывает, куда уходит большая часть денег на запасы, позволяя командам более тщательно управлять этими позициями. Типичные способы применения включают более частый заказ товаров класса A, первоочередное обсуждение цен на них и более частую инвентаризацию. Он также выявляет расходы, которые не соответствуют планам.

Источники: Management Sciences for Health, MDS-3 Глава 40: Анализ и контроль расходов на фармацевтическую продукцию · Равиндер и Мисра, ABC-анализ для управления запасами (2014) · Служба поддержки Microsoft, функция SORT · Служба поддержки Microsoft, функция IFS · Служба поддержки Microsoft, Создание диаграммы Парето.