Công thức Defect Aging trong Excel

Biên dịch báo cáo lão hóa trong Excel cho bất kỳ danh sách hóa đơn nào. Chỉ cần sao chép và dán danh sách hóa đơn của bạn vào mẫu và nhập ngày sao kê thích hợp. Phân tích độ tuổi có thể được biên soạn cho mỗi tài khoản khách hàng cá nhân hoặc tổng cộng và bạn có thể sử dụng ngày cuối tháng hoặc bất kỳ ngày nào khác trong tháng

• Bảng liệt kê hóa đơn hoàn toàn có thể tùy chỉnh để dễ sử dụng
• Ngày sao kê do người dùng xác định & 360 ngày lão hóa hàng tháng
• Chứa tính toán lão hóa cuối tháng và ngày trong tháng
• Phân tích độ tuổi cho tất cả khách hàng hoặc một tài khoản khách hàng

Ảnh chụp màn hình

Vật mẫu

71KB

Sự thử nghiệm

85KB

Cách sử dụng mẫu Phân tích tuổi hóa đơn

Tải xuống bản mẫu hoặc bản dùng thử khi xem hướng dẫn này

Mẫu này cho phép người dùng biên dịch phân tích độ tuổi từ danh sách hóa đơn dựa trên bất kỳ ngày sao kê nào do người dùng xác định. Phân tích độ tuổi có thể được tổng hợp cho tất cả các tài khoản khách hàng hoặc có thể chỉ định tham chiếu tài khoản khách hàng cụ thể để đưa một tài khoản khách hàng vào tính toán phân tích độ tuổi

Ghi chú. Mẫu này là giải pháp lý tưởng để biên soạn phân tích độ tuổi từ bất kỳ danh sách hóa đơn chưa thanh toán nào. Tham khảo các mẫu Hóa đơn thuế & Người nợ của chúng tôi nếu bạn yêu cầu một giải pháp tạo hóa đơn thuế tự động & báo cáo khách hàng, đồng thời cho phép bạn phân tích tất cả các tài khoản khách hàng của mình

Các trang tính sau được bao gồm trong mẫu này.
Tuổi tác - xác định ngày sao kê phù hợp để hiển thị phân tích tuổi tác cho danh sách hóa đơn được đưa vào trang tính Hóa đơn. Bạn cũng có thể nhập hoặc sao chép bất kỳ tham chiếu tài khoản khách hàng nào trong ô D3 để biên dịch phân tích độ tuổi chỉ cho tài khoản khách hàng được chỉ định.
Hóa đơn - sao chép bất kỳ danh sách hóa đơn nào vào bảng Excel trên trang tính này để bao gồm các hóa đơn thích hợp trong tính toán phân tích tuổi. Trang tính này chứa 5 cột mặc định nhưng bạn có thể thêm bao nhiêu cột bổ sung theo yêu cầu và sắp xếp các cột trên trang tính này theo bố cục của danh sách hóa đơn của bạn.

hóa đơn

Trước khi bạn sao chép hóa đơn của mình vào trang tính Hóa đơn, chúng tôi khuyên bạn nên sắp xếp lại các cột mặc định trên trang tính này để dễ dàng sao chép danh sách hóa đơn của bạn vào mẫu. Bạn có thể chèn các cột mới hoặc di chuyển các cột hiện có đến các vị trí thích hợp, nhưng điều bắt buộc là bạn không được xóa bất kỳ cột mặc định nào

Trang tính Hóa đơn chứa các cột mặc định sau.
Số hóa đơn - tất cả các hóa đơn chưa thanh toán phải được đưa vào danh sách hóa đơn mà bạn sao chép vào bảng Excel trên trang tính này và số hóa đơn có thể ở bất kỳ định dạng nào.
Ngày lập hóa đơn - ngày lập hóa đơn trong phạm vi dữ liệu của bạn phải ở cùng định dạng với cài đặt ngày khu vực được xác định trong Bảng điều khiển hệ thống. Nếu không đúng như vậy, mẫu sẽ không hoạt động chính xác và bạn sẽ phải chuyển đổi ngày sang định dạng ngày thích hợp trước khi đưa dữ liệu vào cột này.
Tham chiếu tài khoản khách hàng - cột này được sử dụng để tính toán phân tích độ tuổi cho tài khoản khách hàng được chỉ định trong ô D3 trên trang tính Độ tuổi. Do đó, nó phải là một tham chiếu duy nhất có thể được sử dụng để xác định một tài khoản khách hàng, ví dụ: số tài khoản khách hàng hoặc thậm chí tên thanh toán đầy đủ của khách hàng.
Số tiền hóa đơn - cột này phải bao gồm tổng giá trị hóa đơn đã bao gồm thuế bán hàng. Cột Số tiền Hóa đơn phải chứa các giá trị phải được sử dụng trong tính toán phân tích tuổi trên trang tính Tuổi.
Giá trị lão hóa - cột này chứa một công thức sẽ tự động được sao chép vào tất cả các hàng có trong bảng Excel. Giá trị lão hóa được xác định dựa trên ngày sao kê được chỉ định trong ô B3 trên trang tính lão hóa và các giá trị nằm trong khoảng từ 0 đến 360.

Ghi chú. Nếu bạn không yêu cầu đưa vào Giá trị lão hóa cho từng hóa đơn riêng lẻ, bạn có thể xóa cột này một cách an toàn. Tuy nhiên, cột Giá trị lão hóa có thể được sử dụng hiệu quả làm cột bộ lọc nếu bạn chỉ muốn xem các hóa đơn tạo thành một phần của một trong các tổng số nhóm lão hóa trên trang tính lão hóa. Điều này có thể được thực hiện với tính năng Bộ lọc tự động bằng cách chỉ cần nhấp vào mũi tên lựa chọn bên cạnh tiêu đề cột và chọn giá trị lão hóa phù hợp mà bạn muốn làm cơ sở cho bộ lọc.

Tổng giá trị hóa đơn của tất cả các hóa đơn được bao gồm trên trang Hóa đơn được hiển thị bên dưới tiêu đề trang. Nếu bạn sử dụng tính năng Lọc tự động để chỉ hiển thị hóa đơn cho một khách hàng cụ thể hoặc giá trị cũ, thì tổng số này sẽ tự động được tính toán lại để chỉ bao gồm các hóa đơn đã lọc

Định nghĩa phân tích tuổi

Phân tích tuổi trên trang tính Lão hóa được tính toán dựa trên ngày sao kê được xác định trong ô B3. Tất cả các hóa đơn trên trang Hóa đơn cũng được gán một giá trị cũ dựa trên ngày được chỉ định trong ô này - các giá trị cũ nằm trong khoảng từ 0 đến 360 và do đó bao gồm cả năm. Tất cả hóa đơn có ngày trong năm trước được bao gồm trong giá trị cũ 360

Phân tích độ tuổi có thể được tổng hợp dựa trên bất kỳ ngày sao kê nào. Nếu bạn nhập bất kỳ ngày nào không phải là ngày cuối tháng, các nhóm lão hóa được xác định dựa trên thành phần ngày được bao gồm trong ngày đã chỉ định. Nếu bạn nhập ngày cuối tháng, quá trình lão hóa được tổng hợp dựa trên các tháng theo lịch

Ví dụ. Nếu ngày sao kê được chỉ định là ngày 27 tháng 1 năm 2010, tất cả hóa đơn từ ngày 28 tháng 12 năm 2009 đến ngày 27 tháng 1 năm 2010 được bao gồm trong nhóm cũ 0 [giai đoạn hiện tại], tất cả hóa đơn từ ngày 28 tháng 11 năm 2009 đến ngày

Nếu ngày sao kê được chỉ định là ngày 28 tháng 2 năm 2010 [ngày cuối tháng], các giá trị cũ được xác định dựa trên các tháng dương lịch. Ví dụ. tất cả hóa đơn từ ngày 1 tháng 2 năm 2010 đến ngày 28 tháng 2 năm 2010 đều được bao gồm trong nhóm cũ 0, tất cả hóa đơn ngày từ ngày 1 tháng 1 năm 2010 đến ngày 31 tháng 1 năm 2010 đều được bao gồm trong nhóm cũ 30, v.v.

Các cột Ngày có hiệu lực và Giá trị lão hóa trên trang tính lão hóa cho biết các khoảng thời gian lão hóa đã được chỉ định dựa trên ngày sao kê được chỉ định trong ô B3

Tính toán phân tích tuổi

Tính toán phân tích độ tuổi được bao gồm trong cột D và E trên trang Người cao tuổi. Phép tính bao gồm Nhóm người cao tuổi và Số dư đã được tính toán cho từng nhóm người cao tuổi

Nếu tham chiếu tài khoản Khách hàng trong ô D3 trên trang Người cao tuổi bị bỏ trống, phép tính phân tích tuổi bao gồm tất cả các hóa đơn trên trang Hóa đơn nhưng nếu bạn nhập tham chiếu tài khoản khách hàng cụ thể vào ô D3, thì chỉ các hóa đơn liên quan đến khách hàng được chỉ định

Tất cả khách hàng

Như chúng tôi đã đề cập trước đây, bạn có thể chỉ cần để trống tham chiếu tài khoản khách hàng [ô D3] trên trang Tính tuổi để tính toán phân tích tuổi cho tất cả các tài khoản khách hàng có trong trang Hóa đơn

Tổng của phép tính này phải phù hợp với tổng giá trị hóa đơn [ô A2] trên trang Hóa đơn [miễn là tất cả các bộ lọc đã bị xóa khỏi trang này]. Nếu sau đó, bạn chỉ muốn xem các hóa đơn thuộc một nhóm lão hóa cụ thể, hãy sử dụng tính năng Bộ lọc tự động để lọc cột Giá trị lão hóa trên trang Hóa đơn và chỉ chọn giá trị lão hóa được yêu cầu. Sau đó, trang Hóa đơn sẽ chỉ bao gồm các hóa đơn đã chọn, tổng giá trị hóa đơn sẽ chỉ bao gồm các hóa đơn đã lọc và giá trị này phải phù hợp với giá trị cũ trên trang Lão hóa

Khách hàng duy nhất

Bạn có thể biên dịch phân tích độ tuổi cho chỉ một khách hàng đơn giản bằng cách nhập tham chiếu tài khoản khách hàng thích hợp vào ô D3 trên trang tính Người cao tuổi. Để xem các hóa đơn tạo thành một phần của phép tính này, chỉ cần sử dụng tính năng Lọc tự động để chọn khách hàng phù hợp từ cột Tham chiếu tài khoản khách hàng trên trang Hóa đơn. Tổng giá trị hóa đơn trong ô A2 trên trang Hóa đơn sau đó cũng sẽ chỉ bao gồm các hóa đơn của khách hàng đã chọn và giá trị này phải phù hợp với tổng phân tích độ tuổi trên trang Người già