Как выполнить XYZ-анализ в Excel

Обновлено: 05.07.2024

Я использовал этот шаблон Excel для выполнения анализа.

20 % населения владеет 80 % богатства.
Вы проводите 80 % своего времени с 20 % своих друзей.
20 % ваших клиентов составляют 80 % вашего оборота.
На 20 % ваших поставщиков приходится 80 % ваших покупок.
20 % ваших товаров составляют 80 % вашего оборота.
20 % ваших товаров составляют 80 % ваших запасов.

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

Кривая Парето — пример анализа ABC в индустрии моды

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

Вот полученная нами кривая, где 5 % товаров относятся к коду A, а 80 % — к коду C:

Идея состоит в том, чтобы настроить таргетинг на разные уровни обслуживания в соответствии с классификацией продуктов, что существенно повлияет на вашу стратегию управления запасами. Для продуктов, которые являются основными движущими силами вашего бизнеса, вы можете стремиться к почти 100% уровню обслуживания, чтобы максимизировать выгоды от продаж и удовлетворенность клиентов. Для кодов C вы можете запросить гораздо более низкий уровень обслуживания, чтобы уменьшить свои запасы на широкий спектр продуктов.

Давайте рассмотрим практический пример.

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

Черная футболка – лидер продаж, поэтому она имеет код А. Ваш товар B — оранжевая рубашка, так как она продается меньше. Лишь немногие люди с определенными критериями покупки получат последнюю футболку: это ваш C-код.

< бр />

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

Эти тарифы на услуги будут исходными данными для расчета страхового запаса, определения стратегии повторного заказа и т. д.

ABC-анализ в Excel: пример за 5 шагов

Вот пошаговое руководство по использованию моего шаблона анализа ABC в Excel.

1) Сбор данных из хранилища

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

a) Включите весь портфель продуктов

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

b) Классификация по количеству или стоимости?

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

c) История продаж или прогноз продаж? Какой период использовать?

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

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

Тогда прогноз на 9 месяцев – хороший способ следить за новинками.

Если у вас нет прогнозов, вы все равно можете добавить последние 6 или 12 месяцев.

2) Сортировать товары

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

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

< бр />

Выберите Коды товаров, описание товаров в качестве строк и доход за 12 месяцев в качестве значений.

< бр />

Отобразите сводную таблицу в табличной форме и удалите все итоги, чтобы получить четкий и легко читаемый результат.
Нажмите на дополнительный параметр сортировки и выберите сортировку по убыванию суммы за 12 месяцев.
Добавить столбец ранжирования после сортировки с простой последовательностью 1,2,3….

< бр />

3) Рассчитать совокупный товар и процент оборота

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

Мы делим рейтинг на общее количество элементов:

< бр />

Мы добавляем %оборот товара и совокупный %оборот предыдущего ранжированного товара:

< бр />

4) Настройте ABC-анализ в Excel

Настройте коды ABC: в последнем столбце вставьте формулу «ЕСЛИ».

Если совокупный процент оборота составляет 80 %, то это код C.
Если нет ни одного из двух, это код B.

Подробности этой формулы объясняются в видео и могут варьироваться в зависимости от ваших данных.

Для большей наглядности вы можете включить цвета условного форматирования для каждого отдельного кода.

5) Создайте свою кривую Парето

Чтобы получить график, выберите таблицу и вставьте кривую на вкладке вставки:

% элементов по оси X
% доходов по оси Y

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

Загрузить ABC-анализ Excel

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

Дальше: анализ ABC XYZ

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

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

Основатель AbcSupplyChain | Эксперт по цепочке поставок | 15 лет опыта работы в 6 разных странах –> Следуйте за мной в LinkedIn

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

ABC-анализ в Excel

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

выделите с наибольшим "весом" в общем результате;
анализируйте группы позиций вместо обширного списка;
работать по одному алгоритму с позициями одной группы.

Значения в списке после применения метода ABC разбиты на три группы:

A — самый важный из всех (20% дают 80% результатов).
B – средний по важности (30–15%).
C – наименее важный (50%–5%).

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

Условия использования АВС-анализа:

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

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

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

Метод ранжирования очень прост. Но обрабатывать большие объемы данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает проведение АВС-анализа.

Общая схема:

Определить для целей анализа. Определить объект (какой анализ) и параметр (по какому принципу будет сортироваться по группам).
Сортируйте параметры по убыванию.
Суммировать в числовые данные (параметры - выручка, сумма долга, объем заказов и т. д.).
Найдите долю каждого параметра в общей сумме.
Вычислить долю совокупного итога для каждого значения списка.
Найдите в списке значение, в котором доля нарастающего итога приближается к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
Найдите в списке значение, в котором доля нарастающего итога близка к 95 % (+ 15 %). Это нижний предел группы B.
Для C – все, что ниже.
Подсчитайте количество значений для каждой категории и общее количество позиций в списке.
Найдите общие доли каждой категории.

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

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

Итак, мы закончили ABC-анализ средствами Excel. Дальнейшие действия пользователя – это использование полученных данных на практике.

XYZ-анализ: пример расчета в Excel

Этот метод часто используется в дополнение к ABC-анализу. Комбинированный термин ABC-XYZ-анализ даже встречается в литературе.

Акроним XYZ скрывает до уровня предсказуемости предсказуемость анализируемого объекта. Этот индекс измеряется коэффициентом вариации, который характеризует меру разброса дат вокруг среднего значения.

Коэффициент вариации – относительная мера, не имеющая конкретных единиц измерения. Достаточно информативно. Даже сам по себе. НО! Тенденция, динамика сезонности значительно повышают предсказуемость курса. В результате снижается предсказуемость скорости. Эта ошибка может привести к неправильным решениям. Это огромный минус XYZ-метода. Тем не менее…

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

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

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

Чтобы решить эту проблему, можно ввести анализ ABC XYZ (иногда сокращенно XYZ-анализ).

Анализ XYZ — это основа для классификации продуктов на основе изменчивости спроса на них.

Предметы X = регулярный спрос

Y-товаров = высокая изменчивость спроса

Z-товаров = очень нерегулярный и труднопредсказуемый спрос

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

Как рассчитать анализ запасов ABC XYZ

Изменчивость спроса на товарно-материальные запасы может быть выражена как коэффициент вариации. Таким образом, чтобы разделить ваши продукты на категории X, Y и Z, вам необходимо:

Определите элементы, которые вы хотите включить в анализ.
Рассчитайте коэффициент вариации для каждого элемента, например (стандартное отклонение/среднее значение) * 100.
Отсортируйте элементы по возрастанию коэффициента вариации и суммируйте цифры.
Установите границы для каждой категории.

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

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

От ABC — XYZ-анализа до автоматизации

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

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

< бр />

Анализ XYZ — это способ классификации товаров в соответствии с изменчивостью спроса на них.

X — очень небольшое изменение: товары X характеризуются постоянным оборотом с течением времени. Можно надежно спрогнозировать будущий спрос.
Y – некоторые вариации. Хотя спрос на товары Y не является стабильным, его изменчивость можно в определенной степени предсказать. Обычно это происходит потому, что колебания спроса вызваны известными факторами, такими как сезонность, жизненный цикл продукта, действия конкурентов или экономические факторы. Труднее точно прогнозировать спрос.
Z — максимальное разнообразие: спрос на товары Z может сильно колебаться или возникать спорадически. Отсутствует тенденция или предсказуемые причинные факторы, что делает невозможным надежное прогнозирование спроса.

На следующих диаграммах показаны характеристики трех классов.

Эти классы имеют большое значение для управления запасами. Из-за низкой волатильности спроса управление запасами класса А обычно можно полностью автоматизировать. А из-за предсказуемости спроса небольшой резервный запас может храниться либо самой организацией, либо поставщиком по схеме «точно вовремя» (JIT), что снижает затраты на хранение.

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

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

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

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

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

Читайте также: