Consider the following use case: A customer would like to allocate expenses using a user-defined dimension and limited to a subset of cost centers. The allocation percentages will be derived using a combination of the defined cost centers and base-entity portions of total sales. In this case, UD1 contains a list of the existing cost centers for the customer. The allocation process uses four of these as highlighted at right. In addition to only being run at these cost centers, the rules for determining allocation percentages are different for each cost center used and need to be flexible to incorporate new entities.
In the table below, the user has defined which entities are used for each cost center to determine total percentage of sales. The green highlighted rows indicate known new entities not currently in the system. The allocation results must be done at the local currency level and consolidated up to USD for reporting purposes.
Assumptions and Methodologies
- Runs for all base entities for main hierarchy.
- Runs for all base expense accounts under parent of ‘Operating Expense.’
- Runs for all scenarios (as defined by the given workflow).
- Needs to run for all defined cost centers in one data-management job with the flexibility to also be able to run cost centers independently.
- Prefers to not rework the code for new UK entity switchover later in the year.
- Results reside in user-defined dimensions; UD4 contains data types for Allocation Out and Allocation In.
- Calculate the allocation percentages in new accounts defined in the ‘Statistical’ account hierarchy:
- AllocPct_[cost center number] – dynamically calculated based on the sales criteria defined above.
- Create a dynamic business rule that can run for each cost center.
- Create data-management objects to run the allocation.
Allocation Percentage Account Formulas
We created four new accounts to calculate the proper allocation percentages for each cost center type. These accounts are later referenced in the business rule. For each account, the base entity’s portion of total sales (defined from the table above) is written in UD4 data type of Alloc_In. The formulas ignore entities with ‘GAAP,’ ‘China GBP,’ and ‘OCI Calc’ in their member description.
Cost centers ‘CC_095,’ ‘CC_096,’ and ‘CC_098’ all work the same way. In the example below, we’re looking at the formula for ‘AllocPct_096.’ We start by getting the ‘Total Sales Amount’ for the ‘TotCompany’ parent member. As per the table in the first section, we are removing ‘CU_103_USD’ and ‘EN_703_USD’ from this total. ‘CC_095’ would include these amounts. We then check to make sure that the entity being written to is one of the members we’ve defined in the table as being calculated and ignore the rest. Again, the ‘CC_095’ would include both the ‘Parent’ and ‘LMD’ entities excluded below.
The divisor looks at the sales amount for the entity being calculated and designates that as the ‘Divisor.’ The final result is the ‘Divisor’ divided by the ‘Total Sales Amount.’
For cost center ‘CC_097,’ the formula returns a given amount of ‘.25’ to the defined entities.
The cube view below shows the rate not being calculated for ‘CC_096’ and ‘CC_098’ as requested and ‘CC_097’ only getting 25% to its defined entities.
The expense allocation process is kicked off via a data-management job. Under ‘Data Management Groups’, select ‘Expense Allocation.’ The ‘LANS_RunExpAlloc’ sequence will kick off the steps as shown below. The ‘LANS_ClearExpAlloc’ step clears previously calculated data. The ‘LANS_ExpAlloc_[###]’ steps run the allocation process for each of the designated Cost Centers (095, 096, 097, and 098 respectively), with the final step being a consolidation to show amounts at the ‘TotCompany’ level. Each step can be run independently as well.
The ‘LANS_ClearExpAlloc’ and ‘LANS_ExpAlloc_[###]’ steps kick off a custom calculate business rule called ‘LANS_ExpAllocation.’ For the clear step, it triggers the rule ‘ExpAllocClear.’ The allocation steps kick off ‘ExpAlloc’ and provides a Parameter of ‘Allocation Type’ as defined in each step. These jobs run for all base-level entities and currencies.
We are utilizing a ‘Finance’ business rule titled ‘LANS_ExpAllocation’ to do custom calculations. For this allocation process, we have two defined custom calculations. The first, ‘ExpAllocClear,’ simply clears the previously calculated data from the two UD4 members used to store allocation calculations, ‘Alloc_Out’ and ‘Alloc_In.’
The second custom calculation ‘ExpAlloc’ is used for all the ‘LANS_ExpAlloc_[###]’ steps. It takes the parameter from the data-management job and plugs it in as a string in the beginning to determine how the rule behaves.
The next section returns a list of base members of the Operating Expense parent (AC_00050) and gets the scenario name for which the job is being run.
One of the requirements was to accommodate a switch to two new UK entities in the future without a meaningful update to the existing rule. We have built in a parameter that will need to be updated in the rule once the changeover from CU_709_GBP (UK) and CU_712_GBP (UK Export) to CU_714_GBP (UK (New)) and CU_715_GBP (UK Export (New)), respectively, has been completed. Changing the below code to ‘True’ will accomplish that. These instructions were provided to the client.
The total expense amount is calculated at the total company level, whose entity, ‘TotCompany,’ is at USD. The allocation, however, is calculated at the Local currency level, which necessitates an FX translation both ways. We initially were leveraging the cube’s FX rates in OneStream, but those rates are an average rate, not a point-in-time rate, so it was causing variances when the allocation results were translated back to USD. What we determined is that it would be necessary to calculate an effective FX rate based on the workflow’s time. With the client’s input, we identified an expense account (‘AC_50010 – Salaries and Wages’) that would consistently have an entry for any given time period and base entity, with the known exception of ‘CU_101_USD – Canada.’ Using this account, we can calculate the effective FX rate by dividing the local currency amount of that account by its USD counterpart. The below cube view demonstrates the FX rate calculation.
This code grabs the local currency ID and calculates the effective rate to pass on to the allocation calculation (CalcFXRate). As previously noted, the Canadian entity does not have ‘Salaries and Wages’ expense loaded to it, but it currently has USD as its local currency, so that’s not a problem. Note that we have built in a subclause that will work if the Canadian entity ever switches Local currency from USD to CAD. The client has agreed that in the unlikely event the local currency changes to CAD, they will use the built-in FX rate, which will create an accepted variance.
The last step is to calculate the actual allocated expense amount. The code below retrieves the allocation percentage from the associated ‘AllocPct_[XXX]’ account based on the ‘Allocation Type.’ Because this is a one-size-fits-all calculation, we need to also parse out and return nothing for allocation types 096 and 098 where the entity equals either ‘CU_103_USD – Parent Latam’ or ‘CU_703_TRY – LMD.’ For allocation type ‘097,’ we also must consider whether the changeover to the new UK entities has been completed (blue section below). Finally, it completes the calculation and returns the results. The full ExpAlloc script is annotated in the appendix.