Search This Blog

Wednesday, October 25, 2017

Can I get a little Java with my Jython?

Java and Jython

Quick Update:  I started a new project last year around August and I have been 100% devoted to getting it finished successfully for the client.  I try to take notes of blog topics while going through my work week so I can catch up on here.  Sorry, that I have lost touch this past year. 


Java and Jython go together like Peanut Butter and Jelly!  They actually help complete one another since java has robust libraries and Jython is a logical scripting language. On this project, we did a couple of really cool things. 

  • Built an FDMEE adapter to extract, map, and load Journals, with all the details, into the new HFM application
    • Journal Label, Description, Line Item Descriptions, Approved By, Created By, Balance Type, Journal Group, etc. 
  • Built Pre-Import process for an FDMEE Essbase load that automates HFM Consolidations and Translations for a given Entity Member List. 
    • This process also reads the Application Status Mode and switches it to Admin only, Logs out all users, notifies team via email, and once complete switches the App Mode to allow everyone to log in. 
    • The Entity Member List is one created within HFM that we reference so that it is dynamic and updates on the fly as the users maintain metadata.
Just summarizing the process in two bullet points doesn't give it justice.  We just save a tremendous amount of time and effort to migrate Journals and the client during flash reporting.  During the build, we leveraged the HFM Java Developer guide and a little ingenuity on the FDMEE to identify the API calls that are necessary to build the complete process. 

Tuesday, August 9, 2016

Account Reconciliations

Account Reconciliations within Hyperion

Overview

Monthly account reconciliations are something all Accountants love with a passion.  Usually, they are performed within Excel and posted to an internal SharePoint site for review and approval.  What does the process look like when performing reconciliations? Well, it depends on which style of reconciliation that the accountant is performing.

Reconciliation Types:

  • Explained Balance: We are describing the transactions from the sub-ledger and ledger that make up the ending balance of the account. This process usually contains data from historical periods and current activity. 
  • Balance Comparison: Requires loading balances from two different systems that need to have the same values for data integrity.  
    • Types of Comparisons:
      • Sub-Ledger to Ledger
      • Sub-System(AR/AP) to Sub-Ledger
      • Bank Transactions to Ledger Bank Accounts
      • Ledger to Consolidation Tool (HFM)
      • HFM to Essbase
      • etc...

Manual Reconciliation Process

Let's take a look at the Explained Balance for the sake of this discussion. Typically, an end-user is required to prepare the explained balance of the account for a given fiscal period and fiscal year.  The data and supporting documentation necessary for the explanations can reside in various places (sub-ledger, ledger, sub-system).   The accountants run reports and query the various source systems to compile all the necessary data.  Once the data is compiled the real fun begins.  The accountants start filtering and sorting the data to determine items that are not pertinent to the end result.  Once the accountants finish explaining the information, the file is saved and uploaded to SharePoint or emailed for review and approval.  This process can be relatively brief for some accounts with low activity or very time consuming for accounts that have thousands, if not hundreds of thousands, of transactions.

Hyperion Account Reconciliation Manager Setup

Leveraging Hyperion Account Reconciliation Manager(ARM) helps companies build a centralized repeatable process, with built-in approval process flow.  There are various ways to break down a definition of a reconciliation.

  • Business Unit - Account - Department - Product (Very granular)
  • Business Unit - Account - Department
  • Business Unit - Account 
  • Business Unit - Wild Card the Account
  • Total Company - Account
  • Total Company - Wild Card the Account (Very high level)

We usually see the definition of a reconciliation fall somewhere in the middle, but it is based on the controls the company has for each reconciliation. Within ARM we can define and maintain these reconciliations with all the workflow user and reviewer assignments.  Each reconciliation can have a specific priorities and due dates depending on that account combination.  There are setting that also enable Auto Reconciliation to occur if certain criteria are met.


A client might do business in other currencies for which there will be an additional recon. As long as the data supports the additional currency ARM will handle the recon process.  Hyperion ARM will naturally break the Monetary balances into their individual ISO currency.  Each currency balance must then be explained per the account reconciliation criteria specified.


Additional detail can be captured within the Explained Balance tab.  Within the Transaction Detail tab, attributes that were setup can be captured, optionally flagged as required, to support the explanations.  I once had an Accountant best describe the proper use of the Transaction Detail with the five W's (Who, What, When , Where, and Why).  If we can capture those details then everyone that reviews the recon will have the necessary information to make decisions. 



With one client, we even set up additional attributes to help track various properties and enable better reporting for internal audit.  This allowed us to track and report on various styles of reconciliations independently from month-to-month.  All of the various tasks performed on each reconciliation is captured within the History tab, a big plus for managers and auditors.

In summary, Hyperion Account Reconciliation Manager is a purpose built enterprise tool to centralize the monthly reconciliation process.  Hyperion ARM can create insight and efficiencies during the close and reporting cycles for accountants, managers, and auditors.  We have helped clients gain back hours of research and data mining by building the appropriate level of reconciliations and capturing the detailed supporting data.

Summary

  • Leverage the appropriate tool like Hyperion Account Reconciliation Manager to build efficiencies within the recon process
  • Eliminate the use of Excel and Excel Macros within the reconciliation process
  • Determine the best breakdown of the Business Unit - Account combination
  • Discuss and develop the appropriate level of data detail for the monthly balances
  • Drive centralized processes via the setup and workflow
  • Leverage instructions and questions to centralize and maintain detailed reconciliation information
  • Implement Attributes within ARM,  where it is feasible, for reporting and filtering