Thursday 18 July 2019

Data Phasing and Allocation

Today, I'd like to discuss about techniques I've used to implement rules for cost allocation and data phasing.  Sticking to the "no hard-coding" mantra, here are some of the methods that were used in my PBCS projects.

The examples in this blog rely on a PBCS application with the following dimensions and members:
  • Entity
  • Cost Centre
  • Account
  • Measure
  • Period
  • Scenario
  • Version
  • Cycle
  • Cycle_Line
All the dimensions contain members you would usually find in any PBCS application.  The exceptions are in the Measure, Cycle, and Cycle_Line dimensions which I'll discuss in more detail in sections below.

Phasing of Plan Data

Introduction

More often than not, you'd find yourself getting requests to build planning models that is quick to prepare and the most requested function I found has been the phasing of the budget or forecast data in the outer years based on trends derived from Actuals.  In this instance, planners would have entered their annual planned numbers and will rely on the business rule to phase their plans across the months for the outer years based on historical trends that he/she has selected.  This is to ensure that the plans relfect the actual trend.  One such implementation of mine, the client has requested for a phasing model that allows them to assign one of the following phasing methods to each of the account that they wish to phase:

  • Last Year Actual
  • Last 2 Years Actual Average
  • Last 3 Years Actual Average
Here's a sample of how the phasing works:











In the example above, the Annual Amount of $20,000 for "Acc001" in FY19 is phased based on "Last Year's Actual" while "Acc004" is using the "Last 2-Years Actual Average" as the phasing basis (third row of "Acc004").  So, with "Acc001" the calculation is simply taking the "Amount to be Phased" and then multiplying with the monthly last year actual number for "Acc001" before being divided by the total last year actual amount for "Acc001".  The formula is shown below:










While "Acc004" uses the Average of Last 2 Years' Actual to phase the annual data.









Here's what I did:

Tagging of Phasing Method

A webform was created to expose the relevant accounts that are subjected to the phasing logic with a measure (associated with a Phasing_Method smartlist) displayed in the column.  The Phasing_Method smartlist contains the 3 methods mentioned above.  This form provides the users the flexibility to vary the phasing method by account.




















Another form was defined to capture the annual planned amount and this amount is captured against the "BegBalance" period member.

The Rule

The phasing of the data is done through a business rule where it will evaluate the phasing method associated to each of the accounts and then apply the necessary logic to spread the annual plan data across the periods using the user-selected basis.  Here's a snapshot of the business rule:
























Inter & Intra-Company Cost Allocation

Introduction

Another use case for the above implementation is for cost allocation.  I realised that I have written similar post about cost allocation when discussing about reverse-mapping techniques.  You can read the post here.  In this blog, I have expanded the description to include some examples to further illustrate how the allocation model will work.  So, read on.

In building such a model for one of my clients, I was determined to build them one that they have full control over how the allocation is performed.  By this, I mean to develop one that allows the users to determine the source of costs to be allocated and the targets for the allocated costs.  The model will also accept the allocation ratio for the source/target combination.  The spreadsheet below illustrates the allocation of costs from Entity A Cost Centre 001 to Entity A Cost Centre 002, 003 & 004.



And here's a sample of the Inter-Company allocation where I will allocate part of the cost of Entity A to entities B, C, & D.  Note that 40% of the cost from Entity A is retained within the same entity.

















Now, let's examine the building blocks for this model:

Dimensions & Members

As mentioned earlier, I'll focus on the Measure, Cycle, and Cycle_Line dimensions in this section.  T

Measure Dimension

The main function of this dimension is to enable us to further break an account down further to delineate the origin of a piece of data.  Consider the following hierarchy in the Measure dimension:

  • Measure
    • Total_PL
      • Total_PL_Before_Alloc
        • Loaded_Amount
        • Direct_Amount
        • Phased_Amount
        • Workforce_Amount
        • Project_Amount
      • Allocation_Amount
        • Alloc_In
        • Alloc_Out
    • Allocation_Drivers
      • Phasing_Method
      • Cycle_Description
      • Source_Entity
      • Source_Cost_Centre
      • Source_Account
      • Target_Entity
      • Target_Cost_Centre
      • Target_Account
      • Alloc_Ratio
The "Loaded_Amount" member is used to store the data that is loaded from external data sources (e.g. Actuals from ERP).  "Direct_Amount" member is an input member capturing inputs from users during the planning process while the "Workforce_Amount" and "Project_Amount" members will store that data coming from the Workforce and Project models respectively.  The "Allocation_Amount" member which is further divided into "Alloc_In" and "Alloc_Out" will be used in the Allocation model.  All these members will roll-up to "Total_PL" to arrive at the total P&L number.

Extending on the example discussed in the introduction, I am adding the Measure dimension to the Intra-Company allocation table:



























