Platform Specialists

A Piece of Pie

Financial planning and budgeting is the act of forecasting future financial performance and is a vital process for every company, big and small.  Oracle provides a mechanism to improve the financial planning process using the Oracle Hyperion Planning tool set. Through Hyperion Planning, a company can create applications and processes that consolidate data, run business logic, and generate forecasts based on historical reference data and future assumptions.

There are two methods for building a Hyperion Planning application – referred to as “Classic” and “EPMA”.

A Classic build is the process of building a single, standalone application in the cloud or in a locally installed version of Hyperion.   The alternate method of building an application is through the use of EPMA (Enterprise Performance Management Architect) – a mechanism to share metadata across a suite of Hyperion applications.  EPMA is a tool that centrally manages Hyperion application structures and related settings.  For example, EPMA can be used for creating centralized account structures that are shared across forecasting and consolidation applications.

The sweet thing about building in EPMA is that it makes things easy – as easy as pie.

In what can only be described as a happy coincidence, building a Planning app is a lot like a building a pie, a pie where the tin is made of EPMA, with a flaky Essbase crust and a filling consisting entirely of data.

De-li-cious.

Seating the Data: Artisanal Pie Tin

As Planning pie makers, we want to make sure that our app comes out structured in a way that's valuable to the user. This why, like any artisanal shop, we handcraft everything - even the pie tin.

We create the perfect pie tin to structure our app using EPMA.

EPMA is where we define the application's structure, either by creating it manually or importing a hierarchy into the shared library. There are staple hierarchies that we’ll see again and again, like an account dimension, which holds a company’s chart of accounts, or an entity dimension, that might represent a company’s reporting structure. Additional dimensions will appear based on the needs of the business.

Once we're happy with our structure, we can create and deploy our application. At this point, the tin is popped out of its mold and is ready for duty. If we want to make changes to the structure of the Planning app, we would make the changes in EPMA and then re-deploy the application. This will replace our old pie tin with a new pie tin, comprised of our new structure.

In this case we are going to move forward using our tin (EPMA structure) to make a Planning pie (application).  

Rolling Out the Crust: The Move to Planning

Empty pie tins are significantly inferior to full pie tins. This is a universally accepted truth.

When an application is deployed, EPMA ships the application’s hierarchical structure over to Planning. Planning is the shell of the planning application and acts as the pie crust – everything else will be built on top of it. Naturally, the pie crust will conform to the shape of the pie tin and the structure of the planning app will reflect the hierarchical structures created in EPMA.

With our move to Planning, our pie tin is no longer empty.

Filling the Pie: Loading to Essbase

In addition to pushing to Planning, EPMA also pushes the structures to the Planning application database(s) in Essbase. Essbase is a multi-relational database that houses the data which we’ll use to plan with. When it comes to the next step, Essbase allows us to load our filling or our data into our app.

Now, if it was a real pie, there'd really only be one way to put the filling in: by plopping it in.

However, in Planning, we can do one better.  We’ll discuss two options to get data into Planning.

The first method is familiar. Directly inputting data (either via user input in a web form or through an direct load) plops the data directly into the application. However, if we want to modify our data before we load it in, we can use a second, common method that leverages FDMEE. Financial Data Management (Enterprise Edition), or FDMEE, is a Hyperion tool that lets us use mapping and scripts to get our data exactly where we want it before we wrap it in our Planning application and toss it in the oven.

Baking the Pie: Processing Raw Data

When a pie goes into an oven, all sorts of processes happen, such as convection currents and caramelization, to take our filling from raw to delicious.

A Planning app has similar processes to take the raw data we’ve loaded and transform it into something meaningful. These processes are called business rules and calculation scripts. Both of these processes take raw data (drivers and input) and calculate results.

Time to Eat: Presentation

It's not enough to make a delicious pie. We eat with our eyes and when our users go take a slice of data, we want it to be eye catching and accessible. With an EPMA Planning app, there are three major points of user access:

Forms, housed in the Planning app, is a pre-sliced pie. Forms are structured web or excel based templates which allow the user to view and input data. The planner sets the data view of the form in order to create a specific functionality. 

Smartview lets you cut the pie any way they want. You can perform ad hoc analysis or open Planning forms in excel where you connect to the Planning app. You can set your preferred view of the data, and submit data back to Essbase.

Reports, featuring graphs and charts, are generated in a separate Hyperion tool called Financial Reporting Studio (FRS). These standardized reports are like little display pies. Pretty to look at but no touching allowed.

There you have it! A newly made Planning app ready to be put to work!

I’ve heard all this chatter about FCM but what is Financial Close Manager (FCM) and what does it do?

Let’s start with the basics; what the heck is a financial close!?  Loosely defined, it’s the process of closing your books at the end of each reporting period (Month/Qtr/Year).  This may entail a series of pre-defined processes such as reviewing the trial balance, reconciling Intercompany items, sending countless emails to everyone (oh the emails), etc, etc.  FCM is a great tool to help to manage all of these processes and provide visibility into bottlenecks.

Don’t just take our word for it.  We’ll quote Oracle as if it were Webster’s dictionary: FCM is defined as a tool that helps you outline, execute, and report on the interdependent activities of a period.  It’s a centralized monitoring tool that helps you streamline your close and provide visibility when something has gone wrong.  Rather than emails being in one person’s inbox, FCM stores all of the close items so it’s easy to understand which part of the close process is taking longer than it should, parts that miss the most cut-off days/times, and who’s not doing their job.

It’s the ultimate finger-pointing tool!  (Just kidding, but it does help see where issues are)

That’s great, I can read the admin guide too but how does it work?

FCM is customized to a company’s close process, so no two FCM setups are identical in nature.  While there is flexibility, FCM setup should mirror your other applications (HFM or other) in regards to Years and Periods.  It also takes into consideration company holidays and can use business day layouts as needed.  This allows users to create close templates that are valid across applications involved in the close (HFM, Planning, etc).  Each template is made up of a series of tasks that need to be completed as part of the close being performed.

An unlimited number of close templates allow you to create one for monthly reporting, statutory requirements, and shareholder filings; the options are endless.  Just think, you could even create a close template for all of the office parties since you’ll have so much more time on your hands.

