excel to help divide company profit into tiers with caps

[sarap987]

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!

[#2]

@sarap987 Please see if the attached file does what you have in mind.

[#3]

@sarap987 

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.

[#4]

@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 

[#5]

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!

[#6]

@sarap987 

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?

[#7]

Hey! thats right each person in the tier will get that same max cap. i.e. if there are 4 people in 500 cap, each will get 500 (assuming there are enough funds in the pool...)

[#8]

hi there could you kindly help with this request? thank you

[#9]

@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).