When we are sending our products to Amazon Warehouse (FBA), we often need to know certain information, such as how many items are available now in Amazon, calculating the previous sales i.e. order history and currently available quantity in Amazon Warehouse. If it seems any Top or Hot selling item is low in stock we can send them to FBA. So that we never lose the sale due to unavailability of an item in Amazon.
From the daily inventory report, we can know how many products in total “FBA” quantity available in Amazon Warehouse right now and how many quantities’ lefts in Amazon Warehouse. From this report, we can get a recommendation for an item we need to send to Amazon Warehouse. To prepare the current stock report in FBA, we need to use the tools that are provided on the Amazon website as well as any third party applications like channeladvisor, Linnworks, Sellercloud etc.
In this article, we will be describing how to create a daily inventory report file on Amazon. This article is divided into two parts where part 1 contains how to download a report from Channel Advisor and part 2 covers on downloading reports from Amazon.
The Purpose of this Article
- To know about how many “FBA” products’ quantity available in Amazon Warehouse.
- Time-saving. (It’ll require just about 1 hour for creating this report)
How many you have left in Warehouse (which is the CAR units) – Downloading Channel Advisor Report
Step 1: On Channel Advisor, go to Inventory and then select All Products.
Step 2: In the All Products page, go to the “Export” button on the right-hand side and select “Qty Available” from the Drop Down.
Note: Make sure there are no filters in any columns or you want to get a complete download of all products.
Step 3: On the Export Product page, under the “Export Fields” section, make sure that you have these four Product Fields selected: SKU, Title, ASIN, Quantity Total. Once selected, click the “Export” button on the right to download a report.
Step 4: When the report is processed, click the file name link to download the data on your computer.
Step 5: Open the text file inside the .zip folder.
Step 6: Copy paste all the Data from the text file, into a new excel file and save the file as a name.
Step 7: Rename Auction Title column to Title, Inventory number to SKU, Total Quantity to CAR.
Step 8: Sort the Title Column and delete all rows that are empty.
Step 9: Repeat the same cleaning procedure in CAR and Quantity Column.
Step 10: Cut and reorganize columns to go in this order from left to right: SKU, Title, ASIN, CAR.
You are now ready to start downloading Amazon reports!!!
Adding in US FBA A column – FBA A Column = Total FBA Quantity Available in Amazon Warehouse
Step 11: Download an Amazon Fulfilled Inventory Report and download the .txt file when it ready. Copy and paste the data into a new spreadsheet.
Step 12: Sort the Column E (Warehouse-Condition-code) in Descending order and delete all the rows with “UNSELLABLE” as the condition. Since these cannot be sold, we do not need to know their quantity.
Step 13: Sort column A “Seller-SKU” in Descending order and delete all lines that are not FBA SKU. Keep only SKU that have the “FBA” prefix.
Step 14: Sort in Ascending order and delete any rows that have a poorly formatted FBA SKU (might have space in between FBA- and TD-TPU). Our normal SKU’s look like this TD-TPUXXXX or FBA-TD-TPUXXXX. In the past, we have accidentally made incorrect SKU by adding space in between FBA- or in between those SKU. So, there are some SKUs that ended up looking like this FBA- TD-TPUXXXX (spare after FBA-) or FBA-TD -TPUXXXX (space in between those SKU). Once we create these SKU, they are in the system forever and we have to manually delete them this way.
Step 15: Sort Column C (ASIN) in order and in Cell D2 enter the formula =exact (c2, c3).
Drag this down so all cell in column D is full.
Step 15: Copy and Paste Column D as Values.
Step 16: Ctrl + F the word True and highlight. Scroll through Column D to find any duplicate ASINs. If you find matches, delete the ones that have 0 in FBA and keep the one with stock. Again, this is the result of having created wrong SKUs in the past. What we did to fix it is to create the correct formatted SKU, under the same ASIN again. But once we have created the wrong SKU in FBA Inventory, it is there forever and we are not able to delete it from our system again. There might be multiple SKU under the same ASIN and we have to remove duplicate ASIN. Remove the wrong SKU and leave only the correct SKU that is live on FBA. This happens on all marketplaces US / CA / UK
Step 17: Back on the main report page and do VLOOKUP formula to get data from FBA available inventory report.
Step 18: Pull down the formula to fill all lines in column D and copy and paste as values / text.
Step 19: Sort by descending and replace any cell with #N/A with a 0. US FBA A column should be done!
The completely finished file should look like this!
Trouble Shooting Errors
- Be careful in how you do sort to avoid the data from being mixed up I would suggest you make a couple highlighted lines to make sure all the data is staying correctly aligned. By making a couple different colored lines straight across you will be able to make sure all the data stays correctly aligned as you do sorting.
- One common reason why Data gets mis-sorted is if you insert an empty column in between two other columns. If you sort while there is an empty break, it will definitely mess up the data alignment.
Note: If the data gets mis-sorted, then even if you do everything else 100% correct, your file will be inaccurate so it’s very important to keep it all properly sorted. Feel free to use lots of colored lines just to make sure everything is staying in order!
And so, that is how you can update daily inventory using Channel Advisor. Very soon we will be released on how to do this same task on other third-party apps such as Linnworks, Ecomdash, Sellercloud, and others. So keep reading our blog.
