Know who owes you money, how late it is and who is chasing it. The Accounts Receivable Aging Dashboard in Excel turns an invoice list into five clickable pages: Overview, Aging, Customers, Collectors and Trend. Add your invoices, click Refresh All, and every card and chart updates. It uses no macros and no add-ins.
Watch the Accounts Receivable Aging Dashboard in Excel walkthrough
Good to know: this is an add-data-and-refresh workbook, not a live connector to your accounting software. It is built for desktop Excel on Windows (Excel 2016 or later, or Microsoft 365). The sample data is fictional.
What You Get
- Five dashboard pages: Overview, Aging, Customers, Collectors and Trend
- KPI cards for Total Invoiced, Total Outstanding, Total Overdue, DSO and Overdue Invoices
- Aging buckets of 0-30, 31-60, 61-90 and 90+ days
- Four slicers (Year, Month, Collector and Customer Segment) that filter all five pages
- A Data tab with 500 sample invoices and formula columns for outstanding balance, aging bucket, overdue amount, month, year and quarter
- A Support tab that holds the pivot tables, plus a user manual PDF
Key Features
Overview Page
Five KPI cards sit across the top: Total Invoiced, Total Outstanding, Total Overdue, DSO (Days) and Overdue Invoices. Below them you get Collection Rate % by Industry, a Collection Rate % battery chart, Total Overdue by Region and Total Outstanding by Aging Bucket.
Aging Page
Outstanding by status, outstanding versus overdue by customer segment, average days past due by payment terms, and overdue invoices by aging bucket.
Customers Page
The top 10 customers by total overdue and by DSO, outstanding by industry, and overdue as a percentage of billing by customer segment.
Collectors Page
Collected versus outstanding, collection rate, collection calls and overdue invoices for each collector.
Trend Page
Outstanding versus overdue by month, collection rate by month, overdue by quarter and average invoice value by month.
Who Is It For?
Finance managers, credit controllers, bookkeepers and business owners who send invoices on credit and want a clear picture of what is overdue. It also suits accountants who review receivables for clients.
How to Use
- Download the ZIP and open the Excel file.
- Go to the Data tab and replace the sample invoices with your own.
- Click Refresh All.
- Use the Year, Month, Collector and Customer Segment slicers to filter every page.
- Move through the pages from the navigation bar on the left.
Comparison
| Feature | This dashboard | Plain invoice list |
|---|---|---|
| Aging buckets 0-90+ days | Automatic | Manual formulas |
| DSO and collection rate | On the Overview page | Build yourself |
| Overdue by customer and collector | Charts and slicers | Filter and count by hand |
| Macros required | No | No |
| Live link to accounting software | No | No |
Frequently Asked Questions
Does it connect to my accounting software?
No. It is an add-data-and-refresh workbook. Paste or type your invoices on the Data tab, click Refresh All on the Data tab, and every card, chart and slicer updates. There is no live link to QuickBooks, Xero or any other system.
Does it need macros or add-ins?
No. It uses formulas, pivot tables, charts and slicers only, so there is nothing to enable beyond opening the file.
Which Excel version do I need?
Desktop Excel for Windows, Excel 2016 or later, or Microsoft 365. The slicers and pivot tables do not work fully in Excel for the web.
How is DSO calculated?
The DSO card shows days sales outstanding for the invoices in view. It reacts to the Year, Month, Collector and Customer Segment slicers, so you can see DSO for one collector or one segment.
What are the aging buckets?
Outstanding balances are grouped into 0-30, 31-60, 61-90 and 90+ days, using the due date and the status of each invoice.
Is the sample data real?
No. The 500 invoices on the Data tab are fictional. Replace them with your own and refresh.
Can I keep real customer data in it?
Yes, but remember that an Excel file is only as private as where you store it. Keep your copy somewhere access-controlled and share it with named people only.
Get the Dashboard
Download the Accounts Receivable Aging Dashboard in Excel and see your receivables on one screen. For another finance view, see the Credit Recovery Agencies KPI Dashboard in Excel, or browse all Excel templates.
Reviews
There are no reviews yet.