Abc xyz — анализ в excel одним нажатием клавиши

XYZ-анализ в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как его применять?

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

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

• А – 80% от всех продаж, наибольший объем выручки (оптимально 20% всех клиентов);
• В – 15% от всех продаж, средний объем выручки (оптимально 16% всех клиентов);
• С – 5% от всех продаж, незначительный объем выручки (оптимально 64% всех клиентов).

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

На заметку! Метод развивается, и в настоящее время может применяться его вариант по схеме АВСD – соответственно, 50%, 30%, 15%, 5%. Категория A — самых выгодных клиентов — детализируется и разделяется на А, В для наиболее точного анализа, а две другие сохраняют такие же значения, как в классическом варианте.

XYZ-анализ подразделяет клиентов на категории:

• X – постоянно покупающие продукцию клиенты, их поведение всегда можно спрогнозировать;
• Y – нерегулярные клиенты, прогноз затруднен, но возможен;
• Z – случаи покупок единичны, прогноз сделать практически невозможно.

АВС-анализ технически представляет собой таблицу, содержащую:

• наименование клиента;
• суммы (значения от большего к меньшему);
• доли;
• суммарные доли;
• категории.

На основании расчетов клиентам присваивается категория A (до 80% суммарной доли), В (от 80 до 95% суммарной доли), С (свыше 95% суммарной доли).

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

В сжатом виде формула выглядит так: V= (среднее квадратическое отклонение) / t средний показатель объема продаж за весь период.

Для ее расчета на практике пользуются функциями СТАНДОТКЛОН и СРЗНАЧ табличного редактора Excel. Вписывается формула =СТАНДОТКЛОНП(интервал ячеек подсчета показателя)/СРЗНАЧ(интервал ячеек подсчета показателя). Коэффициент может принимать такие значения по группам: X – до 10%, Y – 10-25%, Z – более 25%.

Коэффициент проставляется в таблице по строке каждого клиента. Каждому клиенту присваивается группа «предсказуемости». Полученные данные необходимо совместить.

Анализ ABC XYZ делается на основе таблицы, где по вертикали располагаются сгруппированные значения ABC, а по горизонтали XYZ. Клиенты из категорий AX, AY, BX, CX считаются оптимально подходящими для дальнейшего сотрудничества, перевода его на более высокий уровень. АZ отличаются нестабильностью, но это покупатели из категории значительных. Нужна дополнительная работа, чтобы не потерять их. Необходимо проанализировать ассортимент и качество обслуживания. CZ – самые безнадежные клиенты. Разорвав с ними деловые отношения, можно высвободить дополнительные ресурсы.

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

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

Выполнение XYZ-анализа

Алгоритм XYZ-анализа строится так:

1. Выбрать объект и анализируемый параметр.
2. Определить временные рамки исследования.
3. Рассчитать коэффициент вариации по каждому объекту.
4. Ранжировать объекты по коэффициенту вариации.
5. Распределить объекты на 3 группы:
   • Х — коэффициент вариации от 0 до 10% — группу характеризует устойчивость;
   • Y — коэффициент вариации от 10 до 25% — поведение группы изменчиво, но прогнозируемо;
   • Z — коэффициент вариации от 25% — случайный, разовый характер сделки, спроса и т. д.

Выполним XYZ-анализ клиентской базы ООО «Альфа» средствами Excel:

1. Объектом анализа выбираем клиентскую базу и рассматриваем сумму покупок по каждому.
2. Определим период, за который проводим анализ. Это будут шесть месяцев из полугодия, рассмотренного в АВС-анализе.
3. Составляем таблицу клиентов с объёмами покупок за каждый из выбранных шести месяцев.
   В исходную таблицу для XYZ-анализа включаются список клиентов и суммы их покупок по месяцам
4. Коэффициент вариации рассчитывается по сложной формуле. Его значения колеблются от 0 до 1. В Excel для этого предусмотрен специальный инструмент: если данные начинают вводиться со строки 3 (ячейки В3-G3), в свободном столбце вписываем формулу =СТАНДОТКЛОНП (B3:G3)/СРЗНАЧ(B3:G3), протягиваем до последней строки, ячейкам задаём процентное значение. В этом варианте коэффициент будет отображаться в процентах.
   Коэффициент вариации можно рассчитать по формуле, но удобнее воспользоваться инструментом Excel
5. Для удобства в таблице можно рассчитать средние продажи за месяц по каждому клиенту и стандартное отклонение. Но для результатов анализа принципиальным будет коэффициент вариации. На этом этапе он должен быть проставлен в строке каждого клиента.
   Коэффициент вариации рассчитавыется в отдельном столбце по каждому клиенту

6. Таблицу клиентов сортируем в порядке возрастания по значению коэффициента (меню «Данные» → «Сортировка» → «По возрастанию»). Делим их на 3 группы. В группу X войдут клиенты с коэффициентом от 0 до 10%, Y — от 10 до 25%, Z — выше этого значения. Если объектов немного, можно вместо сортировки проставить принадлежность к группе вручную в отдельном столбце.

   XYZ-анализ распределяет всех клиентов по трём группам

7. Подведём итог проведённого XYZ-анализа клиентской базы ООО «Альфа». В группу X вошли стабильно покупающие клиенты, их насчиталось 8 из 20. Для вошедших в группу Y (7 клиентов) характерен колеблющийся спрос. В группе Z (5 клиентов) спрос практически непредсказуем и скорее случаен, чем закономерен. Делаем вывод, что поведение большинства клиентов компании стабильно или прогнозируемо.
   XYZ-анализ характеризует группы объектов по степени их стабильности

ABC анализ — примеры использования

ABC анализ делается для того чтобы найти сильные и слабые элементы системы. Когда мы видим сильные и слабые элементы, мы можем найти причины их силы и слабости, мы можем составить оптимальные стратегии управления этими элементами.

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

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

Как использовать ABC анализ в закупках

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

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

Увидеть такие товары, вам и позволяет ABC анализ

Товары группы А приносят 80% всех продаж компании, это значит что вы должны уделять самое пристальное внимание этой группе. Они обязательно и всегда должны быть на складе в количестве чуть больше чем необходимо

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

Вы можете сделать ABC анализ товарных позиций по их объему закупок. Товары группы А, разумеется, будут составлять около 80% всего объема.

Это значит, что вам нужно идти к поставщикам этих товаров и просить скидку. На таком большом закупочном объёме, даже небольшая скидка принесёт огромную экономию вашей организации.

Как использовать ABC анализ в продажах

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

Дальше вам нужно понять причину, почему одни товары продаются хорошо (группа А), а другие плохо (группа С).

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

Например, товара группы С может быть всегда мало на складе (логист на него давно забил и забыл).

Или у менеджеров не хватает обучающих материалов по группе С, или вы просто напросто не конкуренты по этому направлению и нужно вводить новые (более жизнеспособные) позиции.

Вы можете проанализировать менеджеров отдела продаж и распределить их по группам ABC. После того как вы определите менеджеров группы А, присмотритесь к ним внимательно и найдите в них то, что делает их лучшими. Затем перенесите эту модель на остальных.

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

Как использовать ABC анализ в цифровом маркетинге

Вы можете ранжировать по группам источники трафика по их вкладу в продажи и распределять маркетинговый бюджет в сторону группы А.

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

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

Например, продажи по первой фразе (группы А), вам приносят в месяц 1 000 000 рублей/мес. А фразы по последней фразе (группа С) 5 000 рублей.

Если вы увеличите CTR% объявления по первой фразе в два раза, то можно ожидать увеличение объёма продаж минимум на 50% (да, здесь не линейная зависимость), а это + 500 000 рублей.

А если вы те же усилия примените к последней фразе (группы С), то вы получите прирост лишь + 2 500 рублей.

Я думаю разница очевидна.

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

Как использовать ABC анализ в SEO

Да, да, ABC анализ можно использовать где угодно, а уж тем более его нужно использовать в SEO.

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

Так 10 статей с частотностью 100 переходов в сутки каждая, принесут вам в итоге 1 000 посетителей в сутки.

Ту же самую 1 000 вы можете получить используя 100 ключей с частотностью 10 переходов в сутки.

Я думаю разница по трудозатратам очевидна — написать 10 статей или 100!

Эпилог

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

ABC анализ это искатель точек роста, это искатель новых идеи и стратегий!

Как сделать XYZ анализ

Проведение данного вида анализа, как и предыдущего, можно разделить на этапы:

