Search This Blog


Tuesday, August 9, 2016

Account Reconciliations

Account Reconciliations within Hyperion


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.


  • 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

Sunday, July 31, 2016

RunTimeSubVars in FDMEE

FDMEE Essbase/Planning Calculation Script Execution

We have a client that loads the same entities from two separate FDMEE locations.  Typically, it is recommend that we prevent this from happening, but in this case the complete data set originated from two different sources.  During the data load process in FDMEE each location impacts the other location.  To prevent data issues from occurring we leveraged the Target Application Calculation Script setup with RunTimeSubVars.  

The Planning Architect setup a specific Clear CalcScript to limit the impact on the secondary location. This script runs before the data load and is application specific.  Data Rule and Location Script Scopes would not work in this instance because we wanted to limit the number of scripts created in the Planning/Essbase. The second script is the Calc/Aggregation of the cube, which obviously runs after the data load. 

Now the fun part...

The Planning/Essbase Architect setup the CLEARFDM calc script.  As part of the setup we defaulted the rtsvFactility RunTimeSubVar.

In the FDMEE setup we set the script value through the the Data Rule Option 1, which we will leverage to send over the Essbase Calc Script language necessary for SALMON and SUN data load. 

The Data Load Rule Integration Options are available on the Custom Options tab with the Data Load Rule for each Location.  Notice that we passed Essbase Calc Language leveraging the Integration Option 1 into the ClearFDM calc for both Salmon and Sun locations. 

Salmon Custom Options:

Sun Custom Options:

When we load the data to Planning/Essbase we can see the results of the above Integration Options in the log files.

Salmon Log:

Sun Log:

Each process passes the correct Essbase Calc Language to the rtsvFactility RunTimeSubVar preventing each location from impacting each other. 

Below are a few items to note: 
  • We leveraged this because the Target Entities overlapped in each locations data
  • If you have a long Essbase Script Filter that you need to pass, you will need to stack the Script Parameters
    • Each Integration Options within the Data Load Rule only allows 100 characters (Spaces count). Even if you stack and leverage all four Integration Options your max characters is 400