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 1

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 2

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 3

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 4

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 5

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 sellers Coronavirus impact and what we are doing

It’s disheartening to see this play out in real life.

My friend’s bread and butter business are tradeshows.

He also runs his business “paycheck to paycheck” and didn’t consider that the coronavirus could be a business risk. He was part of the “it’s just the flu” crowd.

Every show is canceled for at least the next 2 months.

He has until the end of March before all the cash is gone and he is done.

10 years of business for him going up in flames just like that.

As Amazon sellers, you and I are lucky (for the most part) that we can continue selling through Amazon while retail and customer-facing businesses are hammered.

Today, I’ll share how we are navigating this and what we are doing.

The short version.

  • Come up with plans for different coronavirus situations
  • Be proactive
  • Monitor your pending orders and PPC
  • Ship boxes via UPS more often
  • Track your inventory and stay on top of it
  • Prepare to switch to FBM
  • Fix your broken processes
  • Use data to make objective and better decisions

Coronavirus impact to Amazon sellers

I mentioned in January that we were preparing for the worst. My thoughts at the time on Coronavirus was limited to supply chains. Here’s what I said in Jan which is still relevant today.

I’ve found it helpful to be realistic and prepare for bad case scenarios versus being optimistic and being caught flat footed. That’s how businesses went out of business.

You don’t have to go into full emergency mode, but factoring in 1-2 months of delays and rehearsing scenarios on how your team should handle and make decisions in these situations is a helpful and preventative measure. It’s also great training.

I would rather have an extra 1-2 months worth of inventory (overstock) of best sellers than be out 1-2 months of best sellers.

The coronovirus is going to impact:

  • delays from raw material suppliers as they can’t make enough
  • delays from manufacturers not being able to produce at full capacity
  • delays because bigger companies may have placed massive orders and takes priority over yours
  • delays as quality goes down because manufacturers are scrambling to manufacture and QA is a distant second
  • delays because you have to find new or more inspectors
  • delays as more paperwork from China is required for exporting and backlog of outgoing shipments at the port
  • delays as manufacturers will play the force majeure hand and demanding an adjustment of the costs

Dismissing the impact of coronavirus means you are saying that none of the above are big risks and your supply chain is perfect.

Even if you source 100% from USA or another country, you will have all your eggs in one basket. If a black swan event happens in America, the same issues apply. Having backup manufacturers to existing manufacturers is something we try to do in order to diversify the risk and once it is set up, it isn’t as hard to shift manufacturing from one place to another.

The two big news so far is that

  • Amazon is overwhelmed and needs to hire 100k workers
  • Amazon is prioritizing shipments for medical supplies and other staples

The next obvious step is a total shutdown in the US (or major cities) that will take place and private businesses will be forced to sit and wait.

As Amazon sellers, we are not considered “essential” businesses. That’s something to acknowledge. If you disagree, you’ve got your head in the sand.

The reality is that Amazon can get whatever you are selling, or something similar, from a major manufacturer like Proctor&Gamble, 3M, Johnson&Johnson etc.

It’s not a matter of if, but when, and being proactive as I share below.

Expect more delays at Amazon

As the virus continues spreading quicker, more Amazon employees are not showing up or using their UPT (Unpaid Time Off).

Amazon employees get 20 hours of UPT per quarter and 80 per year, but it’s being used by lots of people.

If you check your pending orders, it’s been growing as Amazon hasn’t been able to ship out on time.

Amazon sellers Coronavirus impact and what we are doing 6

Within Amazon’s FC, the normal metric is around 0.3 days of backlogged orders.

Over the past couple of weeks, the backlog has grown to over 1.0 days. It may not sound like much, but it’s a massive increase. A delay of 1.0 means the delay to the end consumer can be anywhere from 3-7 days.

Employees are also moved around to help with Amazon Fresh which operates at a different FC, system, and procedure.

No wonder they are trying to hire 100,000 more people and giving a $2 raise to lower-tiered employees.

Another important piece of information is that inside the fulfillment centers, they have what is called AFE (Amazon Fulfillment Engine).

AFE is an order packing system that consists of three general operations–sort, re-bin, and pack. Packers pack multiple items in each box.

Amazon Fulfillment Engine. Rates are high and you work elbow to elbow with up to 3 other people packing boxes and jiffies. Normally it’s multiple items but they also do singles as well.
(source)

