I run sales reports on groups of my products because these groups are joint efforts with other companies. I need to report to them exactly how many dollars were collected for their groups of products.
The problem is that if a customer uses an order total discount, that discount is not shown on any of the ecommerce reports that target specific products. Consider that we have these two orders
- Bob orders Item A for $200 and uses an order total discount of $50. Bob pays a total of $150
- Alice orders Item A for $200 and Item B for $100 and uses an order total discount of $50. Alice pays a total of $250.
If we run one of the ecommerce product reports that includes Item A and Item B, it will look like this:
Item Name qty Net Sales
Item A 2 $400
Item B 1 $100
TOTALS 3 $500
This implies that we collected $500, but we only collected $400. The discount applied against the order total for each of these orders is not accounted for at all. There isn’t even a flag column like “Includes pre-discounted invoices”.
If we run one of the ecommerce sales reports that includes these items only, it will look like this:
Name Products Amt Paid
Bob Item A $150
Alice Item A,Item B $250
TOTALS $400
Well, we got the right totals here, but we’re trying to run product specific reports. We can’t use this report if we have a bunch more products and customers ordering in all kinds of combinations. A very simple report might look like this:
Name Products Amt Paid
Bob Item A $150
Alice Item A,Item B $250
Dave Item B $200
Jane Item A,Item D $450
Jim Item C $300
Mike Item D,Item C $450
Frank Item E,Item B $250
TOTALS $2050
Again, the totals would be right, but we can’t learn anything about individual product totals and there’s no mention whether discounts were applied at all. Even if the discounts were mentioned here, how does one decide how much of the discount should apply to each product?
The answer here is distributed discount application in reporting. In Alice’s and others’ orders, two items were purchased and a single order total discount was applied. When reporting, that discount amount should be applied to both items, either equally or weighted based on the items’ full prices.
For Alice’s order, when reporting amounts paid for individual items (regardless of what is shown on the customer’s actual receipt), an equal distribution would simply apply an equal portion of the order discount (half in this case) to each of the items, like this:
Alice’s order discount distribution
Name Full Disct. Amt Paid
Item A $200 $25 $175
Item B $100 $25 $75
TOTALS $300 $50 $250
A weighted distribution might seem more fair depending on context, and is calculated by determining what percentage of the discount should be applied to each item. The percentage is calculated by dividing the full item price by the pre-discounted order total. For Alice’s order, it would look like this:
Alice’s order discount distribution (weighted)
Name Full Disct. Amt Paid
weighted
Item A $200 $33.33 $166.67
Item B $100 $16.67 $83.33
TOTALS $300 $50 $250
If this were done behind the scenes, the ecommerce product reports that includes Item A and Item B mentioned at the top (that includes only Bob’s and Alice’s orders) would look like this:
Item Name qty Net Sales
Item A 2 $316.67
Item B 1 $83.33
TOTALS 3 $400
This report would contain the correct total of $400 actually collected and the Net Sales of each item accounts for the discounts.
I’m in a world of hurt right now because there doesn’t seem to be any product reporting that accounts for discounts. I have partnerships with other companies that need to know how much of their products I’ve sold. As it is now, if I run these reports without consideration for the discount promos I run throughout the year, my partners will get inflated numbers. What I’m having to do to compensate is actually tally the amount of discount that can be applied to any given item, then subtract that from the Net Sales. It’s both tedious and susceptible for error.