Calendar Year to Financial Year Consolidations

[tonyw]

Hi all,

Our company has recently been acquired, and we are being asked to switch financial reporting to financial years from calendar years in line with our new parent company.

I'm having trouble trying to figure out how I can create YTD consolidations where the weeks and months I want to include have more than one calendar year. We upload data using dimensions for calendar week, calendar month, and year.

The only possible solutions I can come up with at the moment are:
1) Consolidate the calendar year to date, and deduct a Q1 consolidation to give Financial YTD (until the end of December)
2) Copy all data on a weekly and monthly basis into a new Financial Year dimension

My knowledge of TM1 is limited (as you may have guessed!), so I've been furiously searching online forums and the IBM guide, but to no avail...
Would it be possible to have a rule where I tell TM1 exactly which months and weeks to pick up to consolidate?

I'd really appreciate any help or guidance you can provide. Thanks very much.

Tony

[tomok]

Unfortunately, when you split your time dimensions like this it makes your problem a lot more vexing. That's why best practice is to have a single time dimension. If you had a single time dimension it would be easy to accomplish with alternate hierarchies. Having said that, what I would do is create two hierarchies in your Month dimension, where you roll the months along calendar and fiscal YTD's. In the Year dimension i would create a rollup for each fiscal year, since it is actually going to contain two calendar years in it. Then I would create a 3D lookup cube (if that's what you want to call it) with Month and Year and then a new dimension called FiscalYear. Then for each element in the FiscalYear dimension, place a value of 1 in the lookup cube where that month and calendar year should be included in the fiscal year. Then in your Measures dimension for the original cube, have a new measure called FiscalAmount, which would be calculated via a rule by taking Amount times the lookup value (if the period is in the fiscal year then it will be amount x 1 and if not it will be amount X 0). All you'll need to do is make sure you feed the rule and you'll be good.

[jim wood]

That's why best practice is to have a single time dimension.

Tomok, apologies for saying this but you're talking out of your posterior. It's not best practice at all. It can be ideal depending on your circumstances. There have been many debates regarding a single time dimension or not and there never has been a clear winner. There are strength and weaknesses either way.

As for sorting this issue if we presume you have one period dimension and one year dimension, export the data as it stands to flat file. (I mean all of it.) Create another year dimension containing your current calendar years. Create another single (string) element dimension. Using the 2 new dimensions and your period dimension create a mapping cube. (single element dimension last.) Then insert for all calendar years and periods which financial year they belong to. Then go in to your current year dimension in your main cube and delete all the calendar years. Then insert all your financial years and save. In doing so all your data will go. Then via TI load your flat file back in using your mapping file to insert the data in to the right year,

Jim.

[tomok]

As for sorting this issue if we presume you have one period dimension and one year dimension, export the data as it stands to flat file. (I mean all of it.) Create another year dimension containing your current calendar years. Create another single (string) element dimension. Using the 2 new dimensions and your period dimension create a mapping cube. (single element dimension last.) Then insert for all calendar years and periods which financial year they belong to. Then go in to your current year dimension in your main cube and delete all the calendar years. Then insert all your financial years and save. In doing so all your data will go. Then via TI load your flat file back in using your mapping file to insert the data in to the right year

An interesting approach but an all or nothing type thing. I'm pretty sure the OP wants to maintain BOTH looks in the cube, both calendar and fiscal year. The lookup and rule calculation approach i suggested will give him both.

[jim wood]

Good point. Our friend has options now which is never a bad thing.

[lotsaram]

Hi Tony,

Jim and Tom can duke it out over time dimension best practices. I think for a GL cube that separate year and month dimensions would be the accepted norm and most useful EXCEPT in the specific instance where you had a requirement to report both calendar year and non Jan->Dec financial year from the same cube in which case a combined year-month dimension would be the way to go. (Which I think is what Tom was getting at.)

If you are in the situation of having a cube with a combined year-month dimension then all you need is new hierarchies, this would be fortunate for you but probably unlikely. It is still possible to do everything from within a single cube but you would need to either create additional financial years like "2012-13" or additional value measures and either populate them with rules or TI form the existing data. This would work but might be confusing. Or you could create a separate cube as per Jim's suggestion and populate via either rules or TI. Either way you will find creating YTG (year-to-go) rollups in the month dimension in addition to the YTD rollups that you probably already have invaluable.

Also if you are inexperienced with TM1 and there is no one within your company with significant TM1 knowledge then it would be a good investment and much less risky to bring in some external help for something like this. As for someone who knows what they are doing this would be a relatively quick and easy thing to do and you will get an immediate result, whereas it could be quite a challenge for a novice.

[Steve Rowe]

Hi,
Just one more option that may or may not be practical depending on the complexity of your model etc etc.

In a 2d system for which you needed to move from the Tax year to the Calendar year and do both styles of reporting you could add three periods to your period dimension.
So you start with A, M, J, J, A, S, O, N, D, J, F, M

You end up with
J (CalYr) , F (CalYr) , M (CalYr), A, M, J, J, A, S, O, N, D, J, F, M

You can now create all the consolidations you need in your month dimension.

You then write a rule that links the periods that are the same but appear in different years.

['J (CalYr)']= N: DB (Cube , !xyz, Attrs('Year' , !Year, 'Prior') , !abc , 'Jan');

Plus the feeder should be like this

['J ']=> N: DB (Cube , !xyz, Attrs('Year' , !Year, 'Next') , !abc , 'Jan (CalYr)');

This should be straight forward to implement, can't tell if it is practical in your case since it will depend on your complexity and if your users can cope.

Cheers,