Question about inventory tracking with spreadsheets

[cwh1981]

I have started selling a few weeks ago and I am recording info for each listing/sale in a single tab for tax purposes.  Just in a few weeks my spreadsheet has grown to 130 listings (rows).   My concern is the spreadsheet will become huge over time.

I'm curious if folks are breaking down their spreadsheet by year (i.e. a new spreadsheet gets created each new year) or if you are using one giant spreadsheet for ALL of ebay selling history spanning multiple years?

It feels like breaking down spreadsheet by year may be easier to manage, but that brings the situation where I still have previous items listed from the prior year come January 1st.. do I just copy those unsold items over into the new spreadsheet?

Any ideas on how you are going about this I would LOVE to hear it.

Thanks in advance!

Charlie

[dbfolks166mt]

     There are multiple pieces to your situation. First Inventory. Most sellers on eBay are not required to keep or report "inventory" on their taxes under the small business exemption. You can find the details of that exemption in the following IRS publication. 

https://www.irs.gov/publications/p334#en_US_2023_publink10005555 

     You will still need to keep track of your cost of goods sold and supplies/expenses for tax purposes. I have a multi-tab spreadsheet that contains a tab for what I paid for an item at the time of purchase and a tab for other expenses such as paper, ink, packing supplies............ I create a new spreadsheet every year. 

     One of the factors that goes into what goes on that spread sheet and is reported in the tax year is the type of accounting you are using. That is something that you have to initially declare on your schedule C and stick to from year to year. It is possible to change but it is a pain to do so. Those details can also be found in the link I provided. 

     There are basically two types of accounting cash and accrual the main difference being when you report the revenue and/or expenses. With accrual you record expenses and revenue at the time they happen regardless of the cash flow. With cash account obviously you record revenues and expenses when the funds are realized. 

     Cash accounting can become a bit tricky at the end of the calendar year for either revenues or expenses. For example if you purchase an item or supplies on 29 December 2024 and pay with a CC card that expense would not be a 2024 expense but will be realized in 2025 once you have paid your CC statement AND the funds have been withdrawn from your bank.

     The same applies to revenues. If a buyer purchases an item on 29 December 2024 with cash accounting that would not be realized as part of your 2024 revenue but it would be in 2025 when eBay has sent the payout to your bank AND the funds have been deposited. 

     With accrual accounting both expenses and revenues are recorded at the time they are realized so an expense on 29 December 2024 would be included in your 2024 taxes and a sale on 29 December 2024 would be part of your 2024 revenues. 

     There are pros and cons to both methods. You can always talk to an accountant about which is best for you. Personally I have always used accrual accounting. 

[luckythewinner]

Just to amplify what dbfolks166mt said:

Publication 334 explains that a seller who does less that ~$25 million per year can avoid having to account for inventory by "Treating inventory as non-incidental material or supplies":

"If you account for inventories as materials and supplies that are not incidental, you deduct the amounts
paid or incurred to acquire or produce the inventoriable items treated as non-incidental materials and supplies in the year in which they are first used or consumed in your operations."

In its most simple terms, this means that if you buy an item in 2020 and sell it on eBay in 2024, you do not account for it in your inventory in 2020, 2021, 2022, and 2023 ... you only deduct the cost as an expense in 2024 when you sell it. (but obviously you have to hold onto the receipt showing the acquisition cost.)

By using this method coupled with cash-basis accounting, it is very easy to manage a small eBay business.

I accomplish this by keeping one master spreadsheet with all my unsold inventory as I acquire it, and creating a new spreadsheet of sold inventory for each year. When an item sells, I move it from the unsold sheet to the sold sheet for the year it sold in.

At the end of a year, the sold sheet for that year has all the inventory I will expense for that year, and the unsold sheet has all my remaining unsold inventory. (I also keep a second sheet for each year with all my expenses like boxes, tape, padded envelopes, bubble wrap, etc.)

Doing my taxes at the end of year takes a couple hours as all I need to do is export all my eBay activity to a spreadsheet, match it up with my sold sheet for that year, and copy over the "cost of goods sold". (And if you buy shipping labels through eBay, the activity export will include all of that information too.)

[brightlightbookseller]

Doing a database system will be better, but more complex to set up.  A spreadsheet can work.  We currently have over 11000 rows in our spreadsheet that have data in them, and I archive items that are no longer active listings and that are over a year old to another set of tabs, so there's a lot of records in those tabs too. It's... manageable, if you know what you're doing, but it gets a little slow for some purposes.  We're using Google Sheets, btw, it would be more efficient in Excel or another app on a computer if you have a fast enough computer, but still can definitely get more difficult to manage.

