The free screen cast here on How To Calculate Sales Commissions using QuickBooks and MS Excel will go over this step by step so you can see exactly how this works!
If you want to learn all of the steps to creating custom fields and reports for use with my excel template so you can save hours calculating commissions and entering bills in QuickBooks then download my full length course on this right now:
In part one you saw How To Set Up Sales Reps in QuickBooks. This and the other customization described in that blog post are required to get things set up. Once you have that in place you have 90% of the battle won. There are only a few steps left in the process now.
- Run the sales rep list report in QuickBooks and export it to excel.
- Copy and paste the sales rep information into the sales rep table in the Excel template.
- Then run the Sales by Sales Rep report in QuickBooks.
- Customize the report so that the columns line up.
- Note the cells in the template are not locked so you can always remove columns if you choose not to use them.
- Be sure and save a backup of the original template just in case.
- Export the Sales By Sales Rep Report from QuickBooks to Excel.
- Copy and paste the sales rep names down so that they appear on each line.
- Get rid of all of the rows other than the ones that contain invoice information (ie totals and the line preceeding each Sales Rep’s section in the report with only their name on the row.
- Then copy and paste the information into the template, only pasting the values.
The free screen cast here and in the previous post leave out a lot of the customization because it is very detailed and too much to go over in a short free screen cast. So I have recorded a full 1 hour tutorial that walks you through each part of the set up including every custom filed and each of the reports – how to set all of this up. There is also a bonus section at the end that teaches you how to write the formulas in excel for those who want to learn.
Download the template (only) from my knowledge center $24.97