herbalife's journey to world class by implementing oracle

COLLABORATE 14 Copyright ©2014 by Deanna Sunde

Herbalife's Journey To World Class by Implementing Oracle Hyperion Enterprise Performance Management

Deanna Sunde

Abstract:

Herbalife, operating in 70+ countries, has adopted a phased approach in its journey to world-class EPM.

The first leg was to implement Hyperion Planning, Hyperion Financial Management and Essbase

reporting. Later phases included calculating transfer profit, implementing cash flow reporting and rolling

forecasts, and an application to better analyze results and spot trends with commentary included. This

presentation will describe the phases and architecture and help you roadmap your own journey to world

class.

World Class

Why aspire to be world class? Because there is normally a 20% to 50% improvement opportunity in

savings and in working capita, as studies have shown.


World class companies have shorter budgeting and forecasting cycle times, budget and forecast

fewer line items, produce fewer reports and leverage a central data repository. Less time is

spent collecting and compiling data for analysis and ad-hoc requests are fulfilled faster. Studies

have shown that less detail actually leads to more accurate results. World class EPM

companies deliver 2.4 times the earnings per share of industry-peer companies.

When it comes to world class performance, the adage “it’s the journey not the destination” holds

true. First “do the right things” then “do the right things the right way” then “do things at the right

place and price.” Throughout the process, there is strategic alignment with business

requirements and best practices are followed for Process, People and Organization, Technology

and Partnering.

World-Class EPM

Performance Differentiators

Budget cycle time Complete the budget 25% faster

Budget detail 19% fewer line items in the budget

Forecast cycle

time5 days or less compared to 2-3 weeks

Forecast detail Far less detail than annual budget (<30

lines), at peers level of detail is typically the same (250+)

Reporting

volumes

59% fewer performance reports per $B

revenue

Technology

leverage

47% more performance reports generated

from a central data repository

Time allocation 1/3 less time spent collecting and

compiling data for analysis

Ad-hoc cycle time 28% faster at turning around ad-hoc

requests

Peer Group World-Class

1.0

2.4

World-Class EPM companies deliver

2.4 times the earnings per share of industry-peer companies.


Herbalife Background

Herbalife has revenues of $2.7 billion and 4,500 employees. Herbalife manufactures and distributes

weight control products including meal replacements, snacks and "enhancers." The company has

international subsidiaries in more than 70 countries. Herbalife's multilevel marketing program involves

some 2 million independent distributors throughout the world.

Business Issues and Goals

Herbalife undertook its journey to world class to address some key business issues. The close cycle was

too long – 14 days. There was a lack of consistency in financial reporting and variance explanations.

The close and forecasting process were inefficient and the actual and forecasting processes were not

well integrated. Timely, accurate information was not readily available to the global stakeholders.

Solution

In order to resolve these issues, Herbalife migrated from a legacy planning application that was heavy

with customizations and an Excel based close process to a full EPM solution. Several hundred users

were consumers of Oracle Hyperion Financial Management (HFM), Financial Data Quality Management

(FDQM), Oracle Hyperion Planning and Oracle Hyperion Essbase.

The Essbase application was an ASO (aggregate storage option) application which allowed for more

detailed, granular reporting with more history than what was available in HFM or Planning and enabled

instant aggregations and storage of many constant rate scenarios.

As a result of implementing the solution, there was increased data transparency, improved internal

controls, reduced external audit costs and reduced effort to close the books. The solution enabled

regional locations to participate and sign-off on current financial results and commitments and also

enabled regional and corporate topside hedging.

Forecast accuracy improved, the solution enabled what-if analysis, the effort to manage and administer

the Planning process was reduced, and the transfer profit tax impact forecasting was automated.

Better reporting was made available – including product and project driver based profitability analysis and

allocations. The effort for financial reporting was decreated through automated intercompany eliminations

and F/X translation and the system supported multiple reporting and elimination rollups.

Phased Approach

Herbalife has approached its journey to world class in phases.

Phase 1 – “Big Bang” – implementation of HFM, Planning and Essbase ASO Reporting

Phase 2 – Transfer Profit – Transfer Pricing for Inventory adjustment for intercompany sale. This was

done in a new Planning application.

Phase 3 – Upgrade of environment from 11.1.1.3 to 11.1.2.1.

Phase 4 – Implementation of Rolling Forecasts in existing Planning applications.

Phase 5 - Implementation of Cash Flow in existing HFM application.


Phase 6 – Analytical Application to analyze variances and trends “apples to apples” for year-over-year

analysis. This was done in a new Planning application.

Additional, there were a number of smaller enhancements that were implemented.

“Big Bang”

The diagram of the Phase 1 “Big Bang” approach is shown below. This initial phase was the

implementation of HFM, Planning and Essbase ASO reporting.

EPMA was used in the solution to centrally manage the metadata for all the Hyperion applications.

The HFM application is multi-currency and includes many complex intercompany eliminations and both

legal and management hierarchies.

The Planning environment includes a Product and Project revenue and expense plan type, an

intercompany matching plan type (summarized data is copied to the main planning application), a balance

sheet plan type (for future use) and a workforce planning application. The budget is done zero-based and

subsequent forecasts for the year are pre-seeded from the budget or the prior forecast. The planning

application includes the management hierarchy but not the legal hierarchy.

Intercompany eliminations are required for actual in total. This was accomplished using the

ELIM_ENTITY member. Elims are not needed for budgeted/forecasted periods. The HFM eliminations

and adjustments are loaded into the main planning application. Cash Flow reporting was not required for

budgets and forecasts. Planning is done at a higher level than in HFM for the entity dimension. The


solution did this by using one entity to represent a group of entities through the use of an alternate

hierarchy. The accounts are at the same level as HFM.

Both of the planning applications are multi-currency. The currency requirements were somewhat

complex. Besides all the constant rate scenarios, translation to “Parent Currencies” were also needed.

The bottom level entities were translated from the local currency to USD and in some cases the level 1

parent had a different currency than its level 0 children (and something other than USD.) To meet this

requirement, the Currency dimension contained the members Local, USD and Parent Currency.

The BSO (block storage option) Essbase application was used in the architecture for several reasons. It

provides more flexibility for calculations than the ASO reporting application. YTD data was loaded into

Staging and the periodic amounts were “backed into” and loaded into the Planning application. YTD data

was loaded into HFM. In this manner, all the applications relied on the same data set. They were always

in sync and the YTD figures were always correct. The constant rate scenario calculations were also

performed in the Staging application. The amount of data and number of calculations were too much to

store in Planning and once the calculations were run they were loaded into the ASO reporting application.

The chart below shows the dimensionality between the different applications/plan types.

The Data Type dimension is useful for HFM, Planning and Essbase ASO reporting applications for

several reasons. It segregates the data loaded by source which facilitates data conversion and

validation. It segregates data that is calculated vs input vs loaded. This facilitates data loads from HFM

to Planning/Essbase, facilitates building security (read vs write) and allows the users to easily make

adjustments on top of loaded or calculated results. It also is useful for reporting and audits as it builds up

the actual from local G/L’s to corporate GAAP to IFRS.

The graphic below shows a simple example of a Data Type dimension.


Transfer Profit

The Transfer Profit Planning application was built to account for transfer pricing for inventory adjustments

for intercompany sales. The result of the calculation (from a Financial Reporting report) was sent to HFM

as a journal entry.

Issues with the prior process included: lengthy manual process done in Excel spreadsheets, difficult to

manually breakdown items in BOM that carry transfer profit, difficult to identify F/X impact in ending

inventory and COS, had to manually prepare journal entry for the elimination process and the entire

process left a lot of room for human error.

The process has the following steps: (1) data loaded from Oracle EBS after the books were closed

between the 1st and 4th day of the month (2) the load included the ending inventory and units purchased

by SKU, standard costs in functional currency of the selling entity, selling prices for purchasing entities

and currency rates (3) two planning forms were used by the users to adjust loaded data (only for

exceptions when the data in Oracle was incorrect) (4) the Essbase calculation would run for

approximately 40 minutes to do all the transfer profit and F/X calculations (5) Hyperion would

automatically prepare an HFM JE for elimination process using an Financial Reporting report.

Benefits – (1) Saved 50 man-hours a month (2) improved the accuracy of the calculations (able to capture

items from BOM) (3) reports were available to identify FX impact to both ending inventory and COS.

The dimensionality of the Planning application is shown below.


One challenge in building this application was how to come up with the purchased amounts in the months

that made up the ending inventory. Different currency rates needed to be applied so the month of the

purchase impacted the calculation. To resolve this, a transfer profit period (TPPeriod) dimension was

used. This “transposed” the data in the ending inventory to the appropriate month and year (going back a

maximum of 3 years.)

In order to do the currency translation, an attribute was applied to each bottom level entity. The currency

rates reside in the accounts dimension. The Currency dimension contains the members “LocalCurrency”

and “USD” (translated to USD). The code below is an example of how to do the translation with these

requirements. The code is much simpler than using UDAs as the translation can be done with one line of

code rather than one line per currency.


FIX(@RELATIVE("ENT_GLOBAL",0),@RELATIVE("ICP_GLOBAL",0)) FIX(@RELATIVE(“OPEX”,0)) "USD_RPT" = "LOCALCURRENCY“ * @MEMBER(@CONCATENATE(@ATTRIBUTESVAL("CURRATTR"),"AVGRATE"))-> "No Entity"->"NOCURRENCY"->"No ICP”; ENDFIX ENDFIX

Another coding challenge was to get all the data from the seller to the purchaser so all the calculations

could be done on the purchaser-side. The code is below. UDAs had to be applied to the entities for the

code to work (this was version 11.1.1.3).

SET CREATEBLOCKONEQ ON; /* Copy Standard Cost and Selling Price from Seller Entity to Purchaser Entity */ FIX(@RELATIVE("Product",0),"STNDCOST",@RELATIVE("SELLPRICE",0),"USD_RPT") FIX(@UDA("Entity","LISTENTITY"),@UDA("ICP","LISTICP")) "PURCHASERSIDE" = "DATAFINAL“-> @MEMBER(@SUBSTRING(@NAME(@CURRMBR("ICP")),4))-> @MEMBER(@CONCATENATE("ICP_",@NAME(@CURRMBR("Entity")))); ENDFIX ENDFIX

Some design tips and tricks for transfer pricing and allocation projects:

Methodology

Get buy-in from business and stick to a specific methodology or set of methodologies

Avoid hard coding and tweaks to get back to legacy results – this is time consuming and

complicates the code, making ongoing maintenance difficult

Automated

Does not require input from users at run-time

Runs automatically when data is input or loaded

o Not always feasible as blocks can get locked when multiple users saving data and

running the same allocation

o Run periodically (hourly, nightly)

Flexible / Low-Maintenance

“Surface” drivers instead of hard-coding in scripts

Build scripts using attributes, UDAs, alternate hierarchies in order to avoid hard coding

Put power in the hands of the users

o Input drivers that are not automatically loaded

o Ability to adjust or override drivers

o Ability to adjust or override calculated results

Build scripts that consider what to do if driver data is missing


Easy to change drivers – but the more flexible, the higher the cost to build

Repeatable

Get the same results every time

Recommend that all loaded, input and calculated data be at the bottom level of all dimensions

o Allows data to be exported at level zero, re-imported and aggregated for de-fragmentation purposes

o Assists with backup and restore

Transparent

If allocation is by entity - Create ICP (Inter Company Partner) dimension

If allocation is by Cost Center - Create sending Cost Center dimension

Ability to drill from receiver to sender using Smart View or Financial Reporting

For additional dimension, add prefix

o Allows using substring and concatenate in code

Rolling Forecasts

More and more companies are implementing rolling forecasting and/or multi-year forecasting. This is a

significant trend and is also considered best practices. While this concept has been around for a while,

leading companies are adopting some interesting variations to suit the needs of their company and

industry.


Here are some things to consider when implementing rolling forecasts.

Decide whether to create a new app or modify existing app (migration/cut-over plan). Update metadata –

possibly periods, years, scenarios, versions. Create substitution variables for periods/years of the rolling

