Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Calculating commission in Excel


(!)

pol17's Avatar
pol17 pol17 is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Apr 2008
03-Apr-2008, 08:23 AM #1
Calculating commission in Excel
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
slurpee55's Avatar
slurpee55 slurpee55 is offline
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
03-Apr-2008, 12:04 PM #2
You might want to take a look at this page
http://office.microsoft.com/en-us/ex...119871033.aspx
__________________
Iowa? I could have sworn this was heaven.
Well, I think I can answer this question most successfully in mime.
My theme song... | Affero - rate me!
slurpee55's Avatar
slurpee55 slurpee55 is offline
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
03-Apr-2008, 12:09 PM #3
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.
__________________
Iowa? I could have sworn this was heaven.
Well, I think I can answer this question most successfully in mime.
My theme song... | Affero - rate me!
MRdNk's Avatar
MRdNk MRdNk is offline
Computer Specs
Member with 439 posts.
 
Join Date: Apr 2007
Location: UK
Experience: Damn I'm good!
03-Apr-2008, 12:22 PM #4
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.
__________________
MRdNk
-----------
Web App Developer: .NET, JavaScript and SQL
pol17's Avatar
pol17 pol17 is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Apr 2008
04-Apr-2008, 06:57 AM #5
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

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Forum Jump

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