Cách thực hiện phân tích ABC trong Excel: 5 bước đơn giản

Phân tích ABC phân loại các mặt hàng tồn kho thành ba nhóm dựa trên giá trị sử dụng hàng năm của chúng. Các mặt hàng Nhóm A là số ít chiếm phần lớn giá trị tiền tệ. Các mặt hàng Nhóm C là số nhiều nhưng chiếm giá trị rất nhỏ, và nhóm B nằm ở giữa. Trong Excel, bạn có thể thực hiện việc này chỉ với một bảng: giá trị hàng năm, tỷ trọng trong tổng số, tổng lũy kế và một công thức để phân loại từng nhóm.

Hướng dẫn này giải thích ý nghĩa của các nhóm, năm bước thực hiện trong Excel, một ví dụ thực tế và cách vẽ biểu đồ kết quả. Hướng dẫn cũng đề cập đến cách chọn các điểm giới hạn và những việc cần làm với từng nhóm sau khi hoàn thành phân tích.

Phân tích ABC là gì

Phân tích ABC là một phương pháp để quyết định xem những mặt hàng nào xứng đáng được chú ý nhiều nhất. Phương pháp này dựa trên một quy luật đơn giản: một tỷ lệ nhỏ các mặt hàng lại chiếm phần lớn chi phí mua sắm.

Một chương viết năm 2012 về phân tích và kiểm soát chi phí dược phẩm từ tổ chức Management Sciences for Health (MSH) đã mô tả điều này một cách rõ ràng. Chương này lưu ý rằng "một số lượng tương đối nhỏ các mặt hàng lại chiếm phần lớn giá trị tiêu thụ hàng năm." Đồng thời cho biết thêm: "Việc phân tích hiện tượng này được gọi là phân tích Pareto hoặc phổ biến hơn là phân tích ABC."

Chương này cũng giải thích rằng các mặt hàng "có thể được phân thành ba nhóm (A, B và C) dựa trên giá trị sử dụng hàng năm của chúng." Phương pháp này hoàn toàn giống nhau cho dù bạn lưu kho thuốc men, phụ tùng thay thế hay các sản phẩm bán lẻ.

Có một điểm rất dễ bị bỏ qua. Các nhóm phân loại không phải là những nhãn dán vĩnh viễn. MSH lưu ý rằng "Nếu mô hình sử dụng thay đổi, mặt hàng đó có thể rơi vào một nhóm khác trong lần phân tích ABC tiếp theo." Vì vậy, phân tích ABC hoạt động hiệu quả nhất khi được thực hiện như một hoạt động kiểm tra định kỳ, chứ không phải là một dự án chỉ làm một lần.

Ý nghĩa của các nhóm A, B và C

Chương tài liệu của MSH đưa ra các khoảng giá trị điển hình cho từng nhóm:

Nhóm	Tỷ lệ mặt hàng	Tỷ lệ giá trị hàng năm	Ý nghĩa thông thường
A	10 đến 20 phần trăm	75 đến 80 phần trăm	Ít mặt hàng, chiếm hầu hết số tiền
B	10 đến 20 phần trăm	15 đến 20 phần trăm	Nhóm trung gian
C	60 đến 80 phần trăm	5 đến 10 phần trăm	Nhiều mặt hàng, chiếm rất ít tiền

Đây là các khoảng giá trị điển hình, không phải là quy tắc bắt buộc. MSH cho biết "Các ranh giới này có phần linh hoạt." Ví dụ của họ thiết lập nhóm A gồm các mặt hàng cộng dồn lại chiếm 70 phần trăm ngân sách.

Giá trị quyết định việc phân nhóm là giá trị tiêu thụ hàng năm: số lượng đơn vị sử dụng trong năm nhân với đơn giá. Một mặt hàng giá rẻ nhưng được sử dụng với số lượng khổng lồ có thể rơi vào nhóm A. Ngược lại, một mặt hàng đắt tiền nhưng chỉ dùng một lần mỗi năm có thể rơi vào nhóm C.

