Cách lập mẫu biên bản đối chiếu công nợ tự động trên Excel

Biên bản đối chiếu công nợ là một loại tài liệu kế toán vô cùng quan trọng, giúp thống kế chi tiết tình hình nợ và cho vay nợ của doanh nghiệp, có xác nhận của các bên liên quan. Vì mang tính chất tổng hợp, thống kê nên việc lập biên bản đối chiếu công nợ tương đối mất nhiều thời gian. Vậy, biên bản đối chiếu công nợ là gì? Làm thế nào để lập biên bản đối chiếu công nợ tự động trên Excel, giúp công việc này trở nên nhanh và chính xác hơn? Hãy cùng Gitiho tìm hiểu trong bài viết này nhé!

Kế toán Tổng hợp từ A - Z - Ai cũng có thể trở thành Kế toán Tổng hợp trong 14 giờ

Biên bản đối chiếu công nợ là gì? Tại sao cần lập biên bản đối chiếu công nợ?

Khái niệm biên bản đối chiếu công nợ

Biên bản đối chiếu công nợ là một trong những tài liệu kế toán vô cũng quan trọng. Biên bản đối chiếu công nợ là biên bản tổng hợp, so sánh, hạch toán tất cả các khoản nợ phải thu và phải trả của doanh nghiệp một cách đầy đủ và chính xác với từng giao dịch phát sinh trong kỳ kế toán. Biên bản đối chiếu công nợ thường được lập cuối năm.

Ý nghĩa của biên bản đối chiếu công nợ

Biên bản đối chiếu công nợ là căn cứ chính xác, hợp pháp thống kê một cách chi tiết, chính xác và có xác nhận của các bên liên quan về tình hình nợ và cho vay nợ của doanh nghiệp. Vì vậy, biên bản đối chiếu công nợ hỗ trợ cho việc theo dõi, giám sát, chốt số tiền công nợ cần phải trả và phải thu để thực hiện việc thu nợ và thanh toán nợ. Bên cạnh đó. biên bản đối chiếu công nợ còn hỗ trợ chốt số liệu cuối quý, cuối năm và là điều kiện cân để doanh nghiệp quyết toán thuế.

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

Biên bản đối chiếu công nợ cần có đủ 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…

Xem thêm: Hướng dẫn các kiến thức cơ bản về Nguồn vốn trong Kế toán

Cách lập biên bản đối chiếu công nợ tự động trên Excel

Những nội dung cần có để lập biên bản đối chiếu công nợ tự động trên Excel

Dựa theo nguyên tắc đã được nêu ở phần trước, chúng ta có thể thấy những nội dung cần thiết để lập biên bản đối chiếu công nợ tự động trên Excel:

• Bên nhận bản đối chiếu: Cần phải có danh sách hợp đồng của mỗi bên. Mỗi hợp đồng là 1 đối tượng cần theo dõi. Một bên có thể có nhiều khoản vay, khoản nợ khác nhau, vì vậy, cần có hợp đồng quản lý riêng cho mỗi khoản vay, khoản nợ. Tổng của các hợp đồng đó sẽ là công nợ cần chốt (áp dụng nguyên tắc bù trừ).
• Thông tin thanh toán: Mỗi hợp đồng khác nhau sẽ có thời gian thanh toán, thời hạn thanh toán hoặc hình thức thanh toán (trả ngay, trả góp....) khác nhau. Vì vậy, cần có thông tin thanh toán để dễ dàng theo dõi tình hình, tiến trình thu hồi hoặc thanh toán nợ.
• Mẫu biên bản đối chiếu công nợ: Là nơi tổng hợp, thống kê các thông tin của đối tượng có liên quan.

Sau khi đã xác định được những nội dung cần thiết để lập biên bản đối chiếu công nợ tự động trên Excel, chúng ta thấy, cần ít nhất 3 sheets trên bảng tính Excel: Mẫu biên bản đối chiếu công nợ, thông tin khách hàng (danh sách hợp đồng) và thông tin thanh toán

Các công thức hàm Excel cần sử dụng trong biên bản đối chiếu công nợ tự động

Việc ứng dụng các công thức hàm Excel vào biên bản đối chiếu công nợ giúp cho việc lấy thông tin được chính xác và nhanh chóng hơn. Các hàm được sử dụng trong công thức hầu hết là các hàm tính toán và hàm tra cứu, tham chiếu. Chúng ta sẽ vào từng phần cụ thể để biết được các công thức sẽ sử dụng trong bản đối chiếu công nợ nhé!

Để hiểu hơn về các công thức, chúng ta sẽ cùng nhau thực hành ngay trên một ví dụ:

Lấy thông tin của các bên

Để lấy thông tin của bên mua hàng căn cứ theo khách hàng được chọn, ta sẽ sử dụng kết hợp 2 hàm là hàm INDEX và hàm MATCH. Công thức như sau:

• Lấy tên của người đại diện thứ 1:

=INDEX(KhachHang!$D$3:$D$32,MATCH(BB_CN!$H$1,KhachHang!$A$3:$A$32,0))

• Lấy chức vụ của người đại diện thứ 1:

=INDEX(KhachHang!$E$3:$E$32,MATCH(BB_CN!$H$1,KhachHang!$A$3:$A$32,0))

Tính toán trong sheet thông tin khách hàng

• Tính cột Đã thanh toán: Sử dụ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)

• Tính cột Còn nợ: Lấy Số tiền hợp đồng trừ đi 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: Sử dụng hàm COUNTIF: =COUNTIF($A$3:A3,A3)

Xem thêm: Hướng dẫn chi tiết cách tự quyết toán thuế thu nhập cá nhân

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

Để xác định số hợp đồng và điền vào biên bản đối chiếu công nợ, chúng ta sẽ sử dụng hàm tra cứu và tham chiếu nhiều điều kiện để kết hợp thông tin Tên khách hàng và số thứ tự của khách hàng để xác định hợp đồng của khách hàng đó từ sheet KhachHang vào Sheet Mẫu biên bản đối chiếu công nợ. Công thức như sau:

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

Lưu ý:

• Nhấn tổ hợp phím Ctrl + Shift + Enter để kết thúc công thức
• Nếu danh sách có nhiều đối tượng, số thứ tự lớn, để tránh trường hợp không tìm thấy kết quả ở các số thứ tự lớn, gây ra lỗi #N/A, hãy sử dụng thêm hàm IFERROR để khắc phục nhé!

Tính toán các thông tin còn lại

Sau khi đã lấy được số hợp đồng với công thức bên trên, chúng ta sẽ tiến hành xác định các thông tin còn lại có trong biên bản đối chiếu công nợ bằng hàm VLOOKUP. Chi tiết như sau:

• Ngày ký hợp đồng: 

=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))

• Số tiền đã thanh toán:

=IF($A18=””,””,VLOOKUP($A18,KhachHang!$I$3:$M$32,4,0))

• Số tiền còn nợ:

=IF($A18=””,””,VLOOKUP($A18,KhachHang!$I$3:$M$32,5,0))

Xem thêm: Hướng dẫn cách thanh lý tài sản cố định hữu hình trong doanh nghiệp

Tổng kết

Như vậy, chỉ với một vài công thức kết hợp các hàm tham chiếu, tính toán trong Excel, bạn đã có thể dễ dàng lập được biên bản đối chiếu công nợ tự động trong Excel, và đơn giản hóa, tiết kiệm thời gian khi sử dụng biên bản này. Bạn có thể tải mẫu biên bản đối chiếu công nợ tự động trên Excel ở cuối bài viết nhé!

Chúc bạn học tốt!