Steps for finding a bad transaction
Bad data just happens. Whether manually keyed or downloaded from an external source, from time to time a transaction gets created that is incorrect resulting in a reporting error. Here are the steps to find:
- Re-run the report to show the error
- Select only that one group of transactions
- Change the subtotal field (e.g. mfg to customer)
- Select just those transactions and audit.
Here is an example of a report that is definitely incorrect due to a bad transaction:
Selecting just "Lutron" (for example) shows values for only that set of transactions:
Changing the report to run by customer shows which customer is affected by the bad transaction:
Next, perform a basic select to show the transactions. In this example we are just doing a basic select in the orders section:
These transactions stick out as being the problem. The reason is a little complicated given the report that is shown. The top down payment report is very accurate on the earnings (commission), but the sales is calculated by calculating the orders average rate of earnings and then multiplying this rate by the amount received. In the four orders above, the rate of earnings is positive (4% for the top order) but the amount received is a large negative (debit of $3017.28 / 4% = ($7532)).
Looks like the project went sideways and the company was charged for some returns (or the like). Best option would be to change order the order to reflect reality (e.g. if the entire order was canceled, then cancel the order which brings the rate of earnings to zero.)