Информация в учебном портале постоянно обновляется!

XYZ анализ, автоматический расчет в Excel

 
 

XYZ в excel, формулы расчета

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

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

 

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

XYZ анализ имеет множество применений. Я же остановлюсь на одном из них, которое используется в розничной торговли. И так, методика xyz анализа:

  1. Необходимо рассчитать коэффициент вариации;
  2. Распределение по категориям X, Y, Z.

Коэффициент вариации

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

Если Вы не сильны в математике, объясню на языке Excel.

Среднеквадратичное отклонение { =СТАНДОТКЛОНП(диапазон периода)} делим на среднее значение { =СРЗНАЧ(диапазон периода)}

Теперь необходимо расставить xyz, где

Х – Является стабильной величиной потребления, или в нашем случае показывает стабильность продаж. Значение категории Х находится в интервале от 0 до 10%

Y – Является переменной величиной потребления, продажи незначительно колеблется. Это могут быть сезонные колебания, нестабильные поставки товара, несвоевременная выкладка товара и т.д. Значение категории от 10 до 25%

Z – Является не регулярным потреблением. Нестабильные продажи, предмет для глубокого анализа товара. В данной категории нет тенденций, и если Вы используете xyz анализ для прогнозирования, то точность этого прогноза будет ничтожно мала. Значение категории выше 25%.

Теперь все эти категории в Excel:

Используем одну из самых распространенных функций «ЕСЛИ»

=ЕСЛИ(Коэффициент вариации <0,1;"X";ЕСЛИ(Коэффициент вариации <0,25;"Y";"Z"))

После чего протягиваем формулу по всем товарам.

А теперь совмещаем АВС и XYZ в одной ячейке при помои формулы:

1 вариант =СЦЕПИТЬ (значение АВС; значение XYZ)

2 Вариант =значение АВС & значение XYZ

 

Дорогие друзья!

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

Скачать шаблон АВС и XYZ

 

Понравилась статья? Отблагодари автора ;)

Комментарии   

0 #18 Юля 14.11.2016 19:49
Дмитрий, третий оплаченный шаблон получила. Благодарю за оперативное реагирование на запрос!
Цитировать
0 #17 Ульянкин Дмитрий 13.11.2016 17:19
Цитирую Юля:
Дмитрий, я оплатила три шаблона. Два получила, один нет - расчет АВС+XYZ. (№ заказа 172).

Да, Юлия, шаблоны оплачены. Проверьте внимательнее почту, возможно одно письмо попало в спам? Если в течении часа не получите шаблон, сообщите мне на адрес, который я Вам выслал.
Цитировать
0 #16 Юля 13.11.2016 12:44
Дмитрий, я оплатила три шаблона. Два получила, один нет - расчет АВС+XYZ. (№ заказа 172).
Цитировать
+1 #15 Ульянкин Дмитрий 27.09.2016 10:19
Цитирую Ирина:
Дмитрий здравствуйте!!!! Вы конечно Мега мозг. Объясните пожалуйста еще раз как Вы находите коэффициент вариации. Более простыми терминами ))). Никак не пойму.ПОЖАЛУЙСТА.....

Спасибо за лестный отзыв!
Попробую простым языком. Вам нужно стандартное отклонение разделить на среднее значение. Т.е. в функции СТАНДОТКЛОНП выбираем значения за каждый анализируемый период (неделя, месяц или год в зависимости какой период вы анализируете). Получившиеся значения делим на среднее значение этого же периода, получаем коэффициент вариации.
Т.е. в статье среднеквадратичное отклонение это и есть коэффициент вариации.
Получилось простым языком) ?
Цитировать
+2 #14 Ирина 27.09.2016 04:38
Дмитрий здравствуйте!!!! Вы конечно Мега мозг. Объясните пожалуйста еще раз как Вы находите коэффициент вариации. Более простыми терминами ))). Никак не пойму.ПОЖАЛУЙСТА.....
Цитировать
+2 #13 Ульянкин Дмитрий 11.06.2016 07:05
Дорогие друзья! Все проблемы с оплатой и доставкой файлов - РЕШЕНЫ!

Но если вдруг возникнет сбой на сервере оплаты, не стесняйтесь пишите! Я лично вышлю купленный Вами файл.
Для тех, кто читает отзывы прежде чем покупать шаблон, скажу, что:
В статье все объясняется и Вы можете сделать подобный шаблон самостоятельно. И только для тех кто ценит свое время, кому некогда делать этот шаблон, я предлагаю его приобрести за 100 руб. Это символическая плата за мой труд. Надеюсь на Ваше понимание.
Цитировать
+3 #12 Василий 03.06.2016 17:42
А у меня не было проблем. Письмо пришло сразу и ссылка в нем на месте. Спасибо дмитрий за этот шаблон.
Цитировать
+2 #11 Ульянкин Дмитрий 03.06.2016 17:39
Цитирую галина:
я оплатила мне не пришла ссылка на скачивание файла