1. необходимо выбрать элементы и показатели для анализа,
2. выбрать временной промежуток, которое вы хотите проанализировать,
3. вычислить коэффициент вариации каждого анализируемого элемента,
4. распределить элементы в зависимости от показателя вариации,
5. разделить элементы на 3 группы: X, Y, Z.

К категории X относятся элементы с показателями вариации 0-10%. Таких клиентов можно охарактеризовать как устойчивых.

Категория Y — показатели вариации 10-25%. Эти клиенты изменчивы, но их поведение можно спрогнозировать.

Категория Z — вариация от 25% и выше. Сюда относятся случайные клиенты, которые приобрели товар один раз по потребности.

Данные для XYZ анализа должны быть статистического характера. Приведем пример XYZ анализа. Для этого воспользуемся офисной программой MS Excel.

Преимущества и недостатки ABC-метода

Преимущества:

• Простота. Анализ можно сделать в течение часа, при этом методу легко научиться.
• Надёжность результатов. Чем проще алгоритм, тем меньше шансов сделать что-то не так, а так как каждый этап расчётов прозрачен и понятен, то и выявить ошибку легко.
• Помогает оптимизировать ресурсы. Результаты ABC-анализа наглядно показывают, на чём стоит сконцентрироваться, а от чего отказаться.
• Универсальность. Можно анализировать практически всё: товары, сырьё, деньги, людей — результаты будут справедливы.

Недостатки:

• Одномерность. Анализу подвергается только одна сторона ресурса. То есть если вам нужно оценить товары только с точки зрения объёмов продаж, то метод подходит. А если хотите одновременно изучить два параметра, например, объёмы продаж и регулярность спроса, то нет.
• Нельзя проанализировать качественные характеристики. Ранжировать товары можно только по количественным показателям. Если в ассортименте намешаны товары разного потребительского назначения, например, продукты питания и бытовая химия, то придется сначала сгруппировать их, а уже потом анализировать отдельно каждую группу.
• Разделение на группы А, В и С не всегда подходит. Например, метод не показывает убыточные позиции. Чтобы избежать этого недостатка, анализ трансформируют в ABCD-матрицу, где D — это группа нерентабельных товаров. Другой вариант — в группу А попали не 20%, а 40% позиций. В этому случае придется добавлять группу А+, куда войдут наиболее рентабельные товары.
• Не отражает влияние внешних факторов: сезонности, колебаний спроса, покупательской способности, конкурентов и т.д.

Отчеты в Декалитрах (ДАЛ) для 1С: Розница 2.2.4 — 2.3.7: Продажи, Поступления, Анализ движения номенклатуры. Движение по документам ЕГАИС

На данный момент в конфигурации 1С:Розница 2.2 есть один большой минус — это наличие реквизита в Номенклатуре «ОбъемДАЛ», а собственно, отсутствие отчетов с его использованием! Как всегда, находятся маленькие магазинчики, продающие алкогольную продукцию, и сопровождающиеся какой-либо бухгалтерской компанией, соответственно ни о каком обмене с бухгалтерией у них и речи идти не может, а бухгалтеру все-таки хочется упростить себе жизнь и не высчитывать декалитры каждый квартал на калькуляторе.
Исходя из повстречавшейся мне аналогичной ситуации возникла необходимость создать несколько отчетов с выводом информации по Производителям алкогольной продукции, Кодам АП и Номенклатуре в декалитрах.
Поэтому представляю вашему вниманию отчёты по выше указанным параметрам: Анализ движения номенклатуры (Приход/Расход), Продажи и Ведомость по продажам товаров. Так же реализованы отчёты по документа ЕГАИС в декалитрах.

1 стартмани

XYZ анализ ассортимента продукции. Формула расчета

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

На первом этапе осуществляется расчет коэффициента вариации объема продаж (уровня спроса) по каждой товарной группе. Изменчивость продаж оценивается с помощью коэффициента вариации. Формула расчета коэффициента вариации представляет собой отношение стандартного отклонения к математическому ожиданию объема продаж. Формула имеет следующий вид:

где:

σ – стандартное отклонение объема продаж;

x_i – объем продаж в период в i-й период;

n – количество рассматриваемых периодов продаж товаров;

х* – среднеарифметическое значение продаж товаров.

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

На втором этапе производится сортировка ассортимента продукции по значению коэффициента вариации и классификация товаров по трем группам – XYZ на основе значений коэффициента вариации.  В таблице ниже показан критерий оценки и экономическое описание для различных групп товаров.

