Forum Discussion
excel to help divide company profit into tiers with caps
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!
8 Replies
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.
- Thanks for this. i see what you have dont though i am not sure why it cannot work without removing the cap of the highest level. none the less, would your template work for the situation i described in my reply to Riny-van-Eekelen below also? thank you!
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?
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
- hi there could you kindly help with this request? thank you
