Accounts payable aging summary spreadsheet template
An accounts payable aging summary shows what a business owes suppliers, how long each balance has remained unpaid, and which invoices need attention. By grouping outstanding amounts into current, 1–30, 31–60, 61–90 and over-90-day bands, it turns a long list of bills into a practical view of unpaid commitments. That makes it easier for a bookkeeper, office manager or owner to prioritise follow-up.
This guide explains how to build a reusable accounts payable aging report in Excel, Google Sheets or LibreOffice Calc. The design suits small Australian teams that need a straightforward way to review invoices, compare balances with the accounting ledger and prepare for supplier payments. It also works for larger operations that want consistent reporting across departments.
Use the workbook as a snapshot rather than a substitute for the general ledger. Enter or paste the latest open invoices, set one reporting date and keep supplier credit notes identifiable. For an Australian business, amounts should be in Australian dollars, dates are easiest to read in day/month/year order, and GST should remain visible rather than being buried in a single figure.
What the ageing report shows
The report normally contains two linked views: a detailed invoice list and a summary by supplier or ageing band. Each row should identify the supplier, invoice number, invoice date, due date, payment terms, invoice total, GST, amount paid, outstanding balance, days overdue, owner and status. Optional fields such as purchase order number, cost centre and dispute notes help explain why a payment is being held.
The summary view uses conditional formatting to highlight ageing bands. Colour can indicate urgent balances, but labels should carry the meaning so the report still works when printed in greyscale. An owner might work through the over-90-day section first, while the finance team uses the current bucket to forecast the next cash requirement. Sorting by days overdue gives a different sequence from sorting by dollar value, so choose the view that matches the decision being made.
Keep the scope limited to amounts payable to suppliers. Customer receivables belong in a separate ageing report, and payroll items should not be mixed into the same total. Deferred subscriptions, capital purchases and recurring software invoices can be grouped internally with clear labels if needed. Credit notes and disputed invoices should also remain visible; silently removing them can make a balance look healthier than it really is.
Columns that suit Australian invoices
Start with a report date near the top of the sheet, followed by a transaction row for every open invoice. Suggested columns are supplier, invoice ID, invoice date, due date, terms, subtotal, GST, total AUD, amount paid, outstanding AUD, days overdue, ageing band, responsible person and notes. Unique invoice IDs prevent the same bill from being counted twice, especially when a supplier uses similar reference numbers.
Australian tax treatment deserves its own columns. A standard-rated invoice may include 10% GST, while some supplies are GST-free or zero-rated; do not add tax based only on a guess. Keeping subtotal and GST visible helps reconcile source documents with the ledger and BAS working papers, although this report is not itself a BAS form. For foreign-currency invoices, use the exchange rate recorded in the accounting system rather than a rate chosen after the fact.
The Australian financial year runs from 1 July to 30 June, with the end of the financial year on 30 June. A 30 June snapshot can assist year-end review, but it does not replace proper cut-off or accrual checks. For a Sydney agency or Melbourne retail business, the same columns support local supplier relationships, while a regional operator may add a branch or location column so balances can be assigned to the correct cost centre.
Formulas that calculate each balance
Put the reporting date in a fixed cell, such as B1, and assume the first data row is row 2. With due date in E2, invoice total in F2 and amount paid in G2, days overdue can be calculated as =IF(E2="","",MAX(0,$B$1-E2)). Using a fixed date makes every ageing band consistent and allows an older report to be reproduced without the figures changing each day.
Outstanding balance is =IF(F2="","",F2-G2). If H2 holds days overdue and I2 holds the balance, a nested formula can assign the band: =IF(I2="","",IF(E2="","Date needed",IF(H2=0,"Current",IF(H2<=30,"1–30 days",IF(H2<=60,"31–60 days",IF(H2<=90,"61–90 days","Over 90 days")))))). Check that zero-paid invoices with a future due date remain current, and decide separately how to display invoices without a due date or with a credit balance.
Create a supplier summary with SUMIF formulas and a grand total that reconciles to the detailed rows. Conditional formatting can apply one fill to each ageing band, while data validation can restrict status values such as Current, Approved, Disputed, On hold and Paid. Avoid totals that count subtotal and GST again; the ageing total should represent only the outstanding amount payable. A second check against the accounts payable control account helps identify omitted or duplicated invoices.
A practical pay run workflow
A practical Australian workflow often begins with an export from Xero, MYOB or QuickBooks. Paste the invoice fields into the template, confirm that dates and amounts arrived as values, and remove fully settled bills. Reconcile the outstanding total with the accounting ledger before circulating the report. An export may include overdue dates based on a different due-date rule or include credit notes that need separate treatment.
Many small teams review bills before the weekly or fortnightly pay run. The bookkeeper might send an aged list to the office manager, ask the manager to “chase up” missing invoices and settle approvals before payment. In a busy Sydney office or Melbourne studio, adding a responsible person and a response-date column keeps ownership clear. Publishing the reporting time and version prevents staff from working with conflicting copies.
New starters can practise tracing documents and spotting gaps before handling the live file. During training, pairing a reconciliation exercise with simple puzzle sheets gives the team a low-pressure way to follow a path, match records and notice missing steps. The exercise does not replace the ledger check, but it can make the habit of checking each link in a process easier to remember.
Reusable templates and reporting options
Reuse the design for monthly supplier reviews, budgeting cash commitments, confirming rebate eligibility where terms apply, and assessing whether an old invoice needs a dispute process. A management version can show totals only, while a working version retains invoice-level evidence and notes. Filter by branch, owner, supplier or status when a larger team needs a focused view.
The format should also support basic controls. Keep source exports with the reporting period, restrict editing of formula cells and record who prepared and reviewed the snapshot. The ATO generally requires business records for five years, although some records need longer. The report contains commercial and sometimes personal information, so limit access and remove unnecessary notes when sharing.
The comparison below shows which method suits different levels of accounting complexity.
| Approach | Best suited to | Advantages | Limitations |
|---|---|---|---|
| Manual supplier list | Owners with few invoices | Quick to create and familiar to use | Prone to missed or duplicated balances |
| Reusable ageing workbook | Small teams and regular spreadsheet users | Customisable formulas, filters and clear ageing bands | Requires imports and ledger reconciliation |
| Accounting-system report | Multi-entity businesses | Often updates automatically and links to source records | Less flexible for tailored management views |
| Hybrid workflow | Growing businesses with multiple approvers | Combines system exports with focused tracking and ownership | Requires a documented review process |
Start the next reconciliation by setting the reporting date, listing open invoices in AUD and confirming that the grand total agrees with the ledger. Keep GST visible, assign owners to overdue balances and save the completed report with its source export so the next update is faster.
Use the free layout to create that supplier snapshot before the next pay run, then share the filtered version with the people responsible for approvals and payment. A consistent, dated workbook gives Australian teams a clearer way to know what is due, what is late and which supplier conversations need attention now.