# Calculating commission in Excel

Discussion in 'Business Applications' started by pol17, Apr 3, 2008.

Not open for further replies.

Joined:
Apr 3, 2008
Messages:
2
Hi - I would like to create an Excel worksheet which can calculate monthly commission for my sales team (11 agents) in a fair way reflecting the calls they have taken, hours worked and individual sales while also incorporating the performance of the department . Currently individual performance is not used and the commission is 1 pence per item for all sales above 70% of the monthly target and 2 pence per item above 100% of the monthly target. For example, if we sold 55,000 items with a month target of 50,000 each agent would receive £250 commission (15,000 x 1 pence & 5000 x 2 pence). With this example an agent could have sold 12,000 items, worked 100 hours and taken 7 calls per hour while another agent could have sold 6,000 items, worked 80 hours and taken 4 calls per hour.

Does anyone have any ideas as to a way to calculate commission in a fair way using these criteria?? Any help would be appreciated. Thanks

Joined:
Oct 20, 2004
Messages:
7,837
4. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Generally commissions are based on the value of the item(s) sold, so what you are trying to do sounds like a mix of an hourly wage (number of hours worked, number of calls taken) as well as a commission. Your system may be fair in some ways, but what about a person who gets more/larger sales while putting in fewer hours? It would seem to penalize your best salesmen. Perhaps you need to figure out the focus of your payment method.

5. ### MRdNk

Joined:
Apr 7, 2007
Messages:
439
This is a bit of an odd request. If you need help setting up a spreadsheet that's something this forum is for, calculating your sales teams commission / incentive basis, I would suggest isn't really in the scope of these forums.

However, I will make a suggestion, you could set your various criteria against a percentage, and work on that basis.

Although, I do have to point out that the most obvious solution for a SALES team, and based on the current commission, and most obvious solution seems to be to divide it by sales made as a percentage of the total sale, and total team commission.

The only "fair way" is to make sure that everyone knows how the bonus is calculated; and remember 'Life isn't fair' and that you're in sales.

End of the day its up to you to decide who deserves more money, the person who gets the most sales, puts the most time in, or takes the most calls! Seems pretty obvious to me - although I don't know the whole story or even the business you're in.

Joined:
Apr 3, 2008
Messages:
2
Thanks, both. I've had a look at the Excel scenarios page and it may be the best solution but, to be honest, I'm not sure how to create one as it seems quite complex. The 'items' are tickets so the individual sales can depend on who has taken the most calls and also who has worked the hardest in selling tickets on calls and to corporate clients. There is certainly a little luck involved (an individual could take a call for 200 or zero tickets or sell 1,000 tickets to a personal client with little effort) but generally the tickets per hour and calls per hour statistics are a good indicator of performance over the month which is why I want to incorporate these into the final commission. I am looking for help with setting up a spreadsheet for this scheme rather than help with working out the best way to give commission so I hope this is the right forum.

One way would be to work out the department 'pot' of monthly commission using the 70%/100% commission scheme mentioned earlier then divide it up, as was said, between agents based on their % of the sales (eg 12% of the pot for 12% of the sales). However, I'd like to mix in another variable looking at the calls per hour and hours worked which, I think, will make this as fair as possible.

But I am not a maths genius so I don't know if this is possible!

As Seen On