SALES cube calculations

The SALES cube has two main business logics. One to group all business logic/calculations based on physical data (LC) and one to take care of the currency conversion.

The SALES cube currency conversion rules read the CurrConTyp attribute and calculate the values for the preconfigured EUR and USD group currencies. If there is a request for any other group currencies, these rules must be duplicated and modified. Currency elements must be added to the CURRTYPE dimension accordingly.

You can view and edit the rules in EPM Administration.

Business logic

The business logic incorporates the following two step process:

Step 1:

price per unit
direct material costs and direct production costs per unit
surcharge indirect material costs and surcharge indirect production costs are captured by product for every month based independent from customer

Step 2:

sales volume by customer and product
sales deduction in percent is captured accordingly

The contribution margin is automatically calculated from the information provided.

The cube rules are preconfigured for the local currency. The currency conversion process transfers data into other currencies.

R1: Sales Volume = 1 when price per unit > 0, for revenue calculation on consolidated elements (N-Rule)

This rule writes a value of 1 to the sales volume account when a price per unit is entered for a specific product (customer allocation unassigned). This enables average price calculations even if no data regarding of the sales volume are available (no division by zero). This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', INTERCO:'unassigned', PROFIT:'Sales Volume']
=B:IF([CURRTYPE:'LC', INTERCO:'unassigned', PROFIT:'Price per Unit']>0,1,stet);

R2: [CURRTYPE:'LC', PROFIT:'Change in Inventory'] (N-Rule)

This rule calculates changes in inventory on a monetary basis. The calculation uses the volumes of change in inventory determined by the R3 rule and the costs entered in step 1 of the sales planning process. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', PROFIT:'Change in Inventory']
=B:[CURRTYPE:'LC', PROFIT:'Change in Inventory (Volume)']*
[CURRTYPE:'LC', PROFIT:'Direct Material Costs per Unit']*
[CURRTYPE:'LC', PROFIT:'Surcharge Indirect Material Costs']
+
[CURRTYPE:'LC', PROFIT:'Change in Inventory (Volume)']*
[CURRTYPE:'LC', PROFIT:'Direct Production Costs per Unit']*
[CUR¬RTYPE:'LC', PROFIT:'Surcharge Indirect Production Costs']
+
[CURRTYPE:'LC', PROFIT:'Change in Inventory (Volume)']*
[CUR¬RTYPE:'LC', PROFIT:'Direct Material Costs per Unit']
+
[CURRTYPE:'LC', PROFIT:'Change in Inventory (Volume)']*
[CURRTYPE:'LC', PROFIT:'Direct Production Costs per Unit'];

R3: [CURRTYPE:'LC', INTERCO:'unsigned', PROFIT:'Change in Inventory (Volume)'] (N-Rule)

This rule calculates changes in inventory based on volume from the difference of production volume versus sales volume. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', INTERCO:'unassigned', PROFIT:'Change in Inventory (Volume)']
=B:[CURRTYPE:'LC', INTERCO:'unassigned', PROFIT:'Production Volume']-
[CURRTYPE:'LC', INTERCO:'TotalPartner', PROFIT:'Sales Volume'];

R4: [PROFIT:'Sales Deduction in percent'] (C-Rule)

This rule assures that sales deduction in percent is calculated based on the division of sales deduction and revenue as opposed to having it aggregated over the hierarchy. This rule is executed only on a consolidated level.

The syntax is:

[PROFIT:'Sales Deduction in percent']
=C:[PROFIT:'Sales Deduction']/[PROFIT:'Revenue'];

R5: [PROFIT:'Price per Unit'] (C-Rule)

This rule assures that the average price per unit is calculated based on the division of revenue and sales volume as opposed to having it aggregated over the hierarchy. This rule is executed only on a consolidated level.

The syntax is:

[PROFIT:'Price per Unit']
=C:[PROFIT:'Revenue']/[PROFIT:'Sales Volume'];

R6: [PROFIT:'Direct Production Costs per Unit'] (C-Rule)

This rule assures that the average direct production costs per unit is calculated based on the division of direct production costs and sales volume as opposed to having it aggregated over the hierarchy. This rule is executed only on a consolidated level.

The syntax is:

[PROFIT:'Direct Production Costs per Unit']
=C:[PROFIT:'Direct Production Costs']/[PROFIT:'Sales Volume'];

R7: [PROFIT:'Direct Material Costs per Unit'] (C-Rule)

