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

09/04/2021

9269

Mục lục Hiển thị

Bảng tổng hợp công nợ phải thu luôn phải được theo dõi và cập nhật thường xuyên trong doanh nghiệp nhằm giúp nhà quản lý có thể nắm bắt được tình hình thu hồi công nợ một cách chính xác nhất. Trong bài viết này, Ketoan.vn sẽ hướng dẫn bạn đọc 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.

Bài tham khảo: 5 điều cần nắm rõ để trở thành kế toán công nợ phải thu chuyên nghiệp

Bước 1: Sheet DMKH

Tại đây các bạn nhập các thông tin cơ bản về khách hàng vào đây như mã số, tên khách hàng, địa chỉ, mã số thuế, tài khoản ngân hàng, số điện thoại, số dư nợ đầu kỳ, số dư có đầu kỳ.

Bước 2: Sheet DATA

Tại đây các bạn nhập các nghiệp vụ phát sinh trong kỳ vào đây. Các thông tin cần nhập đó là ngày, số phiếu, diễn giải, số tiền, tài khoản nợ, tài khoản có, đối tượng.

Bước 3: Sheet THPTKH

Tại đây ta sẽ tổng hợp dữ liệu từ sheet DMKH để lấy số dư đầu kỳ, lấy dữ liệu từ sheet DATA để lấy phát sinh nợ, có trong kỳ. Sau đó cộng đầu kỳ và phát sinh trong kỳ để ra được số dư cuối kỳ.

Tại cột Nợ đầu kỳ các bạn nhập công thức như sau:
=MAX(VLOOKUP(B11;DMKH!$A$3:$H$6;7;0) -VLOOKUP(B11;DMKH!$A$3:$H$6;8;0)+SUMIFS(SO_TIEN;CT_NO;B11;NGAY_GS;”<” & $F$6) – SUMIFS(SO_TIEN;CT_CO;B11;NGAY_GS; “<” &$F$6);0)
Tại cột Có đầu kỳ các bạn nhập công thức như sau:
=-MIN(VLOOKUP(B11;DMKH!$A$3:$H$6;7;0) -VLOOKUP(B11;DMKH!$A$3:$H$6;8;0)+SUMIFS(SO_TIEN;CT_NO;B11;NGAY_GS;”<” & $F$6) – SUMIFS(SO_TIEN;CT_CO;B11;NGAY_GS; “<” &$F$6);0)
Tại cột phát sinh Nợ các bạn nhập công thức như sau:
=SUMIFS(SO_TIEN;CT_NO;B11;NGAY_GS;”>=” & $F$6; NGAY_GS; “<=” & $F$7)
Tại cột phát sinh Có các bạn nhập công thức như sau:
=SUMIFS(SO_TIEN;CT_CO;B11;NGAY_GS;”>=” & $F$6; NGAY_GS; “<=” & $F$7)
Tại cột Nợ cuối kỳ các bạn nhập công thức sau:
=MAX(D11+F11-E11-G11;0)
Tại cột Có cuối kỳ các bạn nhập công thức sau:
=-MIN(D11+F11-E11-G11;0)

Bước 4: Sheet SCT_131

Tại đây ta sẽ tạo sổ chi tiết của từng đối tượng. Ta sẽ tổng hợp dữ liệu từ sheet DMKH để lấy số dư đầu kỳ, lấy dữ liệu từ sheet DATA để lấy phát sinh nợ, có trong kỳ. Sau đó cộng đầu kỳ và phát sinh trong kỳ để ra được số dư cuối kỳ của đối tượng đó.

Tại cột Nợ đầu kỳ các bạn nhập công thức như sau:
=MAX(VLOOKUP(G10;DMKH!A3:H6;7;0) + SUMIFS(DATA!E3:E251;DATA!A3:A251;”<“&G8;DATA!H3:H251;G10)-
VLOOKUP(G10;DMKH!A3:H6;8;0) – SUMIFS(DATA!E3:E251;DATA!A3:A251;”<“&G8;DATA!G3:G251;G10);0)
Tại cột Có đầu kỳ các bạn nhập công thức như sau:
=-MIN(VLOOKUP(G10;DMKH!A3:H6;7;0) + SUMIFS(DATA!E3:E251;DATA!A3:A251;”<“&G8;DATA!H3:H251;G10)- VLOOKUP(G10;DMKH!A3:H6;8;0) – SUMIFS(DATA!E3:E251;DATA!A3:A251;”<“&G8;DATA!G3:G251;G10);0)
Tại cột phát sinh Nợ các bạn nhập công thức như sau:
=IF(D227=””;0;IF(DATA!H213=$G$10;DATA!E213;0))
Tại cột phát sinh Có các bạn nhập công thức như sau:
=IF(D227=””;0;IF(DATA!I213=$G$10;DATA!E213;0))
Tại cột Nợ cuối kỳ các bạn nhập công thức sau:
=MAX(I15+G275-J15 – H275;0)
Tại cột Có cuối kỳ các bạn nhập công thức sau:
=-MIN(I15+G275-J15 – H275;0)

Các bạn có thể tải về file Excel mẫu TẠI ĐÂY.

Như vậy, bài viết trên đã hướng dẫn các bạn những bước lập bảng tổng hợp công nợ phải thu khách hàng trên Excel. Hy vọng bài viết sẽ hữu ích với các bạn trong quá trình làm việc. Chúc các bạn thành công!