Cách tính công nợ trên excel

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!

Doanh nghiệp ở các quy mô, mọi loại hình đều phải theo dõi bảng công nợ phải thu phải trả để nắm bắt tình hình hoạt động của doanh nghiệp. File excel quản lý công nợ là một trong những mẫu file phổ biến, kế toán nhiều doanh nghiệp đặc biệt là các doanh nghiệp nhỏ vẫn đang sử dụng để quản lý tình hình công nợ phải thu, phải trả của doanh nghiệp mình. Bài viết dưới đây sẽ tổng hợp các mẫu file theo dõi công nợ phổ biến, giúp kế toán thực hiện nhanh chóng và dễ dàng hơn.

Mục lục Hiện

1. Biểu mẫu bảng theo dõi công nợ, quản lý công nợ bằng Excel

1.1. Danh mục khách hàng/nhà cung cấp

1.2. Báo cáo công nợ chi tiết

1.3. Báo cáo công nợ tổng hợp

2. Một số vấn đề khi quản lý công nợ bằng excel miễn phí

3. Theo dõi công nợ thông minh bằng phần mềm kế toán

1. Biểu mẫu bảng theo dõi công nợ, quản lý công nợ bằng Excel

Khi tạo file excel quản lý công nợ để làm bảng theo dõi công nợ của doanh nghiệp, chúng ta cần tạo 3 sheet có liên kết với nhau bao gồm:

• Danh mục khách hàng/NCC: gồm các thông tin như mã theo dõi, mã số thuế, thông tin liên hệ
• Báo cáo công nợ chi tiết hay sổ chi tiết công nợ: cập nhật tất cả các giao dịch công nợ chi tiết của riêng từng khách hàng/nhà cung cấp
• Báo cáo công nợ tổng hợp: tổng hợp lại xem với mỗi khách hàng/NCC, tổng phát sinh và dư Nợ/Có cuối kỳ là bao nhiêu, làm căn cứ trả nợ hoặc thu hồi nợ.

1.1. Danh mục khách hàng/nhà cung cấp

Mục đầu tiên của biểu mẫu theo dõi công nợ bằng excel cần cung cấp đầy đủ các thông tin như:

• Mã khách hàng: Mã khách hàng được mã hóa theo tên khách hàng
• Tên khách hàng: Tên khách hàng đang sử dụng sản phẩm của doanh nghiệp
• Địa chỉ liên hệ, số điện thoại
• Mã số thuế
• Ghi chú nếu cần thiết
• …………….

Tại phần khai báo thông tin, nếu file cung cấp nhiều thông tin thì kế toán doanh nghiệp dễ theo dõi và quản lý chi tiết hơn.

Phần mềm kế toán online MISA AMIS hỗ trợ tổng hợp tự động công nợ phải thu theo từng khách hàng và cảnh báo nhắc nhở nhắc nhở, gửi email đối chiếu công nợ định kỳ.

>> DÙNG THỬ MIỄN PHÍ – PHẦN MỀM KẾ TOÁN ONLINE MISA AMIS

1.2. Báo cáo công nợ chi tiết

Báo cáo công nợ chi tiết hay sổ chi tiết công nợ, sẽ giúp doanh nghiệp nắm bắt được chi tiết công nợ từng khách hàng, khách hàng lấy hàng ngày nào, tổng thu bao nhiêu…

Báo cáo công nợ chi tiết cung cấp các thông tin như:

• Mã khách hàng
• Tên khách hàng, địa chỉ liên hệ: Tự động cập nhật theo mã khách hàng đã khai báo ở phần danh mục khách hàng
• Ngày/tháng/năm ghi nhận có công nợ còn phải thu
• Số chứng từ ghi nhận nợ
• Nội dung trong giao dịch bán.
• Phải thu: Số tiền còn nợ sau bán cần phải thanh toán của khách hàng
• Đã thu: Tổng số tiền đã thanh toán được tất cả hoặc một phần công nợ còn thiếu.
• Ghi chú nếu có

Thông thường chúng ta sẽ mở sổ chi tiết cho từng đối tượng, ví dụ đối với các tài khoản như tài khoản 331,131,141. Trong đó sổ chi tiết công nợ phải thu, phải trả người bán là những mẫu sổ chi tiết công nợ phổ biến nhất.

Các khoản công nợ được cập nhật kịp thời vào sổ chi tiết ngay khi phát sinh; Cuối tháng, kế toán công nợ sẽ tổng hợp lại để tính tổng công nợ với từng khách hàng, nhà cung cấp, sau đó đối chiếu số liệu 2 bên.

Vai trò của sổ chi tiết công nợ:

• Sổ chi tiết công nợ giúp theo dõi công nợ theo từng đối tượng cụ thể, riêng biệt, từ đó doanh nghiệp biết được công nợ của mình với từng đối tác ra sao để thanh toán/thu hồi.
• Hỗ trợ kiểm tra, rà soát lại dễ dàng hơn nếu phát hiện số liệu công nợ bị sai lệch.

Hiện nay, hầu hết các doanh nghiệp đều thực hiện công tác kế toán bằng phần mềm hoặc lập sổ trên các file quản lý công nợ bằng excel. File excel có lợi ích là không mất chi phí, đáp ứng được các yêu cầu cơ bản như tổng hợp công nợ. Tuy nhiên, kế toán sẽ mất khá nhiều thời gian để nhập liệu, đối chiếu và khó khăn khi theo dõi tuổi nợ, hạn nợ. Do đó, phần mềm kế toán là một giải pháp tiện lợi hơn đang được các doanh nghiệp quan tâm.

1.3. Báo cáo công nợ tổng hợp

