Vendor ageing analysis shows which supplier invoices are still open at a reporting date, how far past their due dates they are, and who should act next. The same report can support a month-end accounts payable review and the next payment run when it separates invoices due for payment from disputes, unapplied credits and unmatched payments.
Download the vendor ageing analysis Excel format
The workbook includes a completed example and a blank template for up to 50 document lines. It calculates ageing buckets, flags common input issues and compares the net position with your accounts payable control balance.
How to prepare vendor ageing analysis in five steps
1. Set the reporting date and export open items
Use one legal entity, one reporting date and one currency per run. Export the supplier code, invoice or document reference, posting or document date, due date and balance open at that cut-off. For a historical month-end, request a historical open-item export. A current balance can reflect a payment posted after the month closed, which would understate the balance at the earlier date.
2. Confirm due dates and open balances
Use the approved due date from the ledger or agreed supplier terms. An invoice dated 1 October and due 31 October has zero days overdue on 31 October. Review missing dates against the supplier terms before assigning an ageing bucket. For a ₹90,000 invoice with a ₹30,000 payment applied before cut-off, enter the ₹60,000 open balance. An unapplied payment belongs on a separate negative line until it is matched.
3. Group invoices by days overdue
Subtract the due date from the report date. Keep negative and zero days in Not due for this workbook, then use 1–30, 31–60, 61–90 and 91+ days. A formula such as =IF(E23="","",$B$4-E23) illustrates the date calculation. The template also checks cut-off and document type before assigning a bucket. To build a summary, use SUMIFS to total the at-cut-off balance by bucket. Microsoft’s SUMIFS reference
4. Reconcile the net total to the AP control balance
Start with gross open invoices, subtract unallocated credits and payments, and compare the result with an independently exported ledger balance for the same date and scope. For a difference, inspect late postings, missing suppliers, currency mismatches, duplicate rows and manual entries to the control account. Save the ledger export and report together so a reviewer can reproduce the comparison.
5. Assign and review follow-up actions
Give each exception an owner, a next action and a due date. For a blocked invoice, record the exact reason and who can resolve it. For an overdue but approved invoice, confirm the proposed payment run and record any approved deferral. Close the follow-up when its action is complete, then keep the invoice balance open until a payment, credit or other approved entry clears it in the ledger.
Worked vendor ageing analysis as at 31 October 2026
These figures are illustrative. Six invoices total ₹2,80,000. The ₹30,000 disputed invoice remains part of the gross ageing because the balance is still open.
| Document | Due date | Open amount | Days overdue | Bucket |
|---|---|---|---|---|
| INV-101 | 10 Nov 2026 | ₹60,000 | −10 | Not due |
| INV-102 | 31 Oct 2026 | ₹40,000 | 0 | Not due |
| INV-103 | 10 Oct 2026 | ₹80,000 | 21 | 1–30 days |
| INV-104 | 15 Sep 2026 | ₹50,000 | 46 | 31–60 days |
| INV-105 | 15 Aug 2026 | ₹30,000 | 77 | 61–90 days |
| INV-106 | 1 Jul 2026 | ₹20,000 | 122 | 91+ days |
Reconciliation. ₹2,80,000 gross open invoices less a ₹10,000 unallocated credit note and a ₹15,000 unallocated payment gives a ₹2,55,000 net open position. It matches the example AP control balance, leaving a ₹0 difference. Gross overdue invoices total ₹1,80,000, including the disputed amount.
What to do with overdue vendor balances
- Ready for payment. AP confirms approvals, checks for duplicate or recent payments and includes the invoice in the approved payment proposal.
- Quantity or price dispute. Procurement compares the invoice with the purchase order and receipt, requests evidence or a credit note, and records the next review date.
- Payment awaiting allocation. Treasury shares the bank reference and remittance advice. AP checks the supplier account and matches the payment once the evidence supports it.
- Supplier statement mismatch. AP compares invoices, credits and payments line by line with the supplier statement. Record who will resolve each difference and by when.
Before each payment run, review overdue approved items and suppliers at risk of withholding deliveries. At the weekly AP review, bring forward unresolved disputes, unapplied payments and older invoices without an owner. At month-end, preserve the signed-off ageing and its control reconciliation before rolling the tracker into the next period.
From the ageing report to the payment meeting
Start with the items the meeting can move forward. In the worked example, the ₹80,000 Fresh Foods invoice is 21 days overdue and may be a higher operational priority than an older item if the supplier is essential to daily operations. Confirm the invoice is approved and check the latest remittance list before putting it on the payment proposal. Keep the ₹30,000 Maintenance Services dispute with the person responsible for obtaining the service evidence, and agree a date to review the hold.
The ₹15,000 unmatched payment needs a separate action. Ask for the bank reference, payment date, beneficiary and remittance detail, then trace it against the supplier ledger. When AP allocates it, remove the separate unapplied row and reduce the matched invoice balance in the next report. Keep the evidence with the reconciliation so the change between periods can be explained.
At the next review, compare the two vendor ageing analysis snapshots. Check which invoices moved into an older bucket, which disputes were resolved and whether credits were applied. Investigate any increase in the 91+ bucket by looking at individual invoices and their owners. A simple movement summary gives the reviewer a way to distinguish overdue items that need action today from older items already being resolved.
How to use the vendor ageing analysis workbook
- Open Worked example to see the report, bucket totals and follow-up fields in use.
- On Your ageing, enter the report date in B4 and the independently exported AP control balance in F9.
- Enter one open document per row in the yellow cells. Use positive invoice balances and negative unallocated credits or payments. Add an owner, status and next action for each item requiring follow-up.
- Clear or investigate flags for missing dates, duplicate references and out-of-cut-off items. Review the bucket summary and resolve the ledger difference before sign-off.
Frequently asked questions
Is vendor ageing the same as vendor reconciliation?
Vendor ageing analysis groups your open balances by due date. Reconciliation compares your supplier ledger against the supplier’s statement and identifies differences in invoices, credits and payments. An ageing report helps decide which balances to investigate first.
Should disputed invoices stay in the report?
Yes, while their balances remain open in the ledger. Mark them as disputed, record the owner and track the resolution. This keeps the ageing total tied to the ledger and makes the payment hold visible.
How do I handle a goods receipt without an invoice?
Review it through the GR/IR reconciliation process. Include a balance in invoice ageing when it is an open supplier invoice within the scope of your AP export.
