Forum Discussion
measure based on allocation key
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 |
4 Replies
Hi Anonymous , can you provide the expected outcome from your sample data? provide clear example on how to calculate the values.
Hi amustafa
Example and desired result (example is just for company1), but reasoning should be applied for all companies.
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.
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 :
