Пошаговый план расчета ABC – анализа

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

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

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

Этап 1

Начинаем с того, что определяем, что вообще будем считать. В моем случае нужно понять по итогам квартала, что из новой сезонной коллекции продается лучше всего, а что плетется в хвосте. Для этого я выгружаю отчет из 1С по продажам за прошедший квартал. У нас этот отчет называется «валовая прибыль по клиентам». Там я выбираю категорию товаров, которая меня интересует: «коллекция Весна 2018». Именно внутри этой категории я буду проводить анализ. (Смотри задачу выше)

Дальше я открываю наш любимый Excel, создаю новый файл разбиваю колонки следующим образом:

Переношу туда данные:

• 1.под категорию «Spring 2018», я вставляю список всех артикулов из 1С. Тупо копирую из базы и вставляю в файл.
• 2.Вторая пустая колонка мне нужна для внесения данных по характеристике товара – цвет и размер.
• 3.Колонку «количество» наполняю данными из 1С, это количество проданных единиц товара.
• 4.Квартальный оборот (выручка) - вставляю данные из того же отчета 1С.
• 5.Ну и себестоимость. У нас в отчете она тоже есть, так что копирую и вставляю в файл.
• 6.Там, где у меня пустая клетка я вставляю изображения самих моделей чтобы проще было потом ориентироваться (вообще несколько трудозатратная история, так что если можете обойтись без этого, то лучше – нафиг).

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

Этап 2

Дальше идет столбец «доля в обороте (выручке), квартал». Эта основная расчетная колонка, по которой я смогу раскидать товар на ABC категории. Мои коллеги описывают этот процесс так: «В нашем примере вначале нужно рассчитать суммарное значение дохода по всем товарам – это будет итоговая сумма. Затем доход по каждой позиции делится на общий итог. Доли к итогу готовы».

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

В моем случае это =E2/СУММ(Е2:Е233). Столько у меня наименований (по 233 строку).

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

Этап 3

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

Я выбираю первое разделение: 50/30/20 внутри сезонной категории.

Чтобы посчитать долю, я на всякий, сразу отсортирую по результату в ячейке G. Для этого выделяю всю таблицу > вкладка «данные» > поле «сортировка» > поле G > значение > по убыванию

Только после сортировки мы приступаем к расчету накопленной доли к итогу!!!

Этап 4

Чтобы рассчитать накопленную долю к итогу берем значение в первой ячейке дублируем из соседнего столбца. В моем случае в ячейке Н2 я пишу=G2. Ниже в ячейке Н3 =G3+H2 то есть я складываю значение последующей ячейки с предыдущим результатом. Н4 = G4+H3 и так далее вниз.

Дотянув эту формулу до конца, в последних ячейках можно будет увидеть значение = 100% значит все сошлось верно. Дальше ориентируясь на показатели в ячейке G помогу раскидать товары по АВС – категориям. Просто смотрите на проценты, и тем товарам что до 50% (мой случай) присваиваете категорию А, от 50 до 80% - В, от 80 до 100% - С. Бывает, что значение уходит в минусовый показатель, так у меня появилась категория D.

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

Я лично делаю именно так.

Заключение

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

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

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

Если есть вопросы или комментарии, с удовольствием выслушаю коллег по цеху.

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

Много интересного про жизнь нашего бренда вот здесь и разумеется вот здесь

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