In today’s unpredictable business landscape, long-term planning can feel like a shot in the dark. Static annual budgets are often outdated the moment they’re finalized. That’s why high-performing finance teams are turning to rolling forecasts — a forward-looking approach that updates continuously based on actual results.
In this post, we’ll walk you through two practical ways to implement a rolling forecast — starting with a manual Excel version (which includes a free downloadable template) and moving into a more scalable, automated approach using PivotXL.
What Is a Rolling Forecast?
A rolling forecast is a dynamic planning method where forecasts are extended by a set number of periods each time actuals are updated. For example, instead of creating a forecast that ends in December, a rolling forecast always extends 12 months ahead — so when you close July, you add July next year into the forecast window.
🔁 Why It Matters:
- Keeps your forecast current: You’re always planning based on the latest actuals.
- Improves agility: Easily adjust for real-world changes like hiring shifts, supply chain delays, or unexpected growth.
- Enables better decisions: Leaders see what’s coming and can respond proactively.
A rolling forecast doesn’t replace budgeting — it complements it by keeping financial plans relevant all year long.
Two Ways to Build a Rolling Forecast
Let’s break down the two approaches:
🧩 Step 1: Manual Rolling Forecast in Excel
Start with our free downloadable template and follow a structured, formula-based method.
⚙️ Step 2: Automated Rolling Forecast in PivotXL
Take your forecasting process to the next level — with connected templates, dynamic time logic, and real-time data integration.
Step 1: Manual Rolling Forecast in Excel (Free Template)
If you’re comfortable in Excel and looking to get started without buying software, this approach is for you.
This method uses similar logic to the Business Budgeting Template we previously shared, where different account lines roll up to a consolidated profit and loss (P&L). But instead of budgeting once a year, we forecast future months based on formulas linked to actuals.
💡 One of the best parts of using the Excel template? You can click through every formula to see exactly how the forecast is calculated. It’s a great way to understand the logic, learn how rolling forecasts work, and tailor it to your business needs.
How It Works – Step-by-Step
1. 🧾 Input Actuals
Start by entering your monthly actuals by account (e.g., revenue, cost of goods sold, marketing expenses, etc.). You’ll need several months of actual data to build a baseline for forecasting.
2. 📊 Define Forecast Drivers
For each account line, define the logic for forecasting. Common examples include:
- Revenue grows at a fixed monthly percentage
- Expenses follow historical averages
- COGS is a fixed % of revenue
- Salaries increase based on headcount or inflation
You can define these assumptions either:
- Globally (e.g., all revenue grows by 5% monthly)
- Account-specific (e.g., Marketing grows 3%, Rent is fixed)
3. 📆 Apply Rolling Logic
Using Excel formulas like OFFSET, INDEX, or LOOKUP, you can pull the most recent actual month and apply your forecast assumptions to build the next period.
For example:
=OFFSET(B2,0,-1)*(1+$D$1)
This formula pulls the previous month’s actuals and applies a growth rate stored in cell D1.
4. 🧮 Roll Up Forecast Totals
Once individual line items are forecasted, roll them up to your P&L structure:
- Revenue
- Direct Costs
- Gross Profit
- Operating Expenses
- Net Income
You can use a chart of accounts or grouping method similar to your budget file.
5. 🔁 Extend Each Month
As each actual month closes, add the new month into your actuals section and move your forecast window one month ahead. You’ll need to:
- Update formulas or dynamic ranges
- Adjust growth rates (if needed)
- Validate for formula errors
Benefits of Manual Excel Method
✅ Familiar and easy to understand
✅ No new tools or software needed
✅ Customizable to your unique chart of accounts
✅ Great learning experience for small teams
Pain Points of Manual Forecasting
❌ Doesn’t scale beyond 20–30 accounts easily
❌ Formula errors multiply over time
❌ Difficult to maintain monthly
❌ No version control or audit trail
❌ No connection to live actuals
Step 2: Automated Rolling Forecast in PivotXL
Once you have dozens of accounts, monthly updates, or multiple team members involved — Excel starts to break down. That’s when automation with PivotXL becomes a game-changer.
PivotXL is designed for finance teams that want to keep the flexibility of Excel but eliminate the manual overhead. It connects to your actuals data, allows you to build templates once, and dynamically extends forecasts using relative time logic.
How Automation Works in PivotXL — Step-by-Step
1. 🔗 Connect to Actuals
Pull your trial balance or actuals data directly from:
- QuickBooks
- Sage 100 Contractor
- Sage 300 CRE
- Excel exports or APIs
This becomes your source of truth — no more copy-pasting.
2. 🧱 Create a Template Once
Build your forecast structure once, defining formulas and roll-ups by:
- Account
- Department
- Entity
Templates are reusable — no need to rebuild every month.
3. 🕒 Use Relative Time Logic
PivotXL’s unique time engine uses relative references like:
- “Previous closed month”
- “Current forecast month + 1”
- “Trailing 3-month average”
So your forecast always moves forward automatically as time progresses.
4. 📥 Enable Forecast Inputs Across Teams
Let stakeholders (sales, HR, ops) enter assumptions in dedicated input sheets. All entries are validated and traceable — no emailing spreadsheets around.
5. 📈 See Consolidated Forecasts in Real-Time
All inputs roll up instantly into consolidated P&L, balance sheet, or cash flow statements. You can also layer in dashboards, scenario models, and variance analysis.
Key Features That Power PivotXL Forecasting
| Feature | What It Does |
|---|---|
| Connected Templates | Build once, reuse across departments or time periods |
| Relative Time Engine | Auto-shifts time ranges based on last closed month |
| Live Actuals Integration | Pulls directly from QuickBooks, Sage, or Excel |
| Centralized Roll-ups | Inputs from many users consolidate in real-time |
| Audit Trail | Track changes, who made them, and when |
| Scalable Structure | Works across 100s of accounts and multiple entities |
Summary: Which Method is Right for You?
| Criteria | Manual Excel | PivotXL Automation |
|---|---|---|
| Best For | Small teams or one-time setup | Growing teams or recurring use |
| Effort | High maintenance | Build once, reuse forever |
| Actuals Integration | Manual input | Direct sync |
| Time Logic | Static formulas | Dynamic rolling windows |
| Collaboration | Excel file sharing | Multi-user collaboration |
| Error Risk | High | Low |
👇 Ready to Build a Better Forecast?
✅ Download the Free Excel Rolling Forecast Template
🔄 See How PivotXL Automates Budgeting
📊 Explore Our Business Budgeting Guide
