Employees looking at a rolling forecast
Blog

How To Create a Rolling Forecast in Excel [Template Included]

September 29, 2022 Stefanie Neyland  
LinkedIn Twitter Facebook

No matter your industry, it's likely that your business experiences evolving market conditions regularly. But you can plan for and accommodate those challenges with the right budgeting strategy.

Budgeting is one method of planning for the future. However, a more dynamic approach accounts for various scenarios and allows you to plan for unexpected events: the rolling forecast.

Rather than a static, or traditional budget, rolling forecasts predict future performance. Creating a rolling forecast in Excel allows you to combine historical data with present financial information to plan for the future more accurately.

💡Key Takeaways:

  • With a rolling forecast, your company can project future performance in a specific time frame. By using historical information, the forecast seeks to assess financial numbers accurately. You improve the forecast's predictive ability by updating the forecast inputs periodically.
  • The rolling forecast takes an "add/drop" approach. Once the specified period (month, quarter, year) ends, you drop the completed period from the forecast and add a new period. 
  • A rolling forecast consists of several components, including defined objectives, specific value drivers, scenarios, sensitivities and a list of key players.

Benefits of a Rolling Forecast in Excel

Many companies use either a static budget or a rolling forecast.

Whether or not you've used a rolling forecast, most financial planning professionals are familiar with static budgeting. A traditional or static budget remains fixed and doesn't account for the future. A static budget remains consistent even if the business's debts or revenues change.

In Excel, a rolling forecast continually updates as it removes completed periods and adds new periods.

Source: WallStreetMojo


On the other hand, you update a rolling forecast by adding a new period after the expiration of the previous period. For example, if the forecast period is one month, the budget would continuously add a new month once the current month expires.

 

Benefits of a rolling forecast include: 

  • Better risk evaluation: The rolling forecast enables you to adapt. When something in the industry or economy changes, your business can incorporate those changes within the forecast. As you edit the forecast to reflect these changes, you can also identify problem areas that need more attention.
  • Improved financial planning accuracy: Financial planning requires you to consider several ever-changing variables. For instance, a government policy may require you to make immediate changes that a static budget wouldn't account for until the next accounting period.

Creating a Rolling Forecast in Excel

To create a rolling forecast in Excel, a template is beneficial. Using the template, take the following steps:

1. Identify Desired Outcomes and Objectives

Define your objectives upfront. To make the rolling forecast worth your time, you must understand why you're creating this tool. Answer questions like:

  • For what explicit forecast do you need the forecast?
  • Who will use the forecast?
  • What decisions will the forecast inform? 
  • What level of detail is needed?

2. Name the Time Frame

Effective planning requires a defined time frame. How far into the future must you forecast? In what increments will you divide the forecast?

For example, perhaps there's some major event happening in the fourth quarter of your fiscal year. If so, measuring your rolling forecast by quarters would be most helpful. Then, you could choose to divide the quarters into month-long increments.

After each month passes, you'll add a new month into the forecast through Quarter 4.

3. Determine the Level of Detail

The necessary level of detail may depend at least in part on the selected time frame. The longer your rolling forecast in Excel, the less detailed it will likely be.

However, your rolling forecast will play an integral role in the business's financial health. If a particularly consequential financial decision is on the horizon, you should therefore devote more planning resources to prepare for this decision.

4. Identify the Contributors

Just as you need to understand why you're creating a rolling forecast in Excel, you must also understand who should be involved in the process and why.

To get the most out of planning, these individuals should be key stakeholders with an objective, unbiased and insightful viewpoint. Management could also select contributors they can reward and hold accountable as necessary.

5. Identify the Value Drivers

Choose the value drivers that will help the rolling forecast find success. Instead of listing every goal you could achieve, identify the most important and attainable goals. You may also review historical company successes and general industry trends.

For example, when numbers change, you need to know how, why and to what degree they changed.

Imagine that your company is a computer manufacturer. When tracking revenue, you need to know both the quantities and sales prices of each model and which sells most and creates the most revenue.

A rolling forecast is more dynamic than a static budget and lends itself to more effective financial planning.

6. Confirm Data Accuracy

You're probably familiar with the adage "garbage in, garbage out." The rolling forecast will only predict future performance accurately if the data input is reliable and credible.

Review your historical data or "source of truth" for potential errors.

7. Plan for Scenarios and Variances

Planning for the future necessarily entails using assumptions. You can assess the range of possible financial outcomes by using these assumptions and value drivers.

A rolling forecast in Excel accounts for variances as you input new information once received.

Source: Wall Street Prep

As the company gains glimpses into possible futures, it can prepare for actions it could use to adapt. Once new information becomes available and trends begin to crystalize, you can update the forecast to improve accuracy.

This, in turn, helps management make better decisions.

8. Compare the Actual and Estimated Forecasts

As the future becomes the present and you have real-time data, you can compare the actual numbers with the projected performance. If any variances exist, the financial team should determine the causes and work to remedy any errors.

Vena Templates
FREE Rolling Forecast Template for Excel
Improve your operating expense forecasting. Choose from multiple planning methods, view trendlines and variances and more.
Download Your Free Template Now

Recommended Posts