In regards to an FCM implementation, let’s think of the close process as a series of tasks that need to be completed in order to file and report on monthly financials.  Some tasks rely on others to be completed (Predecessor tasks as FCM calls them) while others can be completed days before they need to be completed (Start Early tasks).  Tasks should be created in a hierarchical fashion just as you would create dimensions within an HFM application and/or a Planning cube. 

Think of tasks as your metadata and appropriately group them, assign them and schedule them.  Daily tasks should be broken out individually by assignee and can also have a backup (the next person to point a finger at if incomplete).  See the below screenshot of a close template that can be loaded into FCM.  The user Jdough will be notified on day 2 that they need to run the AR/AP Intercompany (IC) report.  They have until day 4 at noon to complete this or it’s late. This task type may also require them to submit documents and/or any out-of-balance that exists after review. 

Once you’ve defined all of your tasks, you now need task types.  Task Types, what are those!?!?! Task Types are easy ways to group all of the financial close tasks within your new build.  FCM comes with two out-of-the-box (OOB) task types “Basic” and “Parent,” neither of which is pre-defined for parameters, instructions, questions, etc.  However, when creating parent members the parent task type needs to be used.

Basic task types can be customized as needed.  For instance, a basic task type can be created to “request a document.”  Close tasks that require an end user to “request a document”as part of an overall deliverable would be labeled with this specific task type.  Task Types can also have specific parameters, embedded instructions and forms that end users need to fill out.  Since these are common and reoccur over and over, it’s best practice to create them once and assign as needed. 

Now that you’re an expert and you’ve removed your training wheels, you can now start setting up Integration task types. Integration tasks allow you to do things like:

Consolidate data – (Automatically – similar to task flows if you use them)

Bring you to the journals module

Bring you to a specific Data Grid/Form

Extract data to a file (Automatically)

These are built into the tool, so don’t panic! You’ll notice in the screenshot below an example of some of the integrations with HFM.  FCM has the ability to integrate with other Hyperion products but you’ll need to review the admin guide to determine if your product is compatible.

Those integration types that are “Automated” execution types can be setup to run month over month to complete tasks at given times without user input.  A common automated task is to run a consolidation in HFM.  FCM can be setup to consolidate data on the hour, every hour for the duration of your close.  Once complete (if setup) a list of users can receive an email that the consolidation is complete.

The “End User” execution types are a short cut to point users to specific application tasks like linkages to the journals module, data grids, data forms, etc in HFM workspace.  Just another opportunity the tool offers to minimize close time and enjoy some much deserved relaxing.

Automatic emails may be one of the biggest perks of FCM throughout the close process.  As mentioned earlier, the tasks should be set up hierarchically in the order in which they should be completed by day and time.  This includes a Start and End date and time.  It’s important to set this up properly as once the monthly close template is scheduled, each user will get a flood of emails saying the close period has begun.  They’ll also receive various emails for each of the tasks they are assigned to complete and/or the tasks they own (for managers).  

Users will get emails as frequently as every two hours (determined by the users) reminding them that their task needs to be completed (careful this can be set to never!).  

Oracle has gone so far that FCM even adjusts itself by time zone so emails are sent appropriately to each end user by location of origin! With such frequencies and precision of emails to users and managers, if a deadline is missed, well then there’s just no excuse. 

Even better, a user can see all close tasks in a Dashboard, Calendar and/or Gantt chart view - for those of us that like picture books to tell us the whole story.

Notice the Task Status, which shows the manager what needs attention, is pending, etc.

Now that you know what FCM actually is, let’s recap the real value.

If you’ve ever been part of a close process, you know there is a lot to manage and remember.  Let’s summarize the benefits of FCM:

Give people involved in the close a task list to easily manage their portions of the close

Run repeated automated processes during the close

Notify those involved in the close as to what needs to be done and when things get completed

Gain insight into which portions of the close take longer than expected and potentially address them for next close cycle

The flexibility to adjust your close template (tasks) month over month as you find and resolve issues in your process.  You can:

Scrap entire templates with no repercussions (unlike an HFM application)

Delete 1000 entries that you determine unnecessary with ease

Add 1000 entries that you missed on the first template

Change Owners and assignees across multiple tasks at once

As a manager, you can easily identify bottlenecks throughout the close through daily monitoring of your Dashboard.

A record of each financial close that’s stored directly in FCM.

Remember tasks are setup based on YOUR company needs.  You get what you put in and the more time you take thinking through the close process in regards to FCM, the better results you’ll get.

It’s 4:00 PM on a particularly stressful Friday, and as a new financial controller at Vantnies Corp., your budget is due by the end of the day. While rushing to get everything tied out in your Hyperion Planning application, you realize that a few of the corporate expense accounts weren’t adjusted properly and now your allocated expenses are incorrect. At first your heart might race as you think to yourself “This is a manual process! It will take hours to re-allocate!” Then you remember – you spent some time with your Hyperion administrator to come up with a process to take care of all of this in a matter of minutes. PHEW!

Let’s flash back to a few weeks ago when you sat down with the administrator, who explained all about how Hyperion Planning has the ability to perform calculations that can be run as needed in your Hyperion Planning budgeting process. These calculations are coded by a Hyperion developer into a tool that you can use called Business Rules (BRs). Don’t worry though! No coding here – we’re trying to provide you with information, not put you to sleep.

Process Drives the Rule

Business rules can perform many types of calculations, from simple aggregations to very complex allocations. One very important yet commonly overlooked part of developing a BR has very little to do with actual developmental coding at all. Taking the time upfront to properly design and build a business process with your admin can make a great difference in the efficiency of the rule and the speed with which it will be ready for use.

Let’s be honest here – as Hyperion developers in the real world, we can say that it is rare to deliver a 100% perfectly working business rule on the very first try. While this may be true, there are also many pitfalls in building a BR that can be avoided through simple planning. This can be said for both sides of the same coin – the benefits extend to both the controller/planner and developer.

Many times, a developer will receive business rule requirements and start working right away based only off of those basic requirements. This is starting without really getting to what you, the controller, wants as a result of your request. There are many reasons why this is not the best practice when developing a BR. Maybe it was a long day and you forgot a couple steps or exceptions in your initial requirements. Or possibly you are only thinking to automate one piece of your business process, and therefore only ask the developer to build the piece instead of the larger, overall business process. These are some simple reasons that BRs are not delivered on time or end up very different to what you originally envisioned.

