Searching for a new sales commission solution

Topic

New Reply

Hello,

This is my first time on the site, it looks pretty promising.

Here is my issue-

I manage anywhere between 14-19 independent sales reps on a monthly basis. I track the commissioned sales in a spreadsheet. When the end of the month nears, I have one huge master sheet which I have to copy and paste out of to make individual statements. This process usually takes me between 3-5 (looooong) hours.

Does anyone know of a function or tool in excel that I am missing which can make this process go faster?

Reply | Quote

Replies

Hello
Your question is a starter for many more questions.
What is recorded in s/sheet?
How is the s/sheet structured?
What do you want to show in the month end statement?
How are the commissions calculate? Flat or variable stepped rates?

Tools
Filters
Sub Totals
Pivot tables
Scenarios
Custom Views

Functions
dsum()
sumif()

Sorry but the list goes on and on.

Search Microsoft for templates and idea starters.

eg
http://office.microsoft.com/en-us/templates/sales-commission-calculator-TC001055690.aspx

Cheers
G

Reply | Quote

Are you using filters to get the data you want, or copying and pasting line by line?

Reply | Quote

My fault for not being more clear to begin with. The basic layout of my sheet is as follows –

Columns = date, customer, invoice, revenue, salesperson, rate, amount

On another sheet I have a table which determines the rate. The commission rate is based on total revenue. The table has different values and the associated rate. I use a VLOOKUP to pull this over.

Once all the data for the month is in, I filter the results by salesperson and then copy/paste to another sheet where I sum it and print it. I know a little bit about Pivots but when I played around with them I couldn’t seem to get what I wanted.

G, what did you mean by custom views?

Reply | Quote

Could you attach a sample file (no personal or proprietary info) of what you have and also a sheet of what you want at the end. It sounds like Data – subtotals may work with page break between groups or a pivot…

Steve

Reply | Quote

Excel could be a solution for sales commission plans, if other options are not available. There are a lot of issues with using Excel for sales commissions. Problems include copy/paste errors, wrong formulas, multiple commission spreadsheets for reps, no auditability, double entry of data, sales rep complaints, etc.

If you search for “sales commission software” in Google you can find many software solutions for this purpose. Here are a couple of solutions to look at QCommission and Easy-Commission.
GM

Reply | Quote

Just a suggestion

Why not build an individual sheet for each sales rep and have them all roll up into the master sheet.

eg build a page for John Smith, copy it and replace with Mary Jones info and so on until you have all 19 reps. Make one more copy and use it as your master (unless your master needs to show each rep separately).

in the cells of you master have formulas like =+A2 JSmith +A2 MJones +A2 M Monroe etc
or if you need them to show on separate lines then link each line on the master to the specific line on the rep page
only enter the data once into the individual page.
The formula will pull the totals together on your master sheet
This way you can print the individuals sheet each month and still have you master sheet.

If your master sheet is like a database eg headings across the top of each column and rows of data,
then perhaps you could use something like Crystal Reports – once you design the report, you just give it the parameters you want,eg John Smith, run it, print it, then change to Mary Jones, run & print etc
Once the report is designed should only take about 10 to 15 minutes to run all 19 reports.

Reply | Quote

Capri- the problem with that is that I need more than the sums of each rep. I need to have it on one sheet so I can create charts/reports/analysis. And it also create an additional step (finding sheet) when inputting all of the items.

G- I ended up finding the office.com templates (before reading your post- thanks though) and ended up reworking my template based on one I liked there.

GM- Thats for the advice. After a bit of research I narrowed down two services-QCommissions and Nirvaha. Since price was a deciding factor I went went Nirvaha’s OneClick Commissions to create Excel Statements.

Thanks to all for the help. I just finished my reporting earlier this week and it went muuuuch better.

Good luck to all.

Reply | Quote