CASE STUDY · WHITE-LABEL MANUFACTURING AND SERVICES · GERMANY

Moving a finance team from reading numbers to interrogating them

Financial reporting arrived as a static Excel pack, weeks after the period it described. I rebuilt it as a governed Power BI model on Business Central — profit and loss, balance sheet and variance analysis, with proper time intelligence, multi-currency treatment and drill-through to the transaction behind any figure.

core financial reports rebuilt end to end

statement types — P&L, balance sheet, variance

governed model behind all of them

FX treatments — average and closing rate

None

refresh window to miss, via Direct Lake

Built with Microsoft Dynamics 365 Business Central · Microsoft Fabric · OneLake · Direct Lake semantic model · Power BI · DAX calculation groups · Row-level security

Role Lead — solution architecture, financial data modelling, re-implementation of accounting logic in DAX, report design, and validation against the ledger.

PROJECT AT A GLANCE

Business problem I solved

A static Excel month-end packAn interactive reporting layer

A week of rework per questionDrill-through in clicks

Accounting logic buried in spreadsheetsA governed DAX model

Numbers that arrived after the decisionFigures that tie to the ledger, live

Technology stack

Industry

White-label manufacturing and services — financial reporting

Location

Germany

Project Type

Financial reporting platform — Power BI on Business Central via Microsoft Fabric

My Role

Lead — solution architecture, financial data modelling, re-implementation of accounting logic in DAX, report design, and validation against the ledger.

Technology

Microsoft Dynamics 365 Business Central · Microsoft Fabric · OneLake · Direct Lake semantic model · Power BI · DAX calculation groups · Row-level security

Scale

4 core financial reports — P&L, balance sheet, variance and budget, transaction analysis — on one governed semantic model

Users

Group finance, controllers, business unit owners and the management board — access scoped by entity, brand and cost centre

SECTION 1 · WHAT BUSINESS PROBLEM I SOLVED

The finance team could tell you what the numbers were. Not why.

Reporting was produced as an Excel pack assembled after each month-end close. It was accurate and it was well made. But an Excel pack is a photograph: it shows the position at a moment, from one angle, and it cannot be asked anything. When a gross margin fell or an operating expense line overran budget, finding the cause meant going back to the ledger and starting again by hand.

Reporting was static, so every question meant rework

A figure in a spreadsheet cannot be opened. Investigating a single variance meant pulling a new ledger extract, rebuilding a pivot, and reconciling it back — hours of skilled work to answer one question, repeated for the next one.

The numbers arrived after the decisions

By the time the pack was circulated, the period it described was closed and the following one was well underway. Finance was reporting history rather than influencing outcomes.

Accounting logic lived inside spreadsheets

Statement structure, account groupings, sign conventions and currency treatment were embedded in formulas built up over years. That is a control weakness: a broken link or an extended range changes a reported figure silently, and only the person who built it would know.

The business is a white-label operation, and the pack could not show it

Revenue and cost sit against different client brands, contracts and cost centres. Whether a particular arrangement actually made money was a question the reporting could not answer without a bespoke analysis each time.

Analysts spent the month assembling rather than analysing

Qualified finance staff spent a significant part of every cycle collecting, formatting and reconciling. The scarce skill in the team was being consumed by production work.

What I was actually asked to deliver

Not a dashboard version of the pack. A financial reporting layer where a controller can start from a reported P&L line, follow it through the account hierarchy to the individual general ledger entry that caused the movement, and do it in the meeting where the question was raised — while the figures still tie exactly to the statutory ledger.

SECTION 2 · WHICH TECHNOLOGY I USED

Every choice made with reconciliation in mind

Finance has a requirement other departments do not: the output has to be exactly right, and it has to be provably exactly right.

Layer	Technology	Why it is there
Source	Dynamics 365 Business Central	The ERP and system of record. General ledger entries, chart of accounts, dimensions, budgets and exchange rates all originate here, so reporting reads the same data the statutory accounts are built from.
Storage	Microsoft Fabric, OneLake	Business Central data lands as governed tables in OneLake — one copy, no extracts, no spreadsheet staging step in between.
Data model	Direct Lake semantic model	The model reads OneLake tables directly. That means no import refresh cycle to schedule or miss, and reporting reflects the ledger without an intermediate copy that could drift from it.
Structure	Star schema with a reporting layout table	Ledger entries as the fact; account, dimension, entity and date as conformed dimensions; and a separate structure table that defines how a financial statement is laid out. This is what lets the same data produce a P&L and a balance sheet.
Financial logic	DAX measures and calculation groups	Statement logic, sign conventions, balance behaviour and variance rules written once as named measures. Calculation groups apply time intelligence across every measure without duplicating it.
Time intelligence	Fiscal calendar dimension	A financial calendar aligned to the company's fiscal year, supporting month-to-date, year-to-date, prior-year comparison and rolling periods on the accounting calendar rather than the civil one.
Currency	Rate tables with dual treatment	Average rate applied to profit and loss, closing rate to the balance sheet, as required when translating into a reporting currency. Transaction, local and group currency all remain available.
Security	Row-level security	Access controlled by entity, brand and cost centre, so a shared model can serve group finance and individual business owners from the same reports.
Delivery	Power BI with drill-through	Four reports, each able to hand off to transaction-level detail so an investigation never leaves the tool.

