Xero to Excel: Refreshable Reporting Pack

The process starts with general ledger and trial balance extracts from Xero. Power Query cleans and consolidates the data, with each transformation recorded as a repeatable step. The results feed a PivotTable general ledger and trial balance, which then supply the statement of assets and liabilities and the profit and loss.

The profit and loss can show either the consolidated position or an individual cost centre through a single workbook. The dashboard draws from the statements, providing monthly movements and headline KPIs. Refreshing the source extract updates the downstream reports.

The reporting pack was reconciled to the equivalent Xero reports to two decimal places. This check confirms that the outputs agree with the source data and that the model is suitable for recurring reporting.

01 / 03

Statement of Assets and Liabilities

The balance sheet component of the two-statement model, with each line linked to the PivotTable trial balance rather than manually entered figures.

Account mapping is maintained in Power Query, so changes to the chart of accounts can be managed in one place. A new reporting period requires a refresh rather than a complete rebuild.

View the statement

02 / 03

Profit and Loss, Consolidated and by Cost Centre

A single profit and loss report covering two cost centres, with the option to view the consolidated result or an individual centre.

Both views use the same underlying data and formulas. The consolidated result is calculated from the cost centre figures, reducing the risk of differences between separate workbooks.

View the profit and loss

03 / 03

Monthly Dashboard and KPIs

The dashboard is linked to the financial statements and presents monthly movements alongside headline KPIs.

Using the statements as the dashboard’s source keeps the reported figures consistent across the reporting pack and avoids maintaining separate calculations from the raw extract.

View the dashboard