Sunday, 2 August 2015

...FDM reclassifications with logic accounts

I was recently discussing (with my wife) about the various HFM reclassification journals (VAT, Corporate tax, Cash/Overdraft) and it occurred to me that the easiest way to complete this task is with the proper configuration and design of the FDM application.

When designing an FDM – HFM solution, my understanding is that FDM should be used to replace all the manual, time consuming, non-value-added data-management steps of the ETL process while moving data from the ERP to HFM.

This design has 2 main guidelines:
  • The extract query from the ERP system should not exclude any of the data. The main reason of this guideline is to prevent the exclusion of any future ERP changes to be extracted in the extract file.
  • The FDM application should be configured in this way to filter, create and transform the data before loading a complete set of data in HFM.
The simplest example of such a reclassification is the Cash / Overdraft in Bank reclassification. Assuming that you have a number of ERP accounts that gather the cash in bank balances, the mapping to the HFM accounts will depend on the sign of the sum of all these accounts. If the sum of these accounts is positive then the value should be mapped to the Cash in Bank. However, if the sum of these accounts is negative then the value should be mapped to the Overdraft in Bank.
In order to configure FDM to complete such a mapping, you need to use a combination of  logic accounts and "Like" mappings.

The process is quite simple.
  1. First, you need to create a logic account to sum all the cash in bank erp accounts. You can  call the account CASHINBANK
  2. Then you need to update the explicit mappings of all these ERP accounts to IGNORE. This will avoid loading twice the same values.
  3. Finally, you need to create a Like mapping to define that if the CASHINBANK should be mapped to Cash in Bank if it is positive or should be mapped to Overdraft in Bank if it is negative.
This solution is tested in an FDM classic application. I am sure that a similar design can be implemeted in FDMEE.

Obviously, I am not a guru of FDM (such as Francisco), however I believe that the majority of the FDM users do not use more than 60% of FDM's functions. Small and quick changes as the one mentioned above will not cost a lot of money and will have a great impact on Finance users.

Good luck.



Hi all, Just to say that I am still around since a friend told me yesterday that I do not write any more. Actually, I was thinking tha...