Excel guide · 5 min read
How to build a rolling forecast in Excel
=IF(month<=last_closed, actual, forecast). Closed months show actuals and open months show the forecast, so the full-year outlook is just the SUM of that column: actual year to date plus the remaining forecast. Each month you change only the input cell and type in the new actual. Here, with July closed, the outlook is 1,119,400 against a budget of 1,111,000.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Budget | Actual | Status | Outlook (plan) | Outlook (run rate) | Last closed month | Jul 1, 2026 | |
| 2 | Jan 1, 2026 | 80000 | 76500 | Actual | 76500 | 76500 | 3-month run rate | 93100 | |
| 3 | Feb 1, 2026 | 78000 | 79200 | Actual | 79200 | 79200 | |||
| 4 | Mar 1, 2026 | 85000 | 88100 | Actual | 88100 | 88100 | |||
| 5 | Apr 1, 2026 | 88000 | 84300 | Actual | 84300 | 84300 | |||
| 6 | May 1, 2026 | 90000 | 93600 | Actual | 93600 | 93600 | |||
| 7 | Jun 1, 2026 | 92000 | 95800 | Actual | 95800 | 95800 | |||
| 8 | Jul 1, 2026 | 86000 | 89900 | Actual | 89900 | 89900 | |||
| 9 | Aug 1, 2026 | 84000 | Forecast | 84000 | 93100 | ||||
| 10 | Sep 1, 2026 | 95000 | Forecast | 95000 | 93100 | ||||
| 11 | Oct 1, 2026 | 98000 | Forecast | 98000 | 93100 | ||||
| 12 | Nov 1, 2026 | 110000 | Forecast | 110000 | 93100 | ||||
| 13 | Dec 1, 2026 | 125000 | Forecast | 125000 | 93100 |
Download this sheet as an Excel practice file (.xlsx)
Lay out budget, actuals and one input cell
Put the months down column A as real dates (the first of each month, 1 Jan to 1 Dec 2026), the budget in B and actuals in C as they come in. Keep actuals in their own column. If you type each actual over the forecast cell, the forecast for that month is gone and you can never check how good it was (the forecast accuracy guide shows how to measure it).
The input that drives everything is the last closed month in I1, here 1 Jul 2026. A month counts as closed when its books are final, which is usually a week or so into the next month, so on 5 August I1 may still say June. Column D shows the switch: =IF(A2<=$I$1,"Actual","Forecast").
Swap in actuals with one IF
In E2, =IF(A2<=$I$1,C2,B2) shows the actual for closed months and the budget for open ones. Fill it down to December. Jan to Jul now show actuals, Aug to Dec show the plan. When August closes, type its actual into C9 and change I1 to 1 Aug: the August row switches over and nothing else needs editing.
Because the months are dates, the comparison also works across year ends. Comparing month names as text ("Aug" <= "Jul") would compare alphabetically and give the wrong answer.
Result: 1119400
1,119,400: 607,400 of actuals for January to July plus 512,000 of plan for August to December.
What if I move I1 forward before typing the actual?
The month then shows 0 and the outlook drops by a whole month of revenue. A quick check counts closed months with a blank actual. Put it next to the outlook so checking for 0 is part of the monthly routine.
Result: 0
0: every month up to July has an actual. The same count over the open months returns 5, which is what missing actuals look like.
Full-year outlook = actual YTD + remaining forecast
Finance teams usually show the two parts separately, because they answer different questions. Actual year to date is fact; the remaining forecast is the judgement call. SUMIFS with the input cell splits the year in two.
Actual YTD is =SUMIFS(C2:C13,A2:A13,"<="&I1), 607,400. The remaining plan is =SUMIFS(B2:B13,A2:A13,">"&I1), 512,000. Added together they give the same 1,119,400 as summing column E. The "<="& part joins the operator to the date in I1, so the criterion moves when I1 does.
Result: 1119400
1,119,400: actual year to date plus the budget for the five open months.
Plan, run rate or plan adjusted: which forecast for open months
Using the budget for open months assumes the rest of the year goes to plan. Two common alternatives:
- 3-month run rate: the average of the last three closed months,
=AVERAGEIFS(C2:C13,A2:A13,"<="&I1,A2:A13,">"&EDATE(I1,-3))in I2, which gives 93,100 (May to July). Column F uses it for every open month. Simple, but it ignores seasonality: it under-forecasts November and December, where the plan expects 110,000 and 125,000. - Plan adjusted by performance: scale the remaining budget by how actuals have tracked the budget so far. Year to date the business is at 607,400 against 599,000 of budget, 1.4% ahead, so the remaining 512,000 grows by the same 1.4%. This keeps the seasonal shape of the plan.
Result: -38100
-38,100: the run-rate outlook (1,072,900) is below budget, while the plan-based outlook is 8,400 above it. Same actuals, opposite messages.
What's the formula for the plan-adjusted outlook?
Actual YTD plus the remaining budget times the YTD ratio of actual to budget.
Result: 1126579.9666
1,126,580 (rounded): 15,580 above budget, keeping the plan's big November and December.
Which one should I report?
For a seasonal business, plan-based or plan-adjusted. For steady revenue, the run rate is a fair check. Many teams show the plan-based outlook as the number and the run rate beside it as a sanity check; a moving average chart makes the trend visible.
Variance to budget, and a true 12-month rolling window
The headline variance is outlook minus budget: =SUM(E2:E13)-SUM(B2:B13) gives 8,400, or 0.8% of the 1,111,000 budget when you divide by SUM(B2:B13). For month-by-month variance against a fixed budget, see budget vs actual variance.
Strictly, a rolling forecast always looks 12 months ahead instead of stopping at December. Keep adding months to the bottom of the table and sum the 12 after the last closed month: =SUMIFS(E:E,A:A,">"&I1,A:A,"<="&EDATE(I1,12)). In Excel 365, =EDATE(I1,SEQUENCE(12)) lists those 12 month starts for headings. Each month the window moves forward by one, so you always see a full year ahead, which matters most for cash flow planning.
Result: spills 12 rows × 1 column
| Aug 26 |
| Sep 26 |
| Oct 26 |
| Nov 26 |
| Dec 26 |
| Jan 27 |
| Feb 27 |
| Mar 27 |
| Apr 27 |
| May 27 |
| Jun 27 |
| Jul 27 |
Spills Aug 26 down to Jul 27: the 12 months after the last closed month, ready to be the headings of a next-12-months view.
Common mistakes
Typing actuals over the forecast
Keep budget, actual and outlook in separate columns so you can still compare the forecast with what happened.
Months stored as text
Use real dates (the first of each month) so <= the last closed month compares correctly, also across year ends.
Moving the closed month before entering the actual
Add a COUNTIFS check for closed months with a blank actual and make it part of the month-end routine.
Using a run rate for a seasonal business
Use the plan, or the plan scaled by year-to-date performance, so peak months keep their size.
FAQ
What is a rolling forecast in Excel?
A forecast that replaces forecast months with actuals as each month closes and keeps the remaining months forecast. In Excel, one IF per month against a last-closed-month input cell does the switch.
What is the difference between a budget and a rolling forecast?
The budget is set once and stays fixed for the year. A rolling forecast is updated every month with actuals and a new view of the months left, and is compared against the budget.
How do I calculate a full-year outlook in Excel?
Add actual year to date and the forecast for the remaining months: SUMIFS of actuals for months up to the last closed month plus SUMIFS of the forecast for the months after it.
How do I calculate a 3-month run rate in Excel?
Average the last three closed months with AVERAGEIFS, using the criteria "<="&last_closed and ">"&EDATE(last_closed,-3) on the month column.
Functions used
IFPicks a result from a testSUMAdds numbersSUMIFSAdds with several conditionsAVERAGEIFSAverages with several conditionsCOUNTIFSCounts with several conditionsEDATESame day, months laterSEQUENCENumbered list, spillsTEXTNumber to formatted textDATEBuilds a date
Related guides
- How to calculate a seasonal index in Excel
- How to calculate the book-to-bill ratio in Excel
- How to calculate budget vs actual variance in Excel
- How to build a 13-week cash flow forecast in Excel
- How to calculate a weighted pipeline in Excel
- How to calculate backorder rate in Excel
- How to calculate revenue per employee in Excel
Updated