Designing a process with your developer can help them to produce accurate and effective BRs delivered in a timely manner. It will also help you to understand how the rule works and know exactly what to expect when it is finished. The more complex the business process, the more important a BR design will become, so working with a developer to design it can be very beneficial. Finally, no more confusion about what those developers are actually doing!

Let’s Allocate!

Back to our example, allocating corporate expenses is a moderately complex business process that can be turned into a business rule. The basic end-goal of this rule is to allocate corporate expenses to other business units (BU) that will ultimately be paying for those expenses. This type of BR can be mostly straightforward or very complex depending on how many exceptions and special cases are needed in the business process.

Most corporate allocations will take the amount in certain corporate expense accounts, divide up the amount by a metric such as percent of net sales, and move the result to several BUs based on the portion of the metric attributed to each BU. Some accounts may be treated differently than others and be allocated based on different metrics, or (if you’re lucky) everything will be divided by the same metric. Even though these allocations typically deal with many expense accounts, to start with let’s just take one account: “Rent”.

The initial requirements that you give to the developer in an email are that a process is needed to allocate “Rent” from Corporate to six BUs based on their percent of net sales. The allocated amounts should be sent into an “Allocation In” account that already exists in the Account hierarchy, for each BU. With this information, the developer has a general idea of what will need to be performed in a seemingly simple business rule. In the interest of delivering an accurate and effective business rule, you request to go through the full business process with the developer. (Or if you are the developer and the controller doesn’t make the request, you should! This will help you out too!)

Breaking Things Down

One of our favorite methods of designing a process with a stakeholder is to put it in a common language – an Excel model. Breaking down the process using Hyperion SmartView for Excel is a very beneficial tool for both the developer and the controller. SmartView is a powerful add-in for Excel that can be used to connect to Hyperion products and bring data into Excel.

Using this method is useful because it breaks the process down into simple steps for the developer, shows the controller how the desired result will be achieved, and for both confirms the expectations for the entire process. Now, let’s bring this corporate allocation process into Excel, using a new tab on the bottom of the screen for each step.

Step 1 – Input Data

The first step is to retrieve the value of “Rent” from Planning – this will go on the first Excel tab, which will contain the input data to be used in the calculation. Be sure to retrieve all of the accounts, BUs, etc. you will be calculating with. Our example only has one account, but this business process will typically have many.

Step 2 – Metric Data

The second step is to retrieve the metric(s). The metric can either be a static value that is another source (feed from GL, calculated in another business rule, or manually input into Hyperion), or it can be calculated within your rule. If it will need to be calculated, retrieve the data that will be used to derive the metric and create another column to manually perform the calculation in an Excel formula. If the metric is by BU, be sure to pull the metric for each BU.

In our example, the company has a separate business rule to calculate the percent of net sales for each BU because this metric is used so often for many calculations. Therefore we do not have to calculate it within this BR and only need to retrieve the value in SmartView.

Step 3 – Result Data

The third tab will be where the result of the calculation will be stored in Hyperion. In SmartView, set up a query to retrieve where the result of the process should be stored, for each BU. This tab will have no data for now, but once the rule is built and executed this is where we will want the result to populate.

While working through this step with the developer you remember that in addition to the allocation calculation to the other BUs, Corporate must have the expense “zeroed out” since they will no longer pay for that expense. This means that for Corporate, “Rent” must have a counterpart account where the opposite value of “Rent” is held in order to negate (or “zero out”). We will call this an “Allocation Out” account. In this account, we will take the original expense amount and multiply it by -1 only for Corporate.

Go through this process with the developer, and ensure everyone understands it and what to expect out of the business rule once it is complete. Be sure to think of any exceptions to the process, or any other special requirements that might be needed to perform the calculation properly. Make a note of these and bring them into your Excel model as we did in our example.

Based on the Excel model, you and the developer should now have an understanding of the data that you need as input into the calculation, the basics of the formula for the calculation, and the intended result.

Step 4 – Check Validation

Once this is done, the developer can start writing the actual code for the business rule, using the data from the first three tabs of your Excel model. However, we aren’t done with our Excel model yet! When the first draft of the business rule is ready, return to the Excel model for validation.

With any business rule, always be sure to test and validate the results appropriately. Here, this should be done by executing the rule, refreshing the data in the Result Data tab, and then performing a variance check in a fourth Excel tab.

The fourth and last step is for validation, to check the Hyperion result vs. the expected result. This tab will consist of three parts: one part will be the Hyperion results copied from tab #3, the second part will be the calculation performed through Excel formulas, and the last part will be a variance check between the Hyperion and Excel parts.

This step will only make true sense during testing/execution of the rule – you need data in the Result Data tab to compare against. Once there is data in the Result Data tab, move over to the Check tab. Copy the bottom level BU data from the Result Data tab into the Hyperion results section (first part) of the Check.

In the second part of the check, use the inputs from the first two steps (Input and Metric tabs) and put them into an Excel formula to generate the expected result.

In the Variance part of the Check tab, simply line up the Hyperion amount and Excel formula amounts and subtract one from another. If there is a variance shown, you know you and the developer need to go back and assess the BR or the input/metric data in Hyperion for mistakes.

Once you get everything fixed up, you should have no variances. Now with a set of correct and validated data, you can be reassured that the business rule performs as intended. Fast-forward to that very same Friday and see yourself submitting the budget on time!

Oracle EPM systems can be a bit like alphabet soup with endless acronyms and terms – we already used one in this very sentence! A lot of these terms get thrown around during a Hyperion Project and it can get quite confusing, particularly if you’re not familiar with the industry.  This post will shed a bit of light on what some of these terms are and give a real-world example of what each can mean to a finance professional or a systems administrator. 

 THE BASICS

These are a few of the typical terms you’ll hear when your company is embarking on a new financial systems or EPM project.  

• BI - Business Intelligence

The transformation of any business data into meaningful, accessible intelligence for decision-making purposes - applicable from the operational to the strategic level.

Taking SKU level sales data and producing a graphical trend analysis that can assist in the understanding sales seasonality.

• EPM – Enterprise Performance Management

Enterprise Performance Management involves taking a data driven and systematic approach to measuring a company’s financial performance – it can cover planning and forecasting, consolidations, external reporting, financial reporting, and business analytics

Implementing a software system, such as Hyperion, to gather raw financial data and use it to improve the financial close, planning, and/or budgeting process.

• Consolidation

In Corporate Finance terms, consolidation is the process by which a company gathers all of the financial data from its various subsidiaries/legal entities and performs any necessary conversions or adjustments for internal/external reporting.  

Your company is a holding company that is comprised of four subsidiaries.  At the end of each month, the financial data from these subsidiaries are collected, converted to US Dollars (where needed) and presented in a single P&L and Balance Sheet.  

• Planning, Budgeting, and Forecasting

Financial budgeting can go by a number of different names – Budgeting, Forecasting, Planning, and Strategic Planning are a few.   Regardless of what it is called in your organization, the common thread is that the process involves forecasting future financials.  Typically these forecasts are created from a mix of historical data, business drivers, and general knowledge of the business landscape.

Your company takes its previous year financial results and uses predicted growth rates to forecast its sales and costs for the next year. 

ORACLE HYPERION SYSTEMS TERMS

Now we’ll go a level deeper with terminology.  Once your company has started down the path of launching an EPM project, there will be a number of technologies and software systems available to you to facilitate the process – one of which is industry-leading Oracle Hyperion.  The below are terms you’ll hear related to the Oracle Hyperion EPM platform. 

• HP - Hyperion Planning

This tool allows companies to create applications that help them plan or forecast their financial information. It provides a flexible platform that can be customized according to the business’ needs.    

Your company uses a Hyperion Planning application to forecast employee salary and benefit costs based on drivers such as headcount and industry average salaries.  

Hyperion Planning has an underlying “engine” called Essbase, which is outlined below.

• PBCS – Planning and Budgeting Cloud Service

This is a cloud-based version of Hyperion Planning. No installation or infrastructure is required to implement this tool.      

To save on infrastructure costs, your company implemented PBCS as its tool to forecast its top line sales by product category using prior year results and various growth driver metrics.  

• Essbase

Essbase is a multi-dimensional database management system that can be used for various business calculations and analytics.  This analogy isn’t doing the tool justice, but you can create Essbase applications that mirror advanced Excel models and pivot tables.  Essbase applications can be made up of “cubes” (or databases).

Essbase can be used to directly develop applications or can be used as an engine for Hyperion Planning.

A company may use Essbase to calculate and analyze product level gross margin reporting.  In addition to sales and costs, an application might include information such as customer and product level detail.  Using Essbase, you would be able to analyze and organize data in many different ways, including by top customers and best selling products.  

 BSO – Block Storage Option

One of the two types of Essbase cube technologies.  Without getting into the technical aspects of a BSO application, an application that is very calculation intensive typically requires BSO.

Your company might use a BSO Essbase application as a calculation engine to determine currency fluctuations. 

ASO – Aggregate Storage Option

The second type of Essbase cube technologies, an ASO application would typically be used a “reporting” application that contains very large amounts of detailed data.

Your company might use an ASO Essbase cube for reporting daily data across thousands of SKUs

• HFM – Hyperion Financial Management

HFM can be used to create applications to facilitate a company’s financial consolidation for reporting monthly consolidated financial results.  This tool has features such as a built in Journal Entry module, automatic currency translation, and elimination of intercompany balances to help streamline the consolidation process.  It is also possible to build custom business rules to handle things such as minority interest, allocations, or cash flow calculation.

Your company is a distributor of office supplies with sales divisions across the globe.  Your company uses HFM to collect data from all of its sales divisions, convert the data to U.S. Dollars, and book journal entries to produce U.S. GAAP financial statements on a monthly basis.

• FCM – Financial Close Manager

FCM allows users to track monthly financial close activities in a strict order and has the ability for users to sign off on task completion, add comments, attach files, etc. FCM helps with task management and account reconciliation.  In other words, it is a centralized location to be able to point a finger at the user or set of users who didn’t complete their tasks on time!

Your company uses FCM to schedule and track the tasks that need to be completed in its General Ledger and in HFM during its monthly financial close cycle instead of the monotonous email chains of who did what and when.  

• EPMA – Enterprise Performance Management Architect

EPMA is a tool to that helps manage Hyperion application structures and related settings. It is a central place where structures can be managed and even shared across HFM, HP, and Essbase applications noted above.  

A company uses EPMA to build out its corporate chart of accounts and legal entity structure.  It then shares these across its HFM and Planning applications to reduce hierarchy maintenance.  

• DRM – Data Relationship Management

DRM is a separate tool used for managing application structures. DRM is conceptually similar to EPMA, but has additional features that can be used for advanced processes/automation.  One distinction to be made from EPMA is that DRM is not limited to managing structures for Hyperion applications.

Your company uses DRM to centrally manage its corporate chart of accounts, which is used in its General Ledger system, as well as its HP and HFM applications.  

• FRS – Financial Reporting Studio

FRS is a tool for building and presenting financial reports. This tool can be configured to connect to the above-mentioned Hyperion products (HFM, HP, & Essbase).

Your system administrator uses FRS to design and control the company’s standard financial reports such as its P&L or Balance Sheet. You access these reports on the web via Workspace (defined below) or can email them out to the relevant audience.  

• FDM – Financial Data Management

This application can be used to take data from a source, such as a text file, database, or General Ledger system, perform any needed transformations or mappings, and load it into Hyperion applications.  

Your company uses FDM to take data from a small subsidiary that uses a different General Ledger and chart of accounts, map it into the corporate chart of accounts, and load it to HFM for consolidated reporting.  

FDM/ FDMEE are considered ETL (extract, transform, load) tools – tools that can get data from a source, manipulate it as needed, and load to target.

• FDMEE – Financial Data Management Enterprise Edition

FDMEE is a newer version of FDM with functionality similar to FDM but has an updated user interface and additional features that can be leveraged.

You use an FDMEE-only feature called “Multi-Dimensional Mapping” to map data from a subsidiary company to the corporate chart of accounts based on both the source account and source cost center.  

