Reporting Frequency Increased
Financial KPI dashboard moved from quarterly to monthly - same build effort (now 30 minutes instead of a full day). Board sees current-period performance, not last quarter's.
Case study
Quarterly to Monthly: Finance KPI Dashboard Migrated from Excel to Power BI
A manufacturing group's CFO relied on a 200-row Excel financial model rebuilt manually each quarter. The build took a full day, version control was a recurring problem, and the board saw financial KPIs three months behind the operating period. Power BI replaced it with a daily-refreshing dashboard that produces the same outputs in 30 minutes.
Dashboard Rebuild Time
Full month-end financial dashboard refresh reduced from 1 full working day of manual Excel work to 30 minutes of review after an automated overnight data pull.
Group Entities Consolidated
6 operating entities with intercompany transactions consolidated into group P&L, cash flow, and margin views automatically - eliminations applied in DAX, no manual adjustments.
Version Control Incidents
Zero since deployment. Shared Excel model had regular version conflicts when multiple team members updated different sections. Single Power BI dataset with governed refresh cycle eliminated the problem.
Board Pack Preparation
Finance section of the board pack generated as a PDF from Power BI in 3 minutes - same layout as the previous Excel pack. Board pack preparation time for the CFO reduced by 4 hours per month.
First-Year ROI
190% - CFO and finance team time recovered on dashboard preparation, board pack generation automated, and one cost-overrun identified a full month earlier than the previous quarterly cycle allowed.
The Problem: Financial KPIs on a Three-Month Lag
This manufacturing group operates six entities producing components for the automotive and aerospace sectors. The CFO managed financial reporting through a master Excel workbook that consolidated figures from six entity-level P&L files, a cash flow model, a capital expenditure tracker, and a budget variance analysis.
Rebuilding the master workbook was a full-day exercise that the finance manager ran quarterly. It involved opening each entity file, manually copying the updated figures into the consolidation sheet, running the intercompany elimination process by hand, and checking the balance sheet ties. Version conflicts between team members who had updated different sections of the model were a recurring problem - at least twice a year, a saved-over version resulted in re-entering several hours of work.
The board saw financial KPIs three months after the operating period had closed. A cost issue that developed in January was visible to the board in April. By that point, the corrective action window had largely passed.
Two Structural Problems That Could Not Be Patched
Manual Consolidation Created Version Risk
Six entity files updated by different finance staff, then manually consolidated into a master workbook by the finance manager - every consolidation cycle carried the risk of overwriting valid work, applying wrong period figures, or missing an intercompany transaction. The risk was structural: it lived in the manual process, not in a fixable formula.
Quarterly Frequency Was a Business Decision Constrained by the Process
The CFO wanted monthly financial KPI reporting. The finance manager estimated monthly would require two days of additional work every month - on top of an already full schedule. The frequency was set by what the manual process could absorb, not by what the business needed.
What We Built
We designed a Power BI financial reporting solution with three layers: an ERP data integration layer, a multi-entity consolidation data model, and a CFO-facing dashboard with board pack export.
The ERP exports nightly data packages - trial balance, cost centre ledger, and intercompany transactions - to a shared folder. Power Query connections pick up these files on a daily refresh schedule. All six entity datasets are loaded into a single Power BI data model with a consolidation rules table that handles intercompany eliminations in DAX.
The dashboard has five pages: group P&L, margin by product line, cash flow and working capital, capital expenditure vs budget, and entity-level comparison. Slicers allow the CFO to switch between consolidated group view and individual entity view without separate reports.
The board pack PDF export reproduces the layout of the previous Excel pack - same section order, same chart formats. The CFO reviews the PDF and attaches it to the board papers email. Total month-end task time for the finance section of the board pack: 30 minutes, down from a full day.
Dashboard Capabilities
Multi-Entity Consolidation
Six entities consolidated with intercompany eliminations applied automatically in DAX. Switch between group and entity view with a single slicer - no separate entity reports.
P&L with Prior Period and Budget Comparison
Month, quarter, and YTD actuals against budget and prior year. Variance percentages flagged with conditional formatting for lines above materiality threshold.
Margin by Product Line
Gross and operating margin by product category and customer segment. Enables margin conversation by product rather than blended group level.
Cash Flow and Working Capital
Operating cash generation, debtor days, creditor days, and stock days visible as trend lines - the working capital metrics the CFO monitors for covenant compliance.
CapEx vs Budget Tracker
Capital project spend vs approved budget, committed costs, and forecast-at-completion. Finance committee uses this page for monthly capex review.
Board Pack PDF Export
Pixel-perfect PDF reproducing the previous board pack layout generated in 3 minutes from live data. Board receives the same format every month, on time.
After Go-Live: What Changed
The board now reviews monthly financial KPIs instead of quarterly ones. The CFO makes the same statement every month: the figures are current, the comparison is to the right period, and the consolidation has not been touched manually since the last rebuild - because there is no manual rebuild.
Four months after deployment, the margin by product line page showed a cost trend in one product category that had been developing for two months. Under the previous quarterly process, this trend would have been visible to the board six months after it started. The CFO raised it with the operational team at the monthly review - one month into the visible trend. A purchasing decision was changed, and the margin impact was contained.
Version control incidents in the finance team stopped because the Excel consolidation model is no longer the live reporting system. It is still used for scenario modelling - its proper use case - and no longer carries the risk of being overwritten by a colleague during month-end.
“We used to see our financial KPIs three months after the fact. By the time the board saw a problem, there was usually nothing left to do about it. Now we're looking at monthly numbers and catching issues when we can still respond. The version control problems that used to cost us hours have completely gone away too.
Tech Stack
Power BI Service - daily-refreshing cloud dataset with six-entity financial data model and governance controls
Power Query - nightly ERP export processing; CSV transformation and relationship building into the consolidated data model
DAX measures - intercompany elimination logic, consolidated KPI calculations, and budget variance measures
Consolidation rules table - entity-pair elimination mappings; updated by finance without model rebuild
PDF board pack export - pixel-perfect board pack reproduction from live Power BI data; 3-minute generation time
Frequently Asked Questions
How does Power BI connect to the ERP system to pull financial data?
The ERP exports a structured data package to a shared network folder each night - trial balance, cost centre ledger, intercompany transactions, and budget data in fixed-format CSV files. Power Query connections in the Power BI dataset pick up these files on a daily refresh schedule, transform and relate them to the data model, and make the figures available for the dashboard measures. No live ERP connection is required - the nightly export is the data contract. This approach isolates the Power BI build from ERP version changes and keeps the refresh reliable.
What happened to the existing Excel financial model?
The Excel model was not deleted - it became the source documentation for the KPI definitions and calculation rules. Every measure in the Power BI model was validated against the Excel output before the dashboard went live. The model is still maintained by finance for scenarios and what-if analysis that benefit from Excel's formula flexibility. Power BI handles the reporting and distribution layer; Excel handles the modelling and planning layer. This is a common and sustainable division of responsibilities.
How do you handle intercompany eliminations and consolidation in Power BI?
Intercompany transactions are flagged in the ERP export by an entity pair code. The Power BI data model includes a consolidation rules table that maps which entity-pair combinations should be eliminated for group reporting. DAX measures calculate both consolidated and entity-level figures from the same data model - switching between group view and individual entity view is a single slicer selection in the dashboard. Eliminations are applied in the measure logic, not as a separate data transformation step.
Can the Power BI dashboard replace the board's Excel pack entirely?
For most board members, yes - the interactive dashboard with drill-down capability provides more insight than a static Excel file. For board members who prefer a printed document, we configured a pixel-perfect PDF export from Power BI that reproduces the exact layout of the previous Excel pack - same section order, same chart formats, same commentary sections with manual text input. The PDF is generated in about 3 minutes at month-end and attached to the board papers email.
Next step
Want similar results in your operations?
Book an Automation Opportunity Assessment. We map workflows and scope a practical build plan.
- No Onsite Visit Required
- No Technical Specification Required
- Assessment Before Commitment
- Clear Scope Before Work Begins
Questions before you book? Speak with our team at +1 385 386 3860