I will say that Pivot Tables are amazing to work with to find out details about our listed and sold items since we have all that data.  We record everything from the item's title and SKU, date it was listed and ended (whether it sold or something else), to the person here who priced it and the one who listed it, and some key data about what the item is if it's not a unique item we're not likely to see again.  It's a good bit of data. 

One thing I kinda wish I could keep in there more easily was the eBay item number, but we don't currently.  Since it changes if, say, someone orders and then cancels, it would be some work to keep track of those changes that we haven't felt was worth it yet.

Edit:  I should mention that most of our items are also recorded in a database system for our retail store tracking, but we don't use that for quite as much detail because it wasn't built with that in mind.  We can use the SKU to pull it up in either the spreadsheet or the database if we need to compare any of the information that isn't recorded in both places, though.

[dbfolks166mt]

In its most simple terms, this means that if you buy an item in 2020 and sell it on eBay in 2024, you do not account for it in your inventory in 2020, 2021, 2022, and 2023 ... you only deduct the cost as an expense in 2024 when you sell it. (but obviously you have to hold onto the receipt showing the acquisition cost.)

     Actually if you are NOT reporting inventory if you purchased an item in 2020 you report the expense on either your 2020 or 2021 taxes depending on whether you are using accrual or cash accounting. You do NOT report the expense when you sell the item in 2024. 

[luckythewinner]

---

@dbfolks166mt wrote:

In its most simple terms, this means that if you buy an item in 2020 and sell it on eBay in 2024, you do not account for it in your inventory in 2020, 2021, 2022, and 2023 ... you only deduct the cost as an expense in 2024 when you sell it. (but obviously you have to hold onto the receipt showing the acquisition cost.)

     Actually if you are NOT reporting inventory if you purchased an item in 2020 you report the expense on either your 2020 or 2021 taxes depending on whether you are using accrual or cash accounting. You do NOT report the expense when you sell the item in 2024. 

---

My post was specifically about "Treating inventory as non-incidental material or supplies".

In this case, the publication is VERY clear that you deduct inventory in the year it is "used or consumed in your business".  And the publication goes on to define "used or consumed in your business" as: 

"Inventory treated as non-incidental materials and supplies is used or consumed in your business in the
year you provide the inventory to your customers."

Which in my example is 2024, not 2020 or 2021.

[dbfolks166mt]

@dbfolks166mt wrote:

In its most simple terms, this means that if you buy an item in 2020 and sell it on eBay in 2024, you do not account for it in your inventory in 2020, 2021, 2022, and 2023 ... you only deduct the cost as an expense in 2024 when you sell it. (but obviously you have to hold onto the receipt showing the acquisition cost.)

     Actually if you are NOT reporting inventory if you purchased an item in 2020 you report the expense on either your 2020 or 2021 taxes depending on whether you are using accrual or cash accounting. You do NOT report the expense when you sell the item in 2024. 

My post was specifically about "Treating inventory as non-incidental material or supplies".

In this case, the publication is VERY clear that you deduct inventory in the year it is "used or consumed in your business".  And the publication goes on to define "used or consumed in your business" as: 

"Inventory treated as non-incidental materials and supplies is used or consumed in your business in the
year you provide the inventory to your customers."

Which in my example is 2024, not 2020 or 2021.

     In that case you would be correct and you would need to keep and report inventory probably using the  NIMS inventory method. This is generally used by manufacturers that consume raw materials and turn it into a finished product. For most sellers on eBay there is probably no need to keep or report inventory but everyone's case can be different and without the OP disclosing a lot of additional information which most would not want to put on a public forum what is best for each individual depends on their situation which is why I always recommend talking to a tax accountant. 

[cwh1981]

Thanks so much for the very detailed responses!  I like the idea of a separate spreadsheet for "unsold inventory" and then have yearly spreadsheets for "sold" items and think I will implement that system!  Any other advice or gotchas any of you can think I would love to learn more!

[luckythewinner]

 In that case you would be correct and you would need to keep and report inventory probably using the  NIMS inventory method

ROTFL. Yes, if I was treating inventory as "Non-Incidental Material or Supplies" (NIMS) then yes I would use the  "Non-Incidental Material or Supplies" (NIMS) inventory method. Kinda by definition.

For most sellers on eBay there is probably no need to keep or report inventory but everyone's case can be different

You can almost think of it almost like selling an item you originally bought for personal use - when you  sell it, you deduct the cost as an expense to offset the income.

[dbfolks166mt]

 In that case you would be correct and you would need to keep and report inventory probably using the  NIMS inventory method

