Select Page

Amazon FBA Inventory Reconciliation and Accounting Issue

agsdix-smt1-event

April 13, 2021

agsdix-fas fa-user

Kalvin Mizzi

agsdix-fas fa-comment

0

Throughout this blog post you may see specific case IDs or other situational specific information. This post is being sent to the Amazon team in order to try to promote fixes to the Amazon issues. I wanted to post the entire content as a blog post as well as I am sure there are many other frustrated sellers facing similar issues.

I have discovered that inventory accounting with FBA has some big problems.

The worst sellers affected are those who have Unified accounts (i.e. selling in US and Canada) and those who use Manufacturer Barcodes (Commingled inventory). However, all FBA sellers are affected.

As an Amazon Seller, I need to be able to do inventory accounting and calculate the cost of goods sold for the end of a period.

Typically cost of goods sold is calculated at the end of each month.

Using a periodic inventory system, cost of goods sold relies on the ending inventory count for a given month. Because FBA acts like a warehouse location, sellers rely on Amazon to provide reports telling them how much inventory they have at the end of each month.

Amazon provides this page as an Amazon Fulfillment Reports explanation: https://sellercentral.amazon.com/gp/ssof/reports/searchv2.html

On this page, the magic formula Amazon describes for inventory balance calculation is:

Starting inventory balance + received inventory - customer orders + customer returns +/- adjustments - removals = ending inventory balance

Let’s call the received inventory, customer orders, customer returns, adjustments, and removals “FBA Inventory Movements

There are two reasons why a seller would need to know the inventory snapshot at the end of a given month:

  1. Establish a starting inventory balance
  2. Verification check that the previous starting inventory balance + FBA Inventory Movements = ending inventory balance

There are three potential reports that have inventory snapshot data:

Inventory Reconciliation

Seller Central Link

https://sellercentral.amazon.com/gp/ssof/reports/searchv2.html#orderAscending=&recordType=INVENTORY_RECONCILIATION

MWS API ReportType

_GET_FBA_RECONCILIATION_REPORT_DATA_

Supported by API?

No

Returns accurate sku?

If you sell only in the US, maybe
If you sell in the US and Canada, no

If you sell in the US and Canada, the sku returned appears to be randomly picked between the sku on your US account and the sku on your Canada account. I know this because I sell in both marketplaces and I use a different sku format for each marketplace.

I reported this issue in 2019 with Case ID 5884459081 and was told that because my account is unified (US and Canada), the data returned is shared between the two marketplaces. The final reply I got was as follows:


These two skus are using the manufacturer barcode, which have them share inventory on the report. The selection of which sku is used is not determined at this time. However, the inventory is all accounted for under SKU: 1727572-FBA-CA for both SKUs. This is working as intended.

You can use an Amazon Barcode for the SKU: 1727572-FBA so the report will not unify both SKUs. This is the only alternative we have at the moment.

If you need to reconcile your units in each marketplace, you will need to use the Online Reconciliation if you want specific result for the SKU: 1727572-FBA.


I don’t believe that “working as intended” is accurate here when “The selection of which sku is used is not determined”. I do suppose that another condition to when this problem occurs is not just having a unified account, but also using Manufacturer Barcodes instead of Amazon Barcodes (AKA Sellers who use commingled inventory).

Returns accurate quantity?

Yes and no. It is accurate but does not return inventory level for just the US marketplace if you sell in both US and Canada. It returns only the unified total count between the two marketplaces.

This is a problem and breaks the reconciliation process because the FBA Inventory Movements reports do return the inventory level specific to a marketplace.

Google Sheets example link

https://docs.google.com/spreadsheets/d/1itG_gFHQxSiy4eLI6_Y2aOvwiK8xiUphoubrteGXeiI/edit?usp=sharing

Example ReportId

26890806852018661

Quantity returned in example

30

Other notes

Issue 1:

This report is not supported by the MWS API. Since I am needing to calculate cost of goods sold for both myself as a seller and other sellers through an app I am developing, this is an inefficient workflow requiring the user to manually generate the report via Seller Central and then upload it to my software.

If you attempt to RequestReport of type _GET_FBA_RECONCILIATION_REPORT_DATA_ via MWS API, you will get a result of “_DONE_NO_DATA_”

I’ve opened up a support ticket in 2019 (Case ID 6050777341) which reached MWS level 2 support ([email protected]) and received a reply:


I understand you have questions about requesting a FBA reconciliation report via API using the report type _GET_FBA_RECONCILIATION_REPORT_DATA_.

Unfortunately, the report is not supported by the API at this moment.

Currently, the only reports supported by the API are the ones in the following documentation page:
http://docs.developer.amazonservices.com/en_US/reports/Reports_ReportType.html

I’m truly sorry for the frustration you have encountered with this situation.

I have informed our Engineering and Development teams, and they have added your comments as feedback for a Feature request. I really appreciate that you took the time to let our offices know about this limitation. We take your interactions with our programs very seriously, and it is real-world feedback such as you have provided that ultimately helps Amazon become a better platform for everyone involved.


