Lập biên bản đối chiếu công nợ là một trong những công việc quan trọng của kế toán. Ngoài việc chốt số tiền công nợ để thực hiện việc thu nợ, thanh toán nợ, nó còn có tác dụng chốt số liệu kế toán cuối năm, cuối quý… giúp ban giám đốc và bộ phận kế toán quản lý công việc một cách hiệu quả. Nhưng bạn có thể sẽ mất nhiều thời gian khi phải đối chiếu công nợ cho nhiều công ty. Hãy tìm hiểu cách lập mẫu biên bản đối chiếu công nợ trên Excel để làm việc này đơn giản hơn nào.

Nguyên tắc của biên bản đối chiếu công nợ

Biên bản này gồm 3 phần chính:

• Bên gửi bản đối chiếu (thường là bên thu nợ)
• Bên nhận bản đối chiếu (thường là bên phải trả nợ)
• Các thông tin liên quan: Thời gian đối chiếu, lịch sử thông tin phát sinh và thanh toán nợ, số nợ còn lại cần thanh toán…

Vì vậy biên bản đối chiếu công nợ thường lập ra để thu hồi nợ, chốt số nợ phải thu.

Kế toán sẽ nhận các biên bản đối chiếu công nợ của các đối tác để chốt số nợ phải trả. Việc này không cần lập biên bản nữa mà chỉ cần đối chiếu số nợ mà 2 bên đang theo dõi.

Những nội dung cần có trong file Excel quản lý công nợ

Như nguyên tắc trên, chúng ta thấy:

• Bên nhận bản đối chiếu: Chúng ta cần có 1 danh sách hợp đồng của từng khách. Mỗi hợp đồng sẽ là 1 đối tượng theo dõi công nợ. Một khách hàng có thể có nhiều hợp đồng khác nhau, cần theo dõi riêng. Tổng của các hợp đồng đó sẽ là công nợ cần chốt (có áp dụng nguyên tắc bù trừ)
• Thông tin thanh toán: Không phải hợp đồng nào cũng được thanh toán ngay hoặc chỉ thanh toán duy nhất 1 lần. Do đó các thông tin về thanh toán của mỗi hợp đồng cũng cần được theo dõi chi tiết.
• Mẫu biên bản đối chiếu: Đây sẽ là nơi tổng hợp các thông tin của đối tượng có liên quan.

Như vậy cần có tối thiểu là 3 sheet trong 1 file excel.

Mẫu biên bản đối chiếu công nợ tự động trên Excel

Cách xây dựng công thức lấy dữ liệu cho mẫu Biên bản đối chiếu công nợ trên Excel:

Khi chọn tên Khách hàng tại ô H1 ta có:

1. Lấy thông tin của bên mua hàng căn cứ theo khách hàng được chọn bằng hàm Index + Match

Tên người đại diện thứ 1 =INDEX(KhachHang!$D$3:$D$32,MATCH(BB_CN!$H$1,KhachHang!$A$3:$A$32,0))

Chức vụ người đại diện thứ 1 =INDEX(KhachHang!$E$3:$E$32,MATCH(BB_CN!$H$1,KhachHang!$A$3:$A$32,0))

2. Trong bảng thông tin khách hàng, tính một số chỉ tiêu:

• Số Đã thanh toán được tính bằng hàm SUMIFS

=SUMIFS(ThanhToan!$C$3:$C$25,ThanhToan!$B$3:$B$25,I3,ThanhToan!$A$3:$A$25,”<=”&BB_CN!$H$2)

• Số Còn nợ được tính bằng cách lấy số tiền HĐ – Số đã thanh toán

=K3-L3

• Số thứ tự để xác định số hợp đồng phát sinh của khách hàng:

=COUNTIF($A$3:A3,A3)

3. Xác định số hợp đồng

Sử dụng tham chiếu theo nhiều điều kiện: kết hợp điều kiện Tên khách hàng và Số thứ tự (STT) để xác định các hợp đồng có liên quan của khách hàng đó bằng công thức:

=INDEX(KhachHang!$I$3:$I$32,MATCH(BB_CN!$H$1&G18:G22,KhachHang!$A$3:$A$32&KhachHang!$H$3:$H$32,0))

Kết thúc công thức nhấn phím Ctrl + Shift + Enter

Để tránh lỗi #N/A có thể xuất hiện khi không tìm thấy kết quả ở các STT lớn, chúng ta có thể sử dụng thêm hàm bẫy lỗi IFERROR

4. Các thông tin còn lại của hợp đồng

Các thông tin còn lại sử dụng hàm Vlookup để xác định dựa theo số hợp đồng đã có ở bước 3

• Ngày ký =IF($A18=””,””,VLOOKUP($A18,KhachHang!$I$3:$M$32,2,0))
• Số tiền =IF($A18=””,””,VLOOKUP($A18,KhachHang!$I$3:$M$32,3,0))
• Đã thanh toán =IF($A18=””,””,VLOOKUP($A18,KhachHang!$I$3:$M$32,4,0))
• Còn nợ =IF($A18=””,””,VLOOKUP($A18,KhachHang!$I$3:$M$32,5,0))

Trong công thức trên, kết hợp thêm bẫy lỗi không có số hợp đồng (trường hợp ô số hợp đồng là ô trống) sẽ trả về giá trị rỗng trước, sau đó mới dùng hàm VLOOKUP

5. Đọc số tiền bằng chữ: Tham khảo tại bài viết: Hướng dẫn cách sử dụng công thức viết số tiền bằng chữ trong kế toán

Như vậy chỉ với một số hàm Excel cơ bản, chúng ta đã có thể tạo ra mẫu biên bản đối chiếu công nợ tự động thay đổi thông tin dựa theo tên khách hàng được chọn rồi.

Các bạn có thể tải file mẫu tại địa chỉ: http://bit.ly/2Qt0l1d