Allocation with Board
1. Abstract
Board is a great platform for building a planning and budgeting model and it accommodates large and complex allocation models. Board can process a lot of data in a short amount of time. The platform is also very flexible and allows the full customization of your allocation model.
2. Context
Board is a great platform for building a planning and budgeting model and it accommodates large and complex allocation models. Board can process a lot of data in a short amount of time. The platform is also very flexible and allows the full customization of your allocation model.
An allocation is commonly used in a planning and budgeting process. An allocation model distributes an amount, such as expenses, to different segments of a business. The segments can be profit centers, departments, sales districts, etc. An example of a simple allocation model is an IT expenses allocation model which allocates the total expenses among cost centers. This type of model requires a total amount (of expenses) and allocation percentages to calculate allocated amounts.
3. Content
The Expenses cube specifies cost amount by Account, Company and Cost Center.
The Allocation Driver is a percentage by Cost Center and Sales District that sums up to 100% per each Cost Center. Headcount is generally used in the IT expense allocation.
The process aims to allocate expenses at the Sales District level through an allocation driver.
Through a dataflow, we can allocate expenses by Sales District multiplying it by allocation driver percentages.
In this case we don’t have to extend calculation on new tuples since this dataflow can leverage the JOIN algorithm and automatically extend on any new combination.
IT Expenses can now be shown within a P&L report and used for analysis of the Sales District’s profitability.
Another useful feature is the ability to change the allocation base. There are many ways to define or calculate the allocation percentages. A common method is to calculate the percentages based on a set of values across allocated segments, for instance, Revenues. Although headcount is an appropriate base for the IT expense allocation, headcount may not be appropriate for advertising expense allocation. We may want to change the base to the percent of revenue when we allocate advertising expenses.
We can use a temporary cube as Revenue Denominators where we aggregate revenue by Month and Company.
Using the following expression, we will allocate Expenses by Sales District using Revenues as driver.
Extend calculation on new tuples for all members of the Sales District.
In order to optimize this calculation, the dataflow can be split into two steps:
- A step to calculate the Revenue Driver
- Revenue Driver = Revenue / Revenue Denominator
- A second step to run the allocation using the Revenue Driver
- Profit&Loss = Expenses / Revenue Driver
Related Content:
Comments
-
Thanks for this!
I would add here that it is better (to use this example) that the Profit&Loss cube has an additional dimension (call it Process) that has two members: UnAllocated & Allocation.
This generally provides visibility of the allocation process2 -
Hi Kassam,
Thanks for your comment.
The idea of this article is just an initiation to the allocation process, for a more detailed use case, please refer to the related article "How to allocate with a dynamic driver" where we used this additional "Layer" dimension.
Thanks,
1
