Ở phòng khám nha khoa, nhiều dịch vụ như niềng răng, cấy ghép Implant, bọc răng sứ cho phép bệnh nhân thanh toán nhiều đợt. Sau vài tháng, kế toán thường khó trả lời nhanh: bệnh nhân nào còn nợ, nợ bao nhiêu, quá hạn bao lâu, và dịch vụ nào đang bị nợ nhiều nhất.
Nghề Excel đã soạn một file Excel quản lý công nợ cho phòng khám nha khoa. Bạn chỉ cần nhập phiếu điều trị và các lần thu tiền, file tự tính công nợ từng phiếu, tự tổng hợp theo loại dịch vụ, tuổi nợ và lập danh sách bệnh nhân cần nhắc nợ.
Tải file mẫu: File mẫu Excel quản lý công nợ phòng khám nha khoa (OneDrive)

File Excel quản lý công nợ nha khoa gồm những gì?
Sheet | Bạn làm gì ở sheet này |
|---|---|
Hướng dẫn | Đọc cách dùng trong 5 bước và quy ước màu. |
Tổng hợp | Xem các con số chính, công nợ theo nhóm dịch vụ, theo từng dịch vụ và phân tích tuổi nợ. Bạn không cần nhập gì. |
Theo bệnh nhân | Xem công nợ từng bệnh nhân kèm số điện thoại, lọc danh sách cần nhắc nợ. |
Phiếu điều trị | Nhập mỗi lần điều trị một dòng. |
Thu tiền | Nhập mỗi lần bệnh nhân thanh toán một dòng. |
Danh mục | Khai báo dịch vụ, bảng giá, hạn thanh toán, danh sách bệnh nhân và các thông số cài đặt. |
File dùng một quy ước màu thống nhất: cột có tiêu đề xanh lá là cột bạn nhập, cột có tiêu đề xanh navy là cột tự tính bằng công thức. Nhờ vậy người mới nhận việc cũng biết ngay mình cần nhập ở đâu.
Bạn dùng file mẫu theo 5 bước
Bước 1: Bạn khai báo dịch vụ và bệnh nhân ở sheet Danh mục
Bạn nhập mã dịch vụ, tên dịch vụ, nhóm dịch vụ, đơn giá và hạn thanh toán. Hạn thanh toán là số ngày bệnh nhân được trả sau kể từ ngày điều trị, ví dụ niềng răng 180 ngày, Implant 90 ngày. Dịch vụ trả ngay thì bạn để 0. Bạn cũng thêm bệnh nhân mới vào danh sách bệnh nhân kèm số điện thoại.

Bước 2: Bạn nhập mỗi lần điều trị một dòng ở sheet Phiếu điều trị
Bạn nhập mã phiếu, ngày điều trị, chọn mã bệnh nhân và mã dịch vụ từ danh sách thả xuống, rồi nhập số lượng và giảm giá. File tự điền tên bệnh nhân, tên dịch vụ, đơn giá, tính số tiền phải thu, ngày đến hạn, số tiền đã thu, còn nợ, trạng thái và số ngày quá hạn.

Phiếu nào quá hạn lâu hơn số ngày bạn cài đặt (mặc định 60 ngày) sẽ tự tô đỏ, nên bạn lướt qua là thấy ngay phiếu cần xử lý.
Bước 3: Bạn nhập mỗi lần thu tiền một dòng ở sheet Thu tiền
Mỗi lần bệnh nhân thanh toán, bạn nhập ngày thu, số phiếu thu, chọn mã phiếu điều trị, hình thức thanh toán và số tiền. Bệnh nhân trả góp nhiều đợt thì bạn nhập nhiều dòng cùng mã phiếu. File tự cộng các lần thu vào cột Đã thu của phiếu tương ứng.

Bước 4: Bạn xem báo cáo ở sheet Tổng hợp
Sheet Tổng hợp tự cập nhật sau mỗi lần bạn nhập phiếu hoặc thu tiền. Bạn xem được tổng phải thu, đã thu, còn nợ, số tiền quá hạn, công nợ theo nhóm dịch vụ (kèm biểu đồ), theo từng dịch vụ và bảng phân tích tuổi nợ.

