Trong bài viết này, mình sẽ hướng dẫn lập bảng (sổ) tổng hợp công nợ phải thu khách hàng trên Excel bằng việc sử dụng các hàm Excel cơ bản.
MỤC ĐÍCH VÀ VAI TRÒ CỦA BẢNG TỔNG HỢP CÔNG NỢ PHẢI THU KHÁCH HÀNG
Với mục đích chính là giúp đơn vị đưa ra cái nhìn tổng quan nhất về tình hình biến động công nợ của các đối tượng là khách hàng mà đơn vị có giao dịch phát sinh trong kỳ. Từ đó có thể đưa ra các quyết định về chính sách về thanh toán, thời hạn nợ, chiết khấu, khuyến mãi,… với với khách hàng.
Căn cứ để lập bảng này là sổ kế toán chi tiết công nợ phải thu khách hàng.
Mỗi dòng trên bảng tổng hợp công nợ phải thu đại diện cho 1 khách hàng của đơn vị. Hay nói cách khác, đơn vị có bao nhiêu khách hàng thì bảng (sổ) tổng hợp công nợ phải thu cũng cần có từng đó dòng.
MẪU BẢNG TỔNG HỢP CÔNG NỢ PHẢI THU KHÁCH HÀNG
Tuỳ vào nhu cầu thực tế quản lý của mỗi đơn vị mà bảng tổng hợp có thể khách đi một số chỉ tiêu. Tuy nhiên, nhìn chung bảng tổng hợp phải đảm bảo phản ánh được những thông tin như số dư đầu và cuối kỳ, số phát sinh Nợ, Có trong kỳ.
Trong bài viết này, Học Excel Online sử dụng mẫu bảng tổng hợp công nợ phải thu khách hàng nhu sau, Bạn có thể Download mẫu này ngay cuối bài viết.
HƯỚNG DẪN LẬP BẢNG TỔNG HỢP CÔNG NỢ PHẢI THU KHÁCH HÀNG TRÊN EXCEL
Nếu như bạn theo dõi thì trong một bài viết gần đây, mình cũng đã có bài viết hướng dẫn lập sổ kế toán chi tiết công nợ phải thu khách hàng.
Để cho thuận tiện cũng như dễ theo dõi thì mình xin lấy DATA của bài viết đó để làm ví dụ minh họa cho bài viết này. Cụ thể như sau:
Bảng DATA phát sinh của các khách hàng mà đơn vị có giao dịch trong kỳ (bảng nhập liệu hàng ngày)
Bảng DMKH (Danh mục khách hàng) kèm theo số dư đầu kỳ của từng khách hàng.
Về cách ghi bảng (sổ) tổng hợp công nợ phải thu khách hàng bạn có thể tham khảo bài viết sau:
HÀM EXCEL SỬ DỤNG
Bài viết này, mình sử một một số hàm Excel sau để lập bảng (sổ) tổng hợp công nợ phải thu khách hàng:
Để tính số dư đầu kỳ và số phát sinh trong kỳ ta sử dụng hàm VLOOKUP, SUMIFS
Để tối ưu và đảm bảo dữ liệu chính xác khi thay đổi khoảng thời gian thì ta sử dụng thêm các hàm MIN, MAX:
Để hiểu rõ hơn cách kết hợp các hàm này như nào thì ta hãy đến với phần cách thực hiện.
CÁCH THỰC HIỆN
Ở trong phần này, ta sẽ tiến hành sử dụng các hàm Excel với dữ liệu cũng như mẫu bảng như trên để lập bảng tổng hợp công nợ phải thu khách hàng.
Để công thức dễ theo dõi hơn thì mình có đặt tên cho các vùng dữ liệu trong Sheet DATA, cụ thể như sau:
TÍNH SỐ DƯ ĐẦU KỲ
Với mong muốn lập bảng tổng hợp công nợ này có thể linh hoạt thanh đổi số liệu khi bạn thay đổi khoảng thời gian nên số dư đầu kỳ ở đây là số dư tính tới trước thời điểm từ ngày (G7).
Công thức ở ô E12 (Nợ đầu kỳ) như sau:
=MAX(VLOOKUP(C12,DMKH!$A$3:$H$6,7,0) -VLOOKUP(C12,DMKH!$A$3:$H$6,8,0)+SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”<” & $G$7) – SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS, “<” &$G$7),0)
Và E13 (Có đầu kỳ):
=-MIN(VLOOKUP(C12,DMKH!$A$3:$H$6,7,0) -VLOOKUP(C12,DMKH!$A$3:$H$6,8,0)+SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”<” & $G$7) – SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS, “<” &$G$7),0)
Bạn có thể thấy, 2 công thức trên về cơ bản là giống nhau, thay vì hàm kết hợp hàm MAX để xác định số dư bên Nợ thì số dư bên Có bạn thay MAX = -MIN.
Tức là, khi bạn thay đổi ngày tháng trong ô G7 thì số dư này luôn luôn đúng là số dư tính tới thời điểm trước từ ngày (hay chính là NGAY_GS phải <G7 như công thức trên).
Để hiểu rõ hơn bạn có thể tham khảo cách sử dụng cách hàm đó kèm với file ngay dưới bài viết này, bạn tải về để nắm được chi tiết nhất.
TÍNH SỐ PHÁT SINH TRONG KỲ
Để tổng hợp số phát sinh trong kỳ của từng khách hàng thì ta có công thức như sau, sẽ ngắn hơn nhiều so với công thức tính số dư đầu kỳ đấy 
Số phát sinh bên Nợ (G12)
=SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”>=” & $G$7, NGAY_GS, “<=” & $G$8)
Số phát sinh bên Có (H12)
=SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS,”>=” & $G$7, NGAY_GS, “<=” & $G$8)
Tính tổng số tiền với điều kiện NGAY_GS nằm trong khoảng từ ngày – đến ngày
TÍNH SỐ DƯ CUỐI KỲ
Tới đây thì công việc dễ thở hơn nhiều rồi, với công thức đơn giản sau ta sẽ có được số dư cuối kỳ.
Công thức tại ô I12 (số dư cuối kỳ bên Nợ)
=MAX(E12+G12-F12-H12,0)
Công thức tại ô J12 (số dư cuối kỳ bên Có)
=-MIN(E12+G12-F12-H12,0)
Công việc tiếp theo sau khi đã hoàn thành được các công thức và tính ra được các chỉ tiêu trên bảng tổng hợp công nợ phải thu khách hàng là bạn chỉ FillDown các công thức đó xuống cho các dòng còn lại (mỗi dòng là một khách hàng) và SUM cho dòng cộng cuối bảng.
Kết quả là ta có bảng tổng hợp công nợ phải thu khách hàng như sau:
KẾT LUẬN
Mình hy vọng qua bài viết này bạn đã có thể nắm được cách vận dụng các hàm Excel để có thể lập ra được bảng (sổ) tổng hợp công nợ phải thu khách hàng một cách nhanh chóng, chính xác và đặc biệt là với cách này bạn hoàn toàn có thể lựa chọn khoảng thời gian muốn xem tổng hợp, số liệu sẽ được cập nhật chính xác cho bạn.
Về file đính kèm bài viết bạn có thể tải về ở link dưới đây để tham khảo thêm;
Download file đính kèm bài viết ở đây: Hướng dẫn lập bảng tổng hợp công nợ phải thu khách hàng trên Excel
DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.Cám ơn.
