Abc и xyz анализ в excel: таблицы, примеры расчетов, формулы

Методология проведения АВС-анализа

Важно помнить, что АВС-анализ окажется эффективным инструментом менеджмента только при правильном и планомерном его использовании, поскольку в противном случае он способен привести к неверным выводам. Рассмотрим последовательность АВС-анализа – как его сделать в «Экселе» или подобной программе.

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

1. Создание списка всех учитываемых ресурсов. Разумеется, исходным условием является наличие первичных данных, из которых отбираются те, что представляют интерес. В зависимости от цели конкретного АВС-анализа, речь может идти о товарном ассортименте, описи сырья или оборудования, перечне клиентов или групп потребителей, списке персонала, разделах бюджета и т. д.
2. Определение показателя, по которому будет производиться сортировка. Это тоже зависит от целей и задач АВС-анализа. К примеру, ключевой для сортировки признак может быть объемом продаж, размером прибыли, стоимостью закупки сырья, затратами на работника или на подразделение, уровнем инвестиций и т. п.
3. Следующий шаг АВС-анализа – сортировка всех позиций по выбранному критерию в порядке убывания, которая в «Экселе» делается автоматически.
4. Определение доли (вклада в общий результат) каждой позиции. Это действие АВС-анализа тоже легко производится в «Экселе» автоматизированным способом – требуется лишь разделить показатель отдельной позиции на общую сумму и далее перевести полученный результат из абсолютной в процентную форму.
5. Процентные показатели последовательно складываются сверху вниз – для этого создается колонка «Накопительный вклад».
6. Выделение групп А, В и С. Это производится выделением границ в тех местах, где процентные показатели в графе «Накопительный вклад» достигают заданного объема.
   • Все позиции/строки от верхней части таблицы до накопительного показателя 80 % — группа А.
   • Все позиции/строки между накопительными показателями 80 и 95 % — группа В.
   • Все оставшиеся позиции/строки от накопительного показателя 95 % до конца списка — группа C.
7. Формулирование выводов по АВС-анализу. Полученные расчеты в удобном виде показывают сравнительную ценность имеющихся ресурсов, но эти количественные показатели нуждаются в изучении и объяснении. К примеру, желательно четко понимать, почему те или иные товары оказались в данной категории, поскольку могут обнаружиться случайные или временно действующие факторы, которые повлияли на результаты.

Группа А – это главные ресурсы, обеспечивающие максимальную прибыль или продажи. Отказ от них негативно отразится на состоянии компании.

Соответственно, данные ресурсы нужно расширять и продвигать. Например, если это товары, то им должен обеспечиваться самый благоприятный механизм выхода на рынок, нужно заботиться о поддержании их конкурентоспособности, выделяя на это инвестиции. Успешный опыт группы А следует изучать и пытаться использовать его в отношении других категорий.

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

Группа С – это убыточные или приносящие минимальную прибыль активы. Значит, их необходимо улучшать, трансформировать в то, что способно давать доход, или избавляться от них.

Особого внимания заслуживает именно категория С.

Возможны разные причины, почему ресурс попал в эту группу:

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

Совмещение

Одним из наиболее эффективных инструментов считается совмещение всех представленных анализов в один. Для этого создается одна таблица, которая согласно девяти групп расставляет объекты по итогам двух анализов.

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

Совмещенная таблица

Разделение проходит таким образом:

