KHIZHNYAKOVABC-XYZ ассортимент талдауы Excel-де: қадамдық нұсқаулық
ABC-XYZ ассортимент талдауы — Excel үлгісі — Үлгіні ашу →
Дүкен немесе интернет-дүкен ассортиментінде жүздеген позиция болуы мүмкін, бірақ түсімнің негізгі бөлігін әдетте олардың аз ғана бөлігі береді. Қалғандары айналым қаражатын байлап, сөрелер мен қоймада орын алады. Қай тауарды әрқашан қорда ұстау керек, қайсысын тапсырыспен ғана әкелу керек, ал қайсысын ассортименттен алып тастау керек — осы сұрақтарға ABC-XYZ талдауы жауап береді.
Әдіс күрделі бағдарламаларды қажет етпейді: бір жылдық айлық сатылым деректері мен бірнеше Excel формуласы жеткілікті. Төменде талдауды қадамдап, мысалмен көрсетеміз.
ABC және XYZ дегеніміз не
ABC талдауы тауарларды түсімге қосқан үлесі бойынша үш топқа бөледі (Парето қағидасына негізделген):
- A — түсімнің алғашқы 80%-ын беретін тауарлар;
- B — келесі 15%;
- C — қалған 5%.
XYZ талдауы сұраныстың тұрақтылығын вариация коэффициенті (CV) бойынша бағалайды:
- X — CV 10%-ға дейін: сатылым тұрақты, болжау оңай;
- Y — 10–25%: ауытқулар бар, бірақ болжамды;
- Z — 25%-дан жоғары: сұраныс тұрақсыз.
Шекаралар — жиі қолданылатын бағдарлар, оларды өз бизнесіңізге қарай өзгертуге болады.
1-қадам: деректерді дайындаңыз
Әр тауарға бір жол, 12 бағанда айлық сатылым (ақшамен немесе данамен — бірақ барлық тауар үшін бірдей бірлікпен). Жаңа тауарлар мен ассортиментке жыл ортасында кірген позицияларды бөлек қарастырыңыз — олардың статистикасы толық емес.
2-қадам: ABC есебі
- Жылдық жиын:
=SUM(B2:M2). - Үлес:
=N2/SUM($N$2:$N$300). - Тауарларды үлестің кемуі бойынша сұрыптаңыз.
- Жинақталған үлес: бірінші жолда
=O2, келесісінде=P2+O3. - Топ:
=IF(P2<=0,8;"A";IF(P2<=0,95;"B";"C"))— жинақталған үлес 80%-ға дейін болса A, 95%-ға дейін B, қалғаны C.
Мысал: шағын азық-түлік дүкенінің 7 позициясы
| Тауар | Жылдық түсім, ₸ | Үлес | Жинақталған | Топ |
|---|---|---|---|---|
| Кофе 250 г | 5 000 000 | 41,7% | 41,7% | A |
| Шай 100 пак. | 3 200 000 | 26,7% | 68,3% | A |
| Қант 1 кг | 1 300 000 | 10,8% | 79,2% | A |
| Печенье | 1 000 000 | 8,3% | 87,5% | B |
| Бал 500 г | 700 000 | 5,8% | 93,3% | B |
| Шоколад | 500 000 | 4,2% | 97,5% | C |
| Джем | 300 000 | 2,5% | 100% | C |
3-қадам: XYZ есебі
Вариация коэффициенті = айлық сатылымның стандартты ауытқуы / орташа мәні:
=STDEVP(B2:M2)/AVERAGE(B2:M2)
(Excel 2010 және жаңарақ нұсқаларында STDEV.P те қолдануға болады.) Топ: =IF(Q2<=0,1;"X";IF(Q2<=0,25;"Y";"Z")).
Мысалы, айлық сатылымы 100, 110, 95, 105, 98, 102 дана болатын тауардың CV-і 4,8% — X тобы. 80, 95, 70, 100, 85, 90 — 11,4%, Y тобы. Ал 0, 30, 5, 60, 10, 0 — 123%, айқын Z.
4-қадам: 3×3 матрица және шешімдер
ABC мен XYZ топтарын біріктіріп (=R2&S2), тоғыз үйлесім аласыз:
| X | Y | Z | |
|---|---|---|---|
| A | Әрқашан қорда, тапшылыққа жол бермеу | Сақтандыру қорын көбейту | Сұранысты жиі қадағалау, тапсырыс бойынша |
| B | Тұрақты автотапсырыс | Орташа қор | Қорды азайту |
| C | Ең аз қор | Тапсырыс бойынша | Ассортиментті қайта қарау |
AX — ассортименттің «алтын қоры». CZ — алғашқы үміткерлер: оларды алып тастау немесе тек тапсырыспен әкелу.
Жиі кездесетін қателер
- Тым қысқа кезең. 2–3 айлық деректер маусымдылықты ескермейді. Кемінде 6, жақсысы 12 ай.
- Маржаны ескермеу. Түсім бойынша C тобындағы тауар жоғары маржалы болуы мүмкін. Қажет болса, талдауды пайда бойынша да жасаңыз.
- Ілеспе тауарларды алып тастау. Кейбір C тауарлары A тауарын сату үшін қажет (кофеге — қант). Шешімді механикалық қабылдамаңыз.
- Тапшылық айларын ескермеу. Тауар қоймада болмаған айда сатылым нөл — бұл сұраныстың жоқтығы емес, ал CV жасанды түрде өседі.
Дайын Excel үлгісі
Ақылы «ABC-XYZ ассортимент талдауы» үлгісінде барлық қадам автоматтандырылған — сұрыптау мен формулаларды қолмен баптаудың қажеті жоқ:
- «Параметрлер» — A, B және X, Y топтарының шекаралары (әдепкі бойынша 80% және 95%, 10% және 25%), оларды өзгертуге болады.
- «Деректер» — 300 тауарға дейін және 12 айлық сатылым; оң жақта жиыны, үлесі, жинақталған үлесі, ABC және XYZ топтары өздігінен есептеледі.
- «Рейтинг» — түсімнің кемуі бойынша топтары көрсетілген тауарлар.
- «Матрица» — тоғыз топтың әрқайсысында қанша тауар мен түсім бар, әр үйлесім үшін қысқа ұсыныс және диаграмма.
Excel 2007 және жаңарақ, LibreOffice, макростарсыз — барлық есептеулер қарапайым формулалармен. Файл қазақ тілінде.
Жиі қойылатын сұрақтар
Талдауды қаншалықты жиі жасау керек?
Тоқсанына бір рет — көп бизнес үшін оңтайлы. Маусымдық ассортиментте маусым басталар алдында және аяқталған соң қайталаңыз.
Данамен бе, ақшамен бе есептеген дұрыс?
ABC үшін әдетте түсім (ақша) алынады, себебі ол бизнеске маңыздылықты көрсетеді. XYZ үшін данамен есептеу дәлірек: бағаның өзгеруі сұраныстың «тұрақсыздығы» болып көрінбейді.
Жаңа тауарларды қалай бағалаймын?
Оларды талдаудан уақытша шығарыңыз немесе бөлек белгілеңіз. 3–4 айлық тарихы жиналған соң ғана топқа қосу мағыналы.
Маркетплейс сатушысына ABC-XYZ қажет пе?
Иә, тіпті көбірек: маркетплейс қоймасында сақтау ақылы, ал AX тауарларының таусылуы карточканың позициясын төмендетеді. Матрица қай тауарды көбірек жеткізу керектігін көрсетеді.
