Whenever there is a need to find the average SAM, It becomes quite clear that simple average doesn’t give us the right picture. This is because simple average divides the SAM equally between all the styles. This division is wrong because there will be some styles with bigger order Quantity and some styles with lesser order quantity and the effect of order quantity will not be reflected in the averaged SAM. Therefore we need to calculate weighted average SAM whenever there is need to find average.
This flaw of simple average is removed by weighted average.
So how can we calculate weighted average SAM or weighted average SMV?
- Multiply order quantity with respective SAM (or SMV) and sum it all.
- Now divide this sum by sum of order quantities.
That’s it, the resultant is Weighted Average SAM (or SMV).
How to calculate weighted average SAM in Excel ?
Excel makes this even easier, instead of multiplying order quantity and SMV one by one, SUMPRODUCT function can be used in excel to find the weighted average: