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.
Download the E-book that walks you through the full set up step by step: