Automation of Financial Consolidation and Reporting using HFM - Requirements Document - Final

Namaste!

Welcome back!

This is going to be the final one in the requirement series on "Automation of consolidation and reporting using HFM".

I know it is a bit long journey but it certainly worth a travel because re-visiting the requirements (if any) would be a costly affair in the midst of Development or Go-live. It would impact cost, quality and schedule of your project!

Anyway, let's get on to documenting the remaining sections of the requirements document.

• Scope of the project
• Implementation expectations
• Functional requirements
• TO-BE Process flow 
• Data Migration and Validation
• Security
• Appendix
•       GL Accounts and Global COA
•       List of entities and ownership details for Holding companies
•       List of cost centres/locations or any other segments
•       Reporting roll ups for all COA's
•       Templates for Data Loads
•       Process docs for Translations
•       Process docs for Consolidations
•       Process docs for Eliminations
•       Security Matrix

Scope of the Project:

Here is the sample write up based on the feedback from requirements workshop and analysis we have been doing..,

• Automation of the Consolidation processes including data collection, translations, eliminations and calculations to generate the Consolidated Financial Statements    
• Faster reporting close cycle and improved data security by moving away from excel based consolidations
• Audit Trail capability and enable generation of reports for auditors
• Ease of handling dynamic change in the group organization structures due to mergers and acquisitions
• Automation of Minority Interest calculations, recording of Inter-company eliminations and Adjustment entries using web based interface.
• Facilitate ease of analyzing financial data and creation of ad-hoc Consolidated Financial reports
• Transparency in currency conversion process
• Make controllers own the data they have been submitting on monthly basis
• Provide web based interface to controllers to map the local COA to global COA    
• Generate the web based report pack such as Profit & Loss, Balance Sheet and Cash Flow Statement

 Implementation expectations:

Again, this is a sample write up based on the feedback from requirements workshop and analysis we have been doing..,

• The Consolidation application should be able to handle up to 150 users.
• Application should be able to integrate windows Microsoft active directory and enable single sign on
• Application should be able to support account, entity and cost centre wise security for all users
• Application must be a central repository of Consolidated Financials at all the consolidation levels.
• Automate various consolidations related adjustment entries and IFRS adjustment entries.
• Generate the Consolidated Cash Flow Statement as per IFRS for the reporting period.
• Consolidation application should be able to generate Group Consolidation Reports, Statutory Reports, MIS and variance reports.
• Enable user to drill through the source system (Oracle EBS) to the level of transaction in Sub ledger.
• Enable user to to pull the data from excel for ad-hoc analysis.
• Enable user to modify/update hierarchies with minimal maintenance and rework as Dimensions like Account, Cost Centre and Entity roll ups.
• Enable user to pass adjustment entries using Journals
• Full automation of metadata and data load and seamless integration with source and target systems.
• Enable users to enter cell level comments and attach documents for supporting details. 

Functional requirements:

Every organization is unique. Though these may be commonly asked requirements, you should do required due diligence to determine your client requirements. Here are some of those requirements I could think of:

• Consolidated financial statements and reports for all legal entities grouped as per holding structure.
• Stand-alone financial statements and reports for all legal entities.
• Stand alone and consolidated Cash flow statements.
• Automation of Schedules and Supplementary details.
• IFRS compliant translation of foreign currency trial balances.
• Data load formats for all GL TB loads by Controllers.
• Automation of schedules and/or Non TB data load templates:

• Accumulated depreciation movements for fixed asset schedule.
• Investments Movements.
• Reserves movements.
• Equity Share Capital Movements.
• Secured and Unsecured Loans Movements.
• Inter company transactions.
• Commitment & Contingent liabilities.
• Number of Shares and Percentage of Shareholding.
• Directors and Auditors Remuneration.
• Expenses/Earnings in Foreign currency (accrual basis).
• Deferred Tax Liabilities Details.

• Data load monitor report by period and entity to track the TB load status.
• Elimination of inter company balances, investments and share capital on consolidation.
• Ability to store budget and rolling forecast numbers within application for MIS reporting purposes.
• Inter company matching and elimination reports.
• Each Quarter additions need to be shown by segment and Quarter YTD.
• Each Segment (entity), additions need to be shown for all quarters.
• All movements information for Q1, closing balance of MAR 31st of Year including depreciation/disposals/write-offs.
• Ability to pass parent level adjustment entries within Consolidation application.
• Task lists and work flow as per user security matrix.
• Security groups by entity and cost centre.
• User provisioning on consolidation applications based on role. 

 TO-BE Process flow:

At this stage we should be able to draw how the future state of the consolidation process flow looks like. It would be okay to express the process flow using technology specific naming conventions since we have determined to use "HFM" in this case.

Data Migration and Validation:



Data Migration task depends on Client's requirement of amount of historic data to be brought into the future consolidation application to be built.

Many clients consider to bring only the current year data and prior year ending balances when they plan to go-live with a consolidation solution (HFM).

The reason being, historical data doesn't provide any value from Legal and Statutory reporting standpoint, since organization might have already submitted these reports for prior years. 

However few clients choose to load 2 to 3 years of historical data with the intention of producing management reports for past and current years using this proposed consolidation application.
So, there is no hard and fast rule on this!

It would be Solution architect's responsibility to devise the strategy to pull the historical data from different sources. Will have to work closely with the current consolidation system owners to identify the data feed formats of all these sources.

I would recommend to request client (Data source admins) to arrange all non TB data in a pre-defined format that FDMEE could understand is a best bet. However TB data could be directly plugged into FDMEE using Oracle provided source adapters.

Now let's talk about the Validation piece of this requirement. Who will be validating the historical data that will be brought into this proposed consolidation application. No offence intended to anyone but common complaint from Implementation team on historical data would be users does not participate in data validation testing!! 😞

Well, part of the blame must go to the project manager who might have missed out publishing the RACI matrix as part of the stakeholder communication.

Apologies for deviating a bit. Here is a sample RACI matrix that would save the Implementation team from latter disappointment!

Folks, please do not under estimate the value of publishing the RACI on time and make everyone understand the tasks in it, so everyone would be on same page! So there will be no "I thought it is your responsibility" kind of surprises!!

So in the RACI, we have clearly defined that Client is responsible for uploading the 12 months of trial balance using FDMEE by consulting the Implementation Team/Vendor. Same goes with the testing.

Security:

I would think we should reserve this topic for the design sessions after requirements are documented and send it to client review and sign-off.

So, I will write more in next series of blogs on "Consolidation Application Design using HFM".


So far, we have prepared the following sections of the requirement document in this blog:

• Scope of the project
• Implementation expectations
• Functional requirements
• TO-BE Process flow 
• Data Migration and Validation
• Security 

Now final portion of the document is attaching all the templates that are collected during the requirements workshop sessions. 

Appendix:

GL Accounts and Global COA:

Please attach the excel sheets of GL accounts from all entities. Also attach the Global COA (if any) that currently being used for financial consolidation and reporting purpose.

Here is the sample list of Global COA. It varies company to company, FYI.

10025    BOFA - Cash Account
10110    Accounts Receivable - USD
10140    GST / HST Receivable
10147    GST Payable - USD Funds
10149    HST Clearing
10180    Employees receivable
10190    Allowance for Doubtful Accounts
10215    Inter-company
10305    Prepaid Expenses - Insurance
10610    Buildings
10620    Equipment
10670    Office Furniture & Equipment
10716    Acc Dep - Leasehold Improvement
10719    Acc. Depreciation - Building Equipment
20020    Accounts Payable - USD
20100    Accrued Payroll
20215    Accrued Legal and Audit Fees
20299    Accrued General Liabilities
20357    Petty Cash
20550    Income Tax Payable
30900    Retained Earnings
40100    Sales Professional Services
40400    Inter-company Sales
40700    Sales Returns
50050    Cost of Sales - Professional Services
50100    Standard COGS Direct  Labor
50500    Direct labor Wages
50560    Holiday Pay
50565    Vacation Pay
50565    Vacation Pay
50569    Sick Pay
50570    Direct Labor Bonus
50680    Direct Labor Payroll Taxes
70730    Postage & Courier
70750    Software Expense
70830    Depreciation
80050    SG&A WAGES
80260    Payroll Fees
80300    Travel/Meals/Entertainment
80320    Training
80500    Rent
80620    Office Supplies
80650    Software
90999    Suspense


 List of entities and ownership details for Holding companies:

