Automated Intercompany Reconciliation with Multi-Currency Revaluation
โ Accountant
Seed: Ledgers: EntityA amount, EntityB amount, Currency, FXRateDate; Formula: Revalued = Amount * XLOOKUP(FXRateDate,RateDateRange,RateRange)Professional Guide
This model supports intercompany reconciliation across multiple entities and currencies, automating revaluation and balancing entries. Import trial balances from subsidiaries, normalize account codes with a mapping table, and compute net positions per counterparty. Apply FX rates per reporting date using XLOOKUP or INDEX/MATCH and calculate realized/unrealized FX differences. Flag mismatches by tolerance thresholds and generate suggested journal entries to clear intercompany imbalances. Produce a reconciliation report showing matched pairs, unmatched items and ageing of recon differences for controllers. Maintain a rates table with source and validated dates; for material balances, include commentary fields for audit evidence.