Building a Month-End Management Reporting Pack from Xero in Excel
Most management packs are not badly designed. They were designed once, for one client, in a hurry, and then copied. Three years later the practice has thirty of them, all slightly different, and month-end turns into a week of opening files and remembering which one has the broken link in the cash flow tab.
A better template does not fix that. What does is splitting the pack into layers that fail independently, so that a new account code or a back-dated journal stops meaning a rebuild. This guide sets out that structure, the Excel and Power Query patterns behind it, and which parts are worth automating first. It assumes your Xero data already reaches Excel somehow. If it doesn't yet, start with how to connect Excel to Xero with Power Query, which covers the three routes (one of them being a synced database, the approach Flow takes).
What the pack has to contain
Directors read four things: what happened this month, how it compares to something, where the cash is, and what you make of it. The rest is supporting detail.
In practice that means a P&L with prior month and year-to-date columns, a balance sheet, a cash summary, and a page of commentary. Add budget versus actual where there's a budget worth comparing against, and departmental or project splits where the chart of accounts or tracking categories support them. Adding pages because the data happens to exist is how packs get slow, since every page then has to reconcile every month.
Settling the contents first tells you what shape the data underneath needs to be, which is the next section.
Build it in three layers
The most useful structural decision is to stop thinking of the pack as a workbook and start thinking of it as three layers that touch each other in one direction only.
The data layer is one long, tidy table: one row per account per period, with the organisation stamped on it. No formatting, no subtotals, no report structure. Power Query loads it and nothing in it is typed by hand.
The report layer turns that table into P&L and balance sheet blocks using formulas that read the table by name rather than by cell position. This is where account codes become report lines.
The presentation layer is the formatted pages people actually see: headings, number formats, charts, commentary. It reads the report layer and contains no calculations of its own.
One rule holds it together. Each layer reads only the layer below it, never sideways and never upwards. When a report line comes out wrong you can tell straight away whether it's a mapping problem or a source problem, because there is exactly one place each of them can live.
Layer 1: a spine that survives the chart of accounts
The data layer needs a period column, an account code, an amount, and an organisation column if you report on more than one entity. The cleaning steps for a Xero trial balance export are covered in Xero trial balance in Excel with Power Query. What matters for a monthly pack is that the query returns several months rather than just the current one, because every comparative column in the pack is a filter on that table rather than a separate data source.
Join the mapping table inside the query rather than on the sheet, so the report layer only ever sees report lines:
let
Source = Sql.Database("your-server.database.windows.net", "your-database"),
TB = Source{[Schema = "dbo", Item = "TrialBalance"]}[Data],
Window = Table.SelectRows(TB, each [SnapshotDate] >= #date(2025, 4, 30)),
Joined = Table.NestedJoin(Window, {"AccountCode"}, Mapping, {"AccountCode"}, "map", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Joined, "map", {"Statement", "ReportLine", "Sort"}),
Grouped = Table.Group(Expanded, {"SnapshotDate", "Statement", "ReportLine", "Sort"},
{{"Amount", each List.Sum([Amount]), type number}})
in
Grouped
Use a left outer join, then load the unmapped rows to their own small sheet as an exceptions query:
let
Source = Expanded,
Unmapped = Table.SelectRows(Source, each [ReportLine] = null),
Summarised = Table.Group(Unmapped, {"AccountCode", "AccountName"},
{{"Amount", each List.Sum([Amount]), type number}})
in
Summarised
That query is the most valuable control in the pack. When someone adds an account in Xero on the 28th, it shows up there on your next refresh, along with the amount that would otherwise have disappeared. An inner join would have dropped it silently and the P&L would still have footed. Map by code rather than by name, too. Names get edited in Accounting → Advanced → Chart of accounts, and a mapping keyed on them breaks without saying anything.
Layer 2: report blocks that reflow
Once report lines are in the data, a P&L page is one formula per block instead of one formula per row. Here's a month-plus-comparatives block, driven by a single month-end date in $B$2:
=LET(
ml, $B$2,
fyStart, DATE(YEAR(ml) - (MONTH(ml) < 4), 4, 1),
names, UNIQUE(FILTER(pl[ReportLine], pl[Statement] = "P&L")),
lines, SORTBY(names, XLOOKUP(names, pl[ReportLine], pl[Sort])),
actual, SUMIFS(pl[Amount], pl[ReportLine], lines, pl[Period], ml),
prior, SUMIFS(pl[Amount], pl[ReportLine], lines, pl[Period], EOMONTH(ml, -1)),
ytd, SUMIFS(pl[Amount], pl[ReportLine], lines, pl[Period], ">=" & fyStart,
pl[Period], "<=" & ml),
HSTACK(lines, actual, prior, actual - prior, ytd)
)
Two details are worth pulling out. SUMIFS accepts the spilled lines array as a criterion and returns one result per line, so the block grows and shrinks with the data instead of needing rows inserted. And the prior-month column uses EOMONTH(ml, -1) rather than EDATE(ml, -1), because EDATE from a 30 April period end returns 30 March, which quietly matches nothing when your periods are month-end dates.
Variance percentages are worth packaging once as a named LAMBDA instead of repeating an IFERROR down every page:
=LAMBDA(actual, comparative,
IF(comparative = 0, "", (actual - comparative) / ABS(comparative))
)
Define it in Formulas → Name Manager as VarPct and call it with =VarPct(D5, E5). The ABS on the denominator is the whole point. Without it, a cost line that improved from a credit balance reports its variance with the sign flipped, and that's the sort of error that survives review because the number still looks plausible.
The checks that make it safe to send
A pack that refreshes is not yet a pack that's safe to send. Four checks, all cheap, all sitting on the same sheet, all recalculating on every refresh rather than depending on someone's diligence at 6pm:
- The trial balance foots. Sum every mapped amount for the period. It should be nil. If it isn't, the mapping is dropping something.
- The exceptions query is empty. A row here means an account exists in Xero and not in your pack.
- Balance sheet movement ties to the P&L result. Retained earnings movement less the period result should be nil, or explained by dividends and reserve transfers you can name.
- Opening balances agree to last month's pack. This is the check that catches back-dated journals, and it's why you hold several months in the data layer rather than only the current one.
Format each as a single cell showing nil or a variance, conditionally formatted red. If any of the four is non-zero, nothing goes out.
What to automate first
In rough order of payback for a practice doing this across a portfolio:
- Getting the data in. The export step is almost always the biggest recurring cost, and it's the only one that scales with clients and months at the same time.
- The mapping and the exceptions check. Cheap to build, and it turns a silent failure mode into a visible one.
- The report blocks. Real time saved, but only once the two above are stable.
- Commentary. Don't. Variance thresholds can flag what to write about. The writing is the value you're charging for.
Where several clients or entities share a pack format, the append and mapping patterns in consolidating multiple Xero organisations in one Excel workbook apply directly. A portfolio of clients is structurally the same problem as a group of entities, minus the eliminations.
Pitfalls worth designing around
- The current month is provisional. It changes daily, and clients close at different speeds. Label it in the pack, not just in your head.
- Comparatives move. A back-dated journal changes last month's figures after last month's pack went out. Decide once whether you restate or footnote, then apply it consistently.
- Sign conventions. Fix them in the query, debits positive and credits negative, and never again in the report layer. A pack with the convention flipped in one place still foots and is still wrong.
- Formatting in the data layer. The moment someone types over a refreshed cell, the pack has two sources of truth. Protect the sheet.
- One client's bespoke request. Either add it to the template for everyone or keep it in a separate appendix page. A per-client fork is how you end up with thirty different packs again.
Where Flow fits
Flow syncs your connected Xero organisations into an Azure SQL database, covering monthly trial balance snapshots, invoices, credit notes and the chart of accounts. The data layer above then refreshes with no export step in front of it, and historic months are re-verified rather than written once, which is what keeps comparatives agreeing to the pack you sent last month.
Plans start at £20/month for up to 5 organisations with 2 years of trial balance history. Plus (£40/month) covers 30 organisations with configurable 1–5 year history and real-time refresh, and Premium (£60/month) adds tracking category breakdowns across up to 60 organisations. There is a 14-day free trial, no credit card required.