Báo cáo công nợ tổng hợp là phần cuối cùng trong mẫu file excel quản lý công nợ và bán hàng. Cuối tháng dựa vào bảng này, kế toán doanh nghiệp sẽ chốt được công nợ phải thu, phải trả của từng khách hàng dựa trên tổng số lần nhập – xuất hàng.

Báo cáo tổng hợp công nợ phải trả bao gồm có các thông tin sau:

• Số thứ tự
• Các nội dung liên quan đến sản phẩm và khách hàng/ nhà cung cấp (tên gọi, mã hàng, cột phân loại khách hàng và nhà cung cấp)
• Các thông tin dữ liệu về công nợ phải trả như phải trả bắt đầu, phải trả đầu kỳ, phải trả tăng, phải trả giảm, phải trả cuối kỳ,…

> Tham khảo mẫu file excel công nợ dành cho kế toán doanh nghiệp TẠI ĐÂY.

Tìm hiểu thêm

Tải ngay mẫu bảng tổng hợp công nợ mới nhất

Mẫu biên bản xác nhận công nợ mới nhất – Kèm file tải về

Hạch toán bù trừ công nợ hai bên, ba bên như thế nào mới đúng?

2. Một số vấn đề khi quản lý công nợ bằng excel miễn phí

Excel là một trong những phần mềm phổ biến được nhiều doanh nghiệp, đặc biệt là các doanh nghiệp có quy mô nhỏ sử dụng trong quản lý công nợ bởi việc cài đặt đơn giản và sử dụng miễn phí. Người dùng sử dụng các hàm có sẵn trên excel để thiết lập các công thức tính, giúp việc tính toán trở nên đơn giản, chính xác hơn.

Tuy nhiên bên cạnh những ưu điểm, sử dụng file excel quản lý công nợ vẫn còn tồn tại không ít rủi ro và bất cập

• Kế toán nhập liệu thủ công bằng tay, tiêu tốn nhiều thời gian công sức và thường xảy ra sai sót

Đối với các kho hàng lớn, nhiều mã hàng hóa thì việc nhập liệu hàng hóa lên file excel khiến kế toán gặp nhiều sai sót. Mỗi lần nhầm lẫn, kế toán phải mất hàng giờ đồng hồ thậm chí cả ngày để rà soát lại.

• File excel quản lý dữ liệu và tính bảo mật kém

Nhược điểm thường thấy khi sử dụng Excel để quản lý kho hàng chính là tính năng bảo mật thấp. Khi sử dụng Excel, mọi thông tin đều được cập nhật và lưu trữ trên hệ thống máy tính với nhiều bộ phận sử dụng cho công việc nên doanh nghiệp khó có thể bảo mật các dữ liệu.

• Các hàm tính trên excel phức tạp, dễ bị nhầm lẫn nên không tránh được những sai sót xảy ra trong quá trình thống kê, báo cáo

Để xây dựng và thiết lập chính xác các công thức tính cho các mã hàng hóa khác nhau không phải là điều dễ dàng với các kế toán. Chưa kể nếu có xảy ra sai sót kế toán cũng mất hàng giờ tìm kiếm và khắc phục các sai sót. Quản lý chấm công cho nhân sự trong công ty cũng quan trong như việc quản lý công nợ khách hàng. Để đảm bảo việc chấm công chính xác, nhanh chóng, giảm các thủ tục không cần thiết hãy dùng thử AMIS chấm công.

Doanh nghiệp của bạn cần chuyển đổi số cho bộ phận Tài chính - Kế toán?Tham khảo ngay giải pháp MISA AMIS Kế Toán!

3. Theo dõi công nợ thông minh bằng phần mềm kế toán

Các doanh nghiệp hiện nay đều thực hiện công tác quản lý tài chính – kế toán bằng phần mềm hoặc lập sổ trên các file quản lý công nợ bằng excel. File excel có lợi ích là không mất chi phí, đáp ứng được các yêu cầu cơ bản như tổng hợp công nợ phải thu khách hàng, phải trả nhà cung cấp.

Tuy nhiên, kế toán sẽ mất khá nhiều thời gian để nhập liệu, đối chiếu và khó khăn khi theo dõi tuổi nợ, hạn nợ. Do đó, phần mềm kế toán là một giải pháp tiện lợi hơn đang được các doanh nghiệp quan tâm.

Chẳng hạn, phần mềm kế toán online MISA AMIS đã có thể hỗ trợ doanh nghiệp quản lý công nợ khách hàng, nhà cung cấp, đối tác một cách hiệu quả:

• Tổng hợp công nợ phải thu theo từng khách hàng, nhân viên bán hàng, bộ phận bán hàng, thị trường
• Tổng hợp công nợ phải thu theo hạn nợ: Nợ chưa đến hạn, Nợ quá hạn (1-30 ngày, 30- 60 ngày, …, nợ quá hạn khó đòi) giúp DN có kế hoạch thu hồi nợ hoặc chuẩn bị dòng tiền cho các khoản thanh toán sắp đến hạn.
• Cho phép xem nhanh công nợ trên danh sách để biết được tình trạng nợ của từng khách hàng, chi tiết từng chứng từ công nợ của khách hàng đó
• Tự động đối trừ công nợ, bù trừ công nợ giữa khách hàng vừa là khách hàng, vừa là nhà cung cấp của doanh nghiệp
• Nhắc nhở và gửi email đối chiếu công nợ định kỳ với khách hàng, nhà cung cấp để đảm bảo số liệu công nợ luôn khớp đúng.

Xem thêm video Hướng dẫn Quản lý công nợ trên MISA SME dưới đây:

Tham khảo ngay phần mềm kế toán online MISA AMIS để quản lý công tác tài chính – kế toán hiệu quả hơn.