Abc и xyz анализ в excel с примером расчета товарного ассортимента

Отчеты в Декалитрах (ДАЛ) для 1С: Розница 2.2.4 — 2.3.7: Продажи, Поступления, Анализ движения номенклатуры. Движение по документам ЕГАИС

На данный момент в конфигурации 1С:Розница 2.2 есть один большой минус — это наличие реквизита в Номенклатуре «ОбъемДАЛ», а собственно, отсутствие отчетов с его использованием! Как всегда, находятся маленькие магазинчики, продающие алкогольную продукцию, и сопровождающиеся какой-либо бухгалтерской компанией, соответственно ни о каком обмене с бухгалтерией у них и речи идти не может, а бухгалтеру все-таки хочется упростить себе жизнь и не высчитывать декалитры каждый квартал на калькуляторе.
Исходя из повстречавшейся мне аналогичной ситуации возникла необходимость создать несколько отчетов с выводом информации по Производителям алкогольной продукции, Кодам АП и Номенклатуре в декалитрах.
Поэтому представляю вашему вниманию отчёты по выше указанным параметрам: Анализ движения номенклатуры (Приход/Расход), Продажи и Ведомость по продажам товаров. Так же реализованы отчёты по документа ЕГАИС в декалитрах.

1 стартмани

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% от выручки.

Этап 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-подходу.

Методика АВС анализа

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

Программы для проведения ABC анализа — Excel, Google Таблицы, SAP, 1С.

Как структурировать информацию для ABC анализа

1. Данные в таблицах выстраивают в порядке убывания. 
2. Определяют долю каждой позиции в общем объеме.
3. Складывают доли и объединяют в группы по заранее утвержденным признакам А, В и С. 

В XIX веке экономисты сформулировали правило: 20% покупателей приносят 80% прибыли. Оно легло в основу метода ABC анализа.

Классический ABC анализ состоит из трёх пропорций:

Код группы	Объект	Параметр
А — самые ценные	20% всех запасов/клиентов	80% валового дохода
В — средние	30% всех запасов/клиентов	15% валового дохода
С — наименее ценные	50% всех запасов/клиентов	5% валового дохода

Для каждого кода соотношение объекта и параметра может быть любым. Главное в этих утверждениях не числовые значения, а сам факт сильного различия групп между собой. При выборе матрицы нужно учитывать особенности бизнеса, ассортимент, динамику продаж конкретных позиций. Могут также понадобится дополнительные группы:

D — новый товар. По нему еще не сформировалась статистика продаж. Его нельзя привязывать к группам А, В, С т.к. это исказит результат. 

Е — уникальный единичный товар, который выпускали по отдельным заказам клиентов.

О — в исследуемом периоде товар убрали из матрицы. Приходов на склад и продаж больше не планируют.

ABC и XYZ анализ: суть, методика, расшифровка результатов

Недостатки АВС анализа

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

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

Чтобы исключить такие ошибки в интерпретации данных к выгрузке ABС добавляют показатель динамики продаж.

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

ABC-анализ является методом, посредством использования которого становится возможным произвести классификацию ресурсов организации по степени их значимости. Представленный метод считается одним из основных методов рационализации, причём, применять его можно в сфере деятельности совершенно разных предприятий.

Основой ABC-анализа является принцип Парето, гласящий о том, что 20% действий приносят 80% результата, а 80% действий приносят лишь 20% результата. Применительно к теме бизнеса, данный принцип можно выразить так: 20% всей продукции дают компании 80% оборота финансовых средств.

Если же соотнести принцип Парето с ABC-анализом, то получим следующее выражение: умение надёжно и должным образом контролировать 20% позиций позволяет держать под контролем 80% всей системы, и не важно: продуктовая это продукция, детские игрушки, медикаменты, строительные материалы, комплектующие, сырьё или что-либо другое. ABC-анализ представляет собой анализ товарных запасов через разбиение их на три основные категории:

