Moving average formula for calculating cost

Posted by Thomas Wong
Moving average

Costing methods are very important to nail down because, given the same stock levels and purchase prices, each method can report very different levels of profit and cost of goods sold (COGS). We’ve talked about FIFO (First in first out) and LIFO (Last in first out) costing methods before, so it’s now time to discuss a third: the moving average formula.

Using a moving average formula will derive a product cost that falls between what FIFO and LIFO would report, which can be very useful if you don’t want to — or it’s simply impossible — to deal with a lot of different costing layers.

Moving average doesn’t use costing layers

You might recall this table for Zealot lens purchase and sales orders, which we used to discuss FIFO and LIFO. We tallied the cost of goods sold for 70 units by finding the sum of different costing layers.

costing layers per FIFO go from oldest to newest, costing layers per LIFO go from newest to oldest

FIFO used January and February’s orders to derive COGS because it takes from the oldest stock first, and LIFO used March and February for COGS because it takes from the newest stock first. Now let’s examine COGS by tallying the sale of 70 units on March 19 and March 23:

Whichever way you slice it, that’s two costing layers you have to account for when you sell those goods. These layers can make it more difficult to calculate profit or COGS, especially when a single order falls across multiple cost layers. The current example is very neat and tidy because the sales orders use up exactly two costing layers. However, you can often have sales orders that takes all the Zealot lenses from January, and just three lenses from February. That would leave you with a partial cost layer from February that you still need to account for moving forward.

Moving average formula

Using a moving average formula saves you from having to track any costing layers at all. Instead, you’ll re-calculate the average cost per unit each time you purchase more stock — hence the name “moving average”. Here’s those same set of POs for Zealot lenses, with an extra column for unit cost:

The Unit cost in the moving average formula is recalculated each time new stock comes in

The unit cost is based on the value of incoming stock + the value of leftover stock from previous orders. The value of what you have left + value of the newly received stock is your total cost. When you divide total cost after a PO by total quantity after a PO, you get unit cost. Here’s how to use the moving average formula to arrive at the unit cost of $10.50:

Calculating cost with the moving average formula is Total Cost after PO divided by Total Quantity after PO equals Unit Cost

The big advantage to the moving average approach is that COGS becomes a lot easier to calculate: all you need to do is see how much of a product you have sold, and multiply that amount by your current unit cost. Because you’ll always be recalculating your unit cost after every PO, there’s only ever one unit cost to worry about. Let’s recall the sales orders of 70 units of Zealot lenses from earlier, but this time we’ll calculate the COGS with moving average:

Given a quantity sold of 70 and unit costs of $10.50, the total COGS per the Moving Average Formula is $735.00

Now that we’ve used the moving average formula to derive COGS from Zealot lens sales, you can that it falls between the figures that FIFO or LIFO report:

  • FIFO reported a COGS of $720.00
  • Moving Average reported a COGS of $735.00
  • LIFO reported a COGS of $750.00

Move those averages to inFlow Cloud

Some businesses track their FIFO and LIFO costs on paper, but if you choose to use moving average, you’ll want software to help you keep all of the math manageable. Excel can work if you have a formula to keep rolling the totals from a previous cell, but that requires consistent fine tuning. inFlow handles all of these important, continuous calculations on the back-end so that you can focus on other aspects of your business.

But don’t worry: inFlow Cloud’s built in cost history makes it easy to view a product and see exactly how its unit cost has changed over time, and you can manually adjust costs even while using the moving average method.

Try inFlow Cloud free

No credit card required. Sign up now!

Thomas Wong

About Thomas Wong

Thomas is a 100% human being who divides his time between writing medium-sized articles with his keyboard and taking large photographs with his camera.

Leave a Reply to Dan Cancel reply

  1. Hi! I’m going to use average moving method. Could you advise,how to calculate average cost if i have sales first than purchases?

    1. Hi Liza,

      This sounds like a situation where you are back-ordered or you have to specially order an item in. I think you would need to wait for the purchase order (PO) to arrive, and that would help you to establish your cost.

      You can still use the moving average formula provided in the article, but with a slight modification.

      Our original formula above tries to factor in existing stock, but you can’t do that in your example, because you sold your item on a sales order (SO) without having any in stock. You would have to create a new PO to actually get that product in so that your customer could take it.

      So in cases where you have 0 or negative stock, I’d modify the formula to apply to your very next PO only:

      Average unit cost = [Total cost of PO / Total qty ordered on PO]

      Once you get back to having a positive amount of stock (over 0), you can go back to using the formula in the article.

      – Thomas

  2. Can similar items be reasonably grouped? For example if I have 2-3 types of necklace cords I use for making/selling pendants can I group these under “Necklace Cords” with an averaged price? I do many small batch and one-off projects and tend to buy a lot of different raw materials but in a fairly small number of categories.

    1. Hi Dan,

      If I understand that correctly, groiuping 2–3 different pendants would be calculating cost for a category or group. It’s fine to do that if they’re very similar costs and prices, but it’s not the same thing as identifying the moving average cost for one particular kind of pendant.

  3. What about if I have incoming debits and credits and I need to find the latest value?

    For example:

    Description AMOUNT QTY
    Credit -255.15 -1.000000
    Credit -255.15 -1.000000
    Credit -255.15 -1.000000
    Credit -255.15 -1.000000
    Debit 289.80 1.000000
    Debit 331.80 1.000000
    Debit 331.80 1.000000
    Debit 67.20 1.000000
    Debit 22.22 1.000000

    Assuming last transaction is latest date, would MAC be Sum of amount / sum of qty?

    1. Hi BB, in the case of calculating cost based on moving average, we’d only use the quantities and the credits (assuming these represent paying for the product). Any debits from sales of the product would not affect the cost.
      Since all your credits in that example were -225.15, then the cost per unit is simply 225.15.
      However, since there were only 4 credits and 5 debits, that leaves your stock in an overall quantity of -1.
      So you’d need one additional credit in order for any system to properly calculate the cost.