How to calculate Average COGS?

[KJ23]

I'm a little confused on how to calculate COGS using the Average method. I wrote an ecommerce platform for my business and when an item is sold, the COGS for that item is saved in the sales record and that's what I use for as my COGS value (so it's like a real-time calculation, as opposed to some business' that calculate COGS quarterly, from what I've read).

Is the COGS for an item using the Average method = [(SUM purchased items costs)/(# items purchased)] * (# sold)?

I thought that made sense, until today when something confused me and I ran the below test, which shows that there's something wrong with the equation I'm using.

Example 1:

For example, supposed I purchase 50 widgets for $1.00/pc. I sell 10 widgets and the COGS = [($50)/(50)]*(10) = $10.

Example 2:

Now I purchase 50 widgets for $1.00/pc and then sell 10 more widgets. The COGS = [($100)/(100)]*(10) = $10.

Example 3:

Now I purchase 100 widgets for $0.50/pc and then sell 10 more widgets. The COGS = [($150)/(200)]*(10) = $7.5.

Example 4:

Now I sell the remaining 170. The COGS = [($150)/(200)]*(170) = $127.50.

Summary:

Total paid for items: 150
Total calculated cogs: 155

What am I doing wrong?

Thanks for your help

 

[Counterofbeans]

It's because you are messing up your inventory cost calculation. In this situation, you either you need to pick (1) a moving average or (2) a true average, neither of which you are doing correctly right now.

I'd probably go with a moving average, as a "true" average doesn't really work until the end of a reporting period and you can look back and calculate the costs.

In a moving average cost-flow assumption/calculation, you re-calculate the average cost of each inventory item in stock after every inventory purchase, which you haven't done above. The calculation is the total cost of the items purchased divided by the number of items in stock.

Since the cost per item hasn't changed from Example 1 to Example 2, it really doesn't make too much difference, but it does in Example 3.

At the end of example 2, you have 80 units in inventory costing $80, or $1 per unit. When you purchase 100 units at $0.50, you then have 180 units costing $130. This results in an average cost in inventory of ~$0.72. As such, when you sell all remaining 180 units, they'll total $130. When added to the $20 above, you'll have a total COGS of $150.

If you were steadfast in using a true average, then your cost per unit, from minute one, would be $0.75 per unit. As such, when you sell all 200 units, you'll get a total COGS of $150.

Of course, you can probably imagine that the answer to your question, "Is the COGS for an item using the Average method = [(SUM purchased items costs)/(# items purchased)] * (# sold)?"...the answer is NO. Change your formula from, "purchased" to "number of items in stock," recalculate the average cost in inventory after every purchase, and you should be fine.

Hope that helps

 

[KJ23]

Thank you very much for your reply!

If I understand what you're saying, equation I was using is actually for the True Average method and I was applying it (incorrectly) to the Moving Average method. Is that right?

So, does mean that the correct equation for me to use would be the following?

COGS = ([qty in stock] * [COGS of items in stock] + [total cost of items in PO]) / ([qty in stock] + [qty in PO])

(and I would need to do that calculation after every PO is made)

 

[Counterofbeans]

Thank you very much for your reply!

If I understand what you're saying, equation I was using is actually for the True Average method and I was applying it (incorrectly) to the Moving Average method. Is that right?

I suppose so. My only point was that you weren't calculating it correctly.

So, does mean that the correct equation for me to use would be the following?

COGS = ([qty in stock] * [COGS of items in stock] + [total cost of items in PO]) / ([qty in stock] + [qty in PO])

(and I would need to do that calculation after every PO is made)

When your formula refers to a PO, are you talking about a received PO? If so, why isn't that now an item in stock? If it's unreceived, then I'd be very careful including those in inventory (you now have an issue in regards to when title and risk of loss pass)

 

[LYAK]

You are running into a problem because you are not using the moving average of your ENTIRE inventory; you are treating each purchase separately. You wouldn't use the equation [($150)/(200)] because at this point, you only have $130 worth of inventory and you wouldn't use 200 because at this point you only have 180 widgets in inventory. Therefore, you have a unit price of $0.722222, meaning that when you sell 10, the COGS will be $7.222222. When you sell the remaining 170 units at $0.72, you get a COGS of $122.7788. So, the first 20 widgets sold equal a COGS of $20. The next 10 widgets equal a COGS of $7.22222. The last 170 widgets have a value of $0.72222, and overall a value of $122.7788. So, $20 + $7.22222 + $122.7788 roughly equals $150, the total amount spent (obviously rounding plays a role here).