Assignment example: Calculating a variance
Variance rules compute the difference between two members of one dimension, such as two members of the Time dimension. Variances are common in performance and process management. Typically, variance rules are special cases of assignment rules.
Variance rules apply equally across all other dimensions. The following example computes the variance between actual costs and budgeted costs, and stores the result in a model property called BudgetVariance.
%Budget Variance% = (Actual – Budget) / Budget.
Variance calculations can also be applied to aggregations of dimension hierarchies, or to leaf levels. Thus, authoring a variance rule often requires very little specification of scope.
The following examples show variance calculations performed on Scenario dimension members.
Variance for Operating Expenses
Variance on Interest Expenses
Variance Between Budget and Prior Year Estimate
Example: Variance for operating expenses
The following rule example calculates the variance between the budget and the annual estimate for operating expenses. This example is based on an imaginary situation at a resort company, limited to the expenses at a single resort. The variance spans a twelve-month period.
//
// SCOPE expression
//
SCOPE (
// LeafMembers function returns all leaf-level members
[Account].[Corporate].[Operating Expense].LeafMembers,
[BusinessDriver].[All Members].[NONE],
[BusinessProcess].[Standard].[Input],
[Currency].[All Members].[USD],
[Entity].[ResortOps].[Resort - Tahoe],
[ParentEntity].[All Members].[None],
[Product].[ResortProducts].[NONE]
// Scenario is Variance
[Scenario].[All Members].[Variance],
// Time dimension specifies 12 months in 2004
[Time].[Monthly].[Month 1 Year 2004]:[Time].[Monthly].[Month 12 Year 2004],
[Version].[All Members].[NONE]
);
//
// Statement
// Performs actual variance calculation Budget- Estimate
// Stores result in object specified by SCOPE
//
THIS =
([Scenario].[All Members].[Budget]) -
([Scenario].[All Members].[Annual Estimate]);
END SCOPE;
Variance on interest expenses
The next rule example calculates the variance between the budget and the annual estimate for interest expense. This example is based on an imaginary situation at a resort company, limited to the expenses at a single resort. The example resembles the previous example, but differs in that the Interest Expense member is specified for the Account dimension. The variance spans a twelve-month period.
//
// SCOPE expression
//
SCOPE (
[Account].[Corporate].[Interest Expense],
[BusinessDriver].[All Members].[NONE],
[BusinessProcess].[Standard].[Input],
[Currency].[All Members].[USD],
[Entity].[ResortOps].[Resort - Tahoe],
[ParentEntity].[All Members].[None],
[Product].[ResortProducts].[NONE]
// Scenario is variance
[Scenario].[All Members].[Variance],
// Time dimension specifies 12 months in 2004
[Time].[Monthly].[Month 1 Year 2004]:[Time].[Monthly].[Month 12 Year 2004],
[Version].[All Members].[NONE] );
//
// Statement
// Performs actual variance calculation, Budget- Estimate
// Stores results in cells specified by SCOPE
//
THIS =
([Scenario].[All Members].[Budget]) -
([Scenario].[All Members].[Annual Estimate]);
END SCOPE;
Variance between budget and prior year estimate
The following rule example calculates the variance between the Budget scenario and the Annual Estimate scenario for the prior year. In the statement list of the example, notice that two functions are used to calculate the variance for each month, CurrentMember (PEL) and Lag (PEL).
As the rule iterates through each destination cell, the calculation first returns the current member along the specified [Time].[Monthly] hierarchy, which is a month. Next, the Lag function retrieves the value that is 12 members back from the just-retrieved current member. This value is the corresponding month of the previous year.
Finally, the calculation computes the difference between the budget of the current member and the annual estimate for the previous year, and sets the variance value in the destination cell.
//
// SCOPE expression
// Specifies only 2 dimensions explicitly
// Thus, all other model dimensions take default values
SCOPE(
[BusinessProcess].[Standard].[INPUT],
[Scenario].[All Members].[Variance]
);
//
THIS =
( [Scenario].[All Members].[Budget] -
( [Scenario].[All Members].[Annual Estimate],
[Time].[Monthly].CurrentMember.Lag(12) )
);
END SCOPE;