Бысто делаем авс-анализ в excel

Примеры использования анализов

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

В закупках

Интегрированный анализ помогает определить правильное покрытие запасов.

Примером использования ABC-XYZ-метода служит таблица, в которой сгруппированы данные о количестве выручки за период и даны прогнозные значения спроса. Если товар класса B приносит небольшой регулярный доход, то менеджер совершает закупку. Напротив, при высоковолатильном спросе на продукт категории A придется увеличить запасы для страхования риска скачка покупательской активности.

В продажах

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

Как работать с дефицитом?

Представим, что мы проводим анализ за последний год. Один товар продавался всё время и обеспечивал прибыль 1000 штук. Другой товар 60 из 360 дней был в дефиците и обеспечил прибыль 950 штук. 20% времени его не было на остатках. Логично предполагать, что, если мы проводим анализ за прошлый период, необходимо делать поправку на дефицит.

Посмотрим на примере:

У нас есть 10 товаров (графа А). По ним есть определённая выручка (графа В) и известно, сколько дней эти товары были в дефиците (графа С). Считаем, какой процент времени товар был в дефиците (графа D). Для этого нужно разделить дни дефицита на тот период, за который мы делаем анализ, а потом скорректировать тот показатель, что у нас есть, на дни дефицита.

Посмотрим формулу:

У нас есть выручка 2600 за 320 дней. Мы можем посчитать, какая была бы выручка по товару за 360 дней. Для этого нужно разделить 360 на (360 — число дней дефицита). Так мы получим скорректированную выручку

Обратите внимание: в формуле есть проверка, что дни дефицита меньше 30%

Корректировать дефицит нужно не по всем товарам. Допустим, у нас есть товар, который был в продаже всего 10 дней, приходил на склад и всё время продавался. У него будет большое количество дефицита (90-95%). Если по нему скорректировать показатель, то он попадёт в очень высокую группу, и к нему будут применены определённые подходы. Но скорее всего, товары, у которых большие дни дефицита, это продажа с колёс, либо позиции под заказ.

При корректировке анализа на дефицит мы рекомендуем делать поправку только по тем товарам, по которым суммарный процент дефицита был меньше определённого параметра. В нашем примере это 30% дней. Если товар меньше чем 30% дней в дефиците, то мы корректируем показатели по нему. Если нет, оставляем изначальные.

Таким образом, мы получили скорректированное значение выручки. Далее делаем то же самое  с ABC анализом. Записываем скорректированную выручку, отсортировываем от большего к меньшему, получаем определённый процент, считаем накопительный процент и разбиваем товары по группам. В общем, делаем то же самое, но с поправкой на дефицит.

В настройках Forecast NOW! есть пункт «Учитывать дефицит при abc анализе». Если его включить, то те показатели, которые можно скорректировать, корректируются. Что касается порога дефицита, мы можем задавать его сами. Это значение дефицита, которому мы доверяем. Обычно эта цифра не меньше 30-50% от всего времени дефицита товаров на складе.

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

Этап 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-анализ

В основу метода положен принцип Парето 20/80. Да-да, тот самый, который уже много лет звучит “из каждого утюга”, но от этого не теряет своей эффективности. В применении к этому методу сформулировать его можно, например, следующим образом:

Всего 20% любых товаров, клиентов и т. п. приносят 80% всей прибыли компании.

Но как же определить эти 20% звезд? Именно для этого нужен ABC-анализ продаж. Он позволяет выявить лидеров и сосредоточить на них основные усилия.

В результате анализа товаров по этому методу можно выделить группы:

• А, куда относятся не более 20% позиций, но приносят они от 70 до 90% дохода;
• В, в которой сосредоточены середнячки, то есть порядка 30% позиций, дающих примерно 20% выручки;
• С — самая многочисленная группа, где обычно оказывается порядка 50% всех реализуемых товаров.

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

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

Если полученные в результате ABC-анализа показатели на 10-15% отличаются от перечисленных выше, это допустимое отклонение. Как правило, чем больше объектов участвует в анализе, тем ближе результаты к классическим параметрам распределения.

Применение ABC XYZ анализ

Специалисты используют ABC XYZ анализ для анализирования прибыли, просматривая различные факторы, которые влияют на нее.

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

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

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

Анализируя клиентскую базу данных с помощью ABC анализа, вы можете условно разделить всех клиентов на 3 группы: A, B, C. То есть на больших, средних и малых соответственно.

При этом нет единого правила, по которому можно произвести условное разделение. Все зависит от самого бизнеса и его объемов продаж. Так например для малого бизнеса к крупным клиентам можно отнести тех, которые приносят вашей компании примерно 150 тыс. рублей. При этом для крупных компаний с такой суммой клиент отнесется к категории C, а в категории А будет список клиентов, которые приносят компании миллионы.

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