• продукция групп А и В составляет максимальный оборот предприятия. Лучше, чтобы такие товары всегда были в наличии;
• АХ и ВХ отображают постоянный спрос и большой оборот. Следует предусматривать регулярное пополнение и наличие, но не делать чрезмерный запас;
• AY и BY обладают недостаточной стабильностью расхода и необходимостью увеличения страхового запаса;
• AZ и BZ имеют высокий оборот, но малую прогнозируемость расхода. Рекомендуется откорректировать систему заказов продукции;
• группа С является основой 80% товарного ряда, поэтому использование XYZ-анализа способно снизить сроки, которые тратятся сотрудниками на контроль и управление продукцией, находящейся в группе;
• СХ и продукция этой группы применяется для системы заказов с регулярной периодичностью и снижением страхового запаса продукции;
• CY характеризуется возможностью перехода на систему с постоянным объемом заказов с созданием страхового запаса;
• CZ составляет перечень новой продукции, а также товары периодичного и спроса под заказ. Их можно удалять из перечня продаваемых и следует регулярно держать на контроле, потому что они являются неликвидными или сложно реализуемыми и могут приводить к убытку.

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

Распространено совмещение в одной таблице результатов АВС и XYZ-анализов, для получения наиболее эффективных выводов.

Примеры решенных задач

Приводим примеры решенных задач на проведение ABC — анализа

Задача №1

В целях укрепления позиции на рынке оптовая фирма расширяет ассортимент. Свободных финансовых средств нет, дополнительных складских помещений нет. Перед службой логистики была поставлена задача сокращения общего объема товарных запасов с целью сокращения затрат. Снижение запасов не должно отражаться на уровне обслуживания клиентов! Торговый ассортимент компании, годовая реализация представлена в таблице 1.

Таблица 1 — Исходные данные для проведения АВС – анализа

№ поз.	Наименование товара	Годовая реализация, тыс. руб.
1	Батончик «Марс»	1700
2	Батончик «Твикс»	650
3	Батончик «Баунти»	780
4	Батончик «Сникерс»	2400
5	Изюм	1500
6	Инжир	670
7	Кетчуп «Кальве»	3100
8	Кетчуп «Балтимор»	350
9	Кофе «Нескафе»	1020
10	Кофе «Чибо»	510
11	Кофе «Якобс»	38
12	Кофе «Арабика» молотый	56
13	Круассаны	220
14	Курага	96
15	Кукурузные хлопья	150
16	Миндаль	2000
17	Рис длинный	6500
18	Рис круглый	130
19	Сахар-песок	1600
20	Чай «Ахмад»	200
21	Чай «Липтон»	50
22	Торт вафельный	60
23	Шоколад «Аленка»	110
24	Шоколад «Вдохновение»	300
25	Шоколад «Несквик»	80
	ИТОГО	24270

1. Сформулировать цель АВС-анализа.

2. Указать объект управления.

3. Указать признак, на основе которого будет осуществлена дифференциация.

4. Сформировать табл. 2.

Таблица 2 — Расчет доли товара в общей реализации

№ п/п	Наименование товара	Годовой объем, тыс. руб.	Доля в общей реализации, %
1	Батончик «Марс»	1700
2	Батончик «Твикс»	650
…	…	…
	ИТОГО	24270	100

5. Выстроить ассортиментные позиции в порядке убывания доли в общей реализации и разместить в табл.3.

Таблица 3 — АВС – анализ

№ п/п	Наименование товара	Годовой объем, тыс. руб.	Доля в общей реализации, %	Номер строки упорядоченного списка	Кол-во позиций ассортимента нарастающим итогом в % к общему кол-ву позиций (ось ОХ)	Доля товара нарастающим итогом (ось ОУ), %
1	2	3	4	5	6	7
…	…	…	…	…	…	…
…	…	…	…	…	…	…
	ИТОГО	24270			100	100

6. Построить кривую АВС

7. Разделить анализируемый ассортимент на группы А, В и С, предположив, что среднестатистическое процентное соотношение групп:

Группа	Доля в ассортименте (ОХ), %	Доля в реализации (ОУ), %
А	20	80
В	30	15
С	50	5

8. Определить границы А, В, С графическим методом на кривой.

9. Сделать выводы.

Эта задача уже решена! Вы можете получить её за 250 руб.

Задача №2

Корпорация производит быстродействующие 1 – мегабайтные блоки (чипы). Она организовала запасы 10 наименований на годовой долларовой базе. Сделайте анализ – АВС по этим запасам. Постройте график АВС – анализа.

Исходные данные:

Наименование запасов	Годовой объем	Доля от годового объема в %	Стоимость единицы $	Годовой объем $	Доля от годового объема %	Класс (А; В; С)
10286	900		80
13444	600		16400
10818	1450		17
11317	450		32,86
12640	1000		22,5
15111	600		14,17
10678	1900		0,6
1447	200		8,5
1519	1100		0,42
14075	250		0,6
Итого:

Эта задача уже решена! Вы можете получить её за 250 руб.

Задача №3

Имеется десять товарных позиций. Для каждой дано значение среднего запаса за последний квартал. Исходные данные приведены в таблице 1.

1) Решение оформить в таблице (таблица 2).

2) Выполнить дифференциацию объектов управления по степени их важности и влияния на конечный результат с использованием АВС-анализа (представить ранжирование по группам А, B, С), сделать вывод. Таблица 1 — Исходные данные для проведения АВС-анализа

Таблица 1 — Исходные данные для проведения АВС-анализа

№ позиции	Средний запас за квартал, усл.ед.
1	2600
2	700
3	3000
4	530
5	120
6	1870
7	180
8	17000
9	300
10	4020

Таблица 2

№ позиции	Средний запас за квартал, усл.ед.	Доля в итоговом запасе,%	Доля с накоплением,%
…	…	…	…

Эта задача уже решена! Вы можете получить её за 250 руб.

Вернуться к примерам решения задач по логистике

ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами

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

Количество товара — более 5 000 позиций. Объединяем их в группы по видам номенклатуры.

Из учетной системы выгружаем данные за 2020 год:

количество продаж с разбивкой по кварталам;
цена реализации за единицу;
выручка итого за год в рублях

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

ABC-анализ в Excel: пример

Сортировка выручки по убыванию

Выделяем диапазон ячеек: вся таблица вместе с заголовками без строки «Итого».

В ниспадающем меню выбираем:

Данные — Сортировка — Сортировать по:

• столбец «Выручка»
• сортировка «Значения»
• порядок «По убыванию»

Нажимаем «Ок».

Система выстраивает таблицу по убыванию размера выручки в столбце D.

ABC-анализ в Excel: сортировка выручки по убываниюABC-анализ в Excel: сортировка по убыванию

Доля каждой строки в общем параметре

Определяем долю каждой номенклатуры в выручке:

• добавляем графу Доля (Е). Формат ячеек процентный;
• в строку 2 для товара 6 вводим формулу: выручка товара 6 / выручка итого;
• протягиваем формулу вниз по всем товарам.

Добавляем графу F и рассчитываем Долю накопительным итогом: складываем текущее значение со всеми предыдущими.

ABC-анализ в Excel: формулы расчёта доли каждого товара в выручке

Символ & предупреждает Excel, что формулу нельзя двигать:

• & перед буквой — по столбцам;
• & перед цифрой — по строкам.

ABC-анализ в Excel: доля каждого товара в выручке

Перед тем как создавать ABC-таблицу проверьте долю каждого товара в общем значении (выручки, запасах, себестоимости и пр.). Проводить ABC аналитику бессмысленно, если объект распределяется примерно в равных долях. Каждый показатель вносит одинаковый вклад в результат.

Определяем группу

Создаем графу Группа. Каждому товару присваиваем значения А, В, С в зависимости от доли в выручке.

Руководство утвердило матрицу:

Группа	Диапазон
A	до 70%
B	70-90%
C	90-100%

В ячейке G2 прописываем формулу . Протягиваем формулу вниз по всем товарам.

В примере для наглядности проценты заданы цифрами. 

В рабочем файле Excel вместо процентов ссылки на ячейки со значениями матрицы. При изменении параметров матрицы формула будет автоматически пересчитываться по всем товарам.

ABC-анализ в Excel: распределение по группам, формулы
ABC-анализ в Excel: распределение по группам — результат обработки

В столбце G каждой номенклатурной группе присвоен код А, В, С.

В группу А попали товары, которые приносят основную прибыль.

В группу В — продукция компании, на которую нерегулярный спрос.

Группа С — товары, которые зарабатывают только 10% от выручки.

Принятие решений на основе результатов ABC‑анализа

Теперь можем решить, как оптимизировать ассортимент. Например, перестанем закупать товары из диапазона С, потратим освободившиеся деньги на продукцию из группы А.

Но однокомпонентная сортировка — не лучшее подспорье для решений. Лучше сделать расчеты по двум-трем критериям, например, сортировку по маржинальному доходу дополнить аналогичной по выручке.

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

Так же и с клиентами: некоторые заказчики приносят минимум прибыли, но имеют громкое имя. Их примеру следуют более мелкие фирмы, которые заключают с вами прибыльные контракты.

Представим, что фирма «Интек» провела анализ по выручке и распределила строки по двухкомпонентным группам:

Итоговая таблица по двум критериям

Теперь проще решить, что делать:

• Исключить из списка строки ВС, СС и СВ. В них организация «замораживает» капитал, который выгоднее потратить на закупку товаров из «желтых» и «зеленых» диапазонов.
• Увеличить закупки продукции АА. Это продукция с максимальной маржой и спросом. Если повысить объемы продаж, увеличится прибыль.
• Активизировать маркетинг для позиций с АВ. У них хорошая рентабельность, но низкий спрос. Возможно, клиенты просто не знают про эти варианты.
• Повысить маржинальность продукции ВА. Спрос хороший, но не хватает маржи. Может помочь изменение условий закупки или поиск новых поставщиков.
• От товаров из «желтого» диапазона сразу не отказываемся, но снижаем объемы закупок в пользу «зеленых». Можно рассмотреть шаги по повышению рентабельности и спроса.

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

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

Как сделать ABC-анализ: пошаговая инструкция

Ну что ж, теории было достаточно, попробуем теперь практику. Возьмем простенькую бизнес-задачу, связанную с продажами товара.

Допустим, самозанятая Екатерина печет на заказ для кондитерской булочки, пирожные и печенье. Ей кажется, что печенье для нее – самые выгодные продажи, потому что она выпекает его больше всего по количеству. За последние два месяца выручка по разным изделиям менялась неравномерно, и перед ней встала задача: из ее продукции нужно убрать что-то одно, потому что времени на все не хватает, а заказы кондитерской постоянно растут. Что ей потребуется, чтобы провести АВС-анализ на примере собственных продаж?

• Если Екатерина – девушка продвинутая, то может воспользоваться компьютером и каким-либо видом электронных таблиц, Excel или Google. Если Екатерина – одинокая пенсионерка и с техникой на Вы, тогда придется довольствовать бумагой и ручкой.
• Следующий шаг: формулировка цели расчетов. Екатерине нужно знать, какие изделия приносят ей самую большую и самую маленькую выручку, чтобы сделать оценку и удалить наихудшие. Поэтому таблица у нее будет простой, состоящей из пяти столбцов. Столбец 1 – все виды выпечки, столбец 2 – выручка по каждой позиции, столбец 3 – доля каждого вида в общей выручке, столбцы 4 и 5 – итоговые результаты ранжирования.
• Далее Екатерине нужно перенести все данные о продажах и выручке из своей учетной тетради в таблицу. При этом заполнить нужно столбец 1 и столбец 2. В столбце 3 будет стоять формула расчета доли продаж каждого вида изделий в общей выручке.
• После заполнения данных и введения формул таблица сама считает необходимые значения. Екатерине остается только отсортировать сформированные расчеты по убыванию.
• В отсортированной таблице остается только рассчитать совокупный процент по каждому изделию и распределить товары по группам в соответствии с основным правилом АВС-анализа.
• Последний этап – организационно-стратегический. Здесь Екатерине уже придется сделать выбор, что продолжить выпекать, а от чего отказаться.

Этап 1. Анализ запасов по методу АВС

В основе подхода лежит принцип «20/80», который вывел социолог и экономист В. Парето. Он формулируется так: 20% усилий дают 80% результата. Подход подтверждается многолетней практикой, поэтому ему стоит верить.

Если переложить на экономику предприятия, то получим следующее:

• 20% товаров, продукции, работ или услуг обеспечивают 80% дохода/валовой прибыли предприятия;
• 20% материалов формируют 80% всех материальных затрат;
• 20% поставщиков закрывают 80% потребностей организации в снабжении и т.д.

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

Есть два момента про принцип Парето и метод АВС, которые важно знать.

Первый. Разработчики подхода расширили количество групп, которые выделяются из совокупности анализируемых объектов, до трех. Это А, В и С. Разбивка следующая:

• А – 20/80;
• В – 30/15;
• С – 50/5.

Применительно к товарам расшифровку привели на схеме.

Рисунок 1. АВС-анализ: что показывает

Такой вариант является традиционным, хотя не единственным. Например, есть модификация метода, в которой выделяются еще две группы:

• D – абсолютно неликвидные товары, не продающиеся годами;
• N – товары-новинки, по которым пока не сформировалась статистика продаж и принадлежность к конкретной группе неочевидна.

Второй. Методика обычно не работает с абсолютной точностью. Например, возможно, что около 78% дохода обеспечивается за счет 17% от общего количества товарных позиций. Это нормально и не считается изъяном подхода.

Давайте рассмотрим условный пример.

Предположим: организация продает 10 видов товаров. Результаты по доходам за 2020 г. были такими.

Таблица 1. Исходные данные

Ассортиментная позиция	Выручка, млн руб.
Товар 1	1,5
Товар 2	1,8
Товар 3	10,2
Товар 4	84,1
Товар 5	18,8
Товар 6	55,6
Товар 7	2,5
Товар 8	3,1
Товар 9	11,5
Товар 10	42,4

Наша задача: выделить из массива три группы. Первая должна формировать 80% дохода или чуть меньше. Вторая – около 15%. Третья – оставшиеся примерно 5%.

Чтобы ее решить:

• перестроим отчет по убыванию выручки. В этом поможет функция Excel Сортировка и фильтр → Настраиваемая сортировка;
• рассчитаем процентную долю выручки по каждому товару в общей величине;
• сложим ее нарастающим итогом;
• найдем в нарастающих итогах, между какими товарами пролегают две условные границы в примерно 80 и 95%. Все позиции, которые окажутся выше 80%, попадают в группу А. Те, что находятся между 80 и 95%, – в группу В. Оставшиеся – это категория С.

_________________________

Примечание: 95% получили как сумму 80% накопленного дохода для группы А и еще 15% для В.

_________________________

Результаты вычислений смотрите во второй таблице.

Таблица 2. АВС-анализ товаров

Ассортиментная позиция	Выручка, млн руб.	Доля в общей выручке, %	Доля в общей выручке нарастающим итогом, %	Группа
Товар 4	84,1	36,3	36,3	А
Товар 6	55,6	24,0	60,3	А
Товар 10	42,4	18,3	78,7	А
Товар 5	18,8	8,1	86,8	В
Товар 9	11,5	5,0	91,7	В
Товар 3	10,2	4,4	96,2	С
Товар	3,1	1,3	97,5	С
Товар 7	2,5	1,1	98,6	С
Товар 2	1,8	0,8	99,4	С
Товар 1	1,5	0,6	100,0	С
Итого	231,5	100,0	×	×

Есть рекомендация от экспертов: вместо выручки использовать в анализе валовую прибыль, и именно по ней формировать группы. Такой вариант в чем-то даже лучше. Ведь доход, полученный по товару, еще не означает получение прибыли от его реализации. Если продажная цена ниже закупочной, значит, организация торгует в убыток. Применение выручки в АВС-анализе не покажет подобную проблему, а подход на основе валовой прибыли сразу выявит. Правда, тогда может возникнуть сложность с долями значений, если по какому-то товару получен валовый убыток. Его удельный вес окажется с минусом, а это нелепица.

Какие выводы позволяет сделать

Полученная группировка говорит о следующем:

Может показаться, что метод АВС самодостаточен: расчеты сделаны, выводы получены. Однако у него есть существенный недостаток. А что если группа C – это не неликвиды, а недорогие товары повседневного спроса, например, хлеб и соль? Они продаются постоянно, но цена невелика, поэтому они не могут конкурировать с, допустим, элитным коньяком и шоколадом. Последние реализуются намного реже, но зато сразу «делают кассу».

Чтобы исключить подобного рода ошибки в интерпретации данных, прибегают к XYZ-подходу.

Как настроить классификации товаров в 1С УТ 11?

Если вы владеете такой статистикой, то сможете уделить больше внимания операциям именно с этими товарами. А на менее важные группы номенклатуры тратить меньше времени и ресурсов.

В конфигурации 1С УТ11 для деления номенклатуры по степени важности используются общепринятые классификации ABC и XYZ. С их помощью пользователь легко выделяет товары, продажа которых приносит максимальную прибыль и идет максимально стабильно

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

В основе АВС-анализа лежит принцип Парето, или закон 20/80: 20% действий дают 80% результата, 20% клиентов дают 80% прибыли и т.п.

XYZ-анализ номенклатуры классифицирует товары по категориям стабильности:

• Товары X-класса продаются стабильно и потребность в них легко поддается прогнозу.
• Товары Z-класса, наоборот, продаются нерегулярно по разным причинам (сезонность, тренд и т.д.).

 Как настроить классификации ABC и XYZ в 1С УТ11?

Откройте вкладку «Маркетинг». В поле «Сервис» выберите «Классификация номенклатуры» и укажите вариант отображения «Полные возможности».

Рисунок 1. Здесь нажмите кнопку «Настройка»:

Рисунок 2. Укажите параметры классификации (выручка, валовая прибыль, количество проданного товара) и периоды для каждой из классификаций. Сохраните настройки.

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

После этого нажмите «Выполнить ABC классификацию за все периоды» и «Выполнить XYZ классификацию за все периоды»

На этом настройка классификаций завершена, перейдем к результатам.

На вкладке «Маркетинг» в поле «Отчеты» выберите «ABC/XYZ – анализ номенклатуры» и сформируйте его:

Рисунок 3. Для разделения классификаций перейдите в настройки отчета (Все действия – Изменить вариант отчета) и поочередно снимите галочки с ABC и XYZ.

В результате вы получите раздельные категории товаров, проданных за указанный в настройках период, для последующего анализа:

Рисунок 4.Рисунок 5.Обратите внимание, что в рассмотренном примере товар «Шкаф 3-дверный» попал в A-класс при ABC-классификации, но в Z-класс при XYZ – классификации. Что это значит: За указанный период по выбранным параметрам оценки товар принес компании наибольшую прибыль, но его продажи не были стабильными

Это также сигнализирует о невозможности сделать прогноз о дальнейших уровнях продаж данного товара

Что это значит: За указанный период по выбранным параметрам оценки товар принес компании наибольшую прибыль, но его продажи не были стабильными. Это также сигнализирует о невозможности сделать прогноз о дальнейших уровнях продаж данного товара.

Владельцу магазина результаты анализа дают богатую пищу для размышлений

Следует переключить внимание на товары классов A и B: назначать на обработку заказов по этим товарам лучших менеджеров, искать лучших поставщиков, добиваться их бесперебойной поставки, увеличить расходы на продвижение товара

Товары класса С должны быть понижены в приоритетах, а то и вовсе сняты с продаж.

У вас остались вопросы?