The measure "Total_PL_Before_Alloc" is a roll-up of amounts from "Loaded_Amount", "Direct_Amount", Workforce_Amount", and "Project_Amount".  The example above shows that the last 3 members ("Direct_Amount", Workforce_Amount", and "Project_Amount") contain values of $400, $300, and $300, and therefore,"Total_PL_Before_Alloc" being the parent will hold the sum of $1,000.  We will use this roll up as our source for allocation.  

As a result of the allocation process, the model will allocate to the target Cost Centres (002, 003, & 004) based upon the user-defined ratios and the allocated numbers are stored in the "Alloc_In" measure.  The whole amount from the source "Total_PL_Before_Alloc" will also be reversed and posted to the source Entity/Cost Centre/Account combination and the reversal (-$1,000 in this example) is stored in the "Alloc_Out" measure.  So, when reported at "Total_PL" level, the Overheads from the source Entity/Cost Centre" combination (Entity A, Cost Centre 001) will be fully allocated to the respective targets because the sum of "Total_PL_Before_Alloc" ($1,000) and "Allocation_Amount" (-$1,000) will result in zero.


Cycle & Cycle_Line Dimension

These dimensions allow users to define different allocation rules (in Cycle) and multiple lines per allocation rule (Cycle_Line).  The members in both the dimensions are just running sequential numbers.

  • Cycle
    • C001
    • C002
    • C003
    • ...

  • Cycle_Line
    • CL001
    • CL002
    • CL003
    • ...

Source/Target & Ratios Definition

As mentioned in the introduction, this model serves to allocate cost within the company as well as inter-company allocation.  The mapping table that I've defined via a webform capture not only the source and target accounts, but also the source/target Entity & Cost Centre combination as well.  Here's an illustration of the webform defined to capture the source/target combinations:















The top part of the diagram shows the form to define the Allocation Cycle while the bottom part of the diagram shows the detail allocation lines for each allocation cycle.  Notice that both forms are capturing the combinations against the NA members for Entity, Cost Centre, and Account.  You might be wondering how do I then capture the different combinations of source and target Entity, Cost Centre, and Account combinations?  The answer lies in Smartlist (see next section for details on smartlists).









Columns 3 through to 8 of the Allocation Line form are measures with smartlists tagged to each of the member.  This enables users to then pick the source/target entity, cost centre, and account of their choice from their associated smartlist.  Column 9 is an input column allowing users to enter the allocation ratio for each allocation line.  The above definition is  reflective of the Intra-Company Allocation example that I have mentioned in the introduction earlier.


Smartlists

The following heirarchy-driven smartlists are required to support the allocation model.  The smartlists will be assigned to Allocation_Driver members in the Measure dimension:

  • Source Entity
  • Source Cost Centre
  • Source Account
If you have Target Entities, Cost Centres, or Accounts that are different from the source, you'd need to also define Target smartlists from them:
  • Target Entity
  • Target Cost Centre
  • Target Account

A sample of a hierarchy-driven smartlist is shown below.  Please refer to my blog on Hierarchy-Driven Smartlists here for more in-depth description on the process to build such smartlist.





















The Business Rule

To code the allocation logic, I have decided to use the @XWRITE function.  The cool thing about the model is that the rule is able to create the necessary target blocks based on what the users have selected for their source/target combinations via the webform created.  This helps minimise any chance of a block explosion where blocks are created unnecessarily which will in turn adversely affect the performance of the cube in general.  Keep a look out for my post on block creation which is still a "work in progress".  Also, the fact that all the source/target combinations are captured against Entity_NA, Cost_Centre_NA, and Account_NA means that the rule will rule faster as it does not need to cycle through all the different entity, cost centre, and account base members.  My rule is fixed to the NA members only and using @XWRITE to create the necessary blocks and perform the required allocation.  Here's the rule:




























Notice that I've used the @XWRITE function to both create the blocks and perform the allocation.  The first part of the rule posts the reversal amount to the Source Entity, Cost Centre, and Account combination and to the "Alloc_Out" measure.  The value of "Total_PL_Before_Alloc" is multiplied by -1 before posting the results.

The second section of the rule deals with the allocation of the "Total_PL_Before_Alloc" amount from the Source Entity, Cost Centre, and Account combination to the Target Entity, Cost Centre, and Account combination and to the "Alloc_In" measure.  The value of "Total_PL_Before_Alloc" is multiplied by "Alloc_Ratio" before posting the results to the targets.

That's it folks for this blog.  Hope you've enjoyed reading this as much I have enjoyed writing it.  Do leave a comment and let me know what else you want me to cover.

Until then, sayonara for now.


2 comments:

  1. Awesome and very neat!! Thanks for sharing Chan.

    ReplyDelete
    Replies
    1. Thanks for the feedback, Srikanth. Much appreciated!

      Delete

And then there's the Waterfall Chart in PBCS

Ever found yourself looking to create a Waterfall chart in PBCS Dashboards only to be left disappointed?  Sure, you can have this done when ...