Demand forecasting and planning to skyrocket your online business

seasonally adjusted forecast

By the end of this article, you’ll know how to implement demand forecasting that works for your business. Make more sales and profit without going out of stock or sitting on too much inventory.

Demand forecasting isn’t hard. It’s a lot of common sense and understanding your business.

In this in-depth demand forecasting guide, you learn:

  • How demand forecasting works
  • How to interpret results from a demand forecasting analysis
  • Real-life scenarios that apply to your Amazon or e-commerce business
  • The best demand forecasting models for an e-commerce business
  • Suitable metrics to use in a demand forecasting analysis

If you want the formulas and methods right away, jump to the start of the practical section with this link.

Why does demand forecasting matter?

If you are reading this, it’s safe to assume that you’ve run a business (or worked in one) long enough to understand the issue of going out of stock or sitting on too much inventory.

Demand forecasting and planning to skyrocket your online business 1

These are the two issues that every business wants to avoid.

  1. Going out of stock (especially best sellers or seasonal best sellers)
  2. Sitting on too much inventory

Ordering inventory and estimating demand based on an “empty shelf” isn’t going to improve your sales or cash flow. Using old data and then projecting it will sell the same as before without thinking about the seasonality and current trends, which could mean you over purchase and tie up your cash on slow-moving inventory.

Demand forecasting is:

The trend and demand for online shopping is only growing. 69% of Americans have shopped online. COVID19 is pushing this number higher as online shopping becomes more vital for people to get what they need.

In 2020 alone, millions of buyers flocked to Amazon to get their goods during the pandemic. The surge in sales left many FBA sellers out of stock and missing out on huge sales and profits. Competitors took over the sales while you missed out.

If your business generates sales in the 5 or 6 figures and you are serious about getting into the 7-8 figure range, there is no excuse not to use demand forecasting and better demand planning.

If big sellers and companies rely on these planning strategies and calculations so much, why aren’t you?

Real demand forecasting scenarios

I’m going to revisit the 2 points frequently on this topic.

  1. Going out of stock
  2. Sitting on too much inventory

Going out of stock is one of the worst things to happen. It kills your momentum. If for this one reason alone, forecasting your sales and inventory is pivotal.

Because it is so easy to open an online business, competition is cut-throat. If you’ve spent time and energy to build your sales and then find yourself out of stock, it drains all the momentum you’ve built.

Getting more product in stock can take 30-90 days depending on where you manufacturer and by that time, potential customers could have moved on and bought from your competitor.

Went out of stock on Amazon

Here’s a real example that happens all the time.

Demand forecasting and planning to skyrocket your online business 2

We went out of stock for one of our best selling items on Amazon. We had more inventory on the way, but we didn’t factor into our forecast any potential spikes from summer sales.

Leading up to our boost in sales, we invested a lot of time and money into:

  • PPC
  • advertising on other channels
  • creating sales funnels
  • launches

This isn’t a few hundred dollars I’m talking about.

By the time we got the product back in stock, we had been out of stock for a month.

Rather than picking up where we left off, once inventory hit Amazon again, we had to:

  • increase PPC bids
  • restart ad campaigns with new ideas
  • paid for launches again

After 2-3 months, we finally got traction to where it was before the product went out of stock.

During that 1 month of stock out and the 2-3 months of building sales up again, we lost a considerable amount of sales and profit to competitors who scooped up all the work we had done.

If you have best sellers on Amazon, do whatever it takes to keep it in stock. Better yet, improve your demand planning so that it doesn’t happen.

But it’s not just inventory when it comes to forecasting. Other factors include:

  1. pricing – cheaper sells quicker
  2. consumer trends – fads won’t sell
  3. inventory lead times
  4. seasonality and historical sales

Sitting on too much inventory

Here’s the reverse of the going out of stock.

Too much inventory.

Demand forecasting and planning to skyrocket your online business 3

  • Drains cash flow
  • Eats up fees
  • Takes up space
  • Wastes mental space as you figure out what to do with it

For Amazon Sellers (FBA and FBM) or other e-commerce sellers, planning and knowing your demand will put you ahead of the competition.

If you sell summer swimwear, it is seasonal and you don’t want too much supply after the summer period.

One of our products was in the grilling niche and the problem was that we received our product near the end of summer.

Ouch.

Not only did we mistime inventory, we completely missed out on all sales and ended up with too much inventory. This product sat in our warehouse for months without doing anything until we got creative and re-marketed it for a different industry.

But what if we didn’t have a warehouse to pivot quickly? What if we sent a whole container of this product straight to Amazon fulfillment centers?

Double, triple, ouch.

Imagine, no sales, inventory fees adding up, long term storage fees approaching.

What a nightmare.

If you operate in multiple countries and platforms, it’s so important to track and forecast demand accurately because you lose a lot of control.

We ended up disposing of thousands of inventory in the UK because we weren’t able to sell enough and the fees were costing more than the product itself.

Tore the band-aid off and got out of UK and EU.

Inventory forecasting spreadsheet

You can use Google Sheets or basic Excel to create your own inventory management system. There is a free inventory spreadsheet template in the link.

inventory stock spreadsheet tracking

You can see from the screenshot how many days of inventory is left at Amazon, and how many units you should ship based on the average number of units sold over the last 30 days.

inventory ship to amazon fba
inventory ship to Amazon FBA

By using a formula like the following

QTY TO SHIP = AV30DAYUNITSSOLD X 1.5 - TOTALAVAILABLE

you can keep your inventory in check and you won’t go out of stock because you know how many units need to be sent in to maintain the inventory level threshold to meet your demand.

By understanding the sales demands and sending in the right amount to Amazon, you improve your cash flow by not overbuying and storing.

Get your free inventory template to use as an inventory replenishment tool to help you project the demand.

Economic Order Quantity method

Another basic formula to help with knowing how much inventory to plan and order is called the Economic Order Quantity formula. This works best if your costs are stable.

The EOQ is a company’s optimal order quantity that minimizes its total costs related to ordering, receiving, and holding inventory.

The EOQ formula is best applied in situations where demand, ordering, and holding costs remain constant over time.

Let’s assume the following:

  • A cosmetics store sells 200,000 units of lipstick per year.
  • Each unit costs $1
  • Holding (storage) cost per unit is $1.80 per year ($0.15 per month)

The formula is:

EOQ = √(2DS/H)

where:

D=Demand in units (annual basis)

S=Unit cost (per purchase order)

H=Holding costs (per unit, per year)

EOQ = √[(2 x 200000 x 1) / 1.8] = 471.4

The ideal order size for this business is 471 units of lipstick to minimize costs but meet customer demand.

But only 471 units when this business sells 200,000 units a year? That’s the downside with simplistic formulas such as this.

Seasonality adjusted demand forecasting calculator

This is a demand planning and forecasting calculator that our PRO business spreadsheets offer to FBA sellers.

If sales were always a straight line, you’d never need to run any demand forecasting calculations.

But in reality, sales more likely represents the purple line.

The orange line is a straight line forecasting method that will either leave you out of stock or with too much inventory.

The blue line is the data you need when running a forecasting analysis.

seasonally adjusted forecast
Purple = sales. Blue = seasonally adjusted. Orange = straight line.

The exact calculations are far too complex for this article, but here’s the big idea of how it works.

  • Get at least 12 months (24 months is better) of past sales data with the number of units sold per month
  • Get this number for each SKU. Sold per month over the past 12 to 24 months.
  • The moving average is then calculated.
  • A seasonal index is calculated for each month.
  • A seasonal average is calculated.
  • This number is then deseasonalized and a trend is projected.

If that’s too complex, then trust me when I say it’s easier to get the spreadsheet calculator for yourself and not have to worry about the details. It will cost you far more time and money to create your own calculator and won’t work anywhere near as accurately or better than our ready made solution.

Seasonal sales and inventory forecast calculator
Seasonal sales and inventory forecast calculator

​Data required to create a practical demand forecasting analysis

If you are really keen on making your own, a good demand forecasting calculator needs to monitor business KPIs (Key Performance Indicators) as well as subjective factors.

Business KPIs

  • sales per SKU
  • sales per month
  • inventory on hand
  • inventory days remaining
  • pricing strategy
  • lead times

Subjective factors

  • seasonality planning
  • new product launch management
  • production planning
  • consumer trends

All these points add to demand forecasting in one way or another, but I’ve always found the best results by focusing on fewer pieces of hard data such as sales per SKU, units sold per month, inventory numbers, pricing and lead times.

The subjective factors can always be completely off and that will throw your numbers out the window. It is better to focus on the data that you have.

Let’s say that you have a new T-Shirt coming out in a couple of months and expect huge results and responses from the product launch.

The launch comes and goes and it was a flop. Now you are sitting on 100,000 units that are not moving.

Or what if you came up with the idea of having wheels at the bottom of shoes for kids to roll around.

You create it and launch it and no one buys it – because the Heely’s fad is dead.

Where to get data from seller central?

To get your sales data, go into seller central, and REPORTS > BUSINESS REPORTS.

Demand forecasting and planning to skyrocket your online business 4

Keep adjusting the time periods by month and then download the data.

This will be very time intensive unless you have a sales software that you’ve been using, or use Gorilla ROI to load this type of seller central data immediately into Google Sheets like below.

monthly amazon sales stats gorilla salescount

For inventory data, do the same thing by exporting the data from your seller central account.

  • Go to REPORTS > FULFILLMENT REPORTS
  • Download the Amazon Fulfilled Inventory Reports.
  • Adjust the dates as needed to get monthly data.

With this data, you can use it to create projections and forecast or plug it into free forecast spreadsheet templates from the internet.

Here’s ours you can use.

Basic demand sales forecasting template

This is a free Google Sheets version of a sales demand forecasting spreadsheet.

You need your sales data by month to enter into the spreadsheet.

demand forecasting
demand forecasting spreadsheet

Step 1: Enter the product name so you know what you are analyzing.

Step 2: Enter the units sold per month

Step 3: Enter the unit price as it can change month to month

The spreadsheet will show you which month makes the biggest volume of sales for you to plan your inventory.

demand forecasting month sales

Repeat for all the SKUs you need to analyze. Remember this type of forecasting is done per SKU. Not by total sales.

When you are ready, upgrade to our automatic forecasting calculator which will make all this work effortless and more accurate.

Reorder point formula

I touched on this with the earlier example of the lipstick example. It’s good to know how many units to order, but when should you reorder?

Basic Reorder Point = Average Daily Sales x Lead Time + Safety Stock

This requires a good understanding of your supply chain as lead times will play a major role.

First, you must have safety stock which is a buffer of extra inventory to prevent stock-outs. If you only order exactly what you think you need, without any safety stock, you will run out of stock if any sales spike occurs.

A very basic safety stock calculation is to get an extra X number of days of inventory.

If Loreal sold 100 units of lipstick a day and wanted to keep 14 days of extra safety stock, that would be 100 x 14 = 1400 units extra when reordering.

Reorder point example

Let’s say I’m selling high end Office Chairs.

  • daily sales over 3 months = 26
  • manufacturer lead-time = 120 days
  • safety stock = 14 days = 14×26 = 364

Reorder Point = 26 x 120 + (14 x 26) = 3,484 units

When my inventory reaches 3484 units, I must reorder so as not to go out of stock.

This basic version works whether you sell on Amazon on in brick and mortar sales.

Thoughts on other forecast demand methods

There are plenty of other methods.

Before you came to this page, you’ve probably gone through sites mentioning the following:

These qualitative methods can be used, but if you’re an Amazon seller or small to medium business, it is useless.

Sorry if that ruffles a few academic feathers, but I’ll say it again. These are useless in the real world for small to medium businesses.

Unless you have gobs of time and thousands of dollars to waste, gather this type of data for forecasting is pointless.

Stick to quantitative forecasting where you can use data to:

  • understand what products customers are buying from you
  • see which products are gaining momentum
  • create trends and projections based on past sales data
  • identify slow-moving products
  • understand current demand vs previous months or periods

You can’t get any of this insight from asking strangers who have never bought from you or answers from random surveys and polls.

Other factors that play a role in demand planning

Up till now, I’ve made the assumption that everything remains the same such as your costs, pricing, economy, politics etc.

But life’s never like that right?

Consider the following that will impact your demand.

Pricing

Your pricing formula affects the buyer’s decision to make a purchase.

You’ll obviously sell more if the product is a highly desired item and the price is cheap.

If you plan to run heavy promotions and sales, then your demand will be higher. You can account for this by adding a bigger buffer to your reordering quantity calculations.

If the calculation tells you to order 5000 units with everything stable, then go for 6000 units if you want to run a strong ad campaign.

Just make sure to use the Amazon fee calculator to nail down the best pricing. What’s the point of doing business if you don’t know what your break-even point is.

Knowing details upfront like – how much is the expected profit you are going to make from the lower price vs the higher price, how quickly you expect to sell through, and how quickly you can reorder is important.

Most successful sellers on Amazon also use the price elasticity of demand to set their pricing policies.

The price elasticity of demand calculates how receptive the target market gets towards demanding your products when you adjust the price.

A lot of times on Amazon, or in huge demand markets, the market is willing to pay a higher price.

Take disposable masks for example.

The regular price used to be about 10-20c per mask. With the surge in demand, the price per mask is now easily 50c to $1 and there are still availability issues.

If you are the brand owner and the only one selling an in-demand product, pricing will be less of a factor because shoppers cannot price shop your product.

Competition/ substitute availability

Every e-commerce merchant needs to get along with the fact that competition is infinite and grows each day.

Buyers review other options before deciding to purchase your product.

And if you’re already selling on Amazon, chances are, there are possibly hundreds of substitute or knock off products in your niche.

If your product and brand are already established, then it’s easy to judge the demand because it will likely be stable.

But if you are trying to enter a fierce and competitive industry, the demand for your new product will be considerably lower than an established seller or product.

How to track whether you are winning or losing the buy box

Another function has been released: BUYBOXWINNER() will quickly and easily show you whether you are winning the buy box of your listing.

As always, we update documentation consistently and in multiple locations to make it easy to find.

  1. Official page of all the functions we support
  2. Google Docs version
  3. Website documentation for easier navigation
  4. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples

How to track whether you are winning or losing the buy box 5

Quickly see if you are winning or losing the buy box

Another basic requirement is to know when another seller has taken over the buy box or a hijacker has taken over.

aamzon buy box

We have other buy box functions such as:

  • BuyBoxOffers – Get the list of sellers competing for the buy box
  • BuyBoxPrice – Get the current winning buy box price

BuyBoxWinner() is a simple version that will display whether you are the “winner” or “loser” of the buy box.

This is one example of how you can use it.

amazon buy box winner status
Amazon buy box winner status per SKU or ASIN

How to use BUYBOXWINNER

How to track whether you are winning or losing the buy box 6

GORILLA_BUYBOXWINNER(sku, [marketplace])

Easiest example:

=GORILLA_BUYBOXWINNER(A1:A100)

This simple example will display “winner” or “loser” for each of the SKUs or ASINs listed from A1 to A100 for the US marketplace.

You can enter the marketplace like “US” or if you leave it out, it will use your default marketplace.

=GORILLA_BUYBOXWINNER("B00YD545CC", "US")

=GORILLA_BUYBOXWINNER("B00YD545CC")

Detect hijackers with BuyBoxWinner

The most obvious benefit with knowing whether you are winning or losing the buy box is if you sell your own custom product and a hijacker takes over your listing.

Add some color formatting like the screenshot above and you can quickly detect changes.

To see other functions and examples, visit the functions page for all the features we support.

ASINSTATUS shows if your ASIN is active or inactive

A new function has been released: ASINSTATUS() will quickly and easily show you whether your listing is currently active or inactive.

As always, we update documentation consistently and in multiple locations to make it easy to find.

  1. Official page of all the functions we support
  2. Google Docs version
  3. Website documentation for easier navigation
  4. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples

ASINSTATUS shows if your ASIN is active or inactive 7

Find out if your ASIN(s) or SKU(s) are active or inactive

In seller central, you can easily filter and see which ASIN or SKU are active or inactive.

ASIN status from <a href=

But what happens if one of your ASINs suddenly get deactivated and become inactive?

Amazon does not always send you a notification or email unless it was due to a Negative Customer Experience (NCX) or performance shut down.

If your item goes out of stock or a keyword or review triggers a red flag and your ASIN becomes suppressed, it is up to you to find out and address it.

