Challenge

  • Cash flow currency translation and related F/X impact analysis was completed using a complex Excel model. This model derived a weighted average YTD exchange rate for each foreign entity (i.e. there could be 5 Euro entities, each utilizing a different average exchange rate for purposes of translating cash flow activity). The requirement was to completely eliminate this Excel model.

Technical Solution

  • Business rules were written within the application to derive the required weighted average YTD exchange rate per base level foreign entity. This rate was then applied to a majority of the cash flow movement line items for translation purposes. The remaining line items (i.e. non cash D&A) utilized the standard YTD average rate (refer to the picture below).

picture5

  • YTD USD Net Income for the current period was derived by taking the prior period YTD USD Net Income (already translated from the prior period close process) and adding the current MTD local currency Net Income divided by the current month’s average rate.
  • Income divided by the current month’s average rate.
  • For each entity, a new rate account (AVGRateEntity – see bottom picture to the left) was calculated at the local currency YTD Net Income divided by the derived YTD USD Net Income for the current period.

picture6

  • Once this newly derived Average rate is calculated, the translation in HFM takes the Entity Currency value and multiples it by the AVGRateEntity to populate the Parent Currency value which is USD for all Cash Flow Accounts. User Defined attributes on the accounts were utilized to remove the need to hard code any accounts in the rules file.
  • This logic is executed for all foreign entity locations.

The solution has been running in production since 2009

Benefits

  • Single, standard global financial reporting and consolidation process
  • scalable consolidation system to support growth expectations with quicker, more streamlined integration
  • Detailed support of US GAAP financials and analysis
  • Established Process Management for more visibility into the close process
  • Significant reduction in Cash Flow preparation

Return to Client List