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

№25 Копирование листа

Скопирован лист 2 в третий

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

Для этого надо нажать правой кнопкой мыши на открытый лист и одновременно зажать клавишу Control.

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

Заключение

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

Многие из них значительно помогают в процессе, некоторые просто незаменимы.

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

5 Интересных Трюков в Excel

ТОП-25 секретов и возможностей Excel: Самые полезные лайфхаки для работы

9.5 Оценка

Возможности Excel

УДОБСТВО

9

ЭФФЕКТИВНОСТЬ

10

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

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

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

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

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

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

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

Теперь нам хорошо видно, что первые 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 для более глубокого понимания процессов.

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

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

Microsoft Excel. Готовые решения — бери и пользуйся

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

По признанию автора, книга содержит 20% знаний об Excel, которые помогают решить 80% каждодневных задач.

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

Курсы и уроки Excel — от Базовых основ до Продвинутого уровня и программирования на VBA!

Пример 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% прибыли.

Построение матрицы БКГ

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

1. Сбор исходных данных

Первым делом необходимо составить список тех товаров, подразделений или компаний, которые будут анализироваться с помощью матрицы БКГ. Затем для них нужно собрать данные по объемам продаж и/или прибыли за некоторый период (допустим, за прошедший год). Кроме того, понадобятся аналогичные данные по продажам ключевого конкурента (или ряда крупнейших конкурентов). Для удобства, данные желательно представить в форме таблицы. Так их будет легче обрабатывать.

Первым делом необходимо собрать все исходные данные и сгруппировать их в виде таблицы.

2. Вычисление темпа роста рынка за год

На этом этапе нужно рассчитать годовое увеличение объемов продаж (выручки) или объемов прибыли. Как вариант можно рассчитать и увеличение выручки, и увеличение прибыли за год, а затем вычислить среднее значение. В общем, наша задача здесь – рассчитать темп роста рынка. Например, если в прошлом году условно было продано 100 шт. товара, а в этом году – 110 шт., то темп роста рынка составит 110%.

Затем для каждого анализируемого продукта (подразделения) вычисляется темп роста рынка.

3. Вычисление относительной доли рынка

Рассчитав темп прироста рынка для анализируемых продуктов (подразделений) необходимо для них же подсчитать относительную рыночную долю. Способов сделать это несколько. Классический вариант – взять объем продаж анализируемого продукта компании и поделить ее на объем продаж аналогичного продукта главного (ключевого, сильнейшего) конкурента. Например, объем продаж нашего товара – 5 млн. руб., а сильнейшего конкурента реализующего подобный товар – 20 млн. руб. Тогда относительная доля рынка нашего товара будет – 0,25 (5 млн. руб. делим на 20 млн. руб.).

Следующий шаг – вычисление относительной доли рынка (по отношению к главному конкуренту).

4. Построение матрицы БКГ

На четвертом последнем этапе производится собственно построение матрицы Бостонской консалтинговой группы. Из начала координат проводим две оси: вертикальную (темп роста рынка) и горизонтальную (относительная доля рынка). Каждая ось делится пополам, на две части. Одной части соответствуют низкие значения показателей (низкий темп роста рынка, низкая относительная доля рынка), другой – высокие (высокий темп роста рынка, высокая относительная доля рынка). Важный вопрос, который здесь предстоит решить, какие значения темпа прироста рынка и относительной доли рынка принять за центральные значения, делящие оси матрицы БКГ пополам? Стандартные значения следующие: для темпа роста рынка – 110%, для относительной доли рынка – 100%. Но в Вашем случае эти значения могут быть другими, нужно смотреть на условия конкретной ситуации.

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

Таким образом, каждая ось делится пополам. В результате образуется четыре квадратных сектора, каждый из которых имеет свое название и значение. Об их анализе мы поговорим позже, а пока следует нанести на поле матрицы БКГ анализируемые товары (подразделения). Для этого последовательно отмечайте на осях темп роста рынка и относительную долю рынка каждого товара, и на пересечении этих значений рисуйте окружность. В идеале диаметр каждого такого круга должен быть пропорционален прибыли или выручке, соответствующей, данному товару. Так можно сделать матрицу БКГ еще более информативной.

ABC анализ

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

В основе ABC анализа лежит идея Парето. Он утверждал, что вкладывая 20 % вы можете получить 80

На это стоит обратить внимание и двигаться в правильном направлении

При использовании такого анализа анализируемые объекты условно делятся на 3 вида:

1. A — самые важные элементы, которые составляют 20% от всех и от которых вы получаете 80% результата.
2. B – средние элементы. К ним мы относим 30% от общей массы, и они могут принести вам 15% прибыли.
3. С — остальные элементы. Оставшиеся 50%, которые приносят только 5%.

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

