Before we deal with how the date filters affect the Below MSL screen, I will explain the calculations behind the supply, demand and shortage figures.
Assuming we are considering ACTIVE and QUARANTINE,
SUPPLY = Active + Quarantine + On Order (PO) + On Order (WO) (If not, SUPPLY = On Order (PO) + On Order (WO))
DEMAND = On Order (SO) + Required For WO + MSL
SHORTAGE = Demand - Supply (or if based on MSL only SHORTAGE = MSL - Supply)
Fairly straightforward so far, but if you then introduce date filters then these figures can change depending on the criteria specified. The following figures, instead of being lifted from the inventory table, are recalculated based on the filter:
On Order (PO) is replaced in the supply calculation with the outstanding PO qty on purchase order lines where the line required date meets the date filter criteria;
On Order (WO) is replaced in the supply calculation with the outstanding WO qty where the WO scheduled finish date meets the date filter criteria;
On Order (SO) is replaced in the demand calculation with the outstanding SO qty where the SO line required date meets the date filter criteria;
And finally, Required for WO is replaced in the demand calculation with the sum of the WO requirements where the WO that the part is required for has a scheduled start date that meets the date filter criteria.
As you might expect, these changes can have a significant effect on the shortage. For example, if you enter a filter where Date Required = 01/04/2011, the On Order (PO) part of supply would include ONLY PO’s with a required date of 01/04/2011 and On Order (WO) part of supply would ONLY include WO’s with a scheduled finish date of 01/04/2011, and so on.
To try to illustrate this, I’ve run through an example for part no SW0122 on my test database. On here I have entered the following orders for this part number:
PO for SW0122, qty 10, required date 01/04/2011
PO for SW0122, qty 15, required date 01/05/2011
SO for SW0122, qty 250, required date 28/04/2011
WO for SW0122, qty 5, scheduled start date 20/04/2011, scheduled finish date 21/04/2011
WO for SW0111, qty 5, requirement of 5 x SW0122, scheduled start date 20/05/2011, scheduled finish date 31/05/2011
For simplicity, I have no active or quarantine stock and the MSL for SW0122 is 0 as well.
First of all if we look at the part details/stock status tab, we see a shortage of 225, which comes from supply of 30 (25 on order (PO) plus 5 on Order (WO)) and a demand of 255 (250 on order (SO) plus 5 required for WO).
Then, if we look at the Below MSL screen without any date filters, we see the same shortage, as you would expect.
Now we introduce a date filter, specifying that Date Required is between 31/03/2011 and 07/04/2011.
As you can see, the part has actually dropped off the shortage list. The reason for this can be explained if you check how the calculations change and look at the order dates that are on the system. In this instance, Supply drops from 30 to 10, as only the PO for 10 has a date that falls into the specified date range. However, Demand also falls in this case from 255 to 0, as the SO requirement and the WO requirement are both out-with the date range as well, so for this date range there is actually a surplus of 10.
Finally, if we change the date range again, this time to have the required date between 31/03/2011 and 30/04/2011, the figures will change again. Supply will now be 15 (the PO for 10 required on 01/04/2011 plus the 5 that will come in from the WO scheduled to finish on 21/04/2011), and demand is 250 (from the SO required on 28/04/2011), so the shortage is now 235.
So, as you can see from this simple example, without changing any of the stock levels or orders on the system, we have been able to get 3 different shortage figures for the part, not because there is anything wrong with the calculations, but simply because, depending on the date filters entered, certain elements of the supply and demand become relevant or irrelevant to a particular calculation.