Power BI for Finance: Manufacturer Case Study

Finance teams tend to be the first function in a growing business to hit the limits of spreadsheet reporting. The month end pack starts life as a helpful summary, then quietly grows into a fragile web of linked workbooks that only one person fully understands. When that person is on annual leave, close stops.

This case study covers a project we delivered for a mid-sized manufacturer in the North West with a turnover of around £28m. The finance director wanted to move away from a five-day month-end reconciliation into something closer to a continuous close, using Power BI for finance reporting rather than another round of Excel refactoring.

The challenge

The finance function was running on Sage 200 for the ledger, a bespoke MRP system for production and stock, and HubSpot for sales pipeline. Each system produced its own reports. The management accounts pack was assembled manually every month by exporting trial balances, dropping them into a master workbook, applying journals, and rebuilding the P&L and balance sheet in Excel.

By the time the pack landed with the board it was already two weeks out of date. Aged debt was tracked in a separate spreadsheet, cash flow forecasts in another, and budget variance in a third. Any question that crossed two of those views triggered another manual export.

What we built

We landed the Sage 200, MRP and HubSpot data into a small Azure SQL warehouse, modelled a proper finance semantic layer in Power BI, and rebuilt the month-end pack as four interlinked dashboards.

Management accounts dashboard

A full P&L by department and cost centre, with drill-through to transaction level. Prior year and budget comparisons are built into every measure using DAX time intelligence, so the FD can flip between actual, variance and rolling twelve month views without opening a new report.

Cash flow and working capital

Daily cash position pulled directly from the bank feed, combined with expected receipts from the sales ledger and expected payments from the purchase ledger. The dashboard projects thirteen weeks forward and flags any week where forecast cash drops below the covenant threshold.

Aged debt and credit control

A live aged debt view broken down by customer, sales rep and overdue bucket. Credit controllers now start each morning on this page rather than emailing themselves a fresh export.

Budget variance

Budget is loaded once a year through a governed Excel template, then variance is calculated automatically against the live ledger. Departmental owners can see their own numbers via row-level security without any risk of seeing someone else's.

How we handled the awkward bits

Finance reporting is unforgiving because the numbers have to tie. Three areas needed careful thought.

First, the chart of accounts. It had drifted over the years and had duplicate cost centres for the same activity in different regions. Rather than force a full restructure, we built a mapping table in the warehouse that groups accounts into a clean reporting hierarchy. The ledger stays exactly as it is; the dashboard just presents a tidier view.

Second, month-end journals. The pack had to reconcile to Sage after the month was closed, not the live position mid-month. We added a period status flag to the model so every visual can be locked to the closed period or run against the live one, depending on the audience.

Third, security. Departmental managers were given access to their own variance page but not the group P&L. Row-level security in Power BI, driven by an Entra ID group lookup, keeps that clean without maintaining a separate access list.

The outcome

Month end went from five working days to two. The board pack is now signed off on day three rather than day fifteen, and the FD spends her time explaining the numbers rather than assembling them. Credit control has knocked around eleven days off the average debtor days figure, largely because overdue invoices are visible the day they slip rather than at the next month end.

Perhaps more importantly, the finance team no longer dreads an ad-hoc question from the MD. When he asks why gross margin moved on a particular product line, the answer is two clicks away rather than a Tuesday afternoon of Excel work.

Why Power BI for finance?

Excel is not going anywhere, and we did not try to remove it. The finance team still uses it for modelling, scenarios and one-off analysis. What Power BI replaced was the repetitive, error-prone monthly assembly work. Everything that runs every month, in the same shape, now runs itself. Everything that needs a human brain still gets one.

If your month-end pack is starting to feel fragile, our Power BI consultancy and dashboard design services are the place to start, or get in touch for an informal conversation.

Frequently asked questions