Managing your risk – Why you need a Financial Consolidation system?

It is continually surprising to observe the number of ASX-listed organizations lacking robust systems to generate consolidated financial statements and notes to the accounts. Directors of companies are obligated to exercise due care and diligence under the Corporations Act 2001, which includes financial reporting responsibilities. Financial statements and notes to accounts are required to adhere to accounting standards and accurately reflect the company’s consolidated financial position and performance.

Many organizations depend on Excel as the primary tool for preparing statutory consolidated financial statements. Although Excel is a useful tool, it is often not the most suitable system for producing consolidated financial reports. Excel is not a robust option, and numerous well-documented cases in Australia and abroad have shown that errors in Excel have led to significant financial reporting mistakes.

Dedicated applications designed for preparing consolidated financial accounts come with standard features and functionalities that enable financial accounting teams to prepare the accounts confidently. Without such an application, Financial Controllers or CFOs may encounter several challenges when producing consolidated financial accounts in Excel, including:

Reporting Challenges of Excel

  • Multiple General Ledger Systems – It is very common that an organisation has multiple General Ledger systems, each containing its own unique Chart of Accounts (CoA). These Trial Balances need to be extracted and mapped to a common CoA.  These ledger systems are typically always changing, for example the addition of new accounts or legal entities.  Financial consolidation applications have tools designed for accountants to manage this CoA data mapping process.
  • Currency Conversion – Many organisations have legal entities that report in other currencies and these need to be translated into AUD for consolidation purposes. Financial consolidation applications provide standard functionality to perform this currency conversion and report in multiple reporting currencies, for example AUD and USD.
  • Preparation of Notes to Accounts – The notes are used to make important disclosures that explain the numbers in the financial statements of a company. Common notes to the financial statements include accounting policies, depreciation of assets, inventory valuation, subsequent events, etc. These notes are typical complex to prepare and often require data from a variety of different data sources.
  • Intercompany Eliminations – Excel does not automatically post intercompany elimination and consolidation journals. In Excel, there is no standard ability to produce a mismatch report or maintain an audit trail of changes made to your data.
  • Data Validation – Most consolidation applications have multiple layers of data validation in order to minimise reconciliation issues. If Excel is used, it is difficult to achieve a similar level of the checks and balances required to have complete confidence in your financial reports.
  • Partial Ownership – Many companies have various subsidiaries/entities that are not fully consolidated into financial statements. Most consolidation applications have the ability to apply appropriate accounting equity rules to ensure that entities are correctly consolidated. Excel typically does not have this functionality.
  • Workflow – Financial consolidation reports are typically prepared by a team of financial accountants. Workflow is difficult to manage in Excel as Excel files need to be distributed to various stakeholders and you cannot control last minute adjustments and changes.  A dedicated consolidation application not only improves the efficiency and workflow of staff to assist with closing financial accounts, but can also ensure last minute or post close adjustments are not made without approval.
  • Security & Audit Features – When it comes to accountability, tracking user actions is crucial. This cannot be achieved in Excel.
  • Financial Statement Reporting – When preparing the financial statements there are typically many different views of the data that need to be represented. This may be by Product Groups, or Regions.  A dedicated consolidation application can make this data easy to present in a variety of reporting views.

Reporting Challenges of only using an ERP

Can you implement a financial consolidation system within ERP applications?  Some ERP applications contain some basic consolidation functionality, however there are a number of typical shortcomings with this approach:

  • Mergers, Acquisitions & Disposals – often many large companies are undertaking M&A activities. If you have just made a large acquisition, how will you produce your financial consolidated statements?  Your ultimate goal might be to move the organisation to your core ERP, however these projects can typically take many months or years.  Consolidation applications have tools that allow you to easily extract the Trial Balance from the General Ledger of a new organisation and map the CoA to your common consolidated financial reporting CoA.  Compared to an ERP migration, this can be done in a very short time frame.
  • Notes to the Accounts – Some ERP applications provide functionality to post elimination journal entries, but they do not provide a facility to create notes to the accounts. Again, we too often see organisations attempting to perform this task within Excel with its associated short comings.
  • Flexibility – reporting requirements and accounting standards are continually changing and evolving. Typically, ERP applications are not very agile environments and change is often difficult, time consuming, and costly to implement.
  • Costs – We are often surprised by the large investments that organisations make to enable financial consolidation within an ERP application. An application designed specifically for financial consolidations can be implemented for a fraction of the cost.


Specific financial consolidation applications are not costly to implement or maintain. Such applications can significantly lower audit fees for an organization. When standard end-of-year journal entries are utilized within these applications, they usually require only a single audit and approval by external auditors. In contrast, when spreadsheets are employed, they often necessitate a full audit annually.

As a Financial Controller, CFO, or Director of a major ASX listed company, I would avoid risking my personal or the organization’s reputation by relying on Excel for producing consolidated financial reports. The stakes are simply too great.



Little Known EPM tools: Using JHAT

JHAT tool is another way to automate HFM tasks using a batch file and the HFM API. It is the former HAT updated to be compliant with the last HFM release. JHAT offers the opportunity to use any scheduler to launch HFM tasks and provide better flexibility than Task Flows.

JHAT utility is present here (and the batch file embeds all libraries, paths and other references to execute HFM tasks);


In this example set, we are going to make use of the power of PowerShell scripting and the functionality provided by JHat to perform an HFM metadata scan.

Running an HFM Metadata Scan using JHat

To begin with, let’s create an external variables file called  PRD_External_Vars.ps1 and define all our required variables here.

You could also create 1 single file and declare the variables at the beginning, but I just like it to keep it separate as it becomes easy to manage and generally a cleaner approach.

 #HFM Variables
 $HFM_user_jh = '"hfmadmin"' #Username to login 
 $HFM_Password_jh = '"p@ssw0rd"' #Password for the user 
 $HFM_Server_jh = '"HFMPrd"' #HFM cluster name 
 $HFM_Application_jh = '"Demo"' #Application Name
 $Delimiter = '";"' #App file delimited 
 $HFMScanMode = '"Replace"' #Use the 'Scan' or 'Replace'

Now that we have all our variables declared, let’s just get it going with the JHat script…

Let’s begin by importing all the variables that we declared above in out JHat script

#Create External Variable File Name (as per the environment)
$dir_ext_var = "mydrive:\mydir\PRD_External_Vars.ps1"

#Retrieve Variables from External Variable File
. $dir_ext_var

While we are at it, let’s also declare few additional variables for the log and properties life.

#Log file to log all the steps being executed by JHat
$HFMBatchLog = "mydrive:\\mydir\\HFMMetadata\\HFM_Metadata_Update_Load.log"

#Location for the properties file that will be created by PowerShell on the fly. This will be used by JHat

#Location of the jhat file installed on the Financial Management server
$JHatLocation = "D:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Server"

#Location of the properties file. This would be passed as a parameter to the jhat batch
$InputFileLocation = "mydrive:\\mydir\\HFMMetadata\\"

#Temporary location of the jhat log file
$LogFileLocation_jh = "mydrive:\\mydir\\HFMMetadata\\hfmJH.log"

#Temporary location of the powershell log file
$LogFileLocation_ps = "mydrive:\\mydir\\HFMMetadata\\\hfmPS.log"

#Log file which can be reviewed later after the job execution is completed.

#Location of the powershell if there are any errors with powershell execution.

#App file that will be used to perform the scan and/or load of HFM metadata (the file can be XML too)

The next interesting bit is to create the HFM Metadata load properties file on the fly. This file would be used by JHat utility to perform the metadata scan…

What we are doing below is to create a properties file that would be used by JHat to;

  1. Login to the application,
  2. Open a session for the application,
  3. Perform a metadata scan,
  4. Store the output in a log file,
  5. Close the session and
  6. Logout of the application.

#Clear contents of existing .properties file on the fly
Clear-Content $OutPath\

#Available Functions
#Function Name: Logon - Login to the application
#Function Name: OpenApplication - Open a session to the specified application
#Function Name: LoadMetadata – Scan and/or load HFM metadata into the specified application
#Function Name: CloseApplication – Close the session opened
#Function Name: Logout – Log out of the application

#Create .properties file on the fly
Add-Content -Path $OutPath\ -Value "Logon(""False"","""",$HFM_user_jh,$HFM_Password_jh);"
Add-Content -Path $OutPath\ -Value "OpenApplication($HFM_Server_jh,$HFM_Application_jh);"
Add-Content -Path $OutPath\ -Value "LoadMetadata($DimensionFile,$LogPath_jh,$Delimiter,$HFMScanMode,""True"",""True"",""True"",""True"",""True"",""True"",""True"",""True"",""True"",""True"",""False"",""False"",""False"",""True"");"
Add-Content -Path $OutPath\ -Value "CloseApplication();"
Add-Content -Path $OutPath\ -Value "Logout();"
Add-Content -Path $OutPath\ -Value "End"

#Call Jhat api
#The jhat batch requires the log file location and the inputfile location as the parameter
Start-Process -FilePath "$JHatLocation\jhat.bat" -ArgumentList "-O$LogFileLocation_jh -I$InputFileLocation"

Finally, let’s run the PowerShell now.

Once the execution is complete, checking in Consolidation Administration, we can see that the Metadata load started and completed without any errors.

There are various other functions available with JHat,

Running An InterCompany report using JHat

#Function Name: GenerateReport – To Generate ICP report
#Arg0 = Path (Path of the document in document manager)
#Arg1 = docName (Name of the document)
#Arg2 = reportType (valid options - intercompany, journal, EPU, ICTransactions, IC Match By Account, IC Match by ID)
#Arg3 = reportFormat (HFM_FORMAT)
#Arg4 = reportFile (location of the file where report must be stored)
#Arg5 = overriddenPOV (specify the POV to override it with)

GenerateReport("\\\","Monitoring_REP_Plug_Acct_Matching", "intercompany","HFM_FORMAT","D:\Oracle\Temp\Workspace\Intercompany\InterCompany.html","S#Scenario.Y#2019.P#Jun.W#YTD.V#<Entity Curr Total>.E#{Example.[Base]}");



Important Software EOL (End of Life) Dates (Microsoft & Oracle)

Microsoft and Oracle have some critical end of life dates quickly approaching.  Any customers running on the platforms listed below should begin to prepare for these upcoming support changes.  Contact us today to discuss how these support policy changes might affect your Financial Close, Budgeting and Reporting systems.


Important Software EOL (End of Life) Dates

The following list represents products reaching end of support in the next year. For a comprehensive list of Microsoft products and their lifecycle policy timelines, please search the Microsoft Lifecycle Product Database.

  • Microsoft Windows 2008 Server – January 2020
  • Microsoft SQL Server 2008 – July 2019
  • Microsoft Office 2010 – October 2020

Lifetime Support dates for EPM System release 11.1.2.x have been extended.  The new, extended dates are publicly available in the newly published Lifetime Support Policy: Oracle Fusion Middleware Products.

  • 11.2.4.x – December 2020, extended support to December 2021


11.2.x Release Date & Rumours

  • 11.2.x – To be released September 2019, end of life date of December 2030
  • Oracle has moved the Essbase development work out of EPM and into the Oracle Database development team.
  • When EPM 11.2 comes out, Essbase will initially remain technology under the covers: we won’t be getting the new Sandbox features introduced with Essbase 12 in on-premises OBI12 / cloud OAC.
  • OAC will no longer have Essbase bundled with it, effective immediately for all new customer-managed OAC implementations. Essbase “12c” will have to be installed separately as a standalone instance, and then Essbase cubes would need to be migrated from the old Essbase instance into a new standalone Essbase 12c instance.
  • A new Essbase, Essbase 19c, is under development for 11.2. It is expected to come out sometime next year.  Essbase 19c will be for on-premises only.
  • on-premises Essbase patch development has apparently stopped and will not continue.
  • It is rumoured that 11.2 will first come out for Windows only, perhaps as early as Sept 2019 (SAFE HARBOR APPLIES), and a Linux version will not come out until Q1 or Q2 2020 (SAFE HARBOR APPLIES).


11.2.x Discontinued Features

  • Hyperion Financial Management
    • Financial Management Analytics
    • Essbase Analytics Link for HFM
    • Quantitative Management and Reporting for Solvency
  • Hyperion Planning
    • Hyperion Strategic Finance (HSF)
    • Simplified User Interface (SUI)
    • Workforce Planning
    • Capital Expense Planning
    • Project Financial Planning
    • Offline Planning
  • Hyperion BI +
    • Interactive Reporting (IR)
    • Production Reporting (SQR)
    • Web Analysis (WA)
  • Other
    • Disclosure Management
    • EPM Mobile


This means that staying on-premises is still a choice that will continue to be fully supported until 2030 with some conditions. If you make this decision, you will continue to miss out on the constant product enhancements available in the EPM cloud solution. This also can be as expensive of an option as the cloud solution, because you will incur further costs such as hardware and software upgrades and patches for both the EPM applications and the associated third-party components (e.g., Oracle and Microsoft SQL databases).

To determine the best path forward, owners should figure out the costs both from a short-term and long-term perspective, as well as comparing the difference in functionality between the two offerings.

Need help defining your EPM solutions roadmap?   Contact our team at for more information



  1. Safe Harbour applies. Some of Oracle’s comments are directional in nature and may change in the future.