ROTFL. Yes, if I was treating inventory as "Non-Incidental Material or Supplies" (NIMS) then yes I would use the  "Non-Incidental Material or Supplies" (NIMS) inventory method. Kinda by definition.

     Not sure of the humor in my comment but the point was/is that there are probably VERY few sellers on eBay that are into manufacturing and need to even track or report inventory. 

For most sellers on eBay there is probably no need to keep or report inventory but everyone's case can be different

You can almost think of it almost like selling an item you originally bought for personal use - when you  sell it, you deduct the cost as an expense to offset the income.

     While I have few of those types of items when I decide to sell it I take the expense in the year in which I realized it in accordance with the accrual accounting standards. If it sells in that tax year then fine it offsets the revenues for that particular item if not it offsets revenues from other sold items and since you report at the aggregate level on your taxes that is all that matters. 

[johnrj1226]

Hide the  rows for the oldest year just make certain the first and  last  rows have the year posted in the first  upper and lower left hand column

[tobaccocardyahoo]

The document @dbfolks166mt referenced has a lot of content which probably serves to confuse many,

I do not buy individual items except rarely. A spreadsheet with costs of each item would be a group of numbers which were unverifiable. The document talks about inventory valuation methods without providing enough detail.

I use accrual accounting, so what is reported as COGS is that which applies to the reporting year.

I use the standard COGS calculation, where the starting inventory +purchases-ending inventory is COGS.

Since I rarely sell everything in a lot, some portion of that lot is in both starting and ending inventory. There are a variety of ways which IRS will accept for that inventory, and some of the confusing text deals with that.

I work from a data base of purchases, not a spreadsheet, and I value what the portion of each lot which has sold. It always feels squishy when I do it, but it is legal. Obviously, one cannot write off as much of one's purchases as COGS as one could if we could use cash accounting, but we can do better than if we arbitrarily assigned a cost to each item.

If I am confusing matters, my apologies, but if one is tracking ooak items a spreadsheet is not appropriate for any but the lowest inventory sellers.

[dbfolks166mt]

The document @dbfolks166mt referenced has a lot of content which probably serves to confuse many,

     You ever seen an IRS document that didn't confuse a lot of people? 

I do not buy individual items except rarely. A spreadsheet with costs of each item would be a group of numbers which were unverifiable. The document talks about inventory valuation methods without providing enough detail.

I use accrual accounting, so what is reported as COGS is that which applies to the reporting year.

I use the standard COGS calculation, where the starting inventory +purchases-ending inventory is COGS.

     That works but it's an extra step since your purchases, if they are items you are reselling equate to your           COGS for the year since you are using accrual accounting the expense is realized when it takes place. 

Since I rarely sell everything in a lot, some portion of that lot is in both starting and ending inventory. There are a variety of ways which IRS will accept for that inventory, and some of the confusing text deals with that.

     Are you actually reporting inventory?

I work from a data base of purchases, not a spreadsheet, and I value what the portion of each lot which has sold. It always feels squishy when I do it, but it is legal. Obviously, one cannot write off as much of one's purchases as COGS as one could if we could use cash accounting, but we can do better than if we arbitrarily assigned a cost to each item.

     Cash accounting would be no different with regards to the expense. While with accrual accounting you would write off the entire cost of the lot when you made the purchase with cash accounting you would recognize the expense when the cash was actually expensed from your account. If you purchase with a CC this can get complex at the end of the year. 

If I am confusing matters, my apologies, but if one is tracking ooak items a spreadsheet is not appropriate for any but the lowest inventory sellers.

[onefootflipper1]

Most resellers use cash accounting, not accrual. All expenses happen when they happen and all income happens when it happens.

[gloryglorygifts]

Hi and welcome! We have always used a spreadsheet to track our sales and expenses. Yes, it does grow over time. Currently I have over a thousand line items of products we have sold over the last year from our sites, ebay included. The way I do it is a new year is a new file.  Everything on our sheet is formula driven, but it has everything on there including sale price, cost, inventory, and expenses, etc. To give you an idea, I've done screen shots of my column headings. It also gives me a number of inventory items I have sold from all of our locations at the beginning and end of the year, which is a huge help come tax time. Inventory is included on the tax form. 

To start a new year I use the old spreadsheet and "save as" the new year name. I manually add the inventory amount from the close of the prior year and then erase all under the sold and purchased columns. It should match exactly with your closing totals from the prior year as far as # of items and $. 

I delete items we no longer sell and add new things to keep the spreadsheet fresh all the time. 

Hopefully this isn't too confusing. A spreadsheet can be used. You do what works for you though.