Abc анализ продаж. пример расчета в excel

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

Этот метод позволяет определить степень важности каждого элемента логистической цепи

Методика анализа ABC

Методика анализа ABC основана на классификации элементов логистики по степени их влияния на результаты работы

В рамках анализа ABC важные элементы обозначаются как «А», элементы средней важности — как «В», а наименее важные элементы — как «С»

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

Практическое применение анализа ABC

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

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

Пример 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» от Ждановых. Получить доступ

Основные сведения об управлении запасами

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

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

Получи помощь с рефератом от ИИ-шки

ИИ ответит за 2 минуты

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

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

Управление запасами делится на следующие основные этапы:

1. Закупка запасов — приобретение требуемого товара и его доставка на склад предприятия;
2. Хранение запасов — обеспечение сохранности товаров в течение продолжительного периода времени;
3. Получение прибыли от складских запасов — доставка товаров со склада на рынок (или на место производства товаров), их реализация и получение денежного вознаграждения.

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

Этап 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 и XYZ позволяют классифицировать объекты по стоимости, количеству и частоте отгрузок (или заказов). При этом ABC-анализ позволяет узнать текущую ценность каждого клиента, а XYZ – определить регулярность и прогнозируемость закупок (продаж).

Что такое ABC

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

Аналитик ранжирует изучаемые объекты на основе различных параметров: объема затрат, прибыли, рисков

Затем группирует их по классам важности: A, B, C.. Метод ABC основан на принципе Парето, согласно которому 20% усилий обеспечивают 80% результата

Поэтому позиции класса A образуют 80% прибыли и требуют непрерывного наблюдения, т.к. в случае их утраты компания понесет большие убытки. К категории B относят объекты, формирующие следующие 10% результата и требующие средней степени контроля. Позиции класса С малоприбыльны и наименее весомы, поэтому за ними редко наблюдают

Метод ABC основан на принципе Парето, согласно которому 20% усилий обеспечивают 80% результата. Поэтому позиции класса A образуют 80% прибыли и требуют непрерывного наблюдения, т.к. в случае их утраты компания понесет большие убытки. К категории B относят объекты, формирующие следующие 10% результата и требующие средней степени контроля. Позиции класса С малоприбыльны и наименее весомы, поэтому за ними редко наблюдают.

Что такое XYZ

Анализ XYZ – это способ ранжирования товарно-материальных запасов на основе показателей спроса. Методика помогает определить, сколько товаров разместить на складе, чтобы они не залеживались и приносили предприятию прибыль.

Анализ XYZ позволяет определить количество товара на складе.

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

Расширенные версии анализа

Метод ABC помогает не только распределить значения по степени важности, но и оценить частоту заказов (сколько месяцев в году сохраняется спрос на товары). Например, дорогой товар попадает в категорию A при условии, что он пользуется спросом только в течение одного сезона

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

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

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

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

В закупках

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

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

В продажах

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

Что такое ABC-анализ и зачем он нужен бизнесу

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

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

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

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

• А — 20%, которые приносят 80% прибыли;
• В — 20-30% ассортимента, которые дают 15% прибыли;
• С — 50-60%, которые приносят до 5% прибыли.

Возможны статистические погрешности: например, в первую группу попадет 30-40%. Это зависит от количества продукции — чем больше продаем, тем ниже погрешность.

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

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

Совмещение ABC и XYZ-анализов

Как же использовать ABC XYZ анализ в одном исследовании? Для удобства лучше сразу свести данные в одну таблицу.

Если дело касается товаров (объема продаж и устойчивости спроса) для ABC XYZ анализа пример будет выглядеть следующим образом:

• Заполняем таблицу цифрами продаж по каждому товару;
• Выполняем ABC-анализ по алгоритму выше;
• На следующем листе или на этом же в отдельной таблице проводим XYZ-анализ;
• В третьей таблице пишем напротив каждой позиции получившуюся группу для ABC и XYZ.

В отдельную колонку заносим совмещенный индекс, например, AZ или BY.

