measure based on allocation key

[Anonymous]

Hi all,

I have below matrix that shows per company the cost per category. This is straightforward data for Company 0-8.

However, we have a dummy company, company9, where cost needs to be allocated based on hours (allocation key) from a different table. 

Example : we have in company 9 under category A a cost of 5000 that needs to be allocated based on S.2000. If we look to the allocation data, we see that S.2000 has a total of 1408 hours split between the companies. Company 1 should of the total cost of 5000 get 8/1408, company should get 100/1408 etc..

Final visual/matrix would show per company the direct cost + the allocated cost from company 9.

I have however no clue on how to achieve this, so I was hoping somebody could help me 😊

Below the data that is a sample of the data :

Cost Table

Company	Category	Cost object	Cost
Company6	A	d.1500	400
Company5	D	c.1500	3000
Company10	C	b.1000	5000
Company9	T	s.1500	9000
Company7	W	s.1500	400
Company9	E	s.1500	3000
Company10	T	b.1000	5000
Company9	W	s.1500	9000
Company9	Q	s.1500	3000
Company9	A	s.2000	5000
Company8	R	b.1000	3000
Company9	T	s.2000	5000
Company1	Y	d.1500	9000
Company9	U	s.2000	3000
Company4	H	b.1000	5000
Company0	H	i.1000	400
Company2	H	s.1500	3000
Company6	B	c.1500	3000
Company4	N	b.1000	2000
Company2	F	i.1000	10000

Allocation Table

Company	Cost object	Hours	Receiving Company
Company9	s.1500	500	Company2
Company9	s.2000	400	Company4
Company9	s.1500	600	Company 6
Company9	s.2000	400	Company3
Company9	s.1500	300	Company1
Company9	s.2000	100	Company5
Company9	s.1500	100	Company3
Company9	s.2000	8	Company1
Company9	s.1500	9	Company5
Company9	s.2000	100	Company2
Company9	s.1500	100	Company4
Company9	s.2000	400	Company4

[amustafa]

See the sample file and the desired results in my shared drive.

Only relationship you have between the two tables are Compnay and Cost Object.

Cost Allocation

[Anonymous]

hi @amustafa 

thanks for your help, but not really the desired outcome.

Desired outcome is to allocate costs booked on company 9 to all the other companies based on the allocation key. 

So for example we have category A booked on company 9 for an amount of 5000 that needs to be allocated based on the linked allocation key, for category that is S.2000.
S.2000 allocation is split between 4 companies and the key is as followed between company 1,2,3 and 4 (8/100/400/800). That 5000 should be linked to those companies as below :

[Anonymous]

Hi @amustafa 

Example and desired result (example is just for company1), but reasoning should be applied for all companies.

[amustafa]

Hi @Anonymous , can you provide the expected outcome from your sample data? provide clear example on how to calculate the values.