This is another important document we have to gather during or after requirements workshop.
It will help us to build the entity hierarchy and ownership management table.

Here is a sample list of entities and ownership table looks like:

 List of Cost Centres (or any other segments/dimensions):


Mostly, Cost Centre dimension would be included in Consolidation application though it is not mandatory for Legal and Statutory reporting. However "Cost Censer" segment makes more sense for management reporting.

Attach the list of cost centres that would be going into the consolidation application. Here is the sample list:

Reporting Roll ups for all COA:


This is another important section of the consolidation application artifacts and it would be called "Alternative Hierarchies" as well. 

Many of us would think reporting roll ups are meant to generate the reports.
Partly true but imagine if you plan to product 200 reports from consolidation application, you are going to build those many reporting roll ups.

So it all start with building the flat list of COA's as dimensions in HFM application. Then, few major hierarchies would be designed to accommodate quick validation and reporting out of the application. Here are few COA hierarchies by segment/dimension:

Natural Accounts:     Income Statement, Balance Sheet, Cash Flow Statement, EBITDA

Company/Entity:   Business unit hierarchy, Region hierarchy

Cost Centre/Department: G&A hierarchy, Manpower hierarchy


Here are sample hierarchies looks like:

Templates for Data Loads:

Just to let you know that Oracle Hyperion FDMEE would act as a data management tool with a pre-built source and target adapters in a typical consolidation projects using HFM.

Though FDMEE could handle any file formats to load data into HFM, few users prefer to use the current data formats to submit schedules, movements etc. So we may have to gather all these documents.

Apart from that, all system generated GL TB's would be directly plugged using FDMEE. So, we would have to gather all source system details here.

Process Docs for Translations:

If there are any templates available for translations from client on how it is being done now.
or may be a write up., some thing like this..,

• The account type property of each account will determine the translation rate to be used for the   account in consideration.
• The Share capital, Minority Interest, Fixed Asset, Inventories, Goodwill, Investment in subsidiary, share premium and Accumulated depreciation are valued at Historical rate and remaining balance sheet items at Month-End rate.
• All revenue and expense items are translated at Average rate. 
• The translation differences are taken to Foreign Currency Reserve account.

Process Docs for Eliminations:

If there are any templates available for Inter-company transaction eliminations from client on how it is being done now.
Also please include any other templates which are being used to generate inter-company matching reports.



Process Docs for Consolidations: 

 Please include the consolidated excel spreadsheet that is being used by client to combine the TB's from all GL's to generate the consolidated financial statements.

Detailed process templates for consolidations must go in here.

Security Matrix:

List of users such as controllers by each and every entity/business unit must go in here.
Also who are accessing the reports by location and list of power users should be provided.

During design sessions, I am going to prepare the security matrix template and map users under different roles. Entity wise security will be driven by security classes which we will cover during Design part.


Folks, That's all for now on Requirements gathering, analysis and preparation of "Business requirements document (BRD)" of "Automation of Consolidation and Reporting" Solution.

I will see you soon in HFM Design blog series.

Have a good day!

I have published an Advanced courses on Hyperion Essbase, Planning and HFM on Thinkific Learning Platform. Please apply the coupon codes found on the site to avail the discounts on the courses. Please check it out if you are interested. 

https://courses.trnghub.com/