Hyperion Essbase - Questions asked by Students - 1

 Namaste!

I would like to write few series on the questions my students asked during the course.

Here are few questions and answers I would like to share with you all on Hyperion Essbase related.

More to come latter!

Question1

In Most of the Essbase Outlines always Accounts and Time are set as Dense.

As per my understanding Scenario dimension also Dense in Nature.

But, Why Scenario dimension is set as Sparse mostly in real time? Please advise.

Answer

It also depends upon how you use (retrieve) the data/Calculate the data. Most of the times, we retrieve either Actual data or Budget data and also calculation scripts will run against one scenario at a time.

In this situation if we set scenario as sparse, then retrieval will be easy. System can retrieve only 'actual' related blocks at one time (or blocks related to Budget at a time)


Question2

A little confusion with Data and  members.
for example if 1000 is data then it will go and sit in level 0 level right?.
Are level 0 members means data ?

Answer

Level 0 members are not numerical data. Level 0 members are also called metadata, the characteristic (or description) about data.

When we say 1000, there is no meaning rt, it could be sales or discount or rent.

Let us assume it is sales. I will say Sales = 1000. Then we get another question: which month is it? Let's say Jan.

Now we have more description about 1000. Jan, Sales = 1000. In this case Jan is member, sales is member, 1000 is data

now we get another question, which year is it? let's say year 2020. We can call this member as FY20.

Jan, FY20, Sales = 1000

As you need more description about data, you will add more dimensions to Essbase database to describe the data.


Question3

How to build dimensions with formulas that contain "" and ,

