There are two parts to this series so check part 2, because that’s where you actually get to see how to calculate the sales commissions using QuickBooks and excel.
There are several things that need to be done here in order to make this work. QuickBooks is a database with many related data tables. The sooner we understand this and how this works, the easier it becomes to see how to get those working parts to come together to create the things we need in order to simplify our lives using these powerful tools!
The first step towards calculating the sales commissions is being able to track your sales by sales rep. Tracking sales by sales rep in QuickBooks is very easy to do and that is the focus of this first part. QuickBooks has a list called “Sales Reps”. All you have to do is access that list, and create a new sales rep. It’s done in about 2 clicks. Once you click to create a new sales rep in the sales rep list you are asked to choose whom that sales rep is. The drop down gives you access to all of your employees and vendors. Simply choose one and QuickBooks fills in their initials for you. You can change this if you like.
Once your sales reps are set up you can add the Sales Reps Field to your invoice template. Then you can begin assigning sales reps to an invoice, and that is how you get the sale assigned to the sales rep. The entire invoice effectively gets assigned, which means every line item in that invoice will be included in that sales rep’s sales. With an added custom “item” field you can provide the infrastructure for indicating if a line item on that invoice should not be used in calculating sales commissions in QuickBooks.
You also want to create a Sales rep list with a custom Vendor field that is used to keep track of the sales rep’s commission rate. This cannot be used to calculate anything in QuickBooks, but as you will see in tomorrow’s screen cast it can be included on a report, exported to excel and then looked up and used to calculate the actual commission. The problem with running a vendor list report to get at this information is that it will include all vendors, and there is no filter available for “Sales Reps” in the “List” reports. So you have to set up a Vendor Type called “Sales Reps, then create a Vendor Contact List filtered for the “Sales Reps” vendor type.
The free screen cast here simply shows you how to set up the sales rep. Tomorrow’s screen cast will show you how to run a report in QuickBooks to show sales by sales rep and use that to populate my excel template so that the calculation is done for you in a second. There isn’t enough time to go into the details of how to set up all of the custom fields, bring them to the reports and customize everything.
If you are interested in seeing how all of this is done in a 1 hour, step by step video tutorial which includes the excel template, please purchase the class in my Knowledge Center.
If you think you know how to set everything up in QuickBooks and just want to download the template you can purchase the template only here.
Please enjoy the free screen cast on How to Set Up Sales Reps in QuickBooks:
Watch How To Calculate Sales Commissions Part 2 right here:
Download the template from my knowledge center $24.97
Download the Full 1 hour Video and Template for $64.97
Or you could try this app from Intuit: http://workplace.intuit.com/appcenter/moreInfo.aspx?AppID=3266
This app from intuit will cost minimum $10/month, up to $40/month depending on how many users you need. The excel template in my knowledge center is a one time $25 cost. If you need the full tutorial, $85 one time all inclusive.
Try Nirvaha’s oneclick sales commission.
With just one click, it will calculate and generate statements. It will send them out and have them in the repository. Awesome product. I am taking the free trial right now.
Does it integrate seemlessly with QuickBooks?
Can I calculate commissions based on Gross profit with the method shown here ?
The sales reps in QuickBooks are assigned to customers and invoices so there is really no way to do what you are asking directly. What you can do if everything is job costed properly in QuickBooks is run a P&L By job and use the gross profit from that to figure the commissions but that would be a manual process.
Great Videos.
I was wondering if there is a way to setup a sales rep with different Commission rates? Reason is, my company pays 15% for products A, B & C….but for our Large products we pay 10% for sales. How do I set this up?
I would be to set up a custom field for the items to show the commission rate for that ITEM, then include it in a non-printing column on the invoice template such that you could run transaction detail reports on sales and include the column there so you can export to excel and run the calc.
Seth,
My business frequently gives employees commissions on sales and I was hoping you could answer this question. When I create an invoice for a customer is it possible to have a 5% commission payable immediately generate after entering the invoice?
If your video explains this let be know so I can purchase it. Thanks.
Hey Jim! Unfortunately there is no way to have QuickBooks calculate it, but of course you can calculate it your self the minute you post the invoice. My video shows you how to set up custom fields and reports in QuickBooks such that you can quickly and easily calculate the commissions using a spreadsheet template which I created and is also included with the video.