Aged Accounts Payables Excel (report)
The Aged Accounts Payables Excel report uses vendor ledger entries to aggregate and bucket data according to the Aged as of and Period length parameters in the report's request page. Data is summarized by the two global dimensions.
The report Excel workbook contains four worksheets that you can use to analyze your aged accounts payable:
- By period (LCY)
- By Period (FCY)
- Due by Currencies
- CustomerAgingData
Use the worksheets to analyze data in different ways.
Tip
Tip:
If you configure OneDrive for system features, the Excel workbook opens in your browser in Excel online.
Note
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 (LCY) by vendor, and you can group data by a Year-Quarter-Month-Day hierarchy on the due date.
The filters and slicers let you zoom in on a single vendor or a group of vendors.
Tip
Tip:
The data is shown in an Excel pivot table. Choose any cell with data to open the Field List, where you can arrange fields, group or ungroup data, and filter data. To learn more, go to the following articles:
By Period (FCY) worksheet
This worksheet shows amounts in foreign currency (FCY) by vendor, and you can group data by a Year-Quarter-Month-Day hierarchy on the due date.
The filters and slicers let you zoom in on a vendor or a group of vendors. You can also filter by one or more currency codes, if needed.
Tip
Tip:
The data is shown in an Excel pivot table. Choose any cell with data to open the Field List, where you can arrange fields, group or ungroup data, and filter data. To learn more, go to the following articles:
Due by Currencies worksheet
This worksheet shows amounts by currency code, and you can group data by a Year-Quarter-Month-Day hierarchy on the due date.
The filters and slicers let you zoom in on a vendor or a group of vendors. You can also filter by one or more currency codes, if needed.
Tip
Tip:
The data is shown in an Excel pivot table. Choose any cell with data to open the Field List, where you can arrange fields, group or ungroup data, and filter data. To learn more, go to the following articles:
VendorAgingData 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.
Other worksheets
The Business Central platform always adds three system worksheets to Excel reports:
- TranslationData, with text needed for multi-language reports.
- CaptionData, with text from Business Central fields needed for multi-language reports
- Aggregated Metadata, with data about the report, and when and how it was run.
All three worksheets are hidden by default. To learn more, go to System Excel workbooks in the developer documentation.
Use cases
Analyze vendor balances at the end of each period directly in Excel, by period (LCY), by Period (FCY), or an overview Due by Currencies. Monitor unpaid invoices, and prioritize payments for overdue accounts.
Try the report
Try the report here: Aged Accounts Payables Excel
Tip
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.
Make the report your own
You can create your own report layouts with Excel, either from scratch or based on an existing layout. To modify the layout for the report, export the layout (not the workbook that resulted from running the report) from Business Central, make your changes, and then import the layout into Business Central.
To learn more, go to Task 1: Create the Excel layout file.
Alternative reports
There are several other ways to analyze aged accounts payable. 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 payable analytics
Key finance report overview
Ad hoc analysis on finance data
Financial analytics overview