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.

📥 Download Free Rolling Forecast Template (Excel)


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

FeatureWhat It Does
Connected TemplatesBuild once, reuse across departments or time periods
Relative Time EngineAuto-shifts time ranges based on last closed month
Live Actuals IntegrationPulls directly from QuickBooks, Sage, or Excel
Centralized Roll-upsInputs from many users consolidate in real-time
Audit TrailTrack changes, who made them, and when
Scalable StructureWorks across 100s of accounts and multiple entities

Summary: Which Method is Right for You?

CriteriaManual ExcelPivotXL Automation
Best ForSmall teams or one-time setupGrowing teams or recurring use
EffortHigh maintenanceBuild once, reuse forever
Actuals IntegrationManual inputDirect sync
Time LogicStatic formulasDynamic rolling windows
CollaborationExcel file sharingMulti-user collaboration
Error RiskHighLow

👇 Ready to Build a Better Forecast?

Download the Free Excel Rolling Forecast Template
🔄 See How PivotXL Automates Budgeting
📊 Explore Our Business Budgeting Guide