ABC анализ продаж. Пример расчета в Excel
ABC анализ продаж. Определение
ABC анализ (англ. ABC-analysis) – это метод повышения эффективности и результативности системы продаж предприятия. Наиболее часто метод ABC-анализа используют для оптимизации номенклатуры товара (ассортимента) и его запасов с целью увеличения объема продаж. Другими словами, цель ABC-анализа является выделение наиболее перспективных товаров (или группы товаров), которые приносят максимальный размер прибыли для компании.
Данный вид анализа основывается на закономерности выявленной экономистом Парето: «20% продукции обеспечивают, 80% прибылей компании». Целью компании при проведении такого анализа является определение ключевых товаров, и управление данной 20% группой, которое создаст контроль над 80% денежными поступлениями. Управление продажами и денежными платежами напрямую влияют на финансовую устойчивость и платежеспособность компании.
При проведении анализа продукции все товары делятся на три группы:
Товары компании группы «А» являются целевыми, и требуют максимального внимания к их производству и реализации: наличия их в складских запасов, оперативные поставки, планирование и организация производства и контроль качества данной продукции.
Оценка стоимости бизнеса | Финансовый анализ по МСФО | Финансовый анализ по РСБУ |
Расчет NPV, IRR в Excel | Оценка акций и облигаций |
ABC анализ продаж продукции. Этапы проведения
Этапы проведения ABC-анализа номенклатуры товаров и объема продаж компании (предприятия) следующие:
Пример ABC анализа продаж продукции в Excel
Разберем, как на практике проводить ABC-анализ продаж продукции в Excel для магазина сотовых телефонов. Для этого нам необходимо наличие наименование всех товаров (групп товаров) и их норма дохода. На рисунке ниже показана номенклатура товаров и размер прибыли на каждый из видов.
Номенклатура продукции для проведения ABC-анализа в 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% прибыли.
Преимущества ABC-анализа
Данный метод хорошо себя зарекомендовал на практике, и имеет следующие преимущества:
Другие направления использования ABC-анализа в компании
Спектр использования данного метода повышения эффективности в хозяйственных системах крайне широк:
Недостатки ABC-анализа
Помимо преимуществ методики ей присущи также и недостатки:
Резюме
ABC-анализ продаж позволяет выделить целевые группы товаров, которые обеспечивают 80% прибыли компании. Данный метод повышает эффективность функционирования предприятия, проводит анализ и оптимизацию ресурсов, что в свою очередь отражается на финансовую устойчивости и рентабельности компании. Разобранный пример показывает простоту использования модели ABC для анализа ассортимента товаров и продаж. Метод может широко использоваться в других сферах компании для выделения целевых групп: клиентов, поставщиков, подрядчиков, персонала и т.д.
Автор: к.э.н. Жданов Иван Юрьевич
ABC и XYZ анализ в Excel с примером расчета товарного ассортимента
Для анализа ассортимента товаров, «перспективности» клиентов, поставщиков, дебиторов применяются методы ABC и XYZ (очень редко).
В основе ABC-анализа – известный принцип Парето, который гласит: 20% усилий дает 80% результата. Преобразованный и детализированный, данный закон нашел применение в разработке рассматриваемых нами методов.
ABC-анализ в Excel
Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.
Благодаря анализу ABC пользователь сможет:
Значения в перечне после применения метода ABC распределяются в три группы:
Указанные значения не являются обязательными. Методы определения границ АВС-групп будут отличаться при анализе различных показателей. Но если выявляются значительные отклонения, стоит задуматься: что не так.
Условия для применения ABC-анализа:
Для каких значений можно применять методику АВС-анализа:
Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.
Общая схема проведения:
АВС-анализ товарного ассортимента в Excel
Составим учебную таблицу с 2 столбцами и 15 строками. Внесем наименования условных товаров и данные о продажах за год (в денежном выражении). Необходимо ранжировать ассортимент по доходу (какие товары дают больше прибыли).
Вот мы и закончили АВС-анализ с помощью средств Excel. Дальнейшие действия пользователя – применение полученных данных на практике.
XYZ-анализ: пример расчета в Excel
Данный метод нередко применяют в дополнение к АВС-анализу. В литературе даже встречается объединенный термин АВС-XYZ-анализ.
За аббревиатурой XYZ скрывается уровень прогнозируемости анализируемого объекта. Этот показатель принято измерять коэффициентом вариации, который характеризует меру разброса данных вокруг средней величины.
Коэффициент вариации – относительный показатель, не имеющий конкретных единиц измерения. Достаточно информативный. Даже сам по себе. НО! Тенденция, сезонность в динамике значительно увеличивают коэффициент вариации. В результате понижается показатель прогнозируемости. Ошибка может повлечь неправильные решения. Это огромный минус XYZ-метода. Тем не менее…
Возможные объекты для анализа: объем продаж, число поставщиков, выручка и т.п. Чаще всего метод применяется для определения товаров, на которые есть устойчивый спрос.
Критерии для классификации и характеристика групп:
Составим учебную таблицу для проведения XYZ-анализа.
Что такое ABC и XYZ анализ продаж и как использовать его в бизнесе
Прежде чем повышать продажи и другие величины различными «модными фишками» в любом бизнесе стоит начать с азов, то есть изучения ключевых показателей, и ABC XYZ анализ подходит для этого как нельзя лучше. Простой и универсальный метод легко взять на вооружение, не нужно тратить много времени на вычисления, а полученные результаты успешно применяются в различных отраслях. Спектр применения ABC XYZ анализа в бизнесе практически безграничен. Доля прибыли от клиентов, размер и устойчивость спроса на товары, определение наиболее перспективных продавцов — если понять суть метода, то сосчитать можно всё.
Что такое ABC-анализ
В основу метода положен принцип Парето 20/80. Да-да, тот самый, который уже много лет звучит «из каждого утюга», но от этого не теряет своей эффективности. В применении к этому методу сформулировать его можно, например, следующим образом:
Всего 20% любых товаров, клиентов и т. п. приносят 80% всей прибыли компании.
Но как же определить эти 20% звезд? Именно для этого нужен ABC- анализ продаж. Он позволяет выявить лидеров и сосредоточить на них основные усилия.
В результате анализа товаров по этому методу можно выделить группы:
Цель ABC- анализа выделить приоритетную группу по количественным показателям и сосредоточить усилия на работе с ними.
Эти показатели не являются истиной в последней инстанции и могут варьироваться в зависимости от специфики бизнеса. Но всё же неизменно есть фавориты, которые помогают ему держаться на плаву, и аутсайдеры, отнимающие много усилий, но приносящие минимум прибыли.
Если полученные в результате ABC- анализа показатели на 10-15% отличаются от перечисленных выше, это допустимое отклонение. Как правило, чем больше объектов участвует в анализе, тем ближе результаты к классическим параметрам распределения.
Что такое XYZ-анализ
Что такое ABC- анализ разобрали, но причём тут XYZ? Этот метод часто применяется в дополнение к первому и вместе они создают синергетический эффект.
XYZ- анализ — это метод определения вариативности показателя. То есть с его помощью можно определить, насколько величина имеет свойство отклоняться от средних значений.
Например, при проведении XYZ- анализа продаж с точки зрения спроса на товары можно выделить три категории:
Ц ель XYZ- анализа — определить колебания величины относительно среднего значения и учитывать уровень нестабильности показателя при дальнейшем развитии бизнеса.
Т акая диверсификация крайне оправдана, ведь если товар попал в группу B по средней выручке, но продавался хорошо только в декабре и январе (например, ёлочные игрушки), будет странным делать на него ставку в июле. Не всегда показатели выглядят столь очевидными и заметны невооруженным глазом, но методика XYZ- анализа позволяет выявить такие колебания в любом случае.
Преимущества и недостатки
У совместного ABC XYZ анализа есть следующие преимущества:
Как и любой другой метод, ABC XYZ анализ имеет свои недостатки:
Использование ABC XYZ анализа
Сфер применения ABC XYZ анализа в бизнесе существует множество:
Сквозная аналитика
Как сделать ABC и XYZ анализ
Для того, чтобы провести ABC и XYZ анализ в целом необходимо:
Б ывают ситуации, когда собрать данные за полгода или год в настоящий момент невозможно. Например, отсутствует настолько детальная история покупок. Это хороший повод пересмотреть механизмы учета основных показателей в компании, а к ABC XYZ анализу вернуться, когда на руках будут необходимые для его проведения цифры.
ABC анализ
Приведём пример проведения ABC- анализа клиентов по объему выручки. Порядок действий будет следующим:
Именно по накопленной доле необходимо группировать объекты. Руководствуясь принципом Парето, в первую группу попадут те элементы, у которых этот показатель будет до 80%, а во вторую — до 95%. Оставшиеся составят группу С. По желанию можно добавить цветовую индикацию различных групп для удобства работы с таблицей. 
Полученный результат позволит разделить клиентов по доле в общей выручке на:
Полученный результат совершенно необязательно должен совпадать с фактическим размером компании. Типография за углом может заказывать товары несколько раз в месяц, а крупный холдинг размещать заказы время от времени. Имеет смысл проанализировать причины, по которым клиенты находятся в группе B и С. Возможно, если уделить им больше внимания или немного изменить условия продажи, они поднимутся на уровень выше.
Суммы покупки, от которых начинаются «крупные» клиенты также везде будут разными. Невозможно сравнивать в абсолютных цифрах, например, сферы оптовых и розничных продаж. Для них цифры могут различаться на порядок, например, 1 миллион и 15 тысяч рублей в каждому случае будут крупными.
Технический аудит вашего сайта за 50 рублей
Сервис мониторинга и оптимизации интернет-рекламы
Три дня бесплатного пользования сервисом
Персональное предложение и скидка на подписку
Бесплатный аудит рекламных аккаунтов и скидка 30% на ведение контекстной рекламы
Мониторинг и аналитика социальных медиа
1 месяц YouScan в подарок для новых клиентов
Рекламное агентство с собственной платформой
Бесплатный бот для анализа блогеров и проверки накрутки в Instagram и YouTube
XYZ анализ
Методика проведения XYZ- анализа в E xcel в целом похожа. Но поскольку необходимо искать отклонения от средних показателей, данные потребуется собирать помесячно.
Алгоритм выполнения XYZ- анализа на примере показателей спроса на товар выглядит следующим образом:
Совмещение ABC и XYZ-анализов
Как же использовать ABC XYZ анализ в одном исследовании? Для удобства лучше сразу свести данные в одну таблицу.
Если дело касается товаров (объема продаж и устойчивости спроса) для ABC XYZ анализа пример будет выглядеть следующим образом:
При необходимости можно создать ещё одну таблицу, в которой выделить строки для групп A, B, C и столбцы для X, Y, Z. На пересечение поместить товары, получившие соответствующий индекс. Это позволит представить данные более наглядно.
Что даст такое совмещение коэффициентов? Оно позволит разделить товары уже на девять групп, которые будут иметь следующее значение:
Естественно, что наиболее важное значение для бизнеса будут играть товары категории AX, а вот большая партия товарной групп CZ рискует залежаться на складе надолго в случае падения спроса практически до нуля и нанести компании убытки.
Во многих ситуациях резкое сокращение товарного ассортимента за счет исключения из него товаров группы C неоправданно. Однако обеспечить постоянное наличие на складе товарной группы А необходимо.
Если таким образом провести анализ клиентов, а не товаров, то приоритетной, конечно, также будет группа AX. А вот на тех, кто окажется в колонке AZ, потребуется обратить особое внимание. Если этот контрагент приобретает много, но «от случая к случаю», необходимо периодически напоминать ему о себе. В противном случае он также легко совершит покупку в другом месте, а значимая часть прибыли бизнеса утечет к конкурентам.
Важно актуализировать информацию по результатам ABC XYZ анализа бизнеса примерно раз в полгода. За этот промежуток времени многое изменится: придут новые клиенты, возможно отвалится кто-то из «старичков», появятся новые товары и т. п. Сравнивая данные, можно своевременно предпринимать необходимые действия, например, назначить встречу с клиентом из категории B по предыдущему анализу, который почему-то уже полгода не делал заказы, или вывести из ассортимента товар, который сильно сдал свои позиции.
Если при анализе отчетов наоборот видна позитивная динамика и многие клиенты постепенно продвигаются из категории C к A, при этом объём их заказов становится всё более прогнозируемым, можно смело поздравить себя с отличным результатом. Но не почивать на лаврах, ведь уже завтра ситуация может измениться.
Как выяснить потребности клиента и перевести его в более значимую для компании группу? Стоит уточнить у него с кем из конкурентов он работает и что именно приобретает. Так менеджеры по продажам получат возможность приложить целенаправленные усилия по увеличению доли его покупок.
Заключение
ABC XYZ анализ продаж полезно проводить в любой компании, задействуя также и другие величины — спрос, долю выручки по каждому клиенту, показатели работы с поставщиками и т. п. Чтобы использовать этот инструмент, не потребуется тратить много времени на освоение методики. О таком варианте анализа можно смело сказать, что он «проще паренной репы».
Для проведения ABC XYZ анализа потребуется только Excel и показатели, которые потребуется собрать и занести в таблицу. При этом на сбор информации легко потратить больше времени, чем на сами расчеты.
При использовании важно не принимать полученные результаты как истину в последней инстанции. Необходимо рассматривать каждый товар и каждого клиента по возможности индивидуально без скоропалительных решений. И всё же он помогает избежать одной из главной ошибок предпринимателя — продавать не тем людям не тот товар.
Применение ABC-анализа в Microsoft Excel
Одним из ключевых методов менеджмента и логистики является ABC-анализ. С его помощью можно классифицировать ресурсы предприятия, товары, клиентов и т.д. по степени важности. При этом по уровню важности каждой вышеперечисленной единице присваивается одна из трех категорий: A, B или C. Программа Excel имеет в своем багаже инструменты, которые позволяют облегчить проведение такого рода анализа. Давайте разберемся, как ими пользоваться, и что же собой представляет ABC-анализ.
Использование ABC-анализа
ABC-анализ является своего рода усовершенствованным и приспособленным к современным условиям вариантом принципа Парето. Согласно методике его проведения, все элементы анализа разбиваются на три категории по степени важности:
Отдельные компании применяют более продвинутые методики и разбивают элементы не на 3, а на 4 или 5 групп, но мы будем опираться на классическую схему ABC-анализа.
Способ 1: анализ при помощи сортировки
В Экселе ABC-анализ выполняется при помощи сортировки. Все элементы отсортировываются от большего к меньшему. Затем подсчитывается накопительный удельный вес каждого элемента, на основании чего ему присваивается определенная категория. Давайте на конкретном примере выясним, как указанная методика применяется на практике.
У нас имеется таблица с перечнем товаров, которые предприятие реализует, и соответствующим количеством выручки от их продажи за определенный период времени. Внизу таблицы подбит итог выручки в целом по всем наименованиям товаров. Стоит задача, используя ABC-анализ, разбить эти товары на группы по их важности для предприятия.
Можно также поступить по-другому. Выделяем указанный выше диапазон таблицы, затем перемещаемся во вкладку «Главная» и выполняем щелчок по кнопке «Сортировка и фильтр», расположенной в блоке инструментов «Редактирование» на ленте. Активируется список, в котором выбираем в нем позицию «Настраиваемая сортировка».
В поле «Столбец» указываем наименование той колонки, в которой содержатся данные по выручке.
В поле «Сортировка» нужно указать, по какому конкретному критерию будет выполняться сортировка. Оставляем предустановленные настройки – «Значения».
В поле «Порядок» выставляем позицию «По убыванию».
После произведения указанных настроек нажимаем на кнопку «OK» в нижней части окна.
Учитывая тот факт, что указанную формулу мы будем копировать в другие ячейки столбца «Удельный вес» посредством маркера заполнения, то адрес ссылки на элемент, содержащий итоговую величину выручки по предприятию, нам нужно зафиксировать. Для этого делаем ссылку абсолютной. Выделяем координаты указанной ячейки в формуле и жмем на клавишу F4. Перед координатами, как мы видим, появился знак доллара, что свидетельствует о том, что ссылка стала абсолютной. При этом нужно учесть, что ссылка на величину выручки первого в списке товара (Товар 3) должна оставаться относительной.
Затем, чтобы произвести вычисления, жмем на кнопку Enter.
Итак, в первой строке переносим в столбец «Накопленная доля» показатель из колонки «Удельный вес».
Таким образом, всем товарам, накопленная доля удельного веса которых входит в границу до 80%, присваиваем категорию A. Товарам с накопленным удельным весом от 80% до 95% присваиваем категорию B. Оставшейся группе товаров со значением более 95% накопленного удельного веса присваиваем категорию C.
Таким образом, мы разбили элементы на группы по уровню важности, используя при этом ABC-анализ. При использовании некоторых других методик, о чем говорилось уже выше, применяют разбиение на большее количество групп, но сам принцип разбиения при этом остается практически неизменным.
Способ 2: использование сложной формулы
Безусловно, применение сортировки – это наиболее распространенный способ проведения ABC-анализа в Экселе. Но в некоторых случаях требуется провести данный анализ без перестановки строк местами в исходной таблице. В этом случае на помощь придет сложная формула. Для примера будем использовать ту же исходную таблицу, что и в первом случае.
Задачей данной функции является вывод одного из указанных значений, в зависимости от номера индекса. Количество значений может достигать 254, но нам понадобится всего три наименования, которые соответствуют категориям ABC-анализа: A, B, С. Можем сразу вводить в поле «Значение1» символ «A», в поле «Значение2» — «B», в поле «Значение3» — «C».
Предназначение данной функции – это определение номера позиции указанного элемента. То есть, как раз то, что нам нужно для поля «Номер индекса» функции ВЫБОР.
В поле «Просматриваемый массив» сразу можно задать следующее выражение:
Оно должно быть именно в фигурных скобках, как формула массива. Не трудно догадаться, что эти числа (0; 0,8; 0,95) обозначают границы накопленной доли между группами.
Поле «Тип сопоставления» не обязательное и в данном случае мы его заполнять не будем.
В поле «Искомое значение» устанавливаем курсор. Далее снова через описанную выше пиктограмму в виде треугольника перемещаемся в Мастер функций.
В поле «Диапазон» вводим адрес колонки «Выручка». Для этих целей устанавливаем курсор в поле, а затем, произведя зажим левой кнопки мыши, выделяем все ячейки соответствующего столбца, исключая значение «Итого». Как видим, адрес тут же отобразился в поле. Кроме того, нам нужно сделать данную ссылку абсолютной. Для этого производим её выделение и жмем на клавишу F4. Адрес выделился знаками доллара.
В поле «Критерий» нам нужно задать условие. Вписываем следующее выражение:
Затем сразу же после него заносим адрес первой ячейки столбца «Выручка». Делаем координаты по горизонтали в данном адресе абсолютными, дописав перед буквой знак доллара с клавиатуры. Координаты по вертикали оставляем относительными, то есть, перед цифрой никакого знака быть не должно.
После этого не жмем на кнопку «OK», а кликаем по наименованию функции ПОИСКПОЗ в строке формул.
Далее берем все содержимое поля «Искомое значение» в скобки, после чего ставим знак деления («/»). После этого снова через пиктограмму треугольника переходим к окну выбора функций.
Для наших целей понадобится только поле «Число1». Вводим в него координаты диапазона столбца «Выручка», исключая ячейку, которая содержит итоги. Подобную операцию мы уже проводили в поле «Диапазон» функции СУММЕСЛИ. Как и в тот раз, координаты диапазона делаем абсолютные, выделив их, и нажав на клавишу F4.
После этого жмем по клавише «OK» внизу окна.
Но, конечно, в каждом конкретном случае координаты в данной формуле будут отличаться. Поэтому её нельзя считать универсальной. Но, используя то руководство, которое было приведено выше, можно вставить координаты любой таблицы и с успехом применять данный способ в любой ситуации.
Как видим, результаты, полученные при помощи варианта с применением сложной формулы, ничуть не отличаются от тех результатов, которые мы проводили путем сортировки. Всем товарам присвоены те же самые категории, только при этом строки не изменили своего начального положения.
Программа Excel способна значительно облегчить проведение ABC-анализа для пользователя. Это достигается использованием такого инструмента, как сортировка. После этого производится подсчет индивидуального удельного веса, накопленной доли и, собственно, разбиение на группы. В тех случаях, когда изменение первоначального положения строк в таблице не допускается, можно применить метод с использованием сложной формулы.
Помимо этой статьи, на сайте еще 12476 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
