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.
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.
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’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.
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:
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.