Amazon profit calculator and FBA profit loss spreadsheet template

written by GORILLA ROI

Here’s the link to the Amazon fee calculator spreadsheet if that is what you are looking for.

This is for a FBA P&L template. This is the spreadsheet I’ll be sharing.

amazon profit calculator
amazon profit calculator – click to enlarge

 

This article covers how to create your own Amazon profit calculator to create your own P&L statement in Google Sheets. With our Amazon to Google sheets integration, you can automatically generate a profit loss table which you can use to analyze your Amazon profit margins over different time periods.

If you don’t have our Gorilla ROI addon installed, do so first and follow the install instructions if you want to automate and speed up the tedious process.

If you are an advanced user, you can go straight to the Profit Loss documentation page which includes example formulas, and a video guide.

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

  1. Official page of all the functions we support
  2. Website documentation for easier navigation
  3. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples
Amazon profit calculator and FBA profit loss spreadsheet template 1

How to pull data to create an Amazon Profit Loss statement

Formulas to pull and create your Amazon profit calculator or spreadsheet

gorilla profit loss amazon function

Before getting into the details of the Amazon profit calculator in the next section, you need to know how to get the data.

The quickest and easiest way to get started once your data is synced and ready is to type into any blank spreadsheet

=GORILLA_PROFITLOSS("this year")

This one formula entered into cell A1 will automatically generate a consolidated table like the following:

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

Rather than going through every transaction in the settlement report and then categorizing and reconciling it yourself manually, it is done automatically. To change a time period, change it from “this year” like the formula above to something else like “last 30 days”.

Use any of these date periods that we support.

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

More examples

=GORILLA_PROFITLOSS("LAST 30 DAYS")

=GORILLA_PROFITLOSS("2021-01")

=GORILLA_PROFITLOSS("2021Q4")

=GORILLA_PROFITLOSS("THIS MONTH") and more

More Profit Loss Examples

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

=GORILLA_PROFITLOSS("THIS YEAR", "US", "SKUNAME721")
Amazon profit calculator and FBA profit loss spreadsheet template 2

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

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

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

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

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

  • Sales
  • Discounts/Promotions
  • Amazon Reimbursements
  • Shipping Income
  • Income-Other
  • Amazon Lending
  • Tax
  • Amazon Advertising
  • Storage Fees
  • Amazon COGS
  • Other Amazon Fees
  • Amazon Commission
  • FBA Fulfillment Fees
  • FBA Inventory Removals

To look up a category, use a detailed formula like:

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

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

amazon profit spreadsheet

=GORILLA_PROFITLOSS("THIS YEAR", "US",  ,"COGS", "YES")

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

Amazon profit calculator and FBA profit loss spreadsheet template 3

Creating your Amazon Profit Calculator

Here’s the spreadsheet again. There are 3 main parts.

amazon profit calculator
profit loss spreadsheet
  1. Dynamic inputs using dropdown menus for you to switch time periods, marketplaces, SKU etc
  2. The main P&L section
  3. An exploded view of what is rolled up into each category

The formulas to create sections 2 and 3 were already provided above.

=GORILLA_PROFITLOSS(B1)

where B1 is the dropdown list cell where you can select the different time periods.

And the detailed view of the category is created using the syntax of the following formula, but changed to use dynamic cell references instead of manually entering the values into the formula.

=GORILLA_PROFITLOSS("THIS YEAR", "US",  ,"TAX", "YES")
The magic comes alive with section 1, the dynamic inputs.It’s the prep work when it comes to building spreadsheets, but is always worth it.In columns A and B, I have manually entered all the FBA marketplaces and the time periods we offer.Then use Data Validation to create a drop-down list of the marketplaces and time periods.

Amazon profit calculator and FBA profit loss spreadsheet template 4

 

This way, when you change the value of a cell with the drop-down list, it will update the formula and pull in the data to match your selections.

Grab a copy of the spreadsheet and connect with Gorilla ROI.

Amazon profit calculator and FBA profit loss spreadsheet template 5

Bonus – Monthly finances spreadsheets

In the spreadsheet, I’ve also included a bonus where you can pull monthly transactional data into Google Sheets.

amazon finances profit spreadsheet

  • The blue text are the fees and charges that I manually copied and pasted
  • The purple text contain the formulas to bulk load the data
  • Formulas are dynamically referenced
  • Dropdowns are added at the top of each column

Download the spreadsheet and play around with it.

Follow the install process at the top of the article or go to this page on getting started.

Data we support

For an extensive list of what we do and do not support.

Videos and documentation.

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

What is Gorilla ROI?

Gorilla ROI automatically pulls Amazon data into Google sheets for you to make sense of the data without the inconvenience of manually downloading, sorting and updating spreadsheets.

Learn how you can centralize your data and use it to increase your ROI.

Leave a Reply

Your email address will not be published. Required fields are marked *