Automated Financial Consolidation for Apex Holdings

Case Study

About

This case study showcases the design of an automated financial consolidation solution using Microsoft Fabric. The implementation focuses on standardizing multi-entity financial data, unifying disparate Charts of Accounts, and applying finance-grade currency conversion logic to produce a single, audit-ready source of truth. The solution enables accurate consolidated P&L reporting and executive-level financial visibility through a Power BI Direct Lake semantic model

Challenge

Fragmented Financial Systems – Financial data was spread across Parent, Europa, and Mumbai entities, each operating in different currencies and source systems, making consolidation complex and time-consuming.

Inconsistent Charts of Accounts – The same expenses and revenues were recorded under different account names and codes across entities, preventing straightforward aggregation.

Manual, Time-Intensive Processes – The finance team spent nearly 20 days every month manually mapping accounts, fixing signs, and converting currencies in Excel, significantly delaying month-end reporting.

Lack of Standardized Currency Reporting – Absence of consistent historical exchange rate logic resulted in unreliable cross-region financial comparisons.

Limited Executive Visibility – Leadership lacked a single, trusted P&L view, reducing confidence in financial reporting and slowing strategic decision-making.

Solution

Automated Data Ingestion with Microsoft Fabric – Built scheduled Fabric pipelines to ingest multi-entity financial transactions and retrieve historical currency exchange rates via external APIs covering the past three years, eliminating manual FX maintenance.

API JSON Processing with Warehouse Stored Procedures – Implemented Warehouse stored procedures to parse raw JSON responses from the currency API, extract exchange rates into structured tabular formats, and persist them in governed currency dimension tables.

Centralized Fabric Warehouse – Designed a scalable Warehouse layer to standardize and store financial data, serving as a single source of truth for consolidation and reporting.

Chart of Accounts Standardization – Unified inconsistent account codes and naming conventions across Parent, Europa, and Mumbai using a mapped Chart of Accounts.

Finance-Grade Currency Normalization – Applied historical FX conversion logic to align all transactions to USD while retaining local currency values for regional analysis.

Direct Lake Semantic Model & Power BI – Exposed consolidated data through a Direct Lake semantic model, enabling high-performance Power BI reporting with a dynamic currency toggle and a consolidated P&L matrix covering Revenue, Gross Profit, OpEx, and Net Income.

Business impact

Accelerated Month-End Close – Reduced financial consolidation effort from nearly 20 days per month to a near-real-time process, enabling faster and more reliable reporting.

Improved Data Accuracy & Auditability – Eliminated manual Excel-based mappings and currency conversions, significantly reducing reconciliation errors and improving audit confidence.

Single Source of Financial Truth – Delivered a centralized, standardized P&L view across all entities, ensuring consistent financial metrics for leadership and stakeholders.

Enhanced Executive Visibility – Enabled timely insights into Revenue, Gross Profit, OpEx, and Net Income across regions, supporting data-driven decision-making.

Scalable & Future-Ready Platform – Established a Microsoft Fabric–based architecture that can easily onboard new entities, currencies, and reporting requirements without additional manual effort.

A Microsoft Fabric pipeline orchestrates historical currency exchange ingestion by dynamically setting date parameters, calling an external FX API, and invoking a Warehouse stored procedure to parse JSON responses and persist exchange rates in tabular form. The pipeline executes end-to-end and supports multi-year historical currency normalization

This stored procedure transforms raw JSON responses from a currency exchange API into structured, analytics-ready exchange rate records. It extracts the effective rate date and reporting currency (USD) from the API payload, parses individual currency rates (EUR and INR) from the JSON structure, and normalizes them into BaseCurrency → USD conversion rates by inverting the API values. The processed rates are then inserted into a warehouse table, enabling reliable historical currency conversion and consistent downstream financial reporting.

This fact table represents the unified financial ledger across all entities (Parent, Europa, and Mumbai). It standardizes source transactions using a common Chart of Accounts, applies sign logic for revenue and expenses, and stores both local currency amounts and USD-converted values using historical FX rates. The table serves as the single, trusted source for downstream P&L reporting, currency toggling, and executive-level financial analysis in Power BI.

This Power BI dashboard presents an automated financial consolidation view for Apex Holdings, combining financial data from Europa, Mumbai, and the Parent entity into a single report. It provides a clear entity-wise breakdown along with an overall consolidated total, enabling quick comparison and analysis. The currency slicer allows users to dynamically switch between USD, EUR, and INR, ensuring flexible financial reporting.