If you need to get an idea of how much you owe in taxes, what is being collected by Amazon and what you owe, this function will help you gain a good idea of the amount.
Again, I can’t stress enough that this is not for accounting purposes. The numbers will be off slightly (+/- 3-7%) because we import transactions daily. If a customer decides to return a product on the 23rd day, Gorilla does not redownload the transactions to reconcile for this refund.
With millions of transactions being processed, it is impossible to redownload everyone’s transactional history.
This is why for taxes, you must export the file directly from Amazon to generate the latest reconciled data.
Display Amazon SalesTax by city
The default results will group the sales by city.
It shows the sales of each City as well as the tax amount collected by Amazon or owed by you.
You can also see which city brings in the most sales for you.
This is useful if you want to target ads on Facebook or Google. Targetting towards the city with the most buyers is a good idea to increase conversion and keep ad spend low.
Display SalesTax by state
To group the sales and taxes by state, use the format as in the example below.
=GORILLA_SALESTAX("LAST MONTH", "US", "STATE")
Here’s what it looks like.
If you are registered in multiple states, this will help you organize your cash to figure out how much you can expect to owe.
You can also see where the majority of your buyers are.
Helping with local sales tax remittance
If you file local taxes monthly, you have to enter the cities where you had sales. Trying to do it manually is the worst thing possible.
I literally had to spend hours sorting the reports, combining the data and grouping them by city and state to provide the numbers to the state.
With this function, you can eliminate the majority of the grunt work.
The numbers are not 100% accurate, but they are close enough for you to figure out how much you will owe.
Whether you sell $100k a year or $100M a year doesn’t matter when looking for the best Amazon inventory system. The most important factor to consider for your inventory system is the number of SKUs you manage.
The number of SKUs in your inventory is the number one constraint of your inventory management system.
Why smaller operations should use Google Sheets as an internal Amazon inventory management system
The more SKUs you have, the more complex your requirements become. You’ve outgrown the warehouse tracker you built.
You need a multi-channel fulfillment software or a Warehouse Management System or both.
It is a nightmare to try and handle 10,000 SKUs.
If you have more than 5,000 SKUs, stop reading this and look up “warehouse management software”.
That is what you need – not an inventory tracking spreadsheet.
But if you don’t have thousands of SKUs, keep reading because this solution can work for you.
ShippingEasy for multichannel order management
For multi-channel orders, we use ShippingEasy for our operations to scale our FBA business. It handles all of the green processes in the chart below.
Our main use of ShippingEasy is for two things.
centralize incoming orders from all different selling platforms
centralize purchase orders
ShippingEasy’s inventory tracking and stock management system is on the weak side as there isn’t enough flexibility. However, for order management, ShippingEasy is the way to go.
For our inventory planning and operations, we like to:
monitor inventory data by SKU
monitor inventory data several times a day, last 7 days, last 30 days, by each month, quarter, last 7 days, last 30 days, last 90 days, compare to last year
create forecasts of the SKU we are interested in analyzing
check past inventory sales velocity over any period we want (day, week, month, between any two dates)
To date, I have yet to come across a solution that offered the flexibility that we required in terms of being able to get the date ranges and data for specific SKUs quickly.
We always ended up having to export csv files and then feed it into our spreadsheet models for every date range we tracked.
As I mentioned, unless you have thousands of SKUs in your inventory to manage, there’s a good chance Google Sheets will work for you.
I’ve helped Amazon companies implement a custom inventory system based off our Google Sheets Amazon Business Spreadsheet Package. One company even sells more than $50M in revenue with a corporate structure. But a spreadsheet still works because not everyone can get access to an enterprise software or has the knowledge to use it properly and get the info required.
Whereas, spreadsheets are so easy to create, share and can be accessed by anyone in the company.
The only difference between us and this big company?
They generate huge revenues by selling expensive products with large margins. Whether you sell 1,000 of one SKU in a month vs 10,000 of one SKU, the concept is the same except the velocity is faster.
The reason why I’ve been able to help other companies supercharge their spreadsheets is that we do more unit volume than most companies (at lower margins) and understand how to create efficient spreadsheets and trackers for people in our business.
If you believe that Google sheets or Excel spreadsheets will work for you, let’s get our hands dirty.
Basics to designing an effective Amazon inventory system
When creating spreadsheets, there are basics you must learn and apply.
Anyone can make a spreadsheet, and that's the problem.
You wouldn’t build a house without going through your list of needs and then locking down your structure and design with an architect.
To make your spreadsheet life easier, you should apply the same concept if you don’t want to end up with a spreadsheet where it seems like the toilet is smack in the middle of your kitchen.
The inventory sheet is going to be structured into 3 sections.
Unless a spreadsheet is structured like this, you are going to have a monster on your hands. It will grow out of control quickly.
The rules for each of the three sections.
1. DATA SHEETS - STORES DATA ONLY
2. LOGIC SHEETS - ONLY CONTAINS FORMULAS AND GETS ITS DATA FROM THE "DATA SHEET". DOES THE OPERATIONS.
3. PRESENTATION - USES THE OUTPUT FROM THE LOGIC SHEET TO CREATE CHARTS, TABLES AND VISUAL PRESENTATION
For this inventory management tracker, the architecture looks like this.
The DATA sheets
In this inventory/warehouse project, there are going to be two data sheets.
One for product information such as the SKU, ASIN, FNSKU, that matches the Amazon information.
One is for inventory data such as how many of each sku is instock, transfer, inbound and some basic sales data.
The LOGIC sheet
The middle “INVENTORY LOGIC” is the sheet that contains the formulas to combine and crunch the product data and inventory data into a useable format.
No data is manually entered into the logic sheet.
The logic sheet is 100% dynamic.
Typically, you likely built spreadsheets where the data and formulas are mixed together on the same page, but it is much easier to manage when you separate the spreadsheet tabs like this.
Let’s say you want to add 10 new products to your current spreadsheet, change the ASIN and add dimensions.
you have to add 10 new rows or columns
you enter all the product info
you have to create formulas for the new SKUs
you have to update all your formulas, graphs, tables because the entire data moved and is out of whack
Expect to spend the next 1-2 days (around 10 hours) trying to clean, test and fix up the bugs.
But if you use a logic sheet which simply runs the calculations by looking up the data sheet, all you do is update the data sheet with the extra data and that’s it.
The logic sheet is set up so that it updates itself or with minimal maintenance.
This type of separation also makes it easy to scale your spreadsheet and share your spreadsheet without fear that someone will break it.
The PRESENTATION sheet
The hardest part isn’t the logic, but the presentation. This is where you have to simplify the data to what you need. Not to cram as much as you can onto a screen.
What you don’t want.
This is what you envision being able to share with your team.
The goal of the presentation sheet should be to keep it as simple as possible. Get straight to the point and deliver the key important message.
It should be a dashboard so the business owner or executives can understand what is going on in a split second without needing anyone to explain it to them.
If you need to explain what is on the screen every time, the presentation has failed.
We’ve gone through the design and the architecture. Keep it in mind as it will help you see the picture of how everything comes together.
Let’s start building the structure.
Building the INPUTS sheet
Every spreadsheet system should have a dedicated space for input data. Rather than plugging in an absolute value like “25” for 25 days in a formula, you can reference a cell like A4.
This way, if you need to change the number from 25 to 40, you don’t have to find every formula that has “25”. Referencing cells makes it a time process.
In my inputs tab, I like to keep it very simple. I have some formulas to help reduce steps like counting the number of SKUs, the low stock threshold alert, list of marketplaces.
In our internal Amazon inventory system, there is more information like a list of date ranges that we look up, quarterly deadline dates, seller ID’s, table references and more advanced aspects that feed into our formulas.
It all depends on your needs.
Anytime you find yourself having to enter a hard value into a formula, move it to the Inputs section.
Building the product DATA sheet
Enter your product data into this tab like so.
Enter the product details that don’t change often.
Do not enter stock count, inventory status, pricing etc.
This is your database of product information.
You can put as many heading columns as you want. It doesn’t matter as long as you keep it simple and only enter data that does not change often.
Your SKU is unlikely to change, your ASIN is unlikely to change. Do not enter things like targets you are aiming for, sales velocity and other pieces of data that change constantly.
In our internal “master” product data tab, our headers include:
Peak / Offpeak
Rec Order Lev in Days
Parent or Child
We’ve automated the data so that it gets pulled automatically, but you can enter this data manually as it doesn’t change often.
The SKU is the unique number that we use throughout the spreadsheet to find, link, sort and filter data.
Getting back to the example, if you fill it out, it should look like this:
To get the little thumbnail images:
go to seller central
go to manage inventory
right click on the thumbnail and save as image
insert into your spreadsheet
Building the inventory DATA sheet
The INVENTORY_DATA tab must have the SKU and ASIN to make your life easier
You can fill this data by exporting your inventory reports from seller central
Change the header titles as needed based on the data you need
The hard part with this is that the data for the inventory status needs to be updated regularly. This likely means you have to manually enter the data.
Pro tip: use Gorilla ROI inventory and FBA addon to get the latest data automatically.
When the data is filled up, it looks like this.
Because this inventory template is a simple version, you could combine this data inside “INVENTORY” rather than separating it out as I show here.
However, it’s good practice to see how it comes together.
There is a detailed definition and guide to getting inventory data using Gorilla ROI add-on.
Total Available = InStock + Inbound + Transfer
InStock = inventory available and ready to ship by Amazon
Inbound = inventory on the way to Amazon’s fulfillment center and has not been checked in
Transfer = inventory has been checked in and being transferred to other fulfillment centers
Building the inventory LOGIC sheet
Now we combine the data from PRODUCT_DATA and INVENTORY_DATA into this sheet.
This way, if PRODUCT_DATA or INVENTORY_DATA is updated with new data, this sheet will automatically update itself.
Let the spreadsheet do the work for you.
Columns A to E is the information from PRODUCT_DATA.
Since we have some seasonality like any business, our internal version has more layers to it. We combine it with weighting factors based on the current month.
E.g. we sell more in December and January, so our calculation puts more weight towards the last 7 days and last 14 days calculation during these months than the last 30 days.
During off-peak season, the weighting focuses more on the last 30 days.
AMZN Inv Days Left – Amazon Inventory Days Left
Inventory days remaining data at Amazon via seller central, is always one day late because Amazon generates the report once a day.
If you have fast moving inventory that you need to keep an eye on, the daily report by Amazon is not helpful. They also use straight-line calculation which simplifies the trends and cannot account for spikes and seasonality.
Hence the formula that I use to calculate the Amazon inventory days remaining.
=IFERROR((TotalAvailable ÷ Av30DayUnitsSold) x 30,"")
Remember that TotalAvailable = InStock + Inbound + Transfer.
The main aspect of the formula is (TotalAvailable ÷ Av30DayUnitsSold) x 30.
By using the average 30 days units sold, it compares against the total available inventory. This number is then multiplied by 30 to give you how the total days of inventory you have left at Amazon.
Here’s a quick example.
Let’s say you have 100 units total available.
Average units sold over the last 30 days is 25.
100 ÷ 25 = 4
4 x 30 = 120 days
The result is that there are 120 days of inventory remaining for your Amazon FBA account.
The IFERROR() function is used to make clean up the results. Rather than show an error on the spreadsheet, I like to display a blank space using ” “.
If the last 30 days units sold is 0, then it would throw an error. Using IFERROR() in your Google sheets lets you control the output.
The spreadsheet inventory tracker gives a visual indicator where it turns orange when the days left in inventory is between 18 and 25.
It turns red when the days left is below 18.
You can change the conditional color rules based on your needs.
QTY to Ship – Quantity to Ship
The last column to round out the logic section of the spreadsheet is to create a column that lets you know how many units need to be shipped to meet your inventory threshold.
In my example, I have alert threshold set to 25 days as indicated in the INPUTS sheet.
A value in the “QTY to Ship” appears when the AMZN Inv Days Left column is less than the threshold alert level (25 days).
The QTY to Ship is then calculated to display how many units you need to ship to stock up for 30 days.
QTY to Ship = Av30DayUnitsSold - TotalAvailable
It’s as simple as checking how many units are available at Amazon, then subtracting it how many were sold the last 30 days.
If you have 100 units at Amazon, and you sold 300 units in the last 30 days, then you should send in 200 units.
QTY to Ship = 300 – 100 = 200 units to ship
If you need to ship more units than 30 days, then Av30DayUnitsSold has to be changed to be Av45DayUnitsSold.
A shortcut formula to do this is:
QTY to Ship = Av30DayUnitsSold x 1.5 - TotalAvailable
Since 1.5 x 30days = 45 days, you can multiply the last 30 days units sold by 1.5 to get an estimated last 45 days sold.
This way, the QTY to ship would now be the total units to reach 45 days of inventory at Amazon.
With all this, the final result of the inventory logic sheet has a nice, clean table with the key data points we need to make decisions without getting overwhelmed.
Advanced option for better accuracy
For higher accuracy and more robust inventory system, you can set it up by peak vs off-peak.
Assuming you are good with spreadsheets, you can set up formulas to detect whether the current month is a peak or offpeak month. If it is a peak month, make it calculate using the Last45Days units sold.
If it is offpeak, use the standard formula above.
This way, you can control how much inventory stock is sitting at Amazon warehouses. No need to pay extra for storage fees.
The cost of holding inventory at your own warehouse or even a 3PL will likely be cheaper than sitting in Amazon’s warehouse during peak seasons like Christmas.
Building the dashboard PRESENTATION
So far, we’ve created all the sheets except for the presentation dashboard as shown in the architecture.
INPUTS hold numbers and other references that we want to change as needed.
We got the product data inside PRODUCT_DATA.
Inventory data is coming from INVENTORY_DATA.
INVENTORY pulls in the needed data from INPUTS, PRODUCT_DATA and INVENTORY_DATA to give a nice usable table.
You could end it with the INVENTORY sheet. But I want to simplify it further by having a dashboard display a table with the 15 lowest inventory stock.
A simplified version of the INVENTORY table will help you quickly see which products are out of stock or about to go out of stock and act accordingly.
Imagine if you have a 1,000 SKU list or you manage multiple accounts.
Do you want to go through a massive table every day and find the SKUs manually that need to be prepped and shipped?
Or would you rather have a neat and summarized data table ready for you?
This type of simplified table helps you:
identify what needs to be done
drill down to the next stage by looking up the data
This final table is easy to build. The majority of the grunt work is in making it look pretty.
The main formula that I personally use to create dashboards is by using QUERY().
It’s an advanced formula, but once you understand the concept and usage, it is a lifesaver. It saves so much time, can create tables and data on the fly.
All I need to use is one QUERY formula as shown below to auto-generate a filtered table of results.
=QUERY(INVENTORY!$A:$Y,"select C, A, N, M, O WHERE N IS NOT NULL ORDER BY N ASC LIMIT 15",1)
The formula is saying:
Use the data from INVENTORY sheet, from column A to column Y.
Then grab the info and numbers from column C, A, N, M, O
Make sure the numbers in column N are not null (not an error or non-existent)
Display all the results in ascending order based on column N
Display only 15 results
This QUERY formula is a hard worker. Creating a table by using equals formulas and VLOOKUPS should be left for the logic sheets. Presentations should be built with QUERY where possible.
The possibilities are endless once you understand how to structure spreadsheets and make use of data.
You can create charts to track inventory. This is a chart of ours that tracks when a SKU needs to be reordered, and how many days of inventory remains at Amazon and our warehouse.
Automate with our Amazon inventory add-on for Google Sheets
If you have a system to track inventory at Amazon and your physical warehouse location, the next logical step is to automate and load the inventory data directly without having to log into your account and export reports.
That’s where Gorilla ROI comes in. It is a Google Sheets addon that pulls your FBA data and loads it directly into Google Sheets.
If you have more than 20 SKUs and you are copying and pasting data manually into your models, you know what a hair-pulling experience it is.
If you are growing, your forecasting or inventory management system needs flexible and dynamic data, without requiring you or someone in your team to manually feed it information.
Here’s how easy it is to get started with our addon for inventory, sales, product, reviews, fees data and more.
Authorize Gorilla ROI to your Amazon seller account
Install the addon on your Google account
Activate the addon from the Google Sheets menu
Wait for data to be imported (6-24hrs depending on your size)
Pull inventory data with a formula as easy as =GORILLA_INVENTORY(“MYSKU”)
As you type in =GORILLA_INVENTORY and you will see the popup menu appear.
Follow the syntax and example and you can pull the required data you need.
Being able to access your data without any code or help from programmers or other people means that you can create your own flexible and internal inventory application. Why not look like a genius in front of your peers?
We also support tracking daily inventory levels. You can filter by total availability, instock, inbound or transfer.
This way, you can see when an item went out of stock for better inventory forecasting and you can focus on making better decisions with your inventory.
When you combine the inventory data along with flexible custom sales data, you can create a powerful internal management system to solve your warehouse tracking issues.
Click the button to start creating your own Amazon inventory management system today with our add-on.
Download the Amazon inventory management spreadsheet template
Click the button below to make a copy of the spreadsheet to your Google account.
If you use excel, you can save it to your account first, and then download it as an XLSX file by going to menu > file > Download > Microsoft Excel (.xlsx)
The screenshot above is what you’ll be able to do and make your seller FBA tax calculation easier.
By then end of this Amazon FBA sales tax article, I’m going to show you:
how you can import your Amazon sales tax data with ease
calculate the sales tax amount that has been collected and what you owe
how much sales tax has been collected by Amazon marketplace facilitator
how much you have collected and owe the state
how Amazon pays the sales tax
your role in remitting taxes
FBA sales tax for Amazon sellers
Now that the Wayfair vs South Dakota dust is settling, it’s obvious that sales tax on Amazon sales is here to stay and you should be on top of it so that you don’t get a surprise tax bill in the 5 or 6 figures.
Thank goodness, it is much easier with Amazon handling the collection of sales tax and remitting it to the state on your behalf as the marketplace facilitator – something they should have done a long time ago.
Amazon has not implemented the marketplace facilitator system across all 50 states yet, but the trending is going in that direction.
Click the drop down box on the right that says “add states” and then it will open a list of states with check boxes.
The unchecked states are the states that Amazon does not collect sales tax on your behalf.
E.g. New Hampshire is not on the list. This means that for all sales to NH, you’ll have to get the tax info and remit to the state yourself.
Amazon FBA sales tax is going to be the norm across every state your products are sold and shipped to.
The point is that your listings for FBA sales need to have the correct tax code so that you are not under-receiving sales tax. If you are not collecting the correct amount, you will have to pay the difference out of your own pocket.
Amazon tax code – select the right one
The full list of Amazon product tax codes is long, overwhelming and confusing.
The quickest and easiest way is to select A_GEN_TAX for all of your products. This will classify your product as a general consumable and there are no additional sales tax considerations. This tax rate will cover you safely.
It’s also the safest rate because it’s going to be the highest tax rate. Some products like food, computers and baby goods have lower tax rates.
And no, you don’t get to keep it. You must pay it back, but the downside is that other sellers who have the correct product tax code will have a lower “total price” at checkout than you.
With individual product tax codes set up, Amazon will know when and when not to collect the sales tax and the correct amount.
If you have thousands of items to list, this is a lot of work.
If you don’t sell in any special categories, set it to A_GEN_TAX.
It’s a one time process when you create the listing, so it is worth the effort to set up correctly.
Product tax codes on handmade items or no barcode products?
If you have a product without a barcode or there is not a special product tax code, then select A_GEN_TAX.
Let’s say you make vases, or ceramics or handmade soaps, then all of this goes under A_GEN_TAX.
A tricky part may be a product that you think is part of the baby category. If you make and sell baby diaper bags, or some accessory related to babies that is not related to anything medicinal, food, cosmetic, goes inside the mouth or body, then it has a very high chance of just being a regular product A_GEN_TAX.
After all, what is the difference between a diaper bag and a regular shoulder bag except for additional pockets?
Does Amazon collect sales tax for sellers
Yes and no.
Amazon will collect sales tax on all products sold to the states shown on your tax settings page.
If a state is not listed, Amazon does not collect sales on your behalf. It is up to you to collect sales tax and remit it to the state. (But this is another topic…)
If you sell in Florida, as of today, Amazon does not have the system set up to collect on your behalf.
Amazon also does not collect sales tax on FBM orders. Only FBA orders.
If you fulfill orders via FBM or even through your ecommerce stores, you will need to keep record of the taxes collected and remit it yourself.
Because Amazon only pays on your behalf if they have collected on your behalf. This is why you need to check your tax settings to see which states are being collected on your behalf.
If Amazon does not collect sales tax for Florida, then you will be required to pay to the state of Florida the sales tax you have collected.
If you go to your transaction report, there is a column that says “tax collection model” and “marketplace withheld tax”.
“Tax collection model” tells you whether Amazon has collected it or not. If Amazon has collected sales tax for you, it has the value “MarketplaceFacilitator”.
The column “marketplace withheld tax” will then show you the amount that Amazon has collected and pays to the state for sellers.
Here’s a screenshot of the report and showing the taxes collected and paid/unpaid which I’ve highlighted yellow.
Estimated tax to be collected Amazon?
If you follow the screenshot above, and filter for all the orders where it does not say MarketplaceFacilitator in your transaction report, you can easily find out the total amount of sales tax that was not collected.
Since Amazon will pay sales tax that it has collected for your account, you only have to calculate the amount that you owe.
Here’s how to get your sales tax data from seller central and filter the data.
Select the entire header row of the table.
Click on the menu > data > filter
Click on the drop down icon and then deselect “MarketplaceFacilitator” and click OK.
You will have a list of all the orders where sales tax was not collected.
How to get FBA tax data automatically
If this sounds like too much work – it is.
There is an automatic way that I’ll show you in this section.
However, if you need numbers for accounting purposes and it must be correct to the cent, there is no other way to do it than manually.
No software will be as accurate as generating the transaction report directly from seller central and then going through it yourself as I showed you above.
But what if you want the numbers to prepare yourself and accuracy isn’t so important and having the cash set aside?
This case is different as you are looking for data to show you the health of the business and so you don’t get caught off guard.
Knowing roughly that you’ll owe the state $500 in taxes, and being prepared for it is better than having no idea.
wait around 24hrs for your data to get imported and calculated
In any Google sheet, start to enter the formula =GORILLA_SALES and you will get this popup showing you how to use the formula.
This formula allows you to pull total revenue and the taxes collected by state and city.
If you do something as simple as =GORILLA_SALESTAX(“LAST MONTH”), the result looks something like this.
This single formula is the default display and
combines your sales
organizes it into state and city
shows whether the tax was collected
shows total sales per city and state
shows the total tax collected
The above formula without any inputs, other than the time period displays it by default as “city” setting.
If you take it one step further and group it by “state“, it will look like this.
Notice how all the values are now consolidated into states.
This way, you can quickly see how much sales you did per state, and the level of taxes.
WARNING: This is NOT an accounting tool.
Reports are not reconciled for every refund or canceled order. The data is imported at the end of the day. If a customer returns your product later on, we do not go back into history and reconcile transactions continuously. It is not possible.
What are some ways to use this sale tax data?
Please heed the warning above. Depending on your refund rate, you will see differences.
E.g. if your average monthly refund percentage is 4%, then expect the total sales to be off by about 4%. That is because we import your transaction data daily. We do not generate the data when you request it through your sheets. That is only possible for you to do manually through seller central.
You can see which states have the most sales and you can adjust inventory closer to that region if you use a 3PL.
You can get a very good estimate of how much sales tax you will owe to the state you are registered in.
You can use it in your profit and loss statement to get net income as net income is after taxes.
Make sure that enough tax money is set aside so you don’t get a nasty surprise at the end of the month/quarter/year.
See how automating the data collection process can improve your Amazon FBA business
We have much more data that you can pull using Gorilla ROI addon for Sheets. It’s not just sales and tax data you see here.
You can load your seller data such as:
Your product BSR
Inventory across marketplaces – instock, inbound, transfer
Sales data across any period – last month, 2020 Q1, your fiscal year
Charges and fees for a P&L
You can supercharge any of your existing spreadsheets by data process automated.
Whether you do this with manual data or with auto updating data, structure your spreadsheets so that each tab is dedicated for a single purpose of data, logic or presentation.
The sheet that keeps track of your data is compiled and stored in one place. Not all over the place in 10 different tabs.
How often have you had to search every sheet and cell and update it with the newest data point?
If you keep all your data together in one big place, you can use this central location as the “database” of your spreadsheet.
This way, if you update the data in this single location, all your other sheets will be updated automatically.
Likewise, don’t mix the data with the logic (formulas) on the same sheet, otherwise, it becomes extremely messy to separate.
Make it so that one sheet has all the data, and another has all the formulas and logic so that the sheet with the formulas do not have to be updated again. Make it so that the tab with the formulas will be 100% hands-off. It just updates automatically based on what the data sheet says as you update it with Amazon sales.
Presentation is self-explanatory and there is a section of Do’s and Don’ts in the upcoming section.
Use the presentation sheet to create charts, tables, graphs and so on. It does not need any data or formulas. It uses the output from the formulas.
Again, the three basic and best practices to make your spreadsheet life simple.
1. Data sheets - stores data only
2. Logic sheets - only contains formulas and gets its data from the "Data Sheet"
3. Presentation - uses the output from the logic sheet to create charts, tables and visual presentation
Understanding how to build sales spreadsheets better
If you aren’t sure of what I mean so far, think of it like your house.
You have the following rooms:
bedroom = sleeping, relaxing
kitchen = cooking
dining room = eating
TV room = watch TV
and so on
Organize your spreadsheet like a house.
What everyone does with sales spreadsheets is to create one where everything is everywhere and all mixed up.
The kitchen is used for cooking, sleeping, playing with pets, shaving your beard.
The dining room is used as a swimming pool, toilet, project room, garage.
As you see, it is a mess. Your spreadsheets are likely the equivalent.
A home can have more than one room for each purpose like 5 bedrooms, 2 kitchens, 3 bathrooms.
In your spreadsheets:
one tab could be for daily sales data
one tab be for monthly sales data
one tab be for return data
one tab for product information data
one tab to calculate the logic for monthly sales projections
one tab to present projections
In this example, 4 are used for data.
1 is for logic.
1 is for presentation.
Let one tab serve one purpose (data, logic or presentation). This way, as your spreadsheet grows, it will not become a mess.
A real sales tracking spreadsheet example
Here’s an example of a potential sales tracking spreadsheet for your Amazon sales. This is how we organize our sales tracker that you can save at the bottom of this article.
Dashboard = Presentation = Updated dynamically and what we look at. Updates based on sales data.
I’m not a fan and I only use it to get rough numbers on potential products and ideas that I have in my head.
I never use it for sourcing actual products.
The main issue is that anytime you run the tool on a product, it gets stored on a database for a million other sellers to look up.
I do not run the tool on any of our products for the same reason. No need to share our product and data with the masses.
However, I do use AMZ Scout over Jungle Scout. Purely for the reason that more sellers use Jungle Scout vs AMZ Scout.
To get accurate ideas and data on what is working and what isn’t, using the Amazon Storefront strategy is one of the best options. Amazon lists in order of best to worst selling items in your storefront.
All you have to do is look up competitor storefronts and you can immediately know which one is their top seller.
This way, it’s easy to expand your own product line and you have the most accurate data right in front of you.
The other option is to visit a big box retail store. Our top sellers have come like this. If a big retailer like Target, Walmart, Bed Bath Beyond, Home Depot or Lowes is carrying it, it means the market is big.
From there, we narrow it down a little and find a specific product that looks to be doing well. We then find ways of improving it and putting a unique spin on it.
Knowing that China sellers are copycats, they just want to private label something. i.e. ODM products.
Since we want to be unique and sell it in the long run, we create our own version to own the mold and prevent China sellers from selling the same thing. The more expensive and harder it is for a seller to copy our product, the bigger the barrier to entry.
One of our best selling products used to sell 100 units a day. That has now dropped to 20-30 because of so many copy cats. It was an easy product to copy and they got away with it.
Since then, we’ve decided that if a product is too easy to copy, we don’t want to sell it.
Listing software – NONE
We still use CSV files or manually create listings from seller central as we don’t deal with hundreds of SKU’s.
Yes, it’s a plug for our tool, but only because it does everything that we need.
Inventory planning and forecasting
The latest spreadsheet we created internally is a PPC spend analyzer and optimizer. Not the typical style that you see with other software.
This spreadsheet is specific to our internal KPI. By being able to pull sales totals over various periods and combining it with the PPC spend, it has already become a very powerful tool in understanding our current performance and where we need improvements.
We used to use InventoryLab to get the fees and charges, but their numbers are off and Gorilla ROI pulls in all the fees and charges, much more accurately and is customizable.
By creating custom spreadsheets with Google, it’s become like an “internal app” that we can create and edit on the fly without having to know any code.
There are different spreadsheets for specific employees and departments so that everyone understands what their roles and KPI’s are.
We have our own designer that creates all the marketing images, packaging, mockups, and digital stuff.
No such thing as FBA software or expensive agencies.
Rather than posting a job and waiting for somebody to come by, we have found our best designers and a team we work with by manually going through hundreds of portfolios and contacting the designer directly if we felt they matched our design style.
I used to do it all myself and I’m capable, but not great.
Since we started outsourcing this area I can focus on doing the things that scale and to do tasks that bring in revenue – not tasks that save money.
All in all, we keep things simple on the Amazon side.
Ecommerce outside of Amazon
This is where things get different.
Shopping cart – Prestashop , now WooCommerce
I have used Shopify in the past. We used to sell high-end ceramics so we spent a good deal of time and money on the Shopify site. The pro is that it’s easy to set up and get running, but if you do any decent amount of sales, it becomes expensive fast and severely handcuffing.
Shopify makes you pay for every-single-little-thing.
So for our current store, I chose Prestashop but switched over to wordpress and woocommerce in 2019 with highly customized functions.
It’s free, all the things you pay for in Shopify are included. It works out of the box. Plenty of templates you can purchase and customize.
It’s great and functions very well, but I’ve changed my strategy of converting our site towards content-heavy, landing style pages, and I need something very flexible and more of a CMS than a shopping cart.
Hence the move to wordpress and woocommerce.
I’ve already applied it to one other site and it fits what I’m trying to do, so I’m in the process of moving our main money site to this new format.
It should supercharge conversions and boost our SEO as we get content heavy and keyword rich.
[update] After switching all sites to woocommerce and focusing on making each listing page a long sales page with tons of content (Amazon does it just like this) SEO has received a boost, more keywords are ranking and traffic is increasing.
With this new format, I’m also able to create single Amazon niche sites on the fly. I can literally go from nothing to a working and functioning woocommerce site in less than a week with a 100% custom design.
People send emails, it gets registered into the system for someone in our team to answer. Rather than sending contact forms, questions and emails to a person’s inbox, it is better to centralize such communication into a single location.
Payment Processor and Gateway – Stripe
No introduction needed.
Gone are the days where you had to use old services that stopped innovating and helping sellers like Authorize.net.
Stripe is just easy. No contractual obligations. Fees are decent. If you do volume, you get a discount. They really changed the landscape for online selling.
Integrates very nicely with woocommerce.
If you have multiple stores, you can connect them all to one Stripe account which makes things so easy.
To find the velocity of sales per SKU, you have to download reports from different time ranges and combine them to get a value.
This is a lot of work and eats up a lot of time. If you are doing this daily, or even 2-3 times a week, save yourself hours of work by simplifying and automating the process.
One of our most used spreadsheet templates is the inventory management sheet that helps you keep product in stock as well as planning. It gives you a complete picture of your inventory and sales by SKU or ASIN.
This means you have all the important pieces of information loaded in a single spreadsheet that updates itself automatically.
This is an example of what you can do. It’s so easy to know what needs to be sent, and how many units.
You can automatically load this type of information and create your own custom dashboard, tables or simply feed the data into your existing spreadsheets.
A detailed tutorial to pull inventory data is below.
One thing I’ve learned related to historical inventory is that sellers want to know when they went out of stock to learn about their seasonality.
Because every product is different, keeping track of inventory throughout the past by day, weeks, months helps the sellers to get more insight into their FBA Amazon business to plan for inventory.
The hard part is that it’s a massive chore to write down the dates and number of units remaining in stock for every sku, each and every day.
If you have 5 skus, then it’s possible. If you have 100, it’s a nightmare. Not worth the effort and errors.
By loading historical daily inventory numbers using our Gorilla_InventoryHist() function, you can load your historical inventory units and prevent your operations from going out of stock.
An example is the screenshot below where I can filter for inventory in “transfer” process from this week for any sku I’ve selected.
No clicking around searching for information. No compiling spreadsheets.
5. Review management and respond faster to complaints
Time and money-wasting process:
Go to seller central
Go to the product page
Click around for the reviews
Sort for negatives
Repeat for all products
New efficient process:
Use software that curates it all in one place using REVIEW formulas.
There are tons of Amazon review management and emailing software.
With Gorilla ROI, we’ve taken a different approach where you can pull reviews for any of your child ASINs directly into a spreadsheet.
This makes it easy to share and discuss. You can copy the data into an email and send it to the customer service team to review, you can pull a list of the last 50 reviews or drill down to a single SKU.
Rather than hunting to find the data, create your own dashboard to monitor customer service and reactions.
There is a ready to use review management spreadsheets available as soon as you sign up.
Bonus: Simplify data sharing across teams
One thing that we’ve changed is how data is handled across our business.
We no longer create new accounts for employees. Accounts are only created for people whose job requires it.
Me – Full access
Operations assistant manager – 90% access
Operations assistant – 70% access
Bookkeeper – 50% access
Automation only account – 90% access
That’s about it.
This way, data is protected and people are not logging in and out without reason. The goal is to limit people touching the account as much as possible. Only for those that need to.
We changed to this because after reviewing our systems, even the people who accessed seller central used it for their own single purpose.
Founders only visited the sales report
Fulfillment only inventory pages
Bookkeeper only downloads the finance csv’s
Even the bulk of the work performed by myself and our operations team centered on reviewing listing details and other pages weren’t visited as frequently.
These lesser-visited pages added more work and crowded mind space of another thing to check at intervals.
What’s the Amazon FBA buy box and why is it important for FBA sellers? It’s important as a seller to know who’s product is being presented to the buyer. If you resell branded products, multiple people could be offering the same product.
The buy box is also called Featured Offer which you can read more here.
The box with the price and “buy now” button for this toddler itch relief product looks so unassuming, but more than 82% of sales go through the Amazon buy box.
When a buyer clicks the “buy now” button, it is sold by one of the many sellers. This is where the term “win the buy box” comes from.
You win the buy box based on price and performance-based metrics. But you and your product need to be eligible FIRST.
In Seller Central, go to your Manage Inventory page and then click Preferences.
Select Buy Box Eligible, and at the bottom of the page, click Save Changes.
The Buy Box Eligible column will display the status for each of your ASINs. Here’s Seller Central requirement for product eligibility:
To see other sellers and who is competing with the buy box, you’ll see this small text at the bottom of the buy box area.
When you click on “1 new” link under the “Compare with similar items” link you’ll see the “offers for this product”.
The other listing on this page did not win the buy box most likely because the offer is $4 more expensive.
Note that the link under “compare with similar items” is not always “1 new” as it could be “2 new” or “3 new” depending on the number of competitors for that product.
How to Win the Buy Box
If you are a reseller, it’s hard to consistently be the winner of the buy box as you are competing for the same space.
It’s not uncommon to get the buy box at the start of they day then lose it at the end of the day.
You won’t get featured 100% of the time in a day because the Amazon algorithm dictates who wins. We don’t suggest that sellers should focus on winning the buy box. Keep focus on customer needs instead.
You’ll win if you keep your focus on what you can control – pricing, product/service quality, inventory, and customer satisfaction.
Amazon’s customer focus mantra means if you want to have the featured product placement then you need to pass or exceed these metrics.
Price your items competitively
Offer faster shipping and free shipping
Provide great customer service
Keep stock available
Keep in mind that if you have higher performance metrics you can win the buy box even if your price is higher. It’s Amazon rewarding sellers that sell the best product and customer service or the highest value for the price.