Cách lập Báo cáo Tuổi nợ Phải thu trong Excel (30, 60, 90 ngày)

Báo cáo tuổi nợ phân loại các hóa đơn chưa thanh toán vào các nhóm dựa trên số ngày quá hạn, thường là 0–30, 31–60, 61–90 và trên 90 ngày. Có hai quyết định cốt lõi để xác định xem báo cáo của bạn có chính xác hay không. Thứ nhất là bạn tính tuổi nợ từ ngày đến hạn hay ngày xuất hóa đơn. Thứ hai là hóa đơn đã thanh toán một phần sẽ hiển thị toàn bộ số tiền hay số dư còn lại.

Chỉ cần sai một trong hai yếu tố này, tổng số tiền của từng nhóm tuổi nợ sẽ bị sai lệch, điều này còn tệ hơn là không có báo cáo nào cả.

Hướng dẫn này sẽ giải thích lý do tại sao việc lập báo cáo thường bị lỗi, ba phương pháp phổ biến và giới hạn của từng phương pháp. Đây là một quy trình xử lý dữ liệu, không phải lời khuyên kế toán, vì vậy hãy xác nhận lại cách xử lý với người quản lý sổ cái của bạn.

Tại sao báo cáo tuổi nợ dễ làm hỏng bảng tính

Vấn đề đầu tiên là câu hỏi về ngày tháng. Tính tuổi nợ từ ngày xuất hóa đơn cho biết chứng từ đó đã tồn tại bao lâu. Tính tuổi nợ từ ngày đến hạn cho biết khách hàng đã trễ hạn bao lâu, và đối với việc thu hồi nợ, đây chính là con số bạn cần.

Cả hai cách đều có lý do hợp lý và tạo ra các báo cáo khác nhau. Điểm mấu chốt dễ gây lỗi là một bảng tính mà không ai ghi chú lại phương pháp nào đã được sử dụng.

Vấn đề thứ hai là thanh toán một phần. Một hóa đơn trị giá $10,000 đã nhận thanh toán $7,000 thì khoản phải thu thực tế là $3,000, và con số $3,000 này phải xuất hiện chính xác trong một nhóm tuổi nợ duy nhất. Các báo cáo tuổi nợ được xây dựng từ danh sách hóa đơn thay vì danh sách các khoản mục chưa tất toán (open-items) sẽ âm thầm làm phóng đại mọi con số.

Vấn đề thứ ba là báo cáo chỉ mang tính chất thời điểm (snapshot). Các nhóm tuổi nợ được tính toán so với ngày hôm nay, vì vậy tệp dữ liệu của ngày hôm qua đã trở nên lỗi thời, và mỗi lần lập lại báo cáo là mỗi lần phải tính toán lại từng dòng.

Tiếp theo là những dòng dữ liệu phức tạp. Giấy báo có (credit note), khoản trả trước, hóa đơn đang tranh chấp và số dư đa tiền tệ đều cần có quy tắc xử lý riêng. Và mỗi quy tắc đó phải đảm bảo không bị xáo trộn khi người tiếp theo mở tệp dữ liệu.

Từng vấn đề riêng lẻ thì không khó giải quyết. Chúng chỉ trở nên phức tạp khi xuất hiện cùng một lúc, mỗi tháng một lần, dưới áp lực của thời hạn hoàn thành.

Những tổn thất bạn phải gánh chịu

Một danh sách thu hồi nợ không thể sử dụng được. Mục đích của việc phân nhóm tuổi nợ là để biết cần gọi cho ai trước. Một báo cáo phóng đại số dư sẽ khiến nhân viên đi đòi những khoản tiền thực tế đã được thanh toán.

Phải làm lại việc mỗi tháng. Vì các nhóm tuổi nợ được tính tương đối so với ngày hôm nay, báo cáo tuổi nợ không bao giờ có trạng thái hoàn thành vĩnh viễn. Mỗi chu kỳ đều lặp lại cùng các thao tác liên kết dữ liệu, cùng các công thức và cùng các bước kiểm tra thủ công.

Tổng số tiền không khớp với sổ cái. Khi tổng số tiền của các nhóm tuổi nợ không khớp với số dư khoản phải thu, báo cáo sẽ mất đi độ tin cậy. Việc tìm ra nguyên nhân sai lệch thường mất nhiều thời gian hơn cả việc lập báo cáo ban đầu.

Một báo cáo tuổi nợ được tin cậy là nhờ tổng số tiền của nó khớp với sổ cái. Nếu không đạt được điều này, mọi thông tin khác trong báo cáo đều trở nên vô nghĩa.

Những giải pháp tạm thời thường được áp dụng

Tùy chọn 1: Thống nhất các định nghĩa trước khi thiết lập công thức

Hãy ghi rõ bốn điều ở đầu trang tính: Bạn tính tuổi nợ từ ngày nào, các mốc phân nhóm là gì, số tiền là tổng số hay đã trừ đi các khoản thanh toán, và ngày chốt số liệu (as-of date) là ngày nào.

Việc này chỉ mất mười phút nhưng giúp ngăn chặn những tranh cãi phổ biến nhất. Tạp chí Journal of Accountancy cũng hướng dẫn quy trình thiết lập tương tự với sự nhấn mạnh vào việc chuẩn bị đúng ngay từ đầu.

Điều này cũng giúp bạn xác định nguồn dữ liệu cần dùng. Bạn sẽ cần một bản trích xuất các khoản mục chưa tất toán với số dư còn lại, chứ không phải danh sách của mọi hóa đơn từng được phát hành.

Giới hạn của phương pháp này là các định nghĩa không tự tính toán bất cứ điều gì. Chúng chỉ giúp bạn không tính toán sai hướng.

Tùy chọn 2: Tạo cột phân nhóm, sau đó tổng hợp số liệu bằng Pivot Table

Tính số ngày quá hạn bằng cách lấy ngày chốt số liệu trừ đi ngày đến hạn, sau đó ánh xạ con số đó vào một nhãn nhóm tuổi nợ. Hàm TODAY cung cấp cho bạn ngày chốt số liệu cập nhật theo thời gian thực, và hàm DATEDIF trả về số ngày giữa hai mốc ngày tháng.

Đối với việc gán nhãn, hàm IFS sẽ dễ đọc hơn các câu lệnh IF lồng nhau khi bạn xem lại sau sáu tháng. Sau đó, hãy tính tổng theo khách hàng và nhóm tuổi nợ bằng hàm SUMIFS để đảm bảo việc kiểm tra tính toán có thể thực hiện dễ dàng trên từng dòng dữ liệu.

Hãy sử dụng một ngày chốt số liệu cố định thay vì hàm TODAY khi gửi báo cáo đi. Một tệp dữ liệu tự động cập nhật lại tuổi nợ vào tuần tới sẽ mâu thuẫn với phiên bản đã nằm trong hộp thư đến của người nhận.

Giới hạn ở đây là khối lượng dữ liệu và các trường hợp ngoại lệ. Các công thức vẫn hoạt động tốt, nhưng các giấy báo có, khoản thanh toán một phần và các tranh chấp vẫn phải xử lý thủ công.

Tùy chọn 3: Duy trì một tab quy tắc bên cạnh bảng số liệu

Hãy tập hợp các quyết định phức tạp vào một nơi duy nhất: Cách cấn trừ giấy báo có, hóa đơn tranh chấp được loại trừ hay đánh dấu gắn cờ, cách quy đổi số dư ngoại tệ và áp dụng tỷ giá nào.

Đây là yếu tố giúp báo cáo duy trì được tính nhất quán khi có người khác vận hành. Tuy nhiên, đây cũng là tab thường bị bỏ qua khi thời hạn cuối tháng cận kề.

Hạn chế là tab quy tắc chỉ ghi lại các nguyên tắc xử lý chứ không tự động áp dụng chúng. Ai đó vẫn phải thực thi từng quy tắc trong mỗi chu kỳ. Hướng dẫn của chúng tôi về đối chiếu giao dịch trong bảng tính sẽ đề cập đến công việc khớp dữ liệu để phục vụ cho bước này.

Giới hạn chung. Cả ba phương án trên đều giả định rằng bạn bắt đầu từ một bản trích xuất các khoản mục chưa tất toán sạch sẽ. Khi nguồn dữ liệu là một tệp xuất hóa đơn thô cộng với một tệp thanh toán riêng biệt, công việc thực sự khó khăn là liên kết chúng lại với nhau trước khi bắt đầu phân nhóm.

Cách lập báo cáo tuổi nợ bằng Powerdrill Bloom

Bước 1: Tải lên dữ liệu hóa đơn và thanh toán của bạn

Tải lên bản trích xuất các khoản mục chưa tất toán, hoặc cả tệp hóa đơn và thanh toán cùng lúc. Powerdrill Bloom sẽ phân tích các cột ngay khi tải lên, giúp phát hiện các lỗi như thiếu ngày đến hạn, số tiền bị bỏ trống và số hóa đơn bị trùng lặp trước khi tiến hành phân nhóm.

Bước 2: Mô tả các quy tắc phân nhóm bằng ngôn ngữ tự nhiên

Hãy mô tả các quy tắc thay vì tự xây dựng chúng. Ví dụ, bạn có thể yêu cầu tính tuổi nợ từ ngày đến hạn tính đến một ngày cụ thể, đưa ra các mốc phân nhóm và yêu cầu số tiền phải là số dư ròng sau khi trừ đi các khoản thanh toán đã nhận.

Sau đó, yêu cầu thực hiện các bước kiểm tra trong cùng một lượt: Hỏi xem hóa đơn nào có số tiền thanh toán vượt quá giá trị hóa đơn, hóa đơn nào có ngày đến hạn trước ngày xuất hóa đơn, và liệu tổng số tiền của các nhóm tuổi nợ có khớp với số dư khoản phải thu hay không.

Bước 3: Xuất biểu đồ, báo cáo hoặc slide trình bày

Xuất bảng tuổi nợ theo từng khách hàng, biểu đồ phân bổ nhóm tuổi nợ hoặc danh sách thu hồi nợ được sắp xếp theo số dư nợ lâu nhất.

Tại sao giải pháp này vượt trội hơn việc lập lại báo cáo mỗi tháng

	Phương pháp thủ công	Powerdrill Bloom
Liên kết hóa đơn với thanh toán	Sử dụng các công thức tìm kiếm cho từng tệp	Tải cả hai lên và đặt câu hỏi
Thay đổi ngày chốt số liệu	Tính toán lại và xác minh lại	Chỉ cần đưa ra ngày mới
Cấn trừ các khoản thanh toán một phần	Tự tạo cột số dư thủ công	Yêu cầu tính số dư sau khi trừ thanh toán
Đối chiếu tổng số tiền với sổ cái	Kiểm tra thủ công mỗi chu kỳ	Hỏi xem các tổng số tiền có khớp nhau không

Những bước trung gian này chính là nơi tiêu tốn phần lớn thời gian trong tháng của bạn. Việc phân nhóm chỉ là các phép tính toán học; việc chuẩn bị được một danh sách các khoản mục chưa tất toán sạch sẽ mới là công việc thực sự.

Các sai lầm thường gặp

Tính tuổi nợ từ ngày xuất hóa đơn trong khi bạn muốn tính từ ngày đến hạn. Đối với việc thu hồi nợ, ngày đến hạn hầu như luôn là lựa chọn chính xác. Dù bạn chọn cách nào, hãy ghi rõ điều đó trên báo cáo.

Hiển thị số tiền trên hóa đơn thay vì số dư còn lại. Một hóa đơn đã thanh toán một phần phải được xếp vào nhóm tuổi nợ với giá trị là số dư chưa thanh toán. Việc sử dụng toàn bộ số tiền ban đầu sẽ làm phóng đại tổng số nợ.

Để hàm TODAY tự động cập nhật tuổi nợ của một tệp đã gửi đi. Hãy cố định ngày chốt số liệu trước khi gửi báo cáo, nếu không hai người khác nhau sẽ đọc được những con số khác nhau từ cùng một tệp dữ liệu.

Bỏ qua các giấy báo có. Một khoản ghi có chưa được áp dụng sẽ nằm trong tài khoản của khách hàng và làm giảm số tiền họ nợ. Việc bỏ qua khoản này sẽ khiến tình hình số dư trông tệ hơn thực tế.

Phân nhóm theo khách hàng thay vì theo hóa đơn. Việc phân nhóm phải được thực hiện trên từng hóa đơn, sau đó mới cộng tổng theo từng khách hàng. Việc tính trung bình tuổi nợ của một khách hàng sẽ che lấp đi khoản nợ lâu nhất – vốn là khoản bạn cần tập trung xử lý.

Không bao giờ đối chiếu với sổ cái. Tổng số tiền của các nhóm tuổi nợ phải khớp với số dư kiểm soát của các khoản phải thu. Nếu bỏ qua bước kiểm tra này, báo cáo của bạn chỉ mang tính chất trang trí.

Lập lại báo cáo từ đầu trong mỗi chu kỳ. Các quy tắc không thay đổi theo từng tháng, chỉ có dữ liệu thay đổi. Hãy giữ nguyên các quy tắc và chỉ thay thế tệp dữ liệu xuất ra, tương tự như nguyên tắc áp dụng cho báo cáo ngân sách so với thực tế.

Kết luận

Xác định ngày tính tuổi nợ, sử dụng số dư còn lại, cố định ngày chốt số liệu và đối chiếu tổng số tiền với sổ cái. Bốn yếu tố này tạo nên sự khác biệt giữa một báo cáo có giá trị thực thi và một bảng số liệu gây tranh cãi.

Điều khiến công việc này tốn kém thời gian là mọi thứ đều mang tính tương đối so với ngày hôm nay, nghĩa là nó không bao giờ thực sự hoàn thành. Các thao tác liên kết và kiểm tra dữ liệu sẽ luôn quay trở lại trong mỗi chu kỳ.

Nếu đó là nơi tiêu tốn thời gian cuối tháng của bạn, hãy dùng thử Powerdrill Bloom cho các tệp xuất hóa đơn và thanh toán của bạn. Xem thêm hướng dẫn của chúng tôi về chuyển đổi báo cáo tài chính PDF thành biểu đồ và trang phân tích dòng tiền bằng AI.

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

Các nhóm tuổi nợ tiêu chuẩn trong báo cáo tuổi nợ khoản phải thu là gì?

Hầu hết các báo cáo sử dụng các nhóm 0–30, 31–60, 61–90 và trên 90 ngày, thường đi kèm với một cột dành cho các khoản nợ hiện tại hoặc chưa đến hạn. Các mốc phân chia này mang tính quy ước hơn là một quy tắc bắt buộc, vì vậy hãy ghi rõ các mốc bạn đã sử dụng.

Tôi nên tính tuổi nợ hóa đơn từ ngày xuất hóa đơn hay ngày đến hạn?

Hãy sử dụng ngày đến hạn nếu bạn muốn biết khách hàng đang trễ hạn bao lâu, đây thường là mục tiêu chính của việc thu hồi nợ. Hãy sử dụng ngày xuất hóa đơn nếu bạn muốn biết chứng từ đó đã tồn tại bao lâu.

Tôi nên xử lý các khoản thanh toán một phần như thế nào?

Hãy hiển thị số dư còn lại, chứ không phải số tiền gốc trên hóa đơn, và xếp số dư đó vào một nhóm tuổi nợ duy nhất. Việc bắt đầu từ một bản trích xuất các khoản mục chưa tất toán thay vì danh sách hóa đơn sẽ tự động giải quyết vấn đề này.

Tôi cần sử dụng những hàm Excel nào?

Hàm TODAY hoặc một ngày cố định cho ngày chốt số liệu, và hàm DATEDIF để tính số ngày quá hạn. Hàm IFS dùng để gán nhãn nhóm tuổi nợ, và hàm SUMIFS dùng để tính tổng theo khách hàng và nhóm tuổi nợ. Không có hàm nào trong số này phức tạp; phần khó nhất chính là việc thống nhất các định nghĩa.

Báo cáo nên được lập lại với tần suất như thế nào?

Ít nhất là hàng tháng, và hàng tuần nếu hoạt động thu hồi nợ đang diễn ra tích cực, vì mỗi nhóm tuổi nợ đều được tính tương đối so với ngày chốt số liệu. Hãy cố định ngày đó trên mỗi phiên bản bạn gửi đi.