Группа	Коэффициент вариации	Характеристика класса
Группа «X	0<V<10%	Товары с наиболее устойчивыми объемами продаж
Группа «Y	10%<V<25%	Товары с прогнозируемыми, но изменчивыми объемами продаж
Группа «Z	V>25%	Товары, обладающие случайным спросом

Пример ABC-анализа

Допустим, мы продаём декоративную косметику и хотим понять, какие группы товаров продавать выгодно, а какие нет.

Шаг 1. Заносим в таблицу все интересующие нас группы товаров: тени, тональные средства, помады, пудры, туши и так далее. Проставляем суммы продаж за последние три месяца (квартал) и сортируем по убыванию итоговой суммы продаж.

Шаг 2. Вычисляем процент от общего объёма продаж по каждой группе и сортируем по убыванию процентного значения.

Например, за последние три месяца мы продали тональных кремов на 22 000 рублей и это составляет 22% от суммы общего дохода за этот период (от 100 000 рублей).

Шаг 3. Вычисляем совокупный процент по каждой категории товаров.

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

Шаг 5. Делаем выводы. Нам выгодно продавать тональные кремы, пудры и тени

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

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

ABC-анализ в Excel

Метод ABC позволяет рассортировать список значений на три группы, которые оказывают разное влияние на конечный результат.

Благодаря анализу ABC пользователь сможет:

• выделить позиции, имеющие наибольший «вес» в суммарном результате;
• анализировать группы позиций вместо огромного списка;
• работать по одному алгоритму с позициями одной группы.

Значения в перечне после применения метода ABC распределяются в три группы:

А – наиболее важные для итога (20% дает 80% результата (выручки, к примеру)).
В – средние по важности (30% – 15%).
С – наименее важные (50% – 5%).

Указанные значения не являются обязательными. Методы определения границ АВС-групп будут отличаться при анализе различных показателей. Но если выявляются значительные отклонения, стоит задуматься: что не так.

Условия для применения ABC-анализа:

• анализируемые объекты имеют числовую характеристику;
• список для анализа состоит из однородных позиций (нельзя сопоставлять стиральные машины и лампочки, эти товары занимают очень разные ценовые диапазоны);
• выбраны максимально объективные значения (ранжировать параметры по месячной выручке правильнее, чем по дневной).

Для каких значений можно применять методику АВС-анализа:

• товарный ассортимент (анализируем прибыль),
• клиентская база (анализируем объем заказов),
• база поставщиков (анализируем объем поставок),
• дебиторов (анализируем сумму задолженности).

Метод ранжирования очень простой. Но оперировать большими объемами данных без специальных программ проблематично. Табличный процессор Excel значительно упрощает АВС-анализ.

Общая схема проведения:

1. Обозначить цель анализа. Определить объект (что анализируем) и параметр (по какому принципу будем сортировать по группам).
2. Выполнить сортировку параметров по убыванию.
3. Суммировать числовые данные (параметры – выручку, сумму задолженности, объем заказов и т.д.).
4. Найти долю каждого параметра в общей сумме.
5. Посчитать долю нарастающим итогом для каждого значения списка.
6. Найти значение в перечне, в котором доля нарастающим итогом близко к 80%. Это нижняя граница группы А. Верхняя – первая в списке.
7. Найти значение в перечне, в котором доля нарастающим итогом близко к 95% (+15%). Это нижняя граница группы В.
8. Для С – все, что ниже.
9. Посчитать число значений для каждой категории и общее количество позиций в перечне.
10. Найти доли каждой категории в общем количестве.



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

Правильная последовательность выполнения ABC-анализа обычно такая:

1. Выбрать несколько показателей, по которым будем оценивать процесс. Для ассортимента товаров, пусть это будет выручка, прибыль и количество чеков для каждого товара
2. Сегментировать процесс, если это необходимо. Например, по географии сбыта, т.к. в разных странах могут отличаться стереотипы, модели поведения при покупке. Очевидно, нельзя иметь одинаковый ассортимент в Европе и Китае, т.к. потребительский спрос там разный. Анализ проводите для каждого сегмента отдельно.
3. Выгрузить из базы данных численную информацию по товарам и выбранным показателям за длительный период. Обычно, 1-2 года. В нашем случае, получим объемы продаж в единой валюте, прибыль от этих продаж и количество чеков.

