6) % от общей выручки нарастающим итогом
В сформированной сводной таблице копируем данные из Категорий и Наименований. Вставляем данные в нашу новую таблицу в листе АВС.
Наименования продукции, которые в листе Ассортимент занимали 297 строк, в листе АВС сокращены до 42 строк. Исключены повторяющиеся наименования.
CTRI +A - выделили всю таблицу в листе АВС;
Главная, Форматировать как таблицу;
Выбираете понравившийся вам вариант.
Таблица 4.3. АВС анализ с формулами расчёта. Лист АВС.
Наша формула в ячейке D2 немного исправлена и ограничивается последней позицией в таблице. Это уже не тысячная строка, а шестьдесят третья.
=СУММ(D4:D63).
Присвойте имя ячейке D2. Порядок присвоения имён ячейкам мы уже рассматривали в главе 3.2.
Также, в ячейке Е2 содержится формула, которая расчитывает процент каждой позиции по отношению к сумме всех внесённых в таблицу, к 100%.
=СУММ(E4:E63)*100.
Присвойте имя ячейке Е2. Порядок присвоения имён ячейкам мы уже рассматривали в главе 3.2.
***
В столбце D таблицы 4.4. находится формула распределения выручки по Наименованиям.
=СУММЕСЛИ(Ассортимент!C:C;[@Наименование];Ассортимент!D:D)/1000
Таблица 4.4. Формула, которая суммирует выручку по совпадающим позициям.
Общая сумма выручки в листе Ассортимент и АВС совпадает.
Нажимаем стрелку в столбце Выручка, млн.руб,;
Выбираем Сортировка по убыванию.
Числа в столбце расположились от большего к меньшему, а в столбцах Категория и Наименование данные автоматически подвязались к этим числам.
В столбце Е таблицы 4.5. находится формула расчёта доли выручки по Наименованиям.
=[@[Выручка, млн.руб]]/Итого_сумма
Таблица 4.5. Формула, которая определяет процент выручки по совпадающим позициям.
Переходим к таблице 4.6., где нарастающим итогом показаны данные процентов в общей сумме выручки. В столбце F содержатся формулы, которые представлены ниже:
=СУММ($E$4:E4)
......
=СУММ($E$4:E45)
Таблица 4.6. Проценты нарастающим итогом.
Вернёмся к таблице 4.3., где показана расшифровка формулы расчёта принадлежности позиции к группам А, В или С.
В ячейку G3 внесём название столбца Группа ABС, внесём следующую формулу:
=ЕСЛИ([@[% от общей выручки нарастающим итогом]]>95,49%;"C";ЕСЛИ([@[% от общей выручки нарастающим итогом]]>80,49%;"B";"A"))
Расчёт произведён через формулу ЕСЛИ со ссылкой на ячейки, содержащие процентный расчёт. Если процент более 95,49, то ячейка получит значение С. Если более 80,49 процентов, то ячейка получит значение В. При условии иначе, в ячейке отобразится значение А.
****
В столбце G мы создадим правило для Условного форматирования.
Для этого необходимо:
Выделить данные в столбце D;
Главная, Условия форматирования;
Правила выделения ячеек, Текст содержит;
Для А выбран зелёный цвет, для В жёлтый, для С красный.
***
В приведённом примере мы считаем выручку по 42 позициям.
По результатам анализа:
21 позиция относится к группе А;
10 к группе В;
11 к группе С.
Даже при таких результатах, компания работает и получает прибыль.
Многие из Покупателей, которые приобретают продукцию из Ассортимента, попадающего в первые сорок процентов, приобретают небольшое количество продукции из Групп В и С.
Продукция из Групп В и С является, для компании Продавца сопутствующей. Продавец интересен из-за наличия большого Ассортимента. Это и будет причиной того, что Покупатель и в дальнейшем останется клиентом этого Продавца.
Не было бы таких объёмов из Группы А, если бы не было Ассортимента в Группах В и С.
***
Для анализа по Категориям мы можем взять из сводной таблицы данные и скопировать на новый лист таблицу из листа АВС. Для этого в таблице удалим лист Наименование, а в Категориях оставим 6 следующих групп: