When using spreadsheets to track inventory, you could end up with as many as 25 columns of information that you have to complete and maintain in order to track everything you need for your records and tax purposes.
Inventory

My Handmade Biz-How to Track Inventory

How to track inventory is a question that makes the eyes of most handmade and creative business owners glaze over in confusion and perhaps just a touch of terror. This post offers practical tips on how to track inventory including what info you need to track.

Last week, in Part 1 of this 4 part series on Inventory and Cost of Goods Sold, I talked about what is inventory and WHY inventory is important.  Over the next 2 weeks I’ll talk about Cost of Goods Sold.

From what I’ve read, keeping detailed inventory counts and records is a pretty controversial topic in the handmade business community.  There are even some disagreements about whether the IRS even requires makers or crafters to track inventory and cost of goods sold or not.

I could tell you to go read about this starting on page 13 of IRS publication 538 (and you can if you want to), but I don’t want you to overdose on headache meds and alcohol!  Instead, I’ll tell you……

What I’ve learned in 30+ years of being a professional bookkeeper

  • Makers and crafters are considered manufacturers (meaning we make things to sell)
  • Manufacturer’s keep materials and supplies on hand (the stuff we use to make the things we sell)
  • Those materials and supplies ARE INVENTORY (and we need to keep track of them in detail)
  • Each item we keep in inventory is tracked at the LOWER of cost or retail (what we paid vs what it might cost to replace that item)
  • We can’t expense or deduct the cost materials and supplies in the year that we bought them (here’s that black hole)
  • Instead, we MUST wait until the year that we sell the finished item BEFORE we can deduct the cost of the supplies used in that item as Cost of Goods Sold

When using spreadsheets to track inventory, you could end up with as many as 25 columns of information that you have to complete and maintain in order to track everything you need for your records and tax purposes.This means that we MUST keep detailed inventory counts and records of:

  • What we’ve bought
  • How much it cost
  • The amount we used
  • What is left
  • And, the normal retail value

Sounds simple. Right?  Nope, not at all.

Inventory is a series of increases/additions (what we buy) and decreases/deductions (what we use).  We have to keep a running balance of what’s there, and we have to have periodic totals for tax purposes, such as:

  • What we had BEFORE we became an official business
  • The amount we purchased each year
  • How much we used each year
  • And, we need receipts for EVERYTHING that show exactly what we bought

That brings us to how to track inventory….. 

Again, this is a controversial topic.

Some people will tell you that spreadsheets are the way to go, because you can do anything with spreadsheets and they are free.

Other’s (like myself) will tell you that real bookkeeping software like QuickBooks is your best solution, even if it costs money to buy it.

Either way, it’s a personal choice, and I totally get that.  So, I’m going to leave it up to you.

Ya, I know not a whole lot of help on that front – but I don’t like telling people what they have to use in order to do something.

Instead, I’ll give you a list of things that you’ll need to know:

  • Date purchased
  • What you bought (brand & name)
  • The color
  • How many you bought (the number of skeins, package, etc.)
  • Quantity (in skein, package, etc.)
  • Total purchased (how many you bought MULTIPLIED by quantity)
  • Price you paid for each things you bought (including any sales tax, purchase discounts, and shipping).  See notes below.
  • Cost based on how you use and sell it (per yard or per item)
  • Total you paid for this specific item, which is the amount your inventory is increased by.

Up to this point, you could very well use spreadsheets for your tracking what you buy – even though it would mean perhaps 13 columns of information that you have to complete and maintain.  BUT,this only takes care of what you’ve purchased.

You still need to keep track of:

  • What you’ve used, removed for personal use, given to charity, or sold
  • The amount that’s left of a specific item
  • Retail price – so you can make sure you charge enough for the materials you use in your finished items in order to replace them if they aren’t on sale!

This is where using spreadsheets gets really complicated!  You could end up with as many as 25 columns of information that you have to complete and maintain in order to track everything you need for your records and tax purposes.

Some people like to use spreadsheets – but I prefer software and if you want to know why, watch the replay of my free 90-minute webinar “Thinking of ditching the spreadsheets and using (QuickBooks) software for your bookkeeping needs”.

– – – – – – – – – – – – – – – 

Notes:

Calculating sales tax & shipping costs when buying multiple items at the same time.  There is no perfect solution for calculating this amount – but you can evenly distribute these costs over each item that you purchase.  Here’s an example:

  • You buy 15 skeins of Hobby Lobby, I Love This Cotton on sale for $2.99 per skein (normal retail is $3.69 per skein)
  • You’re charged 6% Sales Tax
  • Shipping charges are $11.95 (so you divide that by 15 items which equals .796 per skein)
  • Sales Tax is calculated by multiplying $2.99 by 6% which equals .1794 per skein
  • Now you add those 3 numbers together ($2.99 + $0.796 + $0.1794) which gives you a cost per skein of $3.249 which is then rounded down to $3.25 cost per skein and 0.02 per yard ($3.249 cost per skein divided by 180 yards per skein).

Now, that’s only part of the math.  You still need to know the RETAIL cost for each skein, so you make sure you are charging enough to replace that yarn when it’s NOT on sale.  So,

  • Shipping charges are still .796 per skein
  • Sales Tax is now .2214 per skein ($3.69 x 6%)
  • Now add these 3 numbers together ($3.69 + $0.796 + $0.2214) for a total retail cost per skein of $4.7074 which is then rounded to $4.71 RETAIL price per skein and 0.026 per yard ($4.7074 divided by 180 yards)

Your invoice will provide you the total sales tax charged and the total discount BUT you will probably need to make a slight adjustment to the purchase discount amount to make the transaction balance.  This is because we are calculating sales tax on each skein and they will be calculating sales tax on the total purchase and there will be a slight rounding error that you will need to account for.

– – – – – – – – – – – – – – – 

That’s it for this week.  Next week we’ll talk about Cost of Goods Sold and focus on WHAT it is.

Until then, Happy crafting!

Nancy

When using spreadsheets to track inventory, you could end up with as many as 25 columns of information that you have to complete and maintain in order to track everything you need for your records and tax purposes.
;
author-sign

You may also like...

4 Comments

  1. Advertisements
  2. […] Over the last couple of weeks we’ve talked about inventory – what it is and how to track it. […]

  3. Courtney says:

    Another great post, Nancy! It’s a lot to take in, and if someone could make a program that’s easier than Quickbooks to track this stuff for handmade businesses, I would buy it in a flash!

    1. Thanks Courtney 🙂 It is a lot to take in and I’m trying to put it out in small enough chunks that everyone can digest it. Just curious though – – what don’t you like about QuickBooks?

So, what did you think of this post? Feel free to leave a comment below.

This site uses Akismet to reduce spam. Learn how your comment data is processed.