FDM/ FDMEE are considered ETL (extract, transform, load) tools – tools that can get data from a source, manipulate it as needed, and load to target.

• ODI – Oracle Data Integrator

ODI is another ETL tool that can be used to pull data from various sources, transform it as needed, and load to various target systems.  ODI is similar in concept to FDMEE, but they both have differing feature sets.  ODI can also connect to a number of systems outside of the Hyperion landscape and is generally used for heavily automated processes.

ODI is the tool used by your company’s IT team to pull a currency rates data file from an FTP site on an hourly basis and transform for it loading into Hyperion Planning.  

ODI is the engine that FDMEE runs on.  ODI can also be used as a standalone tool as noted above.

• Workspace

Workspace is the web-based landing pad, which end-users leverage to interact with the various Hyperion applications they are provisioned for, including Planning and HFM.  In later releases, FDMEE can also be accessed through workspace for data integration management.  

You use Workspace to access your company’s HFM application and fill out roll forward schedules used in its Cash Flow calculations. 

OTHER TERMS YOU MAY HEAR

• Metadata

In reference to systems, metadata is data that provides information about other data.  

In your company’s General Ledger is a record for a sale of $100.  The related metadata for this data point may include the account, legal entity that sold it, customer, product number, sale date, etc.  

• FDMEE - OIA – Open Interface Adapter

OIA is a module of FDMEE that allows you to connect directly to various source systems.

At your company the OIA feature to drill through from an EPM target application to view the data in the ERP source system.

• FDMEE - Direct Connection

In FDMEE, when an ERP or EPM source is directly connected to an EPM target.

A Direct Connection could be used to load actuals financial data to HFM from Oracle’s PeopleSoft without any intermediary steps like exporting the data to a text file.

• EA – Extended Analytics

EA is a feature of HFM that enables the export of data and metadata from HFM applications to a text file for use by other applications.  

You use EA to extract data from an HFM application and load it into a separate database for supplemental reporting.  

• EAL – Essbase Analytics Link

EAL provides an interface to synchronize/move data between HFM applications and Essbase applications.  

Your company uses EAL to take Journal Entry and Intercompany Elimination data from HFM application and push it to an Essbase application that contains the same base financial data. This allows both applications to reflect the same consolidated financial results.  

• EAS Console – Essbase Administration Service Console

Essbase Administration Services Console is where an administrator will manage and maintain Essbase applications and their associated properties. Typically, end-users will not access this tool.

Your system administrator uses the EAS Console to add a new account to the chart of accounts built out in your Essbase application.

• SmartView (SV)

A Microsoft add-on for the Office suite that allows a direct connection to Hyperion applications where the user can perform their necessary tasks.  Many end users will primarily use SmartView to interact with/analyze their financial data in the familiar setting of Excel.  

You use SmartView in Microsoft Excel to pull financial data from your HFM application into Excel and analyze your cash flow results.

• Calc Manager

This tool can be used as a graphical user interface to create and manage the business rules (written in VB Script) that underpin the logic of their Hyperion applications.  This is an alternative to the “Classic” approach, where VB Script rules are written in a separate rules file, then loaded to the application.  

Your system administrator uses Calc Manager to write business rules to automatically allocate product marketing costs among various legal entities.

While this certainly isn’t all the acronyms you’ll hear in the EPM world, it should be enough to get you started.  TY for reading, for now we GTG.  TTYL.

- The Platform Specialist Team

Clients frequently ask us what constitutes a "leading practice" financial budgeting and planning system.  While there is rarely a one size fits all answer, there are key areas of consideration that separate organizations with more mature systems from those just starting out.  We’ve listed our top 5 below.  Even if you haven’t fully invested into one or all of these areas, it’s worth taking stock of how you can start down the path to implement each to create a more effective budgeting system.

1. Data Consistency – In the context of systems, we can define data consistency as a company-wide alignment on the meaning, categorization, and usage of planning data.  In concept, this is an obvious necessity for a budgeting system.  Unfortunately, the reality is that data consistency is an all too common issue that leads to confusion and questions for some clients.  Data inconsistency can lead to people wasting their time asking questions such as:

Do all business units categorize T&E costs the same?

What constitutes a third party sale?

Are all planners using the same assumptions for cost of living salary increases?

An organization with a mature planning system will put in the time and effort needed to get everyone on the same page upfront regarding how their data is used.  Everyone has heard of the old saying "garbage in, garbage out”, but you also want to avoid “confusion in, confusion out”.  A good set of company-wide financial norms, common use of accounts, and a solid definition of metrics can go a long way.

2. Driver and Assumption Based - Over the years, "Driver-based" Budgeting has become an industry buzz word. However, at the core, it is essentially the process of building up a financial budget based on predefined input metrics (“drivers”) that are important to how your business is run.  For instance, a customer support-focused company may base their projections for support staff costs on a combination of the number of customers in a specific market and time needed to serve each customer.  

One of the chief benefits of utilizing a driver-based approach to planning is that the resulting financial forecasts have a clear and direct audit trail.  In the example above, if the total cost of customer support staff salaries went up (typically seen as jump in SG&A personnel costs), you would be able to trace the increase back to either an assumption of an increase in customers or an increase in time needed to serve customers.  Although driver-based budgeting isn’t always practical for planning every section of your financials, it should be a primary building block for estimating core business operations related accounts.

3. Actionability - Actionability is really the sister concept to Driver-Based Planning.  Put simply, actionability is having a systematic process to influence your financial projections.  Let’s continue using the same example as above - the customer support staff example.  If management wants to bring down the costs associated with customer support salaries on the P&L, they can pull a few levers - 1) try and speed up the time staff can resolve customer support calls or 2) reduce customer growth plans.  Option 1 is likely the preferred option because it doesn’t necessarily impede top line growth.  So the actionability in this example may be to launch an initiative to better train support staff to answer customers’ questions quicker.   In a financial planning systems sense, the initiative can be rooted in actionable business intelligence - e.g. a 20% reduction in time to serve customers will lead to a $1M a year savings in personnel costs.

4. Timeliness of Data - In concept, data timeliness is a simple topic, but it can be highly complex in practice.  Good financial planning systems push to have data updated according to the speed of how their business operates.  A holiday-centric business, for example, (A Halloween Costume Store) may need daily forecast updates during the holiday season.  On the other hand, a traditionally non-seasonal business may only need quarterly reforecasting that factors in updated Actual data.  There is no one size fits all for timely data, but the key is to make sure that your decision making isn’t hindered by out of date financial data.

Having timely, accurate data is essential to adequately interpret the business’s performance and instituting changes, where needed.

5. Target Setting – Building on the above, to bring a planning system full circle, it is critical to factor in some level of target setting functionality – “what are we looking to achieve?”.  Target setting can be as high level or granular as needed, but is necessary in order to measure results.  In effect, target setting is really the starting and ending point of the planning loop.  A common target setting example would be a goal for increased profitability (e.g. 2% growth in profitability next year).  The target helps to provide a context for actionability.  After a target is set, an action can be taken to influence drivers, and the results can be calculated and measured against the target.  If the target isn’t feasible, the drivers and assumptions audit will be an easy way to prove out the problem areas.

The top 5 above focus primarily on the functional financial tenets and qualities of a more advanced planning system. There are a host of necessities of a good planning system as well; including system stability, solid data audits, and a knowledgeable user community… but we’ll save those for a future blog post.

Let’s picture a situation in which you work for Hipster Shirts and Pants (HS&P), an apparel company with operations all across the globe.  One crisp Spring morning you receive news HS&P has just acquired a small subsidiary company in Southeast Asia that specializes in fedoras – Fedoras Unlimited.  Fedoras Unlimited has created hats for some of the trendiest celebrity names in the world.

[pictured: Fedora Unlimited “Classic” fedora]

[pictured: Fedora Unlimited “Supreme” fedora]

While great news for the company (given the fedora market is really taking off) you are now tasked with figuring out how to integrate Fedora Unlimited’s financial data into your company’s financial reporting without simply mashing the two together in an Excel spreadsheet.  After all, you have a great EPM system like Hyperion already being used in the company.  You decide to put your mean vlookup skills to the side and leverage that instead.

Your financial group uses Hyperion Planning as its forecasting tool and Hyperion Financial Management (HFM) to derive its consolidated reporting. While both good tools in their own right, both of these applications would be useless without data.  To load the ever-important financial data to these applications, you use Hyperion’s FDMEE tool, which allows for the import, transformation, and loading of data to the various Hyperion applications. 

For the purposes of this post, we will focus on the data integration aspect of this quandary – we can save the ownership rates, currency conversions, forecast processes, etc for discussions some other time.

In regards to integrating Fedoras Unlimited’s data into your system, the best general approach is to:

• Get familiar with your source data • Determine how the data will integrate with your current chart of accounts and company structure • Build the necessary mappings and data transformations within FDMEE

Know your data!

The first step in the process is to get familiar with the data that you will be feeding to the applications.  Is Fedoras Unlimited on the same GL system as the rest of the company? What is the format of the data? Do you have the necessary fields to populate the dimensionality and structure of your applications?

In this example, Fedoras Unlimited is not on the same GL system as the rest of the company, has a different chart of accounts, and will be loaded using a flat file – a text file extracted from their GL system containing all of the pertinent data. 

On the bright side, the company segments its data by legal entity and Cost Center, just like your parent company HS&P.  Consider the sample of the data from Fedoras Unlimited below:

Once we have a good grasp on our source, we must tell FDMEE how it can expect to receive the data from the flat file – what data is in what field.  This is accomplished using an Import Format. Luckily, our case is fairly straightforward, but users also have the ability to write complex import scripts to transform the data as its entering the application.

Let’s take a look at each of the fields in the source file:

1. Entity – Fedora Unlimited’s legal entities 2. Cost Center - segmentation of the data by departments, similar to HS&P.  Notice that there is no Cost Center for the accounts starting with a 3 – these are revenue accounts and do not have a Cost Center. 3. Natural Account – GL system account 4. Sub Account – GL system sub account grouping 5. Customer Name – this is good information to have, but will not be loaded to HFM 6. Customer ID  - again good data, but will not be loaded 7. Amount – Actual data to be loaded to HFM

After a review, we know our data inside and out at this point - we know that Fedoras Unlimited has fields in their GL extract file that links actual data to customers, holds customer ID numbers, and breaks accounts down further into sub accounts.  First, HS&P does not report or plan at this customer level, so the 5th and 6th fields have been left off of the import format (not sourced into Hyperion).  Second, to align the account mapping to our current COA, we will use an import script to concatenate the account and subaccount fields, as highlighted in the Import Format below.  An import script is a tool available in FDMEE to help transform the data into a workable format as it enters FDMEE, before any further data mapping occurs.  (another post to come diving in deeper on import scripts)

Follow the Map

So now we have selected the fields we need from the file and, for the purposes of this blog, have loaded those fields in to review..

After importing the data into FDMEE, we can see it in the Data Load Workbench reflecting the field mappings and scripts from the Import Format.  Notice how the account and subaccount fields are now combined.

Once the data is imported to FDMEE, we need to determine how to map that data to our Hyperion applications. In our case, Fedoras Unlimited is not on the same chart of accounts as the main company.  Instead of working their existing accounts into the current chart of accounts, we will map them into existing parent company accounts. Seen below, we do this using an Explicit map in FDMEE – a direct mapping of a source account to a destination account in HFM.

From an entity perspective, Fedoras Unlimited operates in 5 legal entities. However, given its size, we will map all of its data to one newly-created member within the parent company’s existing Southeast Asian division.   This can be done via a Like map using the * wildcard, as seen below.

For the Cost Center dimension, we will map the source data through to HFM as is, since Fedoras Unlimited happens to use the same cost centers as HS&P.

Lock and Load! 

Now that we have our mappings established, the Validate stage can run, where the mappings are executed. Now, looking back to the Data Load Workbench, we can see the data arranged in columns showing both the source, and the target.  This is an important and useful feature which allows a user to unwind how a given number got into the application.

Finally, the fun starts! The data is exported and loaded into our target Hyperion applications and we have 3 gold fish indicating our success.  You would have to play a lot of games at the carnival to win that many goldfish!

Dude, where is my data (coming from)?

Now that the data has been exported and loaded, we can see it in an ad hoc grid in SmartView (stay tuned for another post if you’re not familiar with SmartView). 

After careful examination of the data you have loaded and, being the astute analyst that you are, you notice an interesting trend.  The sales figures in Fedoras Unlimited’s “Sales Account A” have declined quite dramatically over the last 4 months.    

Doing some investigating, you would like to see what comprises the $808,899.56 sitting in account 300001 so you can talk to Fedora’s controller. Using the FDMEE drillback from excel feature, we can quickly go from an ad hoc grid or query back to FDMEE to see the account and legal entity that went into that value.

Using this drillback feature, you are able to see that source account 30001002, which is attributed to online sales, is unusually low this month at just $33,345.12.

Now you have all the pertinent information you need to go back to Mr. John Controller.  At this point you have done as you were tasked and loaded and consolidated Fedora’s data. Unfortunately, the shine has come off your new acquisition as their financials are not as good as you thought.  It seems as though fedora market must have crashed hard…. but hey, at least you saw it pretty quickly.

Your company HS&P has learned a valuable lesson with FDMEE - Fedoras don’t Make Everybody Equity.

 - The Platform Specialists Team

In the spirit of keeping it light, we’re going to attempt to post a meme-a-week related to the Oracle Hyperion space.  Follow this post for weekly updates.

Aug 3, 2015

Jul 17, 2015

July 8, 2015

December 16th, 2015

This post goes out to all the Finance people that chose a life without audit and CPA requirements.  For all of you who had to sit in meeting after meeting listening to people gripe about External Reporting and US GAAP and IFRS and Actuals, Actuals, Actuals! 

For every decimal out of place, for every foreign exchange rate difference, for every historical override, and especially for every Intercompany Elimination.  The list of insider terms and jargon goes on and on…

Well,we’re here to add a little clarity and understanding to the financial consolidation process.  First off, what is a consolidation?  Essentially, in corporate finance terms, consolidation is the process by which a company combines the financial data from various business units, legal entities, etc. into a broader parent company for reporting purposes.  

With that said, some may think that consolidation is as simple as adding the sum of all the parts to get to a total, especially for External Reporting.  While this is obviously the crux of the process, there is a bit more going on underneath the hood.  

A great way to understand a financial consolidation is to break it down into 3 core processes below:

Aggregation

Sum up the data by financial reporting structures (e.g. Departments rolling up to Segment, business or legal Entities rolling up by Region, Accounts rolling up by major Financial Reporting line items, etc.)

Translation

For multi currency applications, the process of translating from local currency into any other currency, as defined by a company’s Entity structure / dimension (e.g. Needing to report a EUR entity in USD)

Elimination

Transactions between two Entities within an organization must be netted to zero, since a company cannot generate net activity from itself. (e.g. Intercompany Receivables should offset Intercompany Payables)

To accomplish the above, HFM, Oracle’s consolidation system, utilizes the Value dimension.  Some say this is where all the magic happens.

If you’ve ever ventured into HFM or are familiar with its dimensionality, you may have come across the Value dimension. For those without a deep background in HFM, this dimension can be a bit of a black box.  We’re here to help with that.

At first glance it appears flat and uninteresting.  But as you peel away the layers, you can start to understand the transformation that this dimension allows your data to have during the consolidation process.  Let’s go through that step by step.

Entity Currency

Let’s start with Entity Currency.  This is where an entity’s local currency data gets stored and it works in conjunction with the currency attribute assigned to that entity.   For example, if a USD based company has sales offices in London and Tokyo, their Entity Currency data will be in GBP (British Pound) and JPY (Yen), respectively.

Entity Currency Adjustments

After Entity Currency comes Entity CurrAdjs, which represents journal adjustments, performed in local currency through the Journal module of HFM. Going back to our London sales office example, let’s say they closed the books for the month on workday 4.  After careful analysis on workday 7, they find that their sales accrual was short by 100K GBP.  

If timing doesn’t allow for the rebooking of the transaction to the GL, they will book the transaction topside in HFM.  This 100K GBP effect is stored in Entity CurrAdjs to allow an audit trail showing what was loaded from the GL (Entity Currency), and what was journaled directly in the system (Entity CurrAdjs).  

Entity Currency Total

This is nothing more than Entity Currency + Entity Curr Adjs.  It is the total local currency inclusive of adjustments and loaded/submitted data.

Parent Currency

Next up the chain is Parent Currency.  This member takes total local currency (Entity Curr Total)and translates it to the currency of its immediate parent in the entity hierarchy.  

Back to our good old friends across the pond in the London sales office, since they roll up to a Euro parent company, the system will translate from GBP to EUR when the consolidation goes from Entity Curr Total to Parent Currency.

The above shows a translation from GBP to EUR using a currency rate of €1.25 to £1.00

Parent Currency Adjustments

Up from Parent Currency is Parent Curr Adjs.  

Parent CurrAdjs is an intersection that allows users the ability to book an adjusting journal in the currency of the entity’s parent. It is NOT the Entity CurrAdjs translated.  Recall that Entity Currency and Entity CurrAdjs get added together at Entity Curr Total, which then translates to Parent Currency.

Let’s assume that the London sales office had to book a 150K EUR adjustment.  Instead of figuring out what that amount is in GBP and booking in Entity CurrAdjs, the sales office has the option of booking the 150K EUR straight to Parent Curr Adjs.  

Parent Currency Total

As before, the totals for Parent Currency and Parent CurrAdjs are added together to get to Parent Curr Total, which is the total post-translated amount inclusive of any parent currency adjustments. 

For the purpose of simplification, we will jump ahead to the Elimination Value dimension member.  Though there are other Value dimension members between Parent Curr Total and Elimination, these are not typically used other than for percent ownership, equity pickup, and blah blah, accounting speak, blah debit, blah, credit.  We’ll save that for another time.

Elimination

Elimination.  The one word that can make anyone cringe when it comes to data tie out or passing of data between systems.  Eliminations are necessary when two entities that belong to the same organization have activity with one another. Though it is important to be able to see that activity at the entity level, it has to be zeroed out at the appropriate consolidation point.  After all, a company can’t make money off of itself (we’re not running a Ponzi scheme here).  

