Help with cube view and balance sheet

[craigparris1]

Hi all,

I need some help with, firstly, understanding an issue that has been raised by our Finance team, and secondly, putting a resolution in place. Forgive my ignorance in some of the finer accounting points of this topic - I'm in IT, not Finance. Similarly, I have had some past experience with Microsoft OLAP tools, but not TM1.

The issue is relating to reporting of "retained earnings" in the balance sheet. We have a GL cube which builds off a data source containing monthly balances. There is a rule within the cube which looks like the following:

# 9510 - Retained Earnings Additions
['9510', '$' ] = N:
IF(SUBST(!Period, 5,2) @= '01',
DB('ECA_GL',!Scenario,!Period,!Company,!BU,'NPAT',!Values_GL),
DB('ECA_GL',!Scenario,!Period,!Company,!BU,'NPAT',!Values_GL) +
DB('ECA_GL', !Scenario,DIMNM('Period', DIMIX('Period', !Period)-1),!Company,!BU,'9510',!Values_GL)
);

So, account "9510" is essentially just a cumulative sum of the "NPAT" totals, starting from the first period in a range. As you can probably tell, the reporting is generally done by "Company", then "BU" (Business Unit), which are the first two segments of the GL account.

Now, the issue that's occurring is, let's say there's a particular business unit that's no longer having txns posted against it, so from, say, December 2011, the ongoing balances of any accounts for that business unit are $0.00. So, the overall "NPAT" total for that business unit is also $0.00. It seems that from that point (ie. Jan-2012) onwards, the rule for retained earnings is no longer applied, so the amount for "9510" for Jan-2012 shows as $0.00. (then the same for Feb-2012). (I've done some reading in the TM1 doco and it appears to me that even if there are "balance" records for a particular account, for a particular month, if those values are zero then TM1 will just ignore them. Is that correct? Is that ultimately the reason why the rule doesn't get applied?)

Attached is a image showing a quick example of what I mean. You can see from the sample image that once it gets to Jan-2012, the retained earnings balance stops being calculated, and then - ultimately - the retained earnings amount that should continue to carry forward into the subsequent periods is not contributing to the total rollup (eg. when viewing the same "Company" but ALL business units).

I can run a similar view using "year-to-date" elements within the Time dimension, and that then shows consolidated "NPAT" totals that carry forward into subsequent periods, but Finance want to see a view similar to what's in the sample image, it's just that the retained earnings need to continue to carry forward even when there are no (or $0.00) underlying balances in the accounts for a particular business unit. That is, I don't necessarily want to have to create a view that has to display both the "month" AND "month-YTD" values.

Does this all make sense? Is there a way within TM1 to tackle this issue? I do have access to the source data for the cube, so if there's something that I need to do back in the source data to achieve the desired result, that won't be a problem.

Thanks in advance!

Cheers,
Craig

[Martin Ryan]

Sounds to me like a feeder problem. Can you post the associated feeders?

My guess is that only the figure from the current period is feeding the calculated cell. So if that figure is zero, then the calc assumes the result will be zero. But of course there are potential historic values, so they need to feed into subsequent months.

If feeders are a new concept to you then you might like to take a look through the FAQ post (http://www.tm1forum.com/viewtopic.php?f=3&t=1332) and follow a few of the links in there.

HTH,
Martin

[craigparris1]

Hi Martin,

Thanks for the quick response. This is the only feeder I can see that relates to the "retained earnings" account:

'NPAT', '$'] =>
DB('ECA_GL',!Scenario,!Period,!Company,!BU,'9510','$');

I think I understand the general concept of feeders. Essentially, if TM1 determines that if particular cells are too sparse in their non-zero values, then it will just ignore those cells (assuming that the sparsity algorithim is being utilised for particular rules, eg. SKIPCHECK;). The feeders basically tell TM1 to always look through certain cells for values, even if they are sparse.

Perhaps there's some kind of feeder that needs to be added that incorporates all of the prior period values for "9510"? Of course, this should only include prior periods under the same level in the hierarchy. Is there some kind of function to return all prior periods at the same level as the current period?

Cheers,
Craig

[Martin Ryan]

Yep, you'll need a feeder like this one:
['9510', '$'] => DB(if(subst(!Period, 5,2)=12, '', 'ECA_GL'), !Scenario, DIMNM('Period', DIMIX('Period', !Period)+1), !Company, !BU, '9510', !Values_GL);

Note that it's generally not a good idea to use dimix to work out the next and previous elements. It's safer to use an attribute instead. But the above will solve your problem in the short term.

Martin

[craigparris1]

Hi Martin,

That appears to have done the trick. I'll get Finance to verify that the numbers look right, but it's certainly display what they expect for the "retained earnings" account where the business unit has stopped having txns posted to it.

Now, help me understand correctly what's going on with the feeder, if that's OK with you ..... Firstly, what value does !Period actually have, such that
SUBST(!Period,5,2) will return "12"? Is it the name of the period, or something else? I guess I'm imagining that !Period returns things like "Jan-10", "Nov-11", "Feb-12", etc. (which makes sense given the parameters of the SUBST function) - but if that was the case then the feeder would only actually work for months in 2012

I can see that overall the feeder is doing a lookup in the cube to actually find the next period (relative to the current one). So "12" is a month or a year? Obviously, if the SUBST function does return "12" then the IF statement is just returning an empty string, so the DB() lookup will essentially do nothing, yes? Otherwise it's returning the name of the "ECA_GL" cube, which is the current cube.

Cheers,
Craig

[Martin Ryan]

Now, help me understand correctly what's going on with the feeder, if that's OK with you ..... Firstly, what value does !Period actually have, such that
SUBST(!Period,5,2) will return "12"? Is it the name of the period, or something else? I guess I'm imagining that !Period returns things like "Jan-10", "Nov-11", "Feb-12", etc. (which makes sense given the parameters of the SUBST function) - but if that was the case then the feeder would only actually work for months in 2012

The ! ("bang") syntax on the right side of a rule/feeder means it applies to all the same elements that were on the left. So in this case because you didn't specify what Period the feeder applies to, it applies to all elements in the Period dimension. When it's evaluating, say, the feeder for '9510', 'Feb-11' (the left side), the right hand side of the feeder knows that !Period means 'Feb-11'.

The subst syntax works the same as mid does in Excel, so it's taking the two characters from position five.

You're right, the conditional nature of the feeder means that only the Periods that end in 12 will be fed. But this is reflecting my interpretation of your rule, which only applies if the Period ends in 1. Feeders should (generally) be the inverse of your rule. I assumed that that related to months, rather than years. I.e. Month 1 shouldn't have an opening balance. So Month 12 has no where to feed to. But if that's an incorrect assumption then you might want to take out the condition.

I can see that overall the feeder is doing a lookup in the cube to actually find the next period (relative to the current one).

It's an internal flag placement (rather than a lookup, it's placing data, not retrieving it) to ensure that everytime there's a value in 9510 it puts a little flag in the subsequent month to say that it needs to be calculated. But yes, it's find the next period, relative to the current value.

So "12" is a month or a year? Obviously, if the SUBST function does return "12" then the IF statement is just returning an empty string, so the DB() lookup will essentially do nothing, yes? Otherwise it's returning the name of the "ECA_GL" cube, which is the current cube.

I assumed 12 is a month. Yes, returning 12 will yield no cube to place the feeder, so it will go no where and do nothing. Otherwise it'll feed into the current cube. The reason you have to use that notation is because of the use of the DIMNM/DIMIX. This adds complexity so you can't use the basic syntax that you usually can when feeding and ruling internally. This is also why half of your rule uses the simple syntax - for retrieving the value for the current month - and the DB syntax for getting last month's value.

Hope that clears things up a bit. There's quite a lot of other useful stuff in the FAQ about rules and feeders.

Martin

[craigparris1]

Hi Martin,

OK, that was obviously just a misunderstanding. Our periods are all like Jan-03, Jun-07, Aug-11, Oct-12, etc. - a 3-letter month, then hyphen, then 2-digit year.

I'll modify the feeder so that it ignores any period starting with "Jun" (which is essentially what you thought you were telling me to do).

Thanks again,
Cheers,
Craig

[craigparris1]

Actually, I may just leave it for the moment, until Finance confirms whether it's right or not.

You're correct that the existing rule for actually calculating the "9510" amount (from "NPAT") is using SUBST(!Period, 5, 2) != "01", which must somehow indicate the first period. I guess I'll have to look in the dimension itself and see where that attribute comes from.