Sales Quota distribution factoring in % of phone hours

[silentbuddha]

Hi,

I am having difficutly building a formula to distribute overall sales quotas to employees base on each employees expected phone hours.

Total Sales Target : 1000
Total Employees : 15
Total Phone Hours for Dept : 2000
Employee 1 : 161 hours (8.05%)
Employee 2 : 123 hours (6.15%)
Employee 3 : 14 hours (0.7%)
Employee 4, 5 , 6...15

I can't seem to get around dealing with he having fractional sales ?

Thanks,
silent

 

[oldbrewer]

round them all up so your sales target might become 1027 - does this really matter ?

by the way, targets ALWAYS increase costs to a business because people strive to meet their own targets even if doing so adversely affects the overall business

you either get that or you don't, but either way it is true

 

[silentbuddha]

hi oldbrewer, unfortuantely..it does matter. since the department has 15 employees my targets are always a multiple of 15 but my dilemma is how to factor in that each employee may not work the same hours on the phone. As well, employee satisfaction is a key value.

Total Sales Target : 960
Total Employees : 15
Total Phone Hours for Dept : 2161
I have 9 ppl working 161 hours
I have 1 ppl 133
I have 1 ppl 159
I have 1 pp 154
I have 2 ppl 126
I have 1 ppl 14

is there a formula that could spread the quota based on the above information I have provided.

Thank you for your patience,
Silent

 

[oldbrewer]

Total Sales Target : 960
Total Employees : 15
Total Phone Hours for Dept : 2161						target per person
I have 9 ppl working 161 hours			9	161	1449	71.52244331
I have 1 ppl 133			1	133	133	59.08375752
I have 1 ppl 159			1	159	159	70.63396576
I have 1 pp 154			1	154	154	68.41277186
I have 2 ppl 126			2	126	252	55.97408607
I have 1 ppl 14			1	14	14	6.219342897
					2161
				2161	hours
				960	target
				0.444239	target per hour
								how u round them is up to u

<colgroup><col><col span="5"><col><col span="2"></colgroup><tbody>
</tbody>

 

[silentbuddha]

h oldbewer, would you know how i could incorporate the distribution of the factional sales ?

 

[oldbrewer]

do you mean you want a chart of individual's targets ?

round up the people working most hours and round down those working least