lab
.pdfПродолжение таблицы 3.2.1
|
А |
|
|
Б |
|
|
В |
|
|
Г |
|
|
Д |
|
|
Е |
|
|
Ж |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
13 |
|
|
Количество ярусов |
|
|
n |
|
|
- |
|
|
3 |
|
|
4 |
|
|
4 |
|
|
|
|
|
Коэффициент |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
неравномерности загрузки |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
14 |
|
|
склада |
|
|
Кнер |
|
|
- |
|
|
|
1,25 |
|
|
|
|
||
|
|
|
|
Коэффициент грузовой |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
15 |
|
|
площади |
|
|
Кгр |
|
|
- |
|
|
|
0,28 |
|
|
|
|
Решение задачи: для решения поставленной задачи необходимо определить:
1.Объемы упаковки и стандартной паллеты;
2.Количество упаковок в паллете для каждого вида товара;
3.Коэффициент наполняемости паллет;
4.Количество паллет на стеллаже;
5.Норму грузовой площади на 1 паллето/место;
6.Прогноз товарных запасов в весовых и объемных измерителях;
7.Требуемое количество паллето/мест;
8.Грузовую площадь для установки стеллажей;
9.Общую грузовую площадь склада.
Для определения требуемых параметров следует заполнить таблицу 3.2.2 с помощью набора функций MS Excel.
Таблица 3.2.2.
Расчетная таблица
|
|
Обозна- |
Единицы |
Товар |
Товар |
Товар |
№ |
Рассчитываемый показатель |
чение |
измерения |
А |
Б |
В |
1 |
Объем упаковки |
Vуп |
м3 |
|
|
|
2 |
Объѐм стандартной паллеты |
V |
м3 |
|
|
|
|
Количество упаковок в |
|
|
|
|
|
3 |
паллете |
Nуп |
шт. |
|
|
|
|
Коэффициент наполняемости |
|
|
|
|
|
4 |
паллет |
Кнп |
- |
|
|
|
|
Количество паллет на |
|
|
|
|
|
5 |
стеллаже |
Nпал |
шт. |
|
|
|
|
Норма грузовой площади на 1 |
|
|
|
|
|
6 |
паллето/место |
q |
м2 |
|
|
|
7 |
Прогноз товарных запасов |
Q |
кг |
|
|
|
8 |
Прогноз товарных запасов |
О |
м3 |
|
|
|
|
Требуемое количество |
|
|
|
|
|
9 |
паллето/мест |
N |
мест |
|
|
|
|
Площадь грузовая под |
|
|
|
|
|
10 |
установку стеллажей |
Sст |
м2 |
|
|
|
|
Общая площадь склада для |
|
|
|
|
|
11 |
груза |
Sгр |
м2 |
|
|
|
|
Итого склад |
Sобщ |
м2 |
|
|
|
31
Объемы упаковки и паллеты находятся путем перемножения соответствующих длин, высот и ширин.
Количество упаковок в паллете находится как отношение объема паллеты к объему упаковки.
Коэффициент наполняемости паллет равен отношению: |
|
||||||||||
|
Кнп |
|
Nуп |
Vупаковки |
|
(3.2.1) |
|||||
|
|
|
|
|
|||||||
|
|
|
|
|
Vпаллеты |
|
|||||
Количество паллет на стеллаже определяется по формуле: |
|
||||||||||
|
|
Nпал |
|
L |
|
n |
(3.2.2) |
||||
|
|
|
|
|
|
||||||
|
|
|
a |
|
|||||||
|
|
|
|
|
|||||||
|
|
|
|
|
|
|
|
|
|
||
Норма грузовой площади на одно паллето/место определяется |
|||||||||||
отношением площади стеллажа к количеству паллет на стеллаже: |
|
||||||||||
q |
|
Sстеллажа |
|
|
L H |
|
(3.2.3) |
||||
|
|
Nпал |
|
Nпал |
|||||||
|
|
|
|
|
Объем товарных запасов в кг Q определяется по формуле:
Q A T
Д
Объем товарных запасов в м3 определяется по формуле:
O |
Q Vупаковки |
|
M |
||
|
Требуемое количество паллето/мест определяется по формуле:
N |
O Kнер |
||
Vпаллеты |
Kнп |
||
|
(3.2.4)
(3.2.5)
(3.2.6)
Площадь под установку стеллажей представляет собой произведение требуемого количества паллето/мест и нормы грузовой площади на одно паллето/место:
Sст |
N q |
(3.2.7) |
|
|
Грузовая площадь склада определяется как отношение площади, необходимой для установки стеллажей к коэффициенту грузовой площади:
S |
|
Sст |
|
гр |
К гр |
|
|
|
|
(3.2.8) |
|
|
|
|
Общая площадь склада представляет собой сумму площадей, необходимых для размещения всех видов грузов, хранящихся на складе:
Sобщ |
(3.2.9) |
Sгр Sгр А Sгр Б Sгр В |
32
Таким образом, заполненная расчетная таблица будет иметь следующий
вид:
Таблица 3.2.2.
Расчетная таблица
|
|
|
|
|
|
|
|
|
|
Единицы |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Обозна- |
|
|
измерени |
|
|
Товар |
|
|
Товар |
|
|
Товар |
|
|
№ |
|
|
Рассчитываемый показатель |
|
|
чение |
|
|
я |
|
|
А |
|
|
Б |
|
|
В |
|
|
1 |
|
|
Объем упаковки |
|
|
Vуп |
|
|
м3 |
|
|
0,12 |
|
|
0,128 |
|
|
0,015 |
|
|
2 |
|
|
Объѐм стандартной паллеты |
|
|
V |
|
|
м3 |
|
|
1,08 |
|
|
1,2 |
|
|
1,5 |
|
|
|
|
|
Количество упаковок в |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
|
паллете |
|
|
Nуп |
|
|
шт. |
|
|
9 |
|
|
9 |
|
|
100 |
|
|
|
|
|
Коэффициент |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
|
наполняемости паллет |
|
|
Кнп |
|
|
- |
|
|
1 |
|
|
0,96 |
|
|
1 |
|
|
|
|
|
Количество паллет на |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
|
|
стеллаже |
|
|
Nпал |
|
|
шт. |
|
|
9 |
|
|
13 |
|
|
20 |
|
|
|
|
|
Норма грузовой площади на |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
|
|
1 паллето/место |
|
|
q |
|
|
м2 |
|
|
0,37 |
|
|
0,32 |
|
|
0,55 |
|
|
7 |
|
|
Прогноз товарных запасов |
|
|
Q |
|
|
кг |
|
|
35200 |
|
|
64000 |
|
|
16800 |
|
|
8 |
|
|
Прогноз товарных запасов |
|
|
О |
|
|
м3 |
|
|
211,20 |
|
|
546,13 |
|
|
22,91 |
|
|
|
|
|
Требуемое количество |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
9 |
|
|
паллето/мест |
|
|
N |
|
|
мест |
|
|
245 |
|
|
593 |
|
|
20 |
|
|
1 |
|
|
Площадь грузовая под |
|
|
|
|
|
|
|
|
|
|
|
188,16 |
|
|
11,00 |
|
|
0 |
|
|
установку стеллажей |
|
|
Sст |
|
|
м2 |
|
|
91,467 |
|
|
3 |
|
|
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
1 |
|
|
Общая площадь склада для |
|
|
|
|
|
|
|
|
326,66 |
|
|
672,01 |
|
|
39,28 |
|
|
1 |
|
|
груза |
|
|
Sгр |
|
|
м2 |
|
|
7 |
|
|
2 |
|
|
6 |
|
|
|
|
|
Итого склад |
|
|
Sобщ |
|
|
м2 |
|
|
|
1037,965 |
|
|
|
Далее студент составляет отчет о проделанной работе.
Указания по выполнению 3 этапа лабораторной работы 3
Задача 3. Имеются данные об отгрузке автомобильных запчастей со склада (табл. 3.3.1). Требуется провести АВС-анализ, XYZ-анализ, совместить результаты ABC- и XYZ-анализа, сделать вывод о сбалансированности складских запасов автомобильных запчастей. Представить полученные результаты графически.
Решение задачи: для решения задачи необходимо определить:
1.Общую сумму отгрузки за рассматриваемый период;
2.Долю каждой отгрузки в общем объеме;
3.Разбить ассортимент на группы А, В и С;
4.Отобразить полученные результаты графически;
5.Рассчитать коэффициенты вариации отгрузок по каждому товару;
6.Сформировать ассортиментные группы X, Y и Z;
7.Отобразить полученные результаты на графике;
8.Совместить результаты АВС- и XYZ-анализа;
9.Представить полученные результаты в графическом виде.
В первую очередь необходимо заполнить таблицу Excel для проведения АВС-анализа.
33
Сумма отгрузок за период представлена в итоговом столбце таблицы с исходными данными к задаче.
Таблица 3.3.1.
Данные об отгрузке автомобильных запчастей за период с января по
май прошлого года
Наименование |
|
|
Отгрузка за период, руб. |
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
товара |
январь |
февраль |
март |
апрель |
май |
|
июнь |
июль |
итого |
|
|
||||||||
|
|
|
|
|
|
|
|
|
|
Карбюратор |
4347,3 |
5439 |
7924,1 |
4567,2 |
3387,2 |
|
2731 |
3871 |
32266,8 |
Клапаны |
25000 |
23789 |
37030 |
47301 |
41000 |
|
18500 |
13000 |
205620 |
Термостат |
4031,17 |
2561,2 |
3789,1 |
2395,8 |
5093,1 |
|
4320,1 |
5941,1 |
28131,57 |
Поршень |
22058 |
27836 |
30291 |
25603 |
17403 |
|
15706 |
14613 |
153510 |
Головка |
|
|
|
|
|
|
|
|
|
цилиндров |
13290 |
17310 |
15600 |
20000 |
12300 |
|
17000 |
17000 |
112500 |
Шкив рулевого |
|
|
|
|
|
|
|
|
|
вала |
3756,15 |
4520 |
6290 |
6542 |
7009 |
|
10200 |
7600 |
45917,15 |
Коленчатый вал |
12474 |
15048 |
11654 |
13290 |
17236 |
|
15720 |
15900 |
101322 |
Генератор |
11135 |
11345 |
11575 |
12000 |
12675 |
|
13300 |
22365 |
94395 |
Рулевая тяга |
23335 |
15600 |
0 |
25335 |
0 |
|
0 |
1400 |
65670 |
КПП |
485200 |
568670 |
316530 |
65800 |
453400 |
|
342580 |
225000 |
2457180 |
Диск сцепления |
4852 |
5686 |
3165 |
6580 |
4534 |
|
3425 |
16758 |
45000 |
Вал первичный |
2852 |
2686,7 |
1653 |
3580 |
3354,05 |
|
4258 |
2255 |
20638,75 |
Фильтр масляный |
7485 |
4586,7 |
5165,3 |
5470 |
5234 |
|
4547,3 |
7250 |
39738,3 |
Колодки |
|
|
|
|
|
|
|
|
|
тормозные |
7354 |
4387,8 |
6265,4 |
5360 |
4534 |
|
6425 |
4822 |
39148,2 |
Рулевой вал |
4472 |
4686 |
3865 |
5180 |
4534 |
|
2985 |
2250 |
27972 |
Втулка |
|
|
|
|
|
|
|
|
|
направляющая |
4323 |
5686,76 |
3165 |
4587 |
4852 |
|
3434 |
3894 |
29941,76 |
Вкладыши |
|
|
|
|
|
|
|
|
|
шатунные |
4352 |
4600 |
5730 |
4458 |
3400 |
|
3580 |
3250 |
29370 |
Большой насос |
3200 |
5317 |
5317 |
3840 |
4530 |
|
3420 |
3080 |
28704 |
Аккумулятор |
5938 |
8670 |
6530 |
5800 |
5340 |
|
6580 |
4730 |
43588 |
Ролик натяжения |
3620 |
3670 |
3230 |
4200 |
3515 |
|
3425 |
3250 |
24910 |
34
Таблица 3.3.2.
Таблица Excel для проведения АВС-анализа
|
|
|
|
Доля в общей |
|
Доля в |
|
|
|
|
Доля в |
|
ассорти- |
||
№ по |
|
Сумма |
сумме |
|
|||
Наименование |
общей |
|
менте |
||||
поряд |
отгрузок |
отгрузок |
Группа |
||||
товара |
сумме |
нараста |
|||||
ку |
за период |
нарастающим |
|
||||
|
отгрузок |
|
ющим |
||||
|
|
|
итогом |
|
|||
|
|
|
|
|
итогом |
||
|
|
|
|
|
|
||
1 |
Карбюратор |
|
|
|
|
|
|
2 |
Клапаны |
|
|
|
|
|
|
3 |
Термостат |
|
|
|
|
|
|
4 |
Поршень |
|
|
|
|
|
|
5 |
Головка цилиндров |
|
|
|
|
|
|
|
Шкив рулевого |
|
|
|
|
|
|
6 |
вала |
|
|
|
|
|
|
7 |
Коленчатый вал |
|
|
|
|
|
|
8 |
Генератор |
|
|
|
|
|
|
9 |
Рулевая тяга |
|
|
|
|
|
|
10 |
КПП |
|
|
|
|
|
|
11 |
Диск сцепления |
|
|
|
|
|
|
12 |
Вал первичный |
|
|
|
|
|
|
13 |
Фильтр масляный |
|
|
|
|
|
|
|
Колодки |
|
|
|
|
|
|
14 |
тормозные |
|
|
|
|
|
|
15 |
Рулевой вал |
|
|
|
|
|
|
|
Втулка |
|
|
|
|
|
|
16 |
направляющая |
|
|
|
|
|
|
|
Вкладыши |
|
|
|
|
|
|
17 |
шатунные |
|
|
|
|
|
|
18 |
Большой насос |
|
|
|
|
|
|
19 |
Аккумулятор |
|
|
|
|
|
|
20 |
Ролик натяжения |
|
|
|
|
|
|
|
Итого |
|
|
|
|
|
Доля в общей сумме отгрузок представляет собой отношение суммы отгрузок по каждому конкретному виду товара к общей сумме отгрузок за рассматриваемый период. После определения доли в общей сумме отгрузок по каждому наименованию продукции необходимо упорядочить доли по убыванию. Для этого нужно выделить всю таблицу, включая подписи столбцов, но без итоговых сумм. Затем в меню «Данные» выбрать пункт «Сортировка». Откроется окно «Сортировка диапазона». В этом окне необходимо установить переключатель «Идентифицировать поля по подписям…», а в поле «Сортировать по…» - «Доля в общей сумме отгрузок». Затем установить переключатель «по убыванию» и нажать «ОК». Осуществится сортировка.
Далее заполняется столбец «Доля в общей сумме отгрузок нарастающим итогом».
35
Разбить ассортимент товаров на группы А, В и С можно, указав соответствующую букву в столбце «Группа», применив «правило Парето». Можно автоматизировать разбиение на группы, используя формулы. Например, воспользовавшись формулой:
=ЕСЛИ (Е2<=0,8; «А»; ЕСЛИ (Е2<=0,95; «В»; «С»)). (3.3.1)
В столбце «Доля в ассортименте нарастающим итогом» рассчитывается доля объектов анализа нарастающим итогом. Один из вариантов расчета: в ячейку «G2» заносится формула =1/СЧЁТЗ (F2:F21); в ячейку «G3» заносится формула =G2+1/СЧЁТЗ (F$2:F$21) и копируется в ячейки, расположенные ниже. Формула СЧЁТЗ считает непустые ячейки. При этом не важно, цифры или буквы в них содержатся.
Заполненная таблица с АВС-анализом выглядит следующим образом: Таблица 3.3.2.
Таблица Excel для проведения АВС-анализа
|
|
|
|
Доля в общей |
|
Доля в |
|
|
|
|
Доля в |
|
ассорти- |
||
№ по |
|
Сумма |
сумме |
|
|||
Наименование |
общей |
|
менте |
||||
поряд- |
отгрузок |
отгрузок |
Группа |
||||
товара |
сумме |
нараста- |
|||||
ку |
за период |
нарастающим |
|
||||
|
отгрузок |
|
ющим |
||||
|
|
|
итогом |
|
|||
|
|
|
|
|
итогом |
||
|
|
|
|
|
|
||
10 |
КПП |
2457180 |
0,6777 |
0,6777 |
A |
0,05 |
|
2 |
Клапаны |
205620 |
0,0567 |
0,7345 |
A |
0,10 |
|
4 |
Поршень |
153510 |
0,0423 |
0,7768 |
A |
0,15 |
|
5 |
Головка цилиндров |
112500 |
0,0310 |
0,8078 |
B |
0,20 |
|
7 |
Коленчатый вал |
101322 |
0,0279 |
0,8358 |
B |
0,25 |
|
8 |
Генератор |
94395 |
0,0260 |
0,8618 |
B |
0,30 |
|
9 |
Рулевая тяга |
65670 |
0,0181 |
0,8799 |
B |
0,35 |
|
6 |
Шкив рулевого вала |
45917,15 |
0,0127 |
0,8926 |
B |
0,40 |
|
11 |
Диск сцепления |
45000 |
0,0124 |
0,9050 |
B |
0,45 |
|
19 |
Аккумулятор |
43588 |
0,0120 |
0,9170 |
B |
0,50 |
|
13 |
Фильтр масляный |
39738,3 |
0,0110 |
0,9280 |
B |
0,55 |
|
14 |
Колодки тормозные |
39148,2 |
0,0108 |
0,9388 |
B |
0,60 |
|
1 |
Карбюратор |
32266,8 |
0,0089 |
0,9477 |
B |
0,65 |
|
16 |
Втулка направляющая |
29941,76 |
0,0083 |
0,9559 |
C |
0,70 |
|
17 |
Вкладыши шатунные |
29370 |
0,0081 |
0,9640 |
C |
0,75 |
|
18 |
Большой насос |
28704 |
0,0079 |
0,9720 |
C |
0,80 |
|
3 |
Термостат |
28131,57 |
0,0078 |
0,9797 |
C |
0,85 |
|
15 |
Рулевой вал |
27972 |
0,0077 |
0,9874 |
C |
0,90 |
|
20 |
Ролик натяжения |
24910 |
0,0069 |
0,9943 |
C |
0,95 |
|
12 |
Вал первичный |
20638,75 |
0,0057 |
1,0000 |
C |
1,00 |
|
|
|
3625523,5 |
|
|
|
|
|
|
Итого |
3 |
1,0000 |
Х |
Х |
Х |
Графическое изображение ассортиментных групп представлено на рисунке 3.3.1.
36
|
1,2000 |
|
|
|
|
|
|
отгрузок |
1,0000 |
|
|
|
|
|
|
0,8000 |
|
|
|
|
|
|
|
0,6000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Доля |
0,4000 |
|
|
|
|
|
|
0,2000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0,0000 |
|
|
|
|
|
|
|
0,00 |
0,20 |
0,40 |
0,60 |
0,80 |
1,00 |
1,20 |
|
|
|
Доля ассортимента |
|
|
Рис. 3.3.1. Кривая АВС-анализа
Для проведения XYZ-анализа в Excel следует построить таблицу, аналогичную таблице 3.3.3. Для расчета коэффициента вариации можно
воспользоваться формулой: |
|
= СТАНДОТКЛОН(C3:I3)/СРЗНАЧ(C3:J3), |
(3.3.2) |
где «С3»:«I3» – диапазон анализируемых периодов. Данную формулу необходимо скопировать в ячейки, расположенные ниже.
Чтобы упорядочить товары в порядке возрастания значения коэффициента вариации, необходимо произвести сортировку по «коэффициенту вариации»
Далее необходимо расставить индикаторы групп в столбце «Группа».
Сделать это можно с помощью формулы: |
|
= ЕСЛИ (K3<=0,1;"X"; ЕСЛИ (K3<=0,25;"Y";"Z")). |
(3.3.3) |
Заключительное действие – рассчитать долю в ассортименте каждого товара нарастающим итогом. Для этого можно воспользоваться такой конструкцией:
= СЧЁТЗ ($L$3:L3)/СЧЁТЗ ($L$3:$L$22). |
(3.3.4) |
Построение кривой XYZ-анализа часто позволяет уточнить границы интервалов групп по точкам перегиба. Для ее построения используются данные о доле в ассортименте нарастающим итогом и коэффициенте вариации.
37
Таблица 3.3.3.
Таблица Excel для проведения XYZ – анализа
|
|
|
|
Отгрузка за период, руб. |
|
|
|
Доля в |
|||||
|
|
|
|
|
|
|
|
|
|
Коэф- |
|
ассор- |
|
|
|
|
|
|
|
|
|
|
|
|
|||
№ |
Наименова |
|
|
|
|
|
|
|
|
фици- |
Груп- |
тимен- |
|
п/п |
ние товара |
ян- |
фев- |
март |
ап- |
май |
июнь |
июль |
итого |
ент |
па |
те |
|
варь |
раль |
рель |
вари- |
нараста |
|||||||||
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
ации |
|
ющим |
|
|
|
|
|
|
|
|
|
|
|
|
|
итогом |
|
|
Карбюра- |
|
|
|
|
|
|
|
|
|
|
|
|
1 |
тор |
|
|
|
|
|
|
|
|
|
|
|
|
2 |
Клапаны |
|
|
|
|
|
|
|
|
|
|
|
|
3 |
Термостат |
|
|
|
|
|
|
|
|
|
|
|
|
4 |
Поршень |
|
|
|
|
|
|
|
|
|
|
|
|
|
Головка |
|
|
|
|
|
|
|
|
|
|
|
|
5 |
цилиндров |
|
|
|
|
|
|
|
|
|
|
|
|
|
Шкив |
|
|
|
|
|
|
|
|
|
|
|
|
|
рулевого |
|
|
|
|
|
|
|
|
|
|
|
|
6 |
вала |
|
|
|
|
|
|
|
|
|
|
|
|
|
Коленча- |
|
|
|
|
|
|
|
|
|
|
|
|
7 |
тый вал |
|
|
|
|
|
|
|
|
|
|
|
|
8 |
Генератор |
|
|
|
|
|
|
|
|
|
|
|
|
|
Рулевая |
|
|
|
|
|
|
|
|
|
|
|
|
9 |
тяга |
|
|
|
|
|
|
|
|
|
|
|
|
10 |
КПП |
|
|
|
|
|
|
|
|
|
|
|
|
|
Диск |
|
|
|
|
|
|
|
|
|
|
|
|
11 |
сцепления |
|
|
|
|
|
|
|
|
|
|
|
|
|
Вал |
|
|
|
|
|
|
|
|
|
|
|
|
12 |
первичный |
|
|
|
|
|
|
|
|
|
|
|
|
|
Фильтр |
|
|
|
|
|
|
|
|
|
|
|
|
13 |
масляный |
|
|
|
|
|
|
|
|
|
|
|
|
|
Колодки |
|
|
|
|
|
|
|
|
|
|
|
|
14 |
тормозные |
|
|
|
|
|
|
|
|
|
|
|
|
|
Рулевой |
|
|
|
|
|
|
|
|
|
|
|
|
15 |
вал |
|
|
|
|
|
|
|
|
|
|
|
|
|
Втулка |
|
|
|
|
|
|
|
|
|
|
|
|
|
направляю |
|
|
|
|
|
|
|
|
|
|
|
|
16 |
щая |
|
|
|
|
|
|
|
|
|
|
|
|
|
Вкладыши |
|
|
|
|
|
|
|
|
|
|
|
|
17 |
шатунные |
|
|
|
|
|
|
|
|
|
|
|
|
|
Большой |
|
|
|
|
|
|
|
|
|
|
|
|
18 |
насос |
|
|
|
|
|
|
|
|
|
|
|
|
|
Аккумуля- |
|
|
|
|
|
|
|
|
|
|
|
|
19 |
тор |
|
|
|
|
|
|
|
|
|
|
|
|
|
Ролик |
|
|
|
|
|
|
|
|
|
|
|
|
20 |
натяжения |
|
|
|
|
|
|
|
|
|
|
|
Заполненная таблица 3.3.3 и кривая XYZ-анализа выглядят следующим образом:
38
Таблица 3.3.3.
Таблица Excel для проведения XYZ-анализа
|
|
|
|
|
Отгрузка за период, руб. |
|
|
|
|
|
Доля в |
||
|
|
|
|
|
|
|
|
|
|
ассорти |
|||
|
|
|
|
|
|
|
|
|
|
|
Коэффи- |
|
|
№ |
Наименова- |
|
|
|
|
|
|
|
|
|
Груп |
менте |
|
|
|
|
|
|
|
|
|
|
циент |
||||
п/п |
ние товара |
|
|
|
|
|
|
|
|
|
па |
нараста |
|
январь |
февраль |
март |
|
апрель |
май |
июнь |
июль |
итого |
вариации |
||||
|
|
|
|
ющим |
|||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
итогом |
|
Ролик |
|
|
|
|
|
|
|
|
|
|
|
|
20 |
натяжения |
3620 |
3670 |
3230 |
|
4200 |
3515 |
3425 |
3250 |
24910 |
0,0529 |
X |
0,05 |
|
Коленчатый |
|
|
|
|
|
|
|
|
|
|
|
|
7 |
вал |
12474 |
15048 |
11654 |
|
13290 |
17236 |
15720 |
15900 |
101322 |
0,0804 |
X |
0,10 |
|
Головка |
|
|
|
|
|
|
|
|
|
|
|
|
5 |
цилиндров |
13290 |
17310 |
15600 |
|
20000 |
12300 |
17000 |
17000 |
112500 |
0,0928 |
X |
0,15 |
|
Колодки |
|
|
|
|
|
|
|
|
|
|
|
|
14 |
тормозные |
7354 |
4387,8 |
6265,4 |
|
5360 |
4534 |
6425 |
4822 |
39148,2 |
0,1140 |
Y |
0,20 |
19 |
Аккумулятор |
5938 |
8670 |
6530 |
|
5800 |
5340 |
6580 |
4730 |
43588 |
0,1153 |
Y |
0,25 |
|
Втулка |
|
|
|
|
|
|
|
|
|
|
|
|
16 |
направляющая |
4323 |
5686,76 |
3165 |
|
4587 |
4852 |
3434 |
3894 |
29941,8 |
0,1158 |
Y |
0,30 |
|
Вкладыши |
|
|
|
|
|
|
|
|
|
|
|
|
17 |
шатунные |
4352 |
4600 |
5730 |
|
4458 |
3400 |
3580 |
3250 |
29370 |
0,1182 |
Y |
0,35 |
|
Фильтр |
|
|
|
|
|
|
|
|
|
|
|
|
13 |
масляный |
7485 |
4586,7 |
5165,3 |
|
5470 |
5234 |
4547,3 |
7250 |
39738,3 |
0,1213 |
Y |
0,40 |
18 |
Большой насос |
3200 |
5317 |
5317 |
|
3840 |
4530 |
3420 |
3080 |
28704 |
0,1338 |
Y |
0,45 |
15 |
Рулевой вал |
4472 |
4686 |
3865 |
|
5180 |
4534 |
2985 |
2250 |
27972 |
0,1486 |
Y |
0,50 |
4 |
Поршень |
22058 |
27836 |
30291 |
|
25603 |
17403 |
15706 |
14613 |
153510 |
0,1617 |
Y |
0,55 |
|
Вал |
|
|
|
|
|
|
|
|
|
|
|
|
12 |
первичный |
2852 |
2686,7 |
1653 |
|
3580 |
3354,05 |
4258 |
2255 |
20638,8 |
0,1681 |
Y |
0,60 |
8 |
Генератор |
11135 |
11345 |
11575 |
|
12000 |
12675 |
13300 |
22365 |
94395 |
0,1691 |
Y |
0,65 |
3 |
Термостат |
4031,17 |
2561,2 |
3789,1 |
|
2395,8 |
5093,1 |
4320,1 |
5941,1 |
28131,6 |
0,1813 |
Y |
0,70 |
|
Шкив |
|
|
|
|
|
|
|
|
|
|
|
|
6 |
рулевого вала |
3756,15 |
4520 |
6290 |
|
6542 |
7009 |
10200 |
7600 |
45917,2 |
0,1835 |
Y |
0,75 |
1 |
Карбюратор |
4347,3 |
5439 |
7924,1 |
|
4567,2 |
3387,2 |
2731 |
3871 |
32266,8 |
0,2106 |
Y |
0,80 |
2 |
Клапаны |
25000 |
23789 |
37030 |
|
47301 |
41000 |
18500 |
13000 |
205620 |
0,2450 |
Y |
0,85 |
10 |
КПП |
485200 |
568670 |
316530 |
|
65800 |
453400 |
342580 |
225000 |
2457180 |
0,2775 |
Z |
0,90 |
|
Диск |
|
|
|
|
|
|
|
|
|
|
|
|
11 |
сцепления |
4852 |
5686 |
3165 |
|
6580 |
4534 |
3425 |
16758 |
45000 |
0,4185 |
Z |
0,95 |
9 |
Рулевая тяга |
23335 |
15600 |
0 |
|
25335 |
0 |
0 |
1400 |
65670 |
0,7102 |
Z |
1,00 |
вариации |
0,8000 |
|
|
|
|
|
|
0,7000 |
|
|
|
|
|
|
|
0,6000 |
|
|
|
|
|
|
|
0,5000 |
|
|
|
|
|
|
|
Коэффициент |
0,4000 |
|
|
|
|
|
|
0,3000 |
|
|
|
|
|
|
|
0,2000 |
|
|
|
|
|
|
|
0,1000 |
|
|
|
|
|
|
|
0,0000 |
|
|
|
|
|
|
|
|
0,00 |
0,20 |
0,40 |
0,60 |
0,80 |
1,00 |
1,20 |
|
|
|
Доля в ассортименте |
|
|
Рис. 3.3.2. Кривая XYZ-анализа
Далее необходимо совместить результаты АВС- и XYZ-анализа. Для этого, воспользовавшись функцией ВПР, результаты вычислений, полученные ранее, необходимо собрать в одну таблицу. Затем с помощью функции СЦЕПИТЬ следует объединить идентификаторы групп АВС и XYZ, чтобы классифицировать товары одновременно и по доле в отгрузках, и по
39
стабильности отгрузок. После соответствующих преобразований получаем таблицу следующего вида:
|
|
|
|
Таблица 3.3.4. |
||
|
Ранжирование по двум признакам |
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
Доля в |
|
|
|
Доля в |
Коэффи- |
Группа по |
ассорти- |
|
№ по |
|
общей |
менте |
|||
Наименование товара |
циент |
двум |
||||
порядку |
сумме |
нарастаю- |
||||
|
вариации |
признакам |
||||
|
|
отгрузок |
щим |
|||
|
|
|
|
|||
|
|
|
|
|
итогом |
|
10 |
КПП |
0,677745 |
0,2774968 |
AZ |
0,05 |
|
5 |
Головка цилиндров |
0,03103 |
0,0928021 |
BX |
0,10 |
|
7 |
Коленчатый вал |
0,027947 |
0,0804204 |
BX |
0,15 |
|
2 |
Клапаны |
0,056715 |
0,2450186 |
BY |
0,20 |
|
4 |
Поршень |
0,042341 |
0,1617017 |
BY |
0,25 |
|
8 |
Генератор |
0,026036 |
0,1690587 |
BY |
0,30 |
|
6 |
Шкив рулевого вала |
0,012665 |
0,1835222 |
BY |
0,35 |
|
9 |
Рулевая тяга |
0,018113 |
0,7101709 |
BZ |
0,40 |
|
20 |
Ролик натяжения |
0,006871 |
0,0528745 |
CX |
0,45 |
|
19 |
Аккумулятор |
0,012023 |
0,1153407 |
CY |
0,50 |
|
13 |
Фильтр масляный |
0,010961 |
0,1212724 |
CY |
0,55 |
|
14 |
Колодки тормозные |
0,010798 |
0,1139935 |
CY |
0,60 |
|
1 |
Карбюратор |
0,0089 |
0,2105809 |
CY |
0,65 |
|
16 |
Втулка направляющая |
0,008259 |
0,115825 |
CY |
0,70 |
|
17 |
Вкладыши шатунные |
0,008101 |
0,118174 |
CY |
0,75 |
|
18 |
Большой насос |
0,007917 |
0,1337536 |
CY |
0,80 |
|
3 |
Термостат |
0,007759 |
0,1812727 |
CY |
0,85 |
|
15 |
Рулевой вал |
0,007715 |
0,1486304 |
CY |
0,90 |
|
12 |
Вал первичный |
0,005693 |
0,1681272 |
CY |
0,95 |
|
11 |
Диск сцепления |
0,012412 |
0,4185316 |
CZ |
1,00 |
Далее строится сводная таблица 3.3.5 с помощью надстройки «Сводная таблица», находящейся на вкладке «Данные». В сводную таблицу переносятся «Группы по двум признакам», «Доля в общей сумме отгрузок», «Коэффициент вариации», «Доля в ассортименте». При этом в сводной таблице задать следующие «Параметры поля»: для столбцов «Группы по двум признакам» и «Доля в общей сумме отгрузок» – «Сумма»; для столбца «Коэффициент вариации» – «Среднее»; для столбца «Доля в ассортименте» – «Доля». Сводная таблица примет вид:
40