Excel. Простые примеры для автоматизации работы

Глава 4.2. XYZ анализ.

Переходим к XYZ анализу.

Для АВС- анализа мы брали целый период - год.

Для XYZ-анализа информация расчётного года взята с разбивкой по месяцам.

Уточнение: цифры в таблице Ассортимент по месяцам не имеют связи ни с какой реальной базой. Поэтому те результаты, которые мы видим в таблице 4.7. будут скакать то к "0", то к другой высшей числовой точке.

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

***

Для определения регулярности продаж возьмём год и рассчитаем потребность в товарах на складе по периодам.

Наша цель определить Коэффициент вариации, по результатам которого и будет распределение по Группе XYZ.

Таблица 4.7. Вариант расчёта по Группе 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.8. Выборка ассортимента по периодам.

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

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

Рассчитываем:

Среднеарифметическое значение продаж;

Стандартное отклонение;

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

Группа XYZ.

Таблица 4.9. Крупным планом вид наименования столбцов для расчёта.