Cost Allocations in FP&A Models

Effective financial planning and analysis requires a deep understanding of the costs associated with various parts of a business. However, allocating these costs accurately can be challenging. In this article, we will explore the basics of business allocations and discuss how they can be automated effectively in your FP&A models.

Allocations are a fundamental part of many analysis and planning models, both for actual and forecasted data. Here, when I say “allocations”, I mean taking costs (or other resources) and, via a rubric, spreading centralized costs across an organization – generally across different departments, entities, or projects. Some common allocations that fit into this category include:

• Allocating Facilities costs to all departments based on square footage per department
• Allocating IT costs to all departments based on number of employees per department
• Allocating expenses across projects based on hours booked to project

Generally, the goal of such allocations is to see profitability at a more granular level than is possible merely by looking at direct costs. While it can be an exercise in futility to allocate all costs, but generally, it’s considered a good FP&A best practice to allocate things that make sense to allocate, such as the items described above.

It’s important to note that allocation algorithms will never be “perfect.” It’s best to keep the algorithms simple and practical, and not make your allocation model overly complex. The more complex it becomes, the more of a political football allocations become, as well. The goal is to get a better picture of profitability by business activity, and it’s important not to get caught up in esoteric debates which have a tendency to creep in to allocation logic discussions.

When talking about allocations, it’s helpful to have a common language, as allocations can turn into a tangled ball of string quickly. I generally talk about the following four things, which I will define here for clarity:

• Source: The Source of the allocation is generally an expense, such as “Total Expenses for the IT department.”
• Target: The Target defines which areas of the company get the allocation, such as “All departments except the IT department.”
• Basis: The Basis is the method used to distribute the source dollars, such as “Each department gets a percentage of Total IT Expense based on the relative headcount of the department.”
• Contra: The Contra is sometimes needed in order to avoid double-counting (depending on the type of allocation) such as “Use this IT GL Account for the offset of the Total Expenses.” In this case, when the allocation is completed, the Total Expense for the IT department would be zero.

Allocating can be tricky because often the targets for allocation have to be evaluated at a very granular level. For example, with TM1 this often means making trade-offs between live Rules calculations, which can sometimes have performance implications due to “feeding top of house”, and using TI, which generally is evaluating one data point at a time, which is sometimes not optimal. Both Rules and TI are great solutions for many allocation models, but they are not the only two options open to us.

We have introduced Python, via TM1Py (https://code.cubewise.com/tm1py-overview) to overcome many of the difficulties of modeling for allocations. TM1Py allows us to evaluate large cross-sections of data, which turns out to be very useful for many allocation models. In particular, it helps with what I call the “denominator problem.”?The denominator problem stems from the fact that most allocations have a lot of exceptions such as “only allocate to certain departments” or “exclude xyz expense in these situations.”?In order to ensure that we allocate 100% of the costs, for example, to the departments desired, you often end up needing dozens, if not hundreds, of different denominators so that all the exceptions are taken into account.

TM1Py is a natural fit for this, since Python evaluates arrays of data, rather than single data points. This turns out to be just what allocations needed. We can have TM1Py do the “heavy lifting” of calculating all the percentages for every allocation, and then the data can be populated accordingly back into the TM1 model. We can even add commentary that breaks the calculation down, allowing for some traceability in the TM1 model.

It's likely that your allocation model will have some Rules, some Turbo Integrator, and some TM1Py:

• Rules: Best for calculations that change frequently, and preferred for models where performance is not impacted
• TI: Excellent for allocations where one datapoint is evaluated at a time, and there are not too many denominators. Also utilized as a kick-off for TM1Py processes, and may be a “helper program” to TM1Py
• TM1Py: Perfect for models with lots of different cross-sections to be evaluated, and many denominators. Can be used to calculate results which can then be sent back to TM1 or other systems.

If you’d like to get in touch, https://cubewise.com/reachout/

If you’d like to read more content like this, please make sure we’re connected on LinkedIn and reach out with a DM!