Assign costs to higher levels

[Toto]

Hello,

I want to build a planning cube with following dimensions:

- time
- business units( with employees in it consolidated to business centers consolidated to business units)
- chart of accounts
- measures

Now I want to build an excel-sheet where the user (business center manager) can input the costs for his/her employees. Some costs should not be on the employee level but on the business unit level (e.g. rental costs etc). How would you realize that?
I thought of letting the user input the data and afterwards letting a TI-process run that breaks down and assigns the costs per employee. Maybe there is a better way?!

Thanks a lot for sharing your experience with me!

Regards,

Toto

[declanr]

Toto,

Have you considered the right-click data spreading techniques?
There are a few different ways that data can be spread to the nodes below a C level cell

Cheers,
DR

[tomok]

Declanr's solution is one way. I personally don't like that because it will assign costs to employees that really don't make sense. If you were doing an employee profitability model it might make sense but that doesn't appear to be the case. In these situations I just create a leaf-level shadow element at the business-unit level, that rolls to the business unit, just like the employees. The total for the business unit will be correct and there will be no "meaningless" data at the employee level.

[job67]

Hopefully I've understood the question correctly, but coud you not insert notional n level elements for each consolidation.

Σ Business Unit
╠ Σ Business Centre1
║ ╠ n Employee 1
║ ╠ n Employee 2
║ ╚ n N_Business Centre1
╠ Σ Business Centre2
║ ╠ n Employee 3
║ ╠ n Employee 4
║ ╚ n N_Business Centre2
╚ n N_ Business Unit

Then you can post at employee, business centre or Business unit level depending on what suits.

[Steve Rowe]

It might be better to split your model into multiple cubes.

Cube 1. Employee costs
Cube 2. BU Costs
Cube 3. Associate Employees with BUs, i.e. Emp 1 works for BU1 20% and BU2 80%
Cube 4. Costs by BU, multiply Cube 1 by Cube 2 and add in Cube 3.

Something like that?

Obviously very summarised but should be much more flexible than your design. For example what happens when an employee moves BUs part the way through a year?

HTH

[Toto]

Thanks a lot for all your answers.
Steve, I really would like to dive in your approach, but I did not understand everything.

Cube 1. Employee costs
Cube 2. BU Costs
Cube 3. Associate Employees with BUs, i.e. Emp 1 works for BU1 20% and BU2 80%
Cube 4. Costs by BU, multiply Cube 1 by Cube 2 and add in Cube 3.

Cube 1. Ok
Cube 2. Ok
Cube 3. Ok
They do all have a time dimension, in order to show if an employee changes the unit during the year correct?


I did not really understand the concept of cube 4:

E.g. I have the costs for a particular employee of 20.000 € in cube 1 and costs for a particular Unit of 135.000 € in the second cube.
The third cube says that the employee works for 45 % for the Unit mentioned above, what exactly does the fourth cube do and how?

Thanks a lot for your patience,

Toto

[Steve Rowe]

Hi Toto,
Everything is dimensioned by time.
Cube 4 combines the information in the other cubes to give you the results you need.

So Cube 4 is dimensioned by BU and Employee number. Employee number has a "No Employee Detail" account.

Your COA dim has BU Cost and Employee Cost and your rules for same are something like this.

#BU Cost, pulls in the BU Cost from Cube 2, we don't specify BU code on the LHS since we want this var to float with the ! reference. We specify No Employee Detail so that we don't get the BU Cost on every employee

['BU Cost', 'No Employee Detail']=N: DB ('Cube 2' , !Periods , !BUCode);

#Employee Cost, multiply Cube 3 by Cube 1
['Emp Cost']=N:DB ('Cube 1' , !Periods, !EmpNo, 'Emp Cost') * DB ('Cube 3' , !Periods , !EmpNo , !BUCode, 'Percent Employed');

HTH a bit more.

Cheers