CÁCH LẬP BẢNG TỔNG HỢP NHẬP XUẤT TỒN KẾ TOÁN VÀ CÁCH ĐỐI CHIẾU CÔNG NỢ TRÊN EXCEL MỚI 2021

Bảng tổng hợp nhập xuất tồn được lập vào cuối kỳ kế toán, tùy theo các công ty mà kỳ kế toán có thể theo tháng, quý hay năm. Từ những năm xa xưa, các kế toán viên đều ghi chép bằng tay vào cuốn sổ to, dày và tổng hợp. Tuy nhiên, với công nghệ hiện đại ngày nay, công cụ Excel cho phép bạn lập bảng tổng hợp nhập xuất tồn bằng công cụ. Ngoài ra, Gitiho sẽ giới thiệu cho bạn cả cách đối chiếu công nợ bằng Excel.

Hướng dẫn cách lập bảng tổng hợp nhập xuất tồn trên Excel

Sau khi lập bảng tổng hợp, bạn sẽ được bảng tính như sau:

Để bắt đầu, chúng ta sẽ đánh số các mục như sau để bạn dễ hình dung được cách thực hiện nhé:

1. STT: Số thứ tự các loại vật tư
2. Mã số: Lấy từ phần “Mã số vật tư” tại Sổ chi tiết VL, DC, SP, HH
3. Tên vật liệu, dụng cụ, sản phẩm, hàng hóa: Ghi tên cụ thể của VL, DC, SP, HH
4. ĐVT: Đơn vị tính của từng loại vật tư tương ứng
5. Số lượng tồn đầu kỳ: Lấy số liệu phần số lượng “Tồn đầu kỳ” trong Sổ chi tiết VL, DC, SP, HH
6. Thành tiền tồn đầu kỳ: Lấy số liệu từ phần thành tiền “Tồn đầu kỳ” trong Sổ chi tiết VL, DC, SP, HH
7. Số lượng nhập trong kỳ: Tổng số lượng nhập trong kỳ trong Sổ chi tiết VL, DC, SP, HH
8. Thành tiền nhập trong kỳ: Tổng số tiền nhập trong kỳ trong Sổ chi tiết VL, DC, SP, HH
9. Số lượng xuất trong kỳ: Tổng số lượng xuất trong kỳ trong Sổ chi tiết VL, DC, SP, HH
10. Thành tiền xuất trong kỳ: Tổng số tiền xuất trong kỳ trong Sổ chi tiết VL, DC, SP, HH
11. Số lượng tồn cuối kỳ: Tổng số lượng tồn cuối kỳ trong Sổ chi tiết VL, DC, SP, HH
12. Thành tiền tồn cuối kỳ: Tổng số tiền tồn cuối kỳ trong Sổ chi tiết VL, DC, SP, HH
13. Dòng “Cộng”: Cộng số liệu từ các cột thành tiền: Cột 6, Cột 8, Cột 10, Cột 12.

Lưu ý: Gitiho đã thiết kế mẫu bảng tổng hợp xuất nhập tồn ở file đính kèm bài viết này. Các bạn tải về để sử dụng nhé.

Xem thêm: Chia sẻ mẫu bảng tổng hợp chi tiết vật liệu, dụng cụ trên Excel

Hướng dẫn cách đối chiếu công nợ tự động trên Excel

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

Biên bản đối chiếu công nợ bao gồm 3 phần chính:

1. Bên gửi bản đối chiếu công nợ (thường là bên thu nợ)
2. Bên nhận bản đối chiếu công nợ (thường là bên phải trả nợ)
3. 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…

Công dụng của biên bản đối chiếu công nợ là thu hồi nợ, chốt số nợ phải thu. Quy trình sẽ diễn ra như sau: Kế toán sẽ thu bản đối chiếu công nợ từ các bên đối tác sau đó thống nhất số nợ phải trả và thực hiện thanh toán cho bên chủ nợ. Việc này không cần lập thêm biên bản đối chiếu công nợ nào khác mà chỉ cần nhìn vào bảng đối chiếu công nợ mà cả 2 bên đang theo dõi.

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

Dựa vào 3 nguyên tắc đã giới thiệu ở trên, 1 biên bản đối chiếu công nợ cần có 3 thành phần sau:

1. Bên nhận bản đối chiếu công nợ: 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ừ)
2. 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.
3. Mẫu biên bản đối chiếu công nợ: Đây sẽ là nơi tổng hợp các thông tin của đối tượng có liên quan.

Vậy nên, file biên bản đối chiếu công nợ sẽ cần có 3 trang tính (sheets) trong cùng 1 file.

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

Các thao tác lập biên bản

Nhìn vào mẫu dưới đây, các bạn sẽ có cái nhìn khái quát về biên bản đối chiếu công nợ tự động trên Excel

Bước 1: Chọn thông tin khách hàng 

Bạn cần điền thông tin khách hàng vào mục ĐẠI DIỆN BÊN MUA căn cứ theo khách hàng được chọn bằng hàm INDEX + MATCH

• Với tên người đại diện 1, bạn sử dụng công thức:

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

Dữ liệu thông tin lấy từ trang tính KHÁCH HÀNG này

Bạn đọc có thể tải trong file mẫu của Gitiho đính kèm trong bài viết này.

• Với mục chức vụ của người đại diện 1 này, ta sử dụng công thức:

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

Bước 2: Tính một số chỉ tiêu trong bảng thông tin khách hàng

• 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 – 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)

Bước 3: Xác định số hợp đồng

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

• Sau đó nhấn tổ hợp lệnh Ctrl + Shift + Enter để kết thúc công thức. 

Lưu ý: Có thể ở các STT lớn, kết quả có thể sẽ trả lỗi #N/A do không tìm thấy kết quả, bạn đọc nên sử dụng thêm hàm bẫy lỗi IFERROR

Bước 4: Điền 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 IFERROR 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

Bước 5: Viết số tiền bằng chữ

Trong các bài blog của Gitiho, chúng mình có giới thiệu đến bạn đọc Cách lập công thức số tiền bằng chữ trên Excel
Bạn đọc hãy tìm hiểu để có thể tự động hóa phần này một cách nhanh chóng và dễ dàng nhé.

Xem thêm: Cách kết hợp hàm VLOOKUP cùng hàm IF trong Excel

Tổng kết

Như vậy, qua bài viết trên, bằng những kiến thức đơn giản trong Excel, các kế toán viên đã có thể tự lập bảng tổng hợp nhập xuất tồn và đối chiếu công nợ trong Excel,  phục vụ cho công việc một cách thuận tiện và hữu dụng. Đừng quên tham khảo các bài viết về cách sử dụng hàm VLOOKUP, hàm IF, hàm AND, OR,... và luyện tập với các bài tập Excel của Gitiho để có thể ứng dụng được nhiều hơn vào công việc hàng ngày, tối ưu hóa thời gian cũng như khiến công việc trôi chảy hơn bao giờ hết.