При необходимости можно создать ещё одну таблицу, в которой выделить строки для групп A, B, C и столбцы для X, Y, Z. На пересечение поместить товары, получившие соответствующий индекс. Это позволит представить данные более наглядно.

Что даст такое совмещение коэффициентов? Оно позволит разделить товары уже на девять групп, которые будут иметь следующее значение:

• AX – приносящие значительную долю выручки со стабильным спросом;
• AY – большая доля выручки, но спрос подвержен колебаниям;
• AZ – хорошо продаются, но спрос плохо поддаётся прогнозированию;
• BX – средние объемы выручки, низкие колебания спроса;
• BY – средние объёмы выручки, колебания спроса в пределах нормы, например, сезонные;
• BZ – средние объёмы выручки, сложно прогнозируемый спрос;
• CX – низкая доля в прибыли, стабильный уровень спроса;
• CY – невысокий уровень прибыли при средних колебаниях спроса;
• CZ – низкий уровень прибыли, высокие колебания спроса.

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

Однако обеспечить постоянное наличие на складе товарной группы А необходимо

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

Если таким образом провести анализ клиентов, а не товаров, то приоритетной, конечно, также будет группа AX

А вот на тех, кто окажется в колонке AZ, потребуется обратить особое внимание. Если этот контрагент приобретает много, но “от случая к случаю”, необходимо периодически напоминать ему о себе. В противном случае он также легко совершит покупку в другом месте, а значимая часть прибыли бизнеса утечет к конкурентам

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

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

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

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

Если при анализе отчетов наоборот видна позитивная динамика и многие клиенты постепенно продвигаются из категории C к A, при этом объём их заказов становится всё более прогнозируемым, можно смело поздравить себя с отличным результатом. Но не почивать на лаврах, ведь уже завтра ситуация может измениться.

Как выяснить потребности клиента и перевести его в более значимую для компании группу? Стоит уточнить у него с кем из конкурентов он работает и что именно приобретает. Так менеджеры по продажам получат возможность приложить целенаправленные усилия по увеличению доли его покупок.

XYZ-анализ в Excel: оценка динамики продаж

Поиск XYZ позволит вам увидеть изменения спроса на продукцию компании.

Выгружаем данные из учётной системы

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

XYZ-анализ в Excel: продажи по кварталам

Рассчитываем коэффициент вариации

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

Создайте столбец «Средние продажи». В строке 3 введите формулу = СРЕДНЕЕ (B3: E3) и скопируйте ее для всех позиций товаров.

XYZ-анализ в Excel: формула для расчета средних продаж

Создайте столбец стандартного отклонения. Стандартное отклонение / средние продажи.

В строке 3 введите формулу = СТАНДОТКЛОН (B3: E3) и скопируйте ее для всех заголовков.

XYZ-анализ в Excel: формула для расчета стандартного отклонения

Создайте столбец вариации,%. Вводим формулу:

Стандартное отклонение столбца / Средние продажи столбца

• XYZ-анализ в Excel: формула для расчета коэффициента вариации
• XYZ-анализ в Excel: рассчитанный коэффициент вариации

XYZ-анализ: таблицы Excel. Пример

Присваиваем значения XZY и соединяем с ABC

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

Группа	Диапазон
X — постоянный спрос	до 15%
Y — волатильный спрос, сезонность	от 15% до 50%
Z — случайный вопрос	более 50%

Классифицируем полученные результаты с помощью функции «ЕСЛИ» программы Excel».

В ячейке J3 введите формулу: = ЕСЛИ (I3 = 50%; «Z»; «Y»)). Копируем формулу для всех товарных условий.

• XYZ-анализ в Excel: группы товаров методом XYZ — формула
• XYZ-анализ в Excel: группы товаров по методу XYZ — результат

XYZ-анализ в Excel: группы товаров по методу XYZ

Создайте столбец группы, используя метод ABC. Извлеките код группы из таблицы синтаксического анализа ABC, используя формулу: = ВПР (A3; ABC! $ A $ 1: $ G $ 12; 7; 0)

Как настроить формулу ВПР:

Задача функции: из кода продукта в исходной таблице найти значение A, B или C и передать его отчетную таблицу XYZ.

A3 — это параметр, по которому мы ищем значение, например «Продукт 6».

ABC! $ A $ 1: $ G $ 12 — ссылка на диапазон исходной таблицы. В нем, строго в первом столбце, должен быть параметр, с которым ищем значение «Товар 6».

7 — порядковый номер столбца, в котором находятся исходные значения (коды A, B, C)

0 — значение ЛОЖЬ. Для Ecxel это признак того, что желаемый результат должен удовлетворять всем 3 предыдущим условиям.

Анализ ABC и XYZ: электронные таблицы Excel

Для каждого продукта мы получаем двойное кодирование анализа ABC и XYZ.

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

В столбце L для каждой строки введите формулу = K & J.

ABC-анализ в сводной таблице Excel

Продукты AX — это товары с высокой маржой, которые приносят 70% дохода. На них есть стабильный спрос.

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

О методике анализа

Выше приведен сравнительный анализ финансового положения и результатов деятельности организации.
В качестве базы для сравнения взята официальная бухгалтерская отчетность организаций Российской Федерации за 2019,
представленная в базе данных ФНС (2.3 млн. организаций).
Сравнение выполняется по 9 ключевым финансовым коэффициентам (см. таблицу выше).
Сравнение финансовых коэффициентов организации производится с медианным значением показателей всех организаций РФ и организаций в рамках отрасли,
а также с квартилями данных значений. В зависимости от попадания каждого значения в квартиль присваивается
балл от -2 до +2 (-2 – 1-й квартиль, -1 – 2-й квартиль, +1 – 3-й квартиль; +2 – 4-й квартиль;
0 – значение отклоняется от медианы не более чем на 5% разницы между медианой и квартилем, в который попало значение показателя).
Для формирования вывода по результатам анализа баллы обобщаются с равным весом каждого показателя,
в итоге также получается оценка от -2 до +2:

	значительно лучше (+1 — +2вкл)
	лучше (от 0.11 до +1вкл)
	примерно соответствует (от -0.11вкл до +0.11вкл)
	хуже (от -1вкл до -0.11)
	значительно хуже (от -2вкл до -1)

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

	значительно улучшилось (положительное изменение более чем на 1 балл).
	улучшилось (положительное изменение менее чем 1 балл);
	не изменилось (балл не изменился или изменился незначительно, не более чем на 0,11);
	ухудшилось (ухудшение за год менее чем на 1 балл);
	значительно ухудшилось (ухудшение за год более чем на 1 балл);

Источник исходных данных: При анализе использованы официальные данные Росстата и ФНС,
публикуемые в соответствии с законодательством Российской Федерации. Если вам доступен оригинал бухгалтерской отчетности,
рекомендуем сверить его с отчетностью ООО «Икс-Игрек-Зет Автоматизация» по данным ФНС, чтобы исключить опечатки и неточности возможные при занесении отчетности в электронную базу налогового ведомства.

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

Внимание: Представленный анализ не свидетельствует о плохом или хорошем финансовом состоянии организации,
а дает его характеристику относительно других российских предприятий. Для детального финансового анализа воспользуйтесь
программой «Ваш финансовый аналитик»
— загрузить данные в программу >>

Возможно ли проанализировать клиентов?

Помимо анализа товарных запасов, совмещенная методика подходит для изучения результатов деятельности отдельных бизнес-единиц, направленной на извлечение прибыли. К таким бизнес-единицам относятся:

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

Их работа может стать предметом анализа на общую прибыльность каждого работника для компании, а также на его вклад в общую копилку продаж.
Клиенты: совмещенный ABC/XYZ-анализ прямо направлен на сегментацию клиентской базы по степени важности и прибыльности клиентов для компании. Это позволяет определить дальнейшую политику сотрудничества с каждой группой покупателей.
Филиалы и подразделения федеральных сетевых компаний

Их эффективность также поддается аналитике и ранжированию.