Магазин занимается розничной продажей товаров

Перенесем исходные данные в программу Excel, затем рассчитаем оборот за квартал по каждой группе товаров, используя функцию СУММ. Результат расчета отражены на рисунке 9.1, расположенном ниже.
Рис. 9.1. Расчет оборота за квартал по номенклатуре товаров
Отсортируем группы товаров по параметру «Оборот за квартал»: Excel → Данные → Сортировка, от максимальной суммы оборота к минимальной, т. к. в группу А попадает продукция с максимальным показателем анализируемого признака. Результаты отражены на рисунке 9.2, расположенном ниже.
Рис. 9.2. Сортировка по убыванию показателей анализируемого признака
Рассчитаем долю оборота по каждому товару. Так для позиции №15 доля оборота будем рассчитываться с помощью формулы =Е2/СУММ($E$2:$E$21)*100. Результаты расчета представлены на рисунке 9.3, расположенном ниже.
Рис. 9.3. Определение доли продукции в объеме предприятия
Рассчитаем долю прибыли с накопительным итогом:
- для первого наименования товара в ячейке G2 следует записать формулу =F2;
- для второй группы наименований товаров в ячейке G3 формула примет вид =F3+G2;
- для последующих наименований товаров скопируем формулу из ячейки G3 в диапазон ячеек G4:G21.
Результат расчета представлен на рисунке 9.4, расположенном ниже.
Рис. 9.4. Оценка доли оборота с накопительным итогом для товарных позиций
Так как в группу А входит 15–25 % товаров с наибольшей величиной, в нашем случае наблюдается прибыль . При этом суммарная их доля не должна превышать 80 %. Аналогичные действия выполняем для групп В и С.
Используя функцию ЕСЛИ, определим границу до 80 % для группы товаров А и границу до 95 % для групп товаров В, а остальное войдет в группу С. Таким образом, функция для определения принадлежности первого товара (ячейка Е2) к группе А, В или С будет следующей: =ЕСЛИ(G2<=80%;"A";ЕСЛИ(G2<=95%;"B"; ЕСЛИ(G2<=100%;"C"))).
Аналогичным образом определяется принадлежность к группам других представленных товаров. Результаты расчета приведены на рисунке 9.5, расположенном ниже.
Рис. 9.5. АВС-анализ прибыли от реализации товаров
Отобразим полученные результаты вычислений в графическом виде по столбцу G (рис. 9.6).
Рис. 9.6. Кривая АВС-анализа
Для проведения XYZ-анализа необходимо иметь данные не только в среднем за период (квартал, год), но и за более короткий промежуток (месяц, квартал). В условии задачи даны значения показателей по месяцам, а также произведен расчет показателя в целом за квартал. Поэтому следует рассчитать коэффициент вариации по каждой товарной позиции.
Например, коэффициент вариации по товарной позиции №15 рассчитаем как =СТАНДОТКЛОН.Г(B2:D2)/СРЗНАЧ(B2:D2) и составит 1,45 % (формат ячейки – процентный)