Hướng dẫn lập bảng kế hoạch thanh toán hóa đơn trong Excel nhanh chóng nhất

Trong các hợp đồng mua bán hoặc Invoice thường có một nội dung về điều khoản thanh toán như sau: 3/10 net 30

Vậy nội dung này được hiểu thế nào? Việc lập kế hoạch thanh toán cho những hóa đơn này như thế nào? 

Nội dung 3/10 net 30 hoặc 2/15 net 45 chính là Chiết khấu  thanh toán (CKTT). Nó thường nằm tại mục liên quan đến điều khoản, điều kiện thanh toán trong Hợp đồng, Invoice.

Được hiểu là: Nếu khách  hàng thanh toán sớm trước thời hạn thì sẽ được hưởng chiết khấu, với số tiền thanh toán ít hơn số phải trả ghi trên hợp đồng.

Cụ thể: Với CKTT là 3/10 net 30

Thời hạn thanh toán của hợp  đồng là trong vòng 30 ngày kể từ ngày ký hợp đồng, ngày lập Invoice

Thời hạn thanh toán được hưởng chiết khấu thanh toán là trong vòng 10 ngày kể từ ngày ký hợp đồng, ngày lập Invoice (thanh toán trong thời gian này sẽ được hưởng CKTT)

Mức CKTT là 3%

Chiết khấu thanh toán tính trên số tiền trả sớm, không phải toàn bộ số phải trả mà chỉ tính trên số trả trong thời gian hưởng chiết khấu.

• Số tiền trả trong thời gian hưởng CKTT = A
• Số tiền đã thanh toán theo hợp đồng = B = A / (1 – Mức CKTT)
• Số tiền trả sau thời gian hưởng CKTT = C
• Tổng số tiền đã thanh toán = B + C

Vì mỗi khoản A khi thanh toán được hưởng chiết khấu là X%, do đó giá trị thực của A trên hợp đồng sẽ lớn hơn A.

Cụ thể:

• Nếu theo hợp đồng phải trả là 1 triệu, được  hưởng CK là 3%, vậy nếu trong thời gian hưởng CK mà thanh toán hết thì sẽ được giảm số tiền là: 1.000.000 * 3% = 30.000
• Số tiền phải trả trong thời gian chiết khấu = 1.000.000 – 30.000 = 970.000
• Rút gọn công thức là 1.000.000 * (1 – 3%) = 1.000.000 * 97% = 970.000
• Vậy coi 970.000 là A, 1.000.0000 là B thì ta có A = B * (1-3%)
• Đổi công thức lại để tính B = A / (1-3%)

Như vậy chúng ta đã có thể hiểu được cách tính cho những khoản thanh toán được hưởng CKTT.

Giả thiết rằng chúng ta có 1 hóa đơn với các thông tin như sau:

Tổng số tiền phải trả C3 = 150 $

Điều kiện thanh toán C4 = 3/10 net 30

Ngày lập hóa đơn C8 = 08/03/2018

Từ các nội dung trên, chúng ta cần xác định cụ thể các nội dung về CKTT, thời hạn thanh toán, thời hạn thanh toán hưởng CKTT

Chiết khấu thanh toán được hưởng C7 = 3%

Thời gian thanh toán (C5) = 30 (ngày) => Thời hạn thanh toán C9 = C8 + 30 = 07/04/2018

Thời gian thanh toán được chiết khấu C6 = 10 (ngày) => Thời điểm thanh toán được nhận CKTT C10= C8  + 10  = 18/03/2018

Các nội dung cần được xác định rõ để có thể sử dụng trong việc tính toán.

Trình tự thực hiện như sau:

Bước 1: Xây dựng bố cục gồm

Các cột: Ngày thanh toán, Số tiền theo kế hoạch, số thực  nhận nếu bao gồm CKTT

Các hàng:

Các lần thanh toán (từ dòng 3 đến dòng 8)

Lần thanh toán cuối cùng (dòng 9)

Tổng cộng

Xác định số CKTT được hưởng thực  tế khi thực hiện theo kế hoạch

Bước 2: Tổng số thực  nhận luôn bằng giá trị hợp đồng (G10)

G10 = C3

Bước 3: Số tiền thực nhận ở lần thanh toán cuối cùng (G9)

G9 = Tổng số thực nhận – Tổng số thực nhận các đợt thanh toán trước đó

= G10 – SUM(G3:G8)

Bước 4: Tính số tiền kế hoạch trả cho lần trả cuối  cùng (F9)

Căn cứ theo ngày trả lần cuối cùng:

Nếu ngày trả lần cuối cùng nằm trong thời gian hưởng CKTT => F9 = G9 * (1 – C7) kết hợp điều kiện ngày trả nằm trong thời gian hưởng CKTT thì công thức là: IF(E9<=C10,G9*(1-C7),…)

Nếu ngày trả lần cuối nằm ngoài thời gian hưởng CKTT => F9 = G9

Xét trường hợp nếu không có ngày tại ô E9 => Không xác định được  ngày trả => Biện luận nếu không có ngày trả thì không thực hiện tính toán, chỉ tính khi E9 khác  0

Bước 5: Tính số tiền thực nhận tại mỗi lần thanh toán (từ G3 đến G8)

Xét tại G3

Căn cứ theo ngày trả tại lần đó

Nếu ngày trả lần đó nằm trong thời gian hưởng CKTT => G3 = F3 / (1 – C7) kết hợp điều kiện ngày trả nằm trong thời gian hưởng CKTT thì công thức là: IF(E3<=C10,F9.(1-C7),…) => Do có thể viết công thức xuống các lần trả tiếp theo => Cố định các điểm ở cột C

Nếu ngày trả lần cuối nằm ngoài thời gian hưởng CKTT => G3 = F3 hay hiểu cách khác là chiết khấu thanh toán = 0

Cách viết có thể hiểu là G3 = F3 / (1 – Nếu ngày trả trong thời gian hưởng CK thì lấy giá trị tại C7, còn ngoài thời gian hưởng CK thì lấy giá trị 0

Sử  dụng hàm làm tròn số nếu có số lẻ => Hàm ROUND(số cần làm tròn, làm tròn tới vị trí nào)

Sau khi hoàn thành công thức tại G3 có thể áp dụng tương tự cho các ô G4, G5… G8

Bước 6: Tính tổng số tiền kế hoạch trả (F10)

F10 = Tổng các lần trả = SUM(F3:F9)

Bước 7: Tính tổng CKTT được hưởng (F11)

Bởi việc trả nhiều lần có thể xảy ra việc không phải toàn bộ số tiền thanh toán được  hưởng CKTT, do đó có thể xác định lại xem thực tế được nhận bao nhiêu CKTT, % được hưởng là bao nhiêu

F11 = Tổng số thực nhận – Tổng số kế hoạch = G10-F10

Bước 8: Tính tỷ lệ CKTT được hưởng (G11)

G11 = Số CKTT được hưởng / Tổng số thực nhận = F11/G10

Có thể làm tròn số nếu số lẻ

Do đơn vị là % nên có thể dùng hàm Round với vị trí cần làm tròn > 2

Như vậy thông qua việc  phân tích cách tính và từng bước thực  hiện, chúng ta có thể lập được bảng kế hoạch thanh toán cho những hợp đồng có chứa CKTT. Việc  phân tích CKTT và lập kế hoạch Thanh toán sẽ giúp chúng ta có kế hoạch tài chính phù hợp nhất, hoặc có kế hoạch thu hồi công nợ cho những hóa đơn đã bán 1 cách tốt nhất. Chúc các bạn thực hiện thao tác thành công!