How can you use this info?

  1. Monitor orders and PPC. If customers get sick of waiting, they will cancel. Your ACoS goes up, conversions go down.
  2. Employees are avoiding AFE. From forums, the sentiment is many people prefer picking, rather than packing. The FC’s have fans running and if it’s an elbow to elbow situation and someone with the virus sneezes near a fan… you get the picture.
  3. Expect certain fulfillment centers to shut down. Plan for it. It could be the FC you ship to.
  4. Send out smaller quantities quickly to keep your levels topped up rather than large pallets.
  5. Track inventory levels multiple times a day to quickly switch gears as needed.

FBM shipments or international marketplaces

EU sellers have it harder because all the borders are shutting down. You should update your settings to disable accepting orders from different countries if you are unable to fulfill or the delivery will be too late.

Amazon sellers Coronavirus impact and what we are doing 7

Amazon has extended the transit delivery window, but for certain areas like the red zone, don’t expect to meet the delivery date.

Check your shipping zones and adjust your policies as needed.

If you are a seller in EU and selling in the US, read the next section as your future shipments could be in jeopardy.

For USA sellers that sell in Canada, and vice versa, borders are getting stricter. Personal travel is now blocked. Shipments will take longer to pass the border and arrive at the destination.

Strategy to move past Amazon’s prioritization of inbound products

Amazon unexpectedly blocked shipments of products outside of 6 categories.

  1. Baby Products
  2. Health & Household
  3. Beauty & Personal Care (including personal care appliances)
  4. Grocery
  5. Industrial & Scientific
  6. Pet Supplies

Even within these categories, sub-categories not deemed essential are blocked.

If your product is correctly classified and you are not able to create a shipment, then the product in question is not prioritized at this time.

Most of our products are within Health and Household, Home, Personal care and we’ve experienced the 35-50% surge in sales over the past couple of weeks.

But the new blockage means that 8 out of our top 10 sellers cannot be sent in. The irony is that our bad sellers can be shipped.

amazon prioritizing shipments
Lots of our products temporarily blocked

Thankfully, our number 1 seller is whitelisted and we are in a good position with inventory for the other best sellers too.

As soon as we received our shipment from China and Korea last month, we were all hands on deck to get everything stocked up to at least 40 days of inventory.

The recent surge has drawn that inventory level down, but we’ve been topping up products as much as possible during this period.

Pallet shipments are slow to get checked in, so we send in a mix via UPS and pallets. For non-urgent shipment, send via pallets where you can afford at least 14 days for it to get checked in.

Otherwise, send it as box shipments.

If our FBA products go out of stock, we are ready to flip the switch to FBM.

To anticipate this happening, we need to reconfigure our packing stations for better efficiency. We aren’t set up well for FBM so if there are suddenly 100 orders that need to be fulfilled, it’s a lot of work.

Getting it figured out beforehand will be the smart move. You can never go wrong investing in processes and setting up systems. A good thread is up on the seller forum regarding this.

Another option is to transition to a 3PL that has the capability to offer SFP (Seller Fulfilled Prime).

The challenge will be trying to get this done in 2 weeks. 3 weeks max. It also only makes sense for products with enough margin.

Prepare for the worst. Hope for the best.

I have two cliche philosophies for running our business.

1. Prepare for the worst. Hope for the best.
2. Build a business for the long term.

As much as I like growth, I do err on the conservative side. Growth is like a drug. If you get addicted, it gets very dangerous because you end up throwing caution to the wind.

I also understand that as a small business, we don’t have the flexibility and options that big companies attract. I’d love to borrow a billion dollars at 0% like Warren Buffett. But knowing this isn’t the case, I’ve focused on the mantra that cash is king.

When I say prepare for the worst and hope for the best, it is based on the idea that:

  1. the business should be able to run without me
  2. the business should be able to survive at least 6 months with zero revenue

It sounds unnecessary and too much trouble until it is too late.

The important things are never urgent.
The urgent things are usually never important.

But when you define goals clearly and work backward, you’d be surprised how easy everything becomes.

When I realized I needed to diversify the business risk away from me, I took the plunge and promoted an employee to be my right hand, then shared all the knowledge I had.

Didn’t hold back and haven’t looked back.

Took over 1 year to get through everything and learn all the tools and systems we have in place. But I know that if something happens to me, the business will continue.

Next was building up cash balance.

It took us 4 years before we became profitable. A lot of stupid mistakes were made until I admitted we were going nowhere.

Then everything flipped.

Amazon sellers Coronavirus impact and what we are doing 8

Look at the net income line between 2015 and 2016.

We went from $-35k to $77k.

Amazon sellers Coronavirus impact and what we are doing 9

Why the drastic difference?

The biggest factor was going back to basics and calculating my numbers from scratch.

This single task changed the trajectory of our business.

Until 2015, I thought I was making money on products, but my calculations were wrong.

I completely mispriced products and had to raise them. We killed non-performing products, let go of emotional biases for certain products, reduced stupid mistakes and the rest is history.

Every couple of weeks, we transfer a small percentage of cash into an account for paying taxes, and another account for a rainy day or profit-sharing.

Check out the book Profit First. It’s easy to follow and no accounting experience is required.

Just common sense to creating a healthy business and protecting yourself.

Amazon sellers Coronavirus impact and what we are doing 10

Even with the pandemic going on, our focus is put our efforts towards long term and sustainable growth. Not short-term profit seeking efforts like the hand sanitizer guy everyone hates.

Amazon sellers Coronavirus impact and what we are doing 11

Or flipping the same BBQ gloves off Alibaba and trying to do the least amount of work.

Amazon sellers Coronavirus impact and what we are doing 12

Build your internal tools to adapt and move quickly

Anytime something happens – like a delayed order, missing shipment, Coronavirus – we look at it and ask ourselves how we can prevent it or not be caught by surprise.

This helps us continue to focus on a commitment of continual improvement and building internal tools using Gorilla ROI within Google Suite to improve the way we work, save more money and increase speed.

Online spreadsheets have become so powerful now that you don’t even need to pay developers to build custom software for you. Nearly everything can be done within Sheets – with some thought and effort in creating the spreadsheet.

The latest example is our Amazon warehouse dashboard below.

There was too much communication and daily standup meetings required to figure out what had to be done.

This dashboard now does all the talking and is displayed in the warehouse on a large TV for everyone to see.

It clearly shows the KPI (Key Performance Indicators) and everyone is empowered to make the right decisions based on the data from the dashboard.

It’s not hard for our packers to figure out what the priorities are and eliminates hand-holding, useless meetings and micromanaging.

amazon warehouse dashboard
Our Amazon warehouse dashboard

We’ve beefed up our inventory monitoring, projecting and ordering models.

With data from sales of each SKU, refunds, inventory status, our custom solution helps us stay ahead and not have to rely on Amazon seller central or inflexible Amazon seller software.

If you need to improve your data needs or supercharge your own internal tools, check out Gorilla ROI.

Recap of what Amazon sellers can do during this uncertain period

  • Come up with plans for different coronavirus situations
  • Be proactive
  • Monitor your pending orders and PPC
  • Ship boxes via UPS more often
  • Track your inventory and stay on top of it
  • Prepare to switch to FBM
  • Fix your broken processes and build up your tools
  • Use data to make objective and better decisions

Feb FBA monthly update at $238K

 

Feb FBA monthly update at $238K 13

Current Gorilla ROI Amazon sales progress. To see the interactive version, go to this page.

Note: These monthly updates are for educational purposes.


I’ve updated the chart to show the last 12 month (LTM) revenue compared to the goal of hitting $5M. Still a long way off, but the path to get there is clear.

Sales were down from January, which is expected because Dec and Jan are our best months. Then again, Jan this year was the best Jan we’ve ever had, so it’s hard to top that.

Nevertheless, this February was still a 20% improvement compared to Feb of last year.

Here are the stats and my thoughts.

Feb FBA monthly update at $238K 14

February highlights

  • Finished last month at $238k in sales
  • 20% growth from the same month last year
  • Margins increased slightly again as we had to raise prices to slow down sales
  • Removed all coupons without any drop in sales
  • Consistent conversion rate. Increased 1% to 21.1%.

Lowlights for February

  • Best sellers out of stock (note the plural)
  • Coronavirus, port congestion and delays
  • PPC spend over budget

How did Feb 2020 do overall?

If I was a robot and didn’t understand the current market sentiment, February did quite well.

There were hiccups in terms of supply chain and going out of stock which I get to in the next section.

Being up 20% compared to last year is a great start.

For a deeper context, in Feb of 2019, we had an average offer count of 59. This Feb, we had a total of 90. The 50% increase in SKUs was not a result of launching loads of new products, but simply creating more bundles and selling horizontally and vertically with our existing products.

We are now at a point where if we do bring on a new product, we can turn it into 10 different SKUs.

We’ve worked hard to go an “inch wide, mile deep” and now we can try and cast our net a little wider.

It’s still a numbers game because only 1 out of 10 listings will be a hit.

Based on our Amazon business model, I define a standard successful product as:

  • selling over 1,000 units a month
  • about 40% gross margins and 15% operating margins

There are different forms of successful products such as loss leaders, highest quality, highest margins and so on. It depends on your strategy, but to keep things simple, I’m just referring to the standard successful product definition for us.

Now, if the hit rate for a successful product is 1 out of 10, then it comes down to creating more variations and listing. To get 2 winners, it’s likely that I need 20 listings. To discover 3 winners, I’m going to need 30.

This is where the keyword siloing strategy I shared last month will help.

There are a lot of different ways to create variations so use some creativity because you just don’t know what people will like.

This is what we’ve experienced. Our hit rate total is going up and sales come with it.

The next thing I need to figure out is how to automate or at speed up the process of creating new Amazon listings using existing product information.

If you know of “flexible” software that can create new listings (not existing listings), please let me know – or maybe our Gorilla ROI team needs to create one.

Stopped all coupon sales

I’m a big supporter of coupons, and until the start of this year, I ran it on every product that could support it.

But it’s not cheap because coupons have an ACoS of 10% for us. 5% coupon plus the fee comes out to 10%.

If your products are priced $20+, the ACoS will be lower, but you are still giving up 5% at a minimum.

My reasoning for using coupons was to display the green coupon badge and get extra attention. Same goes for lighting deals.

amazon coupons lightning deal

But after checking how much I had spent on coupons in 2019 using the new profit and loss spreadsheet, I knew I had to stop. We ended up spending $18k on coupons.

coupon fee at amazon

Looking back, this was $18k that we could have saved. It would have been easy and wiser to run coupons for a month, measure the sales and conversion rates, and then turn it off and measure again. If there is no significant difference, there’s no reason to throw money at it.

Lesson learned.

At least in 2020, I’ll be using this $18k somewhere else.

As you can see, turning off our coupons has bumped up our margins a fraction. This is always welcome.

But if you have high priced products and a big market like the bluetooth earbuds industry, and huge competition, applying the coupon makes sense as it can absorb the cost.

Just remember to factor it into your operational expenses because this is on top of the PPC you are already spending on the product.

If the first earbud listing has a PPC ACoS of 30% and then 10% on top of that, the ad cost is 40% on this product. Whether that makes sense is up to the seller.

Higher PPC costs

We ran aggressive bids in Jan to capture the spillover from Dec. Looks like we were are little too aggressive towards the end of Jan and starting Feb though.

My optimal level is to keep total ACoS across the company down to 10%. That hasn’t happened for several years though. A realistic target is now 13%.

However, YTD total ad spend is 14.2% so I’m spending too much and need to reign it in.

But there are always different ways to go about it. Normally, you’d just turn off ads or reduce the bids. That’s the easiest and most obvious way.

The other two methods are to:

  1. Not go out of stock. If the Amazon listing says “in stock on March xx”, but the customer clicked an ad to the listing and they bounce, that’s a waste of ad spend.
  2. Sell more, higher margin products. More profit for the same cost.

We’ve reduced the bids for crazy high keywords. That’s a no brainer and the products that went out of stock, we’ll monitor it again this month and adjust as needed.

Out of stock, supply chain, Coronavirus

Our calculations and forecasting were doing so well until it wasn’t.

We get 1/3 of our products from China, 1/3 from Korea and 1/3 from the USA.

Yup.

China got hit with the virus and we were glad we had diversified to Korea. But then Korea got hit…

Feb FBA monthly update at $238K 15

In January, our Korean manufacturer just missed the container loading cut-off date so we lost 2 days until the next loading date.

But this was just before China got worse, so the next container we were waiting for at the Korean port, was stuck in China for 10 days before it finally arrived in Korea and our product got loaded.

This led to us going out of stock as sales kept increasing. Slight price increases, turning off the PPC helped a little, but not enough.

We went out of stock for about 3 of our best sellers in Feb.

Other supply chain issues came up like our US supplier missing our PO for 8 days.

It looks like more stock out.

Feb FBA monthly update at $238K 16

We try to implement as much redundancy and checks to prevent this, but there’s only so much we can handle as they are external factors.

That’s the frustrating part.

I’ve been communicating with our manufacturers and while some are operating, many are still struggling to ramp up production.

A lot are still working from home, some say they’ve started production again, but it’s at limited capacity.

Another one of my manufacturers quoted a lead time reduction of 30%. I don’t know whether that’s a good or bad thing.

Our position is to remain cautious as I don’t believe anything the Chinese government puts out.

Financial performance benchmarks

I’m leaving the 2019 numbers here because doing these numbers on a month to month basis doesn’t make sense. There are high fluctuations from one month to another. Easier and better to do it quarterly to see if we are hitting our objectives.

For 2020, my goals are:

    • COGS of 22% vs 23.3% in 2019
    • Amazon fees of 40% vs 40.6% in 2019
    • Gross profit of 34% vs 32.9% in 2019
    • Amazon ad spend of 12% vs 12.4% in 2019
    • Total operating expenses of 25% vs 25.5% in 2019
    • Operating margin was 9-10% vs 7.3% in 201

Wholesale and our online store

I had some bad news about our wholesale customers trying to go around our back. Our products are patented (utility+design) so I’ll have our lawyer take care of it.

Otherwise, wholesale and online sales are still a fraction of our Amazon sales. Not much to share than that.

New tutorials and guides

We’ve published some awesome tutorials and guides for Amazon sellers.

People ask about advanced techniques and how to build better and dynamic spreadsheets. The first and easiest is to understand how to structure your spreadsheet.

Follow the first link on the Amazon inventory management system, or you can read this simplified best practice to creating spreadsheets.

Free FBA spreadsheets for all

If you have messy sheets and need something refined to help run your numbers, get the free Amazon spreadsheets.

pro spreadsheets fba pricing

You can download it immediately without having to sign up for anything.

Just copy straight to your account.

If you want to get updated data straight into your own Google sheets, you can use Gorilla ROI. It makes work so easy when you don’t have to manually update data or log into accounts constantly and wasting time.

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. 17
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. 18

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. 19
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. 20

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

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

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

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

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 25

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 26

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 27
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 28
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 29

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 30

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 31

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

How to create an Amazon sales tracker with Google Sheets 33

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 34

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

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 36amazon 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

This is why you should not use Amazon research tools on your listing

[updated with additional info, screenshots and explanations]

The biggest Amazon research tools from the marketing campaigns I see frequently are:

  1. Jungle Scout
  2. Amz Scout
  3. Helium10 is up there too

Amazon research and scouting tools have changed the way you source and get data, but there comes a risk with using the tool.

What I’m going to show you is when and when not to use these research tools.

Not the how’s or how not to use.

There are plenty too many tutorials of people wanting to make affiliate sales who post video and articles of how to use it and why you should.

This post is different.

No affiliate links here. Just straight truth of when and when not to use Amazon research software.

Let’s go.

How Amazon research tools work

One area I find sellers to be overzealous is in the area of data protection and fear that a software company will steal their product idea.

I have found this to be far from the truth.

The real fear shouldn’t be whether the software company is going to use your data. But whether they use your data as part of their service to other users.

Scouting and research related tools work like this:

  • You give access to the tool and enter your seller ID and authorization token.
  • The software will start to download your data via the Amazon API.
  • (It’s fine up to this point because you can sever the connection anytime from seller central to protect your account.)
  • Free chrome addons or paid extensions for scouting and researching require you to feed the software company data.
  • The software will scrub, flatten and re-arrange the data so that it can be used.

Next comes the biggest difference between software tools.

  • Any tool that offers research functionality like inventory lab or paid web app tools will make your product searchable and viewable. It’s open for anyone paying for the software to access, find and analyze your product. This is called “one to many” access.
Basic idea of how Amazon research tools collect and provide data
Basic idea of how Amazon research tools collect and provide data
  • Software like Gorilla ROI and accounting tools (think Quickbooks) do not share your data with anyone. It is siloed to your account. Meaning we provide 1-1 connection access only. No one outside of you can see or find your data. Our service is not dependent on being fed data from other users for the rest of the user base to see and use.
Gorilla ROI Silo connection to prevent outside access
Gorilla ROI Silo connection to prevent outside access

If you sell yoyo’s, another seller will not be able to check your sales data on yoyo’s even if they know the ASIN. Each seller can only access their own data. Everyone is permanently walled off from each other.

Knowing this important if you are worried about other sellers finding your product.

Don’t like the idea of companies selling your data? Well, this is what research tools do. They scrub your personal and company information and then repackage it and sell their software for others to use the data.

When to use scouting software

The best time to use Amazon scouting software is to perform research on a product or to find a category or idea.

Think of it as a filter.

This is why you should not use Amazon research tools on your listing 37

Scouting tools help you when you are at the top of the funnel with thousands of different products and categories to choose from.

It helps you narrow the list down.

The lower you get in the funnel, the less you need scouting software.

I won’t get into the details of how to use the tool because there are so many tutorials out there.

When not to use Amazon scouting software

I’ve used Jungle Scout, Helium 10, seller app, and countless other tools. I canceled all accounts that offered an all-in-one service and ultimately chose AMZ Scout as my chrome extension.

With the majority of Amazon sellers using Jungle Scout, every time the tool is run on a particular product, the data is entered into the Jungle scout servers.

You guessed it.

The more people there are using it, the more likely people will find it.

With AMZ Scout, it’s lesser to some extent.

The key is once you have identified your niche and product, stop using the scouting software. There is no need to continually feed the latest data into a database for tens of thousands of other people to find.

One day when AMZ Scout made an update to their tool called “saturation”, it really opened my eyes to the data they are seeing.

AMZ SCOUT saturation score shows data on how many people found it
AMZ SCOUT saturation score shows data on how many people found it

This is for a random skin routine product I came across. Looks like 4 other people found the exact same product and brand.

Here’s a look at bbq gloves. One of the most copied and private labeled products on Amazon.

This is why you should not use Amazon research tools on your listing 38

Other tools have the same thing, but AMZ Scout now shows you how many other people are searching for it.

I assume the numbers are calculated based on a monthly tally or something. 39 people in the last 30 days looking for bbq gloves. Not a product I want to be in.

For this very reason, once you know your product, it is better to try and keep it under wraps by not using scouting tools.

You have the experience and judgement to figure out how much something sells based on the BSR.

The best way to use research software

Using these tools to keep tabs on your competitors is a good strategy.

BUT

If you are searching in such a way that will expose your product, then it’s not good.

The better way is to use the research tool on your competitors Amazon storefront.

  • Not the search results after typing a keyword
  • Not the listing page

The Amazon Storefont – because it will only list the products in their storefront in the tools.

This way, you can hide your product while giving AMZ Scout or whoever, the data of your competitors.

Sure.

People can still trace it back to your product, but at least you aren’t updating your product and data for all to see.

Amazon research tools on competitor Amazon Storefront
Use Amazon research tools on competitor Amazon Storefront

Summary

  • Scouting and research tools are great and have made work so much easier.
  • Once you get past the initial stages of finding your niche and product, stop using the tool.
  • Publicly available tools require data to be loaded onto the servers allowing for others to use it.
  • Use the tools to look up competitor storefronts.
  • Use your internal data to keep track of your sales like with Gorilla ROI.

Jan FBA monthly update at $282K

amazon income report Jan 2020

Gorilla ROI Amazon sales progress. To see the interactive version, go to this page.

Note: These monthly updates are for educational purposes.


I’m surprised by our January results.

January is usually good to us, but normally it translates to numbers that are in line with December results.

After all, December is a shopping bonanza and the best month of the year. But January was much better than usual.

Here are the stats and my thoughts.

January highlights

  • Finished January at $282k in sales
  • Margins increased from December as we kept prices steady but also raised prices on our best sellers to try and slow the sales
  • New bundles and variations we created boosted our presence even further
  • 11% growth from Dec
  • 35% growth from last year Jan
  • Consistent conversion rate of 20.5%
  • Cleaned up and switched accounting from cash to accrual (finally)
  • New keyword/content siloing strategy going into effect for new listings

Lowlights for January

  • Coronavirus
  • Sales spikes and supply chain management
  • Wasted spend on failed lightning deals
  • Port congestion and delays
  • Going out of stock

How did January 2020 grow?

Did you read our 2019 end of year results and thoughts?

Why are we up 11% from December?

Jan FBA monthly update at $282K 39

I did lay out some growth plans in the December report, but to say it’s working within 1 month or implementation is a stretch.

I admit, I don’t know the single reason. And that’s OK because it’s likely a result of multiple factors.

  • People usually buy our cleaning products to start the new year
  • People received Amazon gift cards for Christmas and want to spend it
  • Economy is better than what the news makes it out to be
  • Amazon’s recent review count updates where they’ve merged products again and many buyer accounts had their reviews deleted again

These reasons are mostly secondary and a by-product of putting in the groundwork.

I would say the biggest contributor to the increase in revenue though are the new listings of variations and bundles we created throughout Oct and Nov.

I can instantly grab monthly sales stats per sku and then use Google Sheets sparkline feature to create small charts and show the monthly sales trend.

monthly amazon sales stats gorilla salescount

Side note: to get monthly data, enter the dates as YYYY-MM. Then use the format of the formula below for each month.

=GORILLA_SALESCOUNT("2020-01", "US"A4:A500"all")

I’m looking forward to creating more listings and casting our net wider and farther to dominate the search results, sponsored ads areas and getting our listing to be synonymous with our target keywords.

Which brings me to…

Keyword Siloing

Jan FBA monthly update at $282K 40

If you are familiar with SEO, siloing content and keywords is a polarizing topic. But in Amazon’s world, it works.

What is siloing?

By definition

Silo = isolate (one system, process, department, etc.) from others.

For listings, keywords and Amazon SEO, it’s allowing a listing to rank for a single purpose keyword.

I’m going to assume that most people (myself included) started out by creating product listings and threw in hundreds of keywords into the listing and backend keyword boxes. The intention was to try and get on the first page for all those keywords with the single listing.

More keywords = more traffic = more sales.

Right?

Wrong.

Since you are trying to win the Amazon’s choice award, there has to be correlation with the keywords your listing is targeting and what the shopper is searching.

You can only win one Amazon’s Choice badge at a time.

Here’s an example for “bluetooth speaker”.

Jan FBA monthly update at $282K 41

This listing is going after keywords such as:

  • portable outdoor speaker
  • portable outdoor bluetooth speaker
  • shower bluetooth speaker
  • bluetooth speaker

The listing has 3 variations using the same title and information.

This is how we built most of our listings early on, and now as we try to improve them, it is a major pain. Since the title contain too many keywords, the listings that sold well cannibalized the other variations.

Jan FBA monthly update at $282K 42

In the bluetooth speaker listing, they have 3 child variants.

They could have given a unique keyword to each of the color variants such as:

  • Black = portable outdoor speaker
  • Green = shower bluetooth speaker
  • Blue = waterproof bluetooth speaker

This way, each variant has a specific objective and they aren’t stepping on each other’s toes.

Trying to fix this later when you have sales is going to be tougher because it could break the entire family of listings and sales will tank.

Try doing an assessment of your products to see what keyword it is converting for.

If the black speaker above is converting the highest for “portable outdoor speaker”, the entire listing should be updated to go all in on that keyword.

Other words will naturally be created in the copy, but you’ll be sending signals to Amazon and shoppers that the black speaker is THE product for “portable outdoor speaker”.

This is what we are doing for all of our younger listings. By siloing keywords and content we can make sure we aren’t overlapping and cannibalizing sales of what could be deemed similar.

Applying such changes to mature and strong listings is a different story and requires more precision and analysis. No need to fix what’s working well.

Coronavirus and impact to sellers

Jan FBA monthly update at $282K 43

If you do not source from China, what a huge relief.

For the rest of us, it’s trying to figure out the impact it will have. There are many people saying that this isn’t a big deal and downplaying what’s been going on.

I’m talking from business side here and not on the disaster itself.

I’ve found it helpful to be realistic and prepare for bad case scenarios versus being optimistic and being caught flat footed. That’s how businesses went out of business.

Jan FBA monthly update at $282K 44

Blackberry and Samsung laughed at Apple when they first launched the iPhone. I know because I was part of the Samsung mobile division that launched phones for the US market from 2006-2014.

Blackberry is now dead in the water. Samsung scrambled and got back to #2.

You don’t have to go into full emergency mode, but factoring in 1-2 months of delays and rehearsing scenarios on how your team should handle and make decisions in these situations is a helpful and preventative measure. It’s also great training.

I would rather have an extra 1-2 months worth of inventory (overstock) of best sellers than be out 1-2 months of best sellers.

The coronovirus is going to impact:

  • delays from raw material suppliers as they can’t make enough
  • delays from manufacturers not being able to produce at full capacity
  • delays because bigger companies may have placed massive orders and takes priority over yours
  • delays as quality goes down because manufacturers are scrambling to manufacture and QA is a distant second
  • delays because you have to find new or more inspectors
  • delays as more paperwork from China is required for exporting and backlog of outgoing shipments at the port
  • delays as manufacturers will play the force majeure hand and demanding an adjustment of the costs

Dismissing the impact of coronavirus means you are saying that none of the above are big risks and your supply chain is perfect.

It’s also a wake up call because China wasn’t a big deal for Amazon sellers in 2003 during SARS. In 2020, it’s safe to say that most US sellers rely too much or exclusively on Alibaba and Chinese suppliers.

Diversifying supply chains to different countries is never a bad idea. It’s a lot of work, but it can pay dividends later on.

For sellers who created their entire business based on Amazon+Alibaba, that’s a double whammy.

Even if you source 100% from USA or another country, you will have all your eggs in one basket. If a black swan event happens in America, the same issues apply. Having backup manufacturers to existing manufacturers is something we try to do in order to diversify the risk and once it is set up, it isn’t as hard to shift manufacturing from one place to another.

Again, it’s a lot of work. It’s like insurance. When you need it, you are super glad you have it.

Mismanagement of lead times… argh

Jan FBA monthly update at $282K 45

In my December report, I was all proud that we didn’t run of stock for the Christmas rush.

Well, I spoke too soon.

Our sales have been spiking (good problem to have) but that means our inventory is running on fumes. A steady increase in sales is something we can project, but sudden 30-40% surges lasting for several weeks to a month means our buffer inventory is depleted.

To add to that, our containers were and is delayed at the port of departure, got held up in customs for exams and x-rays, and has been sitting idle for over a week.

Frustrating.

We’ve recalled other variant bundles from Amazon inventory that aren’t as important so that we can reallocate the product to more important listings.

This got me thinking about calculating lead times.

To date, we’ve used a simple method of the manufacturing lead time + 15 days.

No wonder I’ve gone out of stock so often. I realized that calculating the lead time is just like calculating the landed cost.

With landed costs, you have to factor into the pricing:

  • cost to manufacturer
  • packaging components
  • handling charges
  • duties
  • taxes
  • shipping
  • and anything else that goes into making the product.

With leadtimes, I’ve only been thinking about:

  • time to manufacture
  • time it takes to arrive

When it should be broken into every detailed step.

  • time from order submission to get raw materials
  • time from start to end of production
  • time for QA or send inspectors
  • time to remake failed units
  • time to pack and ship
  • time at the port of departure
  • time on the boat
  • time at the port of arrival
  • time of potential x-rays and exams
  • time of delivery
  • time to inspect
  • time to repack
  • time to ship to Amazon
  • time to get to Amazon’s warehouse
Jan FBA monthly update at $282K 46
source: oracle blog

My standard leadtime calculation turned out to be the best case scenario with no delay calculation.

In reality, it should have factored every single touch point for a realistic lead time.

Now that we’ve updated our lead times into our calculations, I’ll keep you updated on how it works out.

Financial performance benchmarks

I’m leaving the 2019 numbers here because doing these numbers on a month to month basis doesn’t make sense. There’s high fluctuations from one month to another. Easier and better to do it quarterly to see if we are hitting our objectives.

For 2020, my goals are:

  • COGS of 22% vs 23.3% in 2019
  • Amazon fees of 40% vs 40.6% in 2019
  • Gross profit of 34% vs 32.9% in 2019
  • Amazon ad spend of 12% vs 12.4% in 2019
  • Total operating expenses of 25% vs 25.5% in 2019
  • Operating margin was 9-10% vs 7.3% in 201

Wholesale and our online store

Wholesale is slower this month. January doesn’t have much going on as customers already stocked up during Christmas.

Found one customer who is now selling under our listing on Amazon.

That’s the problem if customers lie about selling on Amazon. Only thing to do is cut them off the wholesale list.

New automation and operational articles

More articles, tutorials and operational articles were posted.

Free FBA spreadsheets for all

If you have messy sheets and need something refined to help run your numbers, get the free Amazon spreadsheets.

pro spreadsheets fba pricing

You can download it immediately without having to sign up for anything.

Just copy straight to your account.

If you want to get updated data straight into your own Google sheets, you can use Gorilla ROI. It makes work so easy when you don’t have to manually update data or log into accounts constantly and wasting time.