Пример ABC анализа продаж продукции в Excel

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Разберем, как на практике проводить ABC-анализ продаж продукции в Excel для магазина сотовых телефонов. Для этого нам необходимо наличие наименование всех товаров (групп товаров) и их норма дохода. На рисунке ниже показана номенклатура товаров и размер прибыли на каждый из видов.

Номенклатура продукции для проведения ABC-анализа в Excel

Далее необходимо осуществить сортировку товаров по прибыльности. Заходим в главном меню Excel → «Данные» → «Сортировка». Результатом будет сортировка групп товаров по рентабельности от самого прибыльного до самого убыточного.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

На следующем этапе необходимо определить долю по каждому виду товара. Для этого воспользуемся формулами в Excel.

Доля продаж каждого вида товара =B5/СУММ($B$5:$B$15)

Определение доли продукции в объеме продаж компании

На следующем этапе рассчитывается доля групп накопительным итогом по формуле:

Доля товара в номенклатуре накопительным итогом =C6+D5

Оценка доли прибыли накопительным итогом для группы товаров

После этого необходимо определить границу до 80% для группы товаров «А», 80-95% для группы товаров «В» и 95-100% для товаров «С». На рисунке ниже представлен результат группировки товаров по трем группам для магазина сотовых телефонов. Так марки Samsung, Nokia, Fly и LG дают 80% всех продаж, Alcatel, HTC, Lenovo обеспечивают 15% продаж и Philips, Sony, Apple, ASUS приносят 5% выручки от реализации.

ABC анализ продаж продукции. Пример расчета в Excel

Проведя группировку товаров, компания получает аналитический отчет о том, какие товары обеспечивают основные денежные поступления. Дальнейшей целью является увеличение продаж целевых товаров из группы «А» и снижение доли не эффективных товаров из группы «С». В нашем примере около ~30%  всех товаров приносят компании 80% прибыли.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

АВС классификация товара в 1С 8.3

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

Другими словами, такой анализ является своего рода инструментом, посредством использования которого можно достичь следующих целей:

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

Правило Парето

Рассматриваемый нами анализ основывается на широко известном правиле Парето, которое коротко можно сформулировать следующим образом: 20% товарного ассортимента обеспечивают 80% всего оборота. Другими словами, метод АВС анализа строится на принципе «за большую часть ожидаемых результатов отвечает достаточно малое количество причин». Попутно заметим, что в наше время принцип Парето принято называть «правилом 20 на 80».

Что касается трактовки правила Парето непосредственно к АВС анализу, то оно трактуется так: обеспечение надежного контроля над 20% позиций позволяет контролировать 80% всей системы. Это утверждение верно как для сырьевых запасов, так и для любой продукции, выпускаемой предприятием.

Преимущества и суть ABC анализа

• простота;
• прозрачность;
• универсальность;
• достижение оптимизации ресурсов;
• возможность автоматизации анализа без сложного и дорогостоящего  программного обеспечения.

АВС анализ получил широкое распространение из-за своей универсальности и высокой эффективности. После окончания проведения АВС анализа все объекты, подвергшиеся ему, группируются. Как уже упоминалось, основанием для такого группирования является степень влияния объекта на конечный результат.

Весь имеющийся ассортимент анализируется по следующим показателям:

• объем продаж, под которым понимается проданное количество;
• получаемая прибыль — в этом случае подразумевается реализованная торговая наценка.

Иначе говоря, при АВС анализе все товарные запасы подразделяются на три группы:

• А. Самые ценные. 20% продажи товаров этой группы обеспечивают 80% продаж;
• В. Так называемые промежуточные товарные запасы. Они обеспечивают 15% продаж;
• С. Наименее ценные товарные запасы, обеспечивающие только 5% всех продаж.

Товары, относящиеся к группе А, представляют собой товары высокой важности. Они всегда должны быть в наличии

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

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

Если выразить суть данного метода анализа, то можно сказать, что ABC анализ — это своего рода ранжирование ассортимента по самым различным параметрам. Ранжированию могут подвергаться запасы на складах компании, клиенты, временные периоды продаж, т.е. любой показатель, имеющий определенное  количество статданных.

ABC-анализ: это хорошо или плохо?

В качестве безусловных плюсов АВС-анализа для бизнеса можно назвать несколько его характеристик:

• Универсальный характер применения. С его помощью можно анализировать абсолютно все, измеряемое в деньгах и отдельных единицах: товар, услуги, результативность работы персонала, доходность маршрутов и т.д.
• Простота проведения анализа. Готовые настройки и шаблон таблиц в Excel делаются очень просто, а использовать можно бессчетное количество раз без их изменения. С этим же связан такой плюс, как отсутствие необходимости покупать специальные программы для расчетов.
• Надежность результатов, которая основана на простоте расчета и понятности формул.
• Показательность данных, которая помогает легко их проанализировать и выявить необходимость оптимизации продаж.

Излишняя простота, в определённой степени даже примитивность анализа, влечет и минусы:

• Анализ не дает возможность прогнозирования. Только накопление результатов регулярного частого анализа может выявить некоторые тенденции.
• Проанализировать можно только один параметр без взаимосвязи с другими переменными или факторами влияния, например, с дополнительными издержками.
• Одномерность анализа не позволяет учитывать и воздействие внешних факторов: сезонность, конкурентность рынка, колебания спроса. Соответственно, невозможно сделать вывод, от чего зависит успешность или неуспешность продаж товаров.
• Анализ проводится вручную, и постоянно есть риск неправильно внести данные в таблицы. Это может повлечь ошибки в выводах.

ABC анализ продаж. Определение

ABC анализ (англ. ABC-analysis) – это метод повышения эффективности и результативности системы продаж предприятия. Наиболее часто метод ABC-анализа используют для оптимизации номенклатуры товара (ассортимента) и его запасов с целью увеличения объема продаж. Другими словами, цель ABC-анализа является выделение наиболее перспективных товаров (или группы товаров), которые приносят максимальный размер прибыли для компании.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Данный вид анализа основывается на закономерности выявленной экономистом Парето: «20% продукции обеспечивают, 80% прибылей компании». Целью компании при проведении такого анализа является определение ключевых товаров, и управление данной 20% группой, которое создаст контроль над 80% денежными поступлениями. Управление продажами и денежными платежами напрямую влияют на финансовую устойчивость и платежеспособность компании.

При проведении анализа продукции все товары делятся на три группы:

• Группа «А» – максимально ценные товары, занимают 20% ассортимента продукции, и приносят 80% прибыли от продаж;
• Группа «В» – малоценные товары, занимают 30% ассортимента продукции, и обеспечивают 15% продаж;
• Группа «С» – не востребованные товары, занимают 50% ассортимента, и обеспечивают 5% прибылей от продаж.

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

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

Выводы из комплексного анализа

Наложение матриц дает уже 9 групп товаров, каждая из которых имеет свое значение:

• группы АХ и ВХ – хорошие продажи, легко прогнозируемый спрос;
• группы AY и BY – хорошие продажи, трудно прогнозируемый спрос;
• группы AZ и BZ – хорошие продажи, непрогнозируемый спрос;
• CX – невысокие продажи, стабильный спрос;
• CY – невысокие продажи, нестабильный спрос;
• CZ – товары-«темные лошадки» (новинки, спонтанные продажи, индивидуальный заказ и т.д.).

У Екатерины пока что результаты не ахти: ее лидеры продаж находятся в группе товаров с хорошими результатами продаж, но спрос на большинство из них прогнозировать невозможно. Аутсайдер продаж относится к группе спонтанных продаж, по которым данные неточны либо недостоверны.

Отчасти это объясняется не совсем корректно выбранным периодом для анализа: декада – это мало для надежного определения спроса. Поэтому придется накопить немного опыта и времени работы, чтобы располагать более репрезентативной выборкой для проведения анализа.