Home / Report Templates / Aged Receivables Reports for Xero in Excel
The Aged Receivables Reports for Xero in Excel is a simple dashboard that allows you to report and reconcile money owed to you by your customers.
It reproduces much of the functionality available in the Aged Receivables Summary and Aged Receivables Detail report in Xero directly within Excel.
This report can also consolidate data from multiple Xero Organisations (data files) when using the Consolidation OData Feed.
While this report a fully working and standalone report, it can also be used as a starting point that can be further customised or modified as required to fit whatever business rules you need. It is not locked in any way. Because of this, it can also be used to learn how to create power queries, how to create DAX formulas, etc.
Also available are Aged Receivables Reports for Xero in Power BI for those that prefer using Power BI. There is also an Aged Payables Reports for Xero in Power BI and an an Aged Payables Reports for Xero in Excel which allows you to reconcile your outstanding supplier invoices.
Reports Includes
Consolidated Receivables Summary
This report provides the aged receivables summary for multiple data files when using a Consolidation OData Feed. Each company will be listed separately down the page.
Receivables Summary by Months
This report provides the aged receivables summary for all customers.
Receivables Detail by Months
This report provides the aged receivables detail for all customers. It lists all customer invoices outstanding.
Receivables Detail by Months (%)
This report provides the aged receivables detail for all customers. Rather than showing the dollar value, it instead shows the percentage split of the outstanding amount.
Receivables Summary by Days
This report provides the aged receivables summary for all customers. This report is similar to the Receivables Summary by Months but instead breaks the categories down as 0, 30, 60, 90 days bucket.
Receivables Detail by Days
This report provides the aged receivables detail for all customers. This report is similar to the Receivables Detail by Months but instead breaks the categories down as 0, 30, 60, 90 days bucket.
Requirements
To use the report, you will need to:
- Have an OdataLink Subscription or Trial Account.
- Connect OdataLink to your Xero Organisation.
- Configure a Model that includes the following endpoints:
Download and Setup
We provide 2 versions of the reports to download.
Aged Receivables Reports For Xero
This version of the report allows you to report on your receivables as at any date required. This does come at a small increase in performance as additional data must be downloaded.
Download the Aged Receivables Reports For Xero
Aged Receivables Reports For Xero (Current)
This version of the report allows you to report on your receivables as of right now. It only looks at currently outstanding invoices and as such does not need to download a lot of information out of Xero.
Download the Aged Receivables Reports For Xero (Current)
Once downloaded, you can connect it to your own data using this article.
How to Refresh the reports
To refresh the reports, follow these steps.
- Go to the Inputs sheet to enter the Parameters driving this report.
- Enter the As At Date for which to calculate the age.
(this parameter does not apply to the Aged Receivables Reports For Xero (Current) report) - Choose whether to calculate the age using the Invoice Date or the Due Date.
- Go to the Data tab in the ribbon, click the Refresh All button and wait for the data to download.
- Select a Worksheet and use the Filters to display the data you want.
Find an issue with the template?
Let us know of any issues you find with the template. We built it to use the least amount of data possible but may have this might not suit everyone.