0 Replies Latest reply: Aug 30, 2016 8:40 AM by Karunpreet Soni

# Demand Supply QOH and Shortage Problem

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