Sale!
toolsforfba_amazon_fba_dashboard

The Ultimate Amazon FBA Sales Spreadsheet v2

$149.99 $99.99

Description

The Ultimate Amazon FBA Sales Spreadsheet is by far, the most advanced and comprehensive spreadsheet available for Amazon FBA sellers. We’ve made it incredibly versatile, and built features you didn’t think were even possible with a spreadsheet!

You simply won’t believe that we made a spreadsheet that can filter, display, and summarize your sales data like the Ultimate Amazon FBA Sales Spreadsheet.

Easily track your:

  • Sales (FBA & MF)
  • Inventory
  • PO’s
  • Returns
  • Reimbursements
  • Detail page stats
  • Supplies
  • Mileage
  • Advertising
  • Fulfillment only orders (*NEW in v2!)
  • Inbound FBA Shipping (*NEW in v2!)

The Ultimate Amazon FBA Sales Spreadsheet even gives you the ability to accurately and dynamically calculate COGS over time. Most online solutions can’t even do this!  Best of all, no monthly fees, and no giving your data to a 3rd party!

The Dashboard – View all important data in one place!

Also see data by any given date range:

  • Top 20 Sales
  • Top 20 Returns
  • Top 20 Most Profitable Items
  • Items Low on Inventory
  • Well Stocked Items

Sales Tracking – 3 Different ways to view sales statistics!

Product Summary:

To view more screen shots click on the thumbnails under the main image at the top of the page

View a multitude of stats broken down by SKU and filterable by any Date or Settlement ID including:

  • Total FBA & Manual Fulfillment Sold
  • Average sales / day
  • Sessions, Buy Box Percentage, Unit Session Percentage, Units per Order
  • Total inventory on hand at the beginning & end of set date range
  • Inventory value (estimated by cost)
  • Turn rates
  • Gross Sales
  • Amazon fees & credits
  • Inbound shipping cost by SKU (*NEW in v2)
  • Advertising spend
  • Refunds
  • Returns (sellable & non-sellable)
  • Adjustments
  • FBA Fee & Margin percentages
  • COGS & Net Sales
  • Totals for everything in set date range

 

Daily Summary:

The Daily Summary is filterable by any date range, settlement ID, sku or type (Order, Adjustment, Inventory & Service Fees), etc.

You can view stats like:

  • Number of orders/refunds/adjustments
  • Returns & Adjustments
  • Gross Sales
  • COGS
  • Net Profit

 

Year Summary:

To view more screen shots click on the thumbnails under the main image at the top of the page

View all of the above stats by month, including:

  • Totals for everything
  • Breakdown of Expenses/Income
  • PO Summary
  • Transfers from Amazon
  • Returns Summary

Purchase Order Tracking

To view more screen shots click on the thumbnails under the main image at the top of the page
  • Track POs to see exactly how much you’re spending & ordering
  • Easily copy in manifest data from liquidation companies like 888 Lots
  • Automatically compiled product price list by SKU to see what you’ve ordered over time
  • Track PO Source and view summaries by date range
  • Accounts for inventory left over from previous year
  • Import PO Data from Inventory Lab

Returns Tracking

toolsforfba_amazon_fba_sales_returns

  • Summarizes all returns data by month, return reason, and SKU
  • Automatically adjusts sales numbers and COGS based on the type of return (sellable or non-sellable)
  • Track which returns were put back into inventory, and which were a loss
  • Summary of how much you’ve lost due to non-sellable returns
  • Enter returns back into inventory manually if you send a return back to Amazon
  • Calculates the top 20 most returned items

Advertising PPC tracking

toolsforfba_amazon_fba_sales_advertising

To view more screen shots click on the thumbnails under the main image at the top of the page
  • Track exactly how much you’re spending per SKU on Amazon advertising
  • See data organized visually in graphs
  • Ad spend deducts from overall SKU net profit
  • View which keywords/campaigns are performing the best
  • Filter & sort by date range, SKU, and match type

 

 

Detail Page Statistics

To view more screen shots click on the thumbnails under the main image at the top of the page
  • View Sessions, Buy Box Percentage, Unit Session Percentage, Units per Order
  • View estimated Detail Page Sessions by DAY, by SKU! 
  • Filter by Month and SKU
  • See data organized visually in graphs (also filterable by date/sku)*
  • View stats in Product Summary

FBA Shipping Data (*NEW*)

To view more screen shots click on the thumbnails under the main image at the top of the page

View stats on FBA Shipping like:

  • Total quantity sent & received by shipment
  • Shipments with quantity discrepancies
  • Average cost per item to ship per shipment
  • Cost of shipping factored into net profit by SKU on Product Summary
  • Days in transit
  • Days to process
  • Summary by SKU of all of the above stats
  • Breakdown of all of the above by fulfillment center state
  • View which fulfillment centers cost you the most, and take the longest to ship to/process

 

General features: 

  • Works with Microsoft Excel 2007+, and Excel for Mac 2011+ (certain early versions of Excel for Mac 2016 may experience issues – so the latest version is highly recommended) – Charts will not work in Excel for Mac
  • Includes a dashboard that neatly displays sales stats visually with graphs
  • Easily import Amazon sales, returns, detail page stats, FBA shipping, and advertising data without copy/pasting in reports – just download the reports and click a button to import
  • Track purchases of shipping & office supplies, mileage and other online tools by month
  • Track taxes collected by any date range, and SKU
  • Track transfers to your bank account & reconcile by Settlement ID
  • Import PO Data (COGS), Mileage, and Shipping costs from Inventory Lab (*NEW in v2)
  • Import mileage data from MileIQ (*NEW in v2)
  • The spreadsheet can handle as much sales data as your computer can – we do recommend a 64 bit Excel, high memory computer for over 100,000 sales a year. The upper limit of Excel is 1 million rows per sheet.
  • Comes with FREE updates and improvements – only pay once

 

Don’t pay $50 or more a MONTH to hand over your sales data to a 3rd party company. Pay once, and keep it on your computer with our easy to use spreadsheet!

 

See the FAQ for answers to common questions

 

Additional information

Release Log

4/10/16 – v1.0 – Initial release
See change history on v1 product page

3/16/2017 – v2
– Removed upper limit of sales records & monthly SKU amounts (this is now only limited by Excel and your computer)
– Added FBA Shipping Summary & Data
– Added Daily Summary (filterable by any date range, settlement ID, or SKU)
– Added ability to import & update Sales Data with one report
– Removed individual monthly report & summary sheets (24 in all)
– Condensed Product Sum & Income Statement into one very detailed Product Sum page
– Added importers for Inventory Lab PO Data, Shipping Costs, and Mileage data
– Added MileIQ data import for Mileage data
– Added additional detailed costs to Product Sum Income statement
– Added Adjustment COGS, Return COGS, and Sales COGS
– Added ability to display Product Summary by any date range (within the same year)
– Added ability to reconcile / filter Product Summary by Settlement ID
– Added Manual Fulfillment Sold field on Product & Year Summary
– Added shipping calculations to individual SKU's on Product Summary
– Added and updated calculations for Beginning and End Inventory to reflect inbound FBA shipping
– Added report for SKU's with no or 0 PO's on PO Data sheet
– Added refresh data status update for Excel for Mac
– Added status for individual sections of updating, like sales, product sum, and shipping
– Added two new charts to the Dashboard – Refunds/Adjustments, Detail Pages
– Added Fulfillment Only Summary report which shows orders that were not sold through Amazon, but that Amazon fulfilled
– Fixed compatibility issues with Excel for Mac (except for early versions of 2016, which didn't have full VBA support)
– Fixed turn rate calculations for Product & Year Summary
– Updated Inventory Value to calculate by cost since this is more commonly used
– Updated PO Source Sum to include PO's with no source

3/19/2017 – v2 Beta 11
– Fixed bug with Data import on Shipping Data with Excel for Mac
– Added more status indicators in the lower left
– Several changes to the back end to make updating everything faster

3/20/2017 – v2 Beta 12
– Fixed bug with copying Inventory History in FBA Shipping Data correctly
– Fixed dates in UK data Shipping SKU Lists
– Made importing UK sales data slightly faster for large data sets
– Made some cumbersome formulas in FBA Shipping Data faster
– Fixed bug with updating PO Data totals when clicking Refresh all on Dashboard
– Fixed bug with Refresh PO Totals button

3/22/2017 – v2 Beta 13
– Corrected report URLs on Detail Page Data & Instructions for UK data
– Changed Refresh PO Totals to format PO Data and update PO Sum & Source Sum tables
– Improved speed of refreshing PO Data
– Added feature to change pasted in dates in PO Data to correct format (otherwise PO Sum would not work and COGS would not populate)
– Removed requirement for sku titles to match between the same sku entered multiple times in the PO Data sheet
– Filtered blanks from Source Sum
– Fixed bug that would prevent Product Sum from updating correctly after importing data from another sheet
– When importing data from another v2 sheet, dates from sheets with date filtering are also copied

3/24/2017 – v2 Beta 14
– Fixed issue with calculating average shipping cost per item on the Shipping Data / Sum pages
– Added detailed chart to Daily Sum sheet
– Added Month drop down selection to Daily Sum
– Added Month drop down selection to PO Source Sum
– Selecting a month in the month drop down or a Settlement ID now filters everything automatically on the Product Sum page.
– Fixed DIV/0 errors on Product Sum page when no data is loaded

3/25/2017 – v2 Beta 15
– Fixed error with loading FBA Shipping data on Excel for Mac
– Improved speed of calculating shipping data
– Fixed issue with updating all data and the order that things execute so everything doesn't have to be updated twice
– Improved speed of updating everything by reducing redundancy
– Fixed error with importing shipping data from another sheet – the first set of data was not importing
– Improved speed of copying data from another sheet

3/27/2017 – v2 Beta 16
– Added Shipment URLs to FBA Shipping Data sheet to easily gather cost data if needed
– Fixed bug with calculating ship cost per item (regression from b15)
– Major improvement with calculating ship cost automatically per shipment
– Fixed calculation with Avg Ship Cost/Item – previously it was taking the average of averages, which is not correct and would result in averages that were too high
– Added conditional formatting to Actual Ship Cost/Item manual entry field to show red when it needs to be filled in
– Added "Total Cost" to FBA Shipping Sum tables
– Restructured FBA Shipment Detail to data to make a little more sense
– Fixed another bug with import Shipping SKU lists on Excel for Mac
– Fixed bug with filtering by Settlement ID on Product Sum
– Removed un-used data model from PO Data
– Made importing data from v2 sheet faster and more efficient

3/29/2017 – v2 Beta 17
– Fixed error with Date Shipped on FBA Shipping Data
– Optimized re-calculating Shipping Data a little more
– Fixed locked error on PO Data
– Fixed formatting of Source column on PO Data
– Updated field descriptions
– Increased Advertising data from 500k to 1mil rows (Excel limit)

3/31/2017 – v2 Beta 18
– Added error check to make sure PO Data works correctly after copy/pasting data around within the sheet
– Fixed bug in FBA Shipping Data where certain fields would not be calculated correctly after importing new shipping data
– Added filter for Received Inventory Data where the date does not populate because it doesn't have corresponding data from the Shipping SKU List
– Fixed header on Returns sheet

4/4/2017 – v2 Beta 19
– Added importers for Inventory Lab PO Data, Shipping Costs, and Mileage data
– Fixed Shipment Totals in FBA Shipment Summary, and Shipments by Month chart
– Added Shipping Cost in Shipments by Month Chart on FBA Shipping Sum
– Added filter on FBA Shipping Sum for SKU in Fulfillment Center State Sum
– Changed filter on FBA Shipping Sum for Summary By SKU to Months
– Added cell highlighting if required data is not entered on PO Data
– Fixed monthly calculation totals on SuppliesMileage if dates are entered in the format d/m/yyyy
– Fixed Total FBA Sold on Year Sum
– Fixed End Inventory on Year and Product Sum to reflect MF items sold
– Cleaned up some XML code on the back end

4/7/2017 – v2
– Added Fulfillment Only Summary report which shows orders that were not sold through Amazon, but that Amazon fulfilled
– Added MileIQ data import for Mileage data
– Fixed case where updating sales data was crashing Excel for Mac
– Fixed data import on Detail Page Data, when a report contained one record
– Fixed dates for FBA Shipping Data SKU Lists, when dates were in the format mmddyyyy, mm.dd.yy instead of the default mm/dd/yy

4/17/2017 – v2.01
– Fixed bug with "Other Expenses" on Product Sum being positive instead of negative numbers
– Fixed crash bugs with Excel For Mac when importing sales data, shipping data, or Update on the dashboard.
– Fixed bug with imported Received Inventory Data with Excel For Mac
– Fixed bug with importing FBA shipping data sku lists when first file is blank
– Fixed bug when importing a blank Received Inventory Data file
– Added "Remove Data" button to Returns sheet

7/7/2017 – v2.05
– Added Reimbursements report and data analysis
– Reimbursements now help factor into inventory counts, like when Amazon reimburses with inventory instead of cash
– Modified how adjustments modify inventory counts
– If new returns data is loaded, the sellable override no longer goes out of sync with the line item (everything is now sorted by oldest to newest)
– Fixed bug when Advertising cost was included with sales data, and displaying it properly on the Yearly and Product Summary sheets
– Fixed bug with Returns report (Amazon added LPN and customer comment fields)
– Removed redundant "Net Sales" from Year Summary, and changed the Total at the far right to Net Sales to be consistent with figures on the Product Sum page

7/13/2017 – v2.06
– Fixed bug where, if wrong year was entered on PO Data sheet for one line item, COGS would not calculated correctly for an item
– Fixed bug with monthly Tax calculation on Year Summary – previously only orders were being calculated, now returns are included.
– Fixed bug with return COGS calculation total on Product Sum sheet. Previously only items that has sales during the given period were calculated.
– Improved COGS calculation for items that, when returned, were entered back into inventory, For example, if you sold 1 item, it was returned, but then automatically put back into inventory, COGS would not calculate for the second sale unless you had a quantity of 2 entered for that item on the PO Data sheet.
– Added "Shipped to Amz" column on PO Data sheet to count the number of that SKU that was shipped to Amazon, dependent on FBA Shipping Data
– Changed Total Sold on PO Data to not be dependent on the date range set on Product Sum
– Added "Lightning Deal Fee" to Advertising totals on Product Sum and Year Summary
– Added "Manual Processing Fee" under Inventory Fees on Product Sum
– Added "Manual Processing Fee", and "Long-Term Storage Fees" on Year Summary
– Added "Days in Stock" to Product Sum – Avg Sales/Day now calculates from this field
– Added "Avg. Days in Stock" to Product Sum right column.

FAQs

The Ultimate Amazon FBA Sales Spreadsheet v2

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.

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.

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 preventing 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.

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

If you already have the data files loaded in the spreadsheet, saving new version as the same file name and clicking the update button on the dashboard will automatically import the new data.

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

 

You may also like…

  • toolsforfba_amazon_reimb1

    Amazon FBA Refund & Lost Inventory Reimbursement Checker

    Sale! $14.99$49.99
    Select options
  • toolsforfba_fba_fee_check

    Amazon FBA Product Fee Checker

    Sale! $24.99 $14.99
    Add to cart
  • toolsforfba_amazon_fba_tax_summary

    Amazon FBA Tax Summary by State & Quarter

    Sale! $9.99 $4.99
    Add to cart
  • toolsforfba_inventory_value

    Amazon FBA Inventory Value Calculator

    $0.00
    Add to cart
  • Advertising Data Analysis

    Amazon PPC Advertising Analyzer

    Sale! $24.99 $14.99
    Add to cart