SECTION 3 · HOW I BUILT IT — THE METHOD

The least forgiving thing to model

Other reporting can be approximately right and still useful. A balance sheet that does not balance is worthless, and a P&L that does not tie to the ledger is worse than no report at all.

The approach

I worked from the statements backwards. Before any modelling, I sat with finance and established precisely how each line of their existing pack was defined — which accounts rolled into it, which subtotals were sums and which were calculated, where signs were flipped, and how each was translated into the reporting currency. That document became the specification, and later became the test.

Figure 1. Stage 4 is non-negotiable in finance work. A reporting layer that cannot be reconciled to the trial balance will not be used, no matter how good it looks.

How the data travels

The chain is deliberately short. Every additional copy of financial data is another place where reporting can drift away from the ledger, so there are as few steps as possible between the ERP and the report.

Figure 2. Because the model reads OneLake directly, there is no import cycle between the ledger and the report — and no window in which the two can disagree.

The accounting concepts built into the model

This is where a financial reporting project is won or lost. None of the following is a reporting feature — each is an accounting rule that has to be encoded correctly or the statements are wrong.

Concept	What it means	How it was handled
Chart of accounts hierarchy	Accounts roll up through nested groupings into statement lines, subtotals and calculated totals such as gross profit and EBITDA.	A dedicated statement structure table defining parent, sort order, row type and whether each line is a sum or a calculation — kept separate from the ledger itself.
Debit and credit sign convention	The ledger stores credits as negative, so revenue arrives with the opposite sign to how it is presented in a statement.	A sign multiplier held per account type, applied in the measures, so statements read naturally without altering the underlying data.
Movement versus balance	P&L lines are the movement within a period. Balance sheet lines are the cumulative position from inception to a date.	Account-type-aware measures: the same date filter produces period totals for income statement accounts and running balances for balance sheet accounts.
Opening and closing balances	Every balance sheet account carries a brought-forward position that must reconcile to the previous period's close.	Opening balance derived within the model rather than imported, so it is always consistent with the entries it summarises.
Financial dimensions	Business Central tags entries with dimensions such as cost centre, department, brand and project. These are how the business slices its results.	Modelled as proper dimension tables, making dimension-based analysis available on every measure rather than only where it was pre-built.
Fiscal calendar	The accounting year may not match the calendar year, and closing or adjustment periods sit outside normal date ranges.	A fiscal date dimension driving all time intelligence, so year-to-date and prior-year comparisons follow the accounting calendar.
Currency translation	Profit and loss is translated at the average rate for the period; the balance sheet at the closing rate on the reporting date.	Two rate treatments in the model, selected by account type, with transaction, local and reporting currency all retained.
Budget and forecast comparison	Budgets are held at a coarser grain than actuals — typically by account, dimension and period rather than by transaction.	Budget modelled as a separate fact against the same conformed dimensions, so actual and budget compare correctly at every level.
Variance favourability	A cost below budget is favourable; revenue below budget is unfavourable. The same arithmetic means opposite things.	Favourability derived from account type, so variances are presented with the correct business meaning rather than a raw signed difference.

Why one ledger needs two behaviours

The single most misunderstood point in financial modelling is that a profit and loss statement and a balance sheet are not two reports over the same data. They are two fundamentally different aggregations, and a model that treats them the same will produce a balance sheet that silently resets every period.

Figure 3. Get this wrong and the reports still render. They are simply incorrect, in a way that is easy to miss until an auditor finds it.

Currency, handled the way accountants expect

Translating into a reporting currency is not one calculation applied everywhere. Income and expenses are earned across a period, so they are translated at the average rate for that period. Assets and liabilities exist at a point in time, so they are translated at the closing rate on the reporting date. Applying a single rate to both is one of the most common errors in self-built financial reporting, and it produces a balance sheet that does not balance.

Figure 4. Encoding the rule in the model rather than in a spreadsheet formula means it cannot be applied inconsistently between one report and the next.

Variance that means what a manager thinks it means

