Best Practice to implement "Variance" Calculations in TM1

[tm1expert]

Hi,

We have a reporting Cube that has data for many different scenarios. We also need to implement "Variance" Scenarios. Our scenario dimension has the following Elements:

Actuals
Budget
Forecast

We want to implement also the following Elements:

Actuals vs. Budget (Which is Actuals minus Budget)
Actuals vs. Forecast (which is Actuals minus Forecast)
Budget vs. Forecast (which is Budget minus Forecast)

The cube also has the Accounts dimension, which includes statistical accounts and Metrics.

I know that there are 2 ways to achieve this.

One way is to create these "Variance" elements as independent elements and calculate their values in Rules as following:

['Actuals vs. Budget'] = ['Actuals'] - ['Budget']

And then Feed the variance with both elements

The other way (my preferred way) is to Create the "Variance" Element as consolitation of other 2 elements use the weight of -1 for Budget.
This works fine for normal Accounts but not for Statistical and Metric Accounts.
What I'm doing in this case is overwrite the C: Calculation for these accounts, which seems to be working.

Which method you think is more optimal ?

Thank you

[qml]

The second one, by far. Using natural consolidations gives you much better performance, smaller memory consumption and is instantly recognisable for what it is by users.

Of course, the simplicity and calculation performance are somewhat trumped when you have to apply C-level rules for the types of data that require different consolidation methods, but as a rule of thumb use consolidation by hierarchy whenever you get the chance.

[lotsaram]

Always go for the natural consolidations. This also has the significant advantage that where you do need to overwrite the consolidation with a C level rule there is no need to feed the rule. I would even go so far as to make any variance % measures consolidations as well, then these also do not need to be fed should you want them to display in a zero suppressed view for example.

[tm1expert]

Thank you for your quick answer.

lotsaram: Can you please explain how would you implement the Variance % as Consolidation? Do you mean only define the Varance % as a consolidation of Variance$ and then Use the C: Rules to calculate the value of Varance %? Or is there any way to have TM1 calculate the Variance % also:) That would be miracle

Thank you again

[lotsaram]

Thank you for your quick answer.

lotsaram: Can you please explain how would you implement the Variance % as Consolidation? Do you mean only define the Varance % as a consolidation of Variance$ and then Use the C: Rules to calculate the value of Varance %? Or is there any way to have TM1 calculate the Variance % also:) That would be miracle

Thank you again

Essentially yes. "Normally" you would have variance % as a rule without an N: or C: qualifier. If you make ActBud variance % a consolidation with ActBud variance as the child (or actual and budget as children) then the only thing that is different about the rule is that it will be a C: rule and there is no requirement to feed such a rule since values in actual and budget will automatically "feed" the calculation.

[stephen waters]

Hi,

We want to implement also the following Elements:

Actuals vs. Budget (Which is Actuals minus Budget)
Actuals vs. Forecast (which is Actuals minus Forecast)
Budget vs. Forecast (which is Budget minus Forecast)

Leaving aside technical issues check that the variance calculation does not vary with account.

What sign convention are you using for your figures in TM1?
- if you are using dr=negative\cr=positive (ie costs negative, income positive ) then this formula will work for all accounts
- if you are using dr=positive\cr=negative (ie costs postive, income negative, standard convention for many ledgers ) then this formula will need to be reversed but will work for all accounts.
- If you are using all figures positive (standard for budgeting and management reporting applications) you will need to multiple the variance by an indicator depending whether it is income or expense.

And for % variances I think you will need the denominator to be an absolute value.

[tm1expert]

It makes perfect sense

Thank you all