Контрольная работа: Проведение АВС анализа в среде MS EXCEL

Проведение АВС анализа в среде MSEXCEL

1 этап. Занесение исходных данных в MSEXCEL

Рис.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<0,75;”A”; ЕСЛИ(I4<0,95;’B’;’C’))’ и скопировать это условие на все необходимые ячейки (рис5.).

Рис.5 распределение объектов управления по группам

3 этап. Построение кривой АВС

Чтобы построить кривую АВС необходимо воспользоваться кнопкой на панели инструментов (рис.6.), либо пунктом меню Вставка/Диаграмма.

Рис.6. Мастер диаграмм

В появившемся окне выбираем тип диаграммы (рис.7) и нажимаем клавишу далее. В открывшемся окне выбираем диапазон ячеек, по которым будет строиться график, в нашем случае это I4:I53(рис.8). в этом же окне выбираем вкладку Ряд и устанавливаем значение подписи оси Х: «=Лист1!$C$4:$C$54». После этого нажимаем клавиш. Далее, в открывшемся окне выбираем настройки, необходимые для более наглядного отображения графика и нажимаем Далее. Указываем место, где будет храниться диаграмма.

Рис.7. Построение диаграммы (шаг 1)

Рис 8. Построение диаграммы (шаг 2)

Решение задачи XYZ

--> ЧИТАТЬ ПОЛНОСТЬЮ <--