HPCM Management Ledger Part 4 – Setting up Custom Calculations

In my previous posts I have gone over how to work with HPCM Management Ledger basic Allocation rules, and as powerful as these rules can be, sometimes they do not cover your entire requirement.  This is where the Custom Calculations come into play.

Before I get started in how to set up a custom calculation, let’s take a quick moment to review our HPCM set up. Our application has 4 ‘business’ dimensions in addition to the ‘Rule’, ‘Balance’ and ‘POV’ dimensions ( which are mandatory ‘Management ledger’  dimensions) .  As this is HPCM, our objective will be to move costs from one cost center, location, customer combination, to another or set of other cost centers, locations or customer combinations.  The outline looks something like this:

So now what kind of requirement might require us to use a custom calculation you ask, in a recent engagement of mine the client was allocating their  Overhead costs to customers  based on a ‘standard’ then moving the remainder  ‘over/under-absorbed’ costs to a corporate bucket.  This is a perfect example of a requirement for Custom Calculation, so let’s see how this breaks down.  Let’s first define our requirement:

                HR Costs are to be charged to all customers, using a standard of $xx per defined driver ( in this case Headcount  associated with each client) regardless of cost center, and the remaining in ‘over /under-absorbed costs’ to be assigned to the ‘No Customer’ member in the Customer dimension. Total costs to balance in each region.

The first question we have is does our outline support this? In our case we have most of the requirement, we already have driver for Headcount, but we don’t yet have any account to store the ‘HR Charge Per Headcount’, so we need to add that to the outline.  We will also need an Account in which to place our ‘standard HR Charge’ and ‘HR Over Under’, so we can add these into our income statement to support the requirement. (See my other post on how to edit the outline)

Now the outline looks better and we have place holders in the account dimension for storing all related costs and drivers;  let’s start up HPCM and start building our rule.  When it comes to Custom Calculations, you can only move cost to or from one part at a time, so in our example, we actually need to write 3 different rules.

Rule 1: Per Headcount cost charge to  each  customer

Rule 2: Calculate the Over Under cost

Rule 3: Reversal (allocation out) in original cost lines

Since we always want these 3 rules run together, let’s create a new Rule Set for them, make sure you enable your Rule Set and that it is set for Serial Execution, since we want our rules to run in a specific order

Create a new Custom Calculation by click on the Rule Set, then in rules, the Plus sign and Custom Calculation

Rule 1

Now let’s start defining our rule, in Custom Calculation you will notice you do not have a ‘Source’ anymore, this will be defined in your formula, we can define our description the same way as before, remembering to enable the rule as well

For target, we want to define the group of intersections that we will be putting cost into, this will also be used as the source intersections for our formula, in any case we don’t specify a specific member in the formula.  Note that in custom calculation, we also need to define a result dimension; I recommend that you keep this set to the Balance dimension.  So let’s turn our focus to the targets.

For our Customer dimension, we want to allocate to all customers, so we just select our parent member, as this is an ASO cube, all calculations are done at Level 0, so no need to use “descendants” type selections.

Next let’s do the same for Location

We know that our output cost is going to land in the “HR Allocated Cost” account, so go ahead and select that as your account member

Lastly, lets select all of our cost centers.

Now we get to the fun part, let’s write our formula. The syntax here is something new to me, but intersections will be defined with </MEMBERNAME,MEMBERNAME2/> syntax, and you can use any basic mathematical operator.  The first thing we need to define, is what member of the results dimension are we going to be pushing costs to? In our case this is going to be an “Allocation In” so let’s start there.  

</Allocation In/> =

Next we need to know how to calculate that value, keeping in mind our target members will be used for calculation as well.  We want to take our Headcounts and multiply by our Per Headcount charge, so we need to know where those are in our database.  Headcount will be loaded to the “Headcount” account, on the “Input” member, and all other dimensions match target dimensions so this one is easy

</Allocation In/> = </Input,Headcount/>

Then we need to multiply that against something, our standard cost.  Standard cost will be loaded to its own account, but on the No Location, No Customer, and No Cost Center members since the same standard is to be used for all locations. So let’s specify each of those dimensions.

</Allocation In/> = </Input,Headcount/> * </Input, HR Charge per HC,No Location, No Customer, No Cost Center/>

And that is it for this formula, click on Validate and make sure your formula is valid, then save.

Rule 2

For our next 2 rules we will need to repeat these steps, make sure to use a higher sequence number, since we want these rules to run in a specific order.

 For our target, lets select the following:

Account – HR Over Under

Location – All Locations

Customer – No Customer

Cost Center – HR 

For our formula, we need to calculate the total allocable cost, as well as the cost already allocated to find the difference, this will again be assigned to the “Allocation In” member so let’ first find total allocable cost. We want the total costs in “HR” so just use that member, for cost centers, Total Cost for our Account, we also want this for “All Customers” but we want costs to remain in their current location, so we do not specify this member.

</Allocation In/> = </Input,HR,All Customers,Total Cost />

Then we need to subtract any cost already allocated, this is in the rule we just wrote, so let’s pull all of the costs we just allocated in our last rule at, its already in Allocation In, so we can skip that, but I like to call it out, also the other dimensions will be “HR Allocated Cost”, “All Customers”, “All Cost Centers”, since each location is to be calculated on its own, we do not specify a location member.

</Allocation In/> = </Input,HR,All Customers,Total Cost /> - </Allocation In,HR Allocated Cost, All Customers,All Cost Centers/>

Rule 3

Our 3^rd rule is going to be our “Allocation out” rule, all we need to do is reverse our initial input costs, so it’s very straight forward.  Set the rule sequence to 3, and use the intersections we already determined in calculating our total in rule 2 just select those members as your target and use a straight forward formula like this:

</Allocation Out/> = </Input /> * -1

That’s it, make sure to validate and save then we can move to testing that our rules worked. Let’s enter some data, first Costs that will be allocated:

Then some drivers, including our standard cost, here I just made up random numbers.

Once your data is loaded, go ahead and run the rules, in HPCM click Manage Calculation, select “All Rules” and click on “Run Now”

Once your rules are done, head on over to Rule Balancing, and let’s see how our calculations worked out. When you go to rule balancing, make sure you select your POV and click on refresh, the screen will start out blank.

Here we have it, you can see that each of the individual rules results in an off balance change, but all 3 total up to our desired result.  To check that everything went where we expected, let’s use a slightly modified version of our original data input sheets in Smart View

Rule 1 outputs are highlighted here in red, Rule 2 in Green and Rule 3 in Purple, everything looks perfect, but let’s break it down.

Rule 1 – The Red Section, you can see for each 1 headcount we loaded in our inputs we have $100 of cost allocated in, for example IT, Customer 3, Chicago had 11 headcount and now has $1100 of cost

.

Rule 2 – This is our over under, our total costs for New York needed to total to $400, but due to the headcounts, we $1500 of cost, so our over under was a reduction of $1100 to get us back to $400 total in that location

Rule 3 – Here we have reversed our original costs, so an Allocation Out equal to our original inputs.

And that is all there is to it, Operation ‘Demystify Custom Calculations’ has been a success! There are some limitations to Custom Calculations that I hope I will be able to expand on in a future post, but for now this should help get you started on writing your own custom calculations. As always if you have any other questions, please feel free to leave a comment below.