Let’s start by looking at why businesses need rolling forecasts. When running a business, we need to have a full view of what’s happening so that we can make the proper decisions. The best way to achieve this is to implement a budgeting and planning process.
We create an expected standard performance for the business and can then evaluate the actual results and take the necessary changes to course correct. We do this evaluation by performing a budget to actual variance analysis.
A rolling forecast is a planning tool that enables continuous planning over a set time horizon. If the business cycle is one year, and we prepare monthly reports, a rolling forecast will always show the next twelve months. After we close the January report, we add next January to the rolling forecast, so we still have a one-year plan ahead. We not only add another month to our budget, we also re-evaluate the remaining eleven months.
Rolling forecasts, or also known as rolling budgets, differ from the traditional annual budget, which is prepared only once per year and not updated.
Issues with traditional budgeting
The primary purpose of a regular budget is to clarify the allocation of resources and give feedback for strategic decisions. There are a few areas where traditional budgeting suffers:
- It does not react to actual performance;
- Departments have to mostly guesstimate for more than a year in advance, which means the budget is usually outdated as soon as management approves it;
- It encourages managers to adopt an ‘under promise and over deliver’ mentality because targets are often set based on the budget;
- Managers that have budget surplus close to the end of the period may decide to spend their allocated funds in full so that their budget will remain the same next year.
Rolling forecasts try to mitigate these shortcomings by introducing a regular re-evaluation of the estimates based on actual performance. Bringing the resource decisions closer to when the firm uses the resources improves the efficiency of the business.
This financial planning model estimates the future performance of a business over a continuous period based on historical data and drivers.
Rolling forecasts give managers a vision of the next twelve months at any given point, which helps to improve the decision-making process within the company. Frequent re-evaluation of targets allows managers to be more realistic. A rolling forecast cannot entirely replace a traditional budget which is still considered an essential guide for the long-term strategy. A re-forecast as a planning tool is harder to implement, as it is a loop, constantly changing based on actual real-time feedback and data. It requires more resources to be dedicated, compared to traditional budgeting. Also, rolling forecast models can quickly become too complex to track and manage in Excel. One way to mitigate that is to adopt a Corporate Performance Management (CPM) system.
We perform regular actual-to-budget variance analysis to evaluate the effectiveness and accuracy of our rolling forecast and planning process.
To make our switch from traditional budgeting a success, we need to break the link between forecast and reward. Whenever performance rewards are tied directly to the plan outcomes, we start to lose accuracy in the process. The company should set its targets on separate planning sessions, and we don’t update these with each re-forecasting cycle. It can become discouraging if the leadership increases targets when actual performance starts to close in on them.
Businesses usually set a time horizon of four to six quarters for their rolling forecasts, which they can update on a monthly or quarterly basis.
Performing a Rolling Forecast
When we start the process of implementing a rolling forecast model, there are a few general steps we need to take:
- Identify the objectives – it is crucial to define the goals of the forecast model, who will use it and for what purpose;
- Set a time horizon – how far into the future do we plan to look, what is the cycle, and is there any seasonality in the business;
- Determine the appropriate level of detail – forecasts for more extended time horizons are usually less detailed;
- Figure out the participants who have the needed objectivity and knowledge, how much time can they allocate to the process;
- Drivers – identify the value and cost drivers with the most significant impact on the performance of the business;
- Source data – what historical and estimated data is required, is the information available, how hard will it be to obtain, process and manage;
- Implement scenarios and sensitivity analysis – having more variants for the underlying assumptions will tremendously improve our decision-making process;
- Variance analysis – regular actual-to-budget comparison helps us to identify the sources of the potential variances and figure out the appropriate actions to take.
It is vital to have an in-depth understanding of the business and its cycle so that we can set the proper time horizon for our rolling forecast model.
It’s best practice to try to break down metrics to drivers. Doing so enables a better variance analysis and can point to the incorrect assumption. Then we can perform better analysis and take the appropriate actions. Look at the following example.
Here it is evident that breaking down sales into drivers is a much better option. If we only forecast based on a fixed growth assumption, we may end up missing an important consideration. When we switch the sales forecast to drivers, we can perform a more accurate estimation. By looking at sales price and sales volume separately, we remember to consider our maximum production capacity. Such omission is a bit of an extreme example, but it showcases the importance of using drivers when forecasting our most important metrics.
We can’t and shouldn’t forecast all line items with drivers. Analysts would predict less significant metrics and such where it’s hard to identify drivers based on historical data and general estimates.
Benefits of rolling forecasts
Switching from a traditional annual budget to a rolling forecast has some advantages:
- Allows the business to adapt timely to changes and reduce risk exposure;
- Improved financial planning which is regularly adjusted to reflect the reality of the company;
- Changes are considered in real-time as the forecast is timely updated;
- Helps senior management in strategic decision-making;
- Aids us to identify trends and adjust accordingly;
- Keeps track of the critical drivers for the performance of the business.
As the business grows and data becomes more complex, maintaining a rolling forecast gets more complicated. There are some aspects of the model which make it less appealing for companies:
- It is very time- and resource-consuming;
- Can be challenging to implement;
- Over time it becomes harder to track changes in drivers and assumptions.
Example Rolling Forecast Model
Note: The spreadsheets shown in the screenshots are mostly large, and it may be hard to read them in the confinement of this article. You can look at them at full screen or download the whole model below the article and follow along. As with all our models, it’s free to download and no registration is required.
Now, let’s look at an example of a rolling forecast. We are planning the sales of one product, a 360 digital camera. At first, we start with our projection for the next twelve months. For this example, we are not looking into how we arrived at these estimates, and we take this as our starting point.
One month passes, January 2020, and it’s time to input our actual performance for the product. We sold 2,750 pcs compared to 2,500 pcs planned. However, due to discounts and volume discounts, our average price was € 419.50 instead of the € 450.00 in our forecast.
At this point, we also add another period to our rolling forecast. I won’t go into too much detail on the formulas, and you can download the model and audit the way it works, and even use it for your purposes. I will explain how we add our rolling period. We check if there’s actual data entered for January 2020. If this is the case, we add an estimated value based on the same month from the prior year (January 2020 for January 2021). We also have a growth percentage as a manual adjusting assumption. You can estimate the next period in any way you believe provides the best results.
We can take our model one step further and add a Variance analysis for our sales performance. Forecasting based on drivers (volume and sales price) instead of a sales growth percentage gives us another advantage. We can perform a more detailed analysis by breaking down the variance and looking into the effect of sales volume and average price, both separately and as a whole.
We can see that we have a favorable variance of € 28,625. The sales team did better than expected. However, we can also go into more detail and know that we had a positive effect by selling more units than planned. However, our average sales price was below the estimated one and this harmed our total sales.
Now, let’s move into the future. It’s July 2021, and we have already filled our historical data up to June 2021. The best part about the way we set up our model is that as long as we copy the forecast columns to the right, and fill historical data and growth assumptions, we will always have twelve months forecasted and variance analysis on historical data.
We can go further, add annual summaries, and implement more assumptions for our drivers, like increases in months where we run promotions or increases during holiday seasons. It all depends on how much detail we have in our historical information.
As we illustrated, Rolling forecasts are a great management tool. They allow us always to have an outlook on the expected performance for a pre-set horizon. We update them regularly with the most relevant historical data and assumptions are adjusted to reflect the latest best estimates for future performance.
Rolling forecasts enable the business to react and change plans faster based on shifts in the economy, industry, and the firm itself. The model gives the agility to re-allocate resources promptly and avoid potential adverse effects on the company.
Please, show your support by sharing this article with colleagues and friends. Also, don’t forget to download the Excel model below.
Subscribe to our Newsletter
Get a FREE Excel Benchmark Analysis Template
Hi! I am a finance professional with 10+ years of experience in audit, controlling, reporting, financial analysis and modeling. I am excited to delve deep into specifics of various industries, where I can identify the best solutions for clients I work with.
In my spare time, I am into skiing, hiking and running. I am also active on Instagram and YouTube, where I try different ways to express my creative side.
The information and views set out in this publication are those of the author(s) and do not necessarily reflect the official opinion of Magnimetrics. Neither Magnimetrics nor any person acting on their behalf may be held responsible for the use which may be made of the information contained herein. The information in this article is for educational purposes only and should not be treated as professional advice. Magnimetrics and the author of this publication accept no responsibility for any damages or losses sustained in the result of using the information presented in the publication.