Важно: полученные данные должны быть достоверными, «чистыми» и сведёнными до вида «товар –значение». Например, выручка нужна для каждой единицы номенклатуры в виде: «Наименование товара – сумма продаж за год»

Если у вас более детальные данные, можно воспользоваться Сводными таблицами для приведения информации в нужный вид.

Для каждого показателя отдельно провести классификацию (анализ АБЦ). Я распределю товары на категории три раза: по выручке, по прибыли и по количеству чеков. Получится три отдельные таблицы.

Использование ABC XYZ анализа

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

В таком случае создается единая таблица, содержащая данные по ABC анализу продаж и XYZ анализу, и каждому объекту исследования присваивается одна из 9 категорий:

• AX — означает наивысшую потребительскую стоимость, стабильное приобретение товаров и хорошую прогнозируемость,
• AY — высокая потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
• AZ — высокая потребительская стоимость, спонтанные покупки и невозможно спрогнозировать,
• BX — средняя потребительская стоимость, стабильное приобретение товаров и хорошая прогнозируемость,
• BY — средняя потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
• BZ — средняя потребительская стоимость, спонтанные покупки и невозможно спрогнозировать,
• CX — низкая потребительская стоимость, стабильное приобретение товаров и хорошая прогнозируемость,
• CY — низкая потребительская стоимость, но нестабильное приобретение и средняя степень прогнозирования,
• CZ — низкая потребительская стоимость, спонтанные покупки и невозможно спрогнозировать.

Неликвиды на складе: Pro и Lite версия. Редактирование реквизита в таблице документа. Для конфигурации УТ 11.х, ERP 2.x, КА 2.х 1С Предприятие 8.3

Внешний отчет для анализа товаров, находящихся на складе без движения. Работает для конфигураций 1С УТ 11.х, ERP 2.x, КА 2.х.
Отчет отображает товары на складах, движения по которым не производились в течении заданного периода дней.
Подключается как внешний отчет в конфигурации 1С Управление торговлей 11.
В Pro версии можно установить общий реквизит номенклатуры, для дальнейшего включения в отбор в установке цен номенклатуры.
Редактирование в табличном документе ускоряет процесс работы с неликвидами номенклатуры на складах.
Отчет можно использовать для стоимостной оценки товаров на складах.
Полный доступ к тексту модулей и СКД.

1 стартмани

Как рассчитать коэффициент вариации в Excel

Коэффициент вариации — это показатель, отражающий разброс значений относительно среднего (отношение стандартного отклонения к среднему значению). Коэффициент вариации измеряется в процентах и отражает однородность временного ряда.

Коэффициент вариации — это отличный показатель, который поможет вам в подготовке данных для прогноза

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

Если коэффициент равен 0%, то ряд абсолютно однородный, т.е. все значения между собой равны.

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

Например:

Ряд	Oct-12	Nov-12	Dec-12	Коэффициент вариации
Однородный ряд	100	100	100	0%
Неоднородный ряд	150	1	300	81%

Как рассчитать коэффициент вариации в Excel

Коэффициент вариации = отношение стандартного отклонения к среднему

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

=СТАНДОТКЛОНПА(ссылка на ряд)/(СУММ(ссылка на ряд)/СЧЁТЕСЛИ(ссылка на ряд;»>0″))

где

• СТАНДОТКЛОНПА(J6:M6) — формула для расчета значения стандартного отклонения в Excel за анализируемый период;
• (СУММ(J6:M6)/СЧЁТЕСЛИ(J6:M6;»>0″)) — среднее за анализируемый период;

Вводим формулу в ячейку, получаем расчет коэффициента вариации

Протягиваем формулу на весь массив данных.

Выделение девяти групп товаров при совмещенном АВС и XYZ-анализе