forecast (starting with version 11.1.2.2 Oracle makes this easier for you). Update data forms. Update

business rules attached to forms. Create/update seeding calcs. Update “catch-all” calc scripts (calc

scripts that do all the calculations and run in off-hours). Update interfaces and XREF’s. Update Reports.

Unit Test, Training Materials, UAT, Migrate, Admin Documentation and knowledge transfer.

As you can see, this is a very long “to do” list. Do not underestimate the time to complete the project.

And when calculating more years, it is very important to test performance. An application that is working

just fine may not work so well when calculating more years. Test the business rules on forms. Test all

the calc scripts. You may need to change the sparse/dense settings in order to get acceptable

performance. If the settings need to be changed all business rules and scripts would need to be

reviewed, updated and re-tested. Again, don’t underestimate the time it takes to go from a single year

forecast to rolling forecasts/multi-year forecasts.

Cash Flow

The implementation of cash flow reporting to the existing HFM application was on a rapid implementation

schedule. The design and initial build was about 2 weeks and validation started during the third week.

The Cash Flow account hierarchy was built based on the existing CF report. Metadata was driven by

Cash Flow rules and calculations. Cash Flow components are the base level accounts and aggregated to

‘reported CF’ parent accounts.

HFM rules were set up to reference account names and properties to calculate dynamically. There was

no need to update the HFM rules to change the CF calculations, only the metadata change is required to

redefine the Cash Flow calculations.

Roll-forward / flow members are added in custom dimension (Custom3) and applied on B/S accounts.

Roll-forward data is collected via HFM data forms

Cash Flow reports are based on the account hierarchy, allowing standardized view and maintenance for

Financial Reporting reports.

Analytical Application

The business objects of the Hyperion Planning analytical application was to (1) get data “apples to

apples” for year-on-year analysis (2) automate the preparation of YTY & QTQ Flux Analysis (3) automate

the Flash Reconciliation (4) automate the five main analytical reports - Consolidated P&L report,

Consolidated Gross Profit analysis, Country level Gross Profit analysis, Consolidated COS Trend, and

Country Level COS Trend reports (5) automate related sub schedules to the five main reports to expedite

the month-end closing process. This automation will allow more time for analytical work and minimizes

the risks of errors. Note: “Gross Profit” refers to the analysis of revenues, COS and Gross Profit (but at a

high level, not by individual product.)

Flux Analysis - these two reports are to compare data components of gross profit analysis between two

consecutive years. These two reports are generated at consolidated level only and are used for 10Q

reporting. YTY and QTQ reports are used.


Flash Reconciliation - this report is comparison between forecast data and actual gross profit components

on month to month basis. Forecast data is provided by Planning Department and is manually entered in

Flash Reconciliation Data form by user. This report doesn’t support YTY or QTQ reconciliation.

P&L Consolidated - Consolidated report by each product category. It shows the balances for each month

for current and previous year and the trend for the previous 12 months. Adjustments related to each

period are entered in data form by country for each month and include individual entity normalization

items and consolidated normalizaiton items.

Gross Profit Consolidated - this is a global consolidated report to calculate gross profit. It shows the

gross profit trend for current year and prior year. It includes normalization items with recurring and non-

recurring components.

Gross Profit by Country - this report generates gross profit analysis for all entities. It shows the gross

profit trend for current year and prior year. Normalization items specifically for that country are included in

this report.

COS Trend Consolidated - this report shows the monthly trend of normalized standard cost as a

percentage of Retail sales at the consolidated level for each product.

COS Trend Local - this report is to show the monthly trend of normalized standard cost as a percentage

of Retail Sales for each entity before elimination for each product category by country.

The dimensionality of the application is shown below.

The key to the application is the use of the Analysis dimension, which is shown below.


Data loaded from HFM is loaded to the member “BEFORE_ADJ”. “General” normalization items are

primarily the result of calculations and are under the parents “GEN_SALES” and “GEN_COS”. The other

normalization items are input by the users and are under the parents “NML_SALES” and “NML_COS”.

For year-over-year reporting purposes, the member “AFTER_ADJ” is used.

