I have the standard invoice-lineitem-customer-inventory system going.

It was all going fine until I found out that my sales report -- that summed up the invoice line items, might not be accurate because items can be returned and/or exchanged for other item. Since the sales report decides on what royalties are paid out, this figure needs to be accurate.

I already have a running sales and return totals in the inventory, but the twist is that all the reports are run for a certain date range to a simple running total doesn't cut it.

My question is how to handle this? Add a field in the line items is the same as the quantity but gets subtracted if someone returns it then tally that up? Thats the best I can think of. The trick to that is that each line item is not attached to an exchange so I'll have to do some restructuring.

Any ideas?

If I was doing this, I would have the returns and exchanges entered as new transaction line items with the date they are returned. That way, your inventory on any particular date will be correct.

As for sales commissions, they will adjust when the returns happen. I don't see any way around this. If a customer buys something on the 29th of the month, the salesman get a commission on the 31st and then the customer returns the item on the 1st of the next month, the salesman's commission will automatically adjust in the new month. You can't really ask the salesman to give back the commission after it's been paid. You just have to adjust it down accordingly in the following month. The return/exchange line item will do this automatically.

GREAT idea, I should have thought of that.

In terms of the possible problems, I don't see that as a huge problem, especially when there is no real way around it short of making over-complex.

I have used two ways to invoice returns...

Have a basic "Qty Ship" field where you can insert negative numbers for returns.

or

Have a "Qty purchased" and a "Qty Returned" field... the net of which is calculated in a "Qty Ship" field.

The second method allows you to make reports that show how many items were purchased and returned (perhaps to help you identify products with a high return rate), whereas the first method would only give you the net of the two.

but one thing's for sure, you should always invoice the return and the purchase, even if it is a straight exchange.

Customer returns item 1, qty 1, at $10

Customer purchases item 2, qty 1, at $10

The total balance on the invoice is zero, but it is necessary for reporting purposes.

In accordance with generally accepted accounting principles, revenues and expenses should be matched to the period in which they are earned... although this is a SLIGHTLY different idea, it makes sense to me that employees should make the commission in the month they sell the product, and lose it in the month it is returned. The return will simply have a reduction affect on the employee's total sales, therefore reducing their comission in the second month.

Jason