Advice how to structure rolling forecast formula with time element
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!
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!1