TM1 Forum

Hello Everyone,

How can we write two separate formulas for calculating budget variance for "Revenue" and "Expenses"?

For Revenue Accounts: Variance = Actuals - Budget;

For Expense Accounts: Variance = Budget - Actuals;

This is because, we have defined all level zero Revenue accounts with weight = 1; and all all level zero Expense accounts with weight = -1. They are consolidated together to arrive at Net Income.

Budget data pulled from Oracle also has same pattern. i.e. Revenue budget figures are positive; and Expense Budget figures are negative. This is fine.


Problem definition:
-----------------------
Problem is for Expenses. A favourable expense variance is shown "negative" if we use only one formula; variance = actuals - budget;

Appreciate any inputs in this regard,

Thanks

buoyant wrote: How can we write two separate formulas for calculating budget variance for "Revenue" and "Expenses"?

For Revenue Accounts: Variance = Actuals - Budget;

For Expense Accounts: Variance = Budget - Actuals;

This is because, we have defined all level zero Revenue accounts with weight = 1; and all all level zero Expense accounts with weight = -1. They are consolidated together to arrive at Net Income.

Budget data pulled from Oracle also has same pattern. i.e. Revenue budget figures are positive; and Expense Budget figures are negative. This is fine.

Problem definition:
-----------------------
Problem is for Expenses. A favourable expense variance is shown "negative" if we use only one formula; variance = actuals - budget;

One way is not to use two formulas at all; you can use just one. Two possible ways that immediately come to mind:
(a) Use the ElIsAnc function inside an If() function. If the account is a child of the Total Revenue consolidation (or is the top level P&L one), calculate it as A-B, else B-A.
(b) Populate the accounts with an attribute which tells the rule which way to calculate the variance, again putting the rule in with an If() function which reads the attribute and uses the corresponding formula.

The down side of option (a) is that ElIsAnc isn't the speediest function in the world and if you have a large chart of accounts you could run into performance problems. The down side of (b) is that you have to be very assiduous in populating the attribute, though I'd schedule a TI to do that. A TI can use ElIsAnc and get away with it, but unless the cube/hierarchy is small I'd tend to avoid it in a rule if possible.

If the chart of accounts has numbering logic, which most seem to do (like assets starting with "1". etc., then I like to use an attribute called "Account_Type" and then use a rule on the attribute cube like:

['Account_Type'] = S:
IF(SUBST(Account!,1)='1','A',
IF(SUBST(Account!,1)='2','L',
IF(SUBST(Account!,1)='3','C',
IF(SUBST(Account!,1)='4','R',
IF(SUBST(Account!,1)='5','E',
CONTINUE),CONTINUE),CONTINUE),CONTINUE),CONTINUE);

This way the attribute will update itself automatically when a new account is added and the existing attributes are cached so there is no real performance hit to using a rule. It should be noted that my preference when building a model is easy maintainability over performance. A lot of people would say use TI to populate the attribute for performance but since my systems are generally done for people with no TM1 experience, I almost alway prefer to have as few TI as possible as long as the performance is still good.

tomok wrote:If the chart of accounts has numbering logic, which most seem to do (like assets starting with "1". etc., then I like to use an attribute called "Account_Type" and then use a rule on the attribute cube like:

['Account_Type'] = S:
IF(SUBST(Account!,1)='1','A',
IF(SUBST(Account!,1)='2','L',
IF(SUBST(Account!,1)='3','C',
IF(SUBST(Account!,1)='4','R',
IF(SUBST(Account!,1)='5','E',
CONTINUE),CONTINUE),CONTINUE),CONTINUE),CONTINUE);

This way the attribute will update itself automatically when a new account is added and the existing attributes are cached so there is no real performance hit to using a rule. It should be noted that my preference when building a model is easy maintainability over performance. A lot of people would say use TI to populate the attribute for performance but since my systems are generally done for people with no TM1 experience, I almost alway prefer to have as few TI as possible as long as the performance is still good.

Not quite. TM1 never caches calculated string values so the rule will need to be evaluated each time the account type attribute is queried. The additional overhead is probably minimal but assuming the dimension is being updated via an automated ODBC to the GL (which you would surely hope for) then it makes sense to populate the attributes with TI during the dimension maintenance vs. using a rule.

Thanks a lot for all your inputs and contributions.

To not to get into any performance issues i have taken a little different approach; though it is a bit crude and not an ideal solution. Any suggestions to improve it are welcome.

I have declared another variable called "ExpenseVariance" and used formula ExpenseVariance = B - A. The downside of this approach is two separate views need to be created for "Revenue/Net Income Variance Analysis" and "Expense Variance Analysis". However, "%Variance" is a common variable, works for both Revenue and Expense.

Pls see the code below.

===========================================================
SKIPCHECK;

['Variance'] = N: ABS(['Actual']) - ABS(['Budget']) ; C: ABS(['Actual']) - ABS(['Budget']) ;

['ExpenseVariance'] = N: ABS(['Budget']) - ABS(['Actual']) ; C: ABS(['Budget']) - ABS(['Actual']) ;


['%Variance'] = N:( ['Variance'] \ ABS(['Budget']) * 100 ); C: (['Variance'] \ ABS(['Budget']) * 100 );

FEEDERS;
['Budget'] => ['Variance'];
['Actual'] => ['Variance'];

['Budget'] => ['ExpenseVariance'];
['Actual'] => ['ExpenseVariance'];

['Budget'] => ['%Variance'];
['Actual'] => ['%Variance'];

===========================================================

Any suggestions to improve?

Thanks and Regards

As I think you have been told already you would be much better off creating Variance and ExpenseVariance as consolidations as opposed to additive/subtractive rules. Then you would not need rules and feeders at all to calculate the variances. As you know the expected signage of revenue vs. expense lines I can't see that wrapping anything with ABS() does anything particularly useful.

If you were to calculate the variance with an additive/subtractive rule (which is not recommended in case the italics weren't enough) then the correct approach would be as an N: only rule since once the leaves are calculated the consolidated elements follow natural dimension consolidation.
For example these rules would therefore be written:
['Variance'] = N: ABS(['Actual']) - ABS(['Budget']);

For the %Variance since this is a ratio type calculation where the same formula applies to both leaf and consolidated cells there is no need to separately declare the N: and C: filters, simply leave them out. For example this would then be written:
['%Variance'] = ( ['Variance'] \ ABS(['Budget']) * 100 );

Also unless there is some specific requirement that %Variance be displayed in a zero suppressed view then it would be better not to feed this calculation altogether.