Building a Rolling Forecast in Hyperion Planning

In a time of economic uncertainty, businesses are looking for more ways to effectively plan and adapt to ever changing business climates. One more commonly occurring practice to combat this uncertainty is the implementation of a rolling forecast. A periodic “outlook” or “re-forecast” makes a lot of sense when, in a lot of ways, your annual budget essentially becomes outdated once the first month or quarter of actuals become available, maybe even before that…. The re-forecast allows you to update numbers based on more current information and assumptions, and thus, should give you the ability to make more informed decisions.

Generally, a rolling forecast is a new, forward-looking plan, based on the most current actual data. These forecasts are not done to the same level of detail as the annual budget, so the time to prepare them should be a bit shorter, as a result.

I’ve experienced both monthly and quarterly rolling forecasts. Some may say that re-forecasting on a monthly basis requires too much effort, while others may say that forecasting on a quarterly basis doesn’t give them timely enough information. My opinion would be that it depends on the business. If your industry is highly volatile and the environment changes frequently, then a monthly re-forecast might be a fit for you. However, if the business that you’re in is fairly stable and follows a cyclical path, quarterly forecasts may be all you need.

Building a rolling forecast in Hyperion Planning is a fairly common practice. As a user of Planning, that would affect both your data inputs (re-forecast of future periods) and your reporting (mix of actual months and forecast). As an application administrator, it’s a bit more complex as you would need to provide a means for dynamic dimension member updates, as well as create calculations that address the rolling functionality.

The first item to take care of is making sure the appropriate substitution variables are set up in Essbase. A substitution variable is a placeholder for members that change regularly. For example, you can set up variables for the current month and current year. Then, as those values change, you make the update in one location instead of having to go to every input form and every report to make the updates. In the case of a rolling forecast, this makes application maintenance much less time consuming.

The next item to address is the web data input forms. What I generally do is put the Year dimension in the page drop-down and all twelve months in the columns. This allows a user to toggle from one year to the next. A key to this design is to set the forecast scenario to the current period and year range so that only the forward-looking periods are open for input. One drawback to this method is that you don’t get a true rolling effect with actual periods rolling up to forecast. As a work-around, you could:

• Include the scenario dimension in the page with forecast and actual as choices.
• Create a composite web form with the second grid containing actual data.
• Create a custom menu link to a rolling forecast report.

Creating rolling forecast reports can get a little tricky, but that’s mostly due to the fact that they can accommodate more dynamic member selections compared to web forms. The column set up is the most complex portion as you have a mix of actual and forecast data, which oftentimes crosses multiple years. The report design will also be dependent on the start month for the forecast and how far out the forecast goes.

For a year-over-year forecast, you will essentially have multiple sets of columns which display or suppress depending on certain criteria, like whether or not the forecast start and current period are in the same year, for example. I generally use a substitution variable to make the criteria determination. Because of the many options in terms of forecast length and type, I won't go into detail on how to design the columns for these types of reports.

If your forecast is all contained within the same year, then one column would range from January to the current month variable and the second column would range from the current month variable offset by 1 to December.

Finally, you have calculations that need to be updated to accommodate the rolling forecast. Depending on how any trending you have is formulated, these calculations may need to incorporate cross dimensional operators to the appropriate scenario (actual, forecast). Also, when the calculations roll year-over-year, the formulas will need to appropriately account for the change from one year to the next. Usage of substitution variables is also extremely helpful here and is definitely a best practice.

When all is said and done, you should have multiple buckets for each of your planning processes. For example, you most likely would have a set of Annual Budget web forms and reports with their corresponding set of folders. You would also have a set of budget calculations. If you implemented a rolling forecast, that process would then have it’s own set of items as well. From an organization standpoint, it’s cleaner and it also makes it easier for end users to sift through.

In summary, this is generally the way I handle rolling forecasts in Hyperion Planning. Is it the only way to do it – no. I’m sure there are many other options. But, I’ve found this to be an efficient and effective way to implement the solution. If there are other ideas, alternatives, or opinions out there, I’d love to hear them. As a wise man once told me, “The best thing about Essbase calculation scripts is that there are a hundred ways to write the same formula. But, the worst thing about Essbase calculation scripts is that there are a hundred ways to write the same formula!” I guess something similar could be said about implementing rolling forecasts....