Overview
A professional services firm had grown to the point where their month-end financial close was taking 5 business days — mostly because the finance team was manually consolidating data from three accounting systems into a master Excel workbook, then copying figures into separate presentation files for the board. Errors were common, and version control was non-existent.
The engagement delivered a full Power BI financial reporting suite: data ingestion from the accounting systems via Power Query, a robust DAX-based financial model handling intercompany eliminations, and a suite of reports covering the P&L, balance sheet, cash flow statement, and a management summary page. Month-end close dropped from 5 days to same-day.
Key Deliverables
- Profit & Loss report with actuals vs. budget, prior year comparison, and variance analysis
- Balance sheet with rolling snapshots and period-over-period movement analysis
- Cash flow statement (direct method) with waterfall visualisation
- Management summary page with 8 headline KPIs and conditional formatting against thresholds
- Drill-through from any P&L line item to the underlying transactions
- Automated month-end refresh triggered by the finance team with a single button click
Challenges & Solutions
The three source accounting systems used different chart-of-accounts codes for the same nominal accounts. I built a mapping table in Power Query that standardised all codes into a unified taxonomy before loading — meaning the model was completely agnostic to the underlying source system. Adding a fourth system in the future requires only updating the mapping table, not rebuilding the model.
Intercompany eliminations were handled through a dedicated elimination dimension that flagged intercompany transactions. A set of DAX measures then conditionally excluded or included these based on the reporting context, supporting both consolidated and entity-level views from a single model.