• Категория A – самые ценные товары (в этом случае 20% их ассортимента приносят 80% прибыли)
• Категория B – товары промежуточного звена (в этом случае 30% их ассортимента приносят 15% прибыли)
• Категория C – товары, имеющие наименьшую ценность (в этом случае 50% их ассортимента приносят всего лишь 5% прибыли)

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

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

Также важно отметить, что ещё одним основополагающим принципом ABC-анализа является принцип дисбаланса, который позволяет построить график зависимости общего эффекта от числа всех отдельных элементов. Этот график имеет несколько названий: «кривая Парето», «кривая Лоренца» или «ABC-кривая»

Недостатки ABC-анализа

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

Помимо преимуществ методики ей присущи также и недостатки:

1. Одномерность метода. ABC-анализ довольно простой аналитический метод и не позволяет группировать сложные многомерные объекты.
2. Группировка товаров только на основе количественных показателей. Метод не строится только на количественной оценке нормы дохода по каждой номенклатуре товара и не оценивает качественную составляющую каждого товара, например, товары различной категории.
3. Отсутствие группы убыточных товаров. Помимо товаров приносящих прибыль компании есть также товары, которые приносят убыток. В данном методе такие товары не отражены, в результате на практике, ABC-анализ трансформируется в ABCD анализ, где в группу “D” входят нерентабельные группы товаров.
4. Влияние внешних факторов на продажи. Несмотря на довольно устойчивую структуру продаж по данной модели, на оценку объема продаж в будущем сильное влияние оказывают внешние экономические факторы: сезонность, неравномерность потребления и спроса, покупательная способность, влияние конкурентов и т.д. Влияние данных факторов не отражается в модели ABC-анализ.

Резюме

ABC-анализ продаж позволяет выделить целевые группы товаров, которые обеспечивают 80% прибыли компании. Данный метод повышает эффективность функционирования предприятия, проводит анализ и оптимизацию ресурсов, что в свою очередь отражается на финансовую устойчивости и рентабельности компании. Разобранный пример показывает простоту использования модели ABC для анализа ассортимента товаров и продаж. Метод может широко использоваться в других сферах компании для выделения целевых групп: клиентов, поставщиков, подрядчиков, персонала и т.д.

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

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

Виды АВС-анализа

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

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

О методе ABC-анализа

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

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

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

Выполнение ABC-анализа

ABC-анализ предполагает такую последовательность действий:

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

ABC-анализ выполняется пошагово в определённой последовательности

Для примера приведём АВС-анализ клиентской базы компании ООО «Альфа». В качестве инструмента воспользуемся табличной программой Excel.

Выполним АВС-анализ:

1. Ставим цель — ранжировать клиентов из базы по степени их прибыльности.
2. В качестве объекта анализа выбираем 20 клиентов фирмы, которых анонимно обозначим от Клиент 01 до Клиент 20.
3. В качестве параметра анализа рассмотрим сумму покупок каждого клиента за полугодие.
4. Сопоставим каждого клиента с суммой выручки, полученной от него за полугодие, и создадим исходную таблицу Excel, содержащую всего два столбца: А — перечень клиентов, В — выручка за полугодие. Подводим в отдельной строке итог выручки.
5. Отсортируем клиентов в порядке убывания выручки за полугодие (меню «Данные» → «Сортировка» → «По убыванию»).
6. Определим долю каждого клиента в итоговой сумме выручки компании за полугодие по формуле: Доля = (Выручка от клиента) / (Итоговая сумма выручки) * 100%. Чтобы не заводить формулу вручную каждый раз, задаём столбцу С процентный формат ячеек, в первой ячейке (С2) задаём формулу =B2/$B$22, протягиваем до последнего столбца.
7. Рассчитаем накопительную долю для каждого покупателя. В первой строке дублируется процентная доля клиента, в последующих значение вычисляется суммированием этой доли и процентной доли текущего клиента. Технически это выглядит так: во второй ячейке столбца Е задаём формулу =C3+Е2, протягиваем до последней строки.
8. Получим список клиентов, отсортированный по накопительной доле каждого клиента. Для контроля: в последней строке (в нашем случае 21) должно стоять значение 100%.
9. Разделим список, отражающий накопительные доли, на три группы:
   • А — клиенты с наибольшими объёмами покупок. Их накопительная доля — до 80%. В эту группу вошли 5 клиентов;
   • В — клиенты, для которых значение накопительной доли составляет от 80 до 95%. В эту группу вошли 6 клиентов;
   • С — остальные 9 клиентов, накопительная доля которых более 95%.
