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