Bảng Theo Dõi Công Nợ Phải Thu Bằng Excel Và AI: Phân Tuổi Nợ 30/60/90

Nhiều chủ doanh nghiệp nhỏ hay hỏi tôi một câu: "Sổ sách báo lãi, sao tài khoản ngân hàng lúc nào cũng mỏng?" Câu trả lời thường nằm ở một chỗ: tiền nằm trong tay khách hàng chứ chưa về túi mình.

Ví dụ minh họa: Công ty X bán hàng cho các đại lý, cuối tháng ghi nhận công nợ phải thu 500 triệu đồng. Nhìn con số này, ai cũng thấy yên tâm. Nhưng khi tách ra, có 200 triệu đồng đã quá hạn trên 60 ngày. Tức là 40% công nợ đang "đóng băng". Lợi nhuận trên sổ vẫn đẹp, còn tiền trả lương, trả nhà cung cấp thì phải xoay từ nơi khác.

Muốn nhìn ra vấn đề này sớm, bạn cần một bảng theo dõi công nợ phải thu có phân tuổi nợ. Bài này hướng dẫn bạn làm từng bước bằng Excel, rồi dùng AI để tiết kiệm thời gian xử lý dữ liệu và soạn thư nhắc nợ.

Phân tuổi công nợ là gì và vì sao chia mốc 30/60/90 ngày?

Phân tuổi công nợ (aging) là cách xếp các khoản phải thu theo thời gian đã quá hạn thanh toán. Khoản nợ càng "già" thì khả năng thu hồi càng thấp và càng cần được quan tâm.

Cách chia phổ biến gồm 5 nhóm:

Mốc 30/60/90 là quy ước thực hành quen thuộc vì khớp với nhịp đối chiếu công nợ theo tháng của đa số doanh nghiệp. Bạn hoàn toàn có thể điều chỉnh mốc cho phù hợp ngành. Ví dụ ngành có chu kỳ thanh toán dài thì dùng 45/90/120.

Cấu trúc bảng theo dõi trong Excel

Bạn tạo một sheet tên CongNo với mỗi dòng là một hóa đơn (không phải một khách hàng). Các cột cần có:

Mẹo nhỏ: chuyển vùng dữ liệu thành Table (Ctrl + T) để công thức tự động áp dụng cho dòng mới.

Công thức Excel cốt lõi

Số ngày quá hạn

Tại ô G2 (với E2 là hạn thanh toán):

=MAX(0;TODAY()-E2)

Lưu ý: bản Excel tiếng Việt thường đổi tên hàm theo ngôn ngữ, và dấu phân cách có thể là ; hoặc , tùy cài đặt vùng của máy. Nếu công thức báo lỗi, hãy thử đổi dấu phân cách hoặc dùng tên hàm tiếng Anh TODAY().

Nhóm tuổi nợ

Tại ô H2, dùng IFS (Excel 2019 trở lên):

=IFS(G2=0;"Chưa quá hạn";G2<=30;"1-30 ngày";G2<=60;"31-60 ngày";G2<=90;"61-90 ngày";TRUE;"Trên 90 ngày")

Nếu dùng phiên bản Excel cũ, thay bằng IF lồng nhau:

=IF(G2=0;"Chưa quá hạn";IF(G2<=30;"1-30 ngày";IF(G2<=60;"31-60 ngày";IF(G2<=90;"61-90 ngày";"Trên 90 ngày"))))

Tổng hợp theo nhóm bằng SUMIFS

Tạo một sheet TongHop với danh sách nhóm ở cột A, rồi tại ô B2:

=SUMIFS(CongNo!$F:$F;CongNo!$H:$H;A2)

Tỷ lệ % mỗi nhóm trên tổng công nợ tại ô C2:

=B2/SUM($B$2:$B$6)

Ví dụ số liệu minh họa

Quay lại Công ty X (số liệu minh họa, làm tròn để dễ tính):

Nhìn vào bảng này, bạn thấy ngay 40% công nợ đã quá hạn trên 60 ngày. Đây là tín hiệu cần hành động, chứ không chỉ là con số để báo cáo.

Dùng PivotTable và biểu đồ cột chồng để theo dõi xu hướng

Bảng SUMIFS cho bạn bức ảnh tại một thời điểm. Để thấy xu hướng, bạn cần lưu lại số liệu theo tuần.

Cách làm đơn giản:

1. Mỗi thứ Hai, copy bảng tổng hợp và dán giá trị (Paste Values) sang một sheet LichSu, kèm cột ngày chốt số.
2. Chọn vùng dữ liệu LichSu → Insert → PivotTable.
3. Kéo "Ngày chốt" vào Rows, "Nhóm tuổi nợ" vào Columns, "Số tiền" vào Values.
4. Chọn PivotTable → Insert → Biểu đồ cột chồng (Stacked Column).

