Mẫu đối chiếu số dư ngân hàng Excel: cách khớp sổ tiền gửi với sao kê
Hướng dẫn lập mẫu đối chiếu số dư ngân hàng trên Excel: các nguyên nhân chênh lệch giữa sổ tiền gửi và sao kê, công thức tự khớp giao dịch, bảng điều chỉnh hai phía và ví dụ cụ thể.
Đối chiếu số dư ngân hàng là so sánh số dư tiền gửi trên sổ sách của doanh nghiệp với số dư trên sao kê ngân hàng cùng ngày, tìm ra từng khoản chênh lệch và điều chỉnh để hai bên khớp nhau. Việc này nên làm ít nhất mỗi tháng, trước khi khóa sổ. Mẫu đối chiếu trên Excel gồm ba phần: dữ liệu sổ tiền gửi, dữ liệu sao kê, và bảng điều chỉnh hai phía. Bài này hướng dẫn cách dựng mẫu, công thức tự khớp giao dịch và một ví dụ có số liệu.
Vì sao sổ sách và sao kê thường không khớp
| Nguyên nhân | Ví dụ | Điều chỉnh bên nào |
|---|---|---|
| Ngân hàng đã ghi, doanh nghiệp chưa ghi | Khách chuyển khoản chưa báo, phí dịch vụ, phí SMS, lãi tiền gửi | Ghi bổ sung vào sổ |
| Doanh nghiệp đã ghi, ngân hàng chưa ghi | Ủy nhiệm chi lập cuối tháng nhưng ngân hàng xử lý sang tháng sau | Điều chỉnh trên bảng đối chiếu, không sửa sổ |
| Sai sót | Ghi nhầm số tiền, ghi trùng, ghi nhầm tài khoản ngân hàng | Sửa sổ theo chứng từ |
Phân loại đúng nguyên nhân là điểm mấu chốt: chỉ những khoản ngân hàng đã ghi mà sổ chưa có, hoặc sai sót của doanh nghiệp, mới cần bút toán điều chỉnh trên sổ.
Cấu trúc file
- Sheet Sổ tiền gửi: ngày, số chứng từ, diễn giải, thu, chi, số tham chiếu (số UNC, mã giao dịch nếu có). Lấy từ sổ chi tiết tài khoản tiền gửi ngân hàng (thường là TK 112) của phần mềm kế toán hoặc từ sổ cái Excel.
- Sheet Sao kê: dán nguyên sao kê tải từ internet banking (định dạng Excel hoặc CSV), giữ các cột ngày, nội dung, ghi nợ, ghi có, số dư, mã giao dịch.
- Sheet Đối chiếu: bảng điều chỉnh hai phía và danh sách khoản chênh lệch.
Định dạng mỗi vùng dữ liệu thành Bảng (Insert > Table) và thống nhất dấu: tiền vào là số dương, tiền ra là số âm, trong một cột “Số tiền”. Cách này giúp so khớp bằng một điều kiện thay vì hai cột thu, chi.
Công thức tự khớp giao dịch
Ở sheet Sổ tiền gửi, thêm cột “Đã khớp” để kiểm tra mỗi giao dịch có xuất hiện trên sao kê hay không. Khớp theo số tiền và ngày lệch không quá 3 ngày (vì ngân hàng có thể xử lý chậm):
=IF(COUNTIFS(SaoKe[SoTien];[@SoTien];SaoKe[Ngay];">="&[@Ngay]-3;SaoKe[Ngay];"<="&[@Ngay]+3)>0;"Khớp";"Chưa khớp")
Làm tương tự ở sheet Sao kê để tìm các khoản ngân hàng đã ghi mà sổ chưa có. Nếu sao kê có mã giao dịch và kế toán ghi lại mã này khi hạch toán, nên khớp theo mã vì chính xác hơn so với số tiền.
Hai giao dịch cùng số tiền trong vài ngày có thể khớp nhầm. Thêm cột đếm số lần xuất hiện: =COUNTIFS([SoTien];[@SoTien]), rồi kiểm tra thủ công những dòng có kết quả lớn hơn 1.
Lưu ý: tùy cài đặt vùng, Excel dùng dấu chấm phẩy (;) hoặc dấu phẩy (,) để ngăn cách đối số.
Bảng đối chiếu hai phía
| Phía sổ sách | Phía ngân hàng |
|---|---|
| Số dư sổ tiền gửi cuối kỳ | Số dư sao kê cuối kỳ |
| + Tiền vào ngân hàng đã ghi, sổ chưa ghi | + Tiền vào sổ đã ghi, ngân hàng chưa ghi |
| − Tiền ra ngân hàng đã ghi, sổ chưa ghi (phí) | − Tiền ra sổ đã ghi, ngân hàng chưa ghi (UNC chưa xử lý) |
| ± Sửa sai sót trên sổ | ± Sai sót của ngân hàng (nếu có, cần liên hệ ngân hàng) |
| Số dư sổ sau điều chỉnh | Số dư ngân hàng sau điều chỉnh |
Ô kiểm tra cuối cùng: =IF(ABS(SoDuSoDieuChinh-SoDuNHDieuChinh)<1;"Đã khớp";"Còn chênh "&TEXT(SoDuSoDieuChinh-SoDuNHDieuChinh;"#,##0")). Nếu vẫn còn chênh, quay lại các dòng “Chưa khớp” ở hai sheet dữ liệu.
Ví dụ đối chiếu cuối tháng
Ngày 31, sổ tiền gửi có số dư 152.400.000 đồng, sao kê có số dư 167.920.000 đồng. Cột “Chưa khớp” chỉ ra bốn khoản:
| Khoản | Phía sổ sách | Phía ngân hàng |
|---|---|---|
| Số dư cuối kỳ | 152.400.000 | 167.920.000 |
| Khách hàng chuyển khoản, sổ chưa ghi | +12.000.000 | |
| Lãi tiền gửi không kỳ hạn | +18.000 | |
| Phí quản lý tài khoản, phí SMS | −33.000 | |
| Ủy nhiệm chi ngày 31 ngân hàng chưa xử lý | −3.535.000 | |
| Số dư sau điều chỉnh | 164.385.000 | 164.385.000 |
Ba khoản đầu cần ghi bổ sung vào sổ với chứng từ là sao kê và giấy báo của ngân hàng. Khoản ủy nhiệm chi chỉ cần theo dõi: nó sẽ xuất hiện trên sao kê tháng sau. Nếu sang tháng sau vẫn chưa thấy, cần hỏi ngân hàng.
Quy trình hằng tháng gợi ý
- Tải sao kê đúng kỳ, kiểm tra số dư đầu kỳ khớp số dư cuối kỳ tháng trước.
- Xuất sổ chi tiết tài khoản tiền gửi cùng kỳ.
- Chạy cột khớp ở hai sheet, xử lý các dòng chưa khớp.
- Lập bảng đối chiếu, ghi bổ sung bút toán còn thiếu, đối chiếu lại.
- Lưu bản đối chiếu đã khớp (PDF hoặc file chỉ đọc) cùng sao kê làm hồ sơ kế toán.
Với nhiều tài khoản ngân hàng, mỗi tài khoản một file hoặc một bộ sheet riêng; đừng gộp, vì số dư và phí khác nhau theo từng tài khoản.
Liên quan đến các sổ khác
Tiền mặt được đối chiếu theo cách tương tự với biên bản kiểm kê quỹ trong mẫu sổ quỹ tiền mặt Excel. Khoản khách chuyển khoản chưa ghi thường liên quan đến công nợ, nên cập nhật luôn file theo dõi công nợ phải thu. Hóa đơn tương ứng với các khoản chi có thể kiểm tra qua file theo dõi hóa đơn. Xem thêm trong chuyên mục Ghi sổ thu chi.
Giới hạn cần biết
Mẫu Excel giúp phát hiện và giải thích chênh lệch; việc hạch toán điều chỉnh phải theo chế độ kế toán doanh nghiệp đang áp dụng và có chứng từ hợp lệ. Hỏi kế toán khi gặp khoản chênh lệch không rõ nguồn gốc.
Câu hỏi thường gặp
Bao lâu nên đối chiếu ngân hàng một lần?
Tối thiểu mỗi tháng trước khi khóa sổ. Doanh nghiệp có nhiều giao dịch nên đối chiếu hằng tuần để phát hiện sớm khoản thu chưa ghi.
Phí ngân hàng có cần hóa đơn không?
Ngân hàng thường cung cấp hóa đơn hoặc chứng từ cho phí dịch vụ. Hãy tải về cùng sao kê để hạch toán đầy đủ.
Không khớp được vì một khoản không rõ là gì thì làm sao?
Ghi khoản đó vào danh sách chờ xử lý, hỏi ngân hàng nội dung giao dịch và đối chiếu với chứng từ. Không ghi bút toán “chênh lệch” cho đủ số.
Có dùng Google Sheets được không?
Được. COUNTIFS, IF, ABS và TEXT đều có trên Google Sheets; chỉ nên giới hạn quyền truy cập vì file chứa số dư và giao dịch của doanh nghiệp.
