- March 10, 2016
- Posted by: platformspecialists
- Category: Budgeting & Planning, Hyperion EPM, Hyperion Planning
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!
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!