Helping You with Your Amazon FBA Business

Simple Method for Amazon FBA Sellers to Calculate their COGS in Excel

I want to share a simple method that Excel spreadsheet users can use to calculate their costs of good sold (COGS).

Step 1: Copy Amazon Data
Log into your Seller Central account and navigate to Payments -> Transactions View.  Here you’ll get a listing of your recent sales and fees.  For illustration purposes, I’m sharing my last 6 transactions below:

Payments COGS 1 700

Next you’ll highlight the Column Headings (first page only) and every row on your page.

Step 2: Paste the Data into Excel
Open a blank spreadsheet and put your mouse cursor in Cell A1.  Then paste your Amazon data.  Be sure to select destination formatting  so that your spreadsheet doesn’t look too funny.  Here’s how my spreadsheet looks after pasting my data:

Excel COGS 2

If you have more than one page of transactions, then repeat with the rest of the pages.  The differences are that you won’t copy the Column Headings again and that you’ll be pasting your new data into the next available row in your spreadsheet.

Step 3: Format and add a COGS and Net Profit Column
Expand the columns and format the spreadsheet so it’s easier to read.  Here are the changes that I made:

  • Sorted the rows by date
  • Added color to the Column Headings and made the text bold
  • Shortened the Column Heading wording in columns E & F
  • Added totals on the bottom of each column with dollar amounts and made them bold
  • Added a COGS Column

Though not needed to calculate your COGS, you might want to add a Net Profit column as it only takes an extra few seconds.  Then setup a formula to calculate your Net Profit (Total – COGS = Net Profit).

Excel COGS 3 700

Keep in mind that the Net Profit column is not 100% accurate if you collect sales tax.  This is because Amazon puts your sales tax collected amount inside the “Other” column.  But the spreadsheet will still give you a pretty good idea of what your per-item net profit is.  Or you can take it a step further and include a “Sale Tax Collected” column so that you can track and deduct out the sales tax.

As this blog is public, I deleted my Order ID numbers but you’ll keep yours in the spreadsheet.

Step 4: Enter Your Buy Costs
Find how much you paid for each item and enter it in the COGS column.  Hopefully, you’ll have this information handy in another spreadsheet.

Excel COGS 4 750

As you can see, I now have a COGS number of $84.60. 🙂

For those that added the Net Profit Column: If you did the above for one full calendar month and you use the accrual method of accounting, please keep in mind that the Net Profit number is not your real business profit.  You’ll still need to deduct out the sales tax collected and all other expenses such as monthly subscriptions and prepping supplies.

This Method Gets Time Consuming as Your Number of Purchases and Sales Increase
For over a year, I used a slight variation of the above method for calculating my COGS and you can read a previous post about it here.  But over time as my volume increased, it took longer and longer to do my COGS tracking manually.  Besides having a larger number of Amazon sales transactions to go through, there were more months of “purchase” data to search through to find the buy cost.

In other words, someone that’s been selling for 3 months will only have 3 months of receipts or receipt data to go through to find a specific buy cost.  Someone selling for 12 months will have 12 months of inventory data to search through.  At the beginning of my second year, this task became too time-consuming and that’s one reason why I switched over to InventoryLab.

Once tracking your COGS manually becomes a chore, then you may want to consider joining InventoryLab.  You can learn more about them either through this blog post or from their web page (affiliate link).

Here is the spreadsheet that I used.  You may download a copy to use: 

COGS Spreadsheet

I hope this post helps and I value your feedback.  Leave a comment below or find me on my Facebook Page.

Subscribe and Get a FREE FBA Guide

Organize your business with a task list, calendar & time-saving tips!
Plus a Bonus Inventory Tracking Spreadsheet!

I will never give away, trade or sell your email address. You can unsubscribe at any time.

{ 10 comments… add one }
  • Ree Klein January 15, 2016, 9:30 pm

    This looks like a good method and for someone just starting out, it’s free so that makes complete sense! Having to deal with all the receipts drove me nuts, which is part of the reason I turned to private labeling. Had I continued with retail arbitrage, I would definitely have invested in Inventory Labs. It is highly rated by many people I respect.

    Solid record keeping is critical no matter what selling style you like best and I really respect you, Diana, for being so great at it and for sharing your methods!

    • Diana January 16, 2016, 12:36 am

      Ree, I understand about going nuts with the retail arbitrage receipts. Last week in order to calculate my 2015 mileage, I had to read almost 200 receipts. Eek. That was a lot of time and eye strain. Not to mention all of the time spent driving around to 200 places!

      InventoryLab sure has helped with speeding up the inventory and COGS tracking…But what’s weird is that I felt I had a better grasp of my inventory when I tracked it in Excel.

      You’re correct that record keeping is critical and I know you have your own system that works well for you.

      Take care and I’m glad you stopped by!


  • Patsy Pankey January 16, 2016, 1:59 am

    I come to your site often to find different things to help me along in my business. I found a great program you can put on your phone to record your mileage. Then go that site at night and declare whether the trip you took was business or pleasure. I love it, simply because once it is on your phone it logs trips automatically as soon as you start your car. So you don’t forget what you did it’s best to log in at the end of the day and do the declaration part. Voila it is done and your tax man will love the fact that you can tell him exactly what you did and when. It’s called MileIQ. Just type in and you are there. I tried to do the link for you all but it kept going to my account

    • Diana January 16, 2016, 3:55 am

      Thanks Patsy for the mileage app! I’ll give it a try. I looked at mileage apps before, but could never decide on which one to try out. I’m already behind on logging my last two RA trips into Excel. Thanks again!

  • Miguel January 16, 2016, 3:52 am

    Great information, as always! One quick tip for receipts: use your Android/iOS camera to snap a picture and have it automatically converted to searchable PDF, saved in the cloud. Here’s a good article:

    • Diana January 16, 2016, 3:58 am

      Hey Miguel, I’ll take a look at that app! I just signed up for a free month of ShoeBoxed and sent them last years RA receipts. There was no way I would catch up on those. The phone app may be exactly what I need for this year. Thanks!

  • Robert January 16, 2016, 2:34 pm

    Thank you so much for this information. I appreciate you taking the time to share. This information will be put to use right away as I try to better organize my online business. Keep up the great work, love the blog!

    • Diana January 16, 2016, 8:27 pm

      Thanks Robert for the kind words and good luck with your business!!

  • Wellslee Westbrooke August 17, 2016, 2:46 pm

    Excellent form. Thn you for making this available to download. Questions does this include the fees to ship our products to amazon, or do I need to create another column for those fees.

    • Diana August 17, 2016, 3:59 pm

      Hi Wellslee,

      The form doesn’t include inbound shipping and you may add in extra columns.

      I enter my inbound shipping in Quickbooks and that’s where I create my official P & L. But the spreadsheet will work fine too. You’ll just need to add in all of your other expenses.



Leave a Comment

Next Post:

Previous Post:

About Diana

I am working towards creating a full-time income by selling on Amazon. It's a lot of work but very fun! If you're interested in selling on Amazon, be sure to follow my blog as I'll be providing you with lots of tips!


Amazon FBA Supplies

Find the Topic That You’re Looking For!