Archives: FAQs

The auto-import is not working correctly, what is going on?

The auto-import feature uses Internet Explorer to literally go into Seller Central, request the reports, and download them. In some cases we get the data directly from the page (like shipping data). This process requires that you have Internet Explorer (at least v11), and a stable internet connection. The auto-import feature will not work with Excel for Mac.

There are a few things that could, and sometimes do, go wrong:

  1. When saving a file we have to use what is called ‘SendKeys’ to click some buttons. If this process is interrupted when a key is pressed, or an IE window goes out of focus, it can trip up the macro. There is no easy way to recover from this other then skipping the file, and moving on. It is best to not use your computer when the import is running.
  2. Seller Central failed to load within 10 seconds, and data was skipped. Check your internet connection to be sure.
  3. Amazon changed something on a page that we were looking for. This happens from time to time. Recently Amazon put a limit on the amount of sales data that could be downloaded per report to 180 days.

If 1 or 2 happens, simply run the auto-import again, and the missing data will try to download. If an item simply refuses to download, open a support ticket so we can take a look.

Can I add my own calculations/data to your spreadsheet?

Yes, absolutely! A new sheet will have to be created since most of our spreadsheets are locked for editing to prevent things from breaking. You can still pull in data from any cell by referencing it in your formula.

Just follow these steps:

  1. At the bottom, middle of the spreadsheet, click the + next to the right of the sheets tabs to add a new sheet
  2. Click the triangle in the upper left next to cell A1
  3. Right Click and select “Format Cells”
  4. Select the Protection tab and uncheck “Locked” (this prevents the new sheet from being password protected when updating data)
  5. Add whatever you want to the new sheet.

Be sure to uncheck “Locked” before clicking update on the dashboard. Otherwise, the cells will be locked on the new sheet and you won’t be able to see the Protection tab under Format Cells.

Refunds that I processed are showing up on the Refunds report – how can these be excluded?

Unfortunately there is no way to tell who processed a refund in the report data. Because of this refunds that you manually processed will show up in the Refunds report as a problem: “not reimb or returned”.

There is, however, one way this can be remedied. We recently included a feature to import seller notes from the order detail page (for PC users).  If you don’t already, when manually refunding a customer, be sure to put in a note to indicate that it was done by you. This way there will no confusion over what returns should be looked into.

It’s hard to keep track of FBA shipments that need reconciliation. Can the spreadsheet help with this?

Yes! As long as you’ve imported all of your FBA shipment data on the FBA Shipping Data sheet, there is a report on FBA Shipping Sum, “Shipments With Discrepancies” that will show shipments that had differences in received quantities with what you shipped to Amazon. A positive Qty Difference are ones you should be concerned about. You can simply copy the Shipment ID into the search on the Manage FBA Shipments page

I’m in the UK (or other country) looking at my US Data, and it is not filtering by date – how do I fix this?

This can happen if the Amazon data is in one format, but your regional settings on your computer, which Excel uses, are in a different format. The dates in the Amazon data need to match the date format on your computer.

For example, you have US Amazon data, so dates will be converted so Excel can use them to m/d/yyyy, but you are in the UK, so your computer is setup with dates in the format d/m/yyyy. This confuses Excel.

Here’s how to fix it: (via How to change date format in Excel and create custom formatting)

  1. On your computer, Go to Control panel and click Region and Language. If in your Control panel opens in Category view, then click Clock, Language, and Region > Region and Language > Change the date, time, or number format.
  2. On the Formats tab, choose the region under Format, and then set the date and time formatting by clicking on an arrow next to the format you want to change and selecting the desired one from the drop-down list:

If you are in the UK, loading US Amazon data, choose “English (United States)” at the top, and click Apply.

You may need to download a fresh copy of the spreadsheet after applying these settings! Things may have broken on the back end!

Dates should now be in the correct format on your spreadsheet.

The Estimated Inbound Shipping cost is not calculating correctly – what do I do?

There are several reasons the inbound shipping cost could not be calculating correctly:

  1. On the FBA Shipping Data sheet – check to make sure the Ship Cost Calculated column is populating. It is only populated if the Date Shipped is the previous day that you were charged for the shipping (or if you fill in Actual Ship Cost). This data is only available in the sales data and shows up as “FBA Amazon-Partnered Carrier Shipment Fee”. If there was more than one shipment on the same day, the total shipping cost is added together, and the sum of the Qty Received (total qty for that date) is averaged to get Avg Ship Cost/Item.
  2. The dates in “Import Shipping SKU Lists” are missing or incorrect. This could be because the dates were removed from the shipment name when the shipment was created, or the dates are in a format that the spreadsheet does not recognize. (it will recognize US and UK dates, and dates in the format 1/1/17, 1.1.17, and 1-1-17)Normally the Name of a shipment looks like this: FBA (1/1/17 12:00 AM) We recommend, when creating a shipment in Seller Central, to leave the date in tact.

If you do not have dates in your shipment SKU lists (tsv files), the only way to fix this is to edit the files and enter the date manually next to “Name” in the .tsv files. Unfortunately, there is no other way to get this date from Amazon data. Worst case, your “Days in Transit” will be incorrect.

*Update for v2.23 – If you are using the auto-import & dates are not formatted correctly in the TSV files, the spreadsheet will use the dates directly from the Amazon FBA Shipments page.

To automatically get the Actual Ship Cost:

The Actual Ship Cost will populate automatically with the auto-importer when clicking to update all data, just the FBA shipping data, or by clicking “Auto Import Shipping Costs” on the FBA Shipping Data sheet. The manual processing fees and labeling & prep fees are added into the total shipping cost.

To manually enter Actual Ship Cost:

“Actual Ship Cost” can be manually entered on the FBA Shipping Data sheet if you want more accurate shipping cost per item. Cells that are red in column BD need a value filled in. The shipping cost can be viewed by copy/pasting the URL in column BF into a browser, then see Service Fees (at the top), “Amazon Partnered Carrier estimated shipping cost:”   Be sure to add any manual processing fees and Labeling & Prep fees into the total.

OR by going to Inventory > Manage FBA Shipments, search for the fba-shipment-id

When the Actual Shipping Cost is filled in, it should look something like this:

 

How do I tell Amazon to give me a refund on the items lost or damaged?

If the number in Total Discrepancy is greater than 0 for an individual SKU, open a case with Seller Support under Selling on Amazon > Fulfillment by Amazon > FBA Issue > Something else. You’ll want to provide as much information as possible to make the process easier for them.

  1. Explain what SKUs (or FNSKUs) have been lost/damaged
  2. Explain that it has been 45 days since the items were lost/damaged
  3. The quantity that has been lost/damaged
  4. The date inventory was last lost/damaged. (look at the Inventory Adj sheet for this – In v2.0, double click on the last adjustment to filter the report by SKU)
  5. The estimated amount you believe you should be reimbursed per SKU (optional)

If you have a lot of different SKUs that were lost/damaged you should do them in batches of no more than 10 SKUs or so. We don’t want to overwhelm Amazon support. That and this will make it easier to track in the future.

 

Here is a template that can be used:

Hello!

I’m showing that the following inventory was lost or damaged in a fulfillment center, but it was never reimbursed for:

Date – SKU – Quantity
9/18/2016 – SKU1 – 1
7/13/2016 – SKU2 – 1
7/11/2016 – SKU3 – 1
8/3/2016 – SKU4 – 1
11/01/2016 – SKU5 – 1

Since it has been more than 45 days since these items were lost or damaged, I would appreciate a reimbursement per Amazon’s FBA Lost and Damaged Inventory Reimbursement Policy. Please let me know if there is any other information I can provide!

Thank you!

 

What reports are used in the Refund & Lost Inventory Reimbursement Checker?

The following reports are used in the Refund & Lost Inventory Reimbursement Checker (you may have to copy/paste the URLs since Amazon no longer allows direct linking)

What do the fields on the Lost/Damaged Summary sheet mean (v2.0)?

Lost FC – Inventory lost in a fulfillment center

Date Last Lost FC – The date the last item was lost in a fulfillment center

Lost Ship – Inventory lost during shipping to a fulfillment center – these items are almost always reimbursed because of a reconciliation case

Damaged FC – Inventory damaged in a fulfillment center

Date Last Dam FC – The date the last item was damaged in a fulfillment center. Does not include “Damaged – miscellaneous”

Damaged Ship – Inventory damaged during shipping to a fulfillment center – these items are almost always reimbursed because of a reconciliation case

Total Found – Inventory previously lost, but found

From Holding – Inventory transferred from a holding account in order to reimburse you for lost or damaged inventory

Lost/Dam Diff – The difference between all inventory lost & damaged, and inventory found & reimbursed

Inv Adj. Difference – The total if you add up all of the inventory quantity counts on the Inventory Adjustment sheet that are SELLABLE for the given SKU

Reimb Lost – Inventory lost that was reimbursed for – double click to view

Reimb Damaged – Inventory damaged that was reimbursed for – double click to view

Total Reimbursed – – The total you have previously been reimbursed for this item

Total Discrepancy  – The total difference between all lost & damaged, found, from holding, and reimbursed.

A negative value means either:

  1. – you were reimbursed more than once for the same item
  2. – there is a gap in data and an item was lost/damaged before the data starts
  3. – Amazon counted one or more “From holding” transfers as a Reimbursement – In other words, they sometimes say “Transfer from holding account” on the Inventory Adj report and there is a “quantity-reimbursed-inventory” on the Reimbursements report, but not always. It seems to depend on the situation.

Total Owed – The estimated total of reimbursements that Amazon owes you based on the total discrepancy and previous reimbursements – This can only be calculated if there are previous reimbursements for this item

Last Inventory Adjustment – The description of the last inventory adjustment – double click to view all for this SKU

Last Case? – If there was a case with seller support opened previously, the last ID will show up here. This means you opened a case or reconciled inventory related to this SKU previously. Double click to view all cases.

Problem? – There is definitely an issue, and a case to seller support should be opened. This means that the last date an item was lost or damaged was greater than 45 days ago. If there are multiple items lost or damaged, and the last one was lost less than 45 days ago, the row will not be highlighted, but it’s very possible there is still inventory that should be reimbursed for.