Начало публикации анализ.
Переходим к XYZ анализу.
Для АВС- анализа мы брали целый период - год.
Для XYZ-анализа информация расчётного года взята с разбивкой по месяцам.
Уточнение: цифры в таблице Ассортимент по месяцам не имеют связи ни с какой реальной базой. Поэтому те результаты, которые мы видим в таблице 4.7. будут скакать то к "0", то к другой высшей числовой точке.
Возьмите исходную информацию своей компании и, следуя описанию ниже, проведите анализ на реальных данных за отчётный период.
***
Для определения регулярности продаж возьмём год и рассчитаем потребность в товарах на складе по периодам.
Наша цель определить Коэффициент вариации, по результатам которого и будет распределение по Группе XYZ.
В таблице 4.7. приведён вариант, который содержит формулу:
=ЕСЛИ([@[Коэффициент вариации]]>25;"Z";ЕСЛИ([@[Коэффициент вариации]]>10;"Y";"X"))
Вы можете изменить цифры для условия. В примере в формуле заложены 25 процентов, но во многих источниках содержится такая информация:
Если результат меньше 10%, то такой показатель положительный;
Если результат находится в интервале 10-20%, то такой показатель считают средним;
Если результат находится в интервале 20-33% - то показатель с таким существенным разбросом является большим, но допустимым;
В случае, если показатель окажется выше 33%, то такой большой разброс уже недопустим для проведения XYZ-анализа .
***
Первое, что нам необходимо сделать - это создать таблицу, содержащий такой же Ассортимент, как в таблице, где проводили АВС-анализ;
Затем по строке 3 внесём названия месяцев с января по декабрь;
Начиная с ячейки D4 по ячейку О4 вносим формулу для автоматической выборки;
=СУММЕСЛИМН(Ассортимент!$D:$D;Ассортимент!$C:$C;[@Наименование];Ассортимент!$A:$A;">=01/01/2014";Ассортимент!$A:$A;"<=31/01/2014")/1000
.........
=СУММЕСЛИМН(Ассортимент!$D:$D;Ассортимент!$C:$C;[@Наименование];Ассортимент!$A:$A;">=01/12/2014";Ассортимент!$A:$A;"<=31/12/2014")/1000
Измените даты в соответствии с годом, который присутствует в ваших таблицах.
Далее по всем столбцам протягиваем формулы, содержащиеся по строке 4, до строки 45 включительно. Возможно, формула автоматически заполнит столбцы после внесения формулы в ячейки по строке 4.
В таблице 4.8. представлена таблица с формулой выборки по периодам.
Для того, чтобы получить данные для внесения в формулу, которую мы видим в примере таблицы 4.7., необходимо предварительно произвести расчёт других показателей.
Ввиду того, что таблицы выше содержат много информации и заголовки столбцов в образцах таблиц плохо видно, в таблице 4.9. отдельно приведены столбцы, данные в которых будут рассчитаны в этой главе.
Рассчитываем:
Среднеарифметическое значение продаж;
Стандартное отклонение;
Коэффициент вариации;
Группа XYZ.
Рассчитаем среднее значение за двенадцать месяцев. Смотрим таблицу 4.10.
Для этого в ячейку Р4 вносим формулу.
=СРЗНАЧ(D4:O4)
Если столбец не заполнился автоматически, то копированием протягиваем формулу до строки с последним значением в таблице.
В таблице 4.11 показан расчёт показателя Стандартного отклонения, оценивающего среднеквадратичное отклонение случайной величины.
Используем формулу:
=СТАНДОТКЛОН.Г(D4:O4)
Что означает эта формула?
Мы можем самостоятельно рассчитать, чтобы проверить порядок расчёта.
Допустим наши цифры соответствуют порядковому номеру месяцев.
Можно в строку 50 внести числа:
1,2 ........11,12
Сумма этих чисел равна 78, а среднеарифметическое значение 6,5.
Из каждого значения вычитаем среднеарифметическое: 1 - 6,5 и так далее.
Каждое из полученных значений возводим в квадрат.
Суммируем все значения и делим их на 12
=СУММ(D50:O50)/12.
Получили результат и, воспользовавшись калькулятором квадратных корней, мы должны получить тоже число, которое находится по соответствующей строке в столбце Стандартное отклонение.