1 Reply Latest reply on Oct 19, 2016 2:48 AM by Sreekanth Kasaraneni

    Date Calculations - MoM, Budget Growth, and Forecast

    Quincy Osborne

      Hi,

       

      I currently have a list of actuals and budget by state and product. I would like to get the previous year month over month %, budget growth %, average %, and forecast next month's actuals based on the average %.

       

      Previous Year MoM %: (current year month - previous year same month)/current year month

      Budget Growth %: (Budget Month - previous budget month)/Budget Month

      Average %:Average(Previous Year MoM % + Budget Growth %)

      Next Month's Forecast: Actual Sales *(Average % + 1)

       

      The detail sheet would look like the below.

                

      StateTypeDate KeyActual SalesPrevious Year MonthPrevious Year MoM%BudgetBudget Growth %Average %Next Month's Forecast
      GeorgiaTables20150831244
      GeorgiaTables20150930245
      GeorgiaTables20151031246
      GeorgiaTables20151130248
      GeorgiaTables20151231242
      GeorgiaTables20160131243
      GeorgiaTables20160831239244-2%240
      GeorgiaTables20160930239245-3%2472.83%0.2%239.3866397
      GeorgiaChairs20150831307
      GeorgiaChairs20150930308
      GeorgiaChairs20151130310
      GeorgiaChairs20151231303
      GeorgiaChairs20160131304
      GeorgiaChairs20160831299307-3%3,907
      GeorgiaChairs20160930299308-3%3,9200.34%-1.3%295.0032461
      FloridaTables20150831856
      FloridaTables20151231839
      FloridaTables20160131847
      FloridaTables20160831836856-2%8,857
      FloridaTables20160930816845-4%8,8600.04%-1.8%801.6559402

       

       

      This would be a summary sheet.

           

      StateTypeCurrent Month to dateForecastPerentage of Forecast
      GeorgiaTables18223976%
      GeorgiaChairs25029585%
      FloridaTables20080125%

       

      I have tried using table calculations but with no luck.

       

       

      Thanks