Các Bước Lập Bảng Tổng Hợp Công Nợ Phải Thu Khách Hàng Trên Excel

Trong bài viết này, Học Excel Online 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, Học Excel Online 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.

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

Hướng dẫn làm sổ kế toán chi tiết thanh toán với khách hàng trong excel

Để 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:

Cách ghi bảng tổng hợp công nợ phải thu khách hàng

Hàm Excel sử dụng

Bài viết này, Học Excel Online 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

Hàm vlookup trong excel, hướng dẫn sử dụng chi tiết và có ví dụ cụ thể

Cách sử dụng hàm sumifs và sumif với nhiều điều kiện – một số ví dụ công thức

Để 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:

cách sử dụng hàm min so sánh hai hoặc nhiều giá trị trong excel

Hướng dẫn hàm max so sánh hai hoặc nhiều giá trị trong excel

Để 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

Học Excel Online 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

Cách ghi bảng tổng hợp công nợ phải thu khách hàng

Hướng dẫn làm sổ kế toán chi tiết thanh toán với khách hàng trong excel

Hướng dẫn lập bảng tổng hợp các tài khoản ngân hàng trên excel

Hướng dẫn cách làm sổ kế toán chi tiết tiền gửi ngân hàng trên excel

Hướng dẫn làm sổ quỹ tiền mặt, sổ kế toán chi tiết tiền mặt trên Excel

Hướng dẫn làm phiếu nhập kho trên Excel.

hướng dẫn làm phiếu xuất kho trên excel.

Hướng dẫn lập Sổ Nhật ký chung bằng hàm “HeoSQL”

Hướng dẫn lập Sổ Cái bằng hàm “HeoSQL”