Using Pivot Tables and Summaries with Quickbooks Export

Many people use accounting software, such as Quickbooks, as a way to track their business activity. This becomes a valuable way to analyze business health and key metrics because of the granularity this accounting software has. I have put together a template/rough guide as to one way you may want to Quickbooks data output in order to track your sales rep performance.



This excel template is free with any other purchase. Just email me.

As you will see in the video, there are a lot of different ways you can choose to use your data in order to build useful reporting dashboards/summaries.

This specific template was done to handle some pretty deep complexity. It will report on sales rep, customer, revenue, date, and amount of invoices over a given time frame by customer. The point of it is so you can see what sales reps have done what for a given customer and if a given customer that a client had has been active over a certain period.

Then, managers can call up the sales rep and inquire as to why the customer has not been contacted in x months.

Based on these reporting needs, the sheet will show:

  1. Total revenue by rep by customer.
  2. Total revenue by rep (can be granulated down to go by revenue from customers that have had 3+ invoices in last 6 months, 1-2, or none).
  3. Total revenue by customer.
  4. Date of last invoice by customer by rep.
  5. Total invoice count by salesmen.
  6. Invoices by month.
The pivot table makes the reporting really clean and useful, but as you will see charts can also be built based on the data used for the pivot table and this is another visual way to track performance of your customers or reps.

The reason why I picked Quickbooks as the source even though many other Accounting softwares would work fine, is because so many small businesses use it so they are used to see the type of export formats that have been built out in the excel template. 

At a very general level, the point here is that data analysis will help improve your organizations ability to manage customers/salesmen.

One reporting feature I did not show in this, but have added going forward is the ability to track all of this information on a weekly/monthly/annual basis. This would not be granulated by customer or rep, but rather show organizational performance over time.