Biểu đồ cho bạn thấy phần màu "trên 90 ngày" đang phình ra hay thu nhỏ qua từng tuần. Nếu phần màu đỏ cứ dày lên, quy trình thu hồi nợ đang có vấn đề.

Bạn cũng có thể dùng PivotTable theo người phụ trách để biết ai đang giữ nhiều nợ quá hạn nhất.

Ứng dụng AI: làm sạch dữ liệu, phân loại rủi ro, soạn email nhắc nợ

AI không thay Excel, nhưng giúp bạn xử lý những việc tốn thời gian: dọn dữ liệu, gợi ý phân loại, soạn thảo văn bản.

Lưu ý bảo mật: trước khi đưa dữ liệu vào bất kỳ công cụ AI nào, hãy che hoặc thay tên khách hàng, mã số thuế, số tài khoản bằng mã ẩn danh (ví dụ KH001, KH002). Kiểm tra chính sách bảo mật của công cụ bạn dùng.

Prompt 1: Làm sạch dữ liệu xuất từ phần mềm kế toán

Bạn là trợ lý hỗ trợ kế toán. Dưới đây là dữ liệu công nợ xuất từ phần mềm, 
có thể lẫn định dạng ngày không thống nhất, khoảng trắng thừa, mã khách hàng viết hoa/thường lẫn lộn.

Hãy:
1. Chuẩn hóa ngày về định dạng dd/mm/yyyy.
2. Xóa khoảng trắng thừa, viết hoa toàn bộ mã khách hàng.
3. Liệt kê riêng các dòng nghi ngờ lỗi (thiếu hạn thanh toán, số tiền âm, trùng số hóa đơn) 
   để tôi tự kiểm tra, KHÔNG tự sửa các dòng này.
4. Trả kết quả dạng bảng.

Dữ liệu:
[dán dữ liệu đã ẩn danh]

Prompt 2: Gợi ý phân loại khách hàng theo mức rủi ro

Dưới đây là tổng hợp công nợ theo khách hàng (đã ẩn danh): mã KH, tổng nợ, 
số hóa đơn quá hạn, số ngày quá hạn lớn nhất, lịch sử thanh toán trễ.

Hãy gợi ý phân loại mỗi khách hàng vào 3 mức: Thấp / Trung bình / Cao, 
kèm 1 câu giải thích lý do. 
Đây chỉ là gợi ý tham khảo để kế toán xem xét, không phải quyết định cuối cùng.

Dữ liệu:
[dán bảng]

Prompt 3: Soạn email nhắc nợ theo nhóm tuổi

Soạn email nhắc thanh toán công nợ bằng tiếng Việt, gửi từ phòng kế toán Công ty X.
Thông tin: tên khách hàng [..], số hóa đơn [..], số tiền [..], số ngày quá hạn [..].

Giọng điệu theo nhóm tuổi nợ:
- 1–30 ngày: lịch sự, nhẹ nhàng, giả định khách có thể quên.
- 31–60 ngày: nhắc rõ ràng, đề nghị phản hồi lịch thanh toán cụ thể.
- 61–90 ngày: nghiêm túc, đề nghị làm việc trực tiếp, nhắc khả năng xem xét lại điều kiện bán chịu.
- Trên 90 ngày: cứng rắn, nêu rõ hậu quả (tạm ngừng giao hàng, chuyển bộ phận pháp chế), 
  nhưng vẫn giữ thái độ chuyên nghiệp, không đe dọa.

Độ dài tối đa 150 từ, có tiêu đề email.

Lưu ý khi dùng AI

• Luôn đọc lại email trước khi gửi, đặc biệt các thông tin số liệu và cam kết pháp lý.
• Gợi ý phân loại rủi ro của AI chỉ là tham khảo. Người quyết định có tiếp tục bán chịu hay không vẫn là bạn, dựa trên hiểu biết về khách hàng.
• Với nợ trên 90 ngày, nếu liên quan đến biện pháp pháp lý hoặc trích lập dự phòng, hãy tham khảo quy định hiện hành và ý kiến chuyên gia (cần kiểm tra hiệu lực văn bản tại thời điểm áp dụng).

Cách đọc kết quả: tỷ lệ nợ >90 ngày, DSO và hành động ưu tiên

Tỷ lệ nợ trên 90 ngày

=B6/SUM(B2:B6)

Với Công ty X, tỷ lệ này là 20%. Không có một ngưỡng "chuẩn" áp dụng cho mọi ngành, nhưng bạn nên đặt mục tiêu nội bộ và theo dõi xu hướng giảm dần qua từng tháng.

DSO (số ngày phải thu bình quân)

DSO cho biết trung bình bao nhiêu ngày thì doanh nghiệp thu được tiền sau khi bán hàng. Công thức đơn giản:

DSO = (Công nợ phải thu bình quân / Doanh thu bán chịu trong kỳ) x Số ngày trong kỳ

Ví dụ minh họa: công nợ phải thu 500 triệu, doanh thu bán chịu trong 90 ngày gần nhất là 1.500 triệu:

DSO = (500 / 1.500) x 90 = 30 ngày

DSO càng thấp, tiền về càng nhanh. Nếu DSO của bạn cao hơn nhiều so với thời hạn thanh toán bạn cho khách, nghĩa là khách đang trả chậm hơn thỏa thuận, và dòng tiền của bạn đang bị khách "vay" miễn phí.

Hành động ưu tiên theo từng nhóm

Lỗi thường gặp khi lập bảng theo dõi

1. Không cập nhật ngày thanh toán. Khách đã trả nhưng bảng vẫn ghi nợ, khiến báo cáo sai và nhân viên nhắc nhầm khách, rất mất uy tín. Hãy cập nhật cột "Số tiền còn nợ" ngay khi có chứng từ thu tiền.
2. Gộp nhiều hóa đơn của một khách hàng thành một dòng. Mỗi hóa đơn có hạn thanh toán khác nhau, gộp lại thì không phân tuổi chính xác được. Luôn theo dõi theo từng hóa đơn.
3. Dùng ngày hóa đơn thay vì hạn thanh toán để tính tuổi nợ. Hóa đơn xuất ngày 1/3 với hạn 45 ngày thì đến 15/4 mới quá hạn. Tính từ ngày hóa đơn sẽ đẩy nợ vào nhóm "già" sớm hơn thực tế, gây hiểu sai tình hình.
4. Quên đối chiếu với sổ kế toán. Bảng Excel là công cụ theo dõi, cần định kỳ đối chiếu với số dư tài khoản phải thu trên sổ cái.

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

Bảng theo dõi công nợ phải thu nên cập nhật bao lâu một lần? Lý tưởng là cập nhật hằng ngày khi có phát sinh thu tiền, và chốt số lưu lịch sử mỗi tuần. Tối thiểu nên rà soát hằng tuần để kịp thời nhắc nợ.

Dùng IFS hay IF lồng nhau tốt hơn? IFS gọn và dễ đọc hơn, nhưng chỉ có từ Excel 2019 trở lên. Nếu máy bạn dùng bản cũ hoặc cần chia sẻ file cho nhiều người, IF lồng nhau an toàn hơn.

Có nên đưa dữ liệu khách hàng thật vào AI không? Nên hạn chế. Hãy ẩn danh tên, mã số thuế, số tài khoản trước khi đưa vào AI, và kiểm tra chính sách bảo mật của công cụ bạn sử dụng.

AI có thể tự quyết định ngừng bán chịu cho khách hàng không? Không nên. AI chỉ gợi ý dựa trên dữ liệu bạn cung cấp. Quyết định bán chịu hay ngừng giao hàng cần đến phán đoán của người có chuyên môn, hiểu mối quan hệ và bối cảnh kinh doanh.

Khi nào nên trích lập dự phòng nợ phải thu khó đòi? Việc này phụ thuộc quy định kế toán và thuế hiện hành, bạn cần kiểm tra văn bản còn hiệu lực và trao đổi với kế toán trưởng hoặc đơn vị tư vấn trước khi hạch toán.

Kết luận

Triển khai bảng theo dõi công nợ trong tuần đầu tiên không khó, bạn có thể đi theo 5 bước:

1. Ngày 1: Xuất dữ liệu công nợ từ phần mềm kế toán, làm sạch (có thể nhờ AI hỗ trợ, nhớ ẩn danh).
2. Ngày 2: Dựng bảng Excel với đủ 10 cột, thêm công thức số ngày quá hạn và nhóm tuổi nợ.
3. Ngày 3: Làm bảng tổng hợp bằng SUMIFS, tính tỷ lệ % từng nhóm và DSO.
4. Ngày 4: Tạo PivotTable, biểu đồ cột chồng và sheet lưu lịch sử hằng tuần.
5. Ngày 5: Soạn mẫu email nhắc nợ theo từng nhóm tuổi, phân công người phụ trách và lịch rà soát.

Điều quan trọng nhất không phải là bảng đẹp, mà là thói quen cập nhật đều đặn và hành động theo kết quả. AI giúp bạn nhanh hơn, còn phán đoán cuối cùng vẫn thuộc về bạn.

Nếu muốn tiết kiệm thời gian, bạn có thể dùng thử công cụ miễn phí và mẫu biểu có sẵn tại ecostartupai.com/tools. Để học bài bản cách ứng dụng AI vào kế toán – tài chính, hãy tìm hiểu thêm các khóa học và công cụ trên ecostartupai.com.