Có thể bạn quan tâm: Excel 365 Có Gì Mới? Khám Phá Những Tính Năng Đột Phá Và Cải Tiến Mạnh Mẽ Trong Phiên Bản Office 365
1. Giới thiệu chung về việc theo dõi công nợ bằng Excel
Trong môi trường kinh doanh hiện đại, việc quản lý công nợ (công nợ phải thu và công nợ phải trả) là một trong những yếu tố then chốt quyết định sức khỏe tài chính của doanh nghiệp. Một hệ thống quản lý công nợ hiệu quả không chỉ giúp doanh nghiệp nắm bắt được dòng tiền, giảm thiểu rủi ro nợ xấu mà còn hỗ trợ trong việc đưa ra các quyết định chiến lược như mở rộng vốn, đàm phán tín dụng, hay cải thiện quan hệ khách hàng và nhà cung cấp.
Excel, với tính linh hoạt, khả năng tùy biến cao và chi phí thấp, đã và đang là công cụ được nhiều doanh nghiệp, đặc biệt là các doanh nghiệp vừa và nhỏ, lựa chọn để xây dựng hệ thống theo dõi công nợ. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ những khái niệm cơ bản, cách thiết kế bảng tính, công thức quan trọng, cho tới các mẫu báo cáo nâng cao, giúp bạn tối ưu hoá việc quản lý công nợ bằng Excel.
Có thể bạn quan tâm: Hàm Lấy Giờ Phút Giây Trong Excel: Cách Sử Dụng, Mẹo Thủ Và Ứng Dụng Thực Tế
2. Các khái niệm cơ bản cần nắm
| Thuật ngữ | Định nghĩa |
|---|---|
| Công nợ phải thu | Số tiền mà khách hàng còn nợ doanh nghiệp sau khi đã cung cấp hàng hoá, dịch vụ. |
| Công nợ phải trả | Số tiền doanh nghiệp phải trả cho nhà cung cấp, đối tác sau khi đã nhận hàng hoá, dịch vụ. |
| Ngày đáo hạn | Ngày cuối cùng mà khách hàng/đối tác phải thanh toán. |
| Số dư nợ | Giá trị còn lại chưa thanh toán của một khoản nợ cụ thể. |
| Tỷ lệ quay vòng công nợ | Thời gian trung bình mà doanh nghiệp cần để thu hồi công nợ (công nợ phải thu) hoặc thanh toán công nợ (công nợ phải trả). |
Việc hiểu rõ các khái niệm này sẽ giúp bạn thiết kế bảng tính sao cho phù hợp với nhu cầu thực tế và tránh những sai sót trong quá trình nhập liệu.
Có thể bạn quan tâm: Công Thức Excel Nâng Cao: Bí Kíp Tối Ưu Hóa Dữ Liệu Và Tăng Hiệu Suất Làm Việc
3. Lý do nên dùng Excel để theo dõi công nợ
- Chi phí thấp: Hầu hết các doanh nghiệp đã có sẵn Microsoft Office, không cần đầu tư phần mềm quản lý chuyên sâu.
- Tùy biến linh hoạt: Bạn có thể tạo các sheet, biểu đồ, công thức phù hợp với quy trình nội bộ.
- Dễ dàng chia sẻ: File Excel có thể được lưu trên SharePoint, OneDrive hoặc Google Drive để nhiều người cùng làm việc.
- Tích hợp với các công cụ khác: Dễ dàng nhập dữ liệu từ hệ thống ERP, phần mềm kế toán hoặc từ email.
- Khả năng tự động hoá: Sử dụng macro (VBA) hoặc Power Query để tự động cập nhật dữ liệu, giảm công việc nhập tay.
4. Cấu trúc cơ bản của file Excel quản lý công nợ
Một file Excel quản lý công nợ thường bao gồm các sheet chính sau:
| Sheet | Nội dung | Mô tả |
|---|---|---|
| DSKH | Danh sách khách hàng | Mã KH, Tên KH, Địa chỉ, Điện thoại, Email, Điều kiện thanh toán. |
| DSNCC | Danh sách nhà cung cấp | Mã NCC, Tên NCC, Địa chỉ, Điện thoại, Email, Điều kiện thanh toán. |
| Phải Thu | Chi tiết công nợ phải thu | Mã HD, Ngày, Mã KH, Số tiền, Ngày đáo hạn, Trạng thái (Đã thu/Chưa thu), Ghi chú. |
| Phải Trả | Chi tiết công nợ phải trả | Mã PO, Ngày, Mã NCC, Số tiền, Ngày đáo hạn, Trạng thái (Đã trả/Chưa trả), Ghi chú. |
| Báo cáo tổng hợp | Tổng hợp công nợ | Báo cáo công nợ theo KH/NCC, theo ngày, theo trạng thái, biểu đồ. |
| Định mức & Tỷ lệ | Các chỉ số tài chính | Tỷ lệ quay vòng, DSO (Days Sales Outstanding), DPO (Days Payable Outstanding). |
| Macro (nếu có) | Tự động hoá | Các macro nhập dữ liệu, gửi email nhắc nợ, tạo báo cáo PDF. |
4.1. Thiết kế sheet “DSKH”
| Cột | Tiêu đề | Ghi chú |
|---|---|---|
| A | Mã KH | Mã duy nhất, ví dụ: KH001 |
| B | Tên KH | Tên công ty hoặc cá nhân |
| C | Địa chỉ | Địa chỉ liên hệ |
| D | Điện thoại | Số điện thoại |
| E | Địa chỉ email | |
| F | Điều kiện TT | Ví dụ: 30 ngày, 45 ngày, trả trước… |
| G | Ghi chú | Thông tin bổ sung |
Lưu ý: Đặt Data Validation cho cột “Điều kiện TT” để người dùng chỉ chọn từ danh sách (30, 45, 60 ngày…).
4.2. Thiết kế sheet “Phải Thu”
| Cột | Tiêu đề | Công thức / Định dạng |
|---|---|---|
| A | Mã HD | Mã hoá đơn, định dạng “HD-YYYYMMDD-001” |
| B | Ngày | Định dạng ngày (dd/mm/yyyy) |
| C | Mã KH | Data Validation: danh sách từ sheet DSKH |
| D | Tên KH | =VLOOKUP(C2,DSKH!$A$2:$B$500,2,FALSE) (tự động lấy tên) |
| E | Số tiền | Định dạng tiền tệ |
| F | Ngày đáo hạn | =B2 + VLOOKUP(C2,DSKH!$A$2:$F$500,6,FALSE) |
| G | Trạng thái | Data Validation: “Chưa thu”, “Đã thu” |
| H | Số tiền đã thu | Khi trạng thái “Đã thu”, nhập số tiền thực tế |
| I | Số dư nợ | =E2 – H2 (đặt công thức) |
| J | Ghi chú | – |
4.3. Thiết kế sheet “Phải Trả”
Tương tự “Phải Thu”, thay đổi các trường:
- Mã PO (Purchase Order) thay cho Mã HD.
- Mã NCC thay cho Mã KH.
- Tên NCC lấy từ sheet DSNCC.
- Điều kiện thanh toán lấy từ DSNCC.
5. Các công thức quan trọng
5.1. Tính ngày đáo hạn tự động
=Ngày + VLOOKUP(MãKhách,DSKH!$A$2:$F$500,6,FALSE)
Trong công thức trên, cột 6 (F) của DSKH chứa Điều kiện thanh toán (số ngày). Khi thay đổi điều kiện, ngày đáo hạn tự động cập nhật.
5.2. Tính số dư nợ
=IF(@TrạngThái="Đã thu",@SốTiền-@SốTiềnĐãThu,@SốTiền)
Hoặc dùng công thức đơn giản:
=E2-H2
5.3. Đánh dấu công nợ quá hạn (Conditional Formatting)
- Chọn cột “Ngày đáo hạn”.
- Vào Conditional Formatting → New Rule → Use a formula.
- Nhập công thức:
=AND(@TrạngThái="Chưa thu",@Ngày đáo hạn<TODAY())
- Đặt định dạng nền màu đỏ. Khi ngày đáo hạn đã qua và chưa thu, ô sẽ hiển thị màu đỏ.
5.4. Tổng cộng công nợ phải thu / phải trả
Ở cuối sheet “Phải Thu”:
=SUMIF(G:G,"Chưa thu",I:I) // Tổng nợ chưa thu
=SUMIF(G:G,"Đã thu",I:I) // Tổng đã thu
Tương tự cho sheet “Phải Trả”.
5.5. Tính DSO (Days Sales Outstanding)
DSO = (Tổng công nợ phải thu / Doanh thu trung bình hàng ngày)
Giả sử doanh thu tháng hiện tại nằm ở ô B2 của sheet “Doanh thu”, và số ngày trong tháng là 30:
=SUMIF(PhảiThu!G:G,"Chưa thu",PhảiThu!I:I) / (B2/30)
Kết quả trả về số ngày trung bình doanh nghiệp cần để thu tiền.
5.6. Tính DPO (Days Payable Outstanding)
Tương tự DSO, nhưng với công nợ phải trả:
=SUMIF(PhảiTrả!G:G,"Chưa trả",PhảiTrả!I:I) / (C2/30)
Trong đó C2 là tổng chi phí mua hàng trong tháng.
6. Xây dựng báo cáo tổng hợp
Có thể bạn quan tâm: Excel Dùng Để Làm Gì? – Khám Phá Sức Mạnh Đa Năng Của Phần Mềm Bảng Tính Số 1 Thế Giới
6.1. Báo cáo công nợ theo khách hàng
Sử dụng PivotTable:
- Chọn dữ liệu trong sheet “Phải Thu”.
- Insert → PivotTable → New Worksheet.
- Kéo “Tên KH” vào Rows.
- Kéo “Số dư nợ” vào Values (đặt tính tổng).
- Thêm “Trạng thái” vào Filters để lọc “Chưa thu” hoặc “Đã thu”.
Kết quả là bảng tổng hợp số nợ còn lại của mỗi khách hàng.
6.2. Báo cáo công nợ quá hạn
- Thêm một cột “Quá hạn?” trong sheet “Phải Thu”:
=IF(AND(@TrạngThái="Chưa thu",@Ngày đáo hạn<TODAY()),"Có","Không")
- Tạo PivotTable, đặt “Quá hạn?” vào Filters và chọn “Có”.
- Kéo “Tên KH” và “Số dư nợ” vào Rows và Values.
Bạn sẽ có danh sách khách hàng nợ quá hạn và số tiền tương ứng.
6.3. Biểu đồ công nợ
- Chọn PivotTable đã tạo.
- Insert → Column Chart.
- Đặt tiêu đề: “Công nợ phải thu quá hạn theo khách hàng”.
Biểu đồ giúp quản lý nhanh chóng nhìn thấy khách hàng nào đang gây rủi ro cao.
7. Tự động hoá bằng Macro (VBA)
7.1. Gửi email nhắc nợ tự động
Sub SendReminder() Dim ws As Worksheet, rng As Range, i As Long Set ws = ThisWorkbook.Sheets("Phải Thu") Set rng = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp)) For i = 2 To rng.Rows.Count If ws.Cells(i, "G").Value = "Chưa thu" And ws.Cells(i, "F").Value < Date Then Dim mail As Object Set mail = CreateObject("Outlook.Application").CreateItem(0) With mail .To = ws.Cells(i, "D").Offset(0, 3).Value 'Giả sử email ở cột J .Subject = "Nhắc nhở thanh toán hoá đơn " & ws.Cells(i, "A").Value .Body = "Kính gửi " & ws.Cells(i, "D").Value & vbCrLf & _ "Số tiền: " & ws.Cells(i, "E").Value & vbCrLf & _ "Ngày đáo hạn: " & ws.Cells(i, "F").Value & vbCrLf & _ "Vui lòng thanh toán sớm nhất có thể." .Send End With End If Next i MsgBox "Đã gửi email nhắc nợ cho các khoản quá hạn."
End Sub
Lưu ý: Máy tính cần cài Outlook và cho phép macro chạy.
7.2. Tự động cập nhật ngày đáo hạn khi thay đổi điều kiện TT
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C:C")) Is Nothing Then 'Nếu thay đổi Mã KH Application.EnableEvents = False Dim rowNum As Long: rowNum = Target.Row Dim days As Long days = Application.WorksheetFunction.VLookup(Target.Value, _ Worksheets("DSKH").Range("A:F"), 6, False) Cells(rowNum, "F").Value = Cells(rowNum, "B").Value + days Application.EnableEvents = True End If
End Sub
Đặt macro này trong module của sheet “Phải Thu”. Khi bạn thay đổi Mã KH, ngày đáo hạn sẽ tự động tính lại.
8. Kiểm tra và bảo trì dữ liệu
- Kiểm tra trùng lặp: Dùng
Remove Duplicatestrên cột Mã HD/PO để tránh nhập trùng. - Xác thực dữ liệu: Áp dụng Data Validation cho các cột quan trọng (Mã KH, Mã NCC, Trạng thái) để hạn chế lỗi nhập.
- Sao lưu định kỳ: Đặt lịch sao lưu file Excel mỗi tuần hoặc sử dụng OneDrive để tự động lưu phiên bản.
- Kiểm tra công thức: Sử dụng
Formula Auditing → Show Formulasđể rà soát công thức trên toàn workbook. - Đánh dấu lỗi: Áp dụng Conditional Formatting để hiển thị các ô có giá trị âm (số dư nợ < 0) – có thể là lỗi nhập.
9. Mở rộng: Kết nối Excel với các hệ thống khác
9.1. Power Query
Power Query cho phép kéo dữ liệu từ:
- Database (SQL Server, MySQL): Dùng “Get Data → From Database”.
- Web API: Dùng “From Web” để lấy dữ liệu công nợ từ phần mềm ERP.
- CSV/Excel: Nhập dữ liệu định kỳ từ file xuất từ phần mềm kế toán.
Sau khi tải dữ liệu vào, bạn có thể thiết lập Refresh tự động mỗi ngày, giảm công việc nhập tay.
9.2. Power Pivot & DAX
Nếu dữ liệu lớn (> 100k dòng), sử dụng Power Pivot để tạo mô hình dữ liệu và dùng DAX để tính các chỉ số tài chính phức tạp như:
Total_Receivable = CALCULATE(SUM('Phải Thu'Số Tiền), 'Phải Thu'Trạng Thái="Chưa thu")
Overdue_Receivable = CALCULATE(SUM('Phải Thu'Số Tiền), FILTER('Phải Thu', 'Phải Thu'Trạng Thái="Chưa thu" && 'Phải Thu'Ngày đáo hạn < TODAY()))
9.3. Power BI
Xuất dữ liệu từ Excel lên Power BI để tạo dashboard thời gian thực, chia sẻ với ban lãnh đạo qua web. Các visual như Treemap cho công nợ theo khu vực, Line Chart cho xu hướng DSO/DPO là rất hữu ích.
10. Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #N/A trong VLOOKUP | Mã KH/NCC không tồn tại trong danh sách | Kiểm tra lại dữ liệu nguồn, dùng Data Validation để hạn chế nhập sai. |
| Ngày đáo hạn sai | Điều kiện thanh toán không đúng | Đảm bảo cột “Điều kiện TT” trong DSKH/DSNCC là số ngày, không có ký tự. |
| Tổng công nợ không khớp | Công thức SUMIF không bao gồm tất cả các hàng | Kiểm tra phạm vi dữ liệu (đảm bảo không có hàng trống giữa các dữ liệu). |
| Macro không chạy | Bảo mật macro bị chặn | Vào File → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros (có rủi ro). |
| Biểu đồ không cập nhật | PivotTable chưa refresh | Đặt PivotTable → Options → Refresh data when opening file. |
11. Quy trình làm việc mẫu
- Ngày 1-2: Nhập dữ liệu bán hàng (hóa đơn) vào sheet “Phải Thu”.
- Ngày 3: Kiểm tra công nợ quá hạn, thực hiện gửi email nhắc nợ (macro hoặc thủ công).
- Ngày cuối tháng: Tổng hợp báo cáo công nợ, tính DSO, DPO, gửi cho bộ phận tài chính.
- Hàng tuần: Kiểm tra dữ liệu nhập, loại bỏ trùng, backup file.
- Hàng tháng: Cập nhật dữ liệu mua hàng vào sheet “Phải Trả”, tính DPO, xem xét điều kiện thanh toán với nhà cung cấp.
12. Tips nâng cao để tối ưu hoá
- Sử dụng bảng (Table) thay vì dải ô: Khi bạn chuyển dải dữ liệu thành Table (
Ctrl+T), các công thức sẽ tự động mở rộng khi thêm dòng mới. - Dynamic Named Ranges: Đặt tên cho phạm vi dữ liệu để công thức dễ đọc, ví dụ
tblReceivablecho bảng “Phải Thu”. - Slicer: Thêm slicer cho PivotTable để lọc nhanh theo “Trạng thái”, “Quá hạn?”, “Tháng”.
- Dashboard trên một sheet: Tập hợp các biểu đồ, KPI (DSO, DPO, Tổng nợ) trên một sheet để ban giám đốc có thể xem nhanh.
- Sử dụng “Get & Transform”: Nếu bạn nhận dữ liệu từ nhiều nguồn (ERP, email), Power Query sẽ tự động hợp nhất và chuẩn hoá dữ liệu trước khi đưa vào bảng chính.
13. Kết luận
Quản lý công nợ là một công việc đòi hỏi tính chính xác, kịp thời và khả năng phân tích sâu. Excel, với tính năng mạnh mẽ như công thức, PivotTable, Power Query và khả năng mở rộng qua VBA, hoàn toàn đủ để xây dựng một hệ thống theo dõi công nợ toàn diện cho doanh nghiệp vừa và nhỏ. Bằng cách áp dụng các bước thiết kế bảng, công thức tính toán, báo cáo tổng hợp và tự động hoá quy trình như trong bài viết này, bạn sẽ:
- Giảm thiểu rủi ro nợ xấu.
- Nâng cao hiệu quả thu hồi công nợ và thanh toán cho nhà cung cấp.
- Cung cấp thông tin tài chính kịp thời cho quyết định chiến lược.
- Tiết kiệm thời gian và chi phí so với việc mua phần mềm quản lý công nợ đắt tiền.
Hãy bắt đầu triển khai ngay hôm nay, tùy chỉnh mẫu Excel sao cho phù hợp với quy trình và đặc thù ngành của doanh nghiệp bạn. Khi đã thành công với Excel, bạn có thể dần dần mở rộng sang Power BI hoặc một hệ thống ERP chuyên nghiệp hơn, nhưng nền tảng vững chắc luôn bắt đầu từ một file Excel được thiết kế đúng cách. Chúc bạn quản lý công nợ hiệu quả và doanh nghiệp phát triển bền vững!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.