# Sales Quota distribution factoring in % of phone hours

#### silentbuddha

##### Board Regular
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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

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.

Silent

 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>

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

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

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

Replies
3
Views
1K

1,202,914
Messages
6,052,534
Members
444,590
Latest member
GCLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back