Hướng dẫn lập biên bản đối chiếu công nợ trên Excel

Một trong những công việc quan trọng của kế toán viên là lập biên bảng đối chiếu công nợ. Trong quá trình lập biên bản, có thể các bạn sẽ tốn không ít thời gian điền dữ liệu theo cách thủ công. Để giúp các bạn giải quyết vấn đề này, hôm nay Gitiho sẽ giới thiệu phương pháp lập biên bản đối chiếu công nợ tự động trên Microsoft Excel. 

Nội dung của biên bản đối chiếu công nợ

Với mục đích ghi chép chi tiết việc đối chiếu công nợ của doanh nghiệp, một biên bản đối chiếu công nợ 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,...

Qua những thông tin trong biên bản đối chiếu công nợ, kế toán viên có nhiệm vụ kiểm tra thực trạng mua bán giữa bên mua và bên bán, đồng thời kiểm soát tình hình nợ của doanh nghiệp với các bên cung ứng cũng như của khách hàng với doanh nghiệp.

Cấu trúc biên bản đối chiếu công nợ trong Excel

Từ 3 nội dung chính của một biên bản đối chiếu công nợ đã được đề cập ở phần trên, chúng ta xác định 3 sheet Excel tương ứng phải lập.

• Danh sách hợp đồng của từng khách hàng: Mỗi hợp đồng đóng vai trò là một đối tượng theo dõi công nợ riêng biệt, nghĩa là trong trường hợp một khách hàng sở hữu nhiều hợp đồng khác nhau thì kế toán viên cũng cần theo dõi các hợp đồng ấy riêng biệt. Áp dụng các nguyên tắc bù trừ, tổng sau cùng của các hợp đồng đó sẽ là tổng số công nợ cần theo dõi.
• Thông tin thanh toán: Các thông tin về thanh toán trong mỗi hợp đồng bao gồm thời gian thanh toán, số tiền thanh toán, số lần thanh toán, cũng phải được theo dõi chi tiết.
• Mẫu biên bản đối chiếu: Để kế toán viên dễ dàng tổng hợp thông tin cần thiết của các đối tượng trong biên bản đối chiếu công nợ, mọi thông tin sẽ được hiển trị trong cùng một sheet.

Xem thêm: Quản lý công nợ phải thu khách hàng hiệu quả cho các doanh nghiệp

Mẫu biên bản đối chiếu công nợ trong Excel

Sau đây là các công thức nhập liệu cần dùng để lập biên bản đối chiếu công nợ trong Excel cụ thể cho từng ô thông tin:

Thông tin khách hàng

Tại ô H1, chúng ta điền công thức hàm INDEX và MATCH như sau để Excel tự động hiển thị thông tin khách hàng:

• 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!$F$1,KhachHang!$A$3:$A$32,0)

Số liệu trong bảng kê khai công nợ

• Số tiền đã 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ố tiền còn nợ được tính bằng công thức Số tiền - Đã thanh toán: =K3-L3
• Số thứ tự của hợp đồng được xác định bằng hàm COUNTIF: =COUNTIF($A$3:A3,A3)

Số hợp đồng

Tại đây, chúng ta cần sử dụng tham chiếu theo điều kiện: kết hợp Tên khách hàng và Số thứ tự (STT) để xác định hợp đồng liên quan bằng hàm INDEX:

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

Sau khi nhập xong công thức, chúng ta ấn tổ hợp phím Ctrl+Shift+Enter để Excel tiến hành trả kết quả.

Lưu ý: Nếu hiện lỗi #N/A khi tìm kết quả ở các vùng STT lớn, chúng ta có thể sử dụng thêm hàm IFERROR.

Các số liệu còn lại của hợp đồng

Chúng ta sử dụng hàm VLOOKUP để xác định các thông tin còn lại của hợp đồng, cụ thể như sau:

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

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

Xem thêm: Tìm hiểu về cách ghi Sổ chi tiết công nợ phải trả người bán

Số tiền dưới dạng chữ

Tham khảo chi tiết cách viết số tiền bằng chữ: Làm thế nào sử dụng công thức viết số tiền bằng chữ trong kế toán.

Trên đây là hướng dẫn cách lập biên bản đối chiếu công nợ trên Excel. Việc tự động điền dữ liệu của hệ thống Excel sẽ tăng độ chính xác lên gấp nhiều lần so với việc điền dữ liệu thủ công. Nhờ vậy, chúng ta có thể trình bày một biên bản phức tạp một cách khoa học và nhanh chóng chỉ với những hàm cơ bản. Nếu các bạn còn chưa nắm rõ cách thức sử dụng các hàm IF, SUMIFS, COUNTIFS, VLOOKUP trong bài, hãy tham khảo ngay các bài hướng dẫn của Gitiho nhé.

Chúc các bạn kế toán viên áp dụng thủ thuật này thật thành công!