This is where GORILLA_ASINSTATUS() will help.

How to use ASINSTATUS

Find the status of your ASIN. Returns "active" or "inactive".
Find the status of your ASIN. Returns “active” or “inactive”.

Easiest example:

=GORILLA_ASINSTATUS("US", A1:A100)

This simple example will display “active” or “inactive” for each of the SKUs or ASINs listed from A1 to A100 for the US marketplace.

If no marketplace is entered, it will use your default marketplace.

=GORILLA_ASINSTATUS( , A1:A100)

asin status active inactive results

Best use case to stay on top of your listings

The best and easiest way is to add it to a column where you list your entire product list.

We maintain a separate product list with SKU, ASIN, FNSKU, Title, Image and the required basics that you find inside the manage inventory section of Seller Central.

This ASINSTATUS() function can now go right alongside it.

Here’s an example.

Basic product tracking template idea to stay on top of your listings and know when something is active or inactive.
Basic product tracking template idea to stay on top of your listings and know when something is active or inactive.

A very easy, but convenient and powerful way to stay on top of your listings and operations directly within Google Sheets.

To see other functions and examples, visit the functions page for all the features we support.

Profit loss formula to build your P&L statement

profit loss formula

The new ProfitLoss() formula function is available for you to use.

Here’s the page for a deeper understanding and guide on how to use this Profit Loss formula.

As always, we update documentation consistently and in multiple locations to make it easy to find.

  1. Official page of all the functions we support
  2. Google Docs version
  3. Website documentation for easier navigation
  4. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples

Profit loss formula to build your P&L statement 8

Spreadsheet formula to get profit loss data

We’ve added another free template for you to create a profit and loss statement spreadsheet, but it’s not as automated as I wanted it to be.

Thus the GORILLA_PROFITLOSS() is born.

gorilla profit loss amazon function

Easiest example:

=GORILLA_PROFITLOSS("2020")

This simple example will automatically load a consolidated table like the following:

amazon fba profit loss statement table results
amazon fba profit loss statement table results

Rather than looking up all the individual transactions and compiling it yourself, it is done automatically so that you can pull up this data by simply changing the time period you want to look up.

=GORILLA_PROFITLOSS("THIS YEAR")

=GORILLA_PROFITLOSS("2020-01")

=GORILLA_PROFITLOSS("2019Q4")

=GORILLA_PROFITLOSS("THIS MONTH") and more

You can see how easy your life becomes when you can verify your profit and loss on the fly.

Try doing this manually – It is a nightmare.

More Profit Loss Examples

You can also build a profit and loss statement per SKU.

=GORILLA_PROFITLOSS("THIS YEAR", "US", "SKUNAME721")

Profit loss formula to build your P&L statement 9

When you define a SKU, the profit loss table will not include Amazon Reimbursements, Income-Other and Amazon lending.

This is because certain transactions are NOT applicable to a sku. The transaction is matched to your seller account, not SKU.

Think about storage costs. If you look at your transaction data, storage costs are always associated with your seller account.

Amazon does not charge you per SKU. It is a single dollar amount each month.

You can also see the details of what is rolled up into each of the categories.

  • Sales
  • Discounts/Promotions
  • Amazon Reimbursements
  • Shipping Income
  • Income-Other
  • Amazon Lending
  • COGS
  • Amazon Fees
  • AdvertisingTax

To look up a category, use the expanded formula like:

=GORILLA_PROFITLOSS("LAST 30 DAYS", "FR", ,"AMAZON FEES", "YES")

By entering “AMAZON FEES” and “YES” at the end, it will list all the Amazon seller fees one by one and how much was transacted for each item.

Amazon fees breakdown profit loss
Amazon fees breakdown profit loss
=GORILLA_PROFITLOSS("THIS YEAR", "US",  ,"COGS", "YES")

Same thing here where I can drill down on what is rolled up into “COGS” by looking it up in the formula.

Profit loss formula to build your P&L statement 10

There you have it.

To see other functions and examples, visit the functions page for all the features we support.

Load Amazon FBA returns and refunds data into sheets

You can now load the total number of FBA returns and the values of the returns by SKU or across your entire account.

This is on the heels of the newly released Amazon salestax function. If you have data importing requests, leave a comment below and we’ll add it to our consideration board.

As always, we update documentation consistently and in multiple locations to make it easy to find.

  1. Official page of all the functions we support
  2. Google Docs version
  3. Website version for easier navigation
  4. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples

Load Amazon FBA returns and refunds data into sheets 11

Our latest FBA refunds function

We have created two separate functions for refunds. One is to get the qty of FBA returns over any period for any sku.

The other is to get the total value of the refunds.

See how to use it below and the dedicated functions and documentation pages and links to see more examples and usage.

GORILLA_REFUNDSCOUNT()

Easiest example:

=GORILLA_REFUNDSCOUNT("THIS YEAR")

More examples on the RefundsCount() explanation page.

gorilla refunds count function

You can see how easy it is to use. The only requirement is to enter the time period.

The list of time periods is extensive and you can also use custom dates.

=GORILLA_REFUNDSCOUNT("CUSTOM", "FR", A2:A34, "2020-01-01", "2020-01-15")

Enter “custom” in the period attribute. Then fill up the other attributes following the onscreen formula popup.

In the above formula, it’s grabbing the refunded count for:

  • custom time period from 2020 Jan 1 to Jan 15
  • France market
  • for all SKUs entered in cells A2 to A34

Here’s another example of how this looks on Google sheets.

refunds count results gorilla roi

