Loading the Budget Into the Ledger Without Breaking Reporting

Treat the load as a data migration, because that is what it is. You are moving a complete, approved dataset out of one system and into another, where it will be joined to actuals and read by people who were not involved in either. Every obligation that applies to a migration applies here: a defined source, a mapping that covers every record, a load, and a proof that what arrived equals what left.

Almost nobody treats it that way. The load gets an afternoon at the end of a thirteen week cycle, performed by whoever is least tired, and it is judged complete when the file stops throwing errors. That is a different test. A file can import cleanly and still be missing a third of the plan.

Here is the position, stated up front so the rest of this is readable as one argument: the reporting frustration a company feels in the second quarter almost always originated in a partial load in the previous quarter, and it presents as a systems problem when it is a completeness problem. Somebody says the variance report is wrong. It is not wrong. It is faithfully reporting against a budget that has eleven of the fourteen departments in it.

Phasing is settled before any of this, and it is argued out in the budget and reforecast calendar. Do not reopen it during the load. A load week that turns into a phasing debate is a load that does not finish.

The case

The company below is a composite and the setup is the ordinary one. Services, two revenue lines, work delivered out of three locations, forty or so people. The plan was built in a spreadsheet with a tab per budget holder, seven tabs, consolidated on an eighth. The chart of accounts had been rebuilt eighteen months earlier and was in reasonable shape. Actuals were coded to account and to location. Nothing about the setup was unusual and nothing about it was clean.

Elapsed time for the load was four working days, of which the actual import took under an hour. The other three and a half days are the subject of this piece.

Day one: freeze the source

Before anything is mapped, the approved plan becomes a file that cannot move.

Export the consolidated plan to a flat file with one row per account, per dimension, per month. Not a workbook with formulas in it. A flat file, saved read only, with the date and the version in the filename, stored where the close binder lives rather than in somebody’s downloads folder.

This step feels like ceremony and it is the reason the proof at the end is possible. The spreadsheet the plan was built in will keep being edited. Somebody will fix a formula in it next week, entirely in good faith, and if the tie-out at the end of the week is run against the live workbook rather than against the frozen export, it will fail for a reason nobody can reconstruct.

At the same time, write down the plan’s own control totals: revenue, total operating cost, the result, and the monthly result for each of the twelve months. Twelve plus three numbers on a single sheet. They are what you will prove against.

Day one, second half: the mapping table

One row for every line in the plan. Four columns to start with: the plan line as the budget holder wrote it, the account it maps to, the dimension values it carries, and a status.

The status column is where the real work surfaces, because plan lines fall into four groups and only the first one is easy.

Lines that map one to one. A plan line that corresponds to exactly one account, at one dimension. Most of the file. Mark them and move on.

Lines that map to several accounts. The budget holder wrote “software and tools” and the ledger has four accounts under that idea. You have two options and only one of them is correct. Splitting the plan line across the four accounts requires you to invent a split the budget holder never agreed to, and next spring somebody will be explaining a variance in an account nobody ever budgeted deliberately. Instead, go back to the holder with the four account names and ask them to split it themselves. It takes ten minutes and it is the difference between a budget the holder recognises and one they disown.

Lines with no account at all. Every load produces some. A new activity, a new kind of cost, a role that does not fit an existing department. These are decisions about the chart of accounts, and they should be small in number. If the load is generating fifteen of them, stop the load. Fifteen new accounts arriving at the point of import is a signal that the chart no longer describes the business, and no mapping table repairs that. A mapping layer is a translation, and translating faithfully out of a structure that is wrong gives you a faithful record of the wrong structure. That is a rebuild, done deliberately, in its own project, and it is the reason the finance systems work usually comes before a budget cycle rather than after one.

Lines that should not be loaded. Memorandum lines, subtotals, cross-checks, and the holder’s own working notes. They are in the spreadsheet legitimately and they must be excluded explicitly, with the exclusion recorded, because an excluded line and a forgotten line look identical in a mapping table and only one of them is a defect.

By the close of that day, every plan line carries one of those four statuses and none carries a blank. That is the completeness test for the mapping, and it is the only one available before the load.

Day two: dimensions, and the uncomfortable part

The plan was built by budget holder. The ledger codes actuals to account and location. Those are not the same cut of the business, and until somebody notices, the load looks fine.

The rule is short: you can only budget at a dimension your actuals are already coded at. If payroll is coded to location but not to department, then a departmental payroll budget will never have an actual to sit beside it. The variance column will be empty or, worse, populated by an allocation somebody wrote once and nobody maintains.

So the dimension conversation has three possible outcomes, and the company has to pick one before the load rather than discovering it in month two.

