Two Worlds, One Ledger: How an Energy Infrastructure Company Reconciled Its Excel Forecasts with Accounting

There's one file everyone at this company knows. Several hundred megabytes in size. When you open it, you go and fetch a coffee while it loads. And if you don't let it fully recalculate, it quietly shows the wrong numbers – and nobody notices.

That file is the forecast model. It steers an infrastructure project worth hundreds of millions of euros.

Alongside it lives a second world: the ERP. MS Dynamics, general ledger, cost centres, maintained by the accounting team. The two worlds had never been introduced. When project controlling reports €4.2M in costs and accounting reports €4.6M, the detective work begins: Who is right? Why? And how do you explain the difference to management?

That was the situation when we took the project on.

The uncomfortable requirement

The obvious consultant reflex would be: rip out Excel, drop in a new EPM tool, re-engineer every process. That was precisely what was not wanted here – for good reason. The forecast models were the well-rehearsed instrument of project controlling: thoughtfully designed, deeply embedded in the team. Switching tools in the middle of a running mega-project? Too risky, too expensive, too much friction.
The Excel models stay. But they have to stop lying.

That is the much harder task. And the much better one.

Understand first, then build

We didn't start with a requirements document, but with a prototype. We retraced the complete transformation workflow of the Excel models, cell by cell, logic by logic. In parallel, we dissected the MS Dynamics data model – which tables carry which information, and where the general ledger, sub-ledgers and payment runs connect.

Only once both worlds were understood did we build for real: a multi-layer data warehouse on Snowflake. Raw data in, transformations in the silver layer, analysis-ready models in the gold layer, with Tableau as the reporting front end. Master data in a separate, reusable schema. Everything versioned via GitHub – database as code, every change reversible.

The Excel models? They're still read in. But only as input. A versioning logic in the database cleanly historises every forecast state – the versioning principle you know from expensive cube systems, just without the expensive cube system.

Where projects like this usually fail

Then came the hard part. Project controlling thinks in cash flow: what was actually paid, when, and for which trade? But the cost centres that could answer that question exist only in accounting – not in the payment data. Two views of the same money, with no shared key.

The solution lay in the multi-ledger structure of Dynamics: we developed a matching logic that links general-ledger postings, sub-ledgers, payment runs and bank movements, and robustly identifies which posting is actually cash-effective. This let us map the cost centres from accounting onto the payment flows – and feed the result straight back into project controlling.

Sounds self-evident. It isn't. In a complex ERP with partially inconsistent bookkeeping, this exact linkage is the point where most integration projects quietly give up and settle for “roughly right”. We didn't want roughly.

The moment it clicks

Today every line in the forecast has a counterpart in the general ledger. One to one.

What that means in practice: the finance team breaks down P&L and balance sheet at the push of a button, by any cost centre and any project component. Drill-down to the individual document, from every angle. The accounting view and the cash-flow view can be laid over one another – discrepancies explain themselves as an automated bridge, no longer through nightly detective work. Even the eternal date problem is solved: switchable between posting date and transaction date, no more mismatch.

And because the data model is documented in a semantically clean way, AI agents can talk to the data directly and generate analyses fully automatically. On top of an ERP that was previously written off as “poorly connected”.

The whole thing runs for around €400 in infrastructure cost per month, usage-based. For comparison: classic EPM suites like Workday Adaptive or Lucanet cost a multiple of that – and still wouldn't have done the actual linkage work.

What remains when we leave

A system that doesn't break in secret: if the ERP schema changes, the pipeline breaks in a controlled, visible way – instead of silently delivering wrong numbers. Regular security reviews keep the warehouse current. And because everything lives as code in GitHub, every change is traceable and reversible.

Three things you can take away

1. Your data foundation doesn't have to be perfect. Inconsistent bookkeeping and an old ERP aren't a knock-out criterion – they're the norm. What matters is extracting the maximum from them.
2. Preserve proven tools instead of replacing them. The Excel model lives on – but disempowered, as pure input. The logic now sits robustly and versioned in the database.
3. Prototype before specification. Especially in companies whose processes are still evolving, a prototype creates insight where a concept only creates paper.