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]

[TABLE="width: 827"]
<colgroup><col><col span="5"><col><col span="2"></colgroup><tbody>[TR]
[TD]Total Sales Target : 960[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Employees : 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Phone Hours for Dept : 2161[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]target per person[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have 9 ppl working 161 hours[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]1449[/TD]
[TD="align: right"]71.52244331[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have 1 ppl 133[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]59.08375752[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have 1 ppl 159[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]70.63396576[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have 1 pp 154[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]68.41277186[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have 2 ppl 126[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]55.97408607[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have 1 ppl 14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]6.219342897[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2161[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2161[/TD]
[TD]hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]960[/TD]
[TD]target[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.444239[/TD]
[TD="colspan: 2"]target per hour[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]how u round them is up to u[/TD]
[/TR]
</tbody>[/TABLE]

 

[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