Hi All,
I am facing problem in predicting Quantity on Hand and Shortage for the following Data Set:
| Month | Year | Month_Year | Demand | Supply | QOH | Shortage |
| Jul | 2016 | Jul-16 | 12 | 12 |
| Aug | 2016 | Aug-16 | 13 | 12 |
| Sep | 2016 | Sep-16 | 46 | 90 | 34 | -78 |
| Oct | 2016 | Oct-16 | 46 | 35 | 0 | 0 |
| Nov | 2016 | Nov-16 | 90 | 19 | 0 | 0 |
| Dec | 2016 | Dec-16 | 112 | 72 | 0 | 0 |
| Jan | 2017 | Jan-17 | 5 | 75 | 0 | 0 |
As u can see that From Oct-2016 to Jan 2017 QOH and Storage is 0. I need to predict them with the help of Sep-2106 data.
Shortage = Demand - (Supply + QOH)
For example ,
As you all can see that for Sep 2016
Demand = 46
Supply = 90
QOH = 34
Shortage = 46 - (90+34) = -78
Now For Oct 2016,
Demand = 46
Supply = 35
QOH = 78(which was the QOH in Sep)- (46-35) =67
Shortage = 46-(35+67) = -56
Similarly for he Other Months
I am able to achieve it in Excel and the final Output is
| Month | Year | Month_Year | Demand | Supply | QOH | Shortage |
| Jul | 2016 | Jul-16 | 12 | 12 |
| Aug | 2016 | Aug-16 | 13 | 12 |
| Sep | 2016 | Sep-16 | 46 | 90 | 34 | -78 |
| Oct | 2016 | Oct-16 | 46 | 35 | 67 | -56 |
| Nov | 2016 | Nov-16 | 90 | 19 | 0 | 15 |
| Dec | 2016 | Dec-16 | 112 | 72 | 0 | 55 |
| Jan | 2017 | Jan-17 | 5 | 75 | 15 | -85 |
For QOH , I have used the following formula in Excel
=IF(-H17-(E18-F18)<0,0,-H17-(E18-F18))
and for Shortage
=IF(G18=0,H17+(E18-F18),E18-F18-G18)
I am not able to replicate the same in QV.
Please can any one help me to achieve this.
Its a bit of urgent 😐
Thanking all in anticipation
Regards
KP
