Hello! Welcome to your next lesson in the "Budgeting, Planning, and Variance Analysis" module.
In our last session, you learned how to build a Zero-Based Budgeting (ZBB) template. This provided you with a powerful tool for ensuring every dollar of planned spending is justified and aligned with strategic goals. However, a budget created at the start of the year is, by its nature, a static snapshot. In a dynamic business environment, things change—fast.
Today, we address that challenge. This lesson focuses on the learning outcome: Design and implement a rolling forecast model with dynamic logic to switch between actual and forecast data. A rolling forecast is a living document that continuously updates, giving you a more accurate and relevant picture of the future at any point in time. We'll cover the strategic "why" behind this method and then dive into the practical "how" of building one in Excel.
1. From Static Plans to Dynamic Forecasts
A static annual budget, like the ZBB you designed, is crucial for setting a baseline and allocating resources. But what happens when, three months into the year, a major competitor launches a new product, or a key raw material cost doubles? The original budget quickly becomes obsolete as a tool for making real-time decisions. This is where the rolling forecast comes in.
Instead of a forecast window that shrinks as the year progresses (the "fiscal year cliff"), a rolling forecast always looks out a consistent period into the future, such as 12 or 18 months. As each month or quarter of actual results is recorded, it replaces the forecast for that period, and a new forecast period is added to the end.
To understand the strategic importance and mechanics of this approach, let's turn to a guide from Wall Street Prep, a name synonymous with the high-quality training you're aiming for.
Rolling Forecast Guide | FP&A Best Practices Tutorial
The article 'Rolling Forecast Best Practices' provides a clear explanation of what a rolling forecast is, why it's a critical tool for modern FP&A, and how it addresses the shortcomings of traditional static budgets.
Please read the first three main sections: 'How Does a Rolling Forecast Work?', 'Why Do Organizations Need a Rolling Forecast?', and 'Rolling Forecast vs. Traditional Budget'. Focus on the criticisms of the traditional budget and the corresponding benefits of the rolling forecast model.
As you read, note the key benefits: increased agility, more informed resource allocation, and a forward-looking culture. The article also touches on the challenges, primarily the increased complexity and process requirements, which is exactly what we'll learn to manage in Excel.
Forecast Cadence
How often a company updates its forecast and how far it looks out is called the cadence. This isn't one-size-fits-all. A stable utility company might be fine with a quarterly update, while a high-growth tech startup might need to update monthly. Common cadences are described as X+Y, where X is the number of months of actuals and Y is the number of months of forecast.
For a deeper look into these cadences, the following resource provides excellent examples.
Rolling Forecast Template: 12-Month Forecasts (Excel ...
This guide from Cube Software details different rolling forecast cadences and the types of companies they are best suited for.
Please read the section titled 'Rolling forecast cadences'. This short section explains formats like 3+9, 4+8, 8+4, and 9+3, providing context for how forecasts are structured throughout the year.
2. Building a Rolling Forecast in Excel
Now, let's get practical. We will build a model that automatically "switches" from actual data to forecast data based on a single date input. We will follow a structured approach demonstrated in a video from the Association for Financial Professionals (AFP).
The core of our dynamic model will rely on two key Excel concepts:
- A Control Cell: A single cell where we define the "as-of" date. All logic will be driven by this date.
- Flags: A helper row that contains
1s and0s (orTRUE/FALSE) to signal whether a given time period is in the past (actual) or future (forecast).
Step 1: Setting Up the Model Structure
First, we need to create the timeline and the primary logic that pulls either actual or forecast data.
Building a Rolling Forecast in Excel: AFP DIY Automation Video 1
This video, 'Building a Rolling Forecast in Excel,' will guide us through the construction. We'll start with setting up the dates, flags, and the main IF function.
Watch from 00:48 to 02:34. Pay close attention to how the 'Start Date' (our control cell) drives the timeline. Notice the 'Flag' row and the simple but powerful IF statement used to create a combined 'Actuals & Budget' line.
The formula structure is simple: IF(Flag=Actual, [Pull Actuals], [Pull Forecast]). This is the "switch" that gives our model its dynamic capability.
Step 2: Generating the Forecast Data
With the structure in place, we need to generate the numbers for future periods. While sophisticated models use complex driver-based logic (which we'll cover later in the course), a common and effective method for stable data is to use Excel's built-in time-series forecasting functions.
In this step, we'll use FORECAST.ETS to project future values based on the historical actuals.
Building a Rolling Forecast in Excel: AFP DIY Automation Video 1
Continuing with the same video, let's see how to implement the FORECAST.ETS function. Proper use of absolute and relative cell references ($) is critical here.
Watch from 02:34 to 06:13. The presenter explains how to use FORECAST.ETS to project future values. Focus on how the dollar signs ($) are used to anchor the historical ranges, allowing the formula to be dragged across future periods correctly. This is a vital modeling skill.
Step 3: Refining the Logic and Finalizing the Model
Our model is now functional, but it needs two important refinements:
- Preventing illogical results, such as a negative sales forecast.
- Implementing the "rolling" feature, ensuring the forecast only appears in future periods and is limited to a specific horizon (e.g., 12 months).
We'll use the MAX function to solve the first problem and multiply our forecast formula by our flags to solve the second.
Building a Rolling Forecast in Excel: AFP DIY Automation Video 1
In this final build segment, we'll add the finishing touches that make the model robust and truly a 'rolling' forecast.
Watch from 06:13 to 08:03. Observe how MAX(..., 0) is wrapped around the FORECAST.ETS function to prevent negative values. Then, see how multiplying the entire formula by the '12mths' flag row makes the forecast data disappear in past periods and extend only for the desired 12-month window.
Test your understanding!
In the model you just saw built, the forecast for a certain cost line item is based on historical trends using FORECAST.ETS. However, you know for a fact that a new 3-year contract for that service was just signed, locking in the price at $5,000 per month starting next month.
How would you adjust the model to reflect this superior, known information instead of relying on the statistical forecast?
Show answer
You would override the FORECAST.ETS formula for that specific line item. A robust way to do this is to add another IF statement.
For example, the formula for the forecast portion could be changed to: IF(CurrentPeriod > ContractStartDate, 5000, FORECAST.ETS(...))
This demonstrates a critical FP&A principle: statistical forecasts are a tool, not a mandate. Always incorporate known, specific business intelligence into your model, as it is almost always more accurate than a purely historical projection.
Step 4: Testing the Dynamic Logic
The payoff for all this careful setup is a model that updates automatically. By changing a single cell—the "current month" or "as-of" date—the entire model rolls forward.
Building a Rolling Forecast in Excel: AFP DIY Automation Video 1
Let's watch the model in action. This demonstrates the power of the dynamic logic you've just built.
Watch from 09:01 to 09:58. See how changing the 'Current Month' input cell instantly re-draws the line between actuals and forecast. When new actuals are added, the forecast automatically uses this new data to re-project the future.
Try to replicate this entire process in a blank Excel sheet. Building it yourself is the best way to solidify your understanding.
Conclusion
Today you've made the leap from static budgeting to dynamic forecasting. You learned how to design and build a rolling forecast model that is flexible, responsive, and far more useful for decision-making in a changing world.
Key Takeaways:
- Rolling Forecasts Provide Agility: Unlike static budgets, they offer a continuous, forward-looking view, enabling businesses to react quickly to new information.
- Dynamic Logic is Driven by a Switch: The core of the model is a control cell (like an "as-of" date) and
IFlogic that toggles between historical actuals and future forecasts. - Flags are a Key Modeling Technique: Simple helper rows with
1s and0s are an elegant and powerful way to control which formulas are active in which periods. - Excel Functions are Your Toolkit: You can combine logical (
IF), lookup, and forecasting (FORECAST.ETS) functions to build sophisticated and automated models.
In our next lesson, we will address another critical aspect of corporate FP&A: complexity at scale. You'll learn how to structure a chart of accounts and build a consolidation model to aggregate multiple departmental or entity budgets, bringing individual plans together into a single, cohesive company-wide view.