Allocate Costs Across Products

This Essbase calculation script example allocates overhead costs to each product in each market for each month.

Overhead costs are allocated based on each product’s Sales value as a percentage of the total Sales for all products.

Assume that you added two members to the Measures dimension:

OH_Costs for the allocated overhead costs
OH_TotalCost for the total overhead costs

Example script:

/* Declare a temporary array called ALLOCQ based on the Year dimension */

ARRAY ALLOCQ[Year];

/* Turn the Aggregate Missing Values setting off. If this is your system default, omit this line */

SET AGGMISSG OFF;

/* Allocate the overhead costs for Actual values */

FIX(Actual)
   OH_Costs (ALLOCQ=Sales/Sales->Product; OH_Costs = 
   OH_TotalCost->Product * ALLOCQ;);

/* Calculate and consolidate the Measures dimension */

   CALC DIM(Measures);
ENDFIX

Essbase performs these calculations:

Creates a one-dimensional array called ALLOCQ to store the value of Sales as a percentage of total Sales temporarily for each member combination.

The size of ALLOCQ is based on the number of members in the Year dimension.
#MISSING values do not aggregate to their parents (SET AGGMISSG is set to OFF). Data values stored at parent levels are not overwritten.

If SET AGGMISSG OFF is your system default, omit this line. See #MISSING Values.
- Fixes on the Actual values.
- Cycles through the member combinations for Actual, and calculates OH_Costs.
- Takes the Sales value for each product in each market for each month and calculates it as a percentage of total Sales for all products in each market (Sales -> Product). The result is placed in ALLOCQ.
- Takes the total overhead costs for all products (OH_TotalCost -> Product) and multiplies it by the value it has just placed in ALLOCQ. The result is placed in OH_Costs.
  
  Note that both equations are enclosed in parentheses ( ) and associated with the OH_Costs member: OH_Costs (equation1; equation2;).
Calculates and consolidates the Measures dimension.