Allocate costs on a monthly basis

[Anonymous]

Hi all,

I tried looking for an answer myself (or here), but I just can't get it to work.

Hopefully the answer is understandeable for a non-IT guy (finance background)... 🙂

So the problem: 

We have about 100 employees working on our payroll and we want to allocate their individual cost to 4 different business units.

It's possible that person A works from januari - march on business unit 1 and from then on business unit 2.

So basically, we have 1 large table as follows:

We are updating this table as soon as there is a change in the budget/forecast where this person will work.

I already unpivoted that table to the format below:

Next to that, I have a datatable with the same employee numbers and their cost per month (below dummy values).

So the end result would have to be that I know, for each business unit, what my payroll cost was in each month.

Every month, the new actual payroll cost will be added to the model.

In the above example, the cost of employee 145 should be added in business unit TNT in January and as of February it should be allocated to FTTX. 

I hope my problem makes sense?
What is the best practice to solve this, as I don't believe I'm the only one with this request...

Thanks a lot for helping me out!

[Greg_Deckler]

You are going to want to unpivot your month columns and then it should be fairly straight-forward.

[Anonymous]

Please enlighten me 🙂

[Anonymous]

Thanks for the reply!

However, it's not really clear how it should be straightforward then...
Could you please provide me with a bit more info? Thanks!