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 I/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.
Process Flow
From multi-ERP extracts to GAAP-compliant consolidations.
Source System
Transform & Match
Business Rules
Outputs
- System Extracts → 4 accounting systems,detailed transactions
- Standardize → Entity ID, Trading Partner (ICP)
- Match Engine (SQL) → Pair Revenue & Expense
- Rules → Adjust for FX
- Variances → Detail by Project ID
- Outputs → Summary by Entity/ICP
Highlights
- One-click in Excel refreshs schedules in minutes.
- Variance Summary by Entity/ICP.
- Repeatable, auditable process aligning with GAAP consolidation requirements.