Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching for a new sales commission solution

    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?

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    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/te...001055690.aspx

    Cheers
    G
    Last edited by geofrichardson; 2011-03-24 at 18:39. Reason: corrected URL

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are you using filters to get the data you want, or copying and pasting line by line?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  6. #6
    New Lounger
    Join Date
    Mar 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  8. #8
    New Lounger
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem Solved!

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •