Free Bill of Materials template spreadsheet to negotiate and track pricing

bill of materials spreadsheet template
Bill of Materials template spreadsheet available for download at the end

By the end of this article, you will

  • understand why Amazon sellers should use a BOM
  • how to use it to negotiate with your suppliers
  • how to design your own bill of materials template
  • download a free bill of materials template spreadsheet

What is a Bill of Materials (BOM)?

A bill of materials (BOM) is an extensive list of raw materials, components, and assemblies required to construct, manufacture or repair a product or service. – Investopedia

Think of it as a list of ingredients of a recipe to manufacture a product. Just without the instructions.

I also think of it as a checklist to manufacture a product and to record my costs.

Here’s why.

Biggest mistake of not using a Bill of Materials

If you manufacture in China, one of the biggest mistakes you can make is not using a Bill of Materials.

  • You can easily get ripped off
  • You end up with low quality goods
  • You find quality fade with future orders

Why?

There are great factories in China. After many failed products and disasters, we finally found our perfect manufacturers. But it took a lot of effort and using a BOM helped substantially.

Tell me if I’m wrong, but typically, your process for finding and ordering with suppliers will be like this:

  1. Go to Alibaba and search for a product
  2. Contact 3-5 factories and ask for a quote of the product they listed
  3. Finalize down to a couple
  4. Get samples
  5. Choose a single factory
  6. Make a small order
  7. Send inspector
  8. Send shipment
  9. Receive shipment
  10. Get shocked with low-quality products or how you were ripped off

In China, a Bill of Materials will help to prevent this.

How a BOM will save manufacturing problems

Ok.

A BOM won’t save all your manufacturing troubles, but it eliminates miscommunications and murky expectations immediately.

By having a detailed breakdown of how the product is manufactured, you end up with a lot of leverage, transparency and you can control the product.

With a BOM:

  • you know how much each component will cost
  • if prices rise – you can trace it to which component is causing the price increase and determine whether it’s true or a lie
  • if quality fades – you can trace it to the problem
  • when comparing suppliers – you can compare apples to apples and see what materials they are using and how much they are charging
  • know if you are dealing with a middle man or not. Middle men won’t be able to give you a BOM or detailed quote.

If your price quotes are very basic and look something like this from a supplier:

  • yoga mat 20×30: $12

where you are only getting a single dollar value, and there is no breakdown, you are likely overpaying.

It’s convenient to get an all in one price, but it’s never the best. You don’t want your supplier to roll up prices into a single value.

Knowing your exact costs means that you can control the negotiations. Not be controlled.

bill of materials template cogs breakdown

This is a screenshot from our pricing calculator, but a BOM template is simpler.

Bill of Materials example – Plastic organizer

Regardless of how simple you think your product is, there are too many different materials to ignore.

Look at this plastic organizer.

bom example

When manufacturing this, you could go the easiest route and ask for the exact same thing and only change the sticker or the box design.

This is what most newbies do anyway – and then lose money and cry that it’s impossible to sell on Amazon.

But you’re the serious buyer and the supplier will ask you questions like:

  • What type of plastic do you want to use? PP, ABS, AS
  • Quality of the plastic
  • Density of the product
  • Finish of the plastic
  • Color of the plastic
  • Mold costs and if there are more than 1 required
  • Labels that go with the organizer
  • Type of packaging
  • Any wrapping
  • Type of cardboard, thickness, quality
  • and the list goes on

Sure. Not everything is related to the BOM, but it can easily be integrated.

You may think you know it all as you go through it one by one with the manufacturer, but what about 1 year later when you have a make a change?

Or when another person in your company needs to take over the project?

Or what if you need to switch manufacturers?

A BOM means you don’t have to start from scratch and you know your exact requirements and cost.

If you were paying $0.20 for a certain GSM cardboard, and the new supplier is quoting $0.50, you can bet they are taking a cut.

You can now bring this up and ask what the difference is. You may choose to supply the cardboard to them if they keep insisting it is the best price they can offer you.

How to use this free BOM template

There are two main tabs.

  1. Parts List to host all the detailed information regarding raw materials, parts and all the nitty gritty details.
  2. The BOM template where you would construct the full list which you can then use for reference or save as a PDF.

1. Parts list of the BOM spreadsheet

In the tab titled “1.PartsList”, this is where you enter detailed information of each part. The more granular you get, the better in the end.

Add as many columns as needed like manufacturer, weight, size, shape, brand, supplier and more.

The spreadsheet is unlocked and you can customize it 100%.

bom template instructions parts list
BOM template instructions parts list

Most of the data will have to be entered manually. You probably already have a list that you maintain somewhere else. You can copy it into this sheet.

You can mix and match data from Amazon if you wish. If there is a specific data point like brand or dimension that you want to pull, use the matching  Gorilla function to get this information.

E.g. you can get product info like titles, weight, dimensions using GORILLA_PRODUCT(), category using GORILLA_CATEGORY() and also the pricing of the final product using GORILLA_MYPRICE().

A quick word on the naming convention.

You can give a part a “part number” or a “SKU”. They can be used interchangeably because each part should be unique.

The parts list is not the “product SKU” because parts can include raw materials and all the little components that make the final product.

Consider the parts list as the master data file. You have all the detailed data information and the BOM template will pull the matching details from this parts list into the final BOM list.

The concept of organizing this bill of materials is the same as the in-depth tutorial on creating the sales tracking spreadsheet or the Amazon inventory management spreadsheet.

2. Compiling the BOM list

bom compilation template

This is where the fun begins as you can see the fruit of your labor.

The first column is linked to the part number you entered into the parts list. A drop down is used so the information is quickly pulled using vlookups.

The middle body section where the cells are gray, means that it is using a formula (vlookup) to automatically load the information for you.

Quantity must be entered manually as every product will be different.

The total of the product cost is displayed at the bottom.

Now you can keep this tab as it is and make a copy of it to create a BOM for another product, or save as PDF and keep on file.

Download Bill of Materials template for Amazon sellers

Although my focus is on Amazon FBA, you can use this free BOM spreadsheet template for any business that involves making something.

Free Bill of Materials template spreadsheet to negotiate and track pricing 1

Other free spreadsheets and resources for Amazon sellers

Using the Amazon Fee Calculator to maximize profits

Looking to get started with Amazon?

The Amazon fee calculator is an essential tool for sellers to figure out the profit earned per unit sold.

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

  • Calculate the variable and fixed costs using the FBA fee calculator
  • Determine the real profits using different elements
  • Cut down the overhead costs
  • Identify factors that have an effect on the cost of goods sold(COGS)

This way, you can figure out whether using FBA is profitable for your product and business.

Why should you listen to us?

I have lost so much money with bad quality, shady manufacturers and wasted just as much money on bad agencies and advertising. We’ve taken the school of hard knocks.

Today, we sell over 7 figures with a gross profit of 30-40% blended average. After taxes, our net profit is 10-15% profit.

We are in the trenches running a successful Amazon business. You can read our income reports where we provide a lot of transparency on the wins and fails of our business.

Unlike gurus and promotional youtubers, we do this as our bread and butter and share what it is truly like.

No Ferraries. No Lamborghini’s.

Just regular folks working hard at creating products, navigating the ever changing landscape of FBA and making profit.

The Amazon FBA profit calculator: Quick overview

Here’s some cold hard truth.

Newbie FBA sellers lose money because they don’t use the right numbers to calculate a product’s profit potential.

Every niche is potentially profitable if you know your numbers.

That’s why it’s important to factor in a breakdown of all your costs that include, product COGS, freight, shipping, fulfillment costs and Amazon seller fees.

I get into how the FBA  program compares to Fulfillment By Merchant in terms of the overall fees needed to fuel your e-commerce business.

Understanding the Amazon fee calculator

Using the Amazon price calculator, it’s easy to get the associated expenses of existing products selling on Amazon.

The goal of the calculator that Amazon provides is to help you decide whether FBA or FBM is cheaper. It’s not to calculate the deep, detailed profit analysis that you may be looking for.

Their focus is to make you see that it’s likely just as cheap to use FBA rather than fulfill products on your own.

I have a calculator for that which is offered in our PRO Amazon Business Spreadsheet package.

There are other risks associated with letting Amazon handle the shipments such as lost inventory, high customer returns, more margin required to offset the fees, increase in PPC costs. But that is a different topic.

PRO TIP: Amazon's goal is to get you selling on their platform.

Amazon FBA revenue and cost calculator for each marketplace

Each marketplace has a different fee due to logistics and currency. $1 USD does not equal 1 EURO.

Each marketplace has their own adjusted calculator. Europe is mostly all going to be the same. New market additions include United Arab Emirates, Netherlands, Singapore and Australia.

  1. US FBA Calculator
  2. Canada FBA Calculator
  3. Mexico FBA Calculator
  4. UK FBA Calculator
  5. France FBA Calculator
  6. India FBA Calculator
  7. Italy FBA Calculator
  8. Germany FBA Calculator
  9. Spain FBA Calculator
  10. Netherlands FBA calculator
  11. UAE FBA calculator
  12. Australia FBA calculator

If you sell on multiple marketplaces, use the correct calculator to adjust your pricing accordingly.

PRO TIP: Selling in another country's marketplace is always more expensive than your home country cost. Adjust pricing to match the increase in cost.

How does the Amazon Fee Calculator work?

There are lots of Amazon fulfillment fees that get confusing – especially for new sellers.

Using the FBA fee calculator, you can identify the following expenses for any products when you load the ASIN into Amazon’s calculator.

  1. Removal order fees
  2. Returns processing fees
  3. Inventory storage fees
  4. Long-term storage fees
  5. Pick and pack fees (now all included as Fulfillment Fees)
  6. Inbound shipping (using Amazon’s partner carrier to send inventory to warehouses)
  7. Referral fees (AKA closing fees)

Open up the correct marketplace calculator for where you are selling and enter the product title, UPC, EAN, ISBN or ASIN.

If you are currently not selling, use a competitor product to get the fees and details.

amazon fee calculator search bar upc asin sku

In this example, I entered “bluetooth earphones” and some recommendations show up.

fba revenue calculator results product title

Select a product to use in the calculator and product details show up.

Using the Amazon Fee Calculator to maximize profits 2

You can see the ASIN, package dimensions and the weight.

Amazon FBA fees is based on dimensions and weight. That’s how they calculate their fees.

The main calculator is now activated and looks like this.

fulfillment fees- Amazon Calculator

 

This is where you enter your costs for when you ship via your own online store and another one for when you ship using FBA.

Clicking on “cost of seller fulfillment” will open up another small box where you can enter numbers of more detail like how much you currently pay for:

  • monthly storage
  • labor
  • packaging material
  • shipping to customer
  • customer service

The dashboard uses simple form fields to give you a complete report of all the costs which include the selling fees, FBA fees, and estimated net profit.

Let’s see how it compares with the following inputs.

amazon profit margin fbm vs fba
Amazon profit margin FBM vs FBA

Remember Amazon’s goal. It is for you to sell using FBA.

But if your product pricing is good, FBM or selling on your own site is always more profitable if you have good traffic.

FBA is very powerful because of the traffic with very little effort.

fba revenue calculator showing sales flow through net profit
FEE calculator showing sales flow through net profit
PRO TIP: "Net profit" on the calculator is not the actual profit margin. It does not consider other operational expenses such as advertising, returns, insurance, etc.

Unlike typical calculators, this FBA-optimized tool will help you see your numbers on a cost per unit basis. This is the best way to calculate profit rather than using absolute sums.

It’s why our Break-even calculator works much better because it’s focused on a per unit basis.

You don’t need real numbers. You can use estimates and get worthwhile results that give a rough blueprint of the total fulfillment cost per unit sold.

What are FBA selling fees?

The main FBA fees you’ll be dealing with.

  1. Removal order fees
  2. Returns processing fees
  3. Inventory storage fees
  4. Long-term storage fees
  5. Pick and pack fees (now all included as Fulfillment Fees)
  6. Inbound shipping (using Amazon’s partner carrier to send inventory to warehouses)
  7. Referral fees (AKA closing fees)

We already have a monster guide on all the Amazon seller fees and what they are. There’s also a free spreadsheet in that article.

But let’s tie it in with the Amazon fee calculator.

FBA fees are the associated expenses that the seller incurs when the buyer makes a purchase on Amazon.

These fees apply to all sellers, regardless of whether you use FBA or FBM to deliver orders to the customer.

The FBA fees vary depending on the kind of service subscription a seller prefers to work with. Amazon merchants can choose the Professional or Individual plan.

For sellers who fulfill 40 units or less, the Individual account works just fine. While it doesn’t come with any monthly commitments, you’ll pay about $0.99 for any item you fulfill using the FBA program.

If you upgrade to the Professional plan, the monthly subscription fee is $40.

PRO TIP: Don't waste your energy nickel and diming. $40 is not a lot to get started. Think of it as a cost of doing business and to unlock all the features. You will never do 7 figures in sales by trying to save a few cents here and there.

In contrast to the Individual plan, your sales volume isn’t limited, and even better, you don’t pay any fees for each item a customer adds to cart.

If you don’t believe you can sell 40 units a month, it is not even worth doing Amazon FBA as you will pay all sorts of other fees and it is more expensive overall. Better to test your product and idea on eBay.

Both Professional and Individual sellers also need to factor in the referral fees, which range from 6% to 15% and fluctuate based on the item category.

Unlike other expenses, referral fees are like commission paid to Amazon only after the sale of a product is made. Amazon gets a cut for you to use their platform.

To figure out the actual profit, remember to check any “hidden costs”. We’ll look at all these fees in a moment. This is easier to figure out if you use the Amazon fee calculator.

The FBA cost you must understand

Other than the Seller account fees, the FBA profit calculator lets you take into account all the associated fulfillment expenses.

Fulfillment fees depend on whether you want to use Fulfillment by Amazon or handle the logistics yourself.

If you have any stale inventory sitting at Amazon’s warehouse,  you will need to pay the inventory removal fee to get rid of the unsold stock. This costs anything between 50 to 60 cents per unit. You can dispose of it, but what Amazon does is sell it off in bulk at 1-10c per unit.

Here’s an example of someone who purchased supposedly “disposed” items at discount.

Such an expense may seem trivial but starts to eat up margin if you have to recall massive amounts of inventory.

FBA covers things like warehousing, shelving, packing, shipping, customer support. It makes it very easy to run and scale because all the components are offered by Amazon.

You can have the product shipped from China directly to Amazon so that you don’t even have to touch the product.

For returns processing, the FBA price calculator allows you to get the exact cost per unit, which starts at $2.50 to over $100, depending on the size and weight of the shipment.

It all comes down to reducing your FBA fees because the lower it is, the higher your margin.

This could be packing your product so that it is as thin as possible to get in the cheapest rate of $2.50.

Or if you sell a long item, see how you can reduce the length down. By being creative with how the product is packed, you can bring down your fees and fatten your margins tremendously.

Don’t forget about indirect and variable costs that affect profitability

There are indirect costs that pile up and flatten your profit margin curve. Things like insurance, cost to ship product samples, bookkeeping, warehouse rent, and so forth.

Let’s say your gross profit is 40%, but if your costs are so high that the cost of doing business equates to 30%, you end up with 10% net profit. Not so great.

This is why you need a finer breakdown expenses to track your profitability.

Some categories of expenses you should consider:

  • COGS: product cost, freight, shipping to amazon, software
  • Operating expenses: PPC advertising, rent, labor, auto, insurance
  • Misc: taxes, travel

To get more granular, this is where our Gorilla ROI break-even calculator comes in.

A better Amazon FBA profit calculator. Our break-even calculator.

break even analysis

This Amazon calculator is a spreadsheet template included in our Business PRO spreadsheet package.

Full instructional guide on how to use this break-even analysis is here.

A break-even point is simply the point at which your sales equals the total expenses of the product after including all fixed and variable costs.

All the expenses mentioned above can be broken down to a per unit basis.

  • COGS: product cost, freight, shipping to amazon, software
  • Operating expenses: PPC advertising, rent, labor, auto, insurance
  • Misc: taxes, travel

For an Amazon fee calculator to truly show you profitability, it must account for all these factors.

If you can sell more than your business expenses, it is profitable.

If not, you lose money for every unit sold.

Using this calculator, you can accurately divide the number of fixed costs( seller fees), with the revenue per unit minus the variable operating expenses(think shipping costs).

The major problem with free break-even calculators on the internet is that it does not go beyond the bare basics. It assumes the production costs are always constant.

This calculator is not only limited to Amazon sellers. It uses a flexible formula that fits merchants selling digital products and e-commerce retailers on other marketplaces such as Etsy, Walmart, or eBay. Just update the names of the expenses.

Gorilla ROI Accurate Product Pricing Calculator

Before trying to figure out what price you should sell a product, the best method is to understand what margin you want.

Costco and Walmart work with 1-2% net margin (after taxes and interest).

Other businesses have net margins as high as 15-20%.

For more volume, lower your margins. For more profitability, increase your margins.

PRO TIP: Use a benchmark margin for your business. Don't randomly assign margin requirements.

If your selling price does not cover the fixed and variable costs, don’t expect to rake in any profits.

Every Amazon seller needs to learn how to price products for profit and exponential growth.

With the product pricing calculator, you can crush your competitors by knowing what is the min and max price you should sell a product.

For example, our company runs on a target gross margin of 40% and 15% operating margins. We enter all of our fixed and variable costs and desired selling price.

The product pricing tool auto-generates a consolidated profit & loss statement which indicates if the target price is really profitable. The cool part is our sensitivity matrix which shows the change in profitability as the selling price decreases or increases.

product pricing revenue calculator

Based on my costs and inputs, it’s telling me that I need to sell between $23.94 and $26.60 to achieve my margin requirements.

If the price is too high, every seller and their mother will want a piece of the action. If I need to liquidate it, I can go down to $20 and still be profitable.

Strategies for FBA sellers to maximize profit margins

Know your costs

Identify all hidden costs. It’s as easy as going through your transaction reports and jotting down all the stuff Amazon takes from you. Then include it as a per unit cost. This way, you’ll be able maximize your ROI.

We take it as granular as the poly bags that is used for shipping. It’s $0.01-$0.025 only, but when shipping out 100k units, it starts to add on.

Where most people have trouble is calculating their operational expenses.

Instead of summing up the total lease, electricity, water, trash, internet etc etc. The best and easiest way is to open up last year’s P&L and look up the number for “total operating expenses”.

Then divide the number from total sales.

Total Sales ÷ Total Operating expenses = Operating Expense %

If you end up with something like 20%, it means that 20% of your sales goes towards operating expenses.

Easy.

Reduce returns

We track returns as a percentage and think of it like discounts. It’s the cost of doing business, but the more you can reduce it, the better.

On Amazon, we average 2.3% in returns, which you can see from your business reports.

tracking refund rates returns profit analysis

On our Amazon pricing calculator, the refund rate is taken off the total sales, much like a discount.

If our refund rate is 5% and the selling price is $100, we assume net sales of $95. Not $100.

Using the Amazon Fee Calculator to maximize profits 3

However, if you can’t anticipate and control returns, you will leak profit. This should be accounted for when you calculate your costs and added back into pricing.

Here are some simple ways to reduce return rates and maximize returns.

  1.  Use detailed and correct product descriptions. The item which you ship to the buyer must have specifications that match the description on the product page. You can also include more context on how to resolve a claim where the product is faulty.
  2. If you’re sourcing products from a supplier, negotiate a discount or partial refund anytime they ship a damaged product to your potential buyer.
  3. Make adjustments to your order handling if there is an increase in return requests, damaged goods or chargeback claims.
  4. Use high-resolution images to give the buyer the perfect idea of what features your product is made of. Clearly describe the current condition(new, used).
  5. Use short videos. Phones can create great product videos. Keep it simple.

Negotiate with your suppliers

This is a no brainer, but had to include it.

The better your pricing is, the better your margins.

But be careful. Many suppliers will agree to your lowered pricing without objection, because they lower the quality accordingly to make their own profit.

PRO TIP: always focus on win-win pricing. Don't bully suppliers as you will shoot yourself in the foot once you receive the product.

There are many areas where you can negotiate better rates or use creative negotiations. Ask for discounts at different quantities, meeting performance, after a certain time etc.

This applies to freight forwarders. Find a win-win relationship and your fees go down, profits go up.

While making negotiations, it’s more practical to show interest that you’re looking for a long-term business relationship. From this perspective, it would be much easier to secure a better deal from your supplier.

Final thoughts: is the Amazon FBA calculator worth using?

It’s free. So yes.

The best use of the Amazon fee calculator is to find the fees that a competitor is paying.

You then feed this info into our breakeven calculator or Accurate Product Pricing calculator to get the best details and analysis.

There’s a lot missing on Amazon’s calculator, but it’s a good start. The only problem is that you could end up with a false picture. Thinking it is more profitable than it really is.

Make the best use of the FBA profit calculator, together with the above-listed spreadsheet templates to streamline your margin projections and cut unnecessary costs that downsize the potential ROI.

Break-Even calculator and formula for Amazon online sellers

break even analysis

By the end of this article, you will

  • understand how to use the break-even calculator
  • know the formulas to make this break even analysis
  • learn how to extend it to your own analysis
  • know how to interpret the numbers and break even point
  • make the right decision to break even on your products

This is a spreadsheet template that you can create on your own and can be applied to any sales channel. Not just limited to Amazon

This break even calculator spreadsheet is available as part of the PRO Amazon Business Spreadsheet Package.

What is the break-even point?

The simplest definition of breaking even, is to make enough profit to cover all expenses.

But this definition isn’t helpful for Amazon or online sellers who are trying to figure out whether a product is worth adding to their portfolio.

It’s like telling a first time driver to put one foot on the accelerator and press down to drive.

Completely useless in real life.

That’s the problem with many break-even calculators and analysis because it doesn’t clearly define or break down the details for you and it is too generic.

I’ll share all the details with you and more. If you make it through the entire article, you’ll be able to nail it when analyzing your product and calculating the break even point.

Why a break-even analysis is vital

I’m going to use Amazon as the example sales channel. The concept and calculations are the same for ebay, Walmart, Etsy, Craigslist etc.

You’ve done all the work and know what product you are going to sell.

  • Finding a niche
  • Finding a supplier
  • Negotiating with the supplier
  • Received samples
  • Tested the samples
  • Made some custom changes to the product
  • Designed the packaging

Now you are ready to order, but before you go any further, it’s imperative that you’ve performed your break-even analysis.

If you think it will cost $1000 to get the product to your warehouse/home/business, it doesn’t mean you only have to sell $1000 to break even.

This is how newbies lose money. I wasted $70k because I didn’t know how to do a break even calculation properly. I underestimated everything.

Every detail that adds to the landed cost of the product must be identified. Your original estimate of $1000 could mean you need $10,000 in sales to break even.

Don’t allow yourself to be surprised. Follow the formulas and examples below.

How to calculate the break-even point of a product

The standard method of calculation is to use a fixed cost, variable cost per unit, sales price per unit and quantity sold.

I don’t like this because it is mainly for service based businesses.

Standard break-even formula method

E.g. you run a dentistry. The fixed cost per month is $10,000. Dentists are paid $50 and hour, clients are charged $100. What’s the break even revenue?

Break-even Sales = Fixed Cost / [1 - (cost ÷ sales price)]

10000 / [1 – (50/100)] = $20,000

The dentistry needs to make $20,000 in sales each month to break even.

But how does this apply if you are trying to figure out whether a PRODUCT is going to break even or not?

That’s where our version of the Break-even calculator comes into play for Amazon and online sellers.

Breakeven formula for Amazon and online sellers

I keep it simple and to the point.

The minimum break-even point is:

Sales = COGS + Fixed & Variable Costs

You get back what you spend. You don’t lose any money, you don’t lose any money.

Let’s say you are importing Tennis balls from China.

The basic theory and formula to break even is this:

SELLING PRICE - TOTAL COGS = GROSS PROFIT

Break-Even Point is when: GROSS PROFIT = TOTAL COGS

Profit is when: GROSS PROFIT > TOTAL COGS

HUGE Losses occur when: GROSS PROFIT < TOTAL COGS

If you can’t break even on your gross profits, you will bleed money on your operating and net profit.

  • Tennis balls = $1.00
  • Total COGS per unit = $8.72 (after all taxes, tariffs, fees, commissions, shipping)
  • Selling price = $18.00
  • Gross profit = $9.28

Since the gross profit is greater than the total cost per unit, you will profit. Break-even is when the gross profit is equal to the total COGS.

We’ll get into deeper examples and in-depth calculations, but the theory to understand first is that, if your gross profit is less than the total cost per unit, you will never break even with your order quantity.

Using the same example, let’s change the selling price to $15 because you “think” you have plenty of margin since the tennis balls “only” cost $1.

  • Tennis balls = $1.00
  • Total COGS per unit = $8.72 (after all taxes, tariffs, fees, commissions, shipping)
  • Your selling price = $15.00
  •  Your gross profit = $6.80
  • Quantity ordered = 1000

The total COGS is $8.72 x 1000 = $8720

Gross profit if you sell 1000 units = $6800

In this scenario, gross profit is less than the total COGS. You lost money.

If the quantity is 5000, see what happens.

  • Total COGS is $8.72 x 5000 = $43,600
  • Gross profit if you sell 5000 units = $34,000

You just spent $43,600 to make $34,000.

The more you sell, the more you lose. Hence the reason why it’s vital that you know your numbers.

How to enter fixed and variables costs to the formula

Think of fixed costs as the expense required to run your business.

Regardless of whether you sell anything, you have to pay for the things like:

  • lease
  • electricity
  • waste disposal
  • insurance
  • maintenance
  • equipment
  • payroll
  • bookkeeping
  • and so on

These are your fixed costs. It mostly stays the same.

But entering the total sum like $1500 or $7000 is not the best practice.

Why?

If you already have 9 products and you are looking at your 10th product, the 10th product is not supposed to cover all your business expenses. The costs should be spread out and shared across your entire portfolio of 10 products.

You do this by entering your operating costs as a percentage.

OPERATING COSTS = Total Operating Expenses ÷ Total Revenue 

If you use cash basis accounting, it could be 20-30%.

If you use accrual basis accounting, it could be 15-25%.

This percentage is then applied to the selling price and used as the fixed+variable cost.

Example of how to calculate variable fixed costs break even product
Example of how to calculate variable fixed costs break even point of product

E.g. If Tennis balls selling price is set to $10 with operating costs at 15%, then $1.50 will be used up for fixed and variable costs.

By using a percentage, you can keep it consistent across all your products, and also easily adjust it based on how your business is doing.

If your business is booming and margins are increasing, you can decrease the percentage a little. Or if expenses are going up, increase the percentage.

Much better than going over your statements and summing up expenses all the time.

When you factor in fixed and variables costs like referral fees, return rates, operating costs, you are calculating your operating income. For most businesses, the operating income is very close to the net income.

(Operating income is NOT net income. You must pay taxes on operating income. Net income is the profit after taxes. Don’t confuse the two and end up with a nasty surprise.)

PRICE - COGS - Fixed & Variable Costs = OPERATING PROFIT

Best Break-Even Point is when: OP PROFIT = TOTAL COGS

Best Profit is when: OP PROFIT > TOTAL COGS

Losses occur when: OP PROFIT < TOTAL COGS

All break-even point formulas of a product

Break-even sales = COGS + Fixed & Variable Costs

Break-even units = Break-even Sales ÷ Selling price

Break-even months = Break-even units ÷ Est units sold per month

Gross Profit per unit = Selling price - COGS per unit

Gross Profit Margin = Gross Profit per unit ÷ Selling Price x 100

Operating Profit per unit = Selling price - COGS per unit - fixed & variable costs per unit

Operating Profit Margin = Operating profit per unit ÷ Selling price x 100

Break-even spreadsheet calculator

break-even calculator spreadsheet with formulas template amazon ebay ecommerce

Click the image to see the published full size and preview of the break-even calculator.

This calculator is split up into 3 sections.

  1. Your COGS
  2. Your Variable & Fixed Costs as a %
  3. Break-even analysis and chart
break even cogs detailed breakdown
break even cogs detailed breakdown

Under the COGS section, add all break downs associated with your COGS. The more detailed the better. It’s a spreadsheet so you can edit the labels.

The sum of the COGS is shown at the bottom.

variable fixed costs break even
variable fixed costs break even

The variable and fixed costs section is the same as explained above. Enter it as a percentage of your total sales.

It will then calculate the dollar amount based on your selling price. Variable costs are always changing based on your selling price as it is based on a percentage.

break even analysis chart
break even analysis and chart for a product

Based on your COGS and costs, the break even analysis shows the margins based on the selling price, and the right chart shows how many units you’ll need to sell to break even, the total sales you need to break even and how many months it will take to break even.

Here’s a video explanation of the break-even spreadsheet.

Break even calculation definitions

Break-Even UnitsNumber of units you must sell to make back your total investment from the profits.
Break-Even SalesTotal amount in sales you must sell to make back your total investment from the profits.
Break-Even MonthsTotal number of months it will take to make back the investment from the profits.
Cost of Goods SoldCost to make the product
Inspection costCost associated with any factory or product inspections
Duties&TariffsAny additional import duties and tariffs that must be accounted for in the cost
Freight costCost of shipment
Extra labor costIf extra labor is required from your warehouse or from a packing service
FBA FeesPer unit FBA fees for pick and pack
Extra shipping to AmazonExtra costs to ship the item to Amazon
Amazon Referral FeeThe % fee for your Amazon product category
Return rateThe return rate for your account. Find it in your business sales report in seller central.
Operating costsinclude if you want to calculate break even after accounting for returns.

Delete if you want to calculate break even based on gross profit only.

includes business and overhead expenses like:- Advertising, payroll, utilities, rent, legal, fees and interest, marketing, taxes, travel, rent/lease, maintenance, licenses, supplies, software, hardware

Total Cost Per UnitThe total sum of the landed cost and extra variable costs
Profit Margin Per UnitProfit in dollars
Profit Margin RatioProfit as a percentage. If operating costs are excluded, it’s closer to a gross profit margin. If operating costs are included, it is Operating Profit.

Other break-even calculators

Standard break-even calculators

  • https://www.nase.org/business-help/calculators/business/breakeven-analysis-calculator
  • https://www.nase.org/business-help/calculators/business/breakeven-analysis-calculator

Break even calculators for online sellers and Amazon sellers

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 4

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 5

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.

Zapier Amazon Seller Central integration review and alternative

Looking to see how you can best make use of a Zapier Amazon Seller Central integration?

This is a review of the Zapier Amazon FBA connector, how we use it, and a better Zapier to Amazon alternative for your data needs.

What does the Zapier Amazon Seller Central integration support?

zapier seller central

Zapier is awesome.

We use it extensively in our FBA Amazon business to automate tasks that involved some sort of tedious manual process.

However, the main thing to understand is that Zapier is a collection of connectors. It makes it dead simple to connect and relay data from one app to another.

That’s why a lot of people use this Zapier to Amazon FBA integration to relay Amazon orders into different apps.

However, Zapier only supports getting Amazon order information. In other words, the Zapier seller central integration can only be triggered by a new order.

Nothing more.

If you only need to get your FBA order IDs and related data, then Zapier works great.

You can do things like:

  • Create new seller central orders to accounting software and create invoices
  • Add new seller central orders to Google Sheets
  • Send new FBA orders via email, Slack or other messaging platforms
  • Send new Amazon orders to a Trello board

However, this all changes in Feb 2020.

Zapier no longer supports detailed order information

Until now, Zapier was providing PII (Personally Identifiable Information) via their Amazon connection which is what you most likely wanted and needed.

Things like customer name, address, shipping zip codes, phone numbers and anything else related to the order that could be identifiable.

  • To create invoices, you need this information.
  • To create a database of sales, you need this information.
  • To track order information, you need this data.

Starting Feb 2020, this data is not available via Zapier’s connection to Amazon.

Unfortunately, it makes Zapier’s seller central integration obsolete as this was the only information available through their connection. Now, the default information coming through Zapier is very limited.

Here’s an example of the before and after.

BEFORE: Zapier seller central results

  • Amazon Order ID – yes
  • purchase date – yes
  • order status – yes
  • shipping address name – yes
  • shipping address – yes
  • shipping city – yes
  • shipping country – yes
  • shipping state – yes
  • shipping postal code – yes
  • shipping phone – yes
  • order currency – yes
  • order total – yes
  • number of items – yes
  • buyer name – yes

AFTER: Zapier’s results with new seller central connection

  • Amazon Order ID – yes
  • purchase date – yes
  • order status – no
  • shipping address name – no
  • shipping address – no
  • shipping city – yes
  • shipping country – no
  • shipping state – yes
  • shipping postal code – yes
  • shipping phone – no
  • order currency – yes
  • order total – yes
  • number of items – yes
  • buyer name – no

These new changes will wipe out the automated tasks you created with seller central and Zapier.

  • No longer can create invoices for new orders
  • No longer can send order information to quickbooks to track COGS
  • No longer can verify purchases and customer support quickly by checking against names
  • No longer can check whether the buyer is sending it to somebody else
  • No longer can create a new order in shipping software (have to ask the customer for the address through a direct message)

Amazon marketplaces supported by Zapier

Amazon is spreading their website quickly throughout the world and opening up in countries like United Arab Emirates, Singapore, Australia and more.

Zapier does not support any of these regions.

Only the USA marketplace is supported. If you sell in other regions, you will not be able to get the data.

Zapier Amazon FBA alternative for Google Sheets

gorilla roi amazon google sheets integration
gorilla roi amazon google sheets integration

A lot of our FBA business work lives inside spreadsheets as we create reports, calculate projections, organize orders, share with our employees and email non-sensitive data with our suppliers.

If your main goal of using Zapier was to send it to Google sheets, and you need more than just FBA order data, Gorilla ROI is the best Zapier Seller Central alternative there is.

Gorilla ROI marketplaces supported

Zapier Amazon Seller Central integration review and alternative 6

We support all the marketplaces.

✅ USA
✅ Canada
✅ Mexico
✅ Brazil
✅ United Kingdom
✅ Germany
✅ France
✅ Spain
✅ Italy
✅ Japan
✅ Australia
✅ Indian
✅ Singapore
✅ United Arab Emirates

We are quick to support new marketplaces that are launched by Amazon.

Alternative features to Zapier Seller Central

Zapier Amazon Seller Central integration review and alternative 7

Unlike Zapier’s visual method of connecting to seller central, we take a basic and raw approach by giving you full control over your seller central data.

Here’s a Gorilla ROI review that goes deep into the details and clearly shows how it works.

Our core competency is to let you pull in your seller data directly into Google Sheets.

  • No code required
  • No logging in to websites
  • Use a spreadsheet formula to pull what you want

Everything is based on using regular spreadsheet formulas and functions.

If you know how to use a spreadsheet formula like =AVERAGE(A1:A100), then you are qualified to use Gorilla ROI as your connect to seller central.

If you are familiar with vlookup(), match(), index(), then you will master Gorilla ROI in no time.

We have many functions that you can use for specific or general needs.

Functions we support

Our full list of Google Sheets Seller Central functions continues to grow.

The top 3 most used by our FBA sellers are the following.

1. Pull ASIN or SKU in bulk

=GORILLA_ASINLIST() to bulk list your ASIN
=GORILLA_ASINLIST() to bulk list your ASIN

Type =GORILLA_ASINLIST() and you can load all your ASINs directly into Google sheets.

Same with =GORILLA_SKULIST().

=GORILLA_SKULIST to bulk get Amazon SKU's
=GORILLA_SKULIST to bulk get Amazon SKU’s

You can also convert a SKU to an ASIN or FNSKU and vice versa.

Zapier Amazon Seller Central integration review and alternative 8

2. Sales count by SKU and time period

A huge headache is trying to get your sales data by SKU and over different time periods.

To do this manually, you have to generate a report for every time period you need to analyze.

With our Seller Central integration, you can do it on the fly.

Use the formula =GORILLA_SALESCOUNT()

get sales count between any period

We support the following time periods.

  • Today
  • Yesterday
  • Same day last week
  • Same day last month
  • Same day last year
  • Last 7 Days
  • Last 14 Days
  • Last 30 Days
  • This Week
  • This Month
  • This Quarter
  • This Year
  • This Year-to-Last-Month
  • Last Week
  • Last Month
  • Last Quarter
  • Last Year
  • Last 60 Days
  • Last 12 Months
  • Last 7 Days Week Ago
  • Last 7 Days Year Ago
  • Last 30 Days Month Ago
  • Last 30 Days Year Ago
  • Last Week Year Ago
  • Last Month Year Ago
  • YYYY-MM (change format to text)
  • YYYYQ1… YYYYQ4
  • custom

You can cut, slice and dice your data any way you want it to get a true picture of your business for any SKU, ASIN and time period.

3. Inventory data

The benefit of having inventory data at your fingertips is to make sure you don’t go out of stock.

Get your inventory data using =GORILLA_INVENTORY().

seller central integration for inventory data
seller central integration for inventory data

Amazon’s figures are usually 1 day delayed.

Other Amazon seller software delay the data by 1 or even 2 days.

By loading your data directly inside Google sheets, you can feed your spreadsheets with accurate data and know that your decisions will be timely.

Putting it all together

Why does all this matter when you have your current Zapier and seller central integration?

The following are screenshots of the reporting spreadsheets and models that we use in our FBA business to stay on top.

Rather than spend thousands to hire a developer to create internal tools, we are very agile and can create “spreadsheet apps” that are operational quickly and add value immediately.

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

We can calculate and project inventory based on past 2 years of sales and inventory.

This spreadsheet alerts us of inventory that we need to replenish.

inventory sheet screen

We can track our sales performance over different periods to understand where the business is. If the sales are lower than in the previous period, we can identify it and see what the cause is.

FBA sales performance dashboard
FBA sales performance dashboard

If you are looking for a Zapier Amazon seller central alternative, try out Gorilla ROI.

Free Amazon FBA Spreadsheet Template and Sales Analysis Tools for Google Sheets and Excel

What you’ll learn

  • Free Amazon FBA spreadsheets to download and use
  • How to organize and use these Amazon seller spreadsheet templates
  • The difference between each of the free Amazon FBA spreadsheets
amazon sales analysis tools
Download free Amazon Sales Analysis Tools

This is a growing list of free FBA spreadsheets. As more are created, I will continue to update and add to this page.

Make sure to bookmark and share for other sellers to benefit from the free FBA spreadsheets.

Use the table of contents above to quickly jump to the spreadsheet you are looking for.

Free Amazon FBA spreadsheet list and growing

So far, the list of template spreadsheets include:

  • Detail child item sales analysis
  • Amazon PPC Customer Search keyword analysis dashboard
  • Amazon PPC Targeting Keyword analysis dashboard
  • Seller sales performance stats
  • Product listings information template
  • Sale & traffic analysis dashboard

If you have a request for a spreadsheet, leave a comment and I’ll see if I can make it and then add it to this list.

Or if you have a spreadsheet of high quality that you want to add to this list, comment below also.

How to copy the free spreadsheets to your account

These spreadsheets are based on Google spreadsheets. Not excel templates, but you can save and download these templates as XLSX and use it within Microsoft Excel.

Some of the advanced spreadsheets has formulas that Excel does not support straight away. If you don’t have strong skills with spreadsheets, it is best to stick with Google Sheets.

To copy any spreadsheet to your account:

  • Click the link to the Google sheet that you want
  • Click on File > Make a copy
  • (do not select “Request edit access”)
how to copy spreadsheets to your account
How to copy over the free Amazon FBA spreadsheets to your account

Now onto the spreadsheets.


[NEW] Amazon Profit and Loss Spreadsheet

amazon profit loss spreadsheet template
amazon profit loss spreadsheet template
PREVIEW LINK: Preview link here
DOWNLOAD LINK: Spreadsheet link here

What is it?

Amazon has over 150 different Amazon seller fees and transaction types in their payment reports.

How do you know which one is which and properly account for them when you run your profit loss statements and taxes?

To organize your payment and transaction reports, it takes over an hour to consolidate and calculate.

Not with our template. Install Gorilla ROI (free addon available) and then use this template to automatically populate by SKU and many time period options.

Want to know your best seller profit and loss over the time period:

  • last year
  • this year
  • last week
  • last 30 days
  • last quarter
  • 2020Q1
  • 2020Q2
  • 2019Q3
  • and more

Try doing this manually and you’ll spend forever trying to get the breakdown.

How it works

This spreadsheet is impossible to update manually. You probably tried to create your own profit/loss statement and gave up. That’s why you are here.

If you use Gorilla ROI addon to pull your data into the spreadsheet, follow the instructions on how to install and add any 3 of your SKUs.

Allow the addon to start collecting your Amazon seller data for a few hours, and then refresh the profit loss sheet and it will auto populate into the nice and easy to read P&L format you see in the screenshot.

Detailed instructions are here.


Analysis Spreadsheet for Detail Page Sales and Traffic by Child Item

free amazon spreadsheet child sales stats traffic
free Amazon spreadsheet child sales stats traffic
PREVIEW LINK: Preview link here
DOWNLOAD LINK: Spreadsheet link here

What is it?

Amazon is continuing to make it harder to find and understand data.

With this Amazon spreadsheet, it will automatically sort, reorganize and provide a dashboard to help you understand your data.

It takes your report and displays:

  • Best selling product and associated stats
  • Best converting product and associated stats
  • Top 7 sellers, how much each generated in sales, the conversion rate
  • Worst selling product
  • Lowest converting product
  • Bottom 7 sellers with at least 1 sale. Products with zero sales are ignored.

How it works

Instructions for this FBA spreadsheet is included in the spreadsheet, but here’s a quick rundown.

  1. Export your csv report from Reports > Business Reports > Detail Page Sales and Traffic by Child Item
  2. Copy the data from the csv file
  3. Paste it into the Free FBA spreadsheet where the tab says “data” or “copydatahere”. Make sure the column positioning matches the example.

That’s it.

Just copy and paste and the spreadsheet will take care of the rest. Let the spreadsheet become your Amazon sales analysis tool along with the rest of the seller spreadsheets here.


Sales and Traffic Report Dashboard

free fba sales traffic analysis
free fba sales traffic analysis
PREVIEW LINK: Preview link here
DOWNLOAD LINK: Spreadsheet link here

What is it?

Next on this list of free sales analysis tools is a Sales & Traffic Report analysis dashboard. It works very similar to the Child Items Detail report above.

This template takes a higher level approach of analyzing and deciphering  your overall business performance based on the Sales and Traffic report.

Once you copy this spreadsheet over to your account, you can copy and paste your data in it to quickly see:

  • best performing month and stats for that month
  • best converting month and stats
  • slowest month and stats
  • lowest converting month and stats

How it works

Instructions for this FBA spreadsheet is included in the spreadsheet, but here’s a quick rundown.

  1. Export your csv report from Reports > Business Reports > Sales and Traffic
  2. Change the settings to display your data in months, and then expand it to a 2 year view. A screenshot is included in the instructions inside the spreadsheet.
  3. Copy the data from the csv file
  4. Paste it into the FBA template where the tab says “data” or “copydatahere”. Make sure the column positioning matches the example.

Advertising report – customer search term analyzer

Advertising report analyzer for customer search term
Advertising report analyzer for customer search term
PREVIEW LINK: Preview link here
DOWNLOAD LINK: Spreadsheet link here

What is it?

This is the first of two advertising report analysis spreadsheets you can download.

This spreadsheet is built for “search term” reports from your advertising reports page.

The two main exports are for “targeting” and “search term”. Search term are the terms that customers type in and find your products. And because the columns and exports are slightly different, I’ve created two separate versions so you don’t get confused.

I’ve found that ROAS is the best indicator to use when filtering and analyzing your search terms.

ACoS breaks down if you don’t have any sales. You could end up spending $1,000 on a particular keyword, but if there are no sales, it will be blank and you’ll be left in the dark.

Note that this is a manual spreadsheet. So you have to copy and paste the data in from your CSV file. Automation will come later via the addon for Gorilla ROI.

With this seller advertising spreadsheet, you can:

  • see key stats related to what customers are looking for
  • see best revenue generating keyword
  • see lowest and highest ACoS keyword
  • see top 10 best and worst keywords
  • see highest spend keyword

This type of analysis should help you figure out where you need to improve, or where your current campaigns are strong so that you can make it even stronger and box out competitors.

How it works

Instructions for this FBA spreadsheet is included in the spreadsheet, but here’s a quick rundown.

  1. Go to Reports > Advertising Reports
  2. Select “Sponsored Products”
  3. Select “Search term”
  4. Select your report period
  5. Create the report and then download the csv
  6. Copy the data from the csv file
  7. Paste it into the FBA template where the tab says “data” or “copydatahere”.
    Make sure the column positioning matches the example.

Advertising report – targeting keyword analyzer

Advertising report spreadsheet analyzer fba
advertising report spreadsheet analyzer fba
PREVIEW LINK: Preview link here
DOWNLOAD LINK: Spreadsheet link here

What is it?

This is the second of the two advertising report analyzer spreadsheets. While the customer search term spreadsheet above looks up the stats for the search terms your customer types in to the search bar, this one looks up the stats related to the keywords you currently have in your campaign.

Return on Ad Spend (RoAS) is the key filtering metric here. Focus on getting a higher RoAS number as it means your return is higher compared to the spend.

The way this spreadsheet has been designed is to make the data easy to understand and without having to click the same filter button so-many-times to do the same thing over and over again.

In order to automate this spreadsheet, it’s built with a lot of complex INDEX() and MATCH() functions. If you don’t know what you are doing, try playing around a little bit and undoing the changes to return it back to normal.

Once you figure out how this spreadsheet and the formulas work, you can create more “widgets” based on criteria you need to look up.

How it works

Instructions for this targeting keyword spreadsheet is included in the spreadsheet, but here’s a quick rundown.

  1. Go to Reports > Advertising Reports
  2. Select “Sponsored Products”
  3. Select Targeting
  4. Select your report period
  5. Create the report and then download the csv
  6. Copy the data from the csv file
  7. Paste it into the FBA template where the tab says “data” or “copydatahere”.
    Make sure the column positioning matches the example.

Product attributes and details listing

Product listing seller spreadsheet
Product listing seller spreadsheet
PREVIEW LINK: Preview link here
DOWNLOAD LINK: Spreadsheet link here
FREE GORILLA ADDON TO PULL DATA: Google Chrome page

What is it?

This is one of the basic spreadsheets I offer for all users of Gorilla ROI.

It’s a spreadsheet to track your listing information, category, URL’s review links and so on.

Think of it as a log to track your listing performance and how it does over time. You can update the numbers of this spreadsheet weekly, monthly or however often you want.

The real benefit of this is to combine it with Gorilla ROI addon to automatically pull all of your data into the spreadsheet.

You can use this seller template, but the real power and time saving benefit comes from the automation. The addon is free to use for any 3 FBA SKUs. It will not work for FBM. Only FBA.

How it works

If you manually update the spreadsheet, it’s very easy to figure out. Enter the info into the tab “LISTINGS” as often as you want.

If you use Gorilla ROI addon to pull your data into the spreadsheet, follow the instructions on how to install and add any 3 of your SKUs.

Allow the addon to start collecting your Amazon seller data for a few hours and then it will start populating the spreadsheet.

Detailed instructions are here.


Amazon detailed sales performance spreadsheet

Amazon sales performance spreadsheet
Amazon sales performance spreadsheet
PREVIEW LINK: Preview link here
DOWNLOAD LINK: Spreadsheet link here
FREE GORILLA ADDON TO PULL DATA: Google Chrome page

What is it?

Don’t judge this spreadsheet by the image above.

Because it’s one of the most sought after and requested spreadsheets. This sales spreadsheet automatically pulls, loads your data and displays it over 25 preset periods.

If you want to get a granular breakdown of your sales performance, look no more.

Just look at the different preset time periods you can pull for your seller sales.

  • Today
  • Yesterday
  • Same day last week
  • Same day last month
  • Same day last year
  • Last 7 Days
  • Last 14 Days
  • Last 30 Days
  • This week
  • Max
  • This month
  • This quarter
  • This year
  • This Year-to-Last-Month
  • Last Week
  • Last Month
  • Last Quarter
  • Last Year
  • Last 60 Days
  • Last 12 Months
  • Last 7 Days Week Ago
  • Last 7 Days Year Ago
  • Last 30 Days Month Ago
  • Last 30 Days Year Ago
  • Last Week Year Ago
  • Last Month Year Ago
  • YYYY-MM (set format as text)
  • YYYYQ1… YYYYQ4

Trying to get this manually to understand and dig into your data will be a nightmare.

The spreadsheet includes the formulas required to get this information from your seller central account data. All you have to do is install the Gorilla ROI addon, activate it and away you go.

With this type of breakdown, it helps you see how your business is doing. Rather than procrastinating or wasting time (which is money) to do it manually or not doing it at all, you can keep track of sales and jump on things that need attention.

If you want to see how deep and granular you can go, check out the preview above. It’s a version that all Gorilla ROI members get along with other special spreadsheets and dashboards to make life easier as an FBA seller.

How it works

This one is not a manual spreadsheet.

There is simply no way you can update this manually. And that’s the point. Data like this should be automated.

Follow the instructions on how to install and add any 3 of your FBA SKUs. FBM is not supported.

Allow the addon to start collecting your Amazon seller data for a few hours and then it will start populating the spreadsheet.

Detailed instructions are here.

Why Amazon sellers should be using spreadsheets

Many software tools for FBA are popping up, but I believe that spreadsheets are necessary to give flexibility to the user.

Subscribing to software is great and there are many tools out there, but seeing how you can customize a car, home and pretty much most products, you should have the flexibility to use your own data in a way that makes sense to you.

You and I have different KPI’s and the point of using spreadsheet tools is to create basic templates that you or your team members can use without interrupting your workflow.

As Google continues to enhance its Google Sheets product, the power of Sheets continues to increase. A few years back, G Sheets could barely do the basics, but today, you have a full-blown spreadsheet application online.

Combine with Google Data Studio, Zapier, Slack and other apps and you have the tools and power to create internal apps and software processes for pennies.

How the spreadsheets are designed

I wrote about how I do spreadsheets here. This article will help as you can see and understand the architecture of the spreadsheets allowing you to extend it and create bigger, better versions of it.

Just remember to separate the data and the display tabs.

In all the free templates I’ve shared with you, there is a tab for entering the raw data, one that processes the data and one that displays the data.

Keep it like that and you’ll see how easy and quick it is to manage and use a spreadsheet, rather than the messy monsters you are likely used to.

What other spreadsheets do you want?

I love creating free Amazon FBA spreadsheet around here. Whether it be for Google Sheets or for Excel, spreadsheets are not going away anytime soon.

Rather than just creating spreadsheet templates that are nothing but some titles, I want to create functional tools for FBA that you can use.

If you have ideas or a request, leave a comment below.

I will continue to make spreadsheets and add them to this free spreadsheets resource page.

Make sure you bookmark it or share it with others. The more demand, the more FBA templates I’ll offer.

For dedicated Excel resources and templates, Vertex42 has a large collection of spreadsheets you can download and use.

Frequently asked questions

Does this work for Excel?

  • All the Amazon spreadsheets you find here are created for Google Sheets. You can save them as excel format, but there will be formulas that need to be converted to MS excel format. Functions like QUERY() are not supported in Excel and you’ll have to whip out advanced techniques like power queries to run the same thing. Or a lot of VLOOKUPS, INDEX, MATCH functions.

What other spreadsheets and tools do you offer?

  • We offer a suite of ready to go Business Pro Spreadsheets and documents to improve your FBA operations. Things like forecasting calculators, inventory analysis sheets, manufacturing agreements in Chinese and English and so on. The suite of spreadsheets and docs continue to grow and it is lifetime access for a single payment. We have an Amazon inventory management system spreadsheet for free using Google Sheets.

Do you provide support for these spreadsheets?

  • I’ve created these spreadsheets so that people of any level can use them. I take a software engineering approach to create very easy to use spreadsheets and you will see how easy it is once you open them up. I’ll continue updating these spreadsheets if Amazon changes their reports or formatting.

Do I need the Gorilla ROI addon for these spreadsheets to work?

  • Not for all. The spreadsheet for product listings and detailed sales performance is best with the addon. The addon is free for any 3 of your FBA SKUs and if you can automate the process, what have you got to lose? Takes a minute to install and if you don’t like it, just uninstall it.

Can I share these spreadsheets with others?

  • Yes, you can share it with your team, family, friends and whoever else may benefit from having a free Amazon FBA spreadsheet. Please don’t remove the attribution as I have spent a lot of time making these spreadsheets and would hate to take it down if people are found to be claiming they made the spreadsheets.

Do you make custom spreadsheets for Amazon sellers?

  • No. I do not offer custom services. My team does offer support and custom integration into your existing spreadsheets if you sign up to use Gorilla ROI and purchase the Support and Warranty package found in your account.

2020 Amazon seller fees guide. Examples and free spreadsheet.

In this page you’ll find the ultimate guide to ALL the Amazon seller fees you’ll encounter as a seller.

  • I’ll go through what they mean and how to get the data using Gorilla ROI.
  • At the end, there is also a free spreadsheet going over all these fees that you can download or copy to your Google drive.
  • Visit our free Amazon spreadsheets page to copy over to your Google account. No sign up required.

This guide is not related to FBA estimated fees or an FBA calculator as there are so many on the internet already.

However, if you are a seller and want a detailed breakdown of every fee and charge that Amazon makes you pay for their services to calculate your cost, profit and loss, this is the place to be.

Let’s not waste any time.

Difference between fees and charges

An important and confusing point to first understand is the difference between a fee and a charge.

They are very similar and some items are used interchangeably.

The main difference:

  • Amazon seller FEE is when you pay to use a “product” from within the Amazon platform. E.g. a subscription is an FBA software “product” that sellers pay for. Amazon’s warehouse is another product that we pay to use. Fees are considered part of COGS related to selling your product.
  • Amazon seller CHARGES are expenses for you to request Amazon to perform a service or task on your behalf. It’s not a product, but more of a service. E.g. gift wrapping is a service you pay for to offer to customers. Removal orders are charges and not fees because you are requesting Amazon to perform a service on your behalf. Charges are considered part of operational expenses and not COGS.

Looking for Fee Preview?

If you want to see a preview of your Amazon seller fees for a product, go to Manage Inventory and then click on the “Fee Preview” link and it will show something like this.

amazon fba fee preview inventory
Amazon FBA fee preview inventory

Alternatively, you can check the FBA preview report.

Go to Reports > fulfillment > payments

This will bring you to the reports page. Click on “Fee Preview”.

amazon fba fee preview

List of Amazon seller fees to discuss

The following is a list of fees and charges that you will pay Amazon for selling on their platform in alphabetical order.

Amazon Imaging Fee

Amazon has launched an imaging service where the seller can have Amazon pull a product from inventory to take a professional looking photo.

Prices range from $50 to $150 depending on the type of product.

Amazon Exclusives Fee

Sellers agree to sell their product or products on Amazon exclusively. No other online marketplace except your own website. Physical stores are allowed.

Amazon will give the seller more visibility and perks within the Amazon ecosystem, but if the product is a hit, you are locked into selling via Amazon only.

Also consider that you will have to pay an additional 5% on all your sales. This includes everything in your inventory that is not listed on Amazon Exclusives. Ouch.

Closing Fees

Also known as variable closing fees and is an extra fee that Amazon adds on top of the referral fee. The rate varies depending on whether it is a media product or non-media product.

For media products, it is a fixe rate. For non-media products, the variable fee is based on the shipping weight.

Say you sell a book for $20. The referral fee you pay is 15% which is $3.

The variable closing fee is then another fee on top of that depending on your category.

Cash on Delivery Chargeback

Cash on Delivery is no longer used or accepted. An old transactional item for when people did not, or did not want to use credit cards online.

Commission

Also known as referral fee, which is a fee that Amazon charges to all 3rd-party sellers. This is the core fee you need to pay for the privilege of selling on Amazon’s giant platform.

The fee varies greatly with the lowest being 6% for personal computers and 96% for warranties.

If you sell a $1000 PC, then you pay Amazon $60 as a commission.

If you sell a warranty for $100, then you pay Amazon $96.

The most common fee is 15% for most categories.

The fees can change so check the official Amazon list of commission fees here for accuracy and recency.

Coupon redemption fee

  • Includes Coupon Clip Fee

Amazon charges a fee for when you make sell an item that has a coupon attached to it.

When the buyer clips the coupon and makes the purchase you pay $0.60 as a fee for their “coupon service”.

Creating coupons or vouchers is free. However, when it is redeemed, you pay the fee. You do not pay the fee if the buyer only clips the coupon but does not make a purchase.

To access the coupon page, click on advertising>coupons

fba advertising and coupons

Because of this extra fee, it is important to calculate your margins. This $0.60 fee can make a big difference in how much you are really offering.

You may be thinking you are offering a 5% discount on a $20 product. But after adding $0.60, you are really paying $1.60 for the total coupon.

Your total cost for this coupon is 8%, not 5%.

Cross-Border Fulfillment Fee

If you have activated the option to sell across borders, then Amazon will handle the work related to the export. This could include tax, duties, transportation and other fees.

The buyer pays for the import duties and the extra in shipping, but for other aspects related to the export based on your settings, you could be paying for the extra process.

Check out Amazon’s page on international order fulfillment.

Easy Ship Fee

This is the term used for Amazon India.

In the US and other marketplaces, “FBA seller fees” is used as the term. However, Amazon India calls their FBA service as Easy Ship.

ESPO

  • EPSO Chargeback fee
  • EPSO Cross-Border fee
  • EPSO Payment Authorization fee
  • EPSO Payment Settle fee
  • EPSOChargebackFee

At this time, there is no information available on the definition of ESPO.

This section will be updated when we find out what it means.

FBA Amazon-Partnered Carrier Shipment Fee/Inbound Transportation Charge

  • Includes Fresh Inbound Transportation Fee
  • Global Inbound Transportation Fee
  • FBA Inbound Shipment Carton Level Info Fee
  • FBA Inbound Transportation Program Fee

Also known as inbound shipping.  The inbound transportation fee is the payment for shipping your products to FBA warehouses using Amazon’s partnered shipping.

Most likely it is through buying the UPS shipping labels when you create a shipment to replenish inventory.

It is also for Amazon’s partner LTL shipments. Anytime you purchase shipping through seller central, the amount is added up in this fee item.

You can use your own carrier and pay them directly, but you can’t beat Amazon’s shipping rate.

FBA Fulfillment Fees

  • Includes FBADeliveryServicesFee
  • Includes FBA Weight Based Fee
  • Unit Fulfillment Fee

As a seller, this is your biggest expense. It is the fee to cover Amazon’s services to the customer like shipping, refund/returns and handling sales in their ecosystem.

Check the per-unit fees regularly as it changes yearly.

You can find the amounts paid from your reports section.

Go to Seller central > reports > fulfillment.

You will land on this page with a sidebar for a bunch of information that you can download as csv files.

fba payment fee previews

A shortcut is to go to manage inventory and the fee preview section.

amazon fba fee preview inventory

FBA Inbound

  • Includes Fresh Inbound Transportation Fee
  • Global Inbound Transportation Fee
  • FBA Inbound Shipment Carton Level Info Fee
  • FBA Inbound Transportation Program Fee

Inbound refers to the shipment that you send to Amazon. It could be coming from you directly or from your manufacturer.

Amazon requires you to provide accurate information related to your inbound shipment to be processed efficiently. If you enter wrong information about your carton information such as the number of boxes, sizes, and weights, you will pay a fee for the information and work that Amazon has to do to correct it.

This applies to the transportation program fee.

If the amount you paid to UPS or the shipping company is not correct, you will be charged extra for the difference.

Say you entered the box as having a dimension weight of 100kgs or 100lbs.

However, UPS scanners measured it as 120kgs or 120lbs. You will pay the extra fee for the difference.

FBA Inventory Disposals

Amazon FBA has inventory storage fees so it makes sense that they also have inventory disposal fees if you want Amazon to dispose your inventory.

It’s also called FBA removal order fees.

There are 2 situations to dispose a product.

  1. We sell in UK, and if we get a return, there is no point in trying to have it shipped back to us in the US. Therefore, we have selected to “dispose” automatically all returned items.
  2. You can manually dispose products. If an item does not sell and it’s cheaper to throw away, you can request Amazon to dispose the inventory. If you sell food products and it expires, you can also dispose of it manually.

fba create removal order

Here’s the complete list of the different removal fees. Basically, standard size item fees start at $0.15 per item and oversized items are $0.30 per item.

FBA Inventory Removals

If you don’t want to dispose the item, you can request to have it sent back to you. Amazon will then recall all the items from their fulfillment centers and ship the items back to you.

FBA Removal fees include disposal (check the link above) and returns.

Removals are more expensive than disposals as Amazon has to pick each item, box it and send it to you.

dispose inventory amazon seller

FBA Inventory Placement Service Fees

When you create a shipment plan to send inventory to Amazon, they will sometimes make you split up your inventory and send it to different fulfillment centers.

This is usually the case when you ship small quantities.

Inventory Placement Service is when you select the option to send your shipment to one destination fulfillment center.

Amazon will then receive it at the FC, split up the quantities and send it to whatever warehouses that need them.

e.g. You are sending 20 cups and the shipment plan says you have to pay for 3 different shipping labels.

One to CA. One to TX. One to NY.

This can be costly.

Or you if you activate the Inventory Placement Fee, you can send everything to a single designated warehouse.

The fees are based on weight. Check the Amazon page for the most up to date and accurate pricing.

FBA Inventory Storage Fee

Inventory storage fees can eat quickly into your profit if you have slow selling inventory or too much inventory at their warehouses. Amazon does not want you to use their warehouse as stock rooms. Their fulfillment centers must keep turning over inventory.

According to the seller central help page, FBA inventory fee is:

…the daily average volume (measured in cubic feet) for the space your inventory occupies in fulfillment centers.

There are monthly inventory storage fees, monthly inventory storage fees for dangerous goods, and long-term inventory storage fees.

Fees vary by size and peak/off-peak. Check the type of product you have and the volume of the product. Storage fees are charged every 7th and 15th.

Amazon will double the storage fees after one year if your product is still not selling.

You can track your inventory performance by looking at your Inventory Performance Index.

amazon inventory performance index ipi

Inventory removal is a good way to mitigate (or outright avoid) long term storage fees if you think that your products will not sell after 6 months. It simply means taking your inventory out of the Amazon warehouse and shipping it to your own storage facility or your house.

Do this by creating a removal order. Click manage inventory then click the checkbox of the items you wish to be returned. Click the dropdown menu and choose “create removal order”. From there, type in the relevant data, follow the instructions and submit.

fba create removal order

FBA Label Service Fee

If you request Amazon to label your products with the ASIN/FNSKU barcodes, Amazon will charge a per labeling fee. The fee per label is $0.20 but check the page for updates in case this fee is increased.

FBA Liquidation Fee

This service is no longer available.

Amazon has gotten rid of the option to allow sellers to liquidate their old (6+months) inventory via 3rd party liquidators.

The only options left are to dispose it or have it sent back to your address.

FBA Long-Term Storage Fees

When your inventory sits in Amazon’s warehouse for 12+ months, it incurs long term storage fees.

Amazon does not want you to use their warehouses like a storage unit. Their fulfillment centers are designed to keep inventory flowing. Anything that is stale inventory is considered wasted space and revenue.

The long-term storage fees change based on the off peak vs peak season.

On the 15th of each month, Fulfillment by Amazon (FBA) conducts an inventory cleanup. On these dates, inventory that has been in US fulfillment centers for more than 365 days incurs a long-term storage fee (LTSF) of $6.90 per cubic foot or a $0.15 per-unit long-term storage fee, whichever is greater.

FBA Multi-Channel Fulfillment Weight Handling

Multi-Channel Fulfillment (MCF) is when you sell your product on a different marketplace like your website, and then you send the order information for Amazon to fulfill.

Depending on the weight of the order, the fees will differ.

For full fee schedules, check out the Multi-Channel Fulfillment costs.

FBA Per Order Fulfillment Fee

This is the old name for Amazon’s FBA Fulfillment Fees.

Previously, Amazon used to separate the fulfillment fees to pick/pack, shipping, weight and so on.

Now it is all rolled into one fee item “FBA Fulfillment Fees”.

FBA Prep Service Fees

  • FBA Prep Service Fees (Bubble Wrap)
  • FBA Prep Service Fees (Labeling)
  • FBA Prep Service Fees-Adult-Bagging (black or opaque)
  • FBA Unplanned Prep Service Fees
  • Includes Prep Fee refund (PREPFEE_REFUND)

Sellers may need Amazon to handle preparation of the products. It could include bubble wrapping fragile objects, fixing barcode labels or adding extra labels like warning stickers, and repackaging adult products so that it is not visible.

FBA Returns Processing

  • FBA Returns Processing Fee-Order Handling
  • FBA Returns Processing Fee-Pick & Pack
  • FBA Returns Processing Fee-Weight Handling

Additional fees when products are returned by the seller.

FBA Taping Fee

This is part of the FBA Prep section.

Tape

Products with this requirement need additional taping beyond what might normally be used to seal a box or poly bag or secure bubble wrap around an item.

Extra taping may be required if the item:

  • Might fall out of its packaging if the container is not taped shut
  • Might leak if the lid is not taped in place
  • Needs tape to be fully sealed within the container

FBA Transportation Fee

An older name for Inbound Transportation Charge.

This is no longer used but is the amount you pay to ship your items to Amazon via their shipping label service.

FBA Weight Based Fee

This fee is no longer used as it is rolled up into “FBA Fulfillment Fees”.

This was how it was done previously. There are size tiers for different products. Once you’ve determined which size tier your product falls in then you have to pay a flat rate for that product based on shipping weight.

As with the inventory sizes, this is also measured in standard and oversize but the two classifications are also broken down to a few more tiers. You can see the complete chart here, but here’s a quick summary.

Standard products – small standard, large standard

Oversize products – small, medium, large, special oversize

FBA Localization Fee

No information is available from Amazon on the description of this fee.

Our guess is that this is fee is added when Amazon is hired to change the listing language to the local language.

If you live in USA and want to sell in Japan, Amazon handles the listings and localization of the listing. This looks to be the fee related having it translated and adapted to the Japan market.

FBA Overage Fee

Your inventory is now measured based on your Inventory Performance Index (IPI). If you have inventory that exceeds the allowed limit based on your IPI, you will pay overage fees.

You could be in the green in terms of storage limits, but if your sell-through drops and you don’t keep track of your performance, your IPI will drop and that will further accelerate your overage fees.

Get Paid Faster Fee

This one is a processing fee for sellers that wish to “get paid faster” from invoiced orders by business buyers. This post sums it up.

We are also launching the ability for you to “Get Paid Faster” for invoiced orders. With this feature, your payment for Pay by Invoice orders will be credited to the available balance of your Selling on Amazon account immediately after shipment for a processing charge of 1.5% of the invoiced order amount. To take advantage of this option, change your invoiced order payment settings to get paid faster for all your future invoiced orders.

With this feature, your payment for Pay by Invoice orders will be credited to the available balance of your Selling on Amazon account immediately after shipment for a processing charge of 1.5% of the invoiced order amount.

This is not worth doing unless you are desperate for cash and can’t wait 30 days to collect the payment from the business buyer.

Gift Wrap Commission

Amazon FBA gift wrap charge
Amazon FBA gift wrap charge

This is related to when you collect the gift wrap fee from the customer if you have it enabled it on your account.

If you are FBA, Amazon will collect this charge and present it as your revenue. This is your revenue but since Amazon is the party that does the gift wrapping service, they will get a chargeback from you as described in the next section.

Gift Wrap Charge-Back Fee

A shopper selects the gift wrap option at checkout. The shopper pays you for the gift wrap, but since Amazon is the one that gift wraps it, they then do a charge-back to you and this balances it out.

Let’s say Peter buys a $50 glove for Christmas and chooses gift wrap for an extra $5. He pays $55 at checkout and $55 goes into your sales balance.

Amazon will then do the gift wrap and then charge you $5 for the gift wrap.

This way, you end up with $50 for your product.

Gift wrap is optional and not mandatory for FBA sellers.

High-volume Listing Fee

See the pricing information here.

The High-Volume Listing Fee is charged on non-media ASINs for which:

  • you have an active offer,
  • the ASIN was created more than 12 months ago,
  • and the ASIN has not had a sale in the past 12 months.

As an allowance, your first 100,000 ASINs that meet these criteria will not be charged the High-Volume Listing Fee.

Jump Start Your Webstore Fee

This is a rare transactional item related to signing up for a service to have Amazon or a 3rd party agency to build your webstore on Amazon.

Amazon has changed the way webstores work, so this transaction is a rare case.

A few years back, sellers could design and upload their own look and format for a storefront. But now it is done through seller central quite easily using the drag and drop interface.

Monthly subscription fee

Amazon FBA accepts two kinds of sellers – individual sellers and professional sellers.

There are hobbyists that sell <= 40 units a month and they can take advantage of the “individual seller” tier which does not have a monthly subscription fee.

For the individual seller accounts, a $1.00 per unit fee is added to every sale on top of all the other FBA fulfillment fees you pay.

Sellers that sell their own products or private label products should use the professional tier.  You pay $40/mo to get full access to Amazon’s platform and the extra $1 per unit cost is removed.

Paid Services Fee

No information is available for this fee.

Per-item fees for Individual Sellers

As mentioned above in the individual seller plan, there’s a $1.00 per unit fee added to each item sold to offset the $40/mo fee you don’t have to pay.

See what they did there?

You are limited to selling 40 units. For each unit sold you pay an extra $1, which equals up to $40/mo.

Individual sellers will only pay Amazon if their items are sold. It’s cheaper for FBA sellers that want to try Amazon or a hobbyist that sells items a few times a month.

But with so much traffic on Amazon, it’s worth paying $40/mo and getting full access to all of their tools for growth.

Purchase of Rented Books

  • Includes Rental Extensions

Customers can rent books off Amazon and then purchase it. The fee associated with this is on the fee schedule page.

For each textbook rental that is sold, sellers pay a rental book service fee of USD $5.00.

Sales Tax Collection Fee

Now that Amazon automatically collects sales tax in many of the states, this is becoming less of an issue. But check your transactions to see if there is anything called “SalesTaxCollectionFee”.

Amazon charges 2.9% per order to collect sales tax on your behalf for states where marketplace facilitator is not set up and you have defined it as a nexus in your tax settings.

SSO Fulfillment Fee

There is not enough information about this fee.

Refund Administration Fee

Amazon’s page on refund administration fee.

For media items:

When you issue a full refund for a Books, Music, Video, and DVD (BMVD) product, Amazon will refund all of the original order-related fees.

When you issue a partial refund for a BMVD product, Amazon retains the variable closing fee and credits you a proportional amount of the referral fee originally charged for the order, based on the percentage of the item price you refunded to the buyer.

For non-media items:

When you issue a full refund for a non-BMVD product, Amazon retains 20% of the original order-related-fees, up to a maximum of $5, for each line item in the refund. This amount is retained as a refund administration fee. We will credit you the amount of the order-related fees originally charged for the item you refunded, minus the refund administration fee.

When you issue a partial refund for a non-BMVD product, Amazon retains 20% of the original order-related fees, up to a maximum of $5, for each line item in the refund. Amazon credits you a proportional amount of the referral fee originally charged for the order, based on the percentage of the item price you refunded to the buyer.

If you sell non-media items you lose 20% of the FBA fees for the work they did in shipping it to the customer and processing the returns.

This 20% is called the refund administration fee.

Review Enrollment Fee

Positive reviews are important for the product you’re selling on Amazon.

Having a new product reviewed is a significant leg up for sellers. That’s why Amazon opened up the Amazon Early Reviewer Program.

Amazon Early Reviewer Program
Amazon Early Reviewer Program

From seller central go to Advertising > Early reviewer program.

On this page you can submit individual SKUs or use the csv uploader if you want to enroll a list of SKUs.

First thing is to determine if you’re SKU/s are eligible for the early review program.

Here’s how to check.

  1. Product price should be more than $9.00
  2. Only choose the parent SKU since child SKUs will be included in the review program automatically.
  3. Your SKU should have less than 5 reviews on Amazon at the time of enrollment

FBA early reviewer enrollment dashboard

RunLightningDealFee Principal

A Lightning Deal is a promotion offered in a limited quantity for a short period of time.

You can also call it a flash sale, but it is a great way for FBA sellers to have their products posted on the Amazon Todays’s Deals Page.

2020 Amazon seller fees guide. Examples and free spreadsheet. 9

If you run a lightning deal, you pay $150 for the promotion of your product being featured for a 6 hour period.

Cancellation before the sale period will not require sellers to pay, but sellers will pay in full if they cancel while the sale is still in progress.

A typical fee is at $150 but fees are also affected by the country you’re running the deal on and the week your deal will run on. So if you run the deal in a high traffic country like the USA and you run it during the holidays then you’ll have to pay a higher price.

The week of Black friday and cyber monday costs $300 to host a lightning deal because of such high demand. There is no guarantee you will have a 6 hour window.

Here’s how to locate the deal fees. In your Amazon seller central dashboard, click on reports, payments, then transaction view.

locate deal fees fba

In this page, you will find “service fees” in the dropdown.

Now if you want to run a lightning deal all you have to do within seller central is click advertising>deals

This will bring you to the deals page where you can create a new deal or check on whatever current and past deals you’ve created.

RunLightningDealFee Tax

This is the taxable amount of the lightning deal fee. If the standard Amazon seller fee is $150 and the tax is 10%, then $15 is on the transaction as going towards taxes.

Since your amount of $150 is final, Amazon receives $135 and Amazon will pay $15 in tax that it collected from your payment.

Shipping Charge-Back Fee

This is the same concept as the Gift Wrap Charge-Back fee.

People who are not prime members will pay for shipping. At checkout, if they pay $5 in shipping, this $5 goes into your account.

But since Amazon is the one shipping the product, they will take it out of your account with a charge-back to balance out the transaction.

Shipping HB Fee

Also known as Shipping Holdback Fee.

There’s no shipping holdback fee if you do FBA and Amazon is the one shipping your items. But to keep prices down for customers and prevent sellers taking advantage of Amazon, Amazon implemented the shipping holdback fee.

In the past third-party sellers would list the item for $1 and then charge $29 in shipping in order to avoid paying the referral fee. That’s why this was implemented. A small fee amount is added based on the item if you do 100% discounts because it’s the same concept.

Amazon wants to collect fees for their services, regardless of whether you make money or not. You are using their platform and service and they want to be paid for what they do.

Tax Calculation Services Fees

Taxes should be factored in in any business, but if you don’t want to bother with calculations, Amazon will do it for you. Seller central help defines this as:

If you use the tax calculation services, you will pay us (Amazon) 2.9% of all sales and use taxes and other transaction-based charges we calculate. We will retain these fees in the event of any refund on related transactions.

Amazon “charges” that the sellers pay

All the items until now were related to Amazon Seller Fees you pay. Fees and charges are different. Fees are for services that Amazon provides and you want to use.

Amazon Seller Charges are related to the cost of doing business where Amazon will perform an operational task on your behalf and charge you for the work they did.

Amount given to the buyer to compensate for shipping the item back to Amazon

If the buyer is not a Prime member and they required to ship the product back in order to get a refund, Amazon will reimburse the buyer and it will come out of your account.

Charge that Amazon charges the buyer when returning a product

  • Includes Compensation for return shipping when a buyer receives the wrong item and requests a free replacement

This is a ledger item with the amount given to the buyer to compensate for shipping the item back.

Let’s say a buyer wants to a return a TV because the wrong item was sent.

Amazon will add the shipping balance to the buyer. The buyer will then use the balance to purchase the return label.

Ultimately, it is a wash for the buyer as they are using the money that Amazon gave them.

Cash on Delivery

  • COD charge for an order
  • COD charge for an order item
  • COD refund
  • COD revenue

Cash on delivery is rarely used or accepted.

Compensated Clawback

  • Listed as COMPENSATED_CLAWBACK
  • A.K.A “reversal”

A “clawback” is a reversal. Also called a reversal reimbursement which is applied to reimbursed payments that Amazon pay sellers, but then Amazon takes it back.

Take this example.

We are third party sellers and I monitor damaged or lost inventory which Amazon will reimburse. But sometimes Amazon finds missing inventory and will reverse the reimbursement.

Customer Return Wrong Item Reimbursement

  • Listed as CRETURN_WRONG_ITEM

Buyers are able to return items and then Amazon stores it in their warehouse to restock or resell. This one is a reimbursement given by Amazon if the buyer returns the wrong product and the buyer has been refunded.

For example, if a buyer returns a 2 pack of underwear, but you sell it as a 12 pack set, this will be similar to reimbursement when the buyer doesn’t return the item past 45 days.

You will then receive a reimbursement of the difference.

Custer Service Error Items Reimbursement

  • Listed as CS_ERROR_ITEMS

Sometimes, you will see a $50 refund that Amazon turned into a $150 refund to the customer. This happens a few times and this is an intentional ploy by Amazon customer service to show goodwill to the customer.

Amazon will reimburse you for the difference. Amazon customer service has the power to use your funds to give to the customer as “goodwill” and anything above that is part of this customer service error item.

It’s important to track your finance line items closely to request reimbursement.

DebtPayment

FBA debt happens more frequently than you think.

For example.

When a FBA seller fails to sell anything and they can’t pay the storage fees, the seller has a balance due. If the card on file is not working, it becomes debt and Amazon will seek to get payment on this debt.

Amazon will take this from your account, rather than you having to mail a check or pay via credit card.

Export duty that is charged when Amazon ships an item to an international destination

A general rule is that, unless you’re MCF, sellers don’t pay extra FBA seller fees if they use FBA export to ship internationally.

Buyers pay for international shipping and costs related to export.

There are a couple of things you need to know about FBA export.

  1. You can exclude countries to sell/export to in your Amazon seller account settings
  2. Buyers can return products if it is eligible under Amazon’s existing FBA return policy

FBA Liquidation

This option is no longer offered by Amazon. Previously sellers had the option of liquidating old and unsold inventory at liquidation prices. 5-10c on the dollar.

This liquidation service is discontinued.

Fee collected for certain payment methods

No longer used by Amazon.

Free Replacement of Refunded Items Reimbursement

  • Listed as FREE_REPLACEMENT_REFUND_ITEMS

Ever return an item and Amazon shows you the option to receive a refund or a replacement?

Amazon will replace the item for your buyer and the buyer has to return the original purchase. In other words, it’s a switch.

If the buyer does not return the original product, Amazon will reimburse you for the product that was not received.

Here’s what Amazon support has to say about it.

Hello from Fulfillment by Amazon,
Getting a replacement for an FBA item just got easier for customers. As of September 5, 2018, a customer returning your item can ask to have a replacement sent to them free of charge – and there will be no cost to you.

For you, this means less time focusing on payments and refunds because the sale of the original item is unaffected. For customers, this means more convenient FBA returns, consistent with Amazon returns as a whole.

How it works
To get a replacement item, a customer starts a return through the Online Returns Center or Customer Service.

If a customer requests a replacement, Amazon ships one from your inventory and asks the customer to send the original item to one of our returns centers. If they don’t send it back, or if the original item is returned damaged, Amazon will reimburse you according to the FBA customer returns policy.

Other things to know
Only FBA items in new condition qualify for replacement.

Only Amazon can offer a replacement item, and only if the item is in your inventory.

You won’t be charged fees or receive payment for replacement orders. Your fees and payment on the original order will stay the same.

For more information on your replacement orders, go to your Replacements report.

Thank you for selling on Amazon.
Sincerely,
The Fulfillment by Amazon team

Generic Bad Debt Deduction

This is a rare transactional item. Bad Debt Deduction is when a buyer purchases your products but does not pay.

Since all Amazon shoppers need a credit card on file, this rarely comes up.

For more info on bad debt deduction, refer to the IRS page.

Gift Wrap Charge

This is the opposite of Gift Wrap Charge-Back Fee, where the customer pays you initially for gift wrapping.

Amazon will collect from you the amount received via the charge-back fee to balance the account.

GoodWill (Amount given to a buyer as a gesture of)

For reasons that Amazon will determine, they will offer a sort of payment to the customer as goodwill.

For example, if Amazon gets a call from an angry customer demanding a replacement AND something extra be done, they could give a $10 credit in addition.

This amount is deducted from your account. Amazon will usually provide this goodwill amount from their own account, but also from the seller’s account.

Inbound Carrier Damage

  • Listed as INBOUND_CARRIER_DAMAGE

When your shipments are on an Amazon partnered logistics company and your inbound shipment is damaged during transit, you will get a reimbursement.

It is not a one to one reimbursement. You must provide your BOL and other proof of documentation such as insurance to get the proper amount.

Even then, it could still be a battle.

Incorrect Fees Items

  • Listed as INCORRECT_FEES_ITEMS

FBA is not 100% accurate. Many times you are over/under charged.

In the event where you find that your product is wrongly classified into a higher tier and you have been paying more in fees that you should have, Amazon will reimburse you.

Note that until you manually do the work and find the errors, Amazon does not care and will not take any initiative.

There are two types of these- fees items and non-itemized.

Fees items mean there was an incorrect charge for a specific product.

Non-itemized is for non-specific or a service that’s not related to any product or SKU.

Loan Serving

  • Loan Advance
  • Loan Payment
  • Loan Refund

Not a common transaction. If you load goods to products before payment, it would be categorized into the 3 points above.

Lost Or Damaged Reimbursement

If your products are lost or damaged, you will receive a reimbursement if the product has not been found within 30 days.

Amazon determines how much you get back as a reimbursement. It is never the selling price. Amazon will deduct FBA fees and other charges and reimburse the difference.

Low Value Goods

  • LowValueGoods-Principal
  • LowValueGoods-Shipping
  • LowValueGoodsTax-Other
  • LowValueGoodsTax-Principal
  • LowValueGoodsTax-Shipping

These all fall in the Low Value Goods family of charges and reimbursements.

If If you sell cheap products, the revenue generated is listed under “principal”. “Shipping” is the amount the buyer pays.

For countries like Australia and New Zealand, GST is 15% and will be added on top of the total price, or shipping.

This transaction item will show you the total tax that you have collected. If you do not factor this into your prices, you will end up losing a lot of money as you will be owing money for each low value good that you sell.

Marketplace Facilitator Tax

  • MarketplaceFacilitatorTax-Principal
  • MarketplaceFacilitatorTax-RestockingFee
  • MarketplaceFacilitatorTax-Shipping

Amazon is the marketplace facilitator.

As the marketplace facilitator, Amazon will now be responsible to calculate, collect, remit, and refund state sales tax on sales sold by third party sellers for transactions destined to states where Marketplace Facilitator and/or Marketplace collection legislation is enacted.

These tax items are from Amazon withholding taxes from the sale to pay the due taxes to the states.

Taxes come from the sales amount (principal), whether you charge a restocking fee and if you charge tax on shipping.

Amazon then remits the amounts to the states you are registered in.

Missing From Inbound

  • Listed as MISSING_FROM_INBOUND
  • Includes Missing From Inbound Clawback  (MISSING_FROM_INBOUND_CLAWBACK)

Many times, Amazon will lose your shipment during inbound. Whether it is on the way to a fulfillment center, or during transfer between FC’s.

For units that go missing, you can request to have it investigated and then reimbursed.

You can see the shipments in seller central when you click on Inventory>Manage FBA Shipments. From here you will see the shipping Queue.

how to see items missing from inbound

Once on this page, choose the radio button “closed” and you will filter out shipments that are not closed. Look at the number shipped and received.

You can reconcile discrepancies when you ship more than what Amazon received, and Amazon closes it. Even if Amazon closes the shipment, you can still file a manual reconcile.

tarck fba shipment

Click the track shipment button to bring you to the reconcile page.

how to reconcile missing shipments

You need to upload documentation like proof of purchase before you can submit it for investigation.

Missing from inbound clawback is the opposite of missing from inbound reimbursement.

When inventory is misplaced during inbound by Amazon you’ll get a reimbursement for the lost items.

Amazon will claw or take that fee back if they find the inventory they lost.

Multichannel Order

  • MULTICHANNEL_ORDER_DAMAGED
  • MULTICHANNEL_ORDER_LOST

This is the same as for FBA but it applies to multi channel fulfillment (MCF) where you use Amazon’s logistics system to fulfill orders from other channels like your website.

You will be reimbursed for damaged and lost goods.

Payment Retraction Items

  • Listed as PAYMENT_RETRACTION_ITEMS

There is not enough information on this transaction type.

Our guess is that it is related to a reversal of a payment received by the customer. A refund or A-Z claim is processed and comes out of your account.

Product Ads Payment Principal

  • Product Ads Payment Principal Charge
  • Product Ads Payment Principal Refund
  • Product Ads Payment Tax Charge
  • Product Ads Payment Tax Refund

As a seller, you can pay for PPC advertising with your credit card or from your balance.

Principal Ads Payment Charge is the amount you will pay Amazon from your account if you choose to pay them with your balance.

The refund is for any discrepancies that needs to be reimbursed to you.

The transaction items that has “Tax” is the taxable amount withheld.

Promo Rebate

  • Includes Promotional discount for an order item

Sellers can choose to offer promos and discounts to buyers as a way to entice people to buy.

This can be in the form of coupons where you offer a fixed price discount or a percentage discount.

Let’s say you have a $10 product and offer a $1 discount. You sell 100 units.

The promo rebate of $100 will be charged to your account.

There are other promo rebates such as free shipping or free super saver shipping to entice shoppers to sign up for Prime membership. Amazon will reimburse you for these types of promos.

Removal Order Damaged

  • Listed as REMOVAL_ORDER_DAMAGED

As the name suggests, this is a reimbursement when Amazon damages your items when you request a removal order.

Make sure you have evidence of photos and videos to submit once you receive your items from Amazon. Amazon will never be proactive about it so it is the responsibility of the seller to request reimbursements for damaged items.

Removal Order Lost

  • Listed as REMOVAL_ORDER_LOST

If you request a removal order and Amazon loses the item during the process, you will be reimbursed.

However, this is only possible if you count to verify that the amount you requested in the removal order does not match the received amount.

Amazon does not ship all the units in one box. If you request 1,000 units, it will likely come to you in 50 separate boxes as it comes from each fulfillment center that stored the product.

You must wait until you get the “removal order complete” notification via email. Then count the total received and request reimbursement if there is a discrepancy.

Reversal Reimbursement

  • REVERSAL_REIMBURSEMENT

This is a general catch-all reversal line item if something does not fit into a specific category.

This is a reimbursement item of the reversals.

SAFE-T Claim

  • SAFE-T claim amount for the item
  • SAFE-T Reimbursement Charge (listed as SAFETReimbursementCharge)

File a reimbursement (SAFE-T) claim

The Seller Assurance for e-Commerce Transactions (SAFE-T) process allows you to file a claim for reimbursement if you want to appeal Amazon’s decision to issue a refund to a customer. At Amazon’s sole discretion, you may be issued a reimbursement in cases where Amazon determines that you were not at fault.

Selling Price (Principal)

Amazon uses the term “principal” to mean the selling price of the item.

If you sell $20 hot dogs, the principal is $20.

The common term used is sales price, but Amazon uses principal in their terminology.

Shipping Charge

  • Includes Shipping charges for a COD order
  • Also known as Shipping Income

For buyers who pay shipping, the amount is entered as a shipping charge. Buyers who do not have a prime subscription or existing prime members who pay extra for 1-day shipping pay an extra amount at checkout.

The amount charged for shipping is added to your income as a shipping income.

SI_CRETURN_MERCHANT_REIMBURSEMENT

Not enough information is available for this transaction.

Taxes Collected and Withheld

  • Tax amount deducted for promotional rebates
  • Tax collected by the seller on a COD Item Charge
  • Tax collected by the seller on a COD Order Charge
  • Tax collected by the seller on a COD Shipping Charge
  • Tax collected by the seller on a Gift wrap charge
  • Tax collected by the seller on a Shipping Charge
  • Tax withheld by Amazon on other miscellaneous charges
  • Tax withheld by Amazon on the Gift-wrap charge
  • Tax withheld by Amazon on the Principal
  • Tax withheld by Amazon on the Shipping Charge
  • Tax Collected at Source for Central Goods and Services Tax
  • Tax Collected at Source for Integrated Goods and Services Tax
  • Tax Collected at Source for State Goods and Services Tax
  • Tax Collected at Source for Union Territories Goods and Services Tax

All the above items are part of the tax collected by the seller.

This is not a charge or a fee.

It is simply the amount of tax that was collected on your behalf by the marketplace facilitator or by you when you sell via FBM. Your tax settings are adjusted based on what you enter into your product listings page.

The most common tax rate setting on a product is A_GEN_TAX.

This code will classify all your products at the general tax rate for your nexus.

With the new laws set into place after South Dakota v. Wayfair, business without a physical presence in a state with more than 200 transactions or $100,000 in-state sales collect and remit sales taxes on transactions in the state.

Most of this work is now handled by Amazon as it is not possible for small sellers using the Amazon platform to register a business in all 50 states and then submit quarterly or annual tax reports to the states.

The tax is collected for each of the items mentioned above by the marketplace facilitator and they remit the taxes for you.

This is simply a transaction line item showing you how much tax was collected on the sale or service and how much it will be remitted.

When a tax is withheld, it means that the amount has been withheld and not remitted for some reason.

Warehouse Damage

  • Listed as WAREHOUSE_DAMAGE
  • Includes WAREHOUSE_DAMAGE_EXCEPTION

Amazon will reimburse any warehouse damaged items automatically. You don’t need to issue or file a removal fee in most cases.

Amazon will most likely sell the items at a lower price or dispose of it.

If you find that an item is damaged at the warehouse, you can request an investigation for a reimbursement. When the reimbursement is done manually, it falls into the exception category.

Warehouse Lost

  • Listed as WAREHOUSE_LOST
  • Includes WAREHOUSE_LOST_MANUAL

The new Amazon policy is Amazon will pay less reimbursement for damaged/lost goods in Amazon warehouse. If you have products that have been lost in their warehouse then Amazon will be the sole arbiter of how much they’re willing to reimburse the seller.

They do have a criteria as basis for the reimbursement. What we know is that one of them is average market price. Other than that Amazon will decide.

As mentioned above, Amazon is not above damaging inventory in their own warehouse or during delivery. I’m not giving Amazon a pass here, but this might be one of the most difficult problems to solve in an operation this huge.

Amazon does make mistakes (many considering how many packages fly around their warehouses) and lose inventory often.

If you know that you have missing inventory past 30 days then you can check the Amazon FBA reports page. If you have no idea, it’s still a good idea to visit that page and click on Inventory Health Report.

amazon fulfillment reports

Following this page will help you get to the links to help pages and lead you to the reconcile page where you can file a reimbursement request to Amazon.

Free Spreadsheet of Charges and Fee

Want to see how all of these fees and charges come together to build an Amazon profit and loss spreadsheet?

2020 Amazon seller fees guide. Examples and free spreadsheet. 10
Click the image to load the spreadsheet

Here’s the link to the Amazon Seller Fees spreadsheet.

Click on Menu > Make a Copy.

Save it to your account and view how all the categories are organized.

Load Amazon Best Sellers Rank (BSR) or Sales Rank into Google Sheets

If you are looking to track your Amazon Best Sellers Rank (BSR) or sales rank via Google Sheets, follow this tutorial.

The method described is to track your own products that you sell and hold in your inventory in seller central.

This method does NOT work for tracking competitor products or for research.

Only for products in your seller central account.

If you are looking for Amazon seller software for research and to track competitor Best Sellers Rank, the space is filled with tools for this.

Search for “Amazon Best Sellers Rank Tool” and you’ll be given a lot of options.

Our focus however has always been on internal products and operations. Such data is more reliable and is important for you to maintain and grow your business.

Rather than make estimates of what competitors sales are based on the BSR, we give you the data for your own BSR and sales so that it matches up exactly. No guessing.

This way, you can use your BSR and units sold to estimate how much a competitor is selling.

On a different note, if you need to know how many units to sell to break even on new products, check out our break even analysis formula and spreadsheet calculator.

First install the addon to get your Best Seller Rank

If you want to follow along and start pulling your best sellers rank data for your products inside Google Sheets, read the getting started guide to start the right way and to get familiar with the setup and installation.

The addon is free to use for any 3 SKUs (don’t enter the ASIN).

Once the Sheets addon is installed and you’ve entered your correct credentials then you can move on to preparing your spreadsheet.

Load Amazon Best Sellers Rank (BSR) or Sales Rank into Google Sheets 11

The methods described in this article to import your Amazon BSR assumes you have:

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

Let’s begin.

Amazon’s Best Sellers Rank or BSR explained

If you are not familiar with what Amazon’s sales rank or BSR is, it is the rank of the product for the category.

Amazon best sellers rank
Amazon best sellers rank | click to enlarge

You can see that this Amazon product ranking is 315 in the grocery and gourmet food category.

The grocery and gourmet food category is considered the “top” category because it is at the highest level.

This same product is ranked 5th under candy mints.

Candy mints is the “child” category as it is the detailed category and at the deepest position.

A single product is ranked for each category, but the one people are referring to when they talk about BSR is the rank of the topmost broad category. In this case, it is Grocery and Gourmet Food.

I won’t get into the details of what affects your Amazon ranking because there are many factors involved and it’s a black box. Nobody knows Amazon’s algorithm to rank higher and faster.

But a good chunk of it depends on your:

  • recent sales
  • historical sales
  • conversion rates in comparison to other products in the category

How to download your Amazon best sellers rank

If you’ve installed the Gorilla ROI addon for Google Sheets, you can use our functions to pull your product sales rank.

There are two Gorilla functions that will help get historical sales ranking for any period of time. This is an easy way to create your own Amazon rank tracker spreadsheet.

=GORILLA_RANKING()
=GORILLA_RANKHIST()

Let’s look at how the two methods work, what it is used for, and what data you can download with it.

How to get BSR of your Amazon product

Here’s how to use Gorilla_Ranking() to get the Amazon best seller rank of your product.

Load Amazon Best Sellers Rank (BSR) or Sales Rank into Google Sheets 12

=GORILLA_RANKING(sku, [marketplace], [queryType], [returnStyle])
  • SKU – required
  • Marketplace – optional. Use to filter based on marketplace like US, CA, DE, AE etc.
  • queryType – optional. Control whether to pull the rank based on the top category or child category.
  • returnStyle – optional. Control whether to pull just the BSR number, or category or both.

You can get the Amazon product ranking for the broadest top-level category or deepest child category.

99% of the time, the BSR you are interested in is the top level category. The lower the number, the better and the more popular that product is.

Take a look at the results for the formula below.

=GORILLA_RANKING("ASIN123123","US","child","r_cat")

Amazon ranking category in the US market and the child rank for the category

  1. The test product ASIN entered is “ASIN123123”
  2. I want Gorilla ROI to display the rank in the child category for only the US market
  3. “r_cat” means I want to see the rank + category

The child category for this ASIN is “brushes” and the product is ranked 293 for “brushes”.

If I change the variable from “child” to “top”, it looks like this.

=GORILLA_RANKING("B00123123","US","top","r_cat")

Load Amazon Best Sellers Rank (BSR) or Sales Rank into Google Sheets 13The product’s top level category is health and beauty with a sales rank of 22,634.

Here’s another example where you can use ranges to pull BSR in bulk. Even if you have 1000 SKUs, you can use this method.

=GORILLA_RANKING(A5:A15, "US", "child","r_full")
Pull Bulk BSR into Google Sheets
Pull Bulk BSR into Google Sheets
  1. SKUs are entered into A5:A15.
  2. Pulling rank for US marketplace
  3. Pull the child category
  4. Display using the full category tree

Notice that there is a SKU/ASIN that does not have a rank. It can mean a few things like it’s new, Amazon ranking algorithm has not picked it up yet or has been deleted from your account and is no longer supported.

How to get historical BSR for FBA

Another feature that we have is to pull a daily list of historical BSR (Best Sellers Rank) over any time period for a single or range of SKUs.

This method only works for a single marketplace at a time. So if you are trying to pull BSR for all EU marketplaces, it will not work. You have to define each marketplace at a time.

show bsr category

Here’s the syntax:

=GORILLA_RANKHIST(period, sku, [marketplace], [queryType], [start_date]. [end_date])
  • Period – required. We offer over 50 preset time periods to use like “last month”, “last 7 days”, YYYYQ1, YYYY-MM.
  • SKU – required
  • Marketplace – optional. Use to filter based on marketplace like US, CA, DE, AE etc.
  • queryType – optional. Control whether to pull the rank based on the top category or child category.
  • start_date – optional. Use YYYY-MM-DD format.
  • end_date – optional. Use YYYY-MM-DD format.

Here’s an example and the result.

=GORILLA_RANKHIST("last 7 days", B1, "US","top")
historical bsr table
historical BSR table
  1. Getting the rank from the “last 7 days”
  2. Cell B1 is where I have my SKU
  3. US marketplace
  4. Top category only

The next example has a slight change to the preset time period and downloading the child category.

=GORILLA_RANKHIST("last month", B1, "US","child")

sales rank history fba month

Getting bulk historical FBA best sellers rank is just as easy.

I have 5 SKUs in cell A36 to A40. Using a closed range formula, I can do something like this.

=GORILLA_RANKHIST("last 7 days", A36:A40, "US","top")
bulk historical bsr fba products
bulk historical BSR FBA products | click to enlarge

The result is an automatic table of the sales rank over the last 7 days for each of the SKUs.

With this one line, you can generate a table of BSR values in seconds

All that with convenient auto-generated headings, plus categories and exact dates and ranking numbers.

Using Gorilla ROI, you are given so much flexibility and freedom to get the data you want and use it any way you need.

What if you want competitor BSR numbers?

Gorilla ROI is not a research or competitor tracking tool.

There are so many tools out there that track competitor BSR that it’s not an area we need to fill. Our focus is on internal operations and products so that you can control and stay ahead with your business and not be overwhelmed playing catch up all the time.

By knowing your own Best Seller Rank, you can detect when something is wrong, you can better understand your product sales trends when you match the BSR to your units sold.

How do others calculate units sold per month using Best Sellers Rank?

With the research focused tools like Junglescout, Sellics, SellerApp, etc offer BSR tracking.

You enter the ASINs you want to track and then it will update daily.

By scraping and utilizing all the data when a user subscribes to their services, the research tools are able to collect and combine the data into their system.

By maintaining a big database, they are able to guess how many units an item has sold based on the product BSR.

It’s not accurate, but it’s good for an approximation. Products in very tight niches have inaccurate data.

Guesstimating the BSR works better for very competitive industries where the data can be cross checked against other products and sellers.

This is one of the reasons why we suggest sellers not to use research and scouting tools on their own products.

Keep your data to yourself. Don’t feed it into scouting databases for millions of other people to see. You only make it easier for others.

Best Seller Rank is not organic rank

The Best Seller Rank is a number that compares your unit sales to competitors.

If you sold 100 units of keyrings, and your BSR is 3,000, this means that you are ranking 3000 in terms of number of units sold.

An organic rank in the Amazon results is where your listing shows up based on the keyword entered.

Organic rank is related to SEO, but it certainly plays a part with BSR. If your product is showing up in the top 10 results for “keyrings”, your BSR will be much higher than if your product is showing up on the 3rd or 4th page of results.

Shoppers simply don’t scroll to the next pages as often. They simply enter a different keyword to find what they are looking for.

Organic ranking is a whole different strategy and is based primarily on:

  • sales
  • conversions

This is why so many black hat sellers manipulate and cheat the system to try and sell more. Whether it be through fake reviews or launch giveaways.

Download a free Amazon BSR rank tracking spreadsheet

Now that you see the power and flexibility of using Gorilla ROI ranking and rankhist functions here’s a free Amazon rank tracking template you can download or copy straight to your Google sheets account.

The formulas and functions have been entered for you and after you’ve installed the Gorilla addon and activate it.

Give it 1 day to collect the data as the rank is gathered at the end of each day. Then refresh the sheet the next day and it will load it with your data.

Load Amazon Best Sellers Rank (BSR) or Sales Rank into Google Sheets 14

free best sellers rank tracking spreadsheet
Free best sellers rank tracking spreadsheet | Click to load and copy to your account

These functions are just two of the many functions Gorilla ROI supports. You can check out the functions list page to see what creative spreadsheets you can make with Gorila ROI.

References

Resource

Did you also know that you can find profitable niches to sell on Amazon using ZonGuru’s Niche Finder which utilizes powerful keyword data to unlock your next product niche idea. After typing in a keyword, you will instantly see a curated list of profitable niches along with relevant data such as competition, buyer demand, revenue, reviews, search volume, Niche Score, and much more.

Amazon data and features not supported by Gorilla ROI

We support a lot of data and provide you with tons of flexibility and customizability.

However, we are also very clear about what we do not support and are not suited for.

  • Enterprises are not a good fit. Gorilla ROI is best for small to medium FBA businesses.
  • PPC data is not supported.
  • Accounting and tax remittance is not a service that we offer.
  • Data points that are not included in Amazon’s API are not provided.
  • FBM data not supported (Amazon does not provide this data).

Enterprise FBA Sellers Not Supported

Gorilla ROI is best suited for Amazon business that are considered small or medium sized.

Although we can handle thousands of data points, Google Sheets cannot handle the amount of data that we send to your sheet.

Gorilla ROI is a great data solution for sellers who do less than $50M in revenue.

PPC data not supported

Unfortunately, PPC is not supported as Amazon does not allow their PPC data to be loaded on a cloud spreadsheet software like Google.

This is why we only specialize in providing the best data format related to your product, sales, and inventory.

There is no other service that provides the depth and customizability that we offer.

The general rule is if it’s not in our Amazon Google functions page, we don’t support it.

Our functions page is a living document. The number of functions and examples continue to grow to make it easy for you to quick import data related to your products in convenient ways.

Not an accounting or bookkeeping software

Unfortunately, there is no easy way of doing your taxes or bookkeeping. Manual reconciling is the best solution as a customer may return an item after 2 weeks, Amazon may reimburse lost packages, you mave have to request additional reimbursements.

Because there are so many different situations, automating the process will leave numbers lying in the cracks.

We provide detailed information like fees, charges, but our data does not go back into previous periods after the data has been synced. We only pull data on a going forward basis.

It can pull up-to-date data from the transaction reports, but it’s not meant to replace your accounting software or bookkeeping in any way.

Data points not offered by Amazon

If Amazon offers the data point, then we have it.

However, Amazon does not provide every data you see in seller central. A lot of the information in the detailed business reports are not included.

Things like:

  • pageviews
  • impressions
  • session percentage
  • buy box %

These are just some of the data points that Amazon does not provide to their software providers like Gorilla ROI.

Fulfilled by Merchant

FBM data is not available as this data is controlled by the seller and not Amazon.

Because of the limited data you get for FBM products, we have made the decision to not provide it.

Vendor Central

Gorilla ROI doesn’t support direct connection with vendor central. Only seller central is supported.

FC Transfer Data

FC transfer is not part of transfer. There is no API support nor is it listed in the documentation and there is still no word on FC processing being implemented from Amazon.

Gorilla ROI supports many functions in the Amazon API

Amazon data and features not supported by Gorilla ROI 15

Gorilla ROI does one thing best, pulling updated and accurate date from Amazon, but the integration with Google Sheets makes it versatile. It can accommodate power users and new users at the same time.

You don’t need to be a coder or a spreadsheet expert. All you need is some basic computer knowledge in tandem with your Amazon FBA knowledge. Let Gorilla ROI pull the data for you so you and your team can do the rest.

Looking to get data from Amazon into Google Sheets?

If you are looking to connect Amazon with Google Sheets, then you’ve come to the right place.

We are the only software that allows you to take full control of your data.

Rather than locking down what you can and can’t do, you get the flexibility to use your data how you want it. Get direct and flexible access to your Amazon data without having to know code.

Gorilla ROI is a Google Sheets Add-on that allows you to connect and import data directly from your Amazon Seller Central account into Google Sheets.

Do things like bulk convert your ASIN’s to SKU’s or FNSKU, breakdown the fees and charges per sku, pull historical inventory level for projections, load the number of units sold by month, week, last year, last month and more.

Here’s a demo that I did where you can see it in action and how it works.

Full transcript and timeline is available at https://orangeklik.com/demo-gorilla-roi/

FBA spreadsheets are available for new users.

Free Amazon templates are also available. No sign ups, no emails. Just copy to your account and away you go.