Phân tuổi nợ công nợ 0-30 / 31-60 / 61-90 / trên 90

Trung cấpKế toán, công nợ & thuế6 bước

Đầu bài

Sổ công nợ có ngày hóa đơn và số tiền còn nợ. Cần chia dư nợ vào các nhóm tuổi nợ, tổng mỗi nhóm, và bôi đỏ khoản quá 90 ngày.

Các bước làm

1
Tính số ngày quá hạn
=TODAY() - ngày_hóa_đơn (định dạng ô là General để ra số ngày, không phải ngày).
2
Gắn nhóm tuổi nợ
Bảng ngưỡng 0/31/61/91 kèm nhãn, =VLOOKUP(số_ngày, bảng, 2, TRUE) — gọn hơn IF lồng bốn tầng.
3
Tổng dư nợ mỗi nhóm
=SUMIFS(dư_nợ, cột_nhóm, "0-30") cho từng nhóm, khoá vùng F4. Cộng bốn nhóm phải bằng tổng dư nợ (Alt+=).
4
Dư nợ theo từng khách
=SUMIFS(dư_nợ, mã_kh, kh) để biết ai đang nợ nhiều nhất trong nhóm quá hạn.
5
So với hạn mức tín dụng
=IF(dư_nợ_kh > VLOOKUP(kh, bảng_hạn_mức, 2, FALSE), "Vượt", "") — cảnh báo khách vượt trần.
6
Bôi đỏ nhóm quá 90
Conditional Formatting: =$số_ngày>90 tô đỏ cả dòng để thu hồi trước.

TODAY() đổi mỗi ngày mở file nên số ngày nhích dần — muốn đóng băng ngày chốt thì gõ cứng ngày bằng Ctrl+; vào một ô rồi trỏ công thức vào ô đó.