8 Replies Latest reply on May 15, 2018 10:32 AM by Zach DeMascole

    Table with Inventory Balances at Points in Time

    Zach DeMascole

      Hello everyone,

       

      With your help, some of the things I have been able to compute are:

      - Month/Year of the most recent actuals

      - Month/Year of the second most recent actuals

      - Month/Year of the prior year-end actuals

       

      I now am trying to calculate:

      - Ending inventory balance for the Month/Year of the most recent actuals

      - Ending inventory balance for the Month/Year of the second most recent actuals

      - Ending inventory balance for the Month/Year of the prior year-end actuals

       

      Does anyone have any ideas how to do this?

       

      My sample data set has actuals from November 2017 through April 2018 and forecast data for the future.

      What I am trying to get is a table like this (based on the sample data):

       

      TimeInventory Balance
      Most Recent Actuals$1,500
      Second Most Recent Actuals$1,800
      Prior Year-End Actuals$600

       

      I thought it would be easy, but I am struggling.

       

      I have attached a workbook that includes the formulas I have for computing Month/Year of most recent actuals, second most recent actuals, and prior year-end actuals.

      I have also attached the sample data.

       

      Thanks for all of your help!!