This example uses the @ALLOCATE function to allocate budgeted total expenses across expense categories for two products. The budgeted total expenses are allocated based on the actual values for the previous year.
Assume that you made the following changes, as shown in the outline in Figure 25-3:
Added a child, Lease, under Total Expenses in the Measures dimension
Added a child, PY Actual, to the Scenario dimension
Removed the Dynamic Calc tag from the Total Expenses member
Figure 25-3 Modified Measures and Scenario Dimensions
Assume that data values of 1000 and 2000 are loaded into Budget -> Total Expenses for Colas and Root Beer, respectively. These values must be allocated to each expense category, evenly spreading the values based on the nonmissing children of Total Expenses from PY Actual. The allocated values must be rounded to the nearest dollar.
/* Allocate budgeted total expenses based on prior year */ FIX("Total Expenses") Budget = @ALLOCATE(Budget->"Total Expenses", @CHILDREN("Total Expenses"),"PY Actual",, spread,SKIPMISSING,roundAmt,0,errorsToHigh) ENDFIX
The results of the calculation script:
Budget PY Actual Colas Marketing 334 * 150 Payroll #MI #MI Lease 333 200 Misc 333 100 Total Expenses 1000 450 Root Beer Marketing 500 300 Payroll 500 200 Lease 500 200 Misc 500 400 Total Expenses 2000 1100
* Rounding errors are added to this value.
Essbase cycles through the database, performing the following calculations:
Fixes on the children of Total Expenses.
Using a FIX statement with @ALLOCATE may improve calculation performance.
For Budget -> Colas -> Marketing, divides 1 by the count of nonmissing values for each expense category in PY Actual -> Colas for each month.
In this case, 1 is divided by 3, because there are 3 nonmissing expense values for Budget -> Colas.
Takes the value from step 2 (.333), multiplies it by the value for Budget -> Colas -> Total Expenses (1000), and rounds to the nearest dollar (333). The result is placed in Budget -> Colas -> Marketing.
Repeats steps 2 and 3 for each expense category for Budget -> Colas and then for Budget -> Root Beer.
As specified in the calculation script, rounds allocated values to the nearest whole dollar.
Essbase makes a second pass through the block to make the sum of the rounded values equal to the allocation value (for example, 1000 for Budget -> Colas -> Total Expenses). In this example, there is a rounding error of 1 for Budget -> Colas -> Total Expenses, because the expense categories add up to 999, not 1000, which is the allocation value. Because all allocated values are identical (333), the rounding error of 1 is added to the first value in the allocation range, Budget -> Colas -> Marketing (thus a value of 334).