Abc xyz анализ пример в excel

ABC-анализ в Эксель: теория и рабочий пример

Здравствуйте. Сегодня учимся делать АБЦ анализ в Excel. Начнем с определений. АВС-анализ – это способ классификации ресурсов по степени их влияния на процессы, в которые они вовлечены. Например, товарного ассортимента на коммерческую деятельность. Т.е. определить, какие товары приносят максимальную прибыль, а какие – лишь отнимают операционное время ваших работников.

В основе abc-анализа лежит метод ABC и закон Парето: 20% усилий дают 80% результата. Наша задача – разбить перечень ресурсов на 3 категории:

  • А – суммарная доля в общем результате – 80%
  • B – суммарная доля – еще 15%
  • C – оставшиеся 5%

Давайте сделаем АБС анализ ассортимента по объему продаж за год. Действуем по алгоритму:

    Выгружаем из базы данных продажи за год в разрезе товаров:

Сортируем список по убыванию суммарных продаж

В новом столбце считаем долю каждого товара в суммарных продажах. В каждой строке делим соответствующие продажи на суммарные: =B2/СУММ($B$2:$B$23)

В следующем столбце считаем нарастающую долю. То есть процент данного товара плюс проценты всех предыдущих. В последней строке должно получиться 100%

В последнем столбце определяем категорию с помощью функции ЕСЛИ: =ЕСЛИ(D2

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

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

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

Правильная последовательность выполнения ABC-анализа обычно такая:

  1. Выбрать несколько показателей, по которым будем оценивать процесс. Для ассортимента товаров, пусть это будет выручка, прибыль и количество чеков для каждого товара
  2. Сегментировать процесс, если это необходимо. Например, по географии сбыта, т.к. в разных странах могут отличаться стереотипы, модели поведения при покупке. Очевидно, нельзя иметь одинаковый ассортимент в Европе и Китае, т.к. потребительский спрос там разный. Анализ проводите для каждого сегмента отдельно.
  3. Выгрузить из базы данных численную информацию по товарам и выбранным показателям за длительный период. Обычно, 1-2 года. В нашем случае, получим объемы продаж в единой валюте, прибыль от этих продаж и количество чеков.

Важно: полученные данные должны быть достоверными, «чистыми» и сведёнными до вида «товар –значение». Например, выручка нужна для каждой единицы номенклатуры в виде: «Наименование товара – сумма продаж за год». Если у вас более детальные данные, можно воспользоваться Сводными таблицами для приведения информации в нужный вид.

  • Для каждого показателя отдельно провести классификацию (анализ АБЦ). Я распределю товары на категории три раза: по выручке, по прибыли и по количеству чеков. Получится три отдельные таблицы.
  • Сведите полученные распределения в одну таблицу. Для этого можно использовать сводные таблицы или функцию ВПР. У вас получится так:

    Теперь, сопоставляя собранную информацию, можно делать выводы. Например:

    • Samsung Galaxy J2 Prime – при большой выручке, товар не принес значительной прибыли, было не так много чеков. Данная позиция, возможно, слишком дорогая для данного рынка сбыта и ее приходится распродавать без запаса маржи
    • Xiaomi Redmi Note 5 – при небольшом количестве чеков, принес хорошую выручку и значительную прибыль. Товар оптимален с точки зрения экономики предприятия
    • Xiaomi Redmi 6 – при средней выручке, приносит прибыль и активно покупается. Дешевая модель с высокой рентабельностью, которая распродается массово. Стоит внимательно относиться к запасам данного продукта

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

    Проведенный нами АВС анализ товарного ассортимента – пример того, как правильная манипуляция показателями раскрывает суть процессов, а Microsoft Excel – выступает удобным инструментом для этого.

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

    Кстати, все данные, приведенные в таблицах с примерами – вымышленные, не имеют ничего общего с реальностью, получены с помощью генератора случайных чисел и не отражают объективной реальности для перечисленных продуктов!

    А у меня на этом всё. Если хотите что-то спросить, уточнить или добавить – пишите комментарии!

    Источник: officelegko.com

    ABC XYZ – анализ в Excel одним нажатием клавиши

    Из статьи вы узнаете о бесплатной надстройке Excel для ABC- XYZ- анализа.

    Эта надстройка поможет вам одним нажатием клавиши сделать ABC – анализ и XYZ – анализ.

    Данная надстройка когда-то стала идеей для создания Forecast4AC PRO.

    В статье мы расскажем:

    • О возможностях надстройки для ABC XYZ анализа;
    • Как с её помощью сделать ABC анализ;
    • Как сделать ABC XYZ анализ;
    • И где можно бесплатно скачать надстройку.

    Скачать надстройку вы можете на странице сайта Клуб Закупщиков скачать

    После скачивания открываем надстройку и нажимаем кнопку “Добавить A&Z анализ в меню”. У вас появляется в меню Excel “Надстройки” кнопка “A-Z анализ”.

    Если кнопка не нажимается и меню не появляется, то включаем макросы. Как включить макросы в Excel вы можете прочитать в статье “Как включить макросы в Excel”

    Меню добавили, рассмотрим возможности.

    Начнем с настроек АВС анализа.

    1. Дополнительно к группам ABC можно добавить группы AA, D и E. Для этого ставим галочки для соответствующей группы.
    2. Задать границы для каждой из групп

    Если в группе “AA” стоит 15%, в группе “А” 50%, в “В” 80%, “С” 95%, “D” 99%, то

    В группу “AA” попадут позиции, которые делают значительную часть объема продаж (или другого анализируемого показателя) больше заданной границы, в нашем случае – больше 15% от общего объема. Если вы используете эту группу, то товары, которые в нее попадают, исключаются из ABCDE анализа, и анализ сделается по оставшимся товарам.

    • В группу “А” попадают позиции, которые делают 50% от общего объема продаж (или другого анализируемого показателя).
    • Группа “B” — позиции, которые по объему продаж (или другому показателю) делают от 50% до 80% от общего объема продаж .
    • Группа “C” — позиции, которые по объему продаж (или другому показателю) делают от 80% до 95% от общего объема продаж.
    • Группа “D” —от 95% до 99% от общего объема продаж.
    • Группа “E” —оставшийся 1% от общего объема продаж.

    Теперь рассмотрим настройки для XYZ – анализа:

    О применении XYZ – анализа в прогнозировании мы писали в статье “XYZ – анализ – коэффициент вариации – подготовка данных к прогнозу”

    Перейдем к настройкам.

    1. Как и в ABC анализе, есть возможность задать границы групп XYZ;

    2. А также вместе ABC XYZ анализом, возможно вывести сигму, среднее, коэффициент вариации:

    Перейдем к рассмотрению примеров расчетов анализов с помощью надстройки.

    1. ABC – анализ.

    Для этого данные должны иметь следующее представление (в нашем примере наименование товаров и объемы продаж за 2012 год в столбец, во вложенном файле лист “ABC анализ”):

    Устанавливаем курсор в первую ячейку столбца и нажимаем на кнопку A-Z анализ:

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

    2. ABC XYZ анализ.

    Для этого данные должны иметь следующее представление (1 строка Excel — 1 временной ряд, количество рядов не ограничено, во вложенном файле — лист “ABC_XYZ анализ”):

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

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

    Устанавливаем курсор в левую верхнюю ячейку с данными:

    и нажимаем сочетание клавиш ctrl+shift+end, Excel автоматически выделит область, начиная с левой верхней ячейки и заканчивая правой нижней, как на картинке ниже:

    Теперь нажимаем кнопку “A-Z анализ”, и надстройка для каждого временного ряда делает XYZ – анализ, а также автоматически суммирует данные по каждому ряду и делает ABC – анализ.

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

    • в первом столбце — группы ABC,
    • во втором — группы XYZ,
    • в третьем (если стоит галочка) — сигму,
    • в 4 — среднюю,
    • 5 — коэффициент вариации.

    Итак, одним нажатием клавиши, используя надстройку от Клуба Закупщиков, вы сможете сделать ABC- и XYZ – анализ.

    Надстройку вы можете скачать с сайта Клуб Закупщиков скачать

    После скачивания открываем надстройку и нажимаем кнопку “Добавить A&Z анализ в меню”. У вас появляется в меню Excel “Надстройки” кнопка “A-Z анализ”.

    Присоединяйтесь к нам!

    Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

    • Novo Forecast Lite – автоматический расчет прогноза в Excel .
    • 4analytics – ABC-XYZ-анализ и анализ выбросов в Excel.
    • Qlik Sense Desktop и QlikView Personal Edition – BI-системы для анализа и визуализации данных.

    Тестируйте возможности платных решений:

    • Novo Forecast PRO – прогнозирование в Excel для больших массивов данных.

    Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

    Источник: 4analytics.ru

    ABC и XYZ анализ в Excel с примером расчета товарного ассортимента

    Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).

    В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.

    ABC-анализ в Excel

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

    Благодаря анализу ABC пользователь сможет:

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

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

    1. А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
    2. В – средние по важности (30% – 15%).
    3. С – наименее важные (50% – 5%).

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

    Условия для применения ABC-анализа:

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

    Для каких значений можно применять методику АВС-анализа:

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

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

    Общая схема проведения:

    1. Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
    2. Выполнить сортировку параметров по убыванию.
    3. Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
    4. Найти долю каждого параметра в общей сумме.
    5. Посчитать долю нарастающим итогом для каждого значения списка.
    6. Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
    7. Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
    8. Для С – все, что ниже.
    9. Посчитать число значений для каждой категории и общее количество позиций в перечне.
    10. Найти доли каждой категории в общем количестве.

    

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

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

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

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

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

    Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.

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

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

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

    1. Расчет коэффициента вариации уровня спроса для каждой товарной категории. Аналитик оценивает процентное отклонение объема продаж от среднего значения.
    2. Сортировка товарного ассортимента по коэффициенту вариации.
    3. Классификация позиций по трем группам – X, Y или Z.

    Критерии для классификации и характеристика групп:

    1. «Х» – 0-10% (коэффициент вариации) – товары с самым устойчивым спросом.
    2. «Y» – 10-25% – товары с изменчивым объемом продаж.
    3. «Z» – от 25% – товары, имеющие случайный спрос.

    Составим учебную таблицу для проведения XYZ-анализа.

    Источник: exceltable.com

    XYZ анализ продаж. Пример расчета в Excel

    В статье подробно рассмотрим и разберем на практике XYZ анализ продаж.

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

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

    XYZ анализ ассортимента продукции. Формула расчета

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

    На первом этапе осуществляется расчет коэффициента вариации объема продаж (уровня спроса) по каждой товарной группе. Изменчивость продаж оценивается с помощью коэффициента вариации. Формула расчета коэффициента вариации представляет собой отношение стандартного отклонения к математическому ожиданию объема продаж. Формула имеет следующий вид:

    где:

    σ – стандартное отклонение объема продаж;

    xi – объем продаж в период в i-й период;

    n – количество рассматриваемых периодов продаж товаров;

    х * – среднеарифметическое значение продаж товаров.

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

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

    Группа Коэффициент вариации Характеристика класса
    Группа «X» 0 25% Товары, обладающие случайным спросом

    Видео-урок: “Оценка продаж. Пример XYZ-анализа в Excel”

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

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

    Ассортимент и объем продаж продукции

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

    Коэффициент вариации объемов продаж =СТАНДОТКЛОН(B5:G5)/СРЗНАЧ(B5:G5)

    Расчет коэффициента вариации продаж товаров

    Сейчас необходимо классифицировать товары в группу – “X”,”Y” или “Z”. Для этого напишем формулу определяющую класс товара, и воспользуемся встроенной формулой «ЕСЛИ» в Excel. Формула будет иметь следующий вид:

    Группа товара =ЕСЛИ(H5 Самая обсуждаемая >10 000 просмотров >100 лайков С примером Видео-урок

    Источник: finzz.ru

    Готовый пример АВС-анализа c шаблоном в Excel

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

    Данный пример можно использовать для проведения:

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

    Теоретическая справка

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

    Создайте список всех анализируемых ресурсов

    Первым шагом в проведении ABC — анализа просто перечислите все анализируемые позиции.

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

    Определите показатель, по которому Вы хотите отсортировать позиции.

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

    Отсортируйте все позиции по убыванию продаж/ прибыли

    Определите долю (вклад) каждой позиции

    Данное действие выражается в делении суммы отдельной позиции на общую сумму продаж.

    В отдельном столбце рассчитайте совокупный процент

    Совокупный % считается накопительно, начиная с верхних строк таблицы

    Присвойте каждой позиции соответствующую группу А,В или С

    Выделите группы согласно АВС методу по каждой позиции на основе столбца «Накопительный вклад».

    • Все позиции от начала таблицы до границы 80% — категория А
    • Все позиции от границы 80% до границы 95% — категория В
    • Все позиции от границы 95% до границы 100% — категория C

    Подготовьте выводы по АВС-анализу

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

    1 Группа А – самые важные ресурсы, локомотивы кампании, приносят максимальную прибыль или продажи. Кампания будет нести большие потери при резком снижении эффективности данной группы ресурсов.

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

    2 группа В – группа ресурсов , которые обеспечивают хорошие стабильные продажи/ прибыль кампании.

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

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

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

    Например при анализе ассортимента товаров и услуг могут быть следующие варианты попадания товара в группу C:

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

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

    Источник: powerbranding.ru

    ABC XYZ – анализ в Excel одним нажатием клавиши

    Из статьи вы узнаете о бесплатной надстройке Excel для ABC- XYZ- анализа.

    Эта надстройка поможет вам одним нажатием клавиши сделать ABC – анализ и XYZ – анализ.

    Данная надстройка когда-то стала идеей для создания Forecast4AC PRO.

    В статье мы расскажем:

    • О возможностях надстройки для ABC XYZ анализа;
    • Как с её помощью сделать ABC анализ;
    • Как сделать ABC XYZ анализ;
    • И где можно бесплатно скачать надстройку.

    Скачать надстройку вы можете на странице сайта Клуб Закупщиков скачать

    После скачивания открываем надстройку и нажимаем кнопку “Добавить A&Z анализ в меню”. У вас появляется в меню Excel “Надстройки” кнопка “A-Z анализ”.

    Если кнопка не нажимается и меню не появляется, то включаем макросы. Как включить макросы в Excel вы можете прочитать в статье “Как включить макросы в Excel”

    Меню добавили, рассмотрим возможности.

    Начнем с настроек АВС анализа.

    1. Дополнительно к группам ABC можно добавить группы AA, D и E. Для этого ставим галочки для соответствующей группы.
    2. Задать границы для каждой из групп

    Если в группе “AA” стоит 15%, в группе “А” 50%, в “В” 80%, “С” 95%, “D” 99%, то

    В группу “AA” попадут позиции, которые делают значительную часть объема продаж (или другого анализируемого показателя) больше заданной границы, в нашем случае – больше 15% от общего объема. Если вы используете эту группу, то товары, которые в нее попадают, исключаются из ABCDE анализа, и анализ сделается по оставшимся товарам.

    • В группу “А” попадают позиции, которые делают 50% от общего объема продаж (или другого анализируемого показателя).
    • Группа “B” — позиции, которые по объему продаж (или другому показателю) делают от 50% до 80% от общего объема продаж .
    • Группа “C” — позиции, которые по объему продаж (или другому показателю) делают от 80% до 95% от общего объема продаж.
    • Группа “D” —от 95% до 99% от общего объема продаж.
    • Группа “E” —оставшийся 1% от общего объема продаж.

    Теперь рассмотрим настройки для XYZ – анализа:

    О применении XYZ – анализа в прогнозировании мы писали в статье “XYZ – анализ – коэффициент вариации – подготовка данных к прогнозу”

    Перейдем к настройкам.

    1. Как и в ABC анализе, есть возможность задать границы групп XYZ;

    2. А также вместе ABC XYZ анализом, возможно вывести сигму, среднее, коэффициент вариации:

    Перейдем к рассмотрению примеров расчетов анализов с помощью надстройки.

    1. ABC – анализ.

    Для этого данные должны иметь следующее представление (в нашем примере наименование товаров и объемы продаж за 2012 год в столбец, во вложенном файле лист “ABC анализ”):

    Устанавливаем курсор в первую ячейку столбца и нажимаем на кнопку A-Z анализ:

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

    2. ABC XYZ анализ.

    Для этого данные должны иметь следующее представление (1 строка Excel — 1 временной ряд, количество рядов не ограничено, во вложенном файле — лист “ABC_XYZ анализ”):

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

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

    Устанавливаем курсор в левую верхнюю ячейку с данными:

    и нажимаем сочетание клавиш ctrl+shift+end, Excel автоматически выделит область, начиная с левой верхней ячейки и заканчивая правой нижней, как на картинке ниже:

    Теперь нажимаем кнопку “A-Z анализ”, и надстройка для каждого временного ряда делает XYZ – анализ, а также автоматически суммирует данные по каждому ряду и делает ABC – анализ.

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

    • в первом столбце — группы ABC,
    • во втором — группы XYZ,
    • в третьем (если стоит галочка) — сигму,
    • в 4 — среднюю,
    • 5 — коэффициент вариации.

    Итак, одним нажатием клавиши, используя надстройку от Клуба Закупщиков, вы сможете сделать ABC- и XYZ – анализ.

    Надстройку вы можете скачать с сайта Клуб Закупщиков скачать

    После скачивания открываем надстройку и нажимаем кнопку “Добавить A&Z анализ в меню”. У вас появляется в меню Excel “Надстройки” кнопка “A-Z анализ”.

    Присоединяйтесь к нам!

    Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

    • Novo Forecast Lite – автоматический расчет прогноза в Excel .
    • 4analytics – ABC-XYZ-анализ и анализ выбросов в Excel.
    • Qlik Sense Desktop и QlikView Personal Edition – BI-системы для анализа и визуализации данных.

    Тестируйте возможности платных решений:

    • Novo Forecast PRO – прогнозирование в Excel для больших массивов данных.

    Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

    Источник: 4analytics.ru