Create a Rolling Forecast Model in Excel with Claude AI
Progress1 of 4
1
The finished forecast
2
Prepare your inputs
3
Prompt, FAQ & related
4
Quick quiz
Section 01
What the finished rolling forecast looks like
Create a Rolling Forecast Model in Excel with Claude AI
This course gives you a Claude prompt that builds a rolling 12-month forecast model in Excel. Each month, actuals replace the oldest forecast column and a new forecast month is added at the end — so the model always looks exactly 12 months ahead. You get a continuously updated planning tool instead of a static annual budget that's stale by February.
The Completed Rolling Forecast
Here is what Claude produces. The first three columns contain closed-month actuals (blue). The remaining nine columns are forecast periods (yellow). As June closes, its actuals overwrite the forecast, and a new March column appears at the far end.
A
B
C
D
E
F
G
1
Actual
Actual
Actual
Forecast
Forecast
Forecast
2
Line Item
Apr
May
Jun
Jul
Aug
Sep
3
Revenue
520k
535k
510k
540k
555k
580k
4
COGS
208k
214k
204k
216k
222k
232k
5
Gross Profit
312k
321k
306k
324k
333k
348k
6
OpEx
245k
248k
250k
252k
254k
256k
7
Net Income
67k
73k
56k
72k
79k
92k
Six of twelve months shown. Blue = closed actuals. Yellow = forecast driven by growth rates and seasonality you define. The full model extends to 12 columns plus a rolling total.
Always shows exactly 12 months: closed actuals on the left, open forecasts on the right
Forecast columns are formula-driven — change one growth assumption and every future month recalculates
Seasonal adjustments apply automatically based on monthly weightings you provide
A summary row shows the rolling 12-month total for each line item
A rolling forecast makes budget variance analysis more meaningful — you're comparing actuals to a forecast that reflects current conditions, not a plan locked 10 months ago. Both courses are part of the AI for Finance Teams collection.
Prepare Your Historical Data and Forecast Assumptions
Claude builds the forecast from two inputs: your recent actuals and your forward-looking assumptions. The more months of actuals you provide, the better Claude can structure the transition from closed to open periods.
Historical Actuals
A
B
C
D
E
1
Line Item
Jan
Feb
Mar
Apr
2
Revenue
480,000
495,000
510,000
520,000
3
COGS
192,000
198,000
204,000
208,000
4
OpEx
240,000
242,000
243,000
245,000
01
Provide at least 3 months of closed actuals
The more history Claude has, the better it can structure the actual-to-forecast transition. Six to twelve months of actuals is ideal — it lets Claude see trends and seasonality in your data.
02
State your growth and cost assumptions
Tell Claude what drives the forecast: a monthly revenue growth rate, a fixed COGS ratio, an OpEx escalation percentage. Be specific — "revenue grows 2% per month" is actionable; "revenue will increase" is not.
03
Include seasonal weightings if applicable
If Q4 is historically 30% of annual revenue while Q1 is 20%, provide those weights as a monthly index. Claude applies the index to distribute the annual forecast across months realistically.
04
Specify any known one-off items
A planned office relocation in September or a one-time contract payment in November should be listed separately. Claude adds these as hard-coded overrides in the relevant month rather than spreading them across the forecast.
Tip: Keep your assumptions on a separate tab in the workbook. Claude can structure the model so every forecast cell references the Assumptions tab — changing one number updates the entire forecast instantly.
The Claude Prompt for a Rolling Forecast Model
Copy this prompt into Claude with your actuals and assumptions. Claude returns a 12-month model with formulas, not hard-coded numbers — so you can update it each month by pasting new actuals into the appropriate column.
Prompt — paste into Claude
I need a rolling 12-month forecast model in Excel. Below are my historical actuals and forward-looking assumptions.
HISTORICAL ACTUALS (columns: Line Item, then one column per month):
[Paste your actuals here — at least 3 months, more is better]
FORECAST ASSUMPTIONS:
- Revenue growth: [e.g. 2% month-over-month]
- COGS as % of revenue: [e.g. 40%]
- OpEx monthly escalation: [e.g. 0.5% per month]
- Seasonality index (optional): [e.g. Jan=0.85, Feb=0.90, ..., Dec=1.20]
- One-off items (optional): [e.g. Sep: $50,000 relocation cost in OpEx]
Current month (first open forecast month): [e.g. July 2025]
Requirements:
- Build a 12-month rolling model: actuals in the leftmost columns (blue header), forecast in the remaining columns (yellow header).
- Each month, I will paste new actuals to replace the oldest forecast column and add a new forecast month at the far end.
- Forecast columns must use formulas referencing the assumptions, NOT hard-coded values.
- Apply the seasonality index to monthly revenue if provided.
- Add one-off items as hard-coded overrides in the specified month.
- Include subtotal rows for Gross Profit (Revenue minus COGS), Operating Income (Gross Profit minus OpEx), and Net Income.
- Add a rolling 12-month total column on the far right.
- Do NOT invent figures or assumptions I haven't provided.
- Do NOT project beyond the 12-month horizon.
- Format the output as a tab-separated table I can paste directly into Excel, with formulas written out (e.g. =B3*1.02).
Rolling Forecast vs Static Budget — When Each Fits
A rolling forecast does not always replace a static annual budget. Many organisations maintain both: the annual budget as a fixed accountability benchmark for compensation and board reporting, and the rolling forecast as an operational planning tool that reflects reality. The key difference is responsiveness — when conditions change mid-year, the rolling forecast absorbs the new information immediately while the annual budget stays locked.
If you track performance against both, you can run the budget variance report against the static budget for board decks, and a separate forecast variance for internal management reviews. For a more integrated view that links the P&L forecast to the balance sheet and cash flow, the 3-statement financial model course covers the full linkage.
Common Forecast Drivers by Line Item
A
B
C
1
Line Item
Typical Driver
Example Formula Logic
2
Revenue
MoM growth rate × seasonality index
=PriorMonth*(1+GrowthRate)*SeasonIndex
3
COGS
Fixed % of revenue
=Revenue*COGSPercent
4
Payroll
Headcount × average cost, stepped
=Headcount*AvgCost (step up in hire month)
5
Rent
Flat monthly amount until lease renewal
=MonthlyRent (override at renewal)
Tell Claude which driver applies to each line item. The more specific your assumptions, the more useful the resulting model.
Frequently Asked Questions
What is the difference between a rolling forecast and an annual budget?
An annual budget is set once and stays fixed for the fiscal year. A rolling forecast is updated every month or quarter: actuals replace the oldest forecast period, and a new period is added at the far end so the model always looks 12 (or 18) months ahead. This keeps planning current with business conditions.
How many months should a rolling forecast cover?
Twelve months is the most common horizon. Some organisations use 18 months to ensure the forecast always spans at least one full fiscal year. The prompt in this course defaults to 12 but you can change it.
Can Claude automatically pull actuals into the forecast each month?
Claude cannot connect to live data sources. It builds the Excel structure with clearly separated Actuals and Forecast columns. Each month, you paste updated actuals into the Actuals section and the formulas recalculate the remaining forecast periods automatically.
How do I handle seasonality in a rolling forecast?
Include a seasonality index or monthly weighting in your input data. The prompt instructs Claude to apply seasonal adjustments to the base forecast so that Q4 revenue projections, for example, reflect your historical holiday uplift rather than a flat monthly average.
Does this replace a 3-statement financial model?
No. A rolling forecast focuses on the P&L and key operating metrics. A 3-statement model links the income statement, balance sheet, and cash flow statement. They serve different purposes and can complement each other.