Дорогие друзья, коллеги. Письмо с ссылкой может попасть в спам. Кроме этого, письмо может прийти в течении часа. Но если Вы так и не получили письма с ссылкой по какой либо причине. Пишите мне, я обязательно вышлю Вам оплаченный файл.
Цитировать
+3 #10 галина 03.06.2016 07:52
Цитирую Анзор:
Мне не пришла ссылка на скачивание файла, а я оплатил!!!!!!!!! где ссылка
я оплатила мне не пришла ссылка на скачивание файла
Цитировать
+5 #9 Ульянкин Дмитрий 15.12.2015 06:56
Цитирую Анзор:
Мне не пришла ссылка на скачивание файла, а я оплатил!!!!!!!!! где ссылка

Приношу Вам свои извинения. Выслал файл на указанный Вами email.
Дорогие друзья, коллеги. Если у Вас возникнут проблемы с оплатой или Вы вдруг не получите ссылку на скачивание, пишите мне на почту или в отзывах. Я обязательно вышлю Вам файл. У нас все честно!!!
Цитировать
+8 #8 Анзор 15.12.2015 06:33
Мне не пришла ссылка на скачивание файла, а я оплатил!!!!!!!!! где ссылка
Цитировать
+10 #7 Виктория 19.09.2015 07:27
Вы просто супер!!! очень помогло)))) спасибо огромнейшее!!!
Цитировать
+8 #6 Ульянкин Дмитрий 04.09.2015 10:15
Цитирую Василий:
Есть у кого-то четкое понимание почему для определения границ группы X и Y используются именно 10 и 25 процентов. Нигде не могу найти смысл этих процентов.
Для ABC анализа расчет нахождения границ групп я так или иначе нашел, а тут ну никак не могу найти. :(


Очень хороший вопрос!!!
Все очень просто:
0-10% предположим продажи по количеству шт.
90;100; 110; 100; 105 - Это стабильные продажи, не правда ли?(колебания от 0 до 10%) не вооруженным глазом видно, что в следующем периоде продажи составят в среднем 100 шт.

теперь представим колебания например от 0 до 50%
100; 50; 150; 100; 50 - А вот Это на стабильность не похоже.

Отсюда и есть смысл процентажа, допуск до 10% считается нормальным, 25% - необходимо выявлять причину, и все остальные это явная не стабильность и с этим надо что то делать.

Если не понятно, пишите, попробую иначе объяснить.
Цитировать
+8 #5 Василий 03.09.2015 13:00
Есть у кого-то четкое понимание почему для определения границ группы X и Y используются именно 10 и 25 процентов. Нигде не могу найти смысл этих процентов.
Для ABC анализа расчет нахождения границ групп я так или иначе нашел, а тут ну никак не могу найти. :(
Цитировать
+10 #4 Илона 27.08.2015 09:50
Огромное спасибо, за отличный шаблон.
Цитировать
+10 #3 Anton 11.08.2015 09:27
ОГРОМНОЕ СПАСИБО!!! УЖЕ ДАВНО ИЩУ ЧТО ТО ПОДОБНОЕ И ТОЛЬКО У ВАС ЭТО ЕСТЬ!!!
Отличный отчет!!! Спасибо Вам Большое! XYZ и ABC анализ в одном отчете и что мне больше всего понравилось, что не нужно делать отдельно отчеты для категорий товаров, можно просто воспользоваться фильтром и все пересчитывается. СУПЕР!!!
Цитировать
+10 #2 Юлия 10.08.2015 10:41
Огромное спасибо! Очень интересно и понятно.
Цитировать
+11 #1 Denis 05.08.2015 14:24
Спасибо, занимательно и практически
Цитировать

Добавить комментарий


Защитный код
Обновить

КАК ПЛАНИРОВАТЬ ПРОМО АКЦИИ ЗА 5 МИН.

планирование промо акции

ЭКОНОМЬ СВОЕ ВРЕМЯ

БУДЬ В КУРСЕ!

Получайте новые уроки по аналитике продаж в Excel БЕСПЛАТНО!!!

email рассылки Конфиденциальность гарантирована
email рассылки

Яндекс.Метрика