Average Cost. Elegant and Simple… By Mohammed Parak
I woke up this morning with a revelation on Average Cost. While I have been playing with numbers in the retail sector for one and a half decade, what came to me this morning convinced me that there are huge gaps in my knowledge.
We have traditionally always chosen “last cost” as the indicator of the cost of an item. It worked well in establishing the most accurate current reckoning of the total value of ones stock holding. The fact that it is the most recent value meant that it was also the closest thing to replacement cost. What constantly irked our clients was that in a market where they occasionally buy goods cheap the last cost often overstated the entire stock holding by the current high, or understated it the current cost was lower, as the case may be.
Clients often stated that they received new items at a higher value, but existing stock was purchased at a lower value or visa versa. The Last cost method Inflated, or deflated the value of the stockholding.
We at CAD RETAIL evolved a line of faulty logic where we told clients that the “last cost” was actually the “evolutionary practical average cost”. As new prices could theoretically go higher, or lower, we spun the theory that that the difference between the overstating stock and undervaluing stock would “come out in the wash”. In reality prices generally only went one way and on that basis the yarn could easily come undone. Surprisingly, no clients called us out on this one. Possibly because they did not want to burst our bubble of being so apparently knowledgeable in matters “retail”.
Anybody who defended the last cost method was in my book hiding his inability to vouch for the quantity on hand. To explain this is to understand that average cost is calculated by taking the value of existing stock (Units on hand X “Past Cost”) and adding it to the value of current purchase ie: (Units purchased X “Todays cost”). Add these two and divide by the total number of units and you will get the “average cost”. Companies that have little regard for the accuracy of the units on hand, are skeptical of their ability to accurately calculate this value. If the on_hand is wrong, or worse still “negative” then the resulting average would be catastrophic. The on_hand figure may even be correct in the system, but incorrect as a result of theft, resulting in the same distortions.
This logic, I find is the prevailing logic amongst all retailers and software vendors. This deduction is not, as my recent revelation reveals, entirely accurate. It is possible to track average cost in two other ways that could still be accurate, even if the stock on hand were compromised.
Before I go on I may add that ironically, this thought only occurred to me at a time when we resolved all technical difficulties with accurately tracking stock on hand. These arguments would have been infinitely more useful to us in the past, when we had inaccuracies to contend with.
The First method, I call “Historical Purchase Average” .
In this method one simply takes the cumulative past Purchase value (total excl) and divides by cumulative quantity purchased. The resulting figure is truly the most accurate average cost in the business, and the current state of the system quantity on hand and the actual number that remain on the shelves are insignificant to this calculation. I am amazed that in all of the years that I have dealt with this issue that something so simple eluded me and the “super brains” that it has been my pleasure and privilege to work with.
The second method I call simply “Period Average”
In this method we simply take all the various “unit” costs between any given period and run the classic AVG function on them to derive the average. Note that this method uses individual costs of individual items, not the total value derived from cost times quantity. This could give a different figure based on the size of the period in question. More recent averages may be higher that older averages. This would indeed be an interesting average to consider, as the purchase period would be used to derive the result.
Clearly one could use other methods in calculating the Historical Purchase Average and instead of using cumulative figures, one could use purchase information for a given period and calculate Historical purchase average, on a targeted cross section by date. Finally on could take the unit cost from day one and use the AVG function to achieve the resulting average.
Which of these is a more accurate average? The question should rather be what do we want to do with the cost? If one needs to assess the value of your stock holding then clearly the accuracy of the stock on hand one of the two key factors. Cost and stock on hand. If either one of these is inaccurate then the resulting valuation would be flawed. The client needs to embrace one or other of the cost values, ie replacement (Net realizable value), last cost, or the various type of averages, and the problem is easily solved.
For the proposes of the profitability of a sale, the quantity on hand is not significant. Which ever cost calculation method the client embraces, is simply cast against the quantity sold and assessed against the selling price. (excluding VAT).
This may help explain why many retailers ponder why they appear to make a good GP (Gross Profitability) on a daily basis but appear to be making a loss in their financials. The inaccuracy of the stock takes, and stock on hand figures may skew the fiancials, but not the apparent profitability of sales.
We have traditionally always chosen “last cost” as the indicator of the cost of an item. It worked well in establishing the most accurate current reckoning of the total value of ones stock holding. The fact that it is the most recent value meant that it was also the closest thing to replacement cost. What constantly irked our clients was that in a market where they occasionally buy goods cheap the last cost often overstated the entire stock holding by the current high, or understated it the current cost was lower, as the case may be.
Clients often stated that they received new items at a higher value, but existing stock was purchased at a lower value or visa versa. The Last cost method Inflated, or deflated the value of the stockholding.
We at CAD RETAIL evolved a line of faulty logic where we told clients that the “last cost” was actually the “evolutionary practical average cost”. As new prices could theoretically go higher, or lower, we spun the theory that that the difference between the overstating stock and undervaluing stock would “come out in the wash”. In reality prices generally only went one way and on that basis the yarn could easily come undone. Surprisingly, no clients called us out on this one. Possibly because they did not want to burst our bubble of being so apparently knowledgeable in matters “retail”.
Anybody who defended the last cost method was in my book hiding his inability to vouch for the quantity on hand. To explain this is to understand that average cost is calculated by taking the value of existing stock (Units on hand X “Past Cost”) and adding it to the value of current purchase ie: (Units purchased X “Todays cost”). Add these two and divide by the total number of units and you will get the “average cost”. Companies that have little regard for the accuracy of the units on hand, are skeptical of their ability to accurately calculate this value. If the on_hand is wrong, or worse still “negative” then the resulting average would be catastrophic. The on_hand figure may even be correct in the system, but incorrect as a result of theft, resulting in the same distortions.
This logic, I find is the prevailing logic amongst all retailers and software vendors. This deduction is not, as my recent revelation reveals, entirely accurate. It is possible to track average cost in two other ways that could still be accurate, even if the stock on hand were compromised.
Before I go on I may add that ironically, this thought only occurred to me at a time when we resolved all technical difficulties with accurately tracking stock on hand. These arguments would have been infinitely more useful to us in the past, when we had inaccuracies to contend with.
The First method, I call “Historical Purchase Average” .
In this method one simply takes the cumulative past Purchase value (total excl) and divides by cumulative quantity purchased. The resulting figure is truly the most accurate average cost in the business, and the current state of the system quantity on hand and the actual number that remain on the shelves are insignificant to this calculation. I am amazed that in all of the years that I have dealt with this issue that something so simple eluded me and the “super brains” that it has been my pleasure and privilege to work with.
The second method I call simply “Period Average”
In this method we simply take all the various “unit” costs between any given period and run the classic AVG function on them to derive the average. Note that this method uses individual costs of individual items, not the total value derived from cost times quantity. This could give a different figure based on the size of the period in question. More recent averages may be higher that older averages. This would indeed be an interesting average to consider, as the purchase period would be used to derive the result.
Clearly one could use other methods in calculating the Historical Purchase Average and instead of using cumulative figures, one could use purchase information for a given period and calculate Historical purchase average, on a targeted cross section by date. Finally on could take the unit cost from day one and use the AVG function to achieve the resulting average.
Which of these is a more accurate average? The question should rather be what do we want to do with the cost? If one needs to assess the value of your stock holding then clearly the accuracy of the stock on hand one of the two key factors. Cost and stock on hand. If either one of these is inaccurate then the resulting valuation would be flawed. The client needs to embrace one or other of the cost values, ie replacement (Net realizable value), last cost, or the various type of averages, and the problem is easily solved.
For the proposes of the profitability of a sale, the quantity on hand is not significant. Which ever cost calculation method the client embraces, is simply cast against the quantity sold and assessed against the selling price. (excluding VAT).
This may help explain why many retailers ponder why they appear to make a good GP (Gross Profitability) on a daily basis but appear to be making a loss in their financials. The inaccuracy of the stock takes, and stock on hand figures may skew the fiancials, but not the apparent profitability of sales.
Comments