Using ranges can load all the data in one shot.

Getting the returns like this using RefundsCount() is the same concept as SalesCount(). Think of the two functions are sisters. They work the same, just different results.

GORILLA_REFUNDSTOTAL()

Easiest example:

=GORILLA_REFUNDSTOTAL("THIS YEAR")

More examples on the RefundsTotal() explanation page.

gorilla refunds total

This is the same concept as RefundsCount. The only difference is that this pulls in the dollar value of all returns.

If you have 100 refunds for a $10 product, it will load $1,000 instead of 100.

All the same time periods are supported as well as custom time periods.

Here it is in action.

amazon fba returns refunds total results
Amazon fba returns refunds total results

To see other functions and examples, visit the functions page for all the features we support.

New: Amazon SalesTax to get sales and tax data by state and city

The new SalesTax() function is available for you to start using immediately.

Here’s the page for a deeper understanding and guide to Amazon FBA sales tax.

As always, we update documentation consistently and in multiple locations to make it easy to find.

  1. Official page of all the functions we support
  2. Google Docs version
  3. Website version for easier navigation
  4. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples

New: Amazon SalesTax to get sales and tax data by state and city 12

GORILLA_SALESTAX()

Warning: This function is NOT for accounting.
Do NOT use this for your taxes.

Best uses of this function:

  • help you prepare how much you will owe
  • know which states bring in the most sales
  • know which cities bring in the most sales

Easiest example:

=GORILLA_SALESTAX("LAST MONTH")

More examples on the formula functions page under SALESTAX().

gorilla sales tax functionIf 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.

gorilla salestax formula google sheets
Displaying the salestax formula grouped by city

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.

Gorilla FBA sales tax formula by state
Gorilla FBA sales tax formula by state

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.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included.

amazon inventory replenish template spreadsheet

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.

Let’s begin.

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.

inventory stock spreadsheet tracking

amazon excel warehouse inventory details tracking spreadsheet in stock

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

inventory system at amazon for most sellers

Every seller needs a system to track and manage their inventory.

Ultimately on Amazon, there are two main types of sellers regarding inventory.

  1. You focus on a smaller number of SKUs
  2. 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.

small sellers handling inventory tracking
source: Simpsons

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.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 13
source: shippingeasy

Our main use of ShippingEasy is for two things.

  1. centralize incoming orders from all different selling platforms
  2. 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.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 14

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

gorilla sheets functions for Amazon Seller

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.
Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 15
Source: Frogmorris.net

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.

  1. Data
  2. Logic
  3. Presentation

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.

design inventory spreadsheet structure

The DATA sheets

In this inventory/warehouse project, there are going to be two data sheets.

  1. One for product information such as the SKU, ASIN, FNSKU, that matches the Amazon information.
  2. 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.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 16

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.

Here’s why.

Let’s say you want to add 10 new products to your current spreadsheet, change the ASIN and add dimensions.

Now what?

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

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 17

This is what you envision being able to share with your team.

sales Inventory Management Template Inventory Dashboard
Source: insightlysquared

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.

design inventory spreadsheet structure

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.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 18

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.

amazon spreadsheet input ranges date ranges automated

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

product datasheet for inventory warehouse systems spreadsheet

  • 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:

our inventory product master headers

  • SKU
  • ASIN
  • FNSKU
  • MKTPL
  • Image URL
  • Image
  • Product Name
  • UPC
  • Peak / Offpeak
  • Unit/ case
  • Category
  • Rank
  • My Price
  • Leadtime (Days)
  • Rec Order Lev in Days
  • Suppliers
  • 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:

product data setup

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

seller central manage inventory save image

Building the inventory DATA sheet

amazon inventory stock data tracking

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

import amazon inventory data

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.

inventory stock tracking formulas

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

amazon inventory stock data tracking imported

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.

amazon average 30 days units sold

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.

amazon inventory days left

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.

conditional filter alert amazon inventory days left

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.

amazon inventory days remaining to send threshold value in days
Amazon inventory days remaining to send threshold value in days

A value in the “QTY to Ship” appears when the AMZN Inv Days Left column is less than the threshold alert level (25 days).

inventory ship to amazon fba
inventory ship to Amazon FBA

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.

amazon excel warehouse inventory details tracking spreadsheet in stock

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.

design inventory spreadsheet structure

  • 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:

  1. identify what needs to be done
  2. drill down to the next stage by looking up the data
amazon inventory management system dashboard presentation
Final Amazon inventory management system dashboard presentation

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)

using query dashboard inventory at amazon

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.

amazon inventory tracker

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.

  1. Authorize Gorilla ROI to your Amazon seller account
  2. Install the addon on your Google account
  3. Activate the addon from the Google Sheets menu
  4. Wait for data to be imported (6-24hrs depending on your size)
  5. 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.

seller central integration for inventory data

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.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 19

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)

amazon inventory system spreadsheet template download link

Additional Resources

Amazon FBA sales tax for sellers. What is collected and what you owe.

amazon fba sales tax

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

Let’s begin.

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.

Check to see if your state is listed in Amazon’s marketplace facilitator page. Only the states on the list have sales automatically collected and remitted.

An easier way is to go to your tax settings.

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.

tax settings amazon

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.

Ouch.

Amazon tax code – select the right one

The full list of Amazon product tax codes is long, overwhelming and confusing.

If you are a seller, go to this page in your account to see the full list and find the matching product tax code for the item that you sell.

If you don’t have access to a seller account, click here for the full-screen capture of all the product tax codes.

This screenshot is a snippet of the entire list.

amazon product tax code preview
Amazon product tax code preview

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.

This means that if you set products in this category to A_GEN_TAX, then you will be collecting more tax than required.

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.

seller central tax code setting

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.

Does Amazon pay sales tax for sellers

This is another yes and no answer and comes up a lot.

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.

marketplace facilitator tax collected for sellers
marketplace facilitator tax collected for sellers

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.

Much simpler.

Here’s how to get your sales tax data from seller central and filter the data.

  1. Select the entire header row of the table.
  2. Click on the menu > data > filter
  3. Click on the drop down icon and then deselect “MarketplaceFacilitator” and click OK.

Amazon FBA sales tax for sellers. What is collected and what you owe. 20

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.

Enter Gorilla ROI

Import sales tax info using Gorilla ROI

GORILLA_SALESTAX()

GORILLA_SALESTAX("LAST MONTH")

GORILLA_SALESTAX("LAST YEAR", "US", "STATE")

GORILLA_SALESTAX("2020", "US", "CITY")

Assuming you have:

  1. installed the addon and followed the getting started instructions to connect your seller central account
  2. 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.

sales tax gorilla roi
Importing sales tax information with Gorilla ROI – FBA only.

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.

gorilla salestax formula google sheets

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.

Gorilla FBA sales tax formula by state
Gorilla Amazon FBA sales tax formula by state

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:

  • Product information
  • Your product BSR
  • Inventory across marketplaces – instock, inbound, transfer
  • Sales data across any period – last month, 2020 Q1, your fiscal year
  • Reviews
  • Charges and fees for a P&L

You can supercharge any of your existing spreadsheets by data process automated.

How to create an Amazon sales tracker with Google Sheets

dashboard sales fba spreadsheet

By the end of this article, you’ll know how to

  • create your own Amazon sales tracking tool
  • pull Amazon sales data by SKU or ASIN
  • track sales by day, week, month or any other period you need
  • feed and automate your sales dashboard
  • copy a free Amazon sales tracker spreadsheet to your account

all within Google Sheets. You’ll look like a genius in front of your team.

Use the Gorilla ROI sheets addon to start automating your processes.

The basics to creating an effective Amazon sales tracker

In order to track sales data, you need – sales data.

If you are doing it manually, save yourself the headache and hassle of manually downloading reports by automating the work.

The reason why trackers and spreadsheets you first built enthusiastically ultimately fizzle out, is because the work required to update with data is a major hurdle.

How to create an Amazon sales tracker with Google Sheets 21

As your product portfolio grows, your spreadsheets and various trackers grow with it.

What also grows is the time it takes to update. It grows exponentially.

I bet you can find a wasteland of old excel or spreadsheets that you haven’t opened in years in your folders or drives.

In order to scale, where you can grow without increasing your workload to get data, we recommend using Gorilla ROI.

How to create an Amazon sales tracker with Google Sheets 22

To get the Amazon sales data you need, use our free Gorilla Sheets addon which is free for any 3 SKUs.

Enter your SKU, not your ASIN.

The methods described in this article assumes you have:

  1. already installed the Gorilla Sheets addon
  2. connected to your Amazon seller account
  3. data has fully loaded (1-24 hours depending on your size)

Now, let the fun begin.

Laying the foundation

build strong spreadsheet foundation

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.

Data

Logic

Presentation

Organizing “Data”

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 to create an Amazon sales tracker with Google Sheets 23
Source: Frogmorris.net

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.

Organizing “Logic”

How to create an Amazon sales tracker with Google Sheets 24
Source: Frogmorris.net

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.

Organizing “Presentation”

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.

How to create an Amazon sales tracker with Google Sheets 25

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.

amazon sales tracker
Amazon Sales Tracker – Google Sheets

Dashboard = Presentation = Updated dynamically and what we look at. Updates based on sales data.

We use VLOOKUP, QUERY(), SPARKLINE() to create awesome looking dashboards for things like sales tracking, inventory monitoring, sales forecasting, cost analysis and more.

amazon best sellers sales performance
Amazon seller sales performance tracker

SALES_STATS = Data = the sales performance of each SKU over various time periods we track like

  • Last 7 days
  • Last month
  • This month
  • Last year
  • Last week

sales tracker time period

We actually follow over 20 different sales periods to track how we are doing.

SALES_MONTHLY = Data = sales performance of each SKU by month.

amazon sales tracker monthlyProduct_details = Data = contains product information like the entire SKU list, ASIN, FNSKU, UPC etc. Get this using =GORILLA_PRODUCT().

How to create an Amazon sales tracker with Google Sheets 26

If you have existing spreadsheets, try reorganizing it to separate the data and keep it clean before it gets even more out of hand.

Note: When creating a simple spreadsheet like this, a logic sheet is not required because Gorilla ROI handles data + logic.

Manual process to get daily sales into your sales sheet

There are multiple different ways of getting your Amazon sales data.

Here’s the manual (and painful) process.

How to create an Amazon sales tracker with Google Sheets 27

  • Go to seller central
  • Go to reports > business reports > detail page sales and traffic by child item
  • Change the dates on the calendar to what you need
  • Export the data
  • Open up the csv or excel spreadsheet and create a pivot table
  • Extract the SKU and sales data
  • Copy and paste to your main spreadsheet in the data tab
  • Repeat each day (argh!)

It’s a painful and tedious process but it works. Keep doing it on a daily basis to track your Amazon sales.

But what if you want to track something other than daily sales? What then? I’ll get to that later.

Automatic method to get daily Amazon sales data

  • Enter your list of SKUs in column A (let’s say you have 200 SKUs)
  • In cell B1, you type in =GORILLA_SALESCOUNTHIST(A1:A200)
  • Done

This formula will update itself every day and pull in the last 30 days of your daily sales for each of your 200 SKUs.

SalesCountHist() pulls daily sales
SalesCountHist() pulls daily sales

Get monthly sales into the spreadsheet tracker

Getting monthly sales numbers is the same process.

If you do it manually, you have to adjust your dates to the start and end of each month, export the data and then compile it again into a monthly column.

To do it automatically, use the formula =GORILLA_SALESCOUNT().

  • Create a header row in the format YYYY-MM (don’t forget the dash)
  • Use the formula =GORILLA_SALESCOUNT(D1, “US”, A2:A100, “ALL”)
    • This will pull the monthly sales for the YYYY-MM that is entered into cell D1
    • for the US market
    • bulk load for SKUs in A2 to A100
    • for all sales status
Monthly amazon sales by sku
Monthly amazon sales by sku

By making the sales formulas dynamic, anytime the inputs change, the sales stats change with it.

In this case, when the month ticks over and it becomes 2020-03 the headers are updated which the formulas will detected and then also start pulling data for 2020-03.

This way, the tracker is fully automated and all you have to do is focus on the numbers and making decisions of what to send and order.

Sales data presentation

If you have organized your data and logic, you can now turn your attention to the presentation.

Here’s an example of an ugly presentation that everyone ends up creating.

ugly spreadsheet structure and information

How to create an Amazon sales tracker with Google Sheets 28

How to create an Amazon sales tracker with Google Sheets 29

You get the point.

Coming back to the basics, this is because the data, logic, and presentation are combined into one place.

While design is subjective, here are some tips to make dashboards look like this:

PPC analysis and kpi dashboard
PPC analysis and kpi dashboard
dashboard sales fba spreadsheet
dashboard sales fba spreadsheet
Seasonal sales and inventory forecast calculator
Seasonal sales and inventory forecast calculator

12 Simple do’s and don’ts of spreadsheet presentation

How to create an Amazon sales tracker with Google Sheets 30

  • Don’t overuse different colors
  • Do use 3-5 colors max. Keep the color palette consistent.
  • Don’t color your cell unless there is a clear reason and it is so obvious everyone else will understanding the meaning.
  • Do insert notes and comments in the cell to deliver the message.
  • Don’t make the spreadsheet something only YOU can understand.
  • Do understand the objective of what others need to see and future proof it.
  • Don’t use the standard strong colors in the color bar
  • Do use softer, neutral or pastel version of the colors
  • Don’t overcrowd and stuff the sheet with as much data and numbers as possible
  • Do focus on simplifying it and then simplifying it further. Display your core KPIs and the message you want to convey only.
  • Don’t keep every column and row the same default size.
  • Do expand and resize rows and columns to give added room and emphasis.
  • Don’t use numbers to express everything.
  • Do use icons and symbols to break up walls of numbers for visual understanding.
  • Don’t be afraid to use grayish tones for text.
  • Do emphasize the important data.
  • Don’t put everything into a huge table.
  • Do use multiple tables to break down the display into bite-sized tables.
  • Don’t get too crazy by mixing multiple design elements to make it look “pretty”.
  • Do keep the number of visual data presentation to max 2 per sheet. Stick to consistent use of bar charts or one other type.
  • Don’t use pie charts. They are the worst.
  • Do use bar charts or line charts instead. Please avoid pie charts… please?
  • Don’t use 3D effects.
  • Do use simple and clean 2D visuals.

Advanced techniques to boost your visual  spreadsheet presentation

Keeping things as simple and to the point is the best.

To date,  it’s hard to find a better way to visual data than a simple bar chart (horizontal and vertical) or a line chart.

With simplified tables and bar/line charts, you can blow people away with advanced sales dashboards because of the effectiveness and simplicity.

With just 3 advanced spreadsheet functions, you can turn your current spreadsheets from “meh” to “wow”.

VLOOKUP()

Google Sheets VLOOKUP doc.

Sample Usage

VLOOKUP("ASIN B088325DS", A2:B26, 2, FALSE)

Syntax

VLOOKUP(search_key, range, index, [is_sorted])

If you structure your spreadsheets as I recommend into

  • sales
  • logic
  • presentation,

you need a way to find the data without doing things manually.

Use vlookup to find information for a particular ASIN like my inventory template.

VLOOKUP searches for the SKU, in the data sheet that has all the data, and pulls in the matching values for “last 7 days”.

sales velocity vlookup
sales velocity vlookup

This type of table is what I call the “logic”. There is no data. It pulls and updates based on the formulas.

It’s also not the “presentation” as this table is all I need to know my inventory status. It’s a logic sheet that is good enough to be the presentation.

Use VLOOKUP everywhere you can to eliminate manual data entry.

SPARKLINE()

Google Sheets SPARKLINE doc.

Sample Usage

SPARKLINE(A1:F1)

SPARKLINE(A2:E2,{"charttype","bar";"max",40})

SPARKLINE(A2:E2,A4:B5)

SPARKLINE(A1:A5, {"charttype","column"; "axis", true; "axiscolor", "red"})

As they say, a picture is worth a thousand words.

What type of sales data is easier to read and understand?

This table?

amazon daily sales units sold

Or this?

sparkline amazon sales chart visual

By using sparkline charts, you can create mini charts that fit inside a cell.

In my example, I’ve combined sparkline charts for each sku/ASIN inside a simplified table.

Nothing fancy. Just clean, simple and straight to the point.

QUERY()

Google Sheets QUERY doc.

This is one of the most powerful advanced functions that Microsoft Excel does not have.

This functions lets you truly use your spreadsheet like a database + application.

Sample Usage

QUERY(A2:E6,"select avg(A) pivot B")

QUERY(A2:E6,F2,FALSE)

This is how we use it for our sales spreadsheet dashboard.

QUERY is used mostly for the “presentation” stage. It finds the data, and then dumps it onto the screen.

spreadsheet query dashboard
spreadsheet query dashboard
=QUERY('AZUSA-SALES'!$A3:$BB,"select G, A, AZ, L, M, Q, S ORDER BY L DESC", 1)

My query formula is getting the data from columns G,A,AZ,L,M,Q and S from the tab “AZUSA-SALES”.

It then tells the query formula to sort it in descending order based on the data in column L of the AZUSA-SALES table.

The result is what you see in the screenshot above.

How to create an Amazon sales tracker with Google Sheets 31

Why is this so awesome?

If you have a big sheet of seemingly random and messy data, you can ask QUERY to do all the hard work to find, sort and display the data for you.

You could create multiple tables based on your needs.

  • A table analyzing conversion rates
  • A table tracking sales (as shown above)
  • A table tracking the child products of a particular parent
  • A table tracking the BSK rank
  • and so on

Free Amazon Sales Tracker with Google Sheets

The template can be copied with your Google account.

How to create an Amazon sales tracker with Google Sheets 32amazon google sheets spreadsheet template integrationIf you are looking for a way to supercharge your existing spreadsheets to eliminate the manual data work, or build 100% flexible internal tools, check out Gorilla ROI.

More tutorials

Amazon Review Checker with Google Sheets

I’ll show you how to create an Amazon review checker using Google Sheets and our free addon.

Keeping track of Amazon reviews is a vital way for sellers to know what buyers think of your product. This way you can see trends or any issues along the way.

Monitoring reviews also helps in improving your product as you can implement changes that your customers are complaining about.

In this article, I’ll show you how to load the average product rating, review details and the total review count for your products using this automated method.

This way, you can use Gorilla ROI as an Amazon review checker or Amazon review tracker.

Note that Gorilla ROI does NOT work as a research tool to track competitor reviews.

How to retrieve your average Amazon Product rating

amazon review checker

Track each product rating to see how your product is performing, which product is in danger of being delisted, and the general trend of how customers are reacting to your product.

Keeping track of your negative reviews is critical as it determines the life of the product on Amazon.

Here’s how to use the formula to get the rating for a single product.

=GORILLA_RATINGS("ASIN123123","us")

In this one, I picked our sample “ASIN12123” in the USA market. It returns a value of 4.5 which means the product has an average of 4.5 star rating.

gorilla ratings in action 1 sku

The best way to use this is via arrays like the example below.

gorilla ratings array in action

By requesting in bulk, it will load the ratings quicker in Google sheets.

Tracking the ratings of your products is a good way to keep an eye on it. Low ratings will most likely penalize you as a seller or your product might get delisted for being low quality.

Rather than have to go through your listings to get the product rating, you can see it all from a single screen in your spreadsheet.

How to track you Amazon reviews

Amazon Review Checker with Google Sheets 33

This function will load your Amazon reviews, but also provides detailed information about the review.

=GORILLA_REVIEW("ASIN123123","us", 5)

The formula will load the Amazon review for

  • ASIN123123
  • in the US market
  • last 5 reviews on the site

You can’t use arrays with this function because it pulls many data points and automatically loads it in a table format.

If you edit the formula above to be something like below where the 5 is replaced with 30, the function will now pull in your last 30 Amazon reviews.

=GORILLA_REVIEW("ASIN123123","US", 30)

Back to the original formula of 5 reviews. See the screenshot below.

The function returns in a tabular format:

  • ASIN or SKU
  • the review rating (1 to 5)
  • the date of the review
  • review title
  • reviewer name
  • link to the review
  • full text of the review

gorilla reviews in action

This is why ranges such as A1:A100 are not supported because the result comes back as a table. Can’t have a table of results overlapping another table of results and so on.

Get the review count of your product

review count guide

This function is most useful when you want to see the total number of reviews a product has received.

We organize the reviews by the child ASIN, not the parent.

If you have 5 ASINs under a parent, and if one child ASIN is generating 80% of the reviews, it doesn’t make sense to combine and show the total number of reviews for all your child products.

This function opens up many ways you can create your own Amazon review tracker.

If you want to get the total number of review counts use this formula.

=GORILLA_REVIEWCOUNT("ASIN123123", "US)

review count total in action

You can use a single ASIN/SKU with this function and you can also use array. See the example below.

gorilla review count array in action

Using the array, Gorilla ROI will pull in review count data for the SKU/ASIN from A2 to A5.

Example Amazon Review Tracker Spreadsheet

Here’s an example of the type of custom and flexible dashboard you can build to check and track your reviews.

Google spreadsheet preview

amazon reviews and price checker
amazon reviews and price checker

Here’s another version where we created this review tracker to be proactive with managing our listings and customer support.

pull in amazon reviews
click to enlarge Amazon review spreadsheet

These spreadsheets and more are offered for free to Gorilla ROI members.

Free Amazon FBA spreadsheets

Amazon Review Checker with Google Sheets 34

Looking for other free tools?

Go here for free Amazon FBA spreadsheets that you can use to help run and analyze your FBA operations.

Combine it with Gorilla ROI addon that you can get started with for free and see how powerful it is to automate the data retrieval aspect of your business.

References