Variance reporting compares actual results against budget, forecast and prior year. The arithmetic is trivial. The business meaning is not: the same negative number is good news on a cost line and bad news on a revenue line, and a report that presents both identically will be misread.

Figure 5. Variance is reported in both absolute amount and percentage, on the same comparison bases finance already used in the Excel pack.

From a reported figure to its cause

This is the capability that changed how the team works. A statement line is no longer an endpoint. It is the first step of an investigation that stays inside one tool and takes seconds rather than a working day.

Figure 6. Drill-through is only trustworthy if the detail reconciles to the summary. Because both come from one model, they cannot disagree.

SECTION 4 · WHAT THE BUSINESS GOT OUT OF IT

The test is whether the team spends its month differently

Not whether the pack looks better — whether decisions get made on better information.

Finance moved from producing numbers to interrogating them

Investigating a variance no longer means rebuilding an analysis from the ledger. The question is answered in the report, during the discussion that raised it. Root cause is reached in clicks rather than in a working day.

Outcome · faster investigation, shorter decision cycles

Skilled staff stopped assembling spreadsheets

The manual production cycle behind the Excel pack was removed. Qualified finance people now spend that time on analysis, commentary and business partnering, which is what they were hired to do.

Outcome · analyst capacity returned to analysis

Accounting logic became a controlled asset

Statement structure, sign conventions, currency treatment and variance rules moved out of spreadsheet formulas and into a governed model where each is defined once, named, and visible. A silent formula error can no longer change a reported figure.

Outcome · a genuine control improvement, not just a reporting one

Self-service without losing consistency

Business owners and controllers can slice results by entity, cost centre, brand and period themselves — but every one of them is reading the same definitions from the same model. Self-service normally costs consistency; here it did not, because the logic sits below the report rather than inside it.

Outcome · exploration for many, one version of the truth

White-label profitability became visible

Because financial dimensions are modelled properly rather than pre-aggregated, results can be cut by client brand, contract and cost centre on any measure. Whether a particular arrangement earns its margin is now a question the reporting answers directly instead of a bespoke analysis each time.

Outcome · margin visibility at the level the business is actually run

Reporting that reconciles, and can prove it

Every figure ties to the trial balance, and any summary can be opened to the entries behind it. That matters for month-end review, for management confidence, and for anyone who has to defend a number externally.

Outcome · auditable reporting, traceable to source

No gap between the ledger and the report

Reading OneLake directly removes the import cycle, so there is no refresh window to overrun and no intermediate copy that can drift out of step with Business Central.

Outcome · current figures, fewer moving parts to fail

SECTION 5 · SUMMARY

In business terms, and in technical terms

In business terms

A static Excel month-end pack was replaced by an interactive financial reporting layer covering profit and loss, balance sheet and variance analysis.
Finance moved from reading reported numbers to investigating them, reaching root cause in clicks rather than in a working day.
Accounting logic left the spreadsheets and became a governed, documented model — a control improvement as well as a reporting one.
Results can be cut by entity, cost centre, brand and contract, making white-label profitability visible at the level the business is run.
Multi-currency reporting follows proper translation practice, so consolidated figures hold up to scrutiny.
Every reported figure reconciles to the ledger and can be opened to the transactions behind it.

In technical terms

Dynamics 365 Business Central as the source of record — G/L entries, chart of accounts, dimensions, budgets and exchange rates.
Ledger data held as OneLake tables in Microsoft Fabric, read by a Direct Lake semantic model with no import refresh cycle.
Star schema around G/L entries, with account, financial dimension, entity, currency and fiscal date dimensions.
A separate statement structure table driving hierarchy, subtotals, calculated lines and sort order.
Account-type-aware measures producing period movement for P&L and cumulative balances for the balance sheet.
Sign conventions applied through the model rather than by editing source data.
Dual FX treatment — average rate for P&L, closing rate for balance sheet — selected automatically by account type.
Calculation groups delivering MTD, YTD, prior-year and rolling comparisons across every measure.
Budget modelled as a separate fact at its own grain against conformed dimensions.
Row-level security by entity, brand and cost centre; drill-through to G/L entry on all four reports.

My role. I led the project end to end — solution architecture, the financial data model, re-implementation of the accounting logic in DAX, report design, and the reconciliation work that proved every figure against the ledger before release.

What this project demonstrates. That I can read a finance team's statements closely enough to rebuild the accounting behind them — movement versus balance, sign conventions, fiscal calendars, currency translation, variance favourability — and deliver reporting that is both genuinely self-service and exact enough to reconcile. Most reporting work needs one of those. Finance needs both.

NEXT PROJECT

Qlik Sense → Power BI Migration

Canada · Government

View project