Aug 29 2022 03:33 AM
hi there i need to understand how best to use excel to divide a pool of money into different tiers with caps. once the cap of 1 tier has been reached then the remaining pool would need to be split equally into the remaining tiers. once the second is reached, the remaining needs to be split into the remaining tiers and so on and so on until all the pool has been used.
this pool could be smaller than the smallest cap meaning that all tiers would get an equal bonus, or it could be large enough for certain tiers to reach their cap but for the rest of the tiers to get an equal bonus but higher than the caps that have been reached. there are many scenarios depending on the pool in question.
To make a simplistic example, imagine we have 6 tiers and the caps are as per below. if we had a pool of 1000Euro this will be split evenly between the 6 tiers ie. 166.66Euro.
if we had a pool of 10000 euro, then first 3k will be split evenly across 5 levels i.e. all get 500 euro and 1st level will reach cap. the next 1250 Euro will be split into 5 levels so next 5 levels get 250 euro and 2nd level reaches its cap (750euro). next 1000 Euro will be split between 4 levels, adding 250 euro to each level and helping tier 3 reach cap (1000 euro). the remaining 4750 will be split by 3 equally. in this scenario first 3 levels reached cap, and level 3-6 have an equal amount of pool.
6th 15000
5th 10000
4th 5000
3rd 1000
2nd 750
1st 500
id like to apply this 'rule' to any amount of pool that is available for splitting. be it 10 euro to 1 million euro.
thank you for helping me figure this one out!
Aug 29 2022 03:51 AM
Aug 29 2022 03:53 AM
This way, you cannot distribute amounts over 15000+10000+5000+1000+750+500 = 32250 euro, unless you remove the cap of the highest level.
See the attached demo.
Aug 29 2022 06:00 AM
@Riny_van_Eekelen thanks for your reply, i think the excel you provided is great.
i was using the example as a simplistic scenario where 1 person 'lived' in each tier - in reality though there would be X amount of people per tier and this would be changing monthly.
for e.g. 40 people in tier 6 (500Cap), 15 in tier 5, 8 in tier 4, 4 in tier 3, 7 in tier 2 and 1 in tier 1.
Can you create the same excel with a possibility to change the no of people in these tiers as the organisation changes and yet the distribution to be allocated fairly as you very well depicted. thank you
Aug 29 2022 06:04 AM
Aug 29 2022 07:20 AM
If we 'respect' each cap, the total amount cannot be higher than 500 (tier 1) + 750 (tier 2) + 1000 (tier 3) + 5000 (tier 4) + 10000 (tier 5) + 15000 (tier 6) = 32250.
If there are 40 persons in the 500 tier, does that mean each of the 40 can get at most 500, or do the 40 combined get a total of at most 500?
Aug 30 2022 12:32 AM
Sep 01 2022 02:05 AM
Sep 01 2022 03:00 AM
@sarap987 Perhaps you can explain a bit more with a manual calculation example. Let's say we have 80,000 to distribute, How much would every individual in each level receive? Use the same number of people in each group as mentioned in one of your earlier responses (40, 15, 8, 4, 2 and 1).