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

1.17. АВС-, XYZ-сегментация клиентской базы

Скрыть рекламу в статье

1.17. АВС-, XYZ-сегментация клиентской базы

Мы уже говорили о роли и важности клиентской базы. Ее важно вести хотя бы как-то

Но эффективнее вести ее по некоторым правилам. Одно из них – это сегментация базы.

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

АВС-сегментация делит клиентов по объему закупок. Так, А – это самые крупные клиенты, В – средние и С – мелкие. XYZ-сегментация берет за основу частоту покупок. Х – это постоянные клиенты, Y – это нерегулярные клиенты и Z – разовые клиенты. Правило Парето «20/80» работает и в бизнесе. Соответственно, А и Х клиенты – это 20 % базы, которая дает 80 % объема продаж. Сочетание АХ – это так называемые «дойные коровы», т. е. постоянные клиенты с высокой долей покупок. Чем больше таких в базе, тем выше и выгоднее ваши продажи. СZ – это «собаки», т. е. клиенты, которые покупают мало и редко. Скорее всего, они приносят вам больше хлопот, чем прибыли, так как малые объемы не покрывают издержки на работу с ними. АZ – это клиенты-«звезды». Они купили много, но однажды. Остальные сочетания составляют группу «дикие кошки». Этих клиентов можно перевести в категорию «дойные коровы», организуя с ними правильную работу.

Рис. 7. АВС-, XYZ-сегментация клиентской базы

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

Таблица 1

Частота коммуникации с сегментами клиентской базой

Сегментацию базы лучше вести в CRM-системе. Она позволит настроить системные события на каждого клиента. Например, раз в две недели напоминать про звонок клиенту ООО «Типография», который является крупным. И раз в месяц информировать на электронный адрес о новинках клиента ОАО «Колорит», который по сегментации принадлежит к группе Z. Таким образом, вы подберете правильные программы лояльности и точнее попадете в «боль» клиента, увеличив шансы на продажу. Сегментация использует время менеджеров с максимальной эффективностью, одновременно выстраивая стратегию отношений с клиентом должным образом.

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

клиентов, которым вы только собираетесь позвонить. Такая база должна формироваться по критериям. Например, вам выгодно делать поставки только от 50 т. Вы понимаете, что для вашей ниши такие объемы актуальны только для крупного бизнеса с ежемесячным оборотом от 3 млн руб. Соответственно, исключив из базы клиентов, для которых ваше предложение будет финансово неподъемным (например, малый бизнес), вы сэкономите время менеджеров на пустые, заранее нерезультативные звонки.

Оглавление книги

Общие рекомендации для управления запасами по комбинациям ABC/XYZ групп

Рекомендация относительно комбинаций ABC / XYZ:

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

AX, BX, AY, BY и AZ в целом подходят для поставок Just-In-Time (JIT).

Продукты AZ и BZ имеют большую долю в выручке

Однако их трудно контролировать и им нужно уделять особое внимание.

Условия, цели ABC-анализа. Последовательность шагов выполнения ABC-анализа.

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

АВС – анализ стандартными средствами MS EXCEL

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

Примечание

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

Прежде чем, приступить к расчетам ответим на несколько вопросов, которые помогут нам эффективно использовать АВС – анализ.

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

Алгоритм выполнения АВС – анализа:

Сортируем список товаров по убыванию их вклада в выручку.
Формируем столбец с выручкой накопительным итогом (для каждой позиции товара складываем его выручку со всеми выручками от предыдущих, более прибыльных товаров).
Определяем долю выручки для каждого товара накопительным итогом (значения столбца, рассчитанного выше, делим на общую выручку всех товаров). По этому столбцу будем определять границы классов.
Определяем границы классов в долях от выручки. В данном случае используем стандартные значения долей (в %): 80%, 15% и 5%. Т.е. группа наиболее прибыльных товаров должна вносить суммарный вклад в выручку в размере 80%. Все товары, у которых доля выручки накопительным итогом менее или равна 80%, входят в класс А.
Выделяем классы А, В и С: присваиваем значения классов соответствующим товарам.

Теперь реализуем этот алгоритм на листе MS EXCEL (см. файл примера , лист АВС формулами).

Отсортировать список товаров можно с помощью функции РАНГ() – каждому товару будет присвоен ранг в зависимости от его вклада в выручку. Товару, обеспечивающему максимальную выручку, будет присвоен ранг = 1.

С помощью формулы =СУММЕСЛИ($H$7:$H$4699;» формируем столбец с выручкой накопительным итогом. У товара, обеспечивающего максимальную выручку (первый в списке), значение выручки накопительным итогом будет совпадать с его выручкой. У второго товара выручка накопительным итогом будет равна его собственной выручке + выручка первого товара, и т.д.

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

С помощью формулы =ИНДЕКС($N$7:$N$9;ПОИСКПОЗ(J7;$P$7:$P$9;1)) присвоим названия классов каждому товару:

товары, у которых доля выручки накопительным итогом менее или равна 80%, входят в класс А;
товары, у которых доля выручки накопительным итогом более 80% и менее 95% (80%+15%), входят в класс В;
остальные товары принадлежат классу С.

Для наглядности товары, принадлежащие классу А, можно выделить Условным форматированием , а также построить диаграмму Парето (по оси Х указывается количество проданного товара, по оси Y — % выручки накопительным итогом).

Примечание

: Границы классов выделены на диаграмме бордовыми линиями (технически это сделано с помощью горизонтальных и вертикальныхпланок погрешностей ).

Можно также рассчитать сколько позиций товаров входит в каждый класс. Так в класс А входит 342 товара. В класс А входят товары, которые обеспечивают 79,96% выручки (максимальный % меньше 80%). Общая сумма выручки, приходящаяся на эти товары равна 2 116 687,3 руб. Максимальная выручка (у первого товара в классе) равна 76 631,1 руб., а минимальная 1 574,0 руб. (у последнего товара в классе). Часть информации можно найти в таблице в строках расположенных на границах классов (строки 348 и 349).

Как видно из примера, вышеуказанные вычисления, относятся довольно трудоемкими. Есть ли возможность ускорить выполнение АВС-анализа? Безусловно, есть, и одним из решений является надстройка ABC Analysis Tool от компании fincontrollex.com. Ниже рассмотрим ее подробнее.

Примечание

: АВС-анализ относится к числу стандартных и часто используемых инструментов, поэтому он доступен во многих популярных программах бухгалтерского и управленческого учета. Например, в программе «1С: Управление торговлей» (версия 10) существует возможность для проведения анализа клиентов и номенклатуры товаров по следующим параметрам: сумма выручки, сумма валовой прибыли, количество товаров. Причем границы классов не вычисляются, а задаются произвольно, по умолчанию используются значения 80%, 15%, 5%.

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

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

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

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

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

Анализ результатов

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

Американский подход

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

Японский подход

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

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

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

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

Комбинированный ABCXYZ-анализ позволяет увидеть значимость товаров категории AX и бесперспективность ассортимента CZ. Данная классификация позволяет правильно оценить возможности получения дополнительной прибыли и снизить риск возможных убытков.

Соответственно, товары категории A и X должны всегда быть в наличии, а товары Z целесообразнее закупать по мере необходимости.

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

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

В закупках

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

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

В продажах

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

Суть XYZ-анализа

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

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

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

Иногда бывает проще убрать из ассортиментной матрицы товар с нестабильным спросом и сосредоточиться на том, что вызывает устойчивый интерес у потребителей.

Товары категории X – это товары, объемы продаж которых редко выходят за рамки средних показателей. Коэффициент вариаций, то есть отклонения от средних значений, в данном случае не превышает 10 %. Они стабильно востребованы покупателями, и разрабатывать прогнозы продаж таких товаров довольно просто.

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

7 дней назад было продано 20 банок растворимого кофе, три дня назад – 19 банок, сегодня – 20, завтра объем продаж составит примерно 19-20 банок. Отклонений от запрашиваемого количества практически не бывает. Спрос стабилен. Товар причисляется к категории Х.

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

У этих товаров наблюдается отклонение от средней величины продаж. При этом объемы сбыта могут колебаться в пределах 10–25 %.

Если представить себе в качестве примера товара категории Y конфеты ассорти, то ситуация будет примерно следующей: 7 дней назад продали 20 коробок, спустя три дня только 15, сегодня этот показатель был равен – 18, а завтра продажи составят 23 коробки.

Товары категории Z – те, спрос на которые крайне нестабилен. Прогнозировать объемы продаж и определять характер их развития очень сложно.

К категории Z относят новинки, товары подверженные сезонному спросу, позиции, поступившие в соответствии с запросами клиентов, и т. д. Коэффициент вариации этой группы всегда больше 25 %. В некоторых случаях он может превысить даже 100 %.

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

Представленные выше сочетания не являются строго фиксированными. Так же как в случае с АВС-анализом, допускаются иные рамки коэффициента вариации. Для группы Х – это 0-15 %, группы Y – 15-40 %, группы Z – начиная от 40 %.

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

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

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

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

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

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

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

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

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

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

ABC-анализ в Excel с формулами

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

Сортировка выручки

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

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

Чтобы ранжировать клиентов по величине выручки в порядке убывания, нужно повторить такой алгоритм: меню «Данные» → «Сортировка» → «По убыванию» или меню «Данные» → «Сортировка» → «По возрастанию».

Доля каждой позиции

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

=(Выручка от клиента)/(Итоговая сумма выручки)*100%.

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

=(доля клиента за период 1)+(доля клиента за период 2)+ … +(доля клиента за текущий период).

В таблице Excel можно многократно дублировать введенное математическое выражение. С этой целью выделяют диапазон ячеек столбца, в которые следует ввести формулу. При этом заполненная ячейка должна находиться сверху. Затем нажимают комбинацию клавиш CTRL+D.

Определение группы

Расчетные значения накопительных долей разделяют на 3 класса важности, используя правило Парето:

Класс A – клиенты, доля которых в объеме выручки занимает менее 80%.
Класс B – покупатели с накопительной долей от 80 до 95%.
Класс C – покупатели с накопительной долей более 95%.

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

Поэтому при ранжировании часто используют:

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

Выполнение через ABC Analysis Tool

Методы группировки показателей требуют проведения сложных и громоздких расчетов. Специальный плагин Fincontrollex ABC Analysis Tool позволяет автоматизировать этот процесс и посредством нескольких кликов провести профессиональный ABC-анализ в программе Microsoft Excel.

4 вопроса до начала ABC-анализа

Цель. Зачем вы проводите исследование? Увеличить выручку компании, исключить возможность упущенной выгоды и т.п.
Результат. Как вы сможете применить полученные значения? Оптимизируем складские запасы, пересмотрим условия договоров и т.п.
Источники данных. Как вы соберете исходные данные: объект и параметр анализа? Объект анализа — перечень товаров, параметр — выручка в количественном и денежном выражении.
Матрица. Какое АВС XYZ процентное распределение закладывать в расчет? Классический вариант на основе принципа Парето: 80% приносят выручки приносят 20% ключевых клиентов. Чтобы назначить распределение по группам, нужно знать специфику работы компании, жизненные циклы и сезональность. Ошибки в матрице могут привести к тому, что в неприбыльной группе С окажутся важные покупатели с редкими закупками.

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

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

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

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

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

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

В нашем примере АВС-анализа таблица с исходными данными выглядит так.

В таблице содержится различная информация, но для анализа будем использовать только два столбца: Наименование товара и Стоимость.

1. Создадим сводную таблицу для АВС анализа

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

В поле Строки помещаем наименование товаров, а в поле Значения — сумму по полю Стоимость.

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

Как создать сводную таблицу

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

Мы получили список товаров и суммы выручки за каждый из них.

2. Получим доли каждого товара

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

Для этого добавим столбец Стоимость в поле Значения еще раз, просто перетянув его еще раз.

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

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

Для этого щелкните правой кнопкой мыши в любом месте второго столбца с суммой и выберите: Дополнительные вычисления — % от суммы по столбцу.

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

3. Сортируем по убыванию доли выручки

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

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

Для этого щелкнем на фильтре столбца Названия строк (т.е. столбца с наименованиями товаров) и выберем Дополнительные параметры сортировки.

В окне сортировки нужно выбрать переключатель “по убыванию”, и в выпадающем списке выбрать столбец Доля, %.

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

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

4. Получаем долю выручки нарастающим итогом

Как в пункте 2, снова добавляем поле стоимость в поле Значения, и вместо показателя Количество указываем Сумма. Сразу лучше переименовать поле (в примере — Доля нараст. итогом, %).

Теперь опять щелкаем правой кнопкой мыши на любом месте нового поля — Дополнительные вычисления — % от суммы с нарастающим итогом в поле — появляется окно Дополнительные вычисления — нажимаем Ок.

В поле Доля нарастающим итогом считаются доли выручки из предыдущего столбца нарастающим итогом, на картинке показан смысл:

Мы практически достигли цели провести ABC анализ в сводной таблице в Excel. Ведь нам нужно было узнать, какие товары дают примерно 80% выручки, какие — еще 15% (т.е. от 80 до 95%), а какие оставшиеся 5% (от 95 до 100%). И поле “Доля нараст. итогом, %” это показывает.