Phân tích tồn kho ABC kết hợp XYZ trên Excel

Phương pháp phân tíchNâng caoMiễn phí
1HÀM, CHỨC NĂNG

SUMIF % lũy kế · STDEV.P ÷ AVERAGE · IF lồng

2TƯ DUY, PHƯƠNG PHÁP

Xếp ABC theo giá trị xuất (A chiếm 80% giá trị), xếp XYZ theo hệ số biến thiên lượng xuất (X đều, Z thất thường), ghép thành 9 nhóm để chọn cách quản lý tồn.

3TÌNH HUỐNG

Kho: chọn mã cần giữ chặt, mã nên giảm tồn

Bài toán

Kho có nhiều mã, không thể quản lý mã nào cũng kỹ như nhau. Cần biết mã nào giá trị lớn, mã nào bán đều, mã nào thất thường.

Làm xong được gì: Mỗi mã có giá trị xuất, % lũy kế, nhóm ABC, hệ số biến thiên, nhóm XYZ và nhóm ghép như AX, CZ.

File thực hành

Sheet ABCXYZ: 10 mã với lượng xuất 6 tháng và đơn giá ở A5:I15.

Bộ file gồm file đề để làm theo video và file đáp án để so kết quả.

👉 Vào nhóm Zalo để tải file

Các bước làm

1Xếp ABC theo giá trị

Bước một, tổng xuất bằng SUM, giá trị bằng tổng xuất nhân đơn giá. Phần trăm lũy kế: SUMIF cộng các giá trị lớn hơn hoặc bằng mình, chia tổng. Không cần sắp xếp bảng. Đến tám mươi phần trăm là nhóm A, đến chín mươi lăm là B, còn lại là C.

=H6*I6
=SUMIF($J$6:$J$15,">="&J6)/SUM($J$6:$J$15)
=IF(K6<=0.8,"A",IF(K6<=0.95,"B","C"))

2Xếp XYZ theo độ đều

Bước hai, hệ số biến thiên bằng độ lệch chuẩn chia bình quân lượng xuất. Càng nhỏ là bán càng đều. Dưới không phẩy hai lăm là X, đến không phẩy năm là Y, còn lại là Z.

=STDEV.P(B6:G6)/AVERAGE(B6:G6)
=IF(M6<=0.25,"X",IF(M6<=0.5,"Y","Z"))

3Ghép nhóm

Bước ba, ghép hai chữ bằng dấu và. AX là giá trị lớn, bán đều: giữ tồn vừa đủ, đặt hàng định kỳ. CZ là giá trị nhỏ, thất thường: chỉ đặt khi có đơn.

=L6&N6

Lưu ý khi làm với file thật

  • % lũy kế tính bằng SUMIF các giá trị lớn hơn hoặc bằng mình chia tổng, nên không cần sắp xếp bảng.
  • Ngưỡng 80% / 95% và 0,25 / 0,5 là quy ước hay dùng, có thể chỉnh theo ngành.
Muốn học Excel bài bản cùng AI?

Khóa AI EXCEL PRO học trực tiếp qua Zoom với Thầy Đạt: làm quen công cụ AI, tự động hóa báo cáo, xử lý dữ liệu nhanh gấp nhiều lần.

Xem khóa họcNhắn Zalo Thầy 0396.523.865

Bài học khác

Dữ liệu trong bài là dữ liệu minh họa. Xem tất cả bài học.

Lên đầu trang