Một bài báo năm 2014 trên tạp chí American Journal of Business Education đã đặt câu hỏi về việc chỉ sử dụng giá trị đơn thuần. Bài báo lập luận rằng các sách giáo khoa "chỉ tập trung vào giá trị tiền tệ như một tiêu chí duy nhất" và khuyến nghị nên bổ sung thêm các tiêu chí khác. Đối với bước sàng lọc đầu tiên, giá trị là phương pháp mà chương tài liệu của MSH sử dụng.

Những gì bạn cần chuẩn bị trước khi bắt đầu

Phân tích ABC trong Excel chỉ cần một vài cột cho mỗi mặt hàng:

• Tên mặt hàng hoặc SKU. Mỗi mặt hàng một dòng.
• Số lượng đơn vị sử dụng hoặc mua hàng năm. Sử dụng cùng một khoảng thời gian 12 tháng cho mọi mặt hàng.
• Đơn giá. Chi phí của một đơn vị, tính theo cùng một đơn vị đo lường mà bạn đếm.

MSH nhấn mạnh sự đồng nhất về khoảng thời gian: "Hãy đảm bảo rằng cùng một khoảng thời gian xem xét được áp dụng cho tất cả các mặt hàng để tránh những so sánh sai lệch." Tổ chức này cũng khuyên nên sử dụng cùng một đơn vị cơ bản cho cả chi phí và số lượng, chẳng hạn như một viên thuốc hoặc một hộp đơn lẻ, thay vì trộn lẫn các kích cỡ đóng gói khác nhau.

Nếu dữ liệu của bạn đến từ hệ thống tồn kho hoặc mua hàng, hãy xuất dữ liệu đó dưới dạng tệp CSV hoặc Excel. Hãy loại bỏ các mặt hàng không có hoạt động nào trong kỳ, hoặc giữ lại chúng và chuẩn bị tinh thần rằng chúng sẽ rơi vào nhóm C.

Cách thực hiện phân tích ABC trong Excel

Năm bước dưới đây tuân theo phương pháp trong chương tài liệu của MSH, được điều chỉnh cho phù hợp với các công thức Excel. Ví dụ này đặt tiêu đề ở dòng 1, các tiêu đề cột ở dòng 2, và 10 mặt hàng từ dòng 3 đến dòng 12. Các cột A, B và C chứa tên mặt hàng, số lượng hàng năm và đơn giá.

Bước 1: Liệt kê các mặt hàng, số lượng và đơn giá

Nhập hoặc dán mỗi dòng một mặt hàng cùng với tên, số lượng hàng năm và đơn giá của nó. Thêm các tiêu đề cột ở dòng 2 để bảng dễ dàng sắp xếp sau này.

Kiểm tra dữ liệu trước khi tiếp tục. Hãy tìm các ô đơn giá bị bỏ trống, số lượng âm và các SKU bị trùng lặp, vì mỗi lỗi này đều sẽ làm sai lệch tổng số. Việc lọc nhanh trên từng cột thường sẽ giúp phát hiện ra chúng.

Nếu một mặt hàng được mua nhiều lần với các mức giá khác nhau, hãy sử dụng một mức chi phí nhất quán duy nhất. MSH lưu ý rằng "giá trung bình gia quyền hoặc giá trung bình FIFO" là những giải pháp thay thế chính xác nhất khi khó theo dõi đơn giá thực tế.

Bước 2: Tính toán giá trị hàng năm và tỷ trọng của nó trong tổng số

Ở cột D, nhân số lượng với đơn giá để có được giá trị hàng năm của từng mặt hàng. Tại ô D3, nhập =B3*C3 và kéo công thức xuống dưới.

Ở cột E, chia từng giá trị cho tổng của tất cả các giá trị để có được tỷ trọng của nó. Tại ô E3, nhập =D3/SUM($D$3:$D$12) và kéo xuống dưới. Các ký hiệu đô la giữ cho vùng tổng được cố định khi sao chép công thức. Định dạng cột E dưới dạng phần trăm với hai chữ số thập phân.

