The budget lives on its own tab, mapped once to the same report lines the actuals use, and left alone until the next budget round. Variance formulas read from that mapping, not from raw Xero rows, so each month's refresh moves the actuals underneath the report without breaking anything.
Key takeaways
- ✓Keep the budget on a separate tab mapped to the same report lines as actuals, not re-entered each period.
- ✓Use structural references — lookups keyed on a stable line code — instead of fixed cell references, so a refresh cannot shift a formula onto the wrong row.
- ✓Attach commentary to the mapped line through a keyed table, not a cell beside the report, so it survives when rows resize.
- ✓Decide upfront whether the pack shows a re-forecast or a restated budget, and label the column so the reader is not left guessing.
Hold the budget against the same mapped lines as the actuals
A budget entered as a flat list of account names drifts from the actuals the moment either side is reclassified. The fix is the same mapping layer a management accounts pack already uses: a table that keys every Xero account to a stable management line, once, and both actuals and budget pull through it.
Enter the budget once per period — monthly, or annual phased into months — against that mapping table, using the same line codes the actuals report against. Nobody re-types a chart of accounts into a budget tab; they allocate figures to the lines that already exist. When the mapping changes, both sides move together instead of drifting apart.
Keep variance formulas from breaking on refresh
A refreshed Xero export rarely lands in the same shape twice. A new account appears, a dormant one drops out, and every row below the change shifts down or up by one. A variance formula written against a fixed cell — =C14-B14 — now compares the wrong two numbers, and nothing about the sheet tells you it happened.
The fix is a formula that looks up its inputs by the stable line code instead of a cell address: a lookup keyed on the mapping table, not on where that line happened to land this month. Written that way, the formula finds “marketing expense” wherever it sits after refresh, rather than trusting that it still sits in row 14.
- Key every variance lookup on the management line code, never on a row or column position
- Keep the mapping table as the single place a line's identity is defined
- Treat a lookup that returns no match as a visible exception, not a blank cell
Write commentary that survives next month
Commentary typed into a cell beside a report row is the first thing a reshaped export destroys. The row moves; the sentence explaining last month's variance stays where it was, now sitting next to a different line.
Keep commentary in its own table, keyed on the same line code and the reporting period, and pull it into the pack by lookup rather than typing it in place. That keeps the explanation attached to the line it describes, not to a coordinate on the page, and it stops last month's note from silently reappearing as this month's when nobody has written a new one.
Re-forecast versus restated budget
These are different numbers and a reader of the pack needs to know which one they are looking at. A re-forecast revises the expectation for the remainder of the year based on what is now known; it replaces the original budget from that point forward and answers “what do we now expect to happen”.
A restated budget is the original approved figure, rebuilt onto a new mapping after the chart of accounts or management lines change mid-year, with the underlying numbers left alone. It answers “what did we originally approve, expressed in today's structure”, not “what do we now expect”.
Keep both as separate, clearly labeled columns if the pack uses a re-forecast at all — never overwrite the original budget column with a revised figure. A variance against a silently-changed budget reads as a changed result when the actual moved and as a hidden one when the budget did.
A worked variance-tab layout
One row per management line, with these columns in order: Line (the management line name), Code (the stable key from the mapping table, hidden or narrow — this is what every lookup on the row keys against), Actual (this period, pulled through the mapping), Budget (this period, entered or phased against the same code), Variance (Actual minus Budget, by lookup, not by cell reference), Variance % (Variance divided by Budget, guarded against a zero-budget divide).
After the monthly columns: Full-Year Budget, Full-Year Actual to Date, Full-Year Variance, so a reader can see the monthly number and the year's trajectory in the same row without switching tabs. Then Commentary, pulled by lookup from the separate commentary table on the same code and period, and Owner and Status (reviewed or open), so an unreviewed variance is visible rather than assumed.
The mapping code column is the spine of the tab. Actual, Budget, and Commentary all read from it, which is what lets the report reshape underneath without the variance column pointing at the wrong line.
Common questions
Do I need to re-enter my budget in Xero to use this?
No. The budget lives on its own tab in the sheet, mapped to the same lines the actuals use. Nothing about this workflow requires changing what is held in Xero.
What happens when Xero adds a new account partway through the year?
It needs a mapping entry before it first appears in the pack. Without one, a code-keyed lookup returns no match, which shows up as a visible exception rather than a silently missing line.
Should I use SUMIFS or INDEX/MATCH for the variance formulas?
Either works, as long as it looks up by the stable line code rather than a fixed cell address. Use whichever your team already reads and audits confidently.
How do I stop last month's commentary from reappearing this month?
Keep commentary in a table keyed on line and period, not typed into the report cell, and have each period's pack pull only the row matching the current period. A blank result means nobody has written this month's note yet.
Is a re-forecast the same thing as changing the budget?
No. A re-forecast is a revised expectation that sits alongside the original budget; a restated budget is the original figure rebuilt onto a new mapping. Keep both as separate, labeled columns rather than overwriting one with the other.