6 Replies Latest reply on Apr 14, 2017 1:15 PM by Brian Biggs

    Forecasting Inventory Balances

    Brian Biggs

      Hello Tableau Community!

       

      First (well overdue) post!

       

      I'm struggling with forecasting inventory values. Details:

      • I need to present my view by either Week or Quarter and have set up a parameter to allow that. Since we only care about ending inventory values, I've used Bethany Lyon's classic post to create an LOD expression for that. IF [Rolling Week] = { INCLUDE : MAX([Rolling Week]) } THEN [Inventory - Actual]
      • The formula to project inventory balances in the future is to take the last know inventory balance, subtract forecasted sales for the current period (Sales Act/Forecast) and add new incoming Inventory (Inventory - In.) I used Shinichiro Murakami 's post (Re: Running Total to Determine Inventory at Point in Time ) for this calculation and it works great when I view by week but it breaks down when viewing by quarter.

      Here you can see it correctly calculating by week the ending inventory of 150

      Week.png

       

      And here you can see the issue when I try by quarter. It used the last known actual inventory value in FY16Q4 which is incorrect. I need it to use all available actual inventory values in FY17Q1 before starting to use a forecast calc.

      Quarter.png

      I can use a calculated field to capture "Sales Act/Forecast" and "Inventory - In" only when Rolling Quarter is > 0. But how do I retrieve the last known "Inventory - Actual" when Week / Rolling Week is not in the view?

      Version 10.0.7 workbook attached.

       

      Thanks.

        • 1. Re: Forecasting Inventory Balances
          Shinichiro Murakami

          Hi Brian,

           

          Thank you for finding old post.

          I hope I understand you issue correctly.

          You want to show the last week of Quarter when you pick "Quarter" on parameter.

           

          To achieve this, you can filter with customized index.

           

          [index Filter]

          if [Quarters or Weeks]="Weeks" then 0 else last() end

           

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Forecasting Inventory Balances
            Brian Biggs

            Thanks Shinichiro . That's a very clever approach. You're using a customized index to drive a Table Calc filter which removes all but the last week of the quarter from the view but continues to make them available to the calcs so they work correctly. Great option.

             

            What I didn't share is that I'm using a parameter in my actual worksheet to allow users to switch between measures that need to be summed (like Sales) and this inventory value. If I go with your approach, I guess I could just set up Sales as a Running_Sum table calc. That way, the ending week of the each quarter would show the entire quarter's sales. I think that works.

             

            I wonder if there Is there a way to accommodate this using LOD expressions? Using INCLUDE to bring Week into the view so that the calc performs correctly?

            • 3. Re: Forecasting Inventory Balances
              Shinichiro Murakami

              Brian,

               

              Nop.

              Normal filter filters data itself which easily mess up table calculations like this case.

              Using LOD and table calculation at the same time is quite difficult to handle.

               

               

              Thanks,

              Shin

              • 4. Re: Forecasting Inventory Balances
                Shinichiro Murakami

                After further consideration and investigation, I found the way with LOD.

                 

                The problem was field of "Fiscal Year/Quarter/Week"

                 

                Was

                [Fiscal Year/Quarter] + "W" + STR([Week in Fiscal Quarter])

                 

                should be

                [Fiscal Year/Quarter] + "W" + right(STR(100+[Week in Fiscal Quarter]),2).

                 

                problem of "was" is "max" always brings "9".

                Because the the sort result is 1,10,11,2,3,4,5,6,7,8,9,

                Then should be remove that problem.

                 

                Then

                 

                [Date Selector (copy)]

                CASE [Quarters or Weeks]

                WHEN 'Quarters' THEN [Fiscal Year/Quarter]

                WHEN 'Weeks' THEN [Fiscal Year/Quarter/Week (copy)]

                END

                 

                [The Last week]

                {fixed [Fiscal Year/Quarter]:max([Fiscal Year/Quarter/Week (copy)])}

                 

                [LOD Filter]

                if [Quarters or Weeks]="Weeks"

                then "Show"

                elseif [Fiscal Year/Quarter/Week]=[The Last week] then "Show"

                else "Hide" end

                 

                Hide the "Hide" in  LOD filter.

                 

                 

                 

                Thanks,

                Shin

                1 of 1 people found this helpful
                • 5. Re: Forecasting Inventory Balances
                  Bora Beran

                  This is doable with LOD calcs without bringing week into the viz.

                   

                  The reason your calculations return different results when you remove week is that the way the calculation is written if it is a full quarter it gets the latest week's value but if it is not it sums up does a running sum over the weeks in that quarter starting from first week.

                   

                  In a partial quarter you need to take the latest week's known inventory then add expected additions to inventory and subtract expected sales. The rolling week calculations in the workbook always look for the last week in the quarter, when you have a partial quarter, this breaks.

                   

                  So as first step your rolling week comparison calculation should be made aware of partial quarters. You can do this by checking whether inventory value is null

                   

                  IF NOT ISNULL([Inventory - Actual])

                  THEN [Rolling Week]

                  ELSE -5000 END

                   

                  or in this workbook whether rolling week is greater than -1

                   

                  IF NOT [Rolling Week] > -1

                  THEN [Rolling Week]

                  ELSE -5000 END

                   

                  Here -5000 is a random number that will surely fail your comparison of rolling week values with max rolling week since it will always be smaller than max rolling week.

                   

                  Now if you use this calculated field in your ending inventory calculation

                   

                  IF [Modified Rolling Week] = { INCLUDE : MAX([Modified Rolling Week]) } THEN [Inventory - Actual] END

                   

                  This will give you the last known inventory in a given quarter regardless of partial or full quarter.

                   

                  And a minimally invasive modification to inventory -Act/Forecast calculation by just adding a separate section to handle Quarters without Weeks in the view

                   

                  IF MAX([Rolling Week]) <= -1

                     THEN SUM([Inventory - Ending])

                      ELSE IF [Quarters or Weeks] = 'Quarters' then

                          SUM([Inventory - Ending]) + SUM(IF [Rolling Week] > -1 THEN [Inventory - In] - [Sales Act/Forecast] ELSE 0 END)

                  ELSE

                          PREVIOUS_VALUE(0) + SUM([Inventory - In]) - SUM([Sales Act/Forecast])

                  END

                  END

                   

                  This makes sure you don't double count incoming items and sales twice by counting them as zero and relying solely on known [inventory-ending] until the point [inventory-ending] is unknown and has to be estimated by adding [inventory-in]  - [sales forecast] to the last known inventory value.

                   

                  This way you will get the same results with or without weeks being in the view. Below is the result for quarters without weeks in the view. Not to mention getting the correct values for quarterly sales and inventory replenishments.

                   

                  Screen Shot 2017-04-14 at 1.16.12 AM.png

                  2 of 2 people found this helpful
                  • 6. Re: Forecasting Inventory Balances
                    Brian Biggs

                    Thank you for these great replies. Shinichiro Murakami - I like your new LOD approach but since it still requires Week to be in the view, I think Bora Beran's wins out.

                     

                    Bora - I had something like this in my head but I couldn't grok how to get the balances for Sales and Inventory - In only for future weeks. Thank you!

                     

                    For calculating the last known actual inventory value, I know I can always find it at Rolling Week = -1 so my streamlined Ending Inventory calc looks like this:

                    {FIXED : MIN(IF [Rolling Week]=-1 THEN [Inventory - Actual] ELSE NULL END)}

                     

                    I like your generalized solution and I will surely use that in the future.