Aged Accounts Receivable Excel (report)
The Aged Accounts Receivable Excel report shows aggregated and summarized aging data based on customer ledger entries.
Data is aggregated and bucketed according to the Aged as of and Period length parameters in the report's request page. It is summarized by the two global dimensions.
The report Excel workbook contains four worksheets that you can use to analyze your aged accounts receivables:
- By period (LCY),
- By Period (FCY),
- Due by Currencies, and
- CustomerAgingData
Use the worksheets for different ways to analyze data.
Tip
If you configure OneDrive for system features, the Excel workbook opens in your browser by using Excel for the web.
Note
This report does the calculations when you view it in Excel online, or when you download and open it. If a banner displays text about external data connections, you might need to choose the Enable content button to load data. The report doesn't connect to any external data sources. All calculations are done in Excel with Power Query. In some cases (depending on the security configurations for your organization), you might also need to right-click on a pivot table in one of the worksheets and choose Refresh to update data in the reports.
By period (LCY) worksheet
This worksheet shows amounts in local currency by customer and with the ability to group data by a Year-Quarter-Month-Day hierarchy on the due date.
With filters and slicers, you can zoom into a single customer or a group of customers.
.png)
By Period (FCY) worksheet
This worksheet shows amounts in foreign currency (FCY) by customer and with the ability to group data by a Year-Quarter-Month-Day hierarchy on the due date.
With filters and slicers, you can zoom into a single customer or a group of customers. You can also filter by one or more currency codes, if needed.
.png)
Due by Currencies worksheet
This worksheet shows amounts by currency code and with the ability to group data by a Year-Quarter-Month-Day hierarchy on the due date.
With filters and slicers, you can zoom into a single customer or a group of customers. You can also filter by one or more currency codes, if needed.
CustomerAgingData worksheet
This worksheet shows the raw data used in the report. The dataset for the report is aggregated, so the worksheet doesn't show individual transactions.
Use this worksheet for data analysis assisted by built-in tools in Excel, such as Excel Copilot, or the What-if-analysis or Forecast Sheet tools.
To learn more, go to Get started with Copilot in Excel.
Use cases
Analyze customer balances at the end of each period, either in local or in foreign currency. Use as a gauge to measure the reliability of debt collections for your customers. Easily reconcile the customer subledger against the receivables accounts in the general ledger, assuming that direct posting is disabled.
Accounts receivable professionals use the report to:
- Reconcile customer subledgers against the receivables accounts in the general ledger and ensure that all outstanding balances are accurate and complete.
- Identify discrepancies or errors in customer transactions and investigate them.
- Get data to prepare financial statements, such as income statements or balance sheets.
Collections specialists use the report to:
- Identify overdue customer accounts and prioritize follow-up activities based on how overdue the payments are
- Analyze customer payment history to identify patterns or trends in late payments and proactively address issues.
- Prepare collection reports or dashboards for internal or external stakeholders.
Credit analysts use the report to:
- Analyze customer payment history to make recommendations for credit limits or payment terms.
- Identify areas to save cost or generate revenue, and make recommendations for improving financial performance.
- Get data to prepare financial reports for stakeholders such as investors or executives.
Try the report
Try the report here: Aged Accounts Receivable Excel
Tip
If you hold down the CTRL key while you select the report link, the report opens on a new browser tab. In this way, you can stay on the current page while you explore the report on the other browser tab.
Alternative reports
There are several other ways to analyze your aged accounts receivables. To learn more, go to:
Contributors
Microsoft maintains this article. The following contributors provided some or all of its contents.
- Kim Dallefeld | Microsoft MVP
Related information
Accounts receivable report overview
Key finance report overview
Ad hoc analysis on finance data
Financial analytics overview