Hàm Excel tính tuổi nợ và số dư bình quân
Kế toán công nợ thường phải tính tuổi số dư khoản
phải thu của khách hàng để biết khoản nào cần được
thu trước, ngoài ra còn phải tính số dư bình quân của
các khoản phải thu này nhằm "nâng cao chất lượng
quản lý tài chính".
Nếu không có phần mềm kế toán cung cấp sẵn các
chức năng này hoặc phải tác nghiệp bằng Excel một
cách thủ công thì tôi đảm bảo bạn sẽ cảm thấy cuộc
đời của kế toán công nợ không khác gì "cửu vạn".
Hai hàm Excel dưới đây có thể giúp bạn nhanh chóng
lấy lại cảm giác yêu đời.
Ví dụ chúng ta có 1 sheet dữ liệu của khách hàng như
hình dưới
• Cột thứ nhất là ngày tháng giao dịch với khách hàng
(sắp xếp theo ngày tăng dần)
• Cột thứ hai là số tiền phải thu của khách hàng (ghi
nợ)
• Cột thứ ba là số đã thu được của khách hàng (ghi
có)
• Cột thứ tư là cột thứ 2 trừ cột thứ 3
• Cột thứ năm là cân đối còn phải thu sau từng giao
dịch, cột 6 là ghi chú
Việc tính toán chỉ cần cột 1 đến cột 4.
Tạo hàm
1. Function OldOfDebt(mRange As Range, toDate As
Date) As Double
Hàm này trả về tuổi của khoản phải thu theo ngày,
bằng cách giải đáp số dư còn phải thu tại ngày cuối
cùng là các khoản phải thu của các ngày nào theo
nguyên tắc khoản đã thu được sẽ là thanh toán cho
khoản phải thu đến trước, sau đó tính ra số ngày theo
trọng số của từng khoản đối với số dư.
Hàm có hai tham số, thứ nhất mRange chính là vùng
tính toán, trong ví dụ là A2:D13; thứ hai toDate là
ngày để xác định tuổi (toDate phải lớn hơn ngày cuối
cùng phát sinh giao dịch với khách hàng), trong hình
là C19. Theo ví dụ hàm tính tuổi của khoản phải thu
có giá trị 191.000 là 146,36 ngày trả về tại ô E19.
2. Function AvgBalance(mRange As Range, toDate
As Date) As Double
Hàm này có tham số y hệt hàm trên, trả về số dư còn
phải thu trung bình của các khách hàng theo tỷ trọng
về thời gian. Trong hình, hàm tính ra số dư bình quân
trả về ô E21 là 106.791 với mRange là A2:D13 và
toDate là 31/12/2005. (Bạn có thể tính được tổn thất
do khách hàng này chiếm dụng bằng Hàm x lãi suất
trong khoảng thời gian A2 đến A13).
Mã nguồn
Public Function OldOfDebt(mRange As Range,
toDate As Date) As Double
Dim rDate As Range Cot ngay
Dim rDebit As Range Cot ghi no
Dim rCredit As Range Cot ghi co
Dim mPaid As Double Tong so da thu duoc
Dim mClose As Double So du cuoi tai ngay toDate
Dim mAccDebit As Double Debit cong don
Dim thisAmount As Double
Dim thisDate As Double