ABC-анализ с использованием фильтров

ABC-анализ с использованием фильтров

В практической работе на компьютере часто требуется готовить, сортировать и фильтровать различные списки. Сортировка (упорядочивание по возрастанию или убыванию) осуществляется через пункт меню ДанныеàСортировка… Предварительно всю группу сортируемых ячеек/столбцов следует выделить мышью (разберите самостоятельно).

Здесь рассмотрим использование фильтров Excel для оперативного получения оценочных данных на примере задачи ABC-анализа. Суть ABC-анализа состоит в разбиении всех товаров (клиентов) по важности на три группы (A, B и C) по некоторому показателю – объему продаж, прибыли и т.д.

ABC-анализ строится на законе Парето, который гласит, что, как правило, 80% всего объема продаж (прибыли) фирмы достигается за счет не более, чем 20% товаров (клиентов), и, наоборот, 80% всех товаров (клиентов) дают не более 20% оборота.

В связи с этим все товары (клиентов)  можно разбить на 3 группы:

·         A – товары (клиенты), дающие 80% прибыли. В группу А попадают те, которые в основном определяют обороты фирмы;

·         C – 80% товаров (клиентов) с наименьшей важностью, доля участия которых в обороте фирмы невелика;

·         В – все остальные товары (клиенты), занимающие промежуточное положение между группами A и С, если группы А и С не пересекаются.

Пусть имеются данные по отгрузке некоторых товаров за месяц, приведенные на рис. 23. Необходимо определить суммы (и %) отгрузки, которые дают 80% товаров с наименьшей важностью (группа С) и 20% товаров с наибольшей важностью.

Определим сумму отгрузки, которую дают 80% товаров с наименьшей важностью (группа С). Для этого проделайте следующие действия:

Рис. 23

1.       В ячейку В31 запишите формулу =СУММ(B3:B29) – общая сумма отгрузки будет равна 9346500 руб.

2.       Установите фильтр на данные, т.е. выделите диапазон ячеек А2:В29 и выберите ДанныеàФильтрàАвтофильтр. В строке 2 появятся кнопки списков (как на рис. 24).

3.       В списке Сумма (руб) выберите пункт (Первые 10…) как на рис. 24. В появившемся окне установите условие:

4.       После нажатия ОК Вы получите 80% списка товаров, дающих минимальные объемы – рис. 24. Выделите отфильтрованный диапазон А5:А29 – после выделения в строке состояния (внизу) Вы увидите Сумму выделенной области – рис. 24.

Как видно, 80% товаров наименьшей важности дает отгрузку 3036500 руб., что составляет около 32.5%  от общего объема продаж 9346500 руб.

Аналогично оцените объем продаж 20% наибольшей важности, применив следующее условие:

Вы должны получить сумму 5570000 руб., что составляет почти 60% от объема продаж и подтверждает правило Парето.

Снимите фильтр, еще раз выбрав пункт ДанныеàФильтрàАвтофильтр.

Рис. 24

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

Скачано с www.znanio.ru