MSH khuyến nghị độ chính xác đó là có lý do. Theo lời của họ, "một số mặt hàng có thể có giá trị rất gần nhau và nhiều mặt hàng có thể chiếm chưa đầy 1 phần trăm tổng giá trị."

Bước 3: Sắp xếp các mặt hàng theo giá trị, từ lớn nhất đến nhỏ nhất

Chọn toàn bộ bảng, bao gồm cả các tiêu đề cột, và sắp xếp theo cột D từ lớn nhất đến nhỏ nhất. Trong Excel, thao tác đó là Data, sau đó chọn Sort, với cột D và thứ tự được thiết lập từ Largest to Smallest.

Nếu bạn thích sử dụng công thức hơn, hàm SORT sẽ trả về một bản sao đã được sắp xếp. Cú pháp của Microsoft là =SORT(array,[sort_index],[sort_order],[by_col]), trong đó thứ tự sắp xếp là -1 nghĩa là giảm dần. Đối với bảng này, công thức =SORT(A3:E12,4,-1) sẽ sắp xếp theo cột thứ tư, giá trị cao nhất lên đầu.

Sau bước này, mặt hàng có giá trị hàng năm cao nhất sẽ nằm ở trên cùng. Thứ tự đó chính là điều làm cho tổng lũy kế ở bước tiếp theo trở nên có ý nghĩa.

Bước 4: Thêm tỷ lệ phần trăm tích lũy

Ở cột F, thêm tổng lũy kế của các tỷ trọng. Tại ô F3, nhập =SUM($E$3:E3) và kéo xuống dưới. Phần đầu tiên của vùng dữ liệu được giữ cố định, và phần thứ hai sẽ tăng thêm một dòng sau mỗi lần kéo.

Dòng cuối cùng phải hiển thị 100 phần trăm. Nếu không, hãy kiểm tra xem có các ô trống hoặc giá trị dạng văn bản trong cột D và E hay không.

Cột này là cốt lõi của phân tích ABC. Nó cho thấy các mặt hàng phía trên mỗi dòng cùng nhau chiếm bao nhiêu phần trăm trong tổng giá trị.

Bước 5: Phân loại các nhóm A, B và C

Ở cột G, sử dụng một công thức để gắn nhãn cho từng mặt hàng. Với các điểm giới hạn là 80 và 95 phần trăm, hãy nhập công thức này vào ô G3 và kéo xuống dưới:

=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")

Hàm IFS kiểm tra từng điều kiện theo thứ tự và trả về kết quả khớp đầu tiên. Ví dụ của chính Microsoft cũng sử dụng cùng một mô hình này, với TRUE là điều kiện bao quát cuối cùng. Các mặt hàng có tỷ lệ tích lũy lên đến 80 phần trăm sẽ trở thành nhóm A, các mặt hàng lên đến 95 phần trăm sẽ trở thành nhóm B, và phần còn lại sẽ là nhóm C.

Cuối cùng, hãy đếm số lượng của từng nhóm bằng công thức =COUNTIF(G3:G12,"A") và làm tương tự cho nhóm B và C. So sánh số lượng đếm được với các khoảng điển hình ở trên. Hãy điều chỉnh các điểm giới hạn nếu nhóm A quá lớn hoặc quá nhỏ để đội ngũ của bạn có thể quản lý.

Một ví dụ thực tế

Dưới đây là bảng minh họa cho 10 mặt hàng, đã được sắp xếp theo giá trị hàng năm. Các con số này chỉ là ví dụ, không phải dữ liệu từ một công ty thực tế.

Mặt hàng	Số lượng hàng năm	Đơn giá	Giá trị hàng năm	Tỷ trọng	Tích lũy	Nhóm
SKU-01	1,200	$45.00	$54,000	36.00%	36.00%	A
SKU-02	3,000	$12.00	$36,000	24.00%	60.00%	A
SKU-03	500	$40.00	$20,000	13.33%	73.33%	A
SKU-04	8,000	$1.50	$12,000	8.00%	81.33%	B
SKU-05	2,000	$4.00	$8,000	5.33%	86.67%	B
SKU-06	600	$10.00	$6,000	4.00%	90.67%	B
SKU-07	1,000	$5.00	$5,000	3.33%	94.00%	B
SKU-08	1,500	$3.00	$4,500	3.00%	97.00%	C
SKU-09	700	$5.00	$3,500	2.33%	99.33%	C
SKU-10	400	$2.50	$1,000	0.67%	100.00%	C

Tổng giá trị hàng năm là $150,000. Ba mặt hàng, chiếm 30 phần trăm danh sách, tạo nên 73.33 phần trăm giá trị và được xếp vào nhóm A. Bốn mặt hàng rơi vào nhóm B, và ba mặt hàng cuối cùng, trị giá 6 phần trăm giá trị, rơi vào nhóm C.

Có hai chi tiết nổi bật. SKU-04 có số lượng lớn nhất cho đến nay, nhưng đơn giá thấp đã đưa nó vào nhóm B. Và với chỉ 10 mặt hàng, tỷ lệ của các nhóm sẽ không khớp hoàn toàn với các khoảng điển hình, điều này là bình thường đối với một danh sách ngắn.

Cách vẽ biểu đồ kết quả

Một biểu đồ sẽ giúp bạn dễ dàng trình bày quy luật này trong một cuộc họp. MSH gợi ý vẽ biểu đồ tỷ lệ phần trăm tích lũy tương ứng với số lượng mặt hàng, điều này sẽ tạo ra đường cong ABC quen thuộc.

Excel có sẵn một loại biểu đồ tích hợp cho việc này. Microsoft mô tả biểu đồ Pareto là biểu đồ "chứa cả các cột được sắp xếp theo thứ tự giảm dần và một đường biểu diễn tỷ lệ phần trăm tổng tích lũy." Để tạo biểu đồ này, hãy chọn tên các mặt hàng và giá trị hàng năm, sau đó chọn Insert, Insert Statistic Chart, và chọn Pareto.

Thêm hai đường nằm ngang hoặc nhãn tại các điểm giới hạn của bạn, chẳng hạn như 80 và 95 phần trăm, để người xem có thể thấy rõ từng nhóm bắt đầu từ đâu. Hướng dẫn của chúng tôi về cách tạo biểu đồ Pareto bằng AI sẽ đề cập sâu hơn về bản thân biểu đồ này.

Lựa chọn các điểm giới hạn của bạn

Không có một điểm giới hạn chính xác duy nhất nào. MSH giải thích rằng sự lựa chọn này "phụ thuộc vào cách phân bổ số lượng và giá trị giữa các mặt hàng trong danh sách." Nó cũng phụ thuộc vào "cách thức kết quả phân tích ABC sẽ được sử dụng."

Năng lực quản lý chính là giới hạn thực tế. MSH đã nêu trực tiếp: "việc phân bổ các mặt hàng vào nhóm A phải dựa trên năng lực quản lý." Nếu đội ngũ của bạn chỉ có thể xem xét kỹ lưỡng 50 mặt hàng mỗi tháng, thì một nhóm A gồm 300 mặt hàng sẽ làm mất đi mục đích ban đầu.

Một vài cách tiếp cận phổ biến:

• Điểm giới hạn theo giá trị. Nhóm A lên đến 80 phần trăm giá trị, nhóm B lên đến 95 phần trăm, nhóm C cho phần còn lại. Đây là phương pháp được sử dụng ở trên.
• Điểm giới hạn theo số lượng mặt hàng. 20 phần trăm số mặt hàng hàng đầu theo giá trị sẽ trở thành nhóm A, 30 phần trăm tiếp theo là nhóm B, và phần còn lại là nhóm C.
• Danh sách cố định. Một số đội ngũ thiết lập nhóm A là 25 hoặc 50 mặt hàng hàng đầu, bất kể tỷ lệ giá trị của chúng là bao nhiêu.

Cho dù bạn chọn cách nào, hãy ghi lại và áp dụng thống nhất cho mỗi lần thực hiện. Việc so sánh các nhóm của quý này với quý trước chỉ có ý nghĩa nếu các điểm giới hạn được giữ nguyên.

Những việc cần làm với từng nhóm

Mục đích của phân tích ABC là tập trung nỗ lực vào nơi tập trung nhiều tiền bạc nhất. Chương tài liệu của MSH liệt kê một số cách để sử dụng kết quả này:

• Đặt hàng các mặt hàng nhóm A thường xuyên hơn. MSH cho biết việc đặt hàng các mặt hàng nhóm A "thường xuyên hơn và với số lượng nhỏ hơn sẽ giúp giảm chi phí lưu kho."
• Thương lượng giá các mặt hàng nhóm A trước tiên. "Việc giảm giá cho các mặt hàng được phân loại là sản phẩm nhóm A trong phân tích có thể mang lại khoản tiết kiệm đáng kể," theo chương tài liệu này.
• Kiểm kê kho các mặt hàng nhóm A thường xuyên hơn. MSH lưu ý rằng "việc kiểm kê kho định kỳ nên được định hướng bởi phân tích ABC, với tần suất kiểm kê thường xuyên hơn đối với các mặt hàng nhóm A."
• Theo dõi sát sao trạng thái đơn hàng nhóm A. Việc thiếu hụt bất ngờ một mặt hàng nhóm A có thể dẫn đến các giao dịch mua hàng khẩn cấp vô cùng tốn kém.

Các mặt hàng nhóm C có thể áp dụng các quy tắc đơn giản hơn, chẳng hạn như đặt hàng với số lượng lớn hơn nhưng ít thường xuyên hơn và giảm số lần kiểm kê. Nhóm B nằm ở giữa. Nếu hàng tồn kho chậm luân chuyển là một mối lo ngại, hướng dẫn của chúng tôi về cách phát hiện hàng tồn kho chậm luân chuyển sẽ là sự kết hợp hoàn hảo với phân tích này.

Thực hiện nhanh hơn với AI

Các bước thực hiện trong Excel chỉ mất vài phút sau khi dữ liệu đã được làm sạch. Việc làm sạch dữ liệu xuất ra và lặp lại công việc này mỗi quý mới là điều tốn nhiều thời gian hơn.

Một không gian làm việc AI có thể thực hiện các phép tính toán và sắp xếp chỉ trong một yêu cầu. Hãy tải tệp xuất dữ liệu tồn kho hoặc mua hàng lên Powerdrill Bloom và yêu cầu thực hiện phân tích ABC bằng ngôn ngữ tự nhiên với các điểm giới hạn của bạn. Hãy yêu cầu tính giá trị hàng năm, tỷ trọng, tỷ lệ phần trăm tích lũy và phân nhóm cho từng mặt hàng, cùng với một biểu đồ Pareto.

Sau đó, hãy kiểm tra lại như bất kỳ bảng tính nào khác. Hãy đối chiếu tổng giá trị hàng năm với tổng số do chính bạn tính toán, và kiểm tra ngẫu nhiên hai mặt hàng trong mỗi nhóm. Trang Excel AI assistant của chúng tôi đề cập chi tiết hơn về loại công việc xử lý bảng tính này. Để có cái nhìn rộng hơn về các công cụ dự báo, hãy xem bài tổng hợp này về các công cụ AI cho việc dự báo nhu cầu và tồn kho.

Các lỗi phổ biến cần tránh

• Trộn lẫn các khoảng thời gian. Việc tính mười hai tháng cho một mặt hàng và sáu tháng cho mặt hàng khác sẽ làm cho các tỷ trọng trở nên vô nghĩa.
• Sử dụng số lượng đơn vị thay vì giá trị. Việc phân nhóm phụ thuộc vào số lượng đơn vị nhân với đơn giá, chứ không chỉ dựa vào số lượng đơn vị đơn thuần.
• Quên sắp xếp trước khi tính tổng lũy kế. Tỷ lệ phần trăm tích lũy trên một danh sách chưa được sắp xếp sẽ đưa các mặt hàng vào sai nhóm.
• Coi các nhóm phân loại là vĩnh viễn. Hãy chạy lại phân tích mỗi quý hoặc mỗi năm, vì các mặt hàng sẽ dịch chuyển giữa các nhóm.
• Các điểm giới hạn bỏ qua năng lực quản lý. Một danh sách nhóm A quá dài để có thể quản lý chặt chẽ sẽ không nhận được sự chú ý sát sao hơn so với nhóm B.
• Bỏ qua các mặt hàng giá rẻ nhưng quan trọng. Một mặt hàng có giá trị thấp vẫn có thể làm đình trệ công việc nếu bị hết hàng. Chương tài liệu của MSH kết hợp phân tích ABC với một đánh giá riêng biệt về các mặt hàng tối quan trọng, thiết yếu và không thiết yếu.

Khi danh sách mặt hàng của bạn đến từ một tệp xuất dữ liệu lộn xộn, bạn có thể thử Powerdrill Bloom để xây dựng bảng và biểu đồ ABC đầu tiên.

Các câu hỏi thường gặp

Phân tích ABC trong quản lý tồn kho là gì?

Phân tích ABC phân loại các mặt hàng thành ba nhóm dựa trên giá trị tiêu thụ hàng năm. Các mặt hàng nhóm A là số ít chiếm phần lớn giá trị. Các mặt hàng nhóm C là số nhiều nhưng chiếm giá trị rất nhỏ, và nhóm B nằm ở giữa. Phương pháp này giúp các đội ngũ tập trung nỗ lực kiểm soát vào nơi tập trung nhiều tiền bạc nhất.

Làm thế nào để tính toán phân tích ABC trong Excel?

Nhân số lượng hàng năm với đơn giá cho từng mặt hàng, sau đó chia cho tổng số để có được tỷ trọng của từng mặt hàng. Sắp xếp theo giá trị từ lớn nhất đến nhỏ nhất, thêm tổng lũy kế của các tỷ trọng và phân nhóm bằng một công thức như IFS. Các điểm giới hạn 80 và 95 phần trăm nằm trong các khoảng điển hình trong chương tài liệu của MSH.

Các tỷ lệ phần trăm cho phân tích ABC là gì?

Một hướng dẫn phổ biến là nhóm A chiếm từ 10 đến 20 phần trăm số mặt hàng và từ 75 đến 80 phần trăm giá trị. Nhóm B chiếm từ 10 đến 20 phần trăm số mặt hàng khác và từ 15 đến 20 phần trăm giá trị. Nhóm C chiếm từ 60 đến 80 phần trăm số mặt hàng và từ 5 đến 10 phần trăm giá trị.

Công thức phân loại ABC trong Excel là gì?

Với tỷ lệ phần trăm tích lũy ở cột F và dữ liệu bắt đầu từ dòng 3, hãy sử dụng công thức =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Thay đổi 0.8 và 0.95 để phù hợp với các điểm giới hạn của riêng bạn. Các công thức IF lồng nhau cũng có thể thực hiện công việc tương tự.

Tại sao phân tích ABC lại quan trọng?

Phân tích này chỉ ra nơi phần lớn ngân sách tồn kho được chi tiêu, nhờ đó các đội ngũ có thể quản lý các mặt hàng đó chặt chẽ hơn. Các ứng dụng điển hình bao gồm đặt hàng các mặt hàng nhóm A thường xuyên hơn, thương lượng giá của chúng trước tiên và kiểm kê chúng với tần suất cao hơn. Nó cũng cảnh báo những khoản chi tiêu không khớp với kế hoạch.

Sources: Management Sciences for Health, MDS-3 Chương 40: Phân tích và kiểm soát chi phí dược phẩm · Ravinder và Misra, Phân tích ABC trong Quản lý Tồn kho (2014) · Microsoft Support, hàm SORT · Microsoft Support, hàm IFS · Microsoft Support, Tạo biểu đồ Pareto.