Distribute budget proportionally to all the employees with min and max condition

I have got budget which needs to be proportionally allocated to the employees, initially we will try to allocate the budget as per the proposed merit increase%, if the budget is left over, then we will try to adjust the 'final merit hike%', if we allocate the budget as per as shown in 'final merit hike%' column then we can utilize complete budget but another condition we need to consider is, merit hike% should be within 3%-15% range and we should make sure we are not over utilize or under utilize the given budget.
I need your help in fixing the formula for 'Final merit hike%' to fit within 3% to 15% range by making sure we are utilizing the full budget.

Please note that, in the below example proposed hike% for employee name "F" is 0%, it's completely fine as this employee is considered as poor performer, hence we proposed 0% hike in the first place itself. However for the rest of the employees we can make adjustments to the final merit hike% but it should fall within the range 3% to 15% and the budget should not be over utilized or under utilized.

Please look at the following example and let me know, if anything is unclear, looking forward for your help.

Budget.JPG

Hello gkrishna788,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.