Hyperion Planning 11.2 Calculation manager - Business Rules

Namaste!

Here is a simple business rule for driver based revenue planning scenario.
Will discuss more complex scenarios in future.

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/

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/

Automation of Financial Consolidation and Reporting using HFM - Requirements Document - Part 3

Namaste!

Let's continue from the last blog where we have analyzed part of the requirements questionnaire response.

https://padmaja-vemireddy.blogspot.com/2020/01/automation-of-financial-consolidation.html


we will continue analyze remaining responses to be able to deliver the requirements document.

4.    How many Legal Entities and Reporting Chart of Accounts?

Please expect to see the list of divisions/locations/legal entities which are responsible to generate and submit the Trial balances as soon as monthly close happens. Along with that, list of reporting roll ups to meet any management reporting requirements.

In case of reporting chart of accounts, hear out the current pain points with redundant GL accounts/local chart of accounts (COA) and how they are being mapped to the reporting or summary or global chart of accounts.

Based on above response from client, we were able to put together following sections of the
requirement document.

•  Process related challenges to reduce the COA redundancy
•  Complexity involved with mapping exercise from local COA to global COA

5.    Do you anticipate to have consolidation application built using GL Accounts or Summary Accounts level?

I would be very interested to learn the response to this question. The reason being many clients prefer to build the consolidation application at GL account level. It is not their fault since the general belief is having all the GL accounts in one consolidation application would act as a single source of truth.

Fair expectation from client stand point!

But it is very important to convince them that consolidation application would and should never replace the local GL specific reporting systems.

Consolidation application would be overwhelmed if we dump all local GL accounts of all entities.consolidation performance would be degraded drastically from minutes to hours on month end consolidation close.

Best practice would be to design 200 to 400 global chart of accounts (COA) in Consolidation application depending on Financial and Statutory reporting requirements.

Advise clients to publish these global COA to all entities and let controllers own the mapping exercise of GL accounts to global COA.

Many Consolidation projects would be delayed due to one reason, that is under estimate the effort and complexity involved in this mapping exercise.

Also we expect to see a response on reporting roll ups for the Balance Sheet, Income Statement, Statement of Equity and Cash Flow.

6.    Will you be using multiple currencies?

Should be a straight answer on public and reporting currency requirements

7.    Please explain any custom Inter company elimination requirements or is it standard eliminations?

Expect to see a response like how currently an use of elimination companies and how the corporate controllers book elimination entries into these elim companies to enable elimination upon consolidation.


8.    Can you please explain about requirement of Minority Interest calculation?

There may be instances where client holds substantial portion of ownership in other entities. Expect to see a description how minority interest gets calculated

Output:

•   Templates and Process documents for minority interest calculations

   

9.    How often will data be refreshed? Daily or monthly


Client may describe how the GL data is being fetched into one common system or location? Is it daily or month end ?


10.    Do you anticipate Master data management issues? If so, do you have an anticipated plan to deal with these?

Expect to see if client has any centralized master data management (MDM) tool to maintain the GL COA and global COA? If they don't have any MDM tools in place, expect a write-up on what is the current process for governance of metadata?


11.    How many reports do you anticipate will need to be generated?

Expect to see the list of management, legal and statutory report packs that are being generated from their current consolidation system.

Sample Reports:

•     Balance sheet by legal entity and segment
•     Cash Flow by legal entity and segment
•     Statement of Equity by legal entity and segment
•     Trend reports for P&L and Balance Sheet (i.e. monthly / quarterly)
•     Note disclosures for various balance sheet and income statement accounts 

Output: Detailed list of report templates and security matrix to determine who access what reports

12.     Do you generate a “standard” management reporting book? How often?

Expect to see a description on frequency of financial statements for public reporting is being generated

Describe monthly business reporting process to get a view of the balance sheet, income statement and cash flows

13.    Are you planning to include Income Statement, Balance sheet and or cash flow reporting? At what level?

Expect a response on how the income statement, balance sheet and cash flow reporting at the consolidated level is being produced

How the legal entity level reporting is being handled?

13.    Do you plan to do allocations? Briefly explain the complexity.

Does client book manual allocation entries in their GL systems?
How elimination entries are booked in the elimination companies for these to eliminate?
or do they plan to book allocations in consolidation application in future? If yes, expect a detailed write-up on process

14.    Briefly explain about currency translation requirements

Expect to see a description how the local and reporting balance sheet amounts are being translated using month end foreign exchange rate and income statement amounts using the average exchange rate

Does client have any foreign exchange issues to create cash flow statements?

15.    What do you anticipate for Training requirements? Training course for all, train-the-trainer, etc.

Any training requirements for such as user, train-the-trainer, system administrator after the implementation of consolidation application.

16.    What are your documentation requirements?

Describe the need of Technical and system related documentation
Documentation need for system administrators.

That's it! we are done with the questionnaire.

Since we have reviewed and analyzed all the responses from requirement questionnaire, it is time to move on to finish the remaining portion of the requirement document.

As you all know we have written 4 sections of the document so far in the earlier blog:

https://padmaja-vemireddy.blogspot.com/2020/01/hyperion-financial-management_16.html

The sections we have covered in  so far are:

