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

    Demand Supply QOH and Shortage Problem

    Karunpreet Soni

      Hi All,

       

      I am facing problem in predicting Quantity on Hand and Shortage for the following Data Set:

       

         

      MonthYearMonth_YearDemandSupplyQOHShortage
      Jul2016Jul-161212
      Aug2016Aug-161312
      Sep2016Sep-16469034-78
      Oct2016Oct-16463500
      Nov2016Nov-16901900
      Dec2016Dec-161127200
      Jan2017Jan-1757500

       

       

      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

       

         

      MonthYearMonth_YearDemandSupplyQOHShortage
      Jul2016Jul-161212
      Aug2016Aug-161312
      Sep2016Sep-16469034-78
      Oct2016Oct-16463567-56
      Nov2016Nov-169019015
      Dec2016Dec-1611272055
      Jan2017Jan-1757515-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