Payables Reconciliation Report for Xero

Home / Report Templates / Payables Reconciliation Report for Xero

The Payables Reconciliation Report for Xero is a simple dashboard that reconciles a single month of transactions to your Trade Creditors account.

The report includes:

• the opening balance of the trade creditors account.
• all supplier invoices, payments and credit notes posted during the selected month.
• the closing balance of the trade creditors account.

In addition to the Dashboard, it also includes three additional report that list these same invoices, payments and credit notes.

Not only this, but it 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.

For an equivalent report that audits the Trade Debtors account, see the Receivables Reconciliation Report for Xero.

Requirements

To use the report, you will need to:

1. Have an OdataLink Subscription or Trial Account.
2. Connect OdataLink to your Xero Organisation.
3. Configure a Model that includes the following endpoints:

• Accounts
• BalanceSheet
• ContactsExpanded
• CreditNotesExpanded
• DataFiles
• InvoicesExpanded
• OverpaymentsExpanded
• Payments
• PrepaymentsExpanded

Download and Setup

You can download the workbook from the following link.

Once downloaded, you can connect it to your own data using this article.

How to Refresh in Power BI Desktop

To refresh it in Power BI Desktop, follow these steps.

1. Under the Home tab, go to Transform Data and click on Edit Parameters
2. Enter a date for the month you wish to report on. This can be any date, the dashboard will always pull a full month of data.
3. Click the Refresh button and wait for the data to download.

Known Limitations

The closing balance field will include unrealised currency gains and losses. Unfortunately, Xero doesn’t provide the exchange rate used to produce this value. Because of this, a small out of balance will appear when foreign currency invoices are raised and unpaid in Xero.

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.