Consolidation advice

Post Reply
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Consolidation advice

Post by Mark RMBC »

Hi all,

I have a requirement to provide how a budget is resourced/funded at cost centre, project, employee and job level.

The fundings are for example, Revenue, Traded, Grant, Schools

I have created a funding cube that holds the information at this level

The problem I am struggling with is how to consolidate and aggregate this to ask questions such as how is Cost Centre X012345 funding broken down across all employees, jobs and projects.

I have come up with a method but it seems a bit long winded and was hoping someone can provide a better method.

The method I have come up with is as follows,
  • Create a Count element in the Funding dimension, this is derived via a N level rule which says if All_Fundings >0,1,0
  • I have then created another element for each funding type which holds the percentage of the fund type in question to the count in question. So for example, element Revenue_pc. This is dervided via a N and C level rule, so Revenue_pc = Revenue / Count
Please see attached for more information:
Consolidation Issue.docx
(19.43 KiB) Downloaded 144 times
cheers, Mark
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Consolidation advice

Post by jim wood »

This sounds more like an allocation rather than a consolidation. Like all allocations you to agree a method with those in finance. For costs you normally allocate the likes employee costs via FTE.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Consolidation advice

Post by Mark RMBC »

Hi Jim,

the issue isn't around actually allocating the funding, this will be done via users manually updating combined with some processes.

the issue is how to report off this date so how do the allocations look at a consolidated level, so questions like what is the funding split for Service area Z

Answer Service Area Z is split 80% Revenue 20% Grant

cheers, Mark
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Consolidation advice

Post by jim wood »

May be add a measure called % split and calculate at the consolidated level?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Post Reply