Help-Allocation of Shared Service Spend across multiple dimensions

[schenkelpat]

Hi All,

I think this is probably an easy question; but, i am having a serious brain block.  I have a shared service spend bucket that I need to allocate across multiple businesses by month. I am trying to turn this:

Group	Year	Month	Opex
Shared Service	2019	January	277
Business1	2019	January	305
Business2	2019	January	132
Business3	2019	January	424
Shared Service	2019	February	135
Business1	2019	February	337
Business2	2019	February	420
Business3	2019	February	462

into this:

Group	Year	Month	Opex	Shared Service Spend
Shared Service	2019	January
Business1	2019	January	305	27.7
Business2	2019	January	132	83.1
Business3	2019	January	424	166.2
Shared Service	2019	February
Business1	2019	February	337	13.5
Business2	2019	February	420	40.5
Business3	2019	February	462	81

.

I used an allocation file that takes the shared service spend and allocates by Group, year, month into the appropriate buckets based on these allocations.

Business Group	Allocation of Shared Service
Business1	10%
Business2	30%
Business3	60%

I've attached the data in this workbook.  

Thanks,
Pat

[MichalM · Accepted solution]

@schenkelpat 

See whether this works

• You split the data into two streams - shared services and the rest
• Append the shared services budget by Year and Month
• Append allocation %
• Generate Shared Services Spend variable 
• Sort the data

Example attached

[schenkelpat]

Thank you @MichalM!  This worked!