Bước 5: Bạn lấy danh sách nhắc nợ ở sheet Theo bệnh nhân
Sheet này liệt kê công nợ của từng bệnh nhân kèm số điện thoại. Bạn lọc cột Mức ưu tiên nhắc nợ để lấy danh sách cần gọi: "Cao" là bệnh nhân có tiền quá hạn lớn hơn ngưỡng bạn cài đặt, "Trung bình" là có tiền quá hạn nhưng chưa tới ngưỡng.

File tự tính công nợ bằng những công thức nào?
File chỉ dùng các hàm Excel phổ biến, không dùng macro, nên bạn mở ra là xem được từng công thức và tự sửa khi cần:
Việc | Công thức | Giải thích |
|---|---|---|
Lấy đơn giá |
| Tìm mã dịch vụ trong bảng danh mục và lấy đơn giá ở cột thứ 5 |
Tính phải thu |
| Số lượng × đơn giá × (1 − giảm giá), làm tròn tới hàng nghìn |
Cộng tiền đã thu |
| Cộng mọi lần thu có cùng mã phiếu, nên trả góp bao nhiêu đợt cũng đúng |
Đếm ngày quá hạn |
| Phiếu đã trả đủ hoặc chưa đến hạn thì ra 0 |
Xếp nhóm tuổi nợ |
| Tra cứu gần đúng theo các mốc 0, 1, 31, 61, 91 ngày |
Tổng hợp theo nhóm dịch vụ |
| Cộng số tiền còn nợ của các phiếu thuộc nhóm đó |
DM_DICHVU, NGAY_BC, BANG_TUOI_NO là các tên vùng (Named Range) đặt sẵn trong file, giúp công thức dễ đọc hơn so với địa chỉ ô.
Bạn tùy chỉnh file cho phòng khám của mình như thế nào?
Bạn đổi bảng giá và hạn thanh toán ở sheet Danh mục theo chính sách của phòng khám. Mọi phiếu dùng mã dịch vụ đó sẽ tự cập nhật.
Bạn thêm nhóm dịch vụ mới ở mục Nhóm dịch vụ trong sheet Danh mục. Bảng tổng hợp theo nhóm tự hiện thêm dòng.
Bạn chỉnh ngưỡng nhắc nợ và số ngày bắt đầu tô đỏ ở mục Cài đặt.
Bạn xem công nợ tại một ngày bất kỳ bằng cách gõ ngày đó vào ô Ngày báo cáo. Ô này mặc định là ngày hôm nay.
Bạn xóa dữ liệu mẫu trước khi dùng thật: file có sẵn 15 phiếu, 17 lần thu, 8 bệnh nhân giả định. Bạn chỉ xóa nội dung ở các cột tiêu đề xanh lá, không xóa dòng và không xóa cột.
Những lưu ý khi dùng file
Bạn không gõ đè lên các cột tiêu đề xanh navy. Các cột này chứa công thức, gõ đè sẽ làm sai số liệu tổng hợp.
File chứa sẵn công thức cho 300 phiếu điều trị, 500 lần thu tiền và 300 bệnh nhân. Khi cần thêm, bạn chọn dòng cuối của bảng và kéo công thức xuống.
Bạn nhập mã phiếu không trùng nhau. Cột Đã thu cộng tiền theo mã phiếu, nên hai phiếu trùng mã sẽ bị cộng lẫn tiền.
File chạy trên Excel 2010 trở lên và không có macro.
Tải file mẫu tại đây: File mẫu Excel quản lý công nợ phòng khám nha khoa (OneDrive)
Bạn muốn tự xây được những file như thế này?
File mẫu trên chỉ dùng các hàm quen thuộc: VLOOKUP, SUMIFS, COUNTIFS, IF, ROUND, cùng Data Validation và Conditional Formatting. Khi nắm chắc nhóm hàm này, bạn có thể tự xây file quản lý công nợ, kho, chấm công cho đúng nghiệp vụ của nơi mình làm. Bạn có thể xem lộ trình học các nhóm hàm này trong bài Lộ trình học Excel từ cơ bản đến nâng cao.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.