Ở 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)

Bạn dùng file mà bị vướng ở bước nào? Bạn có thể đăng câu hỏi trong nhóm Vườn ươm nhà Nghề để cùng trao đổi và được giải đáp. Vào nhóm hỏi đáp
Sheet Tổng hợp của file Excel quản lý công nợ nha khoa: các con số chính, công nợ theo nhóm dịch vụ và biểu đồ
Sheet Tổng hợp: các con số chính và công nợ theo nhóm dịch vụ, tự cập nhật

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.

Sheet Danh mục: bảng giá dịch vụ nha khoa, hạn thanh toán, danh sách bệnh nhân và cài đặt
Bước 1: khai báo dịch vụ, hạn thanh toán và danh sách bệnh nhân

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.

Sheet Phiếu điều trị tự tính phải thu, đã thu, còn nợ, số ngày quá hạn và tô đỏ phiếu quá hạn
Bước 2: cột xanh lá bạn nhập, cột xanh navy tự tính, phiếu quá hạn lâu được tô đỏ

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.

Sheet Thu tiền ghi từng lần bệnh nhân thanh toán, kể cả trả góp nhiều đợt
Bước 3: mỗi lần thu tiền là một dòng, chọn mã phiếu từ danh sách

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ảng phân tích tuổi nợ: số phiếu và số tiền còn nợ theo từng nhóm quá hạn
Phân tích tuổi nợ: biết bao nhiêu tiền đang quá hạn lâu

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.

Sheet Theo bệnh nhân: công nợ từng bệnh nhân kèm số điện thoại và mức ưu tiên nhắc nợ
Bước 5: lọc mức ưu tiên "Cao" để có danh sách cần gọi nhắc nợ

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á

=VLOOKUP(F6,DM_DICHVU,5,0)

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

=ROUND(I6*J6*(1-K6),-3)

Số lượng × đơn giá × (1 − giảm giá), làm tròn tới hàng nghìn

Cộng tiền đã thu

=SUMIFS('Thu tiền'!$H$6:$H$505,'Thu tiền'!$D$6:$D$505,B6)

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

=IF(P6<=0,0,MAX(NGAY_BC-N6,0))

Phiếu đã trả đủ hoặc chưa đến hạn thì ra 0

Xếp nhóm tuổi nợ

=VLOOKUP(R6,BANG_TUOI_NO,2,TRUE)

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ụ

=SUMIFS(Còn nợ, Nhóm dịch vụ, "Phục hình", Còn nợ, ">0")

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.