2 Replies Latest reply: Feb 28, 2013 11:37 AM by Marcus Ellinghausen RSS

    Inventory Forecast

      Hi,

      I am trying to build an inventory forecast app. This will give me the ability to look at the forecasted level of inventory by looking at the MRP system's demand and supply as well as the current on hand qty.

       

      I have attached 2 spreadsheets (that in my example act as data sources). One giving part_no and On hand qty as of today. the other spreadsheet gives a demand and supply schedule for different parts along with the date when MRP system thinks the transaction will be carried out.

       

      What I am trying to build is that if I select a month in future (lets say May-2013) - the table gives me the following:

      PART and the forecasted qty which is defined as:

       

      Today's On Hand Qty + Cumulative Supply (till the selected date in my example May-13) - Cumulative Demand (till May-13)

       

      I have tried a lot of examples but am struggling to link the two tables and aggregate the forecasted qty. I want to avoid calculations on the charts because of the data volumes I will be dealing with.

       

      Any help will be much appreciated.

      Please reply to this with the questions.

       

      Thanks in advance.

      Regards,

       

      Keshav

        • Re: Inventory Forecast
          Marcus Ellinghausen

          Hello,

           

          to link the both table you must do so

           

          LOAD

          PART_NO,
               ON_HAND_QTY
          FROM
          C:\temp\Unterlagen\ON_HAND_QTY.xlsx
          (ooxml, embedded labels, table is Sheet1);

           

          LOAD

          DATE_REQUIRED,
               [PART NUMBER] as PART_NO,
               QTY_SUPPLY,
               QTY_DEMAND
          FROM
          C:\temp\Unterlagen\SUPPLY_DEMAND.xlsx
          (ooxml, embedded labels, table is Sheet1);

           

          Marcus

           

           

           

           

           

           

           

           

            • Re: Inventory Forecast

              Hi Marcus,

              Thanks for the reply, but that wasnt what I was looking for.

               

              If you read the description again - I am looking to get a cumulative Supply - Cumulative of Demand + Current On hand quantity depending on the date selection. - so It ignores any demand or supply after the selected date and cumulates everything before that and adds current on hand qty.

               

              Regards,

              Keshav