As with any application, HFM has a list of pros and cons. Let’s assume HFM is an asset to your organization. In accounting terms, the debits outweigh the credits. However, data entry in a web-based application can be an inefficient process. For any organization that has at least a moderate volume of journals, the frustration of the HFM journal module is likely apparent. Not surprisingly, one of the most common requests I hear during implementations is the ability to enter non ledger data into HFM through the finance & accounting favorite – Excel.
For those of you familiar with my blog, it’s not a surprise that this post will highlight how FDM can address this request. To be fair, there are other options for generating HFM journals from Excel. One approach that we have also developed leverages custom API coding. With this method, various HFM client components are required to be installed on the PC of the user. This can be a viable option and as with any software solution, the final decision is going to be driven by a mixture of several criteria. For this post, I am going to focus on the use of FDM to address this often requested item.
FDM includes an out of the box feature that allows a user to generate and post an HFM journal using an Excel workbook which can be customized to address myriad business requirements. The Excel workbook is imported/posted into FDM using the journal functionality of the FDM application. Once imported/posted into FDM, journals are processed using the standard workflow. When the journal is exported to HFM, FDM generates an actual HFM journal and can even post it. The use of the workflow process allows many of the key benefits of FDM to be applied including:
- The ability to apply FDM mapping tables allows journal data to be entered in general ledger codes or HFM member names. The former could be useful as detailed journal entries are often required to be pushed down to the general ledger or shared with other reporting systems. The more granular information would allow data to be pushed back to the ledger without requiring the HFM metadata to include the additional detail. If the latter approach is preferred, a combination of FDM event scripts and maps can be used to allow journal entries to be entered in the template in HFM dimensionality and to simply pass through FDM.
- Intersection Validation that occurs as part of the Validate workflow step
Another key benefit is the ability to embed VBA (Visual Basic for Application, i.e., Excel Macros) that is used by FDM to accept/reject the processing of the journal. Within this coding, various validations can be included that could not otherwise be controlled by HFM. For example, the custom top member associated with an account may be a parent called AllCostCenters; however the business requirements may be that only center corporate cost centers are able to post to the account. The HFM metadata may be able to address this but the long-term maintenance of that level of granularity may simply not viable. In this example, this more granular “custom top member” could be managed within a table in Excel and used to drive the validation.
Ok, so if you’ve gotten here, you’re interested in leveraging this functionality. Your next questions are likely:
- Where do I get a copy of this template?
- How do I set up FDM to do this?
For the former, extract the JournalWithUpCheck.xls file from the Templates archive in the SharedComponents folder of the FDM install directory (%hyperion_home%\products\FinancialDataQuality\SharedComponents). For the latter, I’m glad you asked.
The set up for FDM is fairly easy. First the integration options for the HFM target system adaptor need to be configured.
- Enable Journal Loading – Checked (Required)
- Journal Status – Select the status that FDM should process journal into HFM.
- Journal Balancing Attributed – Same as HFM; determined on a case by case basis
While there are other integration options associated with FDM journal processing, the above are most relevant.
The second change that needs to be made is for each Location through which journals will be processed. The Data Value field on the general tab of the location is used to specify the adjustment member of the HFM value dimension in which the journal should be created. Each of the 4 Value dimension adjustment members (<Entity Curr Adjs>, <Parent Curr Adjs>, [Parent Adjs], [Contribution Adjs]) are available for FDM to post journals. When/if certain adjustment members of the Value dimension should be used are beyond the scope of this posting. For this posting, let’s assume that all journals are occurring at local currency. In this instance, the data value field entry will be:
- <Entity Currency>;<Entity Curr Adjs>
The first member is the value dimension member to which FDM will load non journal data and the second is the member to which journal adjustments will be posted.
Once the above changes are made, the journal entry template can be imported/posted using the Journal function under the Activities menu and then loaded HFM by completing the Validate and Export workflow steps.
As you can see, these relatively simply changes quickly addresses a common end-user complaint and hopefully is something that you will find valuable in your organization.
One final question that you may still have is, how much does this cost? The short answer is nothing. The template and this functionality is all included (and supported) by Oracle when you purchase FDM. The more thoughtful answer is that you may incur consulting expense depending on the below factors:
- The level of skill within your organization to write/maintain VBA (should you choose to use it)
- The complexity of the requirements for the template
- The desire to post in HFM dimensionality and leverage pass-through mapping while not impacting maps that are applied to general ledger data processed through the location
I hope that this posting will enable you to leverage your FDM system more fully with little or no additional investment. As always, if you have questions or comments you are encouraged to post them here.