The most voted on QuickBooks Online (QBO) feature request under the Customer Center category is Enable a Sale Rep to be assigned to a Customer/Client. This request has been sitting around since 2012 and was flagged as “Under Review” in 2016. Intuit has kind of address this request by suggesting that an Invoice custom field be used to assign a salesperson to a Customer Invoice. However, I’m not sure that this meets the needs of most users that want to report on their salesperson/customer relationship. Additionally, several comments under this feature request suggest the ability to compute sales commissions on a billed and collected basis be provided.
This post illustrates how these reporting needs can be met using SharePoint Online, Power BI Desktop, and Excel. I suggest the reader first review the posts QuickBooks Online & SharePoint Online Synchronization, Power BI and SharePoint, QuickBooks Online & SharePoint Online Synchronization Revisited, Using Power BI & SharePoint Online to Solve the QuickBooks Online “Type” Void, QuickBooks Online Tables via Microsoft Power BI Desktop Content Pack.
The post SharePoint for Customer Management and many other posts on this blog illustrate how SharePoint Online can be used for customer relationship management. Below is a screen shot of Customer & Prospect List using the Active Customer List. Here is where the Salesperson to Customer relationship is established and this used the Desktop Power BI report showing sales commissions by salesperson on both a billed and collect basis.
Note: A non SharePoint Online user could also use an Excel spreadsheet with Customer and Salesperson as subsititue for the above Customer & Prospect List.
Next I decided to use a simple Excel spreadsheet to track Salesperson and their commission percents.
I should note that in real life I would keep this information on the Human Resources SharePoint subsite.
As mentioned in the post QuickBooks Online & SharePoint Online Synchronization Revisited referenced above, I uploaded QuickBooks Desktop’s Larry’s Landscape and Garden Supply sample company into QBO. The sample company has more data for 2019 so that is the year I will use for the sales commission report. I will assume that Salespersons are paid commissions on a quarterly basis and focus in on the 4th Quarter of 2019.
I then modified QBO’s Sales by Customer Detail report as shown below. I ran the report for both accrual and cash basis and exported the reports to Excel.
I then opened Power BI Desktop and connected to the above Excel reports as a data sources, the SharePoint Customer & Prospect List, and the Salesperson Commission Spreadsheet. I then set the relationships:
I then modified the Excel Accrual spreadsheet table.
As you can see, I eliminated the columns that were not necessary. Notice that I did not eliminate the Memo/Description column. This is because if there are shipping and finance charges on an Invoice it tells you that in this field. I set up separate shipping and finance columns based on the formula in the screen shot. I assumed that a salesperson is not commissioned on Shipping, Finance Charges and Tax so I added a new column called Commission Base which is Amount-Shipping-TaxAmount-Finance Charges. I also computed the Commission Amount based on the Salesperson Commission Spreadsheet and assigned the salesperson based on the SharePoint Customer & Prospect List.
I made the same changes to the Cash basis version of the spreadsheet.
I then added 3 Visualizations to review the data and placed each on its own page.
You will notice that I did not use the invoice and payment tables available through Power BI Desktop data source connector for QBO’s. While developing this post, I ran into difficulties with how the invoice and payment data is extracted from QBO. There were duplicate amounts that I could not eliminate. My work around was to use a QBO custom report and export it to Excel. This is disappointing since the connector would allow refreshing of the data directly from QBO. If data changes after exporting the Excel spreadsheet, you need to reimport it.