One of the admin tasks I’ve been doing the past few weeks, while I wait for stock to arrive, is reconciling Amazon FBA inventory.  I hadn’t even thought about doing this until I was listening to a podcast, where they mentioned that Amazon could potentially owe you thousands by not reimbursing you for lost or damaged inventory.

A little disclaimer here: If you’re an Amazon FBA seller this post should be helpful.  If you’re not it’ll most likely be long, tedious and boring! So don’t say I didn’t warn you!

Why would you want to do this?

If a customer decides to return an item, Amazon takes back the funds from that sale and holds it while they wait for the customer to return the item.  If they do, the item goes back into your inventory.  If they fail to return it, Amazon should release the money back to you – but this doesn’t always happen.

Inventory can also get damaged in the warehouse, or lost when it’s being moved around between warehouses. You should get reimbursed for this, but, many sellers report that they don’t.

I had no idea how to even know if all my stock is accounted for, and Google only got me so far.

Now I finally have my head around it all, here’s how you can do it too.

Getting started

First things first, download your reports.  You’ll find them all under Reports > Fulfilment by Amazon.

The ones you’ll need are:

• Received Inventory
• Customer Shipment Sales
• Returns
• Inventory Adjustments
• Reimbursements
• Removal Order Detail

You’ll need to choose a timescale.  I decided to look back over the last year.

These all download as a .txt file, which you can open in Excel (select ‘open with..’) and then save as an Excel worksheet.

You can download/view online a custom timeframe for most of the reports – other than Customer Shipment Sales, where you can only download a month or less.

So, now you have all this, what next?

Received Inventory

If you start with received inventory, you can check that you received everything you expected. You can do this by simply adding up the quantity column.

Customer Shipment Sales

You might have noticed there’s also a report called Orders. The reason I use Customer Shipment Sales is Orders also includes any Removals sent back to you.  As we’ll be looking at that separately, you don’t want to include those here.

To make looking at the orders simpler (for me, anyway), I created a simple spreadsheet, just containing months and years, viewed my orders online a month at a time, and entered these details into the spreadsheet to get the total.  This means I can just add each month to this and keep it going that way.

Note that Customer Shipment Sales is only orders which have been dispatched.  If you need to see orders that haven’t yet been completed, you can look at Orders (maybe for the past few weeks) and add those into your spreadsheet.

You can now check if there is a difference between what you received, what you sold and what you have in stock.

Customer returns

Next take a look at your returns report.

The status column will tell you whether each item has been reimbursed back to you, or returned to inventory.  (Reimbursed, in this case, means the customer didn’t return the product, so you still received payment for the order.)

Highlight the ones that have been reimbursed – you’ll need these for the next step.

Now, I’m not sure how many returns I had in total – so I now need to start keeping those emails you get when a customer returns an item, in order to keep track of them and be sure that this report is even correct.

Reimbursements

Now check your reimbursement report.

Firstly, you want to check that the returns that were marked as being reimbursed have been so.  You can cross check the order IDs to confirm this.

You can also check how many warehouse damaged items you’ve been reimbursed for.  If you look at the Reason column it will tell you.

This will give you the total number of units you’ve been reimbursed for.  

Adjustments

You can also check your adjustments report.  This isn’t an easy one to work out, but if you add up the quantity column this will tell you about any changes made to your inventory balance.

Note: this does not mean you’ve been credited for any adjustments – it just mean the units have left your inventory balance.

If you end up with a minus number, that’s ok.

I would also want to see that my adjustments and reimbursements matched.

Removals

Your removals report will tell you how many units (if any) have been removed from stock and shipped back to you.  For example, I have any that are classed as unsellable returned to me and I sell them on via other channels as ‘open box’.

Current stock

If you do have current stock, remember you need to take this number into account too.

Working it all out

Now you have all the figures, here’s the maths:

1. Start with the number of units you received.  Minus the number you’ve sold.
2. See how many returns made it back into your stock.  Add these to the total.
3. Check your adjustment report to see if any units have been removed for other reasons (for example) warehouse damage.  Remember we added up the total adjustment figure from this report. Add/subtract this from your total.
4. Check your removals report and subtract these.
5. See what you have left in stock and add this.
6. This then leaves the number of units you cannot currently account for.

For example, you received 720 units and sold 698. This leaves you with a shortfall of 22 units.

You had 21 customer returns.  3 were reimbursed to you.  The other 18 were returned to inventory.

This should leave you with 40 units.

Your adjustment report says 25 units have been removed due to warehouse damage. This leaves you with a total of 15 units missing.

The reimbursement report also says you were reimbursed for 25 damaged units.  (You could also check your payment reports to verify this if you had the time and inclination.)

The removal report shows you’ve had 11 units removed from stock and returned to you.  This leaves 4 units unaccounted for.

You have one item left in stock.  This leaves a shortfall of 3 units.

Making it simple

To help you, I’ve created a (very) simple spreadsheet that does all the calculations for you.  Simply fill in the green cells using information from the relevant reports, leave the red ones alone and it’ll get worked out for you.

Download the spreadsheet.

Getting reimbursed

In the event that you do all of this and find a shortfall, then you need to contact Seller Support. I would suggest sending them a copy of your spreadsheet, to show how you have worked this out.  They can then investigate for you.  I was a few units short on mine and sending them the spreadsheet so they could see how I’d worked it out, definitely helped the process and they reimbursed me in full, quickly and with no fuss.  

Next steps

I know there are bound to be many more common problems that occur when trying to do this.  Look out for a future post covering the things to look out for and how to reconcile them.

In the meantime, enjoy using the spreadsheet, and let me know by commenting below if you find it useful.