Проведение АВС анализа всреде MS EXCEL
1 этап. Занесениеисходных данных в MS EXCEL
Рис.1 Исходные данные
/>
2 этап. Решение задачиАВС
Для удобства расчетовнеобходимо отсортировать исходные данные по среднему запасу за квартал.Выделяем диапазон ячеек (В4:G53),заходим в меню Данные/Сортировка. В открывшемся окне выбираем по убыванию инажимаем кнопку ОК.
Рис.2. Сортировка данных
/>
Далее следует вычислитьсуммы среднего запаса за квартал по всем объектам, для этого необходимоустановить курсор в ячейке В54 и в строке формул набрать следующую формулу«=СУММ (В4: В53)» и нажать клавишу enter.
Сумма среднего запаса заквартал = 120000.
Затем рассчитаем долюкаждого объекта в общем объеме. Устанавливаем курсор в ячейку Н4 и выбираемформулу «=В4/В54». Для того чтобыв рассчитать долю каждого объекта, опускаемкурсор в правый нижний угол ячейки Н4 до появления черного курсора допоследнего объекта управления (рис.3).
Для расчета долинарастающим итогом устанавливаем курсор в ячейку I4 и в строке формул набираем «=Н4», после этого опускаемкурсор в ячейку I5, набираемформулу «=I4+Н5». Опускаем курсор в нижнийправый угол ячейки I5, до появлениячерного курсора и опускаем до последнего объекта (рис.4).
/>
Рис.3. Расчет долиобъекта в общем объеме
/>
Рис.4 расчет долинарастающим итогом
Для распределенияобъектов управления по группам необходимо в ячейке I4 задать условие «=ЕСЛИ(I4
/>
Рис.5 распределениеобъектов управления по группам
3 этап. Построение кривойАВС
Чтобы построить кривуюАВС необходимо воспользоваться кнопкой на панели инструментов (рис.6.), либопунктом меню Вставка/Диаграмма.
/>
Рис.6. Мастер диаграмм
В появившемся окневыбираем тип диаграммы (рис.7) и нажимаем клавишу далее. В открывшемся окневыбираем диапазон ячеек, по которым будет строиться график, в нашем случае это I4:I53(рис.8). в этом же окне выбираем вкладку Ряд иустанавливаем значение подписи оси Х: «=Лист1!$C$4:$C$54».После этого нажимаем клавиш. Далее, в открывшемся окне выбираем настройки,необходимые для более наглядного отображения графика и нажимаем Далее.Указываем место, где будет храниться диаграмма.
/>
Рис.7. Построениедиаграммы (шаг 1)
/>
Рис 8. Построениедиаграммы (шаг 2)
Решение задачи XYZ
Для решения этой задачинеобходимо определить среднее значение по 4-м кварталам по всем объектам. Дляэтого воспользуемся встроенной в Excelфункцией СРЗНАЧ. В ячейке К4 набираем формулу
«=СРЗНАЧ(D4:G4)»
И скопируем на нужныеячейки (рис.9)
/>
Рис.9. Определениесреднего значения
Далее следует определитькоэффициент вариации, для этого воспользуемся встроенной функцией Excel СТАНДОТКЛОНП. В ячейке L4 наберем «=СТАНДОТКЛОНП(D4:G4)/К4» (рис.10). и скопируем на необходимые ячейки.
/>
Рис 10. расчеткоэффициента вариации
Для распределенияобъектов управления по группам необходимо в ячейке l4 задать условие «=ЕСЛИ(l4
/>
Рис.11. распределениеобъектов по группам
4 этап. Построение кривойXYZ
Для построения кривой XYZ воспользуемся мастером построениядиаграмм, находящихся на панели инструментов. В открывшемся окне выбираем типнужной диаграммы, и нажимаем кнопку Далее. Затем необходимо выбрать диапазонячеек, по которым будет строиться кривая, в нашем случае это L4:L54. Для подписания объектов по оси ОХ выбираем вкладку Ряды ив значении подпись по оси Х указываем «=Лист1!$C$4:$C$54».Далее, следует указать в каком месте будет располагаться диаграмма (рис.12).
/>
Рис 12. кривая XYZ
5 этап. Проведение АВС-XYZ анализа
Для проведения АВС — XYZ анализа воспользуемся встроеннойфункцией СЦЕПИТЬ. Для этого в ячейке N4 зададим формулу
«=СЦЕПИТЬ(J4;M4)»
И скопируем ее на нужныеячейки (рис.13).
/>
Рис. 13. АВС-XYZ анализ
Таким образом, данныезадачи АВС и XYZ позволяют автоматизировано решатьвопросы закупочной, распределительной, производственной, складской логистики.
Приложение
/>