Overview
Client: Multi-national technical services company with 35+ legal entities across multiple jurisdictions. Significant intercompany (I/C) activity where one entity provides services to another within the corporate group.
Situation
Under GAAP, intercompany revenue and costs must be identified and eliminated from consolidated financial statements; intercompany receivables and payables must net to zero. Data resided in four different accounting systems, creating fragmentation and manual, error-prone reconciliations
Objectives
- Consolidate 1/C revenue, cost, AR, and AP data from four ERP/accounting systems.
- Match trading partners (entity A …. entity B) and pair transactions at the Project ID level.
- Automate GAAP-compliant eliminations and generate net-to-zero AR/AP checks by entity pair.
- Produce audit-ready tie-outs and variances.
Approach
- Power Query Integration: Extracted from four source systems;
standardized schemas (dates, amounts, currency codes, entity IDs). - SQL Matching Logic: Deterministic joins on Project ID.
- Business Rules Layer: Directional signage (revenue vs. cost).
- Excel + VBA Orchestration: One-click refresh, detail transactions.
Days Hours
Close cycle acceleration
Accuracy
Audit-ready
Tech stack: Excel (Power Query + VBA), SQL (views/ stored procedures), cross-system entity & vendor/customer mappings, currency tables.
