Paying commissions or splitting gross profits based on work effort is a common need that isn’t addressed very well in QuickBooks. Transaction Pro Exporter allows me to pick columns and has many filters for limiting data. This is why I use it to get the raw data needed for this project. For the actual report, I am using an accountant’s best friend, Excel. Readers of my previous article, introducing the joys of Data Wrangling with Power Query understand why I am going to use Power Query rather than calculate commissions directly in Excel.
Starting with Transaction Pro Exporter, I am using the invoices object to get a raw list of invoices to pay. The options I picked were paid status =PAID and dates “Since Last Export”. I like this option in the tool because it saves me from paying the same invoice twice. The items and class filters are handy for scenarios where you don’t pay commissions on a particular scenario. In this case that would be the rows with the “Reimbursable Costs” class. I also eliminated a lot of the fields using the select fields option, as seen below.
Once I have exported the raw data, I open an Excel workbook, where I am storing commission rates on a tab. Because this company puts the principal name as the Class Code, we will use this table to match names and get the commission rate on the Data tab “Get Data” by pointing to the file I just exported from Transaction Pro Exporter. Click the “Transform” button to open the Power Query tool to allow making calculations. I organized my calculations into 3 separate steps:
- Raw import – In this query, I start from the exported data and add a new column “Gross Profit” with a simple formula [TxnLine Amount]-[TxnLine Cost] and then filter out all rows with a gross profit of zero.
- Summary – rather than list all detail rows in the output we just need to be able to reference the invoice #; here is where I summarize the invoices.
- Calculation of commission – using the merge function I match the names to pull in the commission split. The merge will give us a split rate on each line. Once we have that extra column, we can just add the new commission column with a formula of [ProfitAmount]*[Split].
Closing out of Power Query, I choose to load my calculations into a new Pivot table giving me the below result. What I REALLY like about using Power Query, in this case, is that once I have the actual amounts to be paid, I can easily reuse that data. All I need to do is reference that query when I create a query to format this information in a different way. That query’s output can be used as a source for importing back into QuickBooks using, of course, Transaction Pro Importer!
In summary, we can streamline our commission payments by using Transaction Pro Exporter to consistently export out newly paid invoices and then leverage Power Query inside Excel to systematically calculate commissions and build an import file to upload the resulting payouts as a bill using Transaction Pro Importer.
