By TopDown Team
December 7th, 2016
I’ve been writing and speaking about allocations inside of Hyperion Planning and Essbase for several years. Inevitably on a project, I’m asked to write an allocation script that requires a planner to enter a percentage that must total up to be 100%. The scripting is easy, but what happens when you enter a value that doesn’t equal 100% and you either under of over allocate your expenses.
Traditionally I’ve used a webforms and data validation to try to enforce compliance, but I kept thinking to myself, there must be another way to get to the same result. There is, and I’m here to share that with you.
Let’s look at an example. If I’m subdividing out expenses to various cost center or Entities by a percentage. Let’s say IT services gets moved down to a profit center level. 15% goes to Retail, 50% goes to Wholesale and another 30 goes to Direct. Is that 100%, no it’s 95%. So, what happened to that extra 5%?
My default response was to set up a Webform with a rollup and a validation that displayed visually if you reached the desired number of 100%. If you didn’t, the cell would show up red or yellow.
This works great for a small dataset, but when you have many intersections, visually it becomes difficult to scroll down to see the validation Total.
Instead of assuming that the red color cell would alert everyone for the need to correct the totaling, why not force a calculation to true up the percentages to always equal 100%? Pure simplistic genius.
In this example, I have three cost pools that need to receive their share of Admin and Marketing Expenses. This is only step one in the process. I’m first dividing up my high-level Admin and Marketing expense into three high-level cost centers; Retail, Wholesale and Direct. I’ll further subdivide the expenses by net sales later, but since a total percentage of net sales may give me a misleading figure in the allocation, we wanted the flexibility to assign a percentage to each high-level cost center first. For example, wholesale may have a three times the dollar amount of sales as compared to direct, but utilize far less admin and marketing dollar to make those sales happen.
Let’s leverage my form and expand on it. I added and additional member in the account dimension called AdminMarketingAllocation_TrueUp that is dynamically calculated off it’s stored sibling.
With a simple member formula, I can always ensure that my percentage allocations always equal 100%. See my IF statement below. The basic tenant is that I need to divide by Total generated by the input. If I’m at a perfect 100%, my values don’t change. But if I’m below or under, my values are adjusted so that I can ensure that I equally 100%.
The downside to this particular formula is that it will need to be updated if you add additional cost pools. I also did not add in a default exception for #MISSING or a zero value, but you could layer that in as well if you wanted to establish a standard costing.
Returning to my form, I layered in the additional account, where I can true up the percentages. In this example, I have overallocated by 10%, which if ran the allocation ruleset referencing those values would generate 10% additional expenses. Instead, the true up derives to percentages of each relative to the total. My 50% takes a 5% reduction to be in line relative to the total, now equally 45%. My other values are adjusted in tandem as well.
All that’s left to do is update your Business Rules of Calc scripts to reference the new account that you created.
With this simple trick, you can save your user community, (and your) a lot of headache in the future and you never have to worry about over or under allocating your expenses again.