Cost Allocations in FP&A Models

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!

Ryan C.

Sr. Data Engineering Manager | Empowering data driven organizations | Mentoring the next generation of engineers

2 年

Well written Robin. When I speak with customers about allocations, I always discourage the use of "accuracy". Allocations are never accurate, they are always an approximation, and many times a guess. I think it is better to talk about their precision and measure them accordingly.

  • 该图片无替代文字
Zsolt Moravcsik

Founder & Managing Partner at KnowledgeSeed | Expert in IBM Planning Analytics (TM1) | Corporate Performance Management (CPM) | Financial Planning & Analytics | Scenario Modeling | Data-Driven Decision Making

2 年

These days cost allocation and cost steering will be more and mir important as recession coming. But i am not really understanding why you need python for cost allocation. Tm1 rule engine does in 10 line of code for 10+ dimension cubes as faster than any SAP CO. With retaining the origin cost account or per driver on secondary account groups. You need just to fed the rules from the cost drivers.

要查看或添加评论,请登录

Robin Stevens的更多文章

  • FP&A For Financial Services Companies: Beyond the Basics

    FP&A For Financial Services Companies: Beyond the Basics

    The world of financial services is notoriously complex and it relies on many different interactions between different…

    4 条评论
  • How to Transition out of Excel as a Planning Tool

    How to Transition out of Excel as a Planning Tool

    #tldr: Pace yourself. Add value with the initial implementation.

  • Private Equity Portfolio Managers: Experiencing Spreadsheet Pain?

    Private Equity Portfolio Managers: Experiencing Spreadsheet Pain?

    The world of private equity investing, regardless of company structure, has long embraced the idea that investment…

  • Hello! My name is Robin Stevens, and I am your TM1 Therapist!

    Hello! My name is Robin Stevens, and I am your TM1 Therapist!

    #tldr: I’m here to validate your feelings “Therapist”, you ask? “I thought I needed a developer, or maybe a project…

    1 条评论
  • Developer Tips: Rocks in the River

    Developer Tips: Rocks in the River

    When we approach projects in the workplace, we tend to have a very linear approach: start at the beginning, make each…

    3 条评论
  • Biotech Industry FP&A

    Biotech Industry FP&A

    #tldr knowledge of industry-specific needs and trends = building more highly adopted models We all know what a planning…

    4 条评论
  • Level Up your Planning Model with Business Drivers

    Level Up your Planning Model with Business Drivers

    tldr – Streamline and update your planning model by making it more driver-based Let’s just say I’ve built a lot of…

    4 条评论
  • Overcoming the Tyranny of Rationality

    Overcoming the Tyranny of Rationality

    #tldr if your development project feels a bit like going to therapy, you’re probably doing it right Building a new…

  • TM1 Rules Functions -- New Resource!

    TM1 Rules Functions -- New Resource!

    Did you know that Cubewise just published definitions for all the IBM Planning Analytics TM1 Rules functions, with…

    1 条评论
  • Get CPE Credits learning TM1!

    Get CPE Credits learning TM1!

    Would you like to get CPE credits and improve your TM1/Planning Analytics skills? Our entire library of on-site…

社区洞察

其他会员也浏览了