I then tested this again in April 2021 and got the same result

Issue 2:

As of March of 2021, you can no longer calculate end of month inventory for the desired month. I have a separate case open with just this issue: Case ID 8135637491. You can see in this screenshot that Seller Central (the only way to generate this report since no MWS API access), only allows the 1st day of each month to be chosen as the date.

This causes problems because most businesses get inventory snapshot reports once per month at the end of each month (whether their own warehouse or a 3PL partner). The timestamp of inventory count across warehouses (including FBA) needs to be the same in order to assure there is no double counting of inventory between warehouses.

This disabling of any date but the end of the 1st day of a given month is a NEW bug in the UI that appeared sometime in March. It is very restrictive and makes reconciliation even more difficult.

Daily Inventory History

Seller Central Link

https://sellercentral.amazon.com/reportcentral/INVENTORY_SNAPSHOT/0

MWS API ReportType

_GET_FBA_FULFILLMENT_CURRENT_INVENTORY_DATA_

Supported by API?

Yes

Returns accurate sku?

No, returns the sku from the Merchant Fulfilled Offer, not the FBA offer

Returns accurate quantity?

No, in our test example it returned 29 units total (28 US, 1 Canada), different from the reconciled count of 30 in the Inventory Reconciliation report, which I assume is the accurate authority

Google Sheets example link

https://docs.google.com/spreadsheets/d/1itG_gFHQxSiy4eLI6_Y2aOvwiK8xiUphoubrteGXeiI/edit#gid=1009503470

Example ReportId

28946506007018729

Quantity returned in example

29

Other notes

Inventory separated by FBA fulfillment center location

Monthly Inventory History

Seller Central Link

https://sellercentral.amazon.com/reportcentral/INVENTORY_MONTHLY_SNAPSHOT/1

MWS API ReportType

_GET_FBA_FULFILLMENT_MONTHLY_INVENTORY_DATA_

Supported by API?

Yes

Returns accurate sku?

No, returns the sku from the Merchant Fulfilled Offer, not the FBA offer

Returns accurate quantity?

Yes, in our test example it returned 30 units total (29 US, 1 Canada), equal to the reconciled count of 30 in the Inventory Reconciliation report, which I assume is the accurate authority

Google Sheets example link

https://docs.google.com/spreadsheets/d/1itG_gFHQxSiy4eLI6_Y2aOvwiK8xiUphoubrteGXeiI/edit#gid=14529530940

Example ReportId

28951817463018729

Quantity returned in example

30

Other notes

Inventory separated by FBA fulfillment center location. Includes items with 0 end quantity.


Out of these 3 reports, all of them return inaccurate merchant sku, making it difficult to match the report data with the inventory item for accounting. The Inventory Reconciliation report may return both accurate sku and count if you are not a seller who sells in both US and Canada. If you are, the wrong sku returned and lack of separation between inventory in each country makes the report virtually unusable.

Amazon gives tips on the Amazon Fulfillment Reports page to use FNSKU to reconcile inventory. This contradicts their inventory reconciliation formula:

Starting inventory balance + received inventory - customer orders + customer returns +/- adjustments - removals = ending inventory balance

.. since, the customer orders report (aka Amazon Fulfilled Shipments) does not include the FNSKU field.

Another note. You must request FBA Inventory Movements reports with an end date of no later than 72 hours prior to current date. Otherwise, the data returned in FBA Inventory Movement reports may be different than if you request the same report with the same date range after the 72 hours.

There is also random situations where the SKU returned in the FBA Inventory Movements is wrong. The most common error is the merchant fulfilled sku being returned instead of the amazon fulfilled sku. This could be related to Manufacturer Barcode mixups as well.

Summary

To summarize, in order to allow Amazon sellers to do inventory accounting, Amazon needs to fix the following:

  1. Have Inventory Reconciliation Report only return data for the marketplace the seller is in when requesting the report
  2. Allow Inventory Reconciliation Report to be accessible via MWS API
  3. Fix bug with Manufacturer Barcode sku’s returning inaccurate sku’s in FBA Inventory Movements Reports
  4. Fix Seller Central bug to allow seller to choose any date for end of Inventory Reconciliation Report
  5. Disallow User to download FBA Inventory Movement Reports with end dates later than 72 hours prior to current date OR fix the issue with data being inaccurate for a fixed date range depending on when it is requested
  6. Add FNSKU to Amazon Fulfilled Shipments report, so that seller can follow Amazon’s tip of using the FNSKU field in each report (as opposed to merchant sku) to reconcile inventory

Share this post

Kalvin Mizzi

Online Seller, Finance Geek, Developer

Kalvin Mizzi has over 20 years experience in the E-commerce industry.  He holds an Economics degree from University of Toronto, where he graduated with Honors, and is a self-taught programmer and entrepreneur.

Kalvin started his career in the real estate development industry, raising equity/debt, financial modeling, and sales/brokerage.

He is passionate about systems and helping other E-commerce sellers.

Related posts

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *