Sale!

Amazon FBA Refund & Lost Inventory Reimbursement Checker

Rated 5.00 out of 5 based on 1 customer rating
(1 customer review)

$14.99$49.99

Clear
SKU: N/A Category:

Description

Amazon commonly forgets to reimburse for losing/damaging inventory in a fulfillment center. They’re supposed to reimburse you after 45 days of losing or damaging inventory, but they often do not! They may also forget to reimburse you when a customer gets a refund, but then doesn’t return the item within 30 days.

Potentially you can recover THOUSANDS of dollars with the help of this spreadsheet, and there are MANY USERS WHO HAVE DONE EXACTLY THAT!!

Don’t pay a percentage of reimbursements, or a monthly fee, to a reimbursement service when you can pay once and easily do it yourself! 

 

The FBA Refund & Lost Inventory Reimbursement Checker will show you:

For Lost & Damaged inventory:

  • Exactly how much of your inventory Amazon has lost, or damaged in an Amazon fulfillment center
  • The discrepancy between what items you’ve already been reimbursed for & what items have been lost/damaged
  • Takes into account reimbursements that were reversed (NEW for v2.32)**
  • Items lost or damaged during shipping**
  • Filterable charts outlining all data**
  • Flags items that are past the 18 month window for getting a reimbursement from Amazon**
  • The total that you’ve been reimbursed in the past, and the estimated total that Amazon owes you per SKU & overall**

For Returns/Refunds:

  • Refunds where the customer didn’t return the item, and Amazon did not reimburse you for the customer refund
  • Reimbursements of refunds where the amount was not correct**
  • Customer concession refunds that Amazon never reimbursed you for**
  • Takes into account reimbursements that were reversed (NEW for v2.4)**
  • Totals of how much Amazon previously reimbursed you, and estimated amounts of how much Amazon owes you**
  • Works with Amazon UK –  and Amazon Europe (Spain, Italy, Germany, France) ++
  • Flags returns that are past the 18 month window for getting a reimbursement from Amazon**
  • Flags refunds that were given after 35 days (5 days to allow for shipping) of purchase**
  • Flags reimbursements that were not given after the customer returns the item after 30 days. **

Other Features:

  • *NEW for v2.3* – Auto import & updating of all reports (PC only) **
  • *NEW for v2.3* – Gather seller notes from returned orders (PC only)**

 

*Lite – works with all versions of Excel, OpenOffice, and Google Sheets, however you will have to manually paste in the Amazon reports for OpenOffice and Google Sheets – Charts are not included with the Lite version

**Regular & Enterprise – works with all versions of Excel 2010+ – Charts on the Summary sheet will not work in Excel for Mac. We recommend the most recent versions of Excel for Mac 2011 or 2016. 

++All versions work with Amazon.com, Amazon.co.uk, and Amazon Europe (Spain, Italy, Germany, France).   For the Lite version, Refunds use a different Amazon report, and is not as robust in features.

 

Seller Support Templates

 

 EnterpriseRegularLite
Works With (software)Excel 2010+, Excel for Mac 2011/2016Excel 2010+, Excel for Mac 2011/2016Excel 2007+, Excel for Mac 2011/2016, OpenOffice, Google Sheets
Works With (Amazon Market)Amazon.com, Amazon.co.uk, and Amazon Europe (Spain, Italy, Germany, France)Amazon.com, Amazon.co.uk, and Amazon Europe (Spain, Italy, Germany, France)Amazon.com, Amazon.co.uk, and Amazon Europe (must have a separate copy of spreadsheet for each Europe market)
Amount of Amazon data supportedOnly limited by Excel & your computer. We recommend a multi-core processor & 64 bit Excel for large amounts of data.- Up to 10k records for Inventory Adj, & Reimbursement reports
- Up to 50k sales records (to gather refund data)
- Up to 5k records for Returns report
- Up to 5k records for Inventory Adj, Reimbursement Returns, and Refunds report
Auto Generation & Import of Amazon Data (PC Only)Yes - Including automation for Europe refunds processingYesNo
Import seller notes for refunds (PC Only)YesYesNo
Reimbursement & Refund data chartsYes (will not work with Excel for Mac)Yes (will not work with Excel for Mac)No
Detailed problem identification for RefundsYesYesNo - only reports an issue if a refund does not have a corresponding reimbursement or return
Support for reimbursement reversalsYesYesNo
Estimations for amounts Amazon owes youYes - support for EUR and GBPYesNo
Items lost or damaged during shippingYesYesNo
Flags items & returns that are past the 18 month window for getting a reimbursement from AmazonYesYesNo
Checks reimbursements of refunds where the amount was not correctYesYesNo
Checks customer concession refunds that Amazon never reimbursed you forYesYesNo
Flags refunds that were given after 35 daysYesYesNo
Flags reimbursements that were not given after the customer returns the item after 30 daysYesYesNo
Displays refund & original order totals, and order, refund & returned datesYesYesNo

 

 

The Lost Damaged Summary (Regular & Enterprise):

 

The Charts (Regular & Enterprise):

 

The Refunds Summary (Regular & Enterprise)

 

Additional information

Release Log

4/11/2016 – v1.0 – initial release
4/12/2016 – v1.1 – updated max rows for data to 5000, made formulas faster, improved instructions
4/16/2016 – v1.11 – fixed bug with macs, made all columns sortable and filterable
5/10/2016 – v1.13 – added more details to instructions, fixed another mac bug

10/29/2016 – v2.0
– Added 5 filterable charts based on data
– Added count for lost/damaged items in shipping
– Adjusted counts to only include SELLABLE inventory from the Inventory Adj report
– Added date last lost & damaged columns
– Added Last Inventory Adjustment with ability to click and filter Inventory Adj report by specific SKU
– Added ability to click on Reimbursement counts to filter Reimb sheet by specific SKU
– Added last Case ID column
– Added column to determine if there is a problem or not along with field highlighting based on dates of last lost or damaged
– 'Misc Damaged' now counted as damaged
– Added "Total Reimbursed" & "Total Estimated Owed" per SKU and overall
– Added Amount reimbursed to Refunds report
– Added 30 day check to Refunds report
– Added Last Case ID to Refunds report
– All reports adjusted to allow for a max of 20,000 records
– Improved calculation speed of entire spreadsheet

2/9/2017 – v2.1 Regular
– Added feature to remove lost & damaged inventory records from the past 45 days from the Inventory Adj report
– Added feature to filter reports to view lost, damaged, reimbursed records by double clicking on the corresponding numbers in the Summary report
– Added "Items Reimbursed Value" to show the total value of items reimbursed with inventory
– Added "Avg/Item" to show the average amount previously reimbursed per item
– Refined the "Total Owed (estimated)" by factoring in items reimbursed with inventory and using the average of items reimbursed instead of just the last item
– Fixed case where Amazon marks "Damaged – miscellaneous" differently, which caused some damaged inventory to not be counted
– Added ability to import more than one refund report at the same time
– Added a check to Refunds reimbursement to check to see if the amount reimbursed is low and needs to be reviewed
– Added more descriptive Problem cases to show why there is a problem
– Fixed cases where the same order had more than one return and one or more items is not returned (a check is made on the SKU & order number instead of just the order number)
– When orders with more than one of the same item are refunded, but only one is returned, this case will be flagged as the reimbursement not being correct
– Fixed Reimb Amount for Refunds so it includes the correct amount when reimbursement was given with inventory

2/9/2017 – v2.1 Enterprise
– Initial release
– Ability to process more than 10k Inventory Adj, & Reimb, and more than 5k return records at a time
– Entirely different method of processing records on the back end, built for speed

2/14/2017 – v2.11 Regular
– Fixed data file imports with Excel for Mac

2/14/2017 – v2.11 Enterprise
– Fixed data file imports with Excel for Mac

3/19/2017 – v2.12 Regular
– Fixed bug with Refunds not showing as returned when they should have been (column J on Refunds sheet)
– Fixed bug with dates not matching correctly on Refunds sheet
– Fixed bug with filtering reports when clicking on a number from the LostDamaged Sum sheet – previously the wrong report would load in some cases

5/11/2017 – v2.2 Enterprise
– Switched from using Refunds report to Sales Data
– Rebuilt entire Refunds Sheet
– Added ability to flag returns that were refunded after 35 days and returned after 30 days (with accommodation for holiday return policy)
– Added refund date, order/refund days, months since refund, date returned, and days to return to Refunds sheet
– Added Total Previously Reimbursed and Total Owed to Refunds sheet
– Added feature to only flag problems if they occurred in the past 18 months
– Added ability to catch refunds where the entire amount was not refunded, but you should be reimbursed (usually customer concessions that should not have been taken from you to begin with)
– Added report first and last dates on Refunds and LostDamaged Sum
– Added Locality setting for UK and Europe
– Formatted LostDamaged Sum better
– Avg/Item on LostDamaged Sum now populates from Sales Data, if there was never a reimbursement for that SKU, for better accuracy
– Improved speed of calculations

5/25/2017 – v2.21 Enterprise
– Fixed bug with Refunds Amount Summary chart not displaying correctly on Summary sheet

5/25/2017 – v2.21 Regular
– Switched from using Refunds report to Sales Data
– Rebuilt entire Refunds Sheet
– Added ability to flag returns that were refunded after 35 days and returned after 30 days (with accommodation for holiday return policy)
– Added refund date, order/refund days, months since refund, date returned, and days to return to Refunds sheet
– Added Total Previously Reimbursed and Total Owed to Refunds sheet
– Added feature to only flag problems if they occurred in the past 18 months
– Added ability to catch refunds where the entire amount was not refunded, but you should be reimbursed (usually customer concessions that should not have been taken from you to begin with)
– Added report first and last dates on Refunds and LostDamaged Sum
– Added Locality setting for UK and Europe
– Formatted LostDamaged Sum better
– Avg/Item on LostDamaged Sum now populates from Sales Data, if there was never a reimbursement for that SKU, for better accuracy
– Improved speed of calculations

6/6/2017 – v2.22 Regular & Enterprise
– Added ability to import and read Amazon Europe (Spain, Italy, Germany, France) Sales Data reports in the same spreadsheet
– Added check for comma as dollars cents separator for non-English versions of Excel – if a comma is used, dots will be changed to commas and vice versa. This would previously prevent Reimbursement data and Sales data amounts from reading correctly
– Added ability to import Amazon Europe reimbursement data
– Fixed bug where everything would recalculate if button to import a report was clicked but then cancel was pressed
– Fixed bug where data connections to reports wouldn't be removed
– Fixed bug with Inventory adjustments graph in v2.21 Enterprise

7/7/2016 – v2.23 Regular & Enterprise
– Fixed bug with Returns report (Amazon added LPN and customer comment fields)

12/20/2017 – v2.3 Regular & Enterprise
– Fixed issue with Amazon.com sales data not importing total field (Amazon added a field to the sales report)
– Removed "Damaged Miscellaneous" from the total for "Damaged FC" – this was throwing off counts for some people
– Fixed filtering error when clicking on a number to display only that SKU in the data reports on LostDamaged Sum – would only happen when filtering, then filtering again
– Code optimizations
– Errors are now logged to a txt file
– Added cell highlighting for negative and positive values on LostDamaged Sum
– Added ability to import more than one sales report at a time since Amazon changed the max report range to 180 days
– Added "Seller Notes" to Refunds sheet – this field is populated from the order seller notes using the new Auto-import feature (for pc users). This is to indicate who refunded a customer, since there is no way to tell in the data reports.
– Added AUTO-IMPORT for all data. You can now click a button to generate and download all reports the spreadsheet needs.

1/10/2018 – v2.31 Regular & Enterprise
– Fixed bug with Sales Data manual importing
– Fixed bug when Auto-importing Sales Data into a blank sheet
– Fixed bug with generating a new set of sales data reports when they didn't need to be generated

2/5/2018 – v2.32 Regular & Enterprise
– Fixed a date bug with auto-generating sales reports for UK and Europe (the format for these reports is actually mm/dd/yyyy for some reason)
– Reimbursement reversals for lost & damaged inventory are now accounted for on LostDamaged Sum
– If the last date of a lost or damaged item is greater than 18 months ago (the time limit for requesting reimbursements from Amazon), it is now turned yellow and given a separate designation
– Improved the way the Auto-Importer actually clicks "save as" in IE, inputs the folder, and file name. The old method was causing issues for some people, and open would be clicked, and/or the file name would not be changed.
– Improved a formula in the Refunds sheet to make it faster – this will speed things up when there is a lot of sales data
– The sheet no longer saves a separate copy of sales data, which makes for a smaller spreadsheet file when there are large amounts of sales data
– When saving the spreadsheet it no longer recalculates everything before saving

3/13/2018 – v2.33 Enterprise
– Fixed date formatting bug when importing fulfillment reports from Europe market on Auto-import
– Fixed total column for UK, Europe, and Canada sales data
– Fixed bug where the name of a file wasn't properly being changed on Auto-import
– Fixed bug with filtering properly from LostDamaged Sum
– Added LostDamaged Sum, Damaged Ship column filter for Reimb report
– Re-calculating data after loading new data is now faster. In some cases it was redundant
– Added condition for DAMAGED_INBOUND in Inventory Adj report for Damaged Ship column on LostDamaged Sum
– Added condition for WAREHOUSE_DAMAGED when reason is O to Damaged FC column. For an unknown reason sometimes Amazon classifies inventory differently when damaged
– Added Delete All Data to Instructions sheet, which will remove all data from all sheets and reset everything

4/27/2018 – v2.4 Enterprise Beta 2
– Refactored some large formulas, significantly improving speed of recalculating data, especially with large data sets, and for those with multi core processors (and Excel 64 bit)
– Added support for GBP and EUR on the same spreadsheet for Amazon Europe
– Consolidated Amazon.co.uk into Amazon Europe
– Added ability to Auto-import/update sales data from selected Amazon Europe markets all at once
– Added filter for currency and market on charts that have dollar amounts for the Summary sheet
– Added Auto-Import support for French, Spanish, German, and Italian versions of Windows.
– Added "Market" column on Refunds sheet
– Added "Original Order Total" to Refunds sheet
– Added Notes column to LostDamaged Sum (for taking your own notes)
– Added more specific status messages for the Auto-importer
– Added option to Auto-Import sales data when clicking on Auto-Import All Data. Sales data can now be imported separately.
– Added check to "check reimb amount" to not flag if Disposition is "SELLABLE" – If this is normally true, it usually means it is a reimbursement to you for refunded shipping to the customer
– Hid "Is reimb correct?" column to clear some clutter
– Made auto-import of fulfillment reports more efficient by downloading the available report if available instead of checking all of them first then circling back around to download
– Made auto-import of sales data more efficient and consolidated generate reports & download report processes
– Changed folder that sales data downloads to to use the market abbreviation, date separated by dashes or dots (depending on your local settings), and the hour – this prevents the same data from being imported twice
– Added support for different style seller support login pages
– Added check for changing Amazon Markets when there is still data loaded into the spreadsheet
– Added links to orders on Refunds sheet
– Added new "Problem?" to Refunds – "yes – incorrect amount returned & not reimb " – Order was refunded, and customer returned items from the order, but it was the incorrect amount, and Amazon did not reimburse you for the missing items.
– Added new "Problem?" to Refunds – "yes – not reimb or returned – reimb reversed" – This order previously had a reimbursement, but was reversed
– Added ability to detect reversed reimbursements for Refunds
– Adjusted code for recalculating refunds to be a bit faster for Amazon Europe
– Changed "Returned?" and "Reimb?" columns on Refunds sheet to a quantity – this fixes cases where multiple items were purchased/returned/reimbursed
– Changed how Refunds sheet is displayed when there are multiple SKUs in one order – Previously order-ids were grouped together regardless of the SKU. Now if more than one SKU was refunded with the same order, they will be listed on x rows instead of 1
– Fixed bug where header for sales data would disappear when removing data
– Added Reimbursement amount check on Refunds sheet – it will now check for a reimbursement in sales data if it cannot find one in Reimbursement data
– Added Reimbursement amount and qty on Refunds sheet when the reason is classified as "CustomerServiceIssue"
– Added feature to save Seller Notes when updating data on the Refunds sheet

5/1/2018 – v2.4 Enterprise Beta 3
– Fixed bug that prevented more than 7 SKUs from loading on the LostDamaged Sum sheet.
– Fixed calculation for reimbursement reversals for customer returns

5/3/2018 – v2.4 Enterprise Beta 4
– Added WAREHOUSE_DAMAGED disposition with Reason Q (damaged – misc) to Damaged FC Column
– Added DISTRIBUTOR_DAMAGED & CARRIER_DAMAGED dispositions with Reason Q (damaged – misc) to Damaged FC Column
– Added option to count UNSELLABLE inventory in lost and damaged totals.
– Changed how "From Holding" works slightly – This is now The difference between SELLABLE inventory transferred to and from a holding account. Amazon will put sellable inventory into a holding account when they previously reimbursed you for inventory and then found it. If this is a negative number then that is currently how much is IN your holding account. A positive number is how much went towards reimbursing you for lost or damaged inventory.
– Removed WAREHOUSE_DAMAGED reason O from Damaged FC (was added in last version – Reason Q is a better indicator)
– Fixed filtering of other reports when clicking on numbers in the LostDamaged Sum sheet
– Fixed issues with filtering and the numbers displayed not adding up to what was filtered

5/9/2018 – v2.4 Enterprise Beta 5
– Added case for unsellable inventory when it is moved from UNSELLABLE to SELLABLE (usually a corrected mistake by Amazon)
– Added feature to completely clear data cache when removing all data & resetting the spreadsheet
– Fixed issue with reimbursement reversals (lost, damaged, and refunds) when cash reimbursement is reversed, but then is replaced with inventory.
– Fixed bug with adding reimbursement reversal inventory to items reimb value
– Changed time to from refund to reimbursement for non-returned inventory from 30 to 45 days. This will lower false positives a bit.
– Made filtering data on the back end more efficient

7/5/2018 – v2.4 Enterprise Beta 6
– Fixed bug preventing data from updating correctly for the first time.

9/4/2018 – v2.4 Enterprise Beta 7
– Cleaned up some code and fixed misc bugs

Size

Lite, Regular, Enterprise

1 review for Amazon FBA Refund & Lost Inventory Reimbursement Checker

  1. Rated 5 out of 5

    Alejandro Pérez

    I operate in 5 countries: UK, Spain, Italy, Germany, and France. Lost/damaged inventory is very difficult to reconcile on Amazon Seller Central if you have large inventory and sales. I use this Excel tool every 2 o 3 months and I usually recover more than 2.500€ (+2200USD) every time I use it.

    Support from the developer is awesome. It was not working properly for Europe on earlier versions due to the complexity of the Amazon reports and the bugs were solved properly in a timely manner by him. It works very good now.

    I’m very happy with the results and it is an Excel file every seller should have to reconcile inventory. You will find out how many times Amazon fails on giving back money that belongs to you. I’m very happy with it. I would take days every month to reconcile manually.

    Thank you!
    Alejandro P.
    Seller on Amazon in Spain, France, Italy, UK and Germany

Add a review

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

FAQs

Amazon FBA Product Fee Checker

The lite version of the Reimbursement Checker, Inventory Value Calculator, and Tax Summary spreadsheets will work with LibreOffice or OpenOffice, with the exception that you’ll have to manually paste in the reports that are downloaded from Amazon.

Unfortunately, the macros and pivot tables that are used in all other spreadsheets will not run properly in LibreOffice, OpenOffice, or Google Sheets.

Amazon FBA Refund & Lost Inventory Reimbursement Checker

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!

 

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)

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.

v1.0 will work with Google Sheets, but v2.0 will not.

However, for v1.0 to work with Google Sheets, you must copy/paste in the reports manually as the macros that support importing data will not work.

Either Amazon has lost a lot of your inventory, or there is an overlap in the data. If there are a lot of discrepancies in the beginning of the report, the former is more likely.

To fix this, and make the report more accurate you should filter out lost inventory that has been lost in the past 45 days.  Amazon doesn’t reimburse for lost/damaged inventory for at least 45 days from the time it was reported lost/damaged.

To do this:

  1. Go to the Inventory Adj sheet
  2. Find column H (reason), hit the down arrow in the header
  3. Click select all, so everything is un-selected, then click ‘M’. Now only records with M as the reason show show up.
  4. Click cell A2 and drag to I2 
  5. Hit shift, then the down arrow to select down
  6. Keep hitting the down arrow until all records from the past 45 days are selected
  7. Hit delete on your keyboard
  8. Click the arrow again from column H, select “Clear filter from “reason”” 

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

0 means you have already been reimbursed.

A negative value means either:

  • you were reimbursed more than once for the same item
  • there is a gap in data and an item was lost/damaged before the data starts, but there is data showing items were found
  • 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.

 

General

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.

Enable them! The spreadsheet needs them to function correctly!

This warning comes up because the Spreadsheet connects to local Amazon reports (that you’ve previously connected to the spreadsheet), and because macros are used. You’ll want to click Enable Content. Otherwise, the spreadsheet will not update correctly after data is added.

The lite version of the Reimbursement Checker, Inventory Value Calculator, and Tax Summary spreadsheets will work with LibreOffice or OpenOffice, with the exception that you’ll have to manually paste in the reports that are downloaded from Amazon.

Unfortunately, the macros and pivot tables that are used in all other spreadsheets will not run properly in LibreOffice, OpenOffice, or Google Sheets.

Some sheets in our spreadsheets are locked for editing to make sure that certain underlying formulas aren’t accidentally deleted. If you simply need to copy data, you may have gotten the password message when right clicking on a cell. Instead, simply ctrl+c to copy.

If you would like to edit or remove data, we recommend either using the filter option drop down located at the top of the column next to the heading, or removing data from a file before importing. If you choose the latter, it may be easier to import it into a another (new) spreadsheet first, remove data, save as the same file type, then re-import into the spreadsheet.

If you want to add your own calculations/data to a spreadsheet, you can still do so by following these steps.

More than likely, no. The formulas and macros that we use will not work properly with Mac Numbers. We would recommend using Excel for Mac instead.

It sounds like you have macro’s disabled. Follow these steps in Excel:

  1. Click the File tab.
  2. Click Options.
  3. Click Trust Center, and then click Trust Center Settings.
  4. In the Trust Center, click Macro Settings.
  5. Make the selections that you want.
  6. Click OK.

We are also Amazon FBA sellers. Our products came out of the frustration of not having a tool for FBA to track our data that wasn’t $50+ a month. Not to mention, with those services you’re basically handing over your data to anyone that works there. We really wanted something we could control and easily have available.

The spreadsheets that were available were fundamental, and a lot of data had to manually be entered. Knowing what we know about Excel, and what it can do, we started working. The Ultimate FBA Sales spreadsheet took about a month to get to something that worked. Initially there were so many formulas that the spreadsheet was freezing every time something was changed. We had to rework everything using different methods, like pivot tables and macros to do some of the heavy lifting. We continue to add features and optimize the code in all of our products.

Within a period of 30 days from the purchase date, if for any reason you are unhappy with our products, you can request a full refund. 

The Ultimate Amazon FBA Sales Spreadsheet v2

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.

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.

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

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.

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:

 

This is because the spreadsheet is using styles that you may not have or there is something else that you version of Excel does not recognize.

Choose “Open and Repair”

If the entire spreadsheet is does not have any formatting and you use Excel for Mac, please make sure that there are no conflicting fonts in Font Book.

This may be because of the charts, or you need to update to the latest version of Excel for Mac.

Choose “Open as Read-Only”.  After doing so, save a copy of the spreadsheet.

The Ultimate Sales Spreadsheet will work properly on Amazon properties that output their reports in English.

Currently the spreadsheet will work with:

  • Amazon.com
  • Amazon.ca
  • Amazon.co.uk (separate version)

The spreadsheet will NOT work with:

  • Amazon.com.mx
  • Amazon.de
  • Amazon.fr
  • Amazon.it
  • Amazon.es
  • Amazon.co.jp
  • Amazon.cn
  • Amazon.in

If you attempt to use the spreadsheet with non-English reports, it will not calculate Returns, and detailed fees properly because it is looking for specific English phrases in the reports to categorize those fees.

You must have separate spreadsheets between properties unless you combine reports before entering them into the spreadsheet.

This warning comes up because the Spreadsheet connects to local Amazon reports (that you’ve previously connected to the spreadsheet), and because macros are used. You’ll want to click Enable Content. Otherwise, the spreadsheet will not update correctly after data is added.

The lite version of the Reimbursement Checker, Inventory Value Calculator, and Tax Summary spreadsheets will work with LibreOffice or OpenOffice, with the exception that you’ll have to manually paste in the reports that are downloaded from Amazon.

Unfortunately, the macros and pivot tables that are used in all other spreadsheets will not run properly in LibreOffice, OpenOffice, or Google Sheets.

Some sheets in our spreadsheets are locked for editing to make sure that certain underlying formulas aren’t accidentally deleted. If you simply need to copy data, you may have gotten the password message when right clicking on a cell. Instead, simply ctrl+c to copy.

If you would like to edit or remove data, we recommend either using the filter option drop down located at the top of the column next to the heading, or removing data from a file before importing. If you choose the latter, it may be easier to import it into a another (new) spreadsheet first, remove data, save as the same file type, then re-import into the spreadsheet.

If you want to add your own calculations/data to a spreadsheet, you can still do so by following these steps.

For v1:

More than likely the two SKU’s are slightly different.  Compare the two SKU’s, and make sure there are:

  • no leading 0’s
  • no spaces
  • no 0’s (zero) that are O’s (oh) or I’s (eye) that are l’s (el) or 1’s (one)

If a difference is found, find it on the PO data sheet and change it to match the SKU is in the sales data.

When the change is made click update on the Dashboard and everything should refresh. (you may have to click it twice)

 

For v2:

This will happen when the name of the item has changed at some point or multiple times. There may also be adjustments descriptions. This is normal due to the way we are finding unique names within sales data.

This is a running total. It is the amount of that SKU sold up until that point in time. This field is used to compare to the quantity (on the PO sheets) for the price calculation.

You must enter SKU data on the PO Data sheet for every SKU you sell in order for data to populate in the PO Sum & Prod Summary sheet.

If you receive a return and decide that it is sellable, change column N to “Yes” on the Returns sheet for that item. This will put it back into inventory. If you return it to Amazon under a different SKU, do not change column N – enter that SKU on the corresponding monthly PO sheet.

Sellable returns subtract from the amount sold. Reimbursements & non-sellable returns add to the amount sold. Return numbers are adjusted in the month that the item is returned. COGS are also adjusted accordingly.

Absolutely! As long as you can copy in the SKU, Quantity, and Cost. Optionally you can enter the shipping cost according to the instructions on the PO Data sheet.

There are a few things to check:

  1. Make sure there is an entry for that SKU on the PO data sheet. If there is, make sure that the number is entered exactly as it appears in the Sales Data. There could be a leading 0 or a space that you can’t see.
  2. When pasting in SKU’s into the PO data sheet, make sure to choose ‘match destination formatting’. Even if you use only numbers for SKU’s they must remain as text, otherwise Excel will not match them correctly. If you’ve accidentally changed the SKU to format as numbers, right click the SKU, choose format and select text.  Starting with v1.9, the proper formatting is automatically applied each time the “Load/Refresh ALL data” button on the Dashboard is pressed.
  3. If you sold more than the total quantity entered, then the cost of items after that quantity will populate as $0.00.  In this case the total available will be negative on the Prod Summary sheet. Increase the quantity for that SKU on the PO data sheet, or add the most recent PO Data
  4. Go to the Dashboard and click “Load/Refresh ALL data” again. Sometimes when new data is added it does not refresh other tables in an order that catches all of the individual changes.
  5. If none of this works, download the latest version of the spreadsheet. We’ve made improvements in the most recent versions that fix various forms of SKUs (for example, spaces) that were causing issues in past versions.

This part will require some manual entry if you do not already have PO data in a similar format. The important thing is that the SKU matches the SKU in the imported Amazon data. 

An easy way to get a list of SKU and Title, the monthly Sum (Summary) sheets contain product lists for that month. It can simply be copied into the PO sheet, and the cost price and quantity can be added.

If you are having trouble with COGS data populating see this entry.

The spreadsheet will handle 13 different prices per SKU – one for the previous year inventory and one for each month. If a SKU is purchased for different prices in a single month, the average of those prices will be used.

If you want a more exact COGS for that SKU, change the date for that PO entry to a month that you did not purchase that item, or to a month with the same price. Just don’t forget that this will affect your PO purchase data for that month.

For v1 there is a maximum of 6000 sales a month, across 1000 unique SKU’s a month (72,000 sales total), and 5000 unique SKU’s total.

For v2 the max sales are limited by Excel (1 million rows of sales data) and your computer. If you have over 100,000 sales a year, we highly recommend 64 bit Excel and lots of memory.

If you have Excel for PC:

You have to press a button to do so, YES! In v2.1 we introduced a feature that automatically downloads the reports that the spreadsheet needs. Depending on the amount of data you have, this can take some time to complete.

Sales, Returns, Reimbursements, Detail Page data, Advertising, and FBA Shipping Data is downloaded with this feature.

When you need to update data, it only requests the days between when you last updated data and yesterday’s date.

Sorry, this feature does not work with Excel for Mac because it uses automation features only available with MS Internet Explorer.

If you have Excel for Mac:

In order for the spreadsheet to update your data, you must download the sales reports from Amazon, then import them into the spreadsheet. This is done with a few simple clicks.

Optionally, you can load other reports like Returns, Reimbursements, Detail Page data, Advertising, and FBA Shipping Data to view more statistics.

 

 

Sorry, no. The spreadsheet uses very complex formulas, macros, and pivot tables that Google Sheets does not support.

Yes! However, the charts on the Dashboard, Daily Sum & Advertising sheets will not function since Excel for Mac does not support Pivot Charts.

Also, note that if you are using an older version of Excel for Mac 2016, the spreadsheet may not function correctly. We recommend the latest release of Excel for Mac 2011 or 2016.

For the sales reports:

  1. Go to Reports > Payments > Date Range Reports (tab) > Generate a report 
  2. Under “Select report type” select Transaction, then Generate
  3. After the report is generated, download it to a known folder on your computer

For the returns report:

  1. Go to Reports > Fulfillment > Returns > Download Tab
  2. Select the date range starting at Jan 1st up to the date you currently have data for, then Generate Report
  3. After the report is generated, download it to a known folder on your computer

 

Load More

You may also like…

  • Amazon FBA Inventory Value Calculator

    $0.00
    Add to cart
  • The Ultimate Amazon FBA Sales Spreadsheet v2

    Sale! $174.99 $124.99
    Add to cart
  • Amazon FBA Inventory Long Term Storage Fee, Age & Rank Analyzer

    Sale! $9.99 $4.99
    Add to cart
  • Amazon FBA Shipping Label Printer by oCustomize (PC)

    Sale! $49.99 $40.00
    Buy Now!
  • Amazon FBA Product Fee Checker

    Sale! $24.99 $14.99
    Add to cart
  • Amazon FBA Product Barcode (FNSKU) Label Printer by oCustomize (PC)

    Sale! $49.99 $39.95
    Buy Now!
  • Amazon FBA Tax Summary by State & Quarter

    Sale! $9.99 $4.99
    Add to cart
  • Amazon FBA Instant Scan & (FNSKU) Label Printer

    Sale! $29.99 $19.99
    Add to cart
  • Amazon PPC Advertising Analyzer

    Sale! $24.99 $14.99
    Add to cart