Planning Analytics with Watson

 View Only

The Four Gotchas of Profitability Modeling

By Ann-Grete Tan posted Thu June 06, 2019 12:57 PM


Allocations and Profitability are two sides of the same coin. While the profitability of an entire firm may be easily apparent (“take Revenue and subtract Expenses”), there is true analytical value to looking at profitability at a more granular level, for example by Business Unit, Product or Customer. Depending on your business, this kind of analysis can drive useful things like improved expense management, better investment decisions and cross-sell opportunities.

While some companies do profitability analysis to gain insights into their businesses and enhance decision making, other companies are required to do allocations to comply with regulatory requirements.

In the financial services industry it is common to have multiple relationships with customers across many business lines. Customer profitability analysis is a crucial tool for understanding the nature of these relationships and identify further cross- and upselling opportunities. The manufacturing industry uses Activity Based Costing (ABC) to understand the true profitability of their products. The mutual fund industry is limited on how much of its marketing and promotion expenses it can pass on to investors. Calculating and reporting on these 12b-1 fees requires the use of allocations.

You cannot achieve profitability analysis at a granular level without building a model that performs allocations. As any accountant knows the most common allocations are of Indirect Costs, or Overhead. Profitability would be simpler to calculate if all costs were Direct and thus directly attributable, like the Cost of Goods Sold for a product. When this is not possible, you perform an allocation which is just a bit of math that divides up your indirect pool of dollars across your categories.

In the example below there is a Human Resources (HR) department that serves 3 revenue generating Business Units (BU_1, BU_2, BU_3). The premise is that since the Business Units all benefit from the services of the HR department, they should share in the cost of paying for it.

Next, we need to decide on a fair way to apportion (or allocate) the HR expense. Since HR does things like process payroll, and arrange health insurance and benefits, one reasonable option acceptable to the managers of all the Business Units is to split up the expense based on headcount. If you have more people in your business unit, there are more payroll records to be processed. By choosing to use Headcount as your allocation driver (as we have done in this example), you have made a modeling decision.

Allocations Example
Allocation Example: Using Headcount as the Driver

The rest is easy: Divide up your Cost Pool ($10,000 worth of HR Salary Expense) among the 3 business units, in proportion to their headcount. The total Salary Expense with Allocation is then used to arrive at a more complete view of Business Unit profitability. In this model, we validate the allocation by making sure that the total Allocated Salary Expense equals the original Cost Pool. The precise validation method will depend on how you design your model, but keep in mind it is an important step.

This was a simple example to illustrate the general idea. Suffice it to say, there are many ways of modeling allocations, and each of those ways comes with many possible design decisions. For instance:

  • Some models use Allocation Percentages or Allocation Rates instead of Allocation Drivers. These are just different ways of looking at the same thing:
    • Allocation Percentages can be derived from Allocation drivers by dividing by the total
    • Allocation Rates are the Amount per Unit.
  • A model may combine different versions of data for practical reasons related to timing (availability of data) or transparency. For example, Allocation Rates for Actual allocations may be set at the beginning of the year based on prior year actual data, or the budget.
  • Allocation Percentages may be derived outside your model.
    • Beware of allocation percentages that are based on someone’s “gut feeling”. It is always better to use real, objective data to drive the percentages!

Even though the basic premise is simple enough, it can get complicated. For example, you may use different methodologies for Actual and Plan, and different drivers for different kinds of costs. Furthermore, allocations may be multi-tiered, in the sense the results of one allocation may become part of the Pool for another allocation.

To help you navigate the complexity, and succeed in building a useful profitability model, here are four important pieces of advice:

  • NUMBER ONE - REMEMBER that that this is a model. It is something you get useful insights from, and which helps you uncover questions you should be asking - but it will never be 100% correct. If the model is telling you something that doesn’t make sense, you should start by re-examining the model, and validating what it is telling you before using it to make any major decisions!
  • NUMBER TWO - it is important to start with a vision and an idea for your model, but you should EXPECT to find that collecting and organizing your data to fit your vision will almost certainly require more work than you thought. Expect it to be an agile and iterative process and be ready to compromise on some of your original ideas (regardless of how elegant they were)!
  • NUMBER THREE - remember the PEOPLE! If you can’t explain your model, and the results it produces, you have wasted your time building it - regardless of how GOOD it is. Our best advice is to get input and buy in from the start. Prepare to SELL the idea to everyone who will be impacted by the results. And be prepared to sacrifice some accuracy in exchange for simplicity, transparency and buy-in.
  • NUMBER FOUR – invest in a flexible, robust and scalable modeling tool that is fit for purpose! While Excel is widely used for modeling allocations and checks the “flexibility” box, its limitations quickly become apparent at scale. If you use IBM Planning Analytics (TM1) for financial reporting, budgeting or forecasting, you already have a top-notch profitability modeling engine at your disposal! Furthermore, the IBM Planning Analytics Solution for Allocations & Profitability Modeling can serve as an accelerator, or you can take a custom-build approach.