Xreferat.com » Рефераты по информатике и программированию » Проведение АВС анализа в среде MS EXCEL

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

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


Рис.1 Исходные данные

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


2 этап. Решение задачи АВС


Для удобства расчетов необходимо отсортировать исходные данные по среднему запасу за квартал. Выделяем диапазон ячеек (В4:G53), заходим в меню Данные/Сортировка. В открывшемся окне выбираем по убыванию и нажимаем кнопку ОК.


Рис.2. Сортировка данных

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


Далее следует вычислить суммы среднего запаса за квартал по всем объектам, для этого необходимо установить курсор в ячейке В54 и в строке формул набрать следующую формулу «=СУММ (В4:В53)» и нажать клавишу enter.


Сумма среднего запаса за квартал = 120000.


Затем рассчитаем долю каждого объекта в общем объеме. Устанавливаем курсор в ячейку Н4 и выбираем формулу «=В4/В54». Для того чтобыв рассчитать долю каждого объекта, опускаем курсор в правый нижний угол ячейки Н4 до появления черного курсора до последнего объекта управления (рис.3).

Для расчета доли нарастающим итогом устанавливаем курсор в ячейку I4 и в строке формул набираем «=Н4», после этого опускаем курсор в ячейку I5, набираем формулу «=I4+Н5». Опускаем курсор в нижний правый угол ячейки I5, до появления черного курсора и опускаем до последнего объекта (рис.4).


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

Рис.3. Расчет доли объекта в общем объеме


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

Рис.4 расчет доли нарастающим итогом


Для распределения объектов управления по группам необходимо в ячейке I4 задать условие «=ЕСЛИ(I4<0,75;”A”; ЕСЛИ(I4<0,95;’B’;’C’))’ и скопировать это условие на все необходимые ячейки (рис5.).


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

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


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


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


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

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

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


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

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


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

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


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


Для решения этой задачи необходимо определить среднее значение по 4-м кварталам по всем объектам. Для этого воспользуемся встроенной в Excel функцией СРЗНАЧ. В ячейке К4 набираем формулу

«=СРЗНАЧ(D4:G4)»


И скопируем на нужные ячейки (рис.9)


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

Рис.9. Определение среднего значения


Далее следует определить коэффициент вариации, для этого воспользуемся встроенной функцией Excel СТАНДОТКЛОНП. В ячейке L4 наберем «=СТАНДОТКЛОНП(D4:G4)/К4» (рис.10). и скопируем на необходимые ячейки.


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

Рис 10. расчет коэффициента вариации


Для распределения объектов управления по группам необходимо в ячейке l4 задать условие «=ЕСЛИ(l4<0,1;“X”;EСЛИ(l4<0,25; “Y”; “Z”))» и скопировать его на нужные ячейки (рис.11).


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

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


4 этап. Построение кривой XYZ


Для построения кривой XYZ воспользуемся мастером построения диаграмм, находящихся на панели инструментов. В открывшемся окне выбираем тип нужной диаграммы, и нажимаем кнопку Далее. Затем необходимо выбрать диапазон ячеек, по которым будет строиться кривая, в нашем случае это L4:L54. Для подписания объектов по оси ОХ выбираем вкладку Ряды и в значении подпись по оси Х указываем «=Лист1!$C$4:$C$54». Далее, следует указать в каком месте будет располагаться диаграмма (рис.12).


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

Рис 12. кривая XYZ


5 этап. Проведение АВС-XYZ анализа


Для проведения АВС - XYZ анализа воспользуемся встроенной функцией СЦЕПИТЬ. Для этого в ячейке N4 зададим формулу


«=СЦЕПИТЬ(J4;M4)»


И скопируем ее на нужные ячейки (рис.13).


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

Рис. 13. АВС-XYZ анализ


Таким образом, данные задачи АВС и XYZ позволяют автоматизировано решать вопросы закупочной, распределительной, производственной, складской логистики.


Приложение


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

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту

Похожие рефераты: