Helping You with Your Amazon FBA Business
Try Merch Informer
Free 3-Day Trial

Tracking Amazon FBA Inventory and COGS in Excel

Hello everyone!  In this post, I’m going to show you my method of tracking inventory and cost of goods sold in Excel.  I use Excel for these two tasks because I’m not ready to spend $50 a month on Inventory Lab.  This method may not work for you if you have a high number of transactions because then I believe it’ll be time-consuming.  But for new sellers or part-time sellers (like me) with a small number of transactions, it shouldn’t take very long to keep the spreadsheets updated.  I buy and sell between 50 and 100 products a month and to be honest, I no longer feel that I spend a lot of time on bookkeeping.  I hope this post will help you if you’re currently struggling with Excel spreadsheets.  I know that I did at first and it took me a few months to come up with my current system.

Why I Have an Inventory File in Excel
I created an Inventory file in Excel.  With that being said, tracking inventory in Excel isn’t necessary for me to track how much I spend on inventory each month.  That is handled in Quickbooks.  For example, if I bought seven inventory items for a total of $24.43 using my credit card, my Quickbooks Credit Card Liability Account will show a charge of $24.43 and my Inventory Asset Account will increase by $24.43.

The problem with QuickBooks Online Simple Start though is that the Inventory Account shows the total purchase amount of $24.43 but doesn’t show that I bought seven items for $3.49 each.  Without knowing the costs of every individual item, I’m unable to calculate my cost of goods sold (COGS).  This spreadsheet is where I store my costs and other details on each individual inventory purchase.

Below is a screenshot of some of my February purchases.  The product descriptions and Amazon ASINs have been modified so that I can keep my products private.

Excel-4-SHD

Here’s How the Inventory Spreadsheet Works
I enter the purchase date, item name, store name, payment method, price and tax directly from the receipt and I have Excel calculate the total price.  Later when I sell the item, I type in the date it was sold.  This provides me an easy visual to see which items have sold and which haven’t.

The great feature about Excel is that you can add more columns so that you can track any data that’s important to you.  I added the “Source” column after I signed up for ScanDroid Pro as I’m interested in seeing how well the products from my lists do.

While I’m listing my new items in Amazon Seller Central, I copy and paste the ASIN number into the spreadsheet.  This makes it easier to match up the product on Amazon’s site later.  My last column is for miscellaneous notes or to track expiration dates.

Excel calculates my total costs for the month and you’ll see that on the bottom row.

I love this spreadsheet because at a glance I can see how much I bought, how much I paid in sales tax, which items have sold, which have expiration dates coming up and which sources (Keepa, Scandroid Pro, myself) give me the best luck.

When I first started this inventory spreadsheet, I also had it calculate my cost of goods sold.  But that became a little too complicated once I got into the second month of selling.  So, I use a different spreadsheet for my COGS calculations.

Freebie: Get a free inventory tracking spreadsheet by going to the sidebar and signing up for blog updates.  If you’re already a subscriber, you should be directed straight to the Excel download page.

Calculating COGS with the Amazon Cut and Paste Spreadsheet
After signing up as an Amazon FBA Seller, I started creating my own spreadsheets to track my inventory and expenses.  However, none of my spreadsheets turned out quite right because I really didn’t know what information I wanted and I really didn’t understand the whole FBA process.  Therefore, I figured that it would be better to buy an existing spreadsheet rather than trying to reinvent the wheel.  I found the Amazon Cut & Paste Spreadsheet (not an affiliate link) and bought it because not only does it track inventory, it also tracks your sales and expenses and calculates your monthly profit.

To this day, I still use the Amazon Cut & Paste Spreadsheet to calculate my COGS and my return on investment (ROI) for each item sold.  I do not use it to track my expenses or for my Profit and Loss Statement.  I also modified it slightly to fit my needs.

Here is what my modified version looks like (Order ID & Product Details changed to keep my products private):
Excel-3-SHD

Here’s How the Amazon Cut and Paste Spreadsheet Works
Every few days I log into Amazon Seller Central and go to my Payments -> Transactions View page.  I then highlight all of my new transactions and copy and paste them directly into Columns A through I in the spreadsheet.  I don’t need to copy and paste one transaction at a time – I can do a whole page at once.  It really just takes seconds to get the Amazon data into the spreadsheet.

The next step is to enter the item cost into Column J.  I get this information from my Inventory spreadsheet.  For example, in the above spreadsheet I sold two sponges on 2/2/15.  If I go back to my Inventory spreadsheet for January, I’ll see that I paid $3.49 for each sponge.  I manually type $3.49 in the “Item Cost” field for each sponge.  Since I track my shipping costs as a monthly total in Quickbooks, I ignore the “Shipping Cost” column.

The spreadsheet then calculates the net profit, ROI and all of the totals on the bottom.  The original spreadsheet calculates a different number in the ROI column that I don’t care to track.  Again, that’s the great feature with spreadsheets.  You can easily modify them to track any data that you’re interested in.  Just make sure you save a copy of the original spreadsheet before you make too many changes.

Though I’m not showing it in this blog post, the spreadsheet has other tabs where you can enter your expenses and it’ll calculate your monthly profit.

Monthly Summary
At this point, I have a monthly COGs total and that is the number that I enter into Quickbooks.  But since I love spreadsheets, I created a Monthly Summary spreadsheet:

Excel 0

I like this spreadsheet because I can easily view and compare my monthly inventory purchases and COGS amounts.  Also, I compare my totals to Quickbooks as a way of double checking myself.  If the numbers don’t match, I made a data entry error somewhere.  I usually don’t worry about one cent differences because it’s usually due to differences in how the two programs round numbers up or down.  Over time, it always seems to even out.

I hope this post helps you if you do your own spreadsheets.  And again, this system may be too time consuming if you have a large volume of inventory purchases and sales transactions each month.  Since I’m still a low volume buyer and seller, I can update my spreadsheets quickly.

As always, please feel free to leave me questions/comments in the comment form below or on my Facebook page.

Best,

Diana

Don’t forget to grab your copy of a free inventory tracking spreadsheet.  Get it by signing up for blog updates below!

 

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.

{ 114 comments… add one }
  • Ka March 6, 2015, 5:55 pm

    Hello Diana,

    Can you please share your spreadsheet template with your readers so we all can benefit.

    Thanks
    KC

    Reply
    • Diana March 6, 2015, 6:51 pm

      Hi KC, I don’t mind sharing it, but never have shared a spreadsheet so I’ll have to figure out the best way to do that. Thank you for the suggestion and I’ll work on it over the next few days. Diana

      Reply
      • Ka March 6, 2015, 11:59 pm

        Thanks

        Reply
      • Nate Brunner March 14, 2015, 1:33 am

        Diana,
        I would love it as well! It’s really easy to share a spreadsheet on google Drive. You can just import it and then share it, making it a public link. Or even private only for those you give the link to.

        Love your blog! Just getting started with FBA and you do a great job.

        Reply
        • Diana March 14, 2015, 2:42 am

          Hi – I started working on it tonight. I can share my Inventory spreadsheet, but I get my Cost of Goods Sold from the Amazon Cut and Paste spreadsheet. Since I didn’t create that one, I can’t really share it. So, I’m going to come up with something so you can calculate COG in the inventory spreadsheet. I’ll let you know as soon as its done. Diana

          Reply
          • Diana March 15, 2015, 3:51 pm

            Goal is to have it done this week. Thanks for your patience.

          • Diana March 18, 2015, 12:03 am

            You can go to my Facebook Page for a link. If you see any mistakes, let me know!

  • Isabel March 18, 2015, 7:14 pm

    Hi Diana,

    Amazon FBA newbie here. You have fantastic posts that have helped me tremendously. Thank you for being so generous with sharing your knowledge.

    I am still in the process of getting my first shipment together to send to Amazon, but had a question about packing supplies. Do you use your Amazon Pro Seller account to purchase any of your packing supplies, or do you use it strictly for selling products?

    Thanks and have lovely day!
    Isabel

    Reply
    • Diana March 18, 2015, 8:15 pm

      Hi Isabel, your Amazon Seller account and your regular Amazon buyers account are two different things – even if you use the same email address. I use my Amazon Buyer’s account to purchase supplies all the time. I do recommend that you purchase any supplies for your Amazon business separately from any personal purchases and to also use a separate credit card. Please let me know if you have any questions!

      Good luck with Amazon FBA!

      Diana

      Reply
      • Isabel March 18, 2015, 10:54 pm

        Thank you soooo much for responding Diana! This was very helpful 🙂
        Also, thank you for the reminder — I will definitely use the credit card that I have set aside for the business to make the packing supplies purchases online.
        Isabel

        Reply
    • Ka March 20, 2015, 2:25 pm

      Hi Diana

      Thanks. Sorry…I’m not on facebook. Can you please email me the excel.

      Thanks again.
      KC

      Reply
      • Diana March 20, 2015, 3:33 pm

        Hi Ka, I emailed it to you. The download link is on the bottom of the page. Diana

        Reply
  • ka March 20, 2015, 5:02 pm

    Thanks so much!

    Reply
    • Diana March 20, 2015, 5:31 pm

      your welcome. Let me know if you have any questions on it! Diana

      Reply
  • Joanne May 24, 2015, 12:11 am

    Please email me the excel spread sheets. I tried to find them on the site after I signed up but can’t.

    TIA

    Joanne

    Reply
    • Diana May 24, 2015, 1:52 am

      Hi Joanne, check your email, it should be there! Diana

      Reply
  • Adam June 2, 2015, 8:04 pm

    Very nice article, would also love the template if you don’t mind.

    Thanks again.

    Reply
    • Diana June 2, 2015, 9:00 pm

      Hi Adam,

      I emailed you a copy of the spreadsheet! Let me know if you have any questions.

      Diana

      Reply
  • Amy July 5, 2015, 7:41 am

    I would love to try out this spreadsheet so I start reporting and tracking! Would you mind emailing or sharing it with me?

    Reply
  • Diana July 20, 2015, 9:36 pm

    Hi Amy, I’ll send you an email!

    Diana

    Reply
  • Nelson August 13, 2015, 4:15 am

    Hi Diana, thanks for sharing your experience, can you share the Excel file to me?

    Reply
    • Diana August 13, 2015, 12:44 pm

      Hi Nelson, I will email you the spreadsheet!

      Diana

      Reply
  • Steve August 15, 2015, 4:57 pm

    Hi Diana, thanks for the info! Can you share the Excel file with me also?

    Reply
    • Diana August 15, 2015, 5:36 pm

      Hi Steve, I sent it to your email.

      Diana

      Reply
  • Holly Woodford August 18, 2015, 4:36 pm

    Hi Diana! I looked for the spreadsheet link with no luck. Would you please email it to me?? TIA!

    Reply
    • Diana August 18, 2015, 6:16 pm

      Hi Holly, I sent the file to your email! Diana

      Reply
  • S Clay September 1, 2015, 12:48 pm

    I would really love a copy too. Can you send it to my email address please?
    I hope so .Thanks so much.

    Reply
    • Diana September 1, 2015, 1:59 pm

      Hello – I sent it to your email! Diana

      Reply
  • Shirley September 2, 2015, 1:42 am

    Dear Diana
    Thank you very much for sharing!

    Shirley

    Reply
  • Lee Anne Knibb September 10, 2015, 7:20 pm

    Hi Diana,
    I also couldn’t find your spreadsheet. Could you plase email it to me? Thank you,
    Lee Anne

    Reply
    • Diana September 11, 2015, 12:01 am

      Hi Lee Anne, I’ll send it to you in a few minutes! Diana

      Reply
  • Dustin September 14, 2015, 1:06 am

    Hi Diana,

    Could you please email me the template? I couldn’t find it. Thanks
    Dustin

    Reply
    • Diana September 15, 2015, 12:55 pm

      Hi Dustin,
      I sent it. Sorry for taking so long, my comments notifications are going into my SPAM folder again.

      Diana

      Reply
  • Ammar Siddiqui September 24, 2015, 5:35 pm

    Can I also get the spreadsheet emailed to me please

    Reply
    • Diana September 24, 2015, 6:48 pm

      On it’s way to your email!

      Reply
  • Noel Zee October 6, 2015, 6:14 pm

    Hi Diana
    Greate post! I would like to get a copy too if possible.
    Thanks

    Reply
    • Diana October 6, 2015, 7:32 pm

      Hi Noel, I’ll get it sent to you!

      Diana

      Reply
  • Paula October 10, 2015, 11:31 pm

    Hi Diana, I just found your site – great info! Please email a copy of the Excel spreadsheet please!
    Thanks.

    Reply
    • Diana October 11, 2015, 1:26 am

      Hi Paula, I’ll send to you!

      Reply
  • Josh October 21, 2015, 4:56 pm

    Hi Diana, yours is the first post I’ve found with useful information on keeping track of costs, etc. for beginners. Thanks for sharing.
    I have just started with FBA (books) and have a question about how you handle cost per book:
    Say you buy books by the lot from craigslist and pay $20 for 40 books; the base cost/book is $.50 each. Let’s say that only 1/2 are sellable, the other half are not profitable — is your final cost/book $1/each or $.5 each? What do you do with the unprofitable books?
    — Thanks, Josh

    Reply
    • Diana October 21, 2015, 6:51 pm

      Hi Josh, I’m not an accountant, but I believe there are two ways to do it.

      If you spend $20, then you need to account for the $20.00. You can either:
      1. Make the cost of the 20 good books $1.00 each.
      2. Make the cost of the 20 good books $0.50 each. Then for the other $10.00, setup another expense such as “bad inventory” or “unfulfillable inventory” or something like that. Then you’ll have that other $10.00 shown as an expense item.

      Then keep or donate the unprofitable books.

      Diana

      Reply
  • Rob November 7, 2015, 9:00 pm

    Great blog. Very informative. Would love a copy of the spreadsheet as well. Thanks so much!

    Reply
    • Diana November 8, 2015, 2:20 am

      Hi – I’ll send it in a few minutes!

      Reply
  • Espie December 22, 2015, 7:14 am

    Could you send me the spreadsheet also? Thanks!!

    Reply
    • Diana December 22, 2015, 2:09 pm

      Yes, sending it!

      Reply
  • Ben December 22, 2015, 8:31 pm

    Would I be able to get the spreadsheet??

    Reply
    • Diana December 23, 2015, 4:12 am

      Hi Ben, sending it!

      Reply
  • miz December 31, 2015, 8:49 am

    I would love a copy of the spreadsheet… thanks 🙂

    Reply
    • Diana December 31, 2015, 11:43 pm

      Hi Miz, I sent it to your email!

      Diana

      Reply
  • Aaron January 3, 2016, 4:24 am

    Is the spreadsheet still available? If so can I get it?

    Thanks!
    Aaron

    Reply
    • Diana January 3, 2016, 5:05 am

      Hi Aaron, I sent it!

      Diana

      Reply
  • craig January 8, 2016, 5:44 pm

    If you are still sending I’d greatly appreciate a copy of the spreadsheet! Tyvm!

    Reply
    • Diana January 8, 2016, 6:46 pm

      Sent to your email!

      Reply
  • Juan January 25, 2016, 6:22 pm

    Thank you for the write up and information. Is the spreadsheet available? 🙂 I’d love to see the format in excel and play around with it. Could you send me a copy?

    Reply
  • Greg January 25, 2016, 6:51 pm

    Hi Diana,

    This looks extremely helpful – I would also appreciate a copy of the spreadsheet! Thanks so much

    Reply
  • Mariann Sidor January 25, 2016, 10:24 pm

    Hi Diana,
    Your excel sheet looks like it would be very helpful for me to track my sales. Would you be able to email me one as well?

    Thank you so much : )

    Reply
  • Mark Sly January 29, 2016, 7:44 am

    Hi Diana,

    Thank you for the articles, they are extremely beneficial and I’ve leaned a lot! Well done! Can you send me a copy of your spreadsheet please? I was working on my own but yours looks a lot better!

    Thank you

    Reply
    • Diana January 29, 2016, 8:35 pm

      Yes, I’ll send it to you!

      Reply
  • Andrew February 8, 2016, 12:48 am

    Hi Diana,
    Your excel sheet looks like it would be very helpful. I have been trying to do one myself but cant seem to get it right. Would you be able to email me one as well?

    It would be highly appreciated!

    Thank you so much : )

    Reply
  • Andrew February 8, 2016, 6:32 am

    Thanks! You have been such a BIG HELP!

    Reply
  • Eric February 16, 2016, 5:08 pm

    Can you send me a copy of your excel file? I’ve been looking for something like this.

    Reply
    • Diana February 16, 2016, 5:53 pm

      Yes, sending!

      Reply
  • Jamie February 29, 2016, 10:48 pm

    Great article! Thank you so much. If you would, please send this magic spreadsheet my way as well. Thank you!

    Reply
  • Bryan March 7, 2016, 1:07 am

    Hi Diana!

    I’m just starting out in FBA, and was hoping you could send me a link to your spreadsheet – it sounds very helpful! I would very greatly appreciate it.

    Reply
  • Nicole Osborne March 10, 2016, 4:55 pm

    Thanks for taking the time to write and post this and as the other may I also request a copy of the spreadsheet! Thanks so much!

    Reply
  • Rox March 13, 2016, 4:51 pm

    Hi Diana..I know I’m late on this post but I’m wondering if this spreadsheet is still available? If possible, can I get a copy. Thanks much!

    Reply
    • Diana March 13, 2016, 8:05 pm

      Hi Rox, I’ll send it to you!

      Reply
  • Jay March 17, 2016, 5:27 pm

    Hi Diana,

    I would appreciate getting a copy a well. Nice work putting it all together and offering to share!

    Reply
  • Cory March 29, 2016, 3:25 am

    i just found your blog i would love that excel sheet it would help so much!!!!!!!!!!!!!

    Reply
  • Mindy Barnhart April 6, 2016, 1:14 pm

    Hi Diana! I’m just starting my own adventure with Amazon FBA. I think your spreadsheet would be very helpful to me. Would you mind emailing it to me please? Thank you.

    Reply
  • Jim April 9, 2016, 12:39 pm

    Love your website and info for the new starters in amazon selling is great.
    Would it be possible for you to send me the tracking COG spreadsheet also.
    Thanks
    Keep up the good work Diana!

    Reply
    • Diana April 9, 2016, 12:59 pm

      Hi Jim,
      Sending it!

      Diana

      Reply
  • Kimberly May 18, 2016, 2:50 am

    Hi Diana,

    I would love to get a copy of your tracking COG spreadsheet, it looks like it would be very helpful. Could you send me a copy?
    Thanks

    Reply
    • Diana May 18, 2016, 3:06 am

      Yes, will send in a minute.

      Diana

      Reply
  • Rico June 9, 2016, 12:29 pm

    hi Diana. thanks for sharing this info. May i also request for a copy of your spreadsheet template? Thanks so much!

    Reply
  • Brooks June 18, 2016, 7:32 pm

    Diana, this has been so helpful to me, thank you so much for sharing. Would you be able to send me the spreadsheet too? Your site is amazing!!

    Reply
    • Diana June 19, 2016, 3:12 pm

      Yes, sending!

      Reply
  • Derrick R July 9, 2016, 10:33 pm

    Hey thanks for all you do! Really appreciate your unselfishness.
    Would you be able to send me the spreadsheet as well?

    Thanks!

    Reply
    • Diana July 10, 2016, 1:53 am

      Yes, sending!

      Reply
  • Pia July 19, 2016, 11:04 pm

    Hi Diana,
    Really helpful post. Could I get a copy of the spreadsheet.

    Thanks,

    Reply
    • Diana July 20, 2016, 2:34 am

      Yes, sending!

      Reply
  • Clay August 15, 2016, 9:57 pm

    Can i have a copy of it?

    Reply
  • Tim August 21, 2016, 2:44 pm

    Can I please have a copy of the spreadsheet
    Thanks in advance

    Reply
  • khaoula September 20, 2016, 4:57 am

    Hi Diana,
    Would you be able to send me the spreadsheet to my email.
    Your site is amazing!!
    Thanks in advance

    Reply
  • Jamie September 26, 2016, 6:29 pm

    Hi Diana,

    May I request a copy of this spreadsheet as well? Thanks for all the wonderful information you are sharing.

    Jamie

    Reply
  • Mark S September 26, 2016, 10:00 pm

    Hi Diana,

    Just started out with FBA. Very informative post. Could I also get a copy of your spreadsheet?

    Thanks!
    Mark

    Reply
  • Raina October 2, 2016, 1:12 am

    I’m a FBA newbie could I please have a copy of your awesome spreadsheet?

    Reply
  • Lori October 25, 2016, 10:29 pm

    Can I please have a copy of your spreadsheet?
    Thanks so much!

    Reply
  • stacey November 3, 2016, 6:14 pm

    I would love a copy of it too please! Thanks!

    Reply
  • John November 14, 2016, 1:44 am

    Reply
    • Diana November 16, 2016, 1:30 am

      I like that it’s a one-time cost and that it calculates the COGS….Also I like that it has individual sales info by SKU, plus it has a lot of neat features.

      It looks like a great spreadsheet and I think it would be good for people not wanting to pay a monthly fee.

      Diana

      Reply
  • Ashley December 1, 2016, 7:31 pm

    Thank you for all the helpful information. I am new to amazon FBA, could you please send me your excel sheets too. Thank you so much.

    Reply

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!

Services

Amazon FBA Supplies

Find the Topic That You’re Looking For!