How to properly track inventory on Amazon Seller Central to keep my business in order and be ready to file taxes at the year end?
For those of you who do not track your inventory through ScanPower Reports or InventoryLab, I assume you are accounting for inventory through some hellish excel process… and if not, you are probably calculating your ending inventory (and cost of goods sold) only at year end in order to file taxes and understand how profitable your business was for the year.
If you do not use InventoryLab or ScanPower Reports, and you haven’t done anything yet to understand your cost of goods sold for last year, the beginning of the year is your golden opportunity to start off on a good foot. You’ll need this to file taxes anyway, so why not do it now!
This may take some time, but if you keep up with it, it won’t be that bad going forward. I suggest doing this monthly if you prefer not to track it in detail with one of the other options (excel, SPReports or IL.)
Although there are likely other ways to get this data from Seller Central, this is what I suggest. You only need excel and Seller Central open (and maybe an adult beverage; that helps):
Seller Central > Reports > Fulfillment > Monthly Inventory History
Choose your time frame. If you are in January and you want to determine the unsold inventory in place at December 31st, choose “Last Month” from the Event Month dropdown. This is what you will do to calculate your cost of goods sold for the year.
Click “Request Download”
Report status will say “In Progress” which will eventually change to a clickable download button.
A .txt file will download; open that file.
Open excel if you haven’t already.
In the .txt file, click “CTRL” and “a” at the same time to select all data
Click “CTRL” and “c” to copy
Move into excel; in an empty worksheet, click “CTRL” and “v” at the same time to paste.
Delete the column titled “average quantity” “fulfillment-center-id” “detailed-disposition” and “country” (To do this, right click on the column title and then select delete.)
After you have deleted those columns, click “CTRL” and “a” at the same time to select all data.
Sort > data
Sort by: end-quantity and value (choose largest to smallest)
All of the rows that have zero quantity will now be at the end of your data set. Delete those rows. (You don’t have to do this, but I like to remove these rows to avoid confusion.)
Add a column titled “purchase price.” This is where you will enter your purchase price. Hopefully it’s embedded in your SKU so you can do this pretty easily. If not, there are options, but in an ideal world you have added this to your SKU.
The final column you will add a formula that multiplies the quantity by the cost; title this “unsold inventory.”
Add a total at the bottom of the inventory value column and VOILA, you have the value of your unsold at December 31st!
1. If you don’t have cost price in SKU:
Unfortunately you will need to figure it out somehow:
Do you have records of what you’ve paid for each item of inventory?
If you started your business in last year it won’t be as difficult.
Some people categorize inventory in “buckets” where they say (for example) all thrifted books had a cost of $1, all toys had a cost of either $10, $20, or $50 (and then group and calculate in cost buckets) all grocery items were $5, etc. If you have a material amount of inventory you are in for a bigger challenge, but without knowing what you paid for the inventory you sold last year, you will have a tougher time with your tax return.
2. If you have various purchase prices on the same item (purchased the same item at different stores and different sale prices):
Obviously check with your CPA but some people take a general average when buying multiples of the same items at different prices as long as the prices don’t vary materially.
3. You did not get a value column when I downloaded. Now what?
You need to add the value yourself. You’ll add a cell with your cost per unit and then another cell that multiplies your cost by the number of units on hand.
Yes it’s a pain and this is why people put it off; I feel very strongly that all sellers need to know how much of their money they have invested in unsold inventory at least once a quarter but ideally monthly.
Do you have your cost imbedded in the MSKU?
Many sellers do that to help with this process. (And yay for you for looking at your inventory on hand!).
I know that so many people don’t do this and then get a big surprise at tax time.
One seller, revamped her business plan for the summer because her unsold inventory number was a number she wasn’t comfortable with. Another seller is being more cautious of her debt vs unsold inventory by doing this exercise.
You are a smart business owner to take a look at this!
Anna Hill is the founder of Accounting We Will Go, a firm that provides accounting and bookkeeping services along with training for Amazon sellers.