Advice how to structure rolling forecast formula with time element

ChristinePowell
ChristinePowell
in Modeling

Hey-

I am looking to build on a formula in my rolling forecast. I am having trouble laying out the necessary steps.

 

Percentage Seasonality: Each period in a year (12) has an assigned rate. When I roll my forecast, I need the percentage to take the current forecast month rate / the sum of the remaining forecast periods' rates.

 

Any insight would be helpful. Thanks!

 

Tagged:

Answers

  • Misbah
    Misbah

    @ChristinePowell 

     

     

    Step 1 : Create SYS Time Module where you have Current Month stored

    Step 2: In your Calculation Module - Have three line items created

                L1 = Percentage Rate[LOOKUP: SYS Time.Current Month] 

                L2 = TIMESUM(Percentage Rate,12) - Note: This Line item will not have Time as a dimension. Also it is assuming that you need                        12 months rolling all the time

               L3 = L1/L2

     

    Hope that helps!

Categories