IF(@ISMBR("Jan"))
@VARPER ("MetricA"", @PRIOR( "Dec"->"MetricA", 1, @RELATIVE( "Years", 0 )))/100;
ELSE
@VARPER(MetricA,@PRIOR( "MetricA"))/100;
ENDIF

Some of my the metrics I'm building contain such formulas. I'm having trouble uploading these dimensions using the Rules Files because of the commas and spaces in the formulas.

After using the pipeline (|) delimiter and replacing the newlines with spaces as shown below:

IF(@ISMBR("Jan")) @VARPER ("MetricA", @PRIOR( "Dec"->"MetricA", 1, @RELATIVE( "Years", 0 )))/100; ELSE @VARPER("MetricA",@PRIOR( "MetricA"))/100; ENDIF

I still face the problem of EAS removing the quotation marks for me. This appears in the formula editor:


IF(@ISMBR(Jan)) @VARPER (MetricA, @PRIOR( Dec->MetricA, 1, @RELATIVE( Years, 0 )))/100; ELSE @VARPER(MetricA,@PRIOR( MetricA))/100; ENDIF

Please advise what is the best way to build dimensions with such formulas!


Answer

There are two ways of doing this. You may use either of the below method .

1. Please use single apostrophe ' character before and after "

     Example '"'MetricA'"'

2. Use \ character before "

    Example \"MetricA\"

In general, we would  see formulae in account dimension or scenario dimension and most of the times these are one time build, so can be done manually. However we may come across situations where the formulae need to be built using rules files. we have to format the source file formulae as per one of the above formats.


Happy Learning!

I have published an Advanced course on Hyperion Essbase on Udemy. Here is the coupon code. Please check it out if you are interested.

https://www.udemy.com/course/hyperion-essbase-advanced-course/?couponCode=ESBADV6

HFM - On Demand Rules

Namaste! Apologies for not able to write or post any videos, lately!

Today, I would like to write about the HFM's on-demand calculations.

So far we have been executing the standard Calculate, Translate, Consolidate scripts in HFM.

However to run the portion of the calculation script such as actual to budget scenario copy etc, these

on-demand rules are real handy. Another good thing is these rules can be attached to the 

web forms.

Here is a detailed video with practical example of how to do it.

Happy Learning!

I have published an Advanced course on Hyperion financial management on Udemy. Here is the coupon code. Please check it out if you are interested.

 

https://www.udemy.com/course/hyperion-financial-management-advanced-course/?couponCode=HFMADV9

Currency Conversion using Hyperion Essbase ASO

Namaste!

Currency Conversion is an important feature for multi-entity organization., esp., if the business operates in multiple countries.

Even though an organization could span across different countries, It would be fair to say that, ultimately, numbers from different countries would have to be converted into one single reporting currency.

Usually the reporting currency would be the currency where company is head-quartered. Along with that each country has got it's own legal reporting standards too.

How this currency conversion is being handled in Hyperion suite of the products?

HFM and Hyperion Planning tools have an out-of-box features to handle the currency conversion using exchange rates that fed into application.

Hyperion Essbase has no such default feature, though we could convert an Essbase BSO Application into Currency type. It has got it's own limitations to deal with.

One of the options to achieve the currency conversion in Essbase BSO or ASO is to design custom functionality using Calculation or MDX scripts.

In this video, I have demonstrated how to design currency conversion functionality using Hyperion Essbase ASO with MDX scripting.

Happy Learning!

I have published an Advanced course on Hyperion Essbase on Udemy. Here is the coupon code. Please check it out if you are interested.

https://www.udemy.com/course/hyperion-essbase-advanced-course/?couponCode=ESBADV9

Hyperion Essbase - XREF Functionality

Namaste!

Hyperion Essbase has got many features to enhance the multi-dimensional reporting functionality.

In some of the cases few budgeting departments in an organization would require to have a separate database dedicated for their department/cost centre due to the sensitive nature of the data.


For example Payroll department may have to budget the payroll expanses using drivers like employee, grade, department etc. This budget data would have to be secured and not visible to all
other department budget owners.

Also the granularity of employee or grade wise budget would cause more sparsity and result in bigger

database/cube size. All these reasons may add up to take a call on allocation of separate database/cube for payroll budgeting.


However for a consolidating reporting purpose, it would be necessary to bring back the total payroll

cost to main database/cube.

@XREF is the most popularly used feature to transfer the data from one database/cube to another

database/cube.

I have put together a video to demonstrate the XREF functionality of Hyperion Essbase.

Happy Learning!

I have published an Advanced course on Hyperion Essbase on Udemy. Here is the coupon code. Please check it out if you are interested.

https://www.udemy.com/course/hyperion-essbase-advanced-course/?couponCode=ESBADV9

Hyperion Essbase Database Types - BSO vs ASO

Namasthe!

Hyperion Essbase is a multi dimension OLAP database system. It stores the data in multi-dimensional  format., where as relational database stores the data in row and column formats.

Hyperion Essbase has two types of databases.,Block Storage Option (BSO) and Aggregate Storage Option (ASO).

In this video, I have outlined the differences between the BSO and ASO database types.

Happy Learning!

I have published an Advanced course on Hyperion Essbase on Udemy. Here is the coupon code. Please check it out if you are interested.

https://www.udemy.com/course/hyperion-essbase-advanced-course/?couponCode=ESBADV9

HFM Multiple Language support - Demo Video

Namaste!

Hyperion Financial Management would support up to 10 languages. 

In this video, I am going to demonstrate how to enable the multi-language support using Application profile and further steps to display the Application dimension member aliases in other languages.

I have published an Advanced course on HFM in Udemy. Here is the coupon code. Please check it out if you are interested. 

https://www.udemy.com/course/hyperion-financial-management-advanced-course/?couponCode=HFMADV12

Hyperion EPM - Application support Transition Document

I have published an Advanced course on Hyperion Planning in Udemy. Here is the coupon code. Please check it out if you are interested.

https://www.udemy.com/course/hyperion-planning-advanced-course/?couponCode=PLNADV8

Hyperion Planning - Business Rules with Smart Lists

Namaste!

Hyperion Smart Lists are the convenient means of restricting the member selections on web forms. However, making use of these smart list members in business rules would require a bit of processing.

I have created a demo video on how to handle the hyperion smart lists to derive the members dynamically using "@MEMBERAT" function.

Happy Learning!

I have published an Advanced course on Hyperion Planning in Udemy. Here is the coupon code. Please check it out if you are interested.

https://www.udemy.com/course/hyperion-planning-advanced-course/?couponCode=PLNADV8

Hyperion Planning Functional Overview - Demo Video

Namaste!

I have created a video on various planning models and departments involved in an organization's planning and budgeting process.

Happy Learning.

I have published an Advanced course on Hyperion Planning in Udemy. Here is the coupon code. Please check it out if you are interested.

https://www.udemy.com/course/hyperion-planning-advanced-course/?couponCode=PLNADV3

HFM - Member Lists

Namaste!

Hyperion Financial Management (HFM) is best used for financial consolidation and reporting needs. HFM Application consists of metadata structures to be able to map the corporate chart of accounts (COA).

Metadata contains flat list of COA as well as hierarchies to meet the reporting requirements. Alternate hierarchies are another way of accommodating the various reporting  structures.

In the similar lines, Member Lists are another method of organizing the metadata to meet the specific requirements of categorization and organization of metadata.

There are 2 types of member lists., one is static member lists and other is dynamic member lists.
In this video, we will go through the static member lists.

I have published an Advanced course on HFM in Udemy. Here is the coupon code. Please check it out if you are interested. 

https://www.udemy.com/course/hyperion-financial-management-advanced-course/?couponCode=HFMADV9

Hyperion Planning - Requirements Document Preparation - 2

Namaste!

welcome back to the "Hyperion Planning - Requirements" blog series.

In earlier blog, we have discussed the Revenue and Expense Planning requirements.

https://padmaja-vemireddy.blogspot.com/2020/02/hyperion-planning-requirements-document.html

In this blog, we are going to continue with the rest of the budget models., Balance Sheet and Cash Flow Statements.

Balance Sheet Modelling

Fixed Assets Requirements

 
Requirement-8:


Total Utility Plants in Service

Description:

Total Utility Plants in Service includes:

•  Buildings
•  Intellectual Property
•  Generation and Transmission Assets
•  Other Fixed Assets

Manual input for Opening Balance to Total Utility Plants in Service as well as the Additions made during the current  period

Calculation:

Net Utility Plant:

Total Utility Plants in Service (Opening Balance + Changes in CWIP + Additions made +   Capitalized Interest)
(+) CWIP
(-) Accumulated Depreciation and Amortization

Note: CWIP is Construction work in progress


Current and Accrued Assets Requirements


Requirement-09:

Cash – Funds and Special Deposits

Description:

Cash Funds and Special Deposits include the Cash on hand, Cash in Construction Funds, Deposits for special purposes and all other Current Assets

• Provision to input the amounts

Requirement-10:

Materials and Other Supplies

Description:

Materials and Other Supplies includes raw materials apart from fuel and any other supplies required for power generation

• Provision for calculation of the value of Materials and Other Supplies

Calculation:

The Opening Balance is increased by the rate of inflation to arrive at the Closing Balance
   

Requirement-11:

Prepayments

Description:

Prepayments are the expenses paid in advance.
These are mostly Property Insurance.
They are recognized as expenses over the period in which the benefit from the expense is derived.

Calculation:

Prepayments = Beginning Balance + Prepaid during the period – Amount Expensed
   

Other Assets Requirement

Requirement-12:

Un-amortized Debt Discount and Extraordinary Property Losses

Description:

Un-amortized Debt Discount includes refinancing costs and other discounts yet to be amortized


Calculation:

Closing Balance = Beginning Balance  + Additional Costs incurred -  Amount Amortized
   


Requirement-13:

Other Deferred Debits and Accumulated Deferred Income Taxes

Description:

Other Deferred Debits are similar to Prepaid Expenses.

• Provision to track the Deferred Debits

Calculation:

Closing Balance = Beginning Balance + Additions – Amount expensed
   

Total Margins and Equity Requirements

Requirement-14:

Long Term Debt

Description:

Long Term Debt is classified as Other Debts.

• Provision to calculate the movement of Long Term debts

Calculations:

Closing Balance of the Long Term Debt = Beginning Balance + Additions – Repayments

Closing Balance = Beginning Balance + Additional Debts incurred - Principal Repayments
           
Net Interest Accrued unpaid = Gross Interest Accrued - Interest Payments (Expensed) - Interest Payments (Capitalized)

   

Current and Accrued Liabilities Requirements

Requirement-15:

Accounts Payable

Description:

Accounts Payable includes all the expenses which are due, but unpaid.

• Provision to calculate Accounts Payable

Calculations:

Accounts payable is calculated as follows

            Closing Balance =
            (+) Beginning Balance
            (+) Operating and Maintenance Expenditure
            (+) Capital Expenditure Payable
            (-) Fuel Cost
            (-) Payroll related Adjustment
            (-) Payment made (This is assumed to be the same as the Beginning Balance)

  

Requirement-16:

Taxes Accrued

Description:

Taxes Accrued comprises Property Taxes and Income Tax.

• Provision to calculate the Accrued Taxes at the end of the period
• Provision for entry of Taxed paid
• Provision for entry of Expense Accrued (Taxes)

Calculation:

Beginning Balance  + Total Property tax  -  Property tax payments made + Federal taxes – Taxes paid
   

Requirement-17:

Interest Accrued

Description:

Interest Accrued includes the interest payments which fall due but are unpaid

• Provision to calculate the Interest Accrued at the end of the period
• Provision to input Interest Payments made
• Provision to input the Accrued Interest

Calculation:

Closing Balance = Beginning Balance  + Interest accrued for the period – Interest payments made
   

Requirement-18:

Other Current and Accrued Liabilities

Description:

All Other Current and Accrued Liabilities are contained within this item.

• Provision to input Additions to this account
• Provision to input Deductions to be made from this account

Calculation:

 Closing Balance = Beginning Balance  + Additions during the period – Deductions during the period
   


Requirement-19:

Accumulated Operating Provisions

Description:

Accumulated Operating Provisions are made for operating expenses.

• Provision to calculate Accumulated Operating Provisions
• Provision to input the inflation rate

Calculation:

Closing Balance = Opening Balance + Inflation Rate
   

Cash Flow Modelling

The Cash Flow statement would be prepared using the Indirect Method, where Net Income is the starting point for showing the movement of cash.

Temporary Investments on the Assets side of the Balance Sheet is arrived at using a combination of Direct and Indirect methods.

Requirement-20:

Operating Receipts

Description:

Operating Receipts include the Revenue Receipts from retail, Industrial, and other Sales. It also includes the Gain on Sale of Allowances and Interest Earnings

Provision to calculate Operating Receipts

•         Revenue – Retail
•         Revenue – Industrial
•         Revenue – Other Sales
•         Gain on sale of Allowances
•         Interest Earnings

Calculation:

The Revenue figures are calculated by multiplying the respective Effective Rates by the respective Sales volume.


Requirement-21:

Production Over head and Maintenance (O&M)

Description:

• Production O&M costs are Operations and Maintenance Expenditure paid out in cash
• Provision to calculate Production O&M

Calculation:

Operating Expense-Production-Excluding Fuel + Maintenance Expense-Production



Requirement-22:

Administrative & General

Description:

Administrative & General (A&G) costs include customer service, sales related and administrative expenditure as well as General Plant Maintenance paid out in cash

• Provision to calculate Administrative and General (A&G) Costs

Calculations:

Operating Expense(Customer Service and Information) + Operating Expense(Sales) + Operating Expense(Administrative and General) +  Maintenance Expense -  Site Lease Expense


Requirement-23:

Changes in Working Capital

Description:

The Changes in Working Capital item captures movement in Working Capital during a given  period by calculating the difference between the Opening and Closing Balances of Working Capital items (e.g., Current Assets and Liabilities)

• Provision to calculate Changes in Working Capital.

The Beginning and Closing balances of the following Working Capital Assets and Liabilities:

•             Accounts Receivable
•             Materials, Supplies & Other
•             Prepayments
•             Other Current Assets
•             Accounts Payable
•             Taxes Accrued
•             Other Accruals

Calculations:

Closing Balance – Opening Balance


Note:    Negative number on a liability indicates an outflow



Requirement-24:

Income Taxes from Operations

Description:

Income Taxes from Operations includes Income Taxes paid out in cash.

• Provision to input Income Taxes

Requirement-25:

Other Disbursements

Description:

Other Disbursements includes other miscellaneous payments made in cash

• Provision to input and calculate Other Disbursements

Calculations:

Sum of all the individual expenses


Requirement-26:

Capital Expenditure

Description:

Capital Expenditure includes Generation, Transmission, Buildings and Other Capital Expenses paid out in cash

• Provision to input Capital Expenditure
• All outflows towards Capital Expenditure (Captured as part of Total Utility – Assets)
• All Capital Inflows, if any

Calculations:

All Cash Outflows towards Capital Expenditure – All Capital Inflows

In next blog, we are going to wind up by disusing the rest of the topics.

Those are., Reporting requirements and Security Matrix.

Happy Learning!

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/

Hyperion Planning - Requirements Document Preparation - 1

Namaste!

welcome back to "Hyperion Planning - Requirements" blog series.
In earlier blog, we have discussed the questions to be asked during requirements workshop.

https://padmaja-vemireddy.blogspot.com/2020/02/hyperion-planning-requirements-workshop.html

After the requirements workshop has been conducted, all the responses for "requirements questionnaire" from the stake holders has to be collected, analyzed and prepare the "Business Requirements Document (BRD)" or "Functional Specification Document (FSD)".

Today, we will discuss how a typical BRD or FSD looks like.

It all starts with outlining the current budget system/processes in place with client.

Imagine we are preparing this document for a hydro electric company's budgeting requirements.
 
Here is a sample write-up on overview and current state.,

Overview:

The Functional Requirements Document captures the requirements of Client with regard to their Budget and Forecast process. The current budget cycle is manual-intensive incorporating spreadsheet distributions to maintain budget and forecast data. The process is time consuming, person-dependent and prone to inconsistencies.

As-Is Process/Current State of budgeting and reporting processes:

• The budgeting of revenue from the customers is based on usage and other factors.
• General and Administrative expense budgets are provided by the Budget Analysts from various functions/departments.
• The sites provide the budgets for the Operations and Maintenance Expenditure. All other expenses are budgeted based on the prior year actuals.
• Labor costs are calculated on separate spreadsheets using headcount and work hour data.
• All other expenditure items are input, based on prior year actual figures.
• The Trial Balance Budget is prepared followed by the Income Statement in order to provide the Net Income Budget.
• Balance Sheet budget is prepared using opening balances from the previous period. Additions and deductions are provided from relevant income statement budget items or recalculated every period depending on the budget item.
• Cash Flow Statement is prepared using outcomes from the Net Income and movement in current assets and liabilities.
• Forecasting is based upon the current budget.
• Variances are spread across the remaining current budget period dependent of Expenditure Type.
• Actuals are obtained from the Oracle EBS GL system and compared to budget items to arrive at variances.
• Reporting is done on budgeted financial statements, site expenses, labor and overall variances.
• Reporting is handled at the department level and then combined through spreadsheets for overall level reporting. 

After explaining the current state of the budgeting system/processes, the next step would be documenting the functional requirements. Please note that, this section would be combination of the current processes as well as the future or new requirements.

It would be very important to review the functional requirements with the client, time-to-time. Upon receiving the feedback from stake holders, any gaps in understanding the requirements could be patched up!

I would put the high level requirements into following broad categories:

• Revenue Budget Modelling
• Expense Budget Modelling
• Capex Budget Modelling
• Manpower Budget Modelling
• Report Templates/Definitions
• Workflow requirements
• Security Matrix

Revenue Modelling Requirements:

The revenue budgeting process involves arriving at the rates for various customers, taking into account the different calculations and adjustments involved.

Each Customer Type is associated with a different rate structure. The rate structures are derived by making calculations and adjustments as per statute and contractual obligations. The rates are then multiplied by the usage to arrive at the revenue budget figures.



Requirement -1:
Sales

Description:    

• The term, Sales, defines the usage amount hydro power sold to members.
  Provision for entry of usage by Customer Type
• The usage figures are entered for each customer type, month and year.
• Household rates
• Industrial rate

Requirement -2:
Miscellaneous Income

Description:

• Income generated apart from selling hydro power
• Provision for input of the various types of Miscellaneous Income
• All types of miscellaneous income.,Interest Income, Rental Property (For Month, Year, Account)

Expense Modelling Requirements:

The Expense Budget is a combination of the G&A (General and Administrative) budgets, O&M (Operations and Maintenance) budgets and Other Expenditure budgets.

These are primarily straight figures based on previous year Actuals. However, in case of G&A expenditure, the gross budget amounts entered by the Budget Analyst are split between the Departments.

Requirement -3:
General and Administration Expenditure

Description:

General and Administration (G&A) Expenditures are overhead rates which are incurred by the functions or departments. The Budget Analyst would enter a gross amount as the budget for his/her responsibility organization for G&A expenditure.

• Provision for distributing the gross amount to various departments based on predetermined percentages using a  general/common account for the gross amount.
• Provision for input of amounts to G&A accounts directly if no distribution is required. 
• Provision for allocation to Sites.
• Provision for input of the gross amount at Site/Location level. 

   
Calculations:

• The ratio is to be applied to the gross amount and charged to the relevant G&A accounts by organization. 
• The general/common account is split based on the Departments.

   

Requirement -4:
Operations and Maintenance Expenditure

Description:

Operations and Maintenance (O&M) Expenditures are overhead rates incurred at the Sites for Operations and Maintenance.

• Provision for input of the O&M Expenditure at Plan) / Location level 
• Provision for allocation to Sites

Requirement -5:
Other Expenditure

Description:

Other Expenditures are overhead rates incurred at the Corporate level:

• Property Tax
• Total Interest
• Depreciation
• Provincial Tax
• Federal Tax
• All other non-operating expenditures marked as overhead expenses

• Provision for input of the Other Expenditures at Corporate level 
• Provision to enter the supporting detail 
• Provision for allocation to Sites

 
Requirement -6:
Labor Costs - Expense

Description:

Labor Costs are tracked at the Organization level. The rates for each employee are maintained in Oracle HRMS.

The following are the rates associated to Labor Costs (each salary plan/department combo has different rates):
           

• Standard Rate: The rate employees are paid
• Increase Rate:  The rate the standard pay is increases periodically based on planned             merit increases
•  Overtime Rate: The rate overtime pay is calculated
• Burden Rate:    The overhead rate added to the pay (includes healthcare, pension, etc)

Note: Standard and Overtime costs have different Burden Rates.

Once Labor Costs are calculated, they are allocated to different COA accounts and departments

• Provision to distribute Labor Costs to expense accounts as per COA.
• This is to be done by entering all the Labor Costs to a common labor project & common labor task and distributing it based on per-determined percentages to various COA accounts 
• Provision for allocation to Sites

Calculations:

• Standard Labor Cost is a product of Standard Rate and Hours
• Overtime Labor Cost is a product of Overtime Rate and Hours
• Burden Rate is applied on the total cost – separately for Standard and Overtime
• Percentages are applied for allocation of the total costs

   

Requirement -7:
Labor Costs - Capital

Description:

Labor Costs incurred in Capital Projects are capitalized.
The portion which is to be capitalized goes into a capital account.
The Labor Costs which are not capitalized are split between different COA accounts based on              predetermined percentages.
Capital costs are incurred only by IT and Sites

• Provision to enter the budget for capitalized labor by Project
• Provision to extract the labor cost to be capitalized from the total Capital Budget
• Provision to maintain the Capital Budget in Hyperion
• Provision for allocation to Sites

In next blog, we will discuss the following requirements

• Balance Sheet requirements
• Cash Flow Statement requirements

Happy Learning!

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/

Hyperion Planning - Requirements Workshop

Namaste!

Welcome to the Hyperion Planning blog series. Earlier I have written a blog on Hyperion Financial management (HFM) requirements gathering, analysis and preparation of Business Requirements Document (BRD). It was quite lengthy 5 series blog.

This time I want to write a bit crisp version of  Hyperion Planning related, right from the preparation of requirements questionnaire for workshop, analyzing the responses from stake holders and finally prepare the BRD or FSD (Functional specification document) before moving on to the next phases of Hyperion Planning Implementation life-cycle.

As you know, all projects kick start with project charter on hand to understand very high level scope of the project.

As a first step, it would be very important to understand the current state of the planning, budgeting and reporting process in place. Once we identify the stake holders ., primarily with FP&A (Financial Planning and Analysis) department, conducting the several workshops to interact with them would be the next step.

Here is the list of questions that would help us to understand the current landscape of the planning, budgeting and reporting systems or processes.

This is not an exhaustive list for sure but it would get you going.

1    Please explain the current budgeting and forecasting cycle?

Please expect client's response as described below. This is a sample write-up for your understanding.,

The client's annual budget process is a traditional incremental budgeting.  Under this process, budgets are developed and reviewed in the context of the previous year’s budget with funding decision-making primarily focusing on the incremental change to the budget.

Currently budget process has both bottom-up and top-down characteristics.  From corporate strategy stand point it would be a top down and all functions/cost centres use bottom-up approach

Though strategic plan and limited performance data is incorporated into functions budget request submissions every other year, this information does not align to a comprehensive company wide strategic plan and typically serves as contextual or reference information rather than as a driver for the allocation of budget resources. 

Further, the company capital budget does not directly integrate to the operating budget. Hence, decisions regarding capital investment are often made without direct access to information regarding the impact on long-term operating expenditures.

The Client currently develops its annual budget using a disparate collection of software tools throughout the budget development and execution life-cycle.  Accordingly, the company's budget process has been aligned around these different tools and can be characterized as having many discrete sub-processes with limited integration.

The lack of an integrated, end-to-end budgeting application drives many inefficiencies, puts constraints on budgetary analysis, and lacks a platform for effective implementation or integration of current or future policy initiatives to the budget process such as strategy management to support Budgeting for Results or linking capital and operating budgeting.  

