Moving average formula for calculating cost
The moving average formula is a solid choice for ensuring your costs are always up to date. Costing methods are 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 already talked about FIFO (First in first out) and LIFO (Last in first out) costing methods in other articles, so it’s now time to discuss a third option.
Using a moving average formula will generate a product cost that falls between what FIFO and LIFO would report, which can be very useful if if it isn’t possible or feasible to deal with multiple costing layers.
The moving average formula 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.
FIFO used January and February’s orders to derive COGS because it took 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. Multiple cost layers can make it difficult to calculate profit or COGS, especially when a sales order ends up using stock from different purchase orders (with different 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 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:
The big advantage of the moving average 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. You’ll only have one unit cost to worry about because you’ll always recalculate unit costs after every PO. This makes it much simpler to track the actual cost of goods sold, regardless of whether you sell 10 or 100 products on an order.
Now let’s go back to the sales orders of 70 units of Zealot lenses from earlier, but this time we’ll calculate the COGS with moving average:
Using the moving average formula to derive COGS from Zealot lens sales, you can see 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
It’s important to remember that the cost of goods sold will not be identical when comparing FIFO vs. LIFO vs. moving average in a particular period of time, like a financial year. This can affect how much revenue that your business ends up reporting, which affects how much tax you end up paying. So it’s really important to find out which costing method is right for your business and then stick to it.
inFlow Cloud makes moving average costing easy
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.
We think this method of cost tracking works very well for a lot of small businesses, so we’ve made it the default costing method in our own software (although you can change this). If you just start using inFlow it will automatically use the moving average formula so that you can see how unit cost has changed over time.