Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included.
Looking for Google or Excel spreadsheets as an Amazon inventory management system?
You’ve come to the right place.
By the end of this article, you’ll be able to:
- create your own inventory management template using proper spreadsheet structure and formulas
- load Amazon FBA inventory data into Google sheets automatically
- design and use Google Sheets or Excel as an Amazon warehouse management tool
There is a working spreadsheet template at the end of the article you can download. It is the same version that I’ll show you how to create in this article.
Use the quick links below to jump to the section you are looking for.
- What this article is NOT about
- Building the Amazon stock management spreadsheet
- Simplify with Google Sheets for Amazon Inventory
- Basics to designing an effective Amazon inventory system
- Building the INPUTS sheet
- Building the product DATA sheet
- Building the inventory DATA sheet
- Building the inventory LOGIC sheet
- Building the dashboard PRESENTATION
- Automate with our Amazon inventory add-on for Google Sheets
- Download the Amazon inventory management spreadsheet template
- Additional Resources
- What is Gorilla ROI?
What this article is NOT about
If you are not an Amazon seller and looking for information on how Amazon actually operates its warehouse or inventory, this article is not for you.
I do not talk about their warehouse locations, how they pick orders with robots, seller fees, random inventory locations, barcodes and so on.
This article is for Amazon sellers who are looking to create their own Amazon inventory system to handle their own operations.
Building the Amazon stock management spreadsheet
This is the inventory stock spreadsheet that I’ll build with you here. Here’s the link to the final preview.
Before we dive into the technical details, some theory is required.
*Cue the eyeroll*
Yes, I know.
If you are an expert with spreadsheets and also a A-grade UI designer, then skip to the practical section titled “Basics to designing an effective Amazon inventory system”
Understanding your Amazon inventory management system needs
Every seller needs a system to track and manage their inventory.
Ultimately on Amazon, there are two main types of sellers regarding inventory.
- You focus on a smaller number of SKUs
- Or you sell tens of thousands of different SKUs and do large volumes by combining the total sales.
The focus of this article will be on the first group of sellers.
FBA sellers who focus on a small number of SKUs.
- Sellers who carry less than a thousand SKUs. Focuses on a smaller number of SKUs.
- Sellers who sell big volume with a smaller number of products.
- Sellers who don’t have 30+ employees in the warehouse fulfilling products.
- Sellers who don’t need an expensive full-blown Warehouse Management System (WMS) that easily eats up $2,000 a month to operate.
- Sellers who need nimble, flexible and creative solutions without being locked into enterprise software or a programming team.
- Sellers who don’t need EDI interfaces.
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.
It was a nightmare.
That’s why we built our Amazon inventory system on Google Sheets + Gorilla ROI making use of our sales and inventory addon.
Simplify with Google Sheets for Amazon Inventory
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:
- Image URL
- Product Name
- Peak / Offpeak
- Unit/ case
- My Price
- Leadtime (Days)
- 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.
- Columns F to L pull the info from INVENTORY_DATA.
The SKU is loaded by using a formula as simple as
=PRODUCT_DATA!A2 =PRODUCT_DATA!A3 ... =PRODUCT_DATA!A1000
Or you can use more advanced formulas like
=UNIQUE(PRODUCT_DATA!A:A) or =ARRAYFORMULA(PRODUCT_DATA!A:A)
The other columns for image, product name, ASIN is pulled using VLOOKUP.
VLOOKUP is also used to find and automatically display the inventory data points such as instock, inbound, transfer, last 7 days units sold etc.
You can see that the INVENTORY sheet has zero inputs. It’s just a sheet with formulas that updates itself based on your inventory data at Amazon.
The VLOOKUP formulas use the SKU as the search_key to locate the correct data point to pull in.
- The red section in the screenshot above comes from the PRODUCT_DATA sheet.
- The purple section comes from “INVENTORY_DATA”.
(Refer to the spreadsheet template directly to see the formulas for yourself.)
Since this is the logic sheet, this is where you can go wild with all your formulas to calculate stock rates, forecasts and anything else you know.
To keep it simple, I’ve added the following calculations:
- Av/30Day Units Sold
- AMZN Inv Days Left
- QTY to Ship
Av/30Day Units Sold – Average Last 30 days units sold
Rather than using the standard last 30 days, I like to normalize the data to try and account for any spikes.
That’s why I have last 7 days, last 14 days and last 30 days in my inventory data sheet.
The formula to calculate this is:
=(Last7Days x 4 + Last14Days x 2 + Last30Days) ÷ 3
It converts the last 7 days and last 14 days into a “month” equivalent.
Then the average is taken for the three values.
For an even more accurate assumption, you can use the calculation of:
=(Last7Days÷7) + (Last14Days÷14) + (Last30Days÷30) ÷ 3 x 30
What this version does is normalize the 7 day, 14 day and 30 day sales into a one day figure. It then takes the average and then multiplies it by 30 to get the total average last 30 days units sold.
There are a lot of different ways to calculate the average 30 days or normalize it, and it depends on your needs and your business.
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.
In Excel, you can use ISERROR().
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)
- More pre-made template examples and premium Amazon spreadsheets
- More free Amazon spreadsheet templates
- How to create your sales tax spreadsheet
- A spreadsheet containing the full list of Amazon sellers fees
- How to create a sales tracker spreadsheet
What is Gorilla ROI?
Gorilla ROI automatically pulls Amazon data into Google sheets for you to make sense of the data without the inconvenience of manually downloading, sorting and updating spreadsheets.
Learn how you can centralize your data and use it to increase your ROI.