Situation	The honest option	What it costs
Actuals carry the dimension already	Load the plan at that dimension	Nothing. This is the good case.
Actuals do not carry it, and coding could start in the new year	Load the plan at the dimension, start coding actuals to it from the first day of the year, and accept that the comparative year has no dimension	A coding change everyone in the company has to be told about, once
Actuals do not carry it and coding will not change	Load the plan at the level the actuals support, and roll the budget holders’ detail up to it	The budget holders lose their own view. Say so out loud rather than letting them find out.

The third row is the one companies take by default and the one they never announce. A budget holder who submitted eleven lines and can only ever see three of them in a report has been quietly told their submission did not matter. Tell them before the load, offer them the second row, and let the owner decide which cost the company would rather carry.

In this company’s case, the plan had been built by budget holder and the actuals carried location. Payroll was the only material line where the two cuts genuinely conflicted. The decision took twenty minutes: load by location, and give the two holders whose lines spanned locations a supplementary schedule outside the ledger, clearly labelled as such so nobody mistakes it for a report.

Day two, second half: naming the version

Give the load a name before you make it, using a convention written down somewhere permanent.

Three properties are worth having. The name says which fiscal year it plans for. It says what kind of version it is, which for most companies is one of three: the approved budget, a quarterly reforecast, or a working version that nobody may report against. And it carries the date it was locked.

That is enough. What it must not do is drift into adjectives. A ledger holding “Budget 2027”, “Budget 2027 final” and “Budget 2027 final v2” has a real problem, because six months later nobody can tell which one the approver signed, and the variance report is pointed at whichever the system defaults to. If the accounting system permits only one budget version, that constraint is itself a finding: the reforecasts will have to live somewhere else, and where that is should be decided now rather than in the first quarter.

Lock the approved version against editing as soon as the tie-out passes. The lock rule for the plan itself is covered in the calendar piece; the point here is narrower and mechanical, which is that a version anybody can edit will be edited, and a variance history built on an editable reference is not a history.

Day three: the load itself

Order matters, because it lets you catch a failure while it is still small.

1. Load one department first, the smallest one, and reconcile it completely before touching anything else. Every mapping defect you have is probably present in that department, and finding it there costs an hour rather than a day.
2. Load revenue next, by month, at the dimension agreed. Revenue has the fewest lines and the largest numbers, so an error in it is both easy to see and expensive to miss.
3. Load the remaining operating cost by department.
4. Load payroll separately rather than as part of the departmental blocks. It is usually the largest cost, it usually comes from its own build, and giving it its own load step means it also gets its own tie-out.
5. Load capital and depreciation last, and only the depreciation the plan actually assumed, taken from the capital plan rather than recalculated during the load. Recalculating anything at the point of loading introduces a difference between the approved plan and the loaded plan, which is precisely the thing the next section is designed to detect.
6. Load nothing else. In particular, resist the temptation to load a balance sheet budget in the same week unless the plan genuinely contains one. A budgeted balance sheet the plan never produced is a number somebody invented on a Thursday.

Day four: the proof

Four tie-outs, in this order, all run against the frozen file from day one.

Tie-out	What it proves	The failure it catches
Annual total, loaded against approved, for revenue, operating cost and result	The load is complete in aggregate	A department or a whole tab omitted
Each of the twelve monthly results, loaded against approved	The phasing survived the load	A file loaded into the wrong periods, or a twelve-month plan loaded into thirteen
Count of loaded rows against count of mapped rows, excluding the lines marked as excluded	Nothing was silently dropped	Rows rejected by the import that nobody read the log for
Each department’s total, loaded against its own submission as approved	The load agrees with what each holder will be scored on	A rounding convention or a consolidation adjustment applied on the way in

The third one is the tie-out people skip and it is the one that catches the most. Import routines reject rows for ordinary reasons, an account that does not exist, a dimension value spelled differently, a date format, and many of them report the rejection in a log rather than as a failure. A file that loads with warnings has not loaded.

The fourth is the one that produces the awkward conversation, and it is worth having. If a consolidation adjustment was applied during the build so that the total worked, and it was never pushed back into the department it belongs to, then the department will be reported against a number its holder never saw. Push the adjustment down to the department before the load, or accept it as a separate central line, but do not let it sit inside a departmental total silently.

When all four agree, write a single note recording the version name, the date, the four results, and who ran them, and file it with the frozen export. That note is what somebody reads in eight months when a variance looks wrong, and it answers the first question they will have, which is whether the budget in the system is the budget that was approved. It belongs with the rest of the management reporting evidence rather than in the finance team’s own folder.

What the first month tells you

Run the first variance report before anybody asks for one, in the first close of the new year, and read it as a test of the load rather than as a test of the business.

Look for accounts with an actual and no budget, and accounts with a budget and no actual. Both are mapping defects and both are cheap to fix in month one and expensive in month six, because by then people have started explaining them as business events. An account carrying spend all year against a zero budget is not overspending. It is a plan line that landed in the account next door.