Need help with the challenge of inventory valuation

[SumpinSpecial]

Hi Guys,
It's been a while, I'm doing fine. My shop is also doing fine, still slowly growing and I'm in the middle of re-branding. I need some help trying to figure out a simple way to track the value of my inventory. When I google for how to do that, I just find the typical accounting 101 lessons that talk about FIFO, LIFO and all that. I understand that stuff, but right now my business is so small that I want just a very simple approach. The reason I'm having difficulty is that I can't yet afford the version of QB Online that handles inventory (two versions higher than what I can currently afford, grrr), so I have to do it in Excel, manually. The good news is that I think I know where my method is broken.

Currently I'm using this formula:

ending inventory value from the last time I measured it (say, end of last month)
+
dollar value of wholesale purchases since last measurement
-
dollar value of merchandise donations made since last measurement
-
sales made since last measurement


The first (and maybe the only but it's a big one) problem is that the wholesale purchases are (duh) wholesale, but the donations and sales are retail. So clearly I need to keep track of both wholesale and retail prices of everything? And then only use the wholesale price throughout the formula?

What else? Some details to know: I don't have any consumable or perishable merchandise. I know I probably should include depreciation, but let's not do that for now (until I'm bigger). The average wholesale cost per item is $30, so depreciation isn't going to kill me yet.

[tallen]

Your inventory valuation should be based on the cost to acquire the merchandise (including shipping). When you sell an item from your inventory, you should be keeping track of the inventory cost of that item (as your "Cost of Goods Sold"), as well as the overall revenue that the sale produced.

A quick search yielded this: https://www.accountingtools.com/articles/what-is-inventory-valuation.html

Oh, and depreciation does not apply to inventory -- only to capital assets.

[Paul]

I'm glad you're doing well. Your formula looks correct.

1000 previous valuation+500 purchased-300 sold=1200 current inventory

You already understand the Fifo/lifo system as a means of valuation for same merchandise cost fluctuations. You may have bought some items at $ 2 each but now pay $ 1.75, and now the inventory is mixed at different costs. You can use the fifo, lifo or an average cost. It relates more to calculating margin than to inventory valuation. If you sell the $ 2 item for $ 4 your margin has been 50%...at the new cost of $ 1.75 obviously the margin increases. You now can calculate margin on the individual item or on the new average cost of $ 1.90 (example). Fifo is probably the easiest, you just have to keep track of same items at different costs.
You probably know this, but the “increased” valuation of the inventory is actually profit at the end of the year. If you use the full QB financial statement it will adjust assets accordingly.

The QB inventory is a bit of a pain to set up but when you get bigger it will make it all automatic. You will eventually want to move to the higher level.

How much of the QBs are you using? Do you use it to create invoices? Do you enter the cost at time of sale to generate a margin/p&l report?

* I’m not sure what you mean by donations. Is that items you give away? I assume you also track that separately as charitable donations for taxes. Although I’m not sure it matters tax wise since it is reducing bottom line profit anyway. You just don’t want it to distort your actual margin calculations for your own information. Also, donations are not at “retail”, they are at your cost.


Tallen is correct about depreciation on typical inventory. However , you can "write off" inventory if it becomes obsolete or loses value some other way. On freight "in" you can allocate a direct proportion among items OR you can line item freight as a separate expense. It's a choice, sometimes it is too complicated to calculate per item freight costs,depending on the size and mix of the shipment. Shipping "out" of course is much easier because each shipment has its own shipping cost.

[SumpinSpecial]

Okay, I'm recording cost of goods sold when I buy merchandise, but only aggregated. I need to start keeping track of it on a per-unit basis. I can't use QB because the inventory feature is two whole upgrade increments above what I can afford. So stupid!

[turboguy]

You want to ignore that sales figure in dollars but track it by quantity. In other words you buy 6 dog collars for $ 10.00 each and sell two of them for $20.00. Forget the 20 bucks each. You only need to think about the two you sold. So you bought 6 dog collars for $ 10.00 each and sold two of them so now you have 4 dog collars that cost you $ 10.00 each or $ 40.00 worth of dog collars. You can also discount items you feel you are stuck with. If you bought 6 dog collars for $ 10.00 each and sit on them for 4 years and figure you will have to sell them for $ 5.00 to get rid of them you can value them at $ 5.00. If no one ever wants something you can also write off the value.

[SumpinSpecial]

Ah, thanks Turboguy. I think that's the approach I need. I'll adjust my spreadsheet to track it that way.

I was also wondering about discounting umoving items, so that's helpful as well.

[tallen]

Yeah, an inventory is an actual physical count of items. Assigning value to that inventory can be done by the FIFO, LIFO, or average cost methods, subject to the possibility of write-downs as Ray points out. FIFO or LIFO might be important if there is a lot of volatility in your market, but that requires keeping track of more classes of items. The average cost method might be easier: How many XYZ dog collars did you buy over the inventory period, and how much did you pay for them in total? Divide number purchased by total cost to get average cost per item, and multiply that cost by the number you have left to get the value of your remaining inventory (of course you also have to take into account the number of items you already had on hand at the beginning of the inventory period, and their value, as well).

Disclaimer: none of the businesses I am currently involved in entail keeping track of an inventory; it's been awhile...