Hi EDNA Experts,
Greetings…!!!
I am trying to project stock classification any time in the future based on Stock status, Sales projection and Ageing Criteria below,
| Group | Min.days | Max.days |
|---|---|---|
| Healthy | 0 | 180 |
| Slow Moving | 181 | 270 |
| Potential obsolete | 271 | 365 |
| obsolete | 366 | 100000 |
Below is sales projection,
| Product | Month | Sales Projection |
|---|---|---|
| Product 1 | Mar-21 | 100 |
| Product 1 | Apr-21 | 120 |
| Product 1 | May-21 | 300 |
| Product 1 | Jun-21 | 500 |
| Product 1 | Jul-21 | 400 |
| Product 1 | Aug-21 | 600 |
| Product 1 | Sep-21 | 300 |
| Product 1 | Oct-21 | 200 |
| Product 1 | Nov-21 | 600 |
Below is my current stock in hand,
| Plant | Product | Batch No | Current_month | Current Stock | Manufacturing date | Expiry date | Last Sellable date |
|---|---|---|---|---|---|---|---|
| A | Product 1 | AAA1 | 31-03-2021 | 200 | 01-01-2020 | 04-02-2021 | 04-02-2021 |
| A | Product 1 | AAA2 | 31-03-2021 | 300 | 01-02-2020 | 07-03-2021 | 07-03-2021 |
| A | Product 1 | AAA3 | 31-03-2021 | 800 | 23-02-2020 | 29-03-2021 | 29-03-2021 |
| A | Product 1 | AAA4 | 31-03-2021 | 1200 | 05-03-2020 | 09-04-2021 | 09-04-2021 |
| A | Product 1 | AAA5 | 31-03-2021 | 400 | 25-05-2020 | 29-06-2021 | 29-06-2021 |
| A | Product 1 | AAA6 | 31-03-2021 | 200 | 01-07-2020 | 05-08-2021 | 05-08-2021 |
| A | Product 1 | AAA7 | 31-03-2021 | 500 | 05-08-2020 | 09-09-2021 | 09-09-2021 |
| A | Product 1 | AAA8 | 31-03-2021 | 700 | 08-10-2020 | 12-11-2021 | 12-11-2021 |
| A | Product 1 | AAA9 | 31-03-2021 | 600 | 12-12-2020 | 16-01-2022 | 16-01-2022 |
| Total | 4900 |
I am looking for a PQ or DAX solution for this classification which will cover below needs,
1- Classify stocks based on Ageing table any time in the future (Ex. I should be able to see 6 months down how much stock will be under in each bucket by considering future sales projection and current stock in hand)
2- Projected Sales qty should be deducted from old stock in Potential Obsolete then in Slow moving and then last option from Healthy bucket to maintain First In First Out logic. (Obsolete and Expired stocks are not fit for sales).
Solution table as below,
| INVENTORY PROJECTION | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| Product | Month | Sales Projection (S) | YTD Sales Projection | Opening Stock (OS) | Ending Stock (ES) | Healthy (H) | Slow Moving (SM) | Potential Obsolete (PO) | Obsolete (O) |
| Product 1 | Mar-21 | 100 | 100 | 4900 | 4800 | 1300 | 500 | 500 | 2500 |
| Product 1 | Apr-21 | 120 | 220 | 4800 | 4680 | 600 | 1200 | 380 | 2500 |
| Product 1 | May-21 | 300 | 520 | 4680 | 4380 | 600 | 700 | 400 | 2680 |
| Product 1 | Jun-21 | 500 | 1020 | 4380 | 3880 | 0 | 1200 | 0 | 2680 |
| Product 1 | Jul-21 | 400 | 1420 | 3880 | 3480 | 0 | 600 | 200 | 2680 |
| Product 1 | Aug-21 | 600 | 2020 | 3480 | 2880 | 0 | 200 | 0 | 2680 |
It would be grateful If I get this solution from PQ/DAX.
I am attaching the excel file as well here for excel based sample working.
SLOB_PQ_DAX_PROBLEM STATEMENT.xlsx (19.8 KB)
Thanks in advance.
Regards
Jamie