The data type dimension, shown below is also a very important component of the application.


The “typical” structure is under the “REPORTING” data type. Data input by the users, either drivers or the

normalization item, is input to the member “NML_INPUT”. Calculations (the “General” items) are

calculated in the member “NML_CALC”. “NML_INPUT”+”NML_CALC” is copied over to the five reports

based on Yes/No flags. The data is copied to the members “PL_ADJ”, “CT_C_ADJ”, “CT_L_ADJ”,

“GP_C_ADJ” and “GP_CTRY_ADJ” based on the yes/no flags. Yes, the normalization should be part of

the report or #MISSING, no the item should not be part of the report.

Some normalization items were appropriate the first year they were used but when comparing this year to

last year, the item for last year is no longer appropriate. Again, this is flag-driven. The reversal of the

adjustment is in the members “PL_RESTATEMENT”, “CT_C_RESTATEMENT”,

“CT_L_RESTATEMENT”, “GP_C_RESTATEMENT” and “GP_CTRY_RESTATEMENT”.

Many of the general items are recalculated for the prior year when comparing to the current year. In this

case, the original values are reversed in the restatement members as above and the numbers are

recalculated in the “NML_PY_CALC” member and then copied based on flags to the members

“PL_RECALC_PY, “CT_C_RECALC_PY”, “CT_L_RECALC_PY”, “GP_C_RECALC_PY” and

“GP_CTRY_RECALC_PY”.

For year over year reporting purposes, the appropriate members to choose for the Consolidated COS

Trend report, for example would be:

This Year Last Year

Data Type CT_C CT_C_RESTATED

The forms within the application are shown in the list below:


The form below is for the users to enter normalization items for sales. They select the entity, product

category, year and normalization item. The columns allow them to enter in Local Currency and/or USD.

Each bottom level entity is assigned an attribute so that the currency translation can occur. This makes

the coding of the translation very easy (see the Transfer Profit section for an example of the code). The

rows are the accounts. Previously when using spreadsheets, only a few accounts could be used. Now

the users may put in all the details that they wish.


The form for the current year normalization flags is shown below.

The form for the prior year normalization flags is shown below. Data validation has been added to color-

code cases where the flags are different for this year vs. last year. This helps the users to identify which

ones were in place originally but are restated when comparing this year vs. last year.


The report below is an example of how the users can view the original figures and then the normalization

items to get to the adjusted numbers.

One challenge in building this application was the “unwinding” of the adjustment entities and elimination

entities. There were multiple receiving entities to multiple sending entities. The code below performs a

“looping” process to accomplish this. Each receiving entity has an attribute (“EntityID”) to indicate the

original sending entity. This relationship is used to “unwind” the adjustments.

FIX("Contribution Total") FIX("NML_CALC",@RELATIVE("Product",0)) /* GEN07: Packaging and Handling (1.4.1) */ FIX(@DESCENDANTS("E91010",0),@DESCENDANTS("E91020",0),@DESCENDANTS("E91030",0), @DESCENDANTS("E91040",0),@DESCENDANTS("E91050",0),@DESCENDANTS("E91060",0), @DESCENDANTS("E91070",0),"NO_ENTITY","441000") "GEN07A"( IF(@ISMBR("441000")) "GEN07A"->@MEMBER(@SUBSTRING(@ATTRIBUTESVAL("EntityID"),3))=- -"BEFORE_ADJ"->"REPORTING“)+"NML_CALC"-> @MEMBER(@SUBSTRING(@ATTRIBUTESVAL("EntityID"),3)); ENDIF ) ENDFIX FIX(@RDESCENDANTS("ADJ_ENT",0),"441000") "GEN07A"=-"BEFORE_ADJ"->"REPORTING"; ENDFIX


Conclusion

Herbalife is well into its journey to world class.

The next steps for Herbalife will be a second phase of Transfer Profit which will have more complex

relationships (selling entity to intermediate entity to purchasing entity) and a second phase for the

analytical application.

Good luck in your own journey to world class! I hope that this case study has inspired you.

herbalife's journey to world class by implementing oracle

Documents