Lorenzo Reale

Power BI consultant · UK and EU

Work

Demonstration project

Month-End Management Pack

A monthly management pack that tells the partners why profit differs from budget, and checks itself against the ledger before it goes out.

  • Client Ashcombe Partners (fictional)
  • Audience Finance director, partners and service-line heads
  • Sources Ledger and timesheets in SQL Server, budget and commentary workbooks in Excel
  • Built with Power BI · SQL · Power Query · DAX · calculation group
Summary page: revenue, operating profit, utilisation and year-to-date profit, the bridge from budget to actual operating profit, the revenue variance by service line and the commentary
The bridge from budget to actual operating profit for October 2026, the revenue variance by service line and the finance director's commentary.

Synthetic data · fictional client · model, DAX and data generator on GitHub.

01

The problem

Ashcombe Partners runs four service lines across three companies. The monthly pack was assembled in Excel from ledger exports, so the close meeting spent most of its time agreeing the numbers before anyone could explain them. The partners wanted one answer at the start of every close: why is profit different from budget, and what do we do about it?

02

What it showed

For October 2026, the Summary page answers that question in one bridge, and the pages behind it explain each step.

  • The shortfall is billable time, not capacityOperating profit was £206k against a budget of £267k. Available hours were on plan, but Managed Services lost £39k of billable time: its utilisation has fallen every month since January, to 75.8% against an 82% target. The fix is resourcing in that line.
  • Price and subcontractors take the restAdvisory is still billing at 2025 rates, £20k below its rate card this month and £158k over the year. Projects lost gross margin to subcontractors, 22.7% against 28.6%, and Corven Group, at 10.8%, sits with Nyle Logistics below the 30% margin floor. Both clients need a pricing review.
  • The pack proves itself before it goes outThe ledger agrees with the trial balance to the penny once seven lines loaded twice by the extract are removed. Two items are left for finance to confirm: £8.4k of revenue posted with no client, and a laptop lease posted as a credit that flatters overheads.
Clients page: gross margin by client against the 30% floor, revenue by client, and the client register
Gross margin by client against the 30% floor, where the revenue comes from, and the register for a pricing review.
Checks page: the pack checks with their results, and the ledger against the trial balance by account
The pack against the ledger: the trial balance control, the lines loaded twice and the lines finance must confirm.
03

How it's built

SQL Server viewsExcel workbooksPower QuerySemantic modelReport
  • The views do the cleaningSQL views hand the model one row per journal line, amounts with their effect on profit and a client for every line. Power Query stays thin and folds back to the server, and only the finance workbooks are shaped in Power Query.
  • The revenue variance splits three ways, exactlyCapacity, billable hours and billing rates are valued so that they add up to the revenue variance to the pound, so leave and vacancies never show as lost sales.
  • The period is set in one placeA calculation group gives every measure its month, year to date and last-year versions, and the statement is read from a layout table, so its logic is written once.
  • Real problems, handled in the openThe files carry the problems real extracts do: a batch of purchase invoices loaded twice, manual journals with no client, a cost posted as a credit, depreciation with no budget line, and an account code typed with a trailing space. The Checks page counts each one.

Every figure on this page was recalculated from the source files outside Power BI, and matches the report.

04

The source

The semantic model, the DAX and Power Query, the report pages and the script that writes the data are on GitHub, all readable as plain text. The report itself, with its data, is a .pbix file in the project's release, with a PDF of every page.

All data in this project is synthetic. Ashcombe Partners is a fictional client created to demonstrate the work, and no real client data appears anywhere in it.

Next project Data Reconciliation Monitor

Get in touch

A close your board could read unaided?

If your month-end pack takes days of manual work and still invites questions, I'd like to hear about it.

Discuss a project