2    How frequently would forecasting be performed (Monthly/Quarterly)?

3    Is there any requirement of Rolling Forecast (12 month at any point of time) 

4    Is P&L Budgeting and Forecasting in-scope?

5    Is Workforce and Compensation Planning in-scope?

6    Is Balance Sheet Planning in-scope?

7    Is Cash Flow Planning in-scope?

8    Is Capex Planning in-scope?

9    Is Projects Planning in-scope?

10    How many number of  users would be using the future budgeting and forecasting system?

11    Does budgeting use single or multiple currencies?

12    What is Reporting Currency?

13    Does Budget write back to ERP GL is in-scope?

14    Does Revenue Planning and Forecasting done at Location level?

15    Does budget/forecast preparation done at location level but entered into Planning & Budgeting application by Region level FP&A Team?

16    Would you anticipate more granular level (detailed) than GL accounts level for Planning and forecasting?

17    What would be the Time granularity for planning, budgeting and forecasting?

18    Would you anticipate to plan the Revenue and Opex based on prior year/month's actual average trends and to be able to make adjustments on top of the trends?

19    Would you plan to use the driver based revenue or opex planning?

20    Does workforce compensation planning and budgeting to be done at employee level, job level or Employee and job level?

21    Are there any compensation related expenses driven by formula rather than direct input?

22    How hourly based expenses are going to be budgeted (Hourly full time vs Part time)?

23    Would you anticipate to plan employee transfer between departments and Locations?

24    Would you anticipate to plan employee terminations/departures/retirements in a budget cycle?

25    Does Opex planning happens by department using GL accounts?

26    Identify and group the Opex GL accounts that are relevant to departments is in-scope?

27    Are "Facility Expenses" going to be derived using drivers or Direct input?

28    Are "Travel and Entertainment Expenses" going to be derived using drivers or direct input?

29    Are "Marketing Expenses" going to be derived using drivers or direct input?

30    Are "Selling Expenses" going to be derived using drivers or direct input?

31    Are there any Expenses to be allocated for reporting purpose?

32    Do you have any list of Existing Asset details for Depreciation calculations?

33    How many new assets to be budgeted for budget cycle?

34    What is Depreciation method for New Assets?

35    Are existing and new "leased assets" depreciation calculations in-scope for budgeting?

36    Is Asset transfer process in-scope for budgeting?

37    Is  asset retirement process in-scope for budgeting?

38    How many financial reports to be built for management reporting?

39    Does Variance reporting generated from Budgeting or GL Systems?

40    Do you have any other reporting requirements that budgeting systems should meet?

I will prepare a sample requirements document (BRD / FSD) based on the common responses for the above questions in next blog.

Happy Learning!

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/

HFM 11.2 Business Rules - Translations using Historical Rates

Namaste!

In Hyperion Financial Management, we have a common requirement to translate the data using:

• Average rate for Income statement accounts
• End of month rate for Balance sheet accounts

However below accounts would need to translate using Historical rates. Some of these accounts are:

• Investment in Subsidiaries
• Prior year retained earnings
• Investment in properties

In this video I am going to explain how to write the business rule to achieve the translation for these accounts using Historical Translation Rates.

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/

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/