• Current system overview
• Current pain areas
• AS-IS Process flow (High level)

In next blog we are going to finish off by documenting the below sections:

• 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

I will see you guys in next blog.

Note:

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/

Automation of Financial Consolidation and Reporting using HFM - Requirements Document - Part 2

Namaste!

 we are continuing from the last blog.,

https://padmaja-vemireddy.blogspot.com/2020/01/hyperion-financial-management_16.html

There, we were discussing how to put together a requirement document and so far we have touched on topics.,

• Organization Finance Landscape and stake holder identification ** 1
• Organize various data/processes/report templates from related parties ** 2
• Document current system overview **3
• Identification of current pain areas **4
• Draw high level AS-IS Process **5

Fair enough. However the question remains. how did we link the requirement questionnaire from the workshop to the requirement document that is work in progress, now?

If yes, how? If no, then how did we write the above sections of the requirements document?

You are right! The responses from finance users have to be analyzed to be able to write a decent requirements document.

In this blog, I am going to give my view point on what should we expect and how do we analyze the responses from the requirement Questionnaire:

1.   Please explain the current consolidation close cycle

Here we expect to see a write up from the finance department Subject matter Expert (SME) who is familiar with the end to end financial consolidation and reporting close. It starts with explaining the requirement of monthly and quarterly close cycle processes.

It should describe, how many General Ledger (GL) Systems does client have (For example: Oracle EBS, PSI ) and any other 3rd party data sources like Quick books etc.

Upon arriving GL data from all divisions and/or entities, what would be the target place? Since client may have different ERP systems in place, it would not be possible to consolidate all GL's into one ERP system unless they have a customized system in place.

Eventually we should expect details on how the consolidation happens currently, up on arriving various GL's and 3rd party generated Trial balances (TB) to the Corporate Consolidation Division?

Does consolidation happens with one to one mapping of GL Chart of accounts (COA) or using a global COA which is many to one mapping?


Further to this, please expect a write up from finance folks on how  the reports from these GL TB's are being manually plugged into a excel consolidation spreadsheet.

What are the next steps and processes involved to generate the Balance Sheet, Income Statement, Statement of Equity, Cash Flow, the notes to the financial statements and all?

What are the balance sheet re-class adjustments are being done manually and anything else we should be aware of.



Based on the above response from client, we were able to put together following sections of the requirement document.

• Organization Finance Landscape and stake holder identification ** 1
• Organize various data/processes/report templates from related parties ** 2

The above information helps not only the processes and potential functional requirements for future consolidation system, it would enable Implementation team to estimate on the following important tasks of High level design milestone:

Number of Source systems would determine the:

•     Data Integration requirements for various source systems (GL's, 3rd Party and Excel sheets)
•     Build vs Buy the source and target adapters
•     Data Processing and Cleansing requirements 
•     Hardware and Software sizing for Application and Database servers
•     Load balancing and Application server clustering
•     Availability and outage for maintenance windows

2.   How many users do you anticipate using the system? 

Currently there may be just a handful of users from the corporate reporting team might be

responsible. But in future they may expect all division/location/entity controllers must be responsible

for the data they submit to the corporate on monthly basis. That decision would result in increase the

number of users accessing the future consolidation system. For instance, if client organization has

legal entities to generate and submit the monthly Trial balances (TB), we expect to see 50 users from

divisions and handful users from Corporate.

The above information helps  to determine the product licensing requirements (Number of power 

users, view users etc). Also it would help implementation team to determine:

• Potential load on Application Servers
• Potential load on Database Servers
• Stress and Load Testing needs
• Fail over and Switch over needs 

3.   What are some of the key business process issues you are trying to address?

Inter company transactions

Here you expect to see a description from the client how an inter company transactions are being recorded? Usually, many GL Systems are configured with separate elimination companies to book the inter company transactions. of course elimination of inter company balances are manual in those systems. Client must provide you the current process to eliminate the inter company balances

Foreign Exchange and Cash flow

Here client must describe how the cash flows are being created using excel or any 3r party tools? How cash flow balances agree to the income statement? Explain what is the effect of "foreign currency on cash and cash equivalents" in the cash flow statement? What are the expectations from the future consolidation and reporting solution?

Prior Period Adjustments

Here we expect to see client explaining how the opening balance sheet changes are made at source system level, which result in adjustments to the consolidation balance sheet, cash flow statements, foreign exchange balances when comparison is being made

Explain how Prior period adjustments are being made currently when P&L or balance sheet items have been re-classed to meet any GAAP or IFRS reporting requirements

Bottle necks in the consolidation process

For Instance, the current consolidation process is done in one consolidation spreadsheet which can only be worked on by one person at a time.  This creates a bottle neck in the overall quarterly reporting process.

Are Location/Division/Entity controller teams involved in the consolidation process? or is it only corporate reporting team taking care of the close? Any delays with this approach?

External Reporting

How client generate the note disclosures and any other foot notes for external reporting purpose?

Based on above response from client, we were able to put together following sections of the
requirement document.

• Document current system overview **3 (partially)
• Identification of current pain areas **4 (partially)
• Draw high level AS-IS Process **5 (partially)

Let me continue with the rest of the requirement questionnaire responses in the next blog.
Happy Weekend!

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/