Allocations, Activity Based Costing and Essbase Functions

Avatar

By
February 1st, 2012


One of the prevalent Costing models, primarily used in manufacturing is Activity-based Costing. Activity-based Costing or ABC gained popularity as Overhead and Indirect Costs were scrutinized by management, production lines became more complex, and cost center managers were held more accountable for detailed product P&L’s and overhead cost controls.  Traditionally, a fixed percentage was applied to all products, service lines or segments to allocate Indirect Costs such as Rent expense, Depreciation and Home Office salaries.  Most companies these days, use some form of ABC methodology or Responsibility Accounting for internal management reporting, budgeting and forecasting and sometimes for measuring the performance of departments, managers and divisions.  This normally involves a pooling of costs at summary and cascading it down to cost centers and SBU’s through sometimes complex allocation spreadsheet models.  In summary, cost allocations are done for any one of the following reasons:

Let’s now explore how most allocations are performed in a Essbase cube.  The steps are typically the following:

  1. Determine the Total Amount to be allocated, i.e. Advertising Cost at Corporate HQ
  2. Determine the “Basis” or Cost Driver for the allocation, for example, Unit Sold by Entity and Segment
  3. Determine where the target range for the allocated amount, such as divisional rollups or individual cost centers
  4. Establish the rule for spreading and calculate the percentage

In the most simplistic allocation model, cost drivers are first selected to determine the basis of the allocation, for example, cost driver for allocating Rent Expense might be Square Footage occupied by relevant cost centers.  Other driver may be:

  • Headcount
  • Unit Sales
  • Unit Count
  • Labor Hours

In the sample database used selling and administrative costs are loaded to “Corporate HQ->No Segment” intersection:

 

 

 

 

 

 

 

 

 

 

 

 

 

The Basis or Cost Driver in this example is the ‘Units Sold’ by Segment and Entity:

Let’s assume, we want 50% of the Allocation amount to be allocated to ‘TV’ Segments and Cost Centers based on Units Sold which is loaded to ‘BegBalance’ in this case.  This is a multi-dimensional allocation and @MDALLOCATE function can be easily used as follows:

FIX(“HSP_InputValue”,”Input”,”FY12″,”Local”,”Actual”,”Final”)

“Advertising” =@MDALLOCATE (“Corporate HQ”->”No Segment” * .5, 2, @RELATIVE(“USA”,0),@RELATIVE(“TV”,0),”Units”->”BegBalance”, ,Share);

AGG(“Entity”,”Segments”);

ENDFIX

 

 

 

The first variable in the function is Allocated Amount, the second is the number of dimensions used in the allocation followed by the target distribution of the allocation.  Units is the Basis of the allocation and the last variable ‘Share’ indicates an even distribution based on units sold.  The allocation is done at Level0, thus, an aggregation is needed to rollup the parent values.

Now, alternatively, the other 50% of the Corporate overhead is to be allocated to ‘Digital Video’ segment, however, company wants to allocate a flat rate to each cost center that happened to sell “Digital Video” regardless of volume, in other words, the allocation is based on ‘Count’ of possible combination of Entity and Segment.  In other words, the total amount of $500,000 is spread evenly based on all entities/segment combinations that have units and the multi-dimensional allocation function is written like:

“Advertising”=@MDALLOCATE (“Corporate HQ”->”No Segment” * .5,2, @RELATIVE(“USA”,0),@RELATIVE(“DV”,0),”Units”->”BegBalance”,,Spread,SKIPMISSING);

Note that only the last 2 variables changed to make this happen, last one excludes ‘Null’ values for the Units Sold in the combinations.  There are other methods (next to last variable) to be used in this function, such as ‘Percent’, ’Add’, “Subtract’, ‘Multiply’ and ‘Divide’.

Other combinations of these variables can yield different distribution of allocated amounts and sometimes to erroneous results, therefore, it is advisable to choose the allocation criteria, drivers and methods carefully when dealing with the Essbase prebuilt allocation functions.  Also note that this function is relatively costly in terms of memory usage and calculation times, so apply the FIX/ENDFIX filters and increase Cache size when working with these functions.

Off course, these functions do not apply to all allocation concepts and sometimes custom code must be written to calculate percentages and allocated amounts based on percentages.   For example, the first allocation can be written as:

FIX(“HSP_InputValue”,”Input”,”FY12″,”Local”,”Actual”,”Final”,@RELATIVE(“USA”,0),@RELATIVE(“AllSegments”,0))

“Advertising” =”Corporate HQ”->”No Segment” * .5 *  (@CURRMBR(“Segments”)->@CURRMBR(“Entity”)->”Units”->”Begbalance”/”Units”->”Segments”->”USA”->”Begbalance”);

ENDFIX

A second pass through the database is required now to aggregate all segments and entities, so it takes more time to execute:

FIX(“HSP_InputValue”,”Input”,”FY12″,”Local”,”Actual”,”Final”)

AGG(“Entity”,”Segments”);

ENDFIX

And this assumes that the rates are already aggregated for BegBalance->Segments->Entity.  The advantage of using the @MDALLOCATE function is that no pre-aggregation of the total Basis is required and Essbase performs that at the same time is allocating in the memory.  Plus there is less of clutter in the Calc Script, making it more efficient.

 

 


Avatar

About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

Leave a Reply

Your email address will not be published. Required fields are marked *