Reconciliation Preparation – The Control Document

Once this has been done sort your entire document by this purchase order column. So that all line items for each purchase order are grouped together. Insert a new column to the left of the quantity column, and name this as your model or skew number.

A number, which is easily identifiable and recognizable by yourself and your own system to the left of this column. Copy and insert the ASIN or UPC or EAN column in order for you to do a V lookup function between your system and the backup report, so that your model or skew number column is populated accurately.

To the right of the quantity column, insert two more columns, one for quantity from your systems invoice for that PO. And one for the mathematics used to work out the discrepancy between what Amazon’s quantity column says and what your system’s invoice quantity column says. To the right of the net receipts column. Insert a new column for your invoice net total per line model number or skew. Insert another column to the right of this new column, where you can calculate any discrepancies between Amazon’s net receipts column and your own new invoice net total column.

Once all calculations have been done, you can sum each of the relevant columns. That is the quantity, your system’s invoice, quantity, net receipts, and your invoice net totals, and see whether there is a total discrepancy between any of these values at the bottom of each column. If there is, you can now look for the relevant discrepancy above in all of your individual line items.

To build the basis of your control document insert a pivot table, which includes the following from this backup report data, the purchase order number a received date, the sum of net receipts and the sum of the rebate total.

Create a new worksheet where you can copy and paste these values into the relevant columns. Before adding the following information from your own system, invoice total per PO and invoice date on this new worksheet, insert a column with the heading financial period received after the receipt date. And financial period invoiced after the invoice date, the columns from left to right, should then read, purchase order, invoice date, financial period invoiced, invoice value, receipt date, financial period received, and Amazon net receipts.

You can then build the report, adding the following columns to the right of these variance and neck receipts. Explanation Amazon paid invoice, settlement discounts, remittance deductions, Amazon damage allowance, check Amazon base accrual deduction, check Amazon freight allowance, check chargebacks, and chargeback explanation. You are now ready to begin reconciling all your financial transactions on this document.