1) Товары групп А и В обеспечивают основной товарооборот компании, поэтому необходимо обеспечивать постоянное их наличие.
Как правило, по товарам группы А создается избыточный страховой запас, а по товарам группы В — достаточный. Использование XYZ-анализа позволяет точнее настроить систему управления товарными ресурсами и за счет этого снизить суммарный товарный запас.2) Товары группы АХ и ВХ отличает высокий товарооборот и стабильность. Необходимо обеспечить постоянное наличие товара, но для этого не нужно создавать избыточный страховой запас. Расход товаров этой группы стабилен и хорошо прогнозируется.3) Товары группы AY и BY при высоком товарообороте имеют недостаточную стабильность расхода, и, как следствие, для того чтобы обеспечить постоянное наличие, нужно увеличить страховой запас.4) Товары группы AZ и BZ при высоком товарообороте отличаются низкой прогнозируемостью расхода. Попытка обеспечить гарантированное наличие по всем товарам данной группы только за счет избыточного страхового товарного запаса приведет к тому, что средний товарный запас компании значительно увеличится. Поэтому по товарам данной группы следует пересмотреть систему заказов:- перевести часть товаров на систему заказов с постоянной суммой (объемом) заказа;- обеспечить по части товаров более частые поставки;- выбрать поставщиков, расположенных близко к складу, тем самым снизив сумму страхового товарного запаса;- повысить периодичность контроля;- поручить работу с данной группой товаров самому опытному менеджеру компании и т. п.5) Товары группы С составляют до 80% ассортимента компании. Применение XYZ-анализа позволяет сильно сократить время, которое менеджер тратит на управление и контроль над товарами данной группы6) По товарам группы СХ можно использовать систему заказов с постоянной периодичностью и снизить страховой товарный запас.7) По товарам группы CY можно перейти на систему с постоянной суммой (объемом) заказа, но при этом формировать страховой запас, исходя из имеющихся у компании финансовых возможностей.8) В группу товаров CZ попадают все новые товары, товары спонтанного спроса, поставляемые под заказ и т. п. Часть этих товаров можно безболезненно выводить из ассортимента, а другую часть нужно регулярно контролировать, так как именно из товаров этой группы возникают неликвидные или труднореализуемые товарные запасы, от которых компания несет потери. Выводить из ассортимента необходимо остатки товаров, взятых под заказ или уже не выпускающихся, то есть товаров, обычно относящихся к категории стоков.

Применение ABC XYZ анализ

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

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

Для анализирования объема поставок, анализируется база данных поставщиков. С помощью таких видов анализа вы можете просмотреть дебиторскую задолженность и тех, кто вам должен и какие суммы.

С помощью данных видов анализа вы можете узнать кто приносит вам больше прибыли и платит чаще других.

Анализируя клиентскую базу данных с помощью ABC анализа, вы можете условно разделить всех клиентов на 3 группы: A, B, C. То есть на больших, средних и малых соответственно.

При этом нет единого правила, по которому можно произвести условное разделение. Все зависит от самого бизнеса и его объемов продаж. Так например для малого бизнеса к крупным клиентам можно отнести тех, которые приносят вашей компании примерно 150 тыс. рублей. При этом для крупных компаний с такой суммой клиент отнесется к категории C, а в категории А будет список клиентов, которые приносят компании миллионы.

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

За счет применения abc анализа вы можете определить количество целевой аудитории в ваших продажах. Все виды торговли (оптовая, розничная) включают в себя несколько этапов сделки. Процесс покупки товаров или услуг можно представить в виде воронки продаж. Она выглядит как перевернутый конус, разделенный на уровни.

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

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

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

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

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

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

С теми клиентами, которые приобретают ваши товары или услуги, но редко, вы должны поддерживать постоянные контакты. Это необходимо, так как вы можете потерять их.

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

https://youtube.com/watch?v=tHU_LP_Zt2s

Проведение XYZ анализа по классическому методу

Группа	Количество	Коэффициент вариации
X	50%	0-10%
Y	30 %	10-25%
Z	20 %	>25%

Мы ищем коэффициент вариации. Он показывает, насколько отклоняются продажи от среднего. Чем выше xyz анализ коэффициент вариации, тем больше отклонения продаж от средних. В классическом анализе в группу Х попадают товары, у которых этот коэффициент меньше 10%. В группу Y – товары с коэффициентом от 10 до 25%, в группу Z- товары с коэффициентом выше 25%. Коэффициент вариации за выбранный период необходимо посчитать по каждому товару. Такая функция есть в Excel. После этого мы можем разделить товары на группы X, Y, Z. Так выглядит проведение XYZ анализа в классическом варианте.

А что, если весь ассортимент нестабильно продающиеся запчасти, либо электротовары? Если мы будем применять классическую методику, то весь ассортимент упадёт в группу Z. Что с этим делать? Проблему может решить модифицированная методика XYZ анализа.