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:
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:
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).
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.
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:
I hope this post helps and I value your feedback. Leave a comment below or find me on my Facebook Page.