Theo dõi công nợ khách hàng Excel - Mẫu miễn phí

1. Bước 1 — Mở sheet DanhMucKhachHang và cập nhật mã khách hàng, tên, loại khách hàng, địa chỉ, MST, hạn thanh toán mặc định và nhân viên phụ trách.
2. Bước 2 — Sang sheet TheoDoiCongNo, chọn mã khách hàng tại cột B. Các cột C đến F và S sẽ tự lấy thông tin từ danh mục.
3. Bước 3 — Nhập số hóa đơn, ngày hóa đơn, ngày đến hạn, giá trị trước thuế và thuế suất tại các cột G đến K. Thuế suất có danh sách 0%, 5%, 8% và 10%.
4. Bước 4 — Nhập số tiền khách đã thanh toán ở cột N và ngày thu gần nhất ở cột P. Không sửa các cột công thức như A, C, D, E, F, L, M, O, Q, R và S.
5. Bước 5 — Ghi nội dung trao đổi hoặc lịch nhắc nợ tại cột T, chẳng hạn Cần nhắc nợ phần còn lại.
6. Bước 6 — Mở sheet TongQuan để xem tổng dư nợ, số hóa đơn quá hạn, tỷ lệ thanh toán và số tiền còn phải thu theo từng khách hàng.
7. Bước 7 — Trước khi gửi báo cáo, kiểm tra lại số hóa đơn, MST, ngày đến hạn và chứng từ thu tiền; sheet HuongDan giải thích các vùng nhập liệu màu vàng nhạt.

Chủ một cửa hàng bán hàng cho khách doanh nghiệp thường không thiếu hóa đơn, mà thiếu một chỗ nhìn ra hóa đơn nào đã thu và hóa đơn nào đang làm kẹt dòng tiền. Mẫu này phù hợp với kế toán của Công ty TNHH, Công ty CP, hộ kinh doanh bán buôn, nhân viên kinh doanh phụ trách thu hồi nợ và thủ quỹ cần đối chiếu tiền về.

Kế toán doanh nghiệp nhỏ

Vào cuối tuần, kế toán có thể lọc cột R để tách Đã thanh toán, Chưa đến hạn và Quá hạn. Với hóa đơn HD-2026-002 có giá trị trước thuế 185.000.000 ₫, thuế GTGT 10% là 18.500.000 ₫, tổng hóa đơn 203.500.000 ₫; khách mới trả 60.000.000 ₫ nên số còn phải thu là 143.500.000 ₫.

Chủ hộ kinh doanh và người phụ trách bán hàng

Hộ kinh doanh An Khang có hạn thanh toán mặc định 15 ngày, trong khi Công ty CP Đại Việt có 45 ngày. Hai điều khoản này không nên gộp thành một mức chung: cửa hàng cần tiền nhanh nên dùng hạn ngắn, còn khách doanh nghiệp lớn có thể cần thời hạn dài hơn nhưng phải được theo dõi riêng.

Đọc cấu trúc qua ảnh chụp

Ảnh 1 cho thấy cột B chọn mã khách hàng, G đến P chứa dữ liệu hóa đơn và thu tiền, còn O, Q, R là kết quả cần theo dõi. Ảnh 2 cho thấy DanhMucKhachHang lưu MST, hạn thanh toán và nhân viên phụ trách; đây là nơi nên cập nhật trước khi thêm khách mới. Mẫu có các dòng mẫu minh họa và các dòng còn lại được chuẩn bị để nhập thêm, không phải toàn bộ đều là dữ liệu thực tế.

Bảng công nợ phải khớp với hóa đơn điện tử, phiếu thu và sao kê ngân hàng; không nên xem file Excel là chứng từ thay thế. Theo Nghị định 123/2020/NĐ-CP và Thông tư 78/2021/TT-BTC, hóa đơn cần thể hiện tên, MST, ký hiệu, số, ngày lập, đơn giá, thuế suất và tiền thuế GTGT phù hợp.

Tách tiền hàng và tiền thuế

Trong sheet TheoDoiCongNo, cột J là giá trị trước thuế, K là thuế suất, L là tiền thuế và M là tổng hóa đơn. Ví dụ 45.000.000 ₫ áp dụng thuế suất 8% tạo ra 3.600.000 ₫ tiền thuế và tổng giá trị 48.600.000 ₫. Cách tách này giúp bạn đối chiếu nhanh với hóa đơn thay vì nhập một con số tổng rồi tự nhẩm ngược.

Kiểm tra dữ liệu trước kỳ kê khai

Thuế suất trong file có các lựa chọn 0%, 5%, 8% và 10%, nhưng bạn phải chọn đúng theo từng hàng hóa, dịch vụ và chính sách áp dụng tại thời điểm lập hóa đơn. Không được mặc định mọi dòng là 10% chỉ vì đây là thuế suất phổ thông.

Đối chiếu khoản khách đã trả

Cột N ghi số tiền đã thu, cột P ghi ngày thu gần nhất và cột O tính phần còn phải thu. Nếu tổng hóa đơn 13.200.000 ₫ và đã thu đủ 13.200.000 ₫, công thức đưa dư nợ về 0 ₫ và trạng thái thành Đã thanh toán. Ảnh 3 cho thấy TongQuan dùng SUM, SUMIF và COUNTIF để tổng hợp; tuy nhiên các chỉ tiêu trên đó chỉ chính xác khi chứng từ gốc và số tiền thu đã được nhập đầy đủ.

Lỗi đáng ngại nhất không nằm ở công thức, mà ở việc nhập thiếu một mắt xích giữa hóa đơn và tiền thu. Khi số còn phải thu sai, nhân viên có thể gọi nhầm khách đã trả tiền hoặc bỏ quên khoản nợ lớn đã quá hạn nhiều ngày.

Nhập sai mã khách hàng

Nếu chọn nhầm KH001 thay vì KH002, VLOOKUP sẽ lấy tên, MST và nhân viên của khách khác. Hóa đơn 203.500.000 ₫ của Công ty CP Đại Việt có thể bị gán cho Công ty TNHH Minh Phát, khiến báo cáo theo khách hàng trên TongQuan lệch toàn bộ dù số tiền hóa đơn vẫn đúng.

Ghi tiền đã thu vượt hoặc thiếu

Với hóa đơn 48.600.000 ₫, nếu bạn nhập Đã thu là 50.000.000 ₫ thì công thức MAX giữ Còn phải thu ở 0 ₫, nhưng dữ liệu thanh toán vẫn sai và cần kiểm tra lại sao kê. Ngược lại, bỏ quên khoản thu 20.000.000 ₫ sẽ làm dư nợ cao hơn thực tế đúng bằng số tiền đó.

Để trống ngày đến hạn

Cột Q không thể tính số ngày quá hạn khi cột I trống, còn cột R cũng không thể phân loại chính xác. Đây là lỗi thường gặp khi nhân viên nhập nhanh hóa đơn nhưng không đối chiếu điều khoản 15, 30 hoặc 45 ngày trên hợp đồng.

Sửa nhầm cột công thức

Ảnh 4 ghi rõ không sửa các cột Tên khách hàng, Tiền thuế GTGT, Tổng giá trị hóa đơn, Còn phải thu, Số ngày quá hạn và Trạng thái công nợ. Xóa công thức ở một dòng có thể làm một hóa đơn không còn cập nhật trạng thái, và đến cuối tháng bạn phải dò lại từng dòng thay vì chỉ kiểm tra các ô nhập liệu màu vàng.

Mẫu chỉ có giá trị khi được cập nhật theo một lịch cố định. Tôi khuyên bạn chốt một lần vào chiều thứ Sáu, ngay sau khi kế toán đối chiếu ngân hàng, thay vì đợi đến ngày cuối tháng mới nhập dồn 30 hoặc 40 hóa đơn.

Gắn file với ba thời điểm cố định

• Ngày phát hành hóa đơn: nhập G đến M và kiểm tra MST, thuế suất, ngày đến hạn.
• Ngày nhận tiền: cập nhật N và P ngay khi tiền vào tài khoản hoặc quỹ.
• Chiều thứ Sáu: lọc R = Quá hạn, sau đó ghi lịch gọi hoặc email tại cột T.

Giảm lỗi khi nhập liệu

Chỉ sửa vùng dữ liệu đầu vào, giữ nguyên danh mục khách hàng và sao lưu file trước khi khóa sổ tháng. Danh sách chọn sẵn cho mã khách hàng và thuế suất giúp hạn chế lỗi chính tả; định dạng ngày DD/MM/YYYY giúp bạn không nhầm 05/04 với 04/05.

Khi nào cần phần mềm quản lý công nợ

File này hợp lý khi nhóm có khoảng 100 dòng hóa đơn và một vài người cập nhật. Nếu cửa hàng online phát sinh 300 đơn mỗi tháng, nhiều người cùng sửa, cần phân quyền, lịch sử chỉnh sửa hoặc tự gửi nhắc nợ, bạn đã vượt quá cách làm phù hợp của một file Excel đơn lẻ. Khi đó nên chuyển sang phần mềm, nhưng vẫn giữ mẫu này làm bảng đối chiếu và kiểm tra số liệu trong giai đoạn chuyển đổi.

Ảnh 3 giúp người quản lý xem nhanh tổng còn phải thu và công nợ theo khách hàng; ảnh 4 nên được giữ trong file để người mới tiếp nhận có thể làm đúng quy trình ngay từ tháng đầu.