Calculating weighted average

In my last blog i talked about two different ways of calculating accuracy. And i also demonstrated how different approaches yield different results. On this blog we will discuss more about weighted average as i think this is one of the best ways of calculating accuracy.

" An average in which each quantity to be averaged is assigned a weight. These weightings determine the relative importance of each quantity on the average. Weightings are the equivalent of having that many like items with the same value involved in the average. " :- investopedia

Product	Actual Demand	Demand Plan	Difference	Accuracy
A	100	500	400
B	200	400	200
C	300	300	0
D	400	200	200
E	500	100	400
	1500	1500	1200	20.00%

In the above example the overall weighted average accuracy for all 5 products is 20 %. If you closely look in to the above table you will see that, reason for  overall accuracy to go down is due to product 'A' where difference between actual demand and demand plan is very high. Obviously when difference is high, accuracy will  be low.

Now, you can ask yourself, Why entire product family accuracy should suffer when very few items are performing bad ?  That is correct. Accuracy should not be as low as 20% just because one item did not do good. Now the question arise, what can be done to make accuracy calculation realistic ?

In real world scenario, accuracy should be anywhere between 0% to 100%. You cannot be 101% accurate or -300% inaccurate. It means that our calculation logic to calculate accuracy for product 'A' is not correct. Since accuracy should be between 0 to 100%, we should calculate accuracy for product 'A' in such a way that accuracy yields somewhere between 0 to 100%.

Product	Actual Demand	Demand Plan	Difference	Accuracy
A	100	500	400	-300%

In order to avoid unrealistic accuracy i prefer to use following logic.

IF(ABS(DFIFERECE) >ACTUAL DEMAND, ACTUAL DEMAND, ABS(DIFFERENCE))

It means that we will replace difference with actual demand quantity when ever difference is greater than actual demand. 

Product	Actual Demand	Demand Plan	Difference	Accuracy
A	100	500	100	0 %

Since we replaced difference with actual demand for product 'A', accuracy is now 0 %, which is within acceptable limit.

Now let's revisit our initial calculation and apply previously explained approach to product A. You should see over all weighted accuracy percentage to go up. 

Product	Actual Demand	Demand Plan	Difference	Accuracy
A	100	500	100
B	200	400	200
C	300	300	0
D	400	200	200
E	500	100	400
	1500	1500	900	40.00%

In means, realistically speaking, supply chain planner was 40% accurate in demand planning for his product line.

SQL to calculate weighted average will follow in my next blog....