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

[gkrishna788]

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 Allocation Sample.xlsx
	A	B	C	D	E	F	G
1	Budget Available	100
2	Remaining Budget	100
3	Remaining Budget in %	0%
4
5	Min hike%	3%
6	Max hike%	15%
7
8	Employee Name	Salary	Propsed Hike%	Proposed Hike Amount	Final Merit Hike Amount	Final Merit Hike%
9	A	300	12%	36	43	14%
10	B	200	8%	16	19	10%
11	C	80	15%	12	14	18%
12	D	130	12%	15.6	19	14%
13	E	150	3%	4.5	5	4%
14	F	180	0%	0	0	0%
15		1040		84.1	100	10%
16
Sheet1

Cell Formulas
Range	Formula
B2	B2	=E15
B3	B3	=B1-B2
D9:D14	D9	=B9*C9
E9:E14	E9	=((D9/$D$15)*$B$1)
F9:F14	F9	=E9/B9
D15:E15,B15	D15	=SUM(D9:D14)
F15	F15	=AVERAGE(F9:F14)

 

[maabadi]

Welcome to MrExcel Message Board.
With this Example , Because you have 5 active workers, you can set for all Maximum 15% and You use Totally 75%.