Why do we need to load Elimination data if it just nets to zero?  Valid question - if it doesn’t net to zero, or if it zeroes out across levels, it is important to have the detail to support all of the activity.  Enter the Elimination dimension.

To keep it simple, let’s say that Company A has a receivable from Company B.  Company B should have the offsetting Payable on their books.  

That intercompany transactions between Company A and Company B are not eliminated (zeroed above) until the first common parent of each company is met.  To keep things simple, let’s just say that both companies roll up to parent Company AB.  This is where it will eliminate.

Contribution

Contribution represents what ultimately gets passed from child to parent.  Keeping it simple, it is equal to Parent Curr Total + Elimination.  This total then gets sent up the chain to its parent’s Entity Currency member.Seen below, Entity Currency for the parent entity (Total Europe) is the sum of the Contribution amounts from each of its children (London & Zurich). 

Now, putting it all together, hopefully you should be able to make some sense of Oracle’s diagram of the Value dimension, seen below.

And then the process starts all over again…

…with Total Europe at Entity Currency and continues upwards through the Value dimension and, ultimately, gets to total company consolidated financial statements.

Should we ask our salespeople to sell more of Product X or Product Y? 

Based on margin, should we push more of Service A or Service B?

Are our West Coast service lines more profitable than the service lines we offer on the East Coast?

 These seem like simple questions, but getting to a fairly accurate answer isn't always easy.  Most companies with an interest in measuring financial performance at this level turn to an EPM (Enterprise Performance Management) tool like Oracle Hyperion Planning to help derive answers.  Product profitability is important for almost all businesses to understand, and having been through various client EPM initiatives with this goal in mind, we tend to see three core considerations at play:

Grouping and categorizing what a company is selling (products or services) in various "business views" is important to help improve insight

Historical product sales and cost trends can provide a solid basis for forecasting future period gross margin data

Expense data (particularly SG&A) is rarely directly attributable to a product or service level of detail; educated decisions need to be made on how to fairly allocate this data to derive full product P&Ls

Now let’s dive a bit deeper into each consideration area...

Categorize and group products in innovative ways for better analysis

At the core of most EPM systems (like Oracle Hyperion) is the concept of dimensionality.  A dimension is basically a hierarchy that allows you to categorize and summarize information. The way a Product dimension is structured in a Hyperion forecasting application really drives the level of decision making power it can yield.  Most companies start with their “standard” view of product categorization.  For companies that sell physical products, it’s typically a product family hierarchy. For companies that sell service type offerings, individual services are usually grouped by service lines.

(In our simple example below, we show individual Products and Services, but it's certainly possible to analyze at more granular levels, such as Product SKUs.)

This is a great start, but we often push our clients to think about alternate ways to look at their products.  Do you have products that are primarily targeted to specific demographics or specific geographic regions?  Are your products generally sold in varying retail outlets (brick and mortar, web based, etc.)?  Adding additional views of product categorizations can provide some valuable insight when you’re able to see the profitability of each on paper.  In Hyperion Planning we can handle these alternate views with alternative hierarchies or product attributes.  We're analyzing the same Products, just in new ways.  This may ultimately help to show us that we should push more products to our E-Commerce channels if they are most profitable, for instance.

Product level Gross Margin data - historical data is a great starting point

Once products are grouped in a way that helps set the stage for value added analysis, we need to actually plan and track financials for them.  When talking about financial statements, there's no better place to start than the one everyone loves best - the P&L. (Sorry balance sheet, you were a close second)

  In a really basic P&L structure, we have a Net Sales account section that is generally directly tracked or associated with Product or Service information.  Sales and COGS accounts, for instance, are likely tracked by SKU or some sort of Product or Service Tag in a GL system.  In Hyperion Planning, we can use this historical information, along with some assumptions on how price, units, or COGS are expected to change in the future to derive future Gross Margin and Gross Profit product data.  This process for forecasting future gross margin based on historical data can range from simple to ridiculously complex.  For our example though, we’re going to keep it simple.  We’re applying a Unit Growth Rate and an Average Product Selling Price to calculate Gross Sales.  Then we're using Material Cost as a Percent of Sales to derive COGS.  

 Allocating Expense Data to get a full P&L

Nice!  So far we grouped our products in various ways to help drive insight, we planned next year’s gross margin data based on our supremely accurate and never restated historical data, and now we’re almost to the illusive Net Profit by Product. 

In order to get down to Net Profit by Product, we’ll need to work on forecasting our P&L expense section.  Expenses - what do we know about them?  Management always wants us to cut them, that’s for sure.  Aside from that basic truth, we also know that expenses aren't typically tracked by product.  For example, we rarely associate a Corporate Accounting Manager’s salary with Product A or Product C.  This is where systematic allocations come into play.  Usually after a series of design sessions and requirements meetings, those familiar with operations can help to provide a fair allocation methodology to round out our full Product P&Ls.  A few example approaches for expense to product allocation logic are pictured below.

(Our "No Product" column above represents a pre-allocated expense.  We then take that expense and allocate it to Products based on varying methodologies.)

And now, with our product structures setup, gross margin data forecasted, and future expenses allocated, we can reap the fruits of our labor.  Some really sweet reports!  

Based on how we set up our product hierarchies, we are able to run a P&L at the product level, as well report profitability for our alternate product groupings, as shown below. 

There are countless other considerations at play when understanding product profitability (including the best way to forecast expenses before they even get allocated to a product), but we hope this post gave everyone a little foundational insight on how to understand key considerations in the mix.  We hope to see you soon...

Having worked in the Oracle Hyperion and Enterprise Performance Management space for most of our professional lives, one thing has become abundantly clear to our team - the work is complex in nature.  It has to be; we are dealing with forecasting real world financials, complex company consolidations, and countless systems and business processes.

Fortunately, developing a strong background in simple, foundational concepts pays dividends as these complexities come up.  The goal of our blog is to focus on this foundation - we'll be discussing topics and examples that are both simple and applicable in the real world.  The topics will combine a functional and technical view of the discussion at hand.   Our hope is that even those unfamiliar with the specific topic can gain something by reading.

Check back for upcoming posts with topics that include Forecasting Product Profitability, using Hyperion Planning and The Real Value of Consolidations, using Oracle HFM.