Вывод ABC анализа — разделение анализируемых элементов на группы по степени влияния на работу бизнеса.

Цель ABC анализа — разделение объектов по приносимым ими результатов.

ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами

Ассортиментный ABC анализ проведем на примере компании по продаже запасный частей для сельскохозяйственной техники.

Количество товара — более 5 000 позиций. Объединяем их в группы по видам номенклатуры.

Из учетной системы выгружаем данные за 2020 год:

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

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

Сортировка выручки по убыванию

Выделяем диапазон ячеек: вся таблица вместе с заголовками без строки «Итого».

В ниспадающем меню выбираем:

Данные — Сортировка — Сортировать по:

• столбец «Выручка»
• сортировка «Значения»
• порядок «По убыванию»

Система выстраивает таблицу по убыванию размера выручки в столбце D.

Доля каждой строки в общем параметре

Определяем долю каждой номенклатуры в выручке:

• добавляем графу Доля (Е). Формат ячеек процентный;
• в строку 2 для товара 6 вводим формулу: выручка товара 6 / выручка итого;
• протягиваем формулу вниз по всем товарам.

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

Символ & предупреждает Excel, что формулу нельзя двигать:

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

Определяем группу

Создаем графу Группа. Каждому товару присваиваем значения А, В, С в зависимости от доли в выручке.

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

Группа	Диапазон
A	до 70%
B	70-90%
C	90-100%

В ячейке G2 прописываем формулу =ЕСЛИ(F2 =90%;»C»;»В»)) . Протягиваем формулу вниз по всем товарам.

В примере для наглядности проценты заданы цифрами.

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

В столбце G каждой номенклатурной группе присвоен код А, В, С.

В группу А попали товары, которые приносят основную прибыль.

В группу В — продукция компании, на которую нерегулярный спрос.

Группа С — товары, которые зарабатывают только 10% от выручки.

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

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

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

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

Плюсы и минусы АВС-анализа

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

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

Преимущества ABC-анализа

Простота

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

Прозрачность

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

Универсальность

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

Автоматизация

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

Оптимизация ресурсов

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

Недостатки ABC-анализа

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

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

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

№18 Выпадающий список

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

Можно оптимизировать процесс и не делать этого постоянно.

Выпадающий список

Выпадающие списки создаются в качестве своеобразного шаблона.

Он заполняется только изменяющимися данными.

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

Теперь осуществляем переход ко второй таблице, к которой будет применяться выпадающий список. Надо открыть вкладку «данные» на верхней панели и выбрать пункт «проверка данных».

Появится окошко, где надо будет ввести данные. «Тип данных» предполагает в данном случае название «список».

А там, где источник, обязательно через знак равно пишется имя, которое было дано диапазону. Жмем «Ок».

Теперь появляется во второй таблице выпадающий список. Он выглядит как стрелочка на шапке таблицы.

Нажимая на нее, пользователь выбирает подходящее значение.

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

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

Для чего необходим анализ ABC и XYZ

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

В совокупности эти виды анализа помогают оценить:

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

Методы ABC и XYZ помогают оценить маркетинговый план.

Как выглядит совместное применение

Методы ABC и XYZ одинаково важны при планировании закупок и продаж, поэтому их часто объединяют в ABC-XYZ-анализ. На практике это приводит к формированию 9 классов товарных позиций: AX, AY, AZ, BX, BY, BZ, CX, CY, CZ

Комбинирование помогает скорректировать показатели важности относительно прогнозируемого спроса

Преимущества и недостатки использования

Интеграция ABC- и XYZ-методов имеет несколько преимуществ:

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

У комбинированного анализа также есть недостатки:

• нехватка данных для полноценного стратегического планирования;
• искажение информации.

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

Бизнес-анализ с использованием Excel

Хотите применять Excel для решения ключевых финансовых и разнообразных практических бизнес-задач? Это книга точно для вас. Каждому менеджеру и предпринимателю приходится сталкиваться с различными задачами: анализ финансовой отчетности, финансовое планирование и управление финансами компании, принятие инвестиционных решений, управление продажами и маркетинг.На реальных примерах, автор книги показывает, как извлечь максимум пользы из новых возможностей и функций Excel . Пошагово рассматриваются целые бизнес-проекты, сопровождаемые максимально понятными объяснениями и ценными советами. Все это дополняется невероятно полезными электронными примерами, от образцов бухгалтерских книг до инструментов бизнес-прогнозирования, которые можно загрузить с веб-страницы книги на сайте издательства

ABC-анализ в Excel: пошаговая инструкция, рабочие образцы с формулами

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

Количество товаров превышает 5000 наименований. Объединяем их в группы по типам номенклатуры.

количество продаж за квартал;
цена продажи за единицу;
общая выручка за год в рублях

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

ABC-анализ в Excel: пример

Сортировка выручки по убыванию

Выделите диапазон ячеек: вся таблица с заголовками без строки «Итого».

В раскрывающемся меню выберите:

Данные — Сортировать — Сортировать по:

• столбец «Выручка»
• сортировка «Ценности»
• в порядке убывания

Щелкните «ОК».

Система строит таблицу в порядке убывания доходов в столбце D.

ABC-анализ в Excel: сортировка чеков по убыванию ABC-анализ в Excel: сортировка по убыванию

Доля каждой строки в общем параметре

Определите долю каждой статьи в выручке:

• добавьте столбец «Поделиться» (E). Формат ячейки — процентный;
• в строке 2 для продукта 6 мы вставляем формулу: выручка от продукта 6 / общая выручка;
• мы удлиняем формулу для всех продуктов.

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

ABC Analysis в Excel — формулы для расчета доли дохода от каждого продукта

Символ & предупреждает Excel, что формулу нельзя переместить:

• & перед буквой — столбцами;
• & перед цифрой — построчно.

ABC-анализ в Excel: доля каждого продукта в выручке

Перед созданием таблицы ABC проверьте долю каждого элемента в общей стоимости (выручка, запасы, стоимость и т.д.). Нет смысла проводить ABC-анализ, если объект распределен примерно поровну. Каждый показатель в равной степени способствует результату.

Определяем группу

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

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

Группа	Диапазон
А	до 70%
Б	70–90%
С	90-100%

В ячейке G2 запишите формулу = ЕСЛИ (F2 = 90%; «C»; «B»)). Расширение формулы для всех продуктов.

В примере для наглядности проценты указаны цифрами. 

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

ABC-анализ в Excel: распределение групп, формулы ABC-анализ в Excel: распределение по группам — результат обработки

В столбце G каждой группе товаров присвоен код A, B, C.

В группу А входят товары, приносящие основную прибыль.

Группа Б — продукция компании, на которую существует неравномерный спрос.

Группа C: продукты, приносящие только 10% дохода.

ВПР

В английской версии: VLOOKUP

Что делает: сопоставляет данные из одного столбца с другим.

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

=VLOOKUP(«Март»;A2:D13;4;FALSE)

Функция возьмёт наш «Март», проверит весь диапазон, который мы указали, найдёт в нём наше слово и выведет результат из четвёртого столбца, который соответствует марту. Параметр FALSE означает, что нам нужно точное совпадение, — если хватит примерного, поставьте TRUE.

Это выглядит просто на нашей таблице — тут значения можно найти и без функций. Но когда у тебя не 12 строк, а 12 тысяч, то с формулами получается проще.

№17 Маркер

Часто получается, что надо ввести формулу, пользователь при помощи маркера заполнения тянет результаты вниз (как показано на рисунке выше).

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

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

Поэтому есть вариант, после заполнения первого выражения, навести курсор на последнюю клетку с символом и нажать на правый нижний угол левой кнопкой мыши.

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

Здесь действует принцип того, что программа видит значение слева.

Если слева нет символов (пробел), то на этой строке процесс и остановится.

То есть, такая протяжка срабатывает до последней пустой ячейки.

Тянется результат выражений

Найти ошибки в формуле

Бывает, формула не работает, но причина «поломки» непонятна. Иногда, чтобы разобраться в сложной формуле (где как аргумент функции берутся другие функции) или найти в ней ошибку, нужно вычислить только ее часть. Вот две подсказки:

Часть формулы вычисляется прямо в строке формул. Для этого необходимый участок нужно выделить и нажать F9. Все просто, но есть одно «но». Если забыть вернуть все на место, то есть отменить вычисление функции, и нажать enter – посчитанная часть останется в виде числа.

Кликнуть на «Вычислить формулу» во вкладке «Формулы». Откроется окно, где можно вычислять формулу пошагово и тем самым найти момент, где появляется ошибка, если она, конечно, есть.

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

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

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

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

XYZ-анализ в Excel

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

Выгрузка данных

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

Аналитик извлекает информацию о продажах.

При XYZ-анализе в выгрузку нужно включить дополнительные элементы:

• значения, распределенные по месяцам/кварталам;
• значения в натуральных единицах.

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

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

В Excel нет стандартной формулы, посредством которой можно автоматически рассчитывать коэффициент, однако можно ввести команду вручную:

=СТАНДОТКЛОН(заданный диапазон данных)/СРЗНАЧ(заданный диапазон данных).

Ячейке с формулой нужно присвоить процентный формат.

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

Соединение с ABC

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

Анализ XYZ используют как дополнение к ABC-методу.

Ограничения XYZ-анализа

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