10. Подсчитаем долю общей выручки и процент от общего числа клиентов в каждой группе. На практике доля объектов в группах А, В и С не всегда точно соответствует теоретическому значению по Парето. Так, ценные 20% клиентской базы должны составлять четыре клиента, а по итогам расчётов их оказалось 5, то есть 25%. Но по расчётам видно, что они дают компании 80% выручки. Так же и с группой С. Это не следует считать ошибкой расчёта. По законам статистики ближе к теоретическому итогу можно подойти с увеличением количества объектов, например, если клиентов будет не 20, а 500.

Преимущества и недостатки

У совместного ABC XYZ анализа есть следующие преимущества:

• Широкий спектр применения. Его можно использовать для расчета показателей продаж, определения ключевых клиентов, спроса на товар, вклада продавцов в командную работу и т. п.
• Простота. Выполнить расчёты несложно и для них не нужно десять аналитиков — реально обойтись своими силами. Понадобится только таблица в Excel или любой другой подобной программе. Есть CRM-системы, которые считают заданные показатели в автоматическом режиме. При правильной настройке останется только нажать кнопку, чтобы экспортировать данные в нужном формате.
• Эффективность. Это базовое исследование, которое время от времени стоит повторять в каждой организации.

Как и любой другой метод, ABC XYZ анализ имеет свои недостатки:

• И снова простота. Он не даёт возможности составить всестороннюю картину.
• Не стоит использовать как прямое руководство к действию, например, тут же расставаться с клиентами, совершающими редкие покупки или избавляться от товаров со случайным спросом. Часто требуется дополнительный анализ другими методами.

Использование ABC XYZ анализа

Сфер применения ABC XYZ анализа в бизнесе существует множество:

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

Сквозная аналитика

 от 990 рублей в месяц

• Автоматически собирайте данные с рекламных площадок, сервисов и CRM в удобные отчеты
• Анализируйте воронку продаж от показов до ROI
• Настройте интеграции c CRM и другими сервисами: более 50 готовых решений
• Оптимизируйте свой маркетинг с помощью подробных отчетов: дашборды, графики, диаграммы
• Кастомизируйте таблицы, добавляйте свои метрики. Стройте отчеты моментально за любые периоды

Узнать подробнее

АВС-АНАЛИЗ АССОРТИМЕНТА ПРОДУКЦИИ С ПОМОЩЬЮ EXCEL

Табличный редактор Excel содержит необходимые для АВС-анализа формулы работы с исходными данными ЕСЛИ, ВПР/ГПР и функционал сортировки табличных данных.

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

• какой ассортимент продукции в компании приоритетен (группа А) и требует повышенного внимания;

• какая часть ассортимента продукции находится в средней группе значимости (группа В);

• какая часть ассортимента является малозначимой и может быть без ущерба выведена из линейки выпускаемой продукции (группа С).

Алгоритм АВС-анализа ассортимента продукции с помощью табличного редактора Excel можно представить в виде цепочки последовательных шагов (рис. 1).

Рассмотрим эти шаги подробнее.

Шаг 1. Выгружаем данные об ассортименте продукции в файл Excel.

На этом этапе:

Шаг 2. Рассчитываем показатели структуры ассортимента продукции.

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

Значение показателя ассортиментной единицы / Общая сумма показателей ассортиментных единиц × 100 %.

В результате по каждому добавленному столбцу получаем значение удельного веса (в процентах) каждой ассортиментной единицы анализируемой продукции.

Шаг 3. Сортируем ассортимент продукции по группам.

Третий этап АВС-анализа состоит из двух частей:

1. Сначала выводим группу значимости для каждой ассортиментной единицы продукции.

Для этого добавляем в аналитическую таблицу еще одну колонку и в ее ячейке по строке первой ассортиментной единицы прописываем расчетную формулу с использованием функции ЕСЛИ

Обращу внимание читателей на то, что нам нужно получить данные не по двум, а по трем групповым значениям (А, В и С), поэтому формула будет составная:. =ЕСЛИ(Значение ячейки с удельным весом ассортиментной единицы>Нижняя граница группы А в%;»A»;ЕСЛИ(Значение ячейки с удельным весом ассортиментной единицы >Нижняя граница группы В в%;»B»;»C»))

=ЕСЛИ(Значение ячейки с удельным весом ассортиментной единицы>Нижняя граница группы А в%;»A»;ЕСЛИ(Значение ячейки с удельным весом ассортиментной единицы >Нижняя граница группы В в%;»B»;»C»))

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

Например, я для рассматриваемого примера АВС-анализа взял такие параметры:

• ассортиментные единицы продукции, удельный вес которых выше 5 %, относятся к группе А;

• ассортиментные единицы продукции, которые находятся в диапазоне выше 2,5 % и до 5 % относятся к группе В;

• соответственно, в группе С окажутся те ассортиментные единицы продукции, удельный вес которых не больше 2,5 %.

2. Ранжируем ассортиментные единицы продукции по указанным группам.

Для этого выделяем на листе Excel диапазон данных, который собираемся сортировать, и выбираем в меню редактора путь Данные → Сортировка. В открывшемся диалоговом окне выбираем:

• Столбец → Сортировать по (номер или буквенное обозначение столбца, в котором расположены обозначения групп значимости, которые мы присвоили ассортиментным единицам в первой части этого этапа);

• Сортировка — Значения ячеек;

• Порядок — От А до Я (потому что в выбранном столбце у нас указаны А, В, С).

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

Анализ складских запасов ABC/XYZ: зачем нужен

Торговая организация из месяца в месяц закупает примерно одни и те же товары. Одни продаются слету. Другие пылятся на полках витрин и заполоняют складские помещения.

Производственное предприятие сменило техпроцесс. Из-за этого потребность в одном из материалов сократилась в разы. Но снабжение им продолжается в прежнем объеме.

И тот, и другой пример говорят о неэффективных контроле и управлении запасами. Кажется, это же очевидно:

• не покупай то, что плохо продается;
• не бери то, в чем нет надобности.

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

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

Чтобы провести анализ, потребуются:

• для товаров – отчет о продажах;
• для материалов – о списании в производство.

Вот еще несколько советов по исходной информации:

• отчеты нужны в разрезе номенклатурных позиций;
• хорошо, чтобы кроме рублевых значений были данные еще и в натуральных величинах. Это повысит точность результата для XYZ-методики;
• для XYZ понадобятся данные за несколько периодов. Например, за год, но в помесячном разрезе или поквартальном. Чем короче период и чем их больше по количеству, тем надежнее итог вычислений;
• отличной заменой специальных отчетов о продажах и списании в производство являются карточки счета из программы 1С. Для товаров по 41-му, для материалов по 10-му. Сформируйте их с развернутой аналитикой и смотрите в кредитовый оборот. Именно там отражается выбытие ценностей.

АВС/XYZ-анализ реализуется в три этапа. Сначала последовательно применяется каждый метод – АВС и XYZ. Затем полученные результаты сводятся воедино и становятся основой для принятия управленческих решений.

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