Transforming multi-dimensional reporting with Power BI Dashboards

Following a change in ownership, we supported the finance team of a pub operator with over 1,500 sites to transform its forecasting and reporting processes to deliver clarity, efficiency, and confidence to both management and private equity stakeholders. We introduced a streamlined and integrated solution which enabled management to view the business at a summary level, but then to drill into a concept level of detail that gave them real insight at a brand, geographic, and demographic level.

The Client’s Challenges

Following a majority investment from a US-based private equity house, the client faced several critical challenges:

• Fragmented and Inconsistent Reporting: Existing reports lacked consistency, depth, and usability, with insights buried in complex spreadsheets drawing data from multiple systems.
• Volume of data: Site-level data existed across a portfolio 1,500+ pubs on a daily basis and across different systems, which was beyond the capacity of Excel for an extended timeframe.
• Multiple portfolio dimensions: The business needed to cut performance data across brand, geography, demographics, cohorts, and trading concept, but lacked a unified view.
• Demand for Transparency and Accountability: The private equity sponsor required increased transparency, while management needed tools to drill into specific outlets and support divisional accountability.

These issues threatened to prevent performance monitoring and insight into the business, and so delay decision-making, and erode confidence among stakeholders.

Our Solution

Building an Insight-Driven Reporting Tool in Power BI

We began with a discovery and design phase, collaborating closely with the client’s finance and operations teams to map current processes and define ideal outputs. Our approach included:

• Power BI Dashboard Mock-Ups: Developed initial mock-ups and data flows to align expectations across stakeholders.
• Efficient Data Extraction: Built repeatable processes using Power Query to extract and manipulate data from multiple sources, including management accounts and EPOS
• Consistent Historical Data Cube: Constructed a data cube that could be updated with minimal effort, forming the foundation of a “one source of truth” across the business.
• Comprehensive Reporting Tools: Delivered weekly and monthly Power BI dashboards covering financial and operational performance across all outlets and dimensions.

Power BI Dashboard features:

• Analysis by region, outlet type (e.g. premium, tenanted), demographic indicators, and trading location (e.g. railway station, airport etc.).
• Views by cohort (e.g. current year acquisitions) and comparisons against prior year and budget across 13 periods.
• Interactive lookup functionality for filtering by outlet, P&L line item, and cohort to identify key performance drivers.
• Added a “like-for-like” toggle to account for recent acquisitions and disposals.
• Enabled users to switch between allocated and unallocated overheads, supporting divisional accountability.

The Impact

• Provided insight and transparency: Dashboards were built and delivered within six weeks and were embedded into internal reporting processes, published online for management and investor stakeholders.
• Consistent and Credible Reporting: The underlying data cube acts as a single version of the truth, enhancing granularity, timeliness, and accountability across the Group.
• Clarity and Actionability: Management can now analyse outlet-level profitability with clarity, while investors have access to the dimensions and insights needed to monitor performance across a diverse and evolving estate.