Chuck's Reporting Corner July 2008

Author: Chuck Vigeant M.Ed. Created: Wed Jul 16 11:31:20 2008

Commission Reports

Most of the reports we provide to clients falls into four main categories, Financial, Sales Job Cost and Business Intelligence (any subject). Within the Sales category, the most requested report is the commission report.

You might think that after 4 years and thousands of reports, we would have generated every type of commission report imaginable, but nothing could be further from the truth. Every new request brings a surprise – and a new challenge. We have the logic solved for many things like fully paid invoices, gross profit by invoice, etc. but generally, clients who are expecting a canned report are somewhat dismayed when they listen to the long list of questions we have for them.

Questions for the client

Is the report based upon cash basis? Accrual basis? Fully paid invoices only?
For fully paid invoices do they have to be paid within a particular time frame?
For fully paid invoices: if a credit memo is applied against an invoice are we supposed to remove those values from the commission?

Is the commission based upon: per item, per customer, by sales rep? combination?
Is the commission a flat rate, or is it tiered and/or dependent upon achieving certain margins, or aggregate sales?

Is the commission based upon gross sales? Gross profit?

Is the report based upon the total invoice? Or just certain items within the invoice?

What type of items are we expecting to report on? If non-inventory, we know the headaches associated with trying to associate a particular vendor bill with a particular invoice.

Are we excluding items? Are they particular items, or types of items?

Are discounts within an invoice expected to be handled in a particular way? Are there multiple discounts on a single invoice?

If we plan to use custom fields to store the commissions, are there any available?

Are there several levels of sales reps? How are they being used and entered in QuickBooks?

Tips

As we have worked with many Pro Advisors – including Sleeter Group Pro Advisors – there is no lack of “thinking” around the box to achieve the desired results for the client. However, here are some tips for setting up QB to enable proper commission reporting using Crystal Reports, or some other reporting tool:

For gross profit reports, keep the process simple. I can’t tell you how many times we have talked to clients who endeavor to account for every exact penny, only to spend more money tracking the data than what it is worth.

If the commission is based upon the sales rep, use a custom field to store the commission rate. Remember that a sales rep cannot be an Other Name, because custom fields are not available for Other Names.

If the commission is based upon the item, use a custom field to store the commission rate. There are two approaches in this case, each with its own caveat: (a) If you store the rate in each transaction, your data will always be accurate – even if you change the rate later on; however it depends upon the default rate being stable, and/or a watchful eye from the data entry operator. (b) If you only use the rate stored in the item itself – and change the rate later – then your historical data will be inaccurate once the rate has changed; however, this does relieve the data entry operator of having to check this figure every time they create an invoice.

For fully paid invoices, make sure that credits are applied both timely and properly.

Final thoughts

As with any report outcome, the consistency of the data entry system is the key.
Engage with your client to remove any manual and arbitrary thought processes from the system. It costs more money to keep that type of system intact.
Be clear about the consequences of using each item type for reporting and commission purposes.
Above all, listen to the client to ascertain what it is they are trying to achieve with their commission methodology – sometimes the price of keeping a data entry system to their liking, is more expensive than the money they are trying to save using a particular commission structure.