За счет применения abc анализа вы можете определить количество целевой аудитории в ваших продажах. Все виды торговли (оптовая, розничная) включают в себя несколько этапов сделки. Процесс покупки товаров или услуг можно представить в виде воронки продаж. Она выглядит как перевернутый конус, разделенный на уровни.

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

ABC анализ помогает вам увидеть сколько клиентов приобрели у вас товар, каким образом они узнали о вашей продукции и компании, кто помог им совершить покупку из ваших сотрудников и т. п.

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

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

Чтобы поддержать достигнутый уровень получения прибыли от них, вам следует провести ряд мероприятий.

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

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

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

https://youtube.com/watch?v=tHU_LP_Zt2s

XYZ анализ. Определение

XYZ анализ ­(англ. XYZ-analysis) – это метод классификации ресурсов компании по трем группам на основе их изменчивости (устойчивости). Объектами применения XYZ-анализа могут выступать различные экономические показатели компании: объем продаж, выручка, материальные затраты, количество поставщиков и т.д. Одним из самых распространенных направлений применения данного метода является определение товаров, которые имеют устойчивый спрос (объемы продаж), сезонный и случайный. Это позволяет произвести оптимизацию складских запасов и высвободить дополнительные ресурсы.

АВС-анализ товарного ассортимента в Excel

Составим учебную таблицу с 2 столбцами и 15 строками. Внесем наименования условных товаров и данные о продажах за год (в денежном выражении). Необходимо ранжировать ассортимент по доходу (какие товары дают больше прибыли).

1. Отсортируем данные в таблице. Выделяем весь диапазон (кроме шапки) и нажимаем «Сортировка» на вкладке «Данные». В открывшемся диалоговом окне в поле «Сортировать по» выбираем «Доход». В поле «Порядок» – «По убыванию».
2. Добавляем в таблицу итоговую строку. Нам нужно найти общую сумму значений в столбце «Доход».
3. Рассчитаем долю каждого элемента в общей сумме. Создаем третий столбец «Доля» и назначаем для его ячеек процентный формат. Вводим в первую ячейку формулу: =B2/$B$17 (ссылку на «сумму» обязательно делаем абсолютной). «Протягиваем» до последней ячейки столбца.
4. Посчитаем долю нарастающим итогом. Добавим в таблицу 4 столбец «Накопленная доля». Для первой позиции она будет равна индивидуальной доле. Для второй позиции – индивидуальная доля + доля нарастающим итогом для предыдущей позиции. Вводим во вторую ячейку формулу: =C3+D2. «Протягиваем» до конца столбца. Для последних позиций должно быть 100%.
5. Присваиваем позициям ту или иную группу. До 80% – в группу А. До 95% – В. Остальное – С.
6. Чтобы было удобно пользоваться результатами анализа, проставляем напротив каждой позиции соответствующие буквы.

Вот мы и закончили АВС-анализ с помощью средств Excel. Дальнейшие действия пользователя – применение полученных данных на практике.

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

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

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

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

Резюме

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ABC-анализ применяется в бизнесе для следующих целей

• ABC-анализ товаров отдельного бренда или всего товарного ассортимента фирмы (по прибыли, по марже, по доходу);
• АВС-анализ клиентской базы, групп потребителей (по объему заказов в штуках, рублях, марже, прибыли);
• АВС-анализ базы поставщиков по объему поставок;
• АВС-анализ компаний с дебиторской задолженностью (по сумме задолженности);
• АВС-анализ запасов товаров торговой компании в штуках, тоннах и т.п.;
• АВС-анализ сырья и любых закупаемых материалов;
• АВС-анализ эффективности работы подразделений и анализ трудовых ресурсов;
• В логистике ABC-анализ применяют с целью отслеживания объёмов отгрузки определённых артикулов и частоты обращений к той или иной позиции ассортимента;
• АВС-анализ бюджета, финансового плана, инвестиций или любых других затрат.

ABC анализ клиентов и по поставщикам

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

Что нужно сделать? Выбираем склад, разбиваем товар по поставщикам, проводим анализ по заданным критериям.

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

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

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

Бывает так, что клиент ставит жёсткие условия, выставляет просрочки, штрафы за непоставку, а прибыли для вас обеспечивает всего 2%. Может быть, проще от этого клиента отказаться и предложить более выгодные условия тому, от которого вы получаете больше выручки?

АBC анализ довольно гибкий инструмент. Главный принцип abc анализа в том, что его можно проводить по товарам, по поставщикам, по клиентам

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

Оценка метода касательных

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

К преимуществам данного метода можно отнести следующие:

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

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

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