Demand Filter

Let’s say that you are a product manager in a very large company and you have thousands of product lines in your portfolio. Let’s assume that most the demand for your products are volatile, which means demand for products fluctuate a lot.

 (I will cover in my next blog how to find, mathematically, if a product has volatile demand)

Based on above assumption, don’t you want to have a mechanism that would instantly point out those items that have had sudden spike recently?   Demand filter calculation can be very useful to managers who are interested in knowing those items that saw sudden spike in demand last month or demand for items went significantly down recently. In other words demand filter can be used to find those items that have had demand that was more than 3 standard deviations high or less from the historical average.

Preparation:-

1. In order to calculate demand filter you will need historical data. Based on historical data we can determine if recent month demand was very high, very low, or on average. (Larger the historical data points better the demand filter calculation is)

  

2. Next step is to determine exception. An exception can be anything more than 3 DF. It means that if Demand filter for an item is more than 3 then manager needs to identify the cause for the deviation of demand from the historical average. For our example, we use 3.1 as DF exception.

Formula:-

DF = (Absolute difference between the last data point and average of previous months excluding last data point) / Standard deviation of previous months excluding last data point.

In the above example we are calculating demand filter for Product A using its demand for last 30 months.
First we calculate average for previous months starting from month 1 to month 29. Note that we are not including month 30 in our average calculation.
Similarly, we calculate standard deviation of demand for last 29 months excluding most recent data point.
Once Average and Standard deviation is calculated, we use most recent data point quantity to subtract from AVERAGE.  ABS (640812.53-1345679) = 704866
then we divide difference by Standard deviation. = (704866/166165) =4.24
In the example above 4.24 is the Demand filter value, which is greater than our exception 3.1. It means that this item has more than 3 standard deviations from the historical average.

The next step for manager is to find out the root cause of sudden spike in demand and act accordingly.

You can use SQL analytical function to calculate Demand filter against any database engine. For this example we will use Teradata to calculate demand filter.

SELECT a.Product, a.TYPE,  ABS(b.ACTUAL-a.AVGACTUAL)/(nullifzero(a.STDEVACTUAL) (DEcimal(15,3) )) as DF  /* Demand Filter */
 from
 ( Select DISTINCT
Product,
FLAG,
TYPE, 
STDDEV_SAMP(ACTUAL) over  (Partition by FLAG) as STDEVACTUAL,  /* Standard Deviation */
AVG(ACTUAL) over (Partition by FLAG) as AVGACTUAL /* Average */
from
( Select
Product, FLAG, TYPE, SDATE, ACTUAL,
SUM(ACTUAL) over (partition by FLAG order by SDATE ASC rows unbounded preceding) as Cusum  /*  Running Total */
 from
( SELECT
Product,
Trim(Product)||Trim(TYPE) as FLAG,
 TYPE,
DT  SDATE,
SUM(ACTUAL) ACTUAL
FROM ( SELECT Product, TYPE, DT, DMD_PLNG_QTY ACTUAL, MAX(DT) OVER (PArtition by Product) max_DT from DEMAND_TABLE /* Find most recent month */
 ) A
WHERE
 SDATE >= add_months((MAX_DT),-29)  /* Last 29 months  */
AND SDATE < MAX_DT /* Excluding most  recent month */
Group by 1,2,3,4
  ) a ) A
WHERE CUSUM > 0  /*  Excluding  leading zero . (Optional)*/
) a
inner join ( SELECT Trim(Product)||Trim(TYPE) AS FLAG , SUM(DMD_PLNG_QTY) ACTUAL  /* Quantity from  most recent data point */
from
DEMAND_TABLE
AND DT = (SELECT MAX(DT) from DEMAND_TABLE)
group by 1
)  B on A.FLAG=B.FLAG ;

Note:- In our SQL we have used running total flag. Running total flag can be useful if you have many items that are recently introduced to market. Since recently introduced items will not have enough historical data points, we should consider excluding those data points from the calculation. In order words, if an item was introduced 12 months ago then in our calculation we should use only 11 months historical data rather than all 29 points. In such scenario running total flag comes handy.