12 months rolling forecast

[Former Member]

Hi Guys

Can any body explain me best way to implement rolling 12 month forecast. To be more specific, lets say we have completed the Mar-04 then forecast should open up for April-04 to March-05. I need a solution which require minimum maintenance( like auto change of layouts implement). Quick response is appreciated.

[Former Member]

If you need quarterly data, you can manually set each monthly column, and then sum the 3 months to a quarter. I explained how to do this in your other thread.

Good luck.

[Former Member]

Frank,

You are right that approach works well for true 12 months rolling forecasts but a lot of people saying rolling forecast incorrectly uses it and actually mean 12 months of actuals and forecasts together. With 2 variable, you cannot know where is the switch from actual to plan and where from the comparable to regular os we had to work around with Quarter totals at the end.

[Former Member]

Raj,

It looks as if you meant 12 month rolling forecast going forward. Thus you are likely to require 2 variables:

1. Forecast Months: i.e. Jul-05 to Jun-06

2. Since FOX cannot use ranges easily, you might need another variable to give you the beginning of the forecast periods i.e. Jul-05 or the last actual period Jun-05 so you can use it to control the time loops in FOX.

In the layout, you would have the forecast fiscal month range on the level and dynamic on the 0fiscper or 0calmonth for the month depending on your data model...

Hope this helps,

Mary

[Former Member]

Hi Mary,

Thanks a lot for your response. I have following additional doubts regarding using of Fiscal Year/Period as Dynamic Data column in forecast layouts.

1. How should I get the automatic column headings. like Jan 2006, Feb 2006 from variable name. I am getting first value of variable name as the heading of column now.

2. How should I get quarterly totals when using Fiscal Year/Period as Dynamic data columns.

Looking forward for response from forum

Thanks,

Raj.

[Former Member]

1) put 0fiscper in the heading but your variables for the dynamic columns need to be set to the right type of text heading for that or you set it to characteristic value.

2) I could not get quarterly totals as

P1 P2 P3 Q1 P4 P5 P6 Q2 etc since the dynamic columns does not allow for BPS totals on quarterly in BPS layout definition but I use local Excel formula in layout definition in that at the last page of the layout definition, I count out 12 columns in the heading to figure out where the monthly amounts P12 ends and the following 4 columns I set up 4 Excel formula totals for sum(p1column:p3column), sum(p4column:p6column) etc. I tend to put the annual total after the 4 quarterly totals and specify the excel formulas as sum(p1column:p12column). If you want it even nicer since you don't know the number of rows of data, you would actually have something like:

IF(ISBLANK(A4),"",SUM(C4:E4))

Hope this helps,

Mary

Hope this helps.

[Former Member]

Create a variable for fiscal period

Fill in the values for the fiscal periods

e.g Aug 2006 to Jul 2007 rolling forecast will have 2 lines in the variable

008/2006 to 012/2006

001/2007 to 007/2007

This will work if you want to show Plan/Budget/Forecast data

If you need to show Actual data along with Plan/Budget/Forecast data, then you need to create another variable for actual montha and fill it with months 1 to 7.

Hope this helps.

NS

[Former Member]

Hi Raj,

Use dynamic data columns along with variables and offsets in the planning layout.

Dynamic data column setting is avaialble on the first screen of the layout builder under additional settings.

Cheers

Aneesh

[Former Member]

Hi Aneesh,

Can you please elaborate on the two different variable that I need to create like what should be value for them.

Thanks,

Raj.