CÁCH THEO DÕI CÔNG NỢ PHẢI THU KHÁCH HÀNG TRÊN EXCEL MỚI NHẤT

Chắc hẳn các kế toán viên đã quá thân thuộc với bảng theo dõi công nợ khách hàng trong quá trình học và thực hành nghiệp vụ của mình. Hôm nay, Gitiho sẽ giới thiệu cho các bạn một thủ thuật lập bảng theo dõi công nợ phải thu khách hàng sử dụng phần mềm Microsoft Excel nhằm tối ưu hóa thời gian và thao tác trong công việc.

Để lập được bảng theo dõi này, trước hết bạn cần thực sự hiểu được những điều tổng quan về bảng theo dõi công nợ khách hàng.

Xem ngay: Thành thạo cách theo dõi công nợ nhờ khóa học kế toán tổng hợp online này:

Mục tiêu và chức năng của bảng theo dõi công nợ phải thu khách hàng

Đối với các khách hàng, đơn vị có phát sinh giao dịch trong kỳ kế toán với công ty, kế toán của công ty đó cần phải luôn sát sao tình hình biến động về các khoản nợ, khoản thu đối với các khách hàng này. Dựa trên những thông tin đó, các nhà lãnh đạo hay những người có thẩm quyền cao hơn có thể đưa ra các quyết định về chính sách, điều khoản thanh toán, thời hạn nợ, chiết khấu, khuyến mãi với các khách hàng đó. 

Chứng từ tham chiếu với Bảng theo dõi này là Bảng kê chi tiết công nợ phải thu khách hàng.

Bảng theo dõi bao gồm nhiều dòng, mỗi dòng đại diện cho 1 khách hàng của công ty. Hay nói cách khác, công ty, đơn vị có bao nhiêu khách hàng thì bảng (sổ) theo dõi công nợ phải thu cũng cần có từng đó dòng.

Xem thêm: 

Bài tập tính khuyến mãi trong Excel có dùng hàm VLOOKUP

Hướng dẫn cách ghi sổ chi tiết công nợ phải thu chính xác

Mẫu bảng theo dõi công nợ phải thu khách hàng

Bảng theo dõi công nợ phải thu khách hàng cần tuân thủ một số nguyên tắc kế toán như phản ánh được những thông tin như số dư đầu và cuối kỳ, số phát sinh Nợ, Có trong kỳ. Ngoài ra, tùy theo nhu cầu sử dụng và đặc điểm đối tượng khách hàng cũng như quá trình theo dõi mà các công ty, đơn vị có thể tùy chỉnh các chỉ tiêu cho hợp lý.

Hãy cùng nhìn qua mẫu Bảng theo dõi công nợ phải thu dưới đây:

Cách thao tác thực hiện theo dõi công nợ phải thu khách hàng trên Excel

Bước 1: Chuẩn bị dữ liệu (data)

Bạn cần chuẩn bị dữ liệu (data) để có thể thực thiện thao tác lập bảng. Hãy chuẩn bị bảng kê chi tiết giao dịch phát sinh của khách hàng trong kỳ (bảng nhập liệu hàng ngày) như sau:

Và bảng Danh mục khách hàng kèm theo số dư đầu kỳ của từng khách hàng

Bước 2: Xác định các hàm Excel cần sử dụng

Theo Gitiho, bạn đọc nên sử một một số hàm Excel sau để lập bảng (sổ) theo dõi công nợ phải thu khách hàng:

1. Để tính số dư đầu kỳ và số phát sinh trong kỳ ta sử dụng hàm VLOOKUP, SUMIFS
2. Để tối ưu và đảm bảo dữ liệu chính xác khi thay đổi khoảng thời gian thì ta sử dụng thêm các hàm MIN, MAX

Bước 3: Đặt tên vùng dữ liệu

Để công thức dễ theo dõi hơn thì mình có đặt tên cho các vùng dữ liệu trong trang tính dữ liệu (mang tên DATA), cụ thể như sau:

Xem thêm: Cách hạch toán chi phí mua ngoài, thuê ngoài và chứng từ công nợ

Bước 4: Tính số dư đầu kỳ

Để thiết kế bảng theo dõi công nợ phải thu này có thể linh hoạt thay đổi số liệu khi bạn thay đổi khoảng thời gian nên số dư đầu kỳ ở đây là số dư tính tới trước thời điểm từ ngày (G7).

Bạn cần điền công thức vào ô E12 (mục Nợ đầu kỳ) như sau:

=MAX(VLOOKUP(C12,DMKH!$A$3:$H$6,7,0) -VLOOKUP(C12,DMKH!$A$3:$H$6,8,0)+SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”<” & $G$7) – SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS, “<” &$G$7),0)

Và công thức ở ô E13 (Có đầu kỳ) như sau:

=-MIN(VLOOKUP(C12,DMKH!$A$3:$H$6,7,0) -VLOOKUP(C12,DMKH!$A$3:$H$6,8,0)+SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”<” & $G$7) – SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS, “<” &$G$7),0)

Bạn có thể thấy, 2 công thức trên về cơ bản là giống nhau, thay vì hàm kết hợp hàm MAX để xác định số dư bên Nợ thì số dư bên Có bạn thay MAX = -MIN.

Nếu bạn thay đổi ngày tháng trong ô G7, thì số dư ở ô này luôn luôn đúng là số dư tính tới thời điểm trước từ ngày (hay chính là NGAY_GS)

Bước 5: Tính số phát sinh trong kỳ

 Công thức với số phát sinh trong kỳ sẽ đơn giản hơn các công thức trước như sau:

Với số dư bên Nợ (G12)

=SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”>=” & $G$7, NGAY_GS, “<=” & $G$8)
 

Với số dư bên Có (H12):

 =SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS,”>=” & $G$7, NGAY_GS, “<=” & $G$8)

Tính tổng số tiền với điều kiện NGAY_GS nằm trong khoảng từ ngày – đến ngày. Ta có kết quả như hình:

Bước 6: Tính số dư cuối kỳ

Bạn cần điền các công thức tính sau:

Tại ô I12 (số dư cuối kỳ bên Nợ)

=MAX(E12+G12-F12-H12,0)

Tại ô J12 (số dư cuối kỳ bên Có)

=-MIN(E12+G12-F12-H12,0)

Sau khi đã điền hết các công thức vào các ô tương ứng ở dòng đầu tiên trong Bảng. Bạn chỉ cần FillDown các công thức đó xuống các ô tương tự ở các dòng dưới (mỗi dòng tương ứng là 1 khách hàng). Sau đó tộng cổng cuối Bảng bằng hàm SUM là đã hoàn tất.

Xem thêm: Hướng dẫn cách lập công thức tính số dư cuối kỳ trong bảng cân đối phát sinh trên Excel

Kết luận

Hy vọng qua bài viết trên có thể giúp bạn nắm được kiến thức về kế toán công nợ trên Excel để có thể lập bảng theo dõi công nợ khách hàng. Gitiho chúc bạn ứng dụng thành công!