This rule assures that the average direct material costs per unit is calculated based on the division of direct material costs and sales volume as opposed to having it aggregated over the hierarchy. This rule is executed only on a consolidated level.

The syntax is:

[PROFIT:'Direct Material Costs per Unit']
=C:[PROFIT:'Direct Material Costs']/[PROFIT:'Sales Volume'];

R8: [PROFIT:'Gross Margin in percent'] (General Rule)

This rule calculates the gross margin in percent from the ration of gross margin and revenue on all hierarchy levels.

The syntax is:

PROFIT:'Gross Margin in percent']
=[PROFIT:'Gross Margin']/[PROFIT:'Revenue']*100;

R9: [CURRTYPE:'LC', PROFIT:'Sales Deduction'] (N-Rule)

This rule calculates the sales deduction from the multiplication of revenue and sales deduction in percent. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', PROFIT:'Sales Deduction']
=B:[CURRTYPE:'LC', PROFIT:'Revenue']*
[CURRTYPE:'LC', PROFIT:'Sales Deduction in percent'];

R10: [CURRTYPE:'LC', PROFIT:'Revenue'] (N-Rule)

This rule calculates revenue from the multiplication of sales volume and price per unit. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', PROFIT:'Revenue']
=B:[CURRTYPE:'LC', PROFIT:'Sales Volume']*
[CURRTYPE:'LC', PROFIT:'Price per Unit'];

R11: [CURRTYPE:'LC', PROFIT:'Direct Production Costs'] (N-Rule)

This rule calculates direct production costs from the multiplication of sales volume and direct production costs per unit. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', PROFIT:'Direct Production Costs']
=B:[CURRTYPE:'LC', PROFIT:'Sales Volume']*
[CURRTYPE:'LC', PROFIT:'Direct Production Costs per Unit'];

R12: [CURRTYPE:'LC', PROFIT:'Indirect Production Costs'] (N-Rule)

This rule calculates indirect production costs from the multiplication of sales volume and direct production costs per unit and the surcharge for indirect production costs. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', PROFIT:'Indirect Production Costs']
=B:[CURRTYPE:'LC', PROFIT:'Sales Volume']*
[CURRTYPE:'LC', PROFIT:'Direct Production Costs per Unit']*
[CURRTYPE:'LC', PROFIT:'Surcharge Indirect Production Costs'];

R13: [CURRTYPE:'LC', PROFIT:'Direct Material Costs'] (N-Rule)

This rule calculates direct material costs from the multiplication of sales volume and direct material costs per unit. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', PROFIT:'Direct Material Costs']
=B:[CURRTYPE:'LC', PROFIT:'Sales Volume']*
[CURRTYPE:'LC', PROFIT:'Direct Material Costs per Unit'];

R14: [CURRTYPE:'LC', PROFIT:'Indirect Material Costs'] (N-Rule)

This rule calculates indirect material costs from the multiplication of sales volume and direct material costs per unit and the surcharge for indirect material costs. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', PROFIT:'Indirect Material Costs']
=B:[CURRTYPE:'LC', PROFIT:'Sales Volume']*
[CURRTYPE:'LC', PROFIT:'Direct Material Costs per Unit']*
[CURRTYPE:'LC', PROFIT:'Surcharge Indirect Material Costs'];

R15: PROFIT:'Price per Unit from unassigned to all Customer (N-Rule)

This rule writes the price per unit to all elements of the N-Elemente Sales subset (customers) in the INTERCO dimension to assure revenue can get calculated when sales volume information is entered. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', INTERCO:{{N-ELEMENTE SALES}}, UNIT:{{N-ELEMENTS}}, PROFIT:'Price per Unit'] 
=B:[CURRTYPE:'LC', INTERCO:'unassigned', PROFIT:'Price per Unit', UNIT:'unassigned'];

R16: Direct Material Costs per Unit from unassigned to all Customer (N-Rule)

This rule writes the direct material costs per unit to all element of the N-Element Sales subset (customers) in the INTERCO dimension to assure direct material costs can get calculated when sales volume information is entered. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', INTERCO:{{N-ELEMENTE SALES}}, PROFIT:'Direct Material Costs per Unit']
=B:[CURRTYPE:'LC', INTERCO:'unassigned', PROFIT:'Direct Material Costs per Unit'];

R17: Surcharge Indirect Material Costs from unassigned to all Customer (N-Rule)

This rule writes the surcharge for indirect material costs per unit to all element of the N-Elemente-Sales subset (customers) in the INTERCO dimension to assure indirect material costs can get calculated when sales volume information is entered. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', INTERCO:{{N-ELEMENTE SALES}}, UNIT:{{N-ELEMENTS}}, PROFIT:'Surcharge Indirect Material Costs']
=B: [CURRTYPE:'LC', INTERCO:'unassigned', PROFIT:'Surcharge Indirect Material Costs', UNIT:'unassigned'];

R18: Direct Production Costs per Unit from unassigned to all Customer (N-Rule)

This rule writes the direct production costs per unit to all element of the N-Elemente-Sales subset (customers) in the INTERCO dimension to assure direct production costs can get calculated when sales volume information is entered. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', INTERCO:{{N-ELEMENTE SALES}},UNIT:{{N-ELEMENTS}}, PROFIT:'Direct Production Costs per Unit'] 
=B: [CURRTYPE:'LC', INTERCO:'unassigned', UNIT:'unassigned', PROFIT:'Direct Production Costs per Unit'];

R19: Surcharge Indirect Production Costs' from unassigned to all Customer (N-Rule)

This rule writes the surcharge for indirect production costs per unit to all element of the N-Elemente-Sales subset (customers) in the INTERCO dimension to assure indirect production costs can get calculated when sales volume information is entered. This rule is executed only for base elements.

The syntax is:

[CURRTYPE:'LC', INTERCO:{{N-ELEMENTE SALES}}, PROFIT:'Surcharge Indirect Production Costs']
=B:[CURRTYPE:'LC', INTERCO:'unassigned', PROFIT:'Surcharge Indirect Production Costs'];

R20: Surcharge Indirect Material Costs (C Rule)

This rule assures that the surcharge for indirect material costs are calculated based on the division of indirect material costs and direct material costs as opposed to having it aggregated over the hierarchy. This rule is executed only on a consolidated level.

The syntax is:

[PROFIT:'Surcharge Indirect Material Costs']
=C:[PROFIT:'Indirect Material Costs']/[PROFIT:'Direct Material Costs'];

R21: Surcharge Indirect Production Costs (C-Rule)

This rule assures that the surcharge for indirect production costs are calculated based on the division of indirect production costs and direct production costs as opposed to having it aggregated over the hierarchy. This rule is executed only on a consolidated level.

The syntax is:

[PROFIT:'Surcharge Indirect Production Costs']
=C:[PROFIT:'Indirect Production Costs']/[PROFIT:'Direct Production Costs'];

R22: Currency Conversion EUR Sales (N-Rule)

The syntax is:

[CURRTYPE:'EUR']=
B: IF(GETATTR('PROFIT', !Profit, 1, 'CurrConTyp')@='A',
   [CURRTYPE:'LC'] / 
   DB('EXRATE',!TIME, !VERSION, 'EUR', 'Average rate', 
      DB('UNITCONF',!TIME, !VERSION, !UNIT, 'Currency')
   ),
      IF(GETATTR('PROFIT', !Profit, 1, 'CurrConTyp')@='E',
         [CURRTYPE:'LC'] / 
         DB('EXRATE',!TIME, !VERSION, 'EUR', 'End rate',	
         DB('UNITCONF',!TIME, !VERSION, !UNIT, 'Currency')
         ),
         if(GETATTR('PROFIT', !Profit, 1, 'CurrConTyp')@='U',[CURRTYPE:'LC'], stet
      )
   )
);

R23: Currency Conversion USD Sales (N-Rule)

The syntax is:

[CURRTYPE:'USD']
=B: IF(GETATTR('PROFIT', !Profit, 1, 'CurrConTyp')@='A',
   [CURRTYPE:'LC'] / DB('EXRATE',!TIME, !VERSION, 'USD', 'Average rate', 
   DB('UNITCONF',!TIME, !VERSION, !UNIT, 'Currency')),
      IF(GETATTR('PROFIT', !Profit, 1, 'CurrConTyp')@='E',
         [CURRTYPE:'LC'] / 
         DB('EXRATE',!TIME, !VERSION, 'USD', 'End rate', 
            DB('UNITCONF',!TIME, !VERSION, !UNIT, 'Currency')
         ), 
         IF(GETATTR('PROFIT', !Profit, 1, 'CurrConTyp')@='U',[CURRTYPE:'LC'],
         stet
      )
   )
);