Cellwise Start free

Excel guide · 5 min read

How to build a rolling forecast in Excel

Quick answerPut the last closed month in one input cell, then for each month use =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.

RowABCDEFGHI
1MonthBudgetActualStatusOutlook (plan)Outlook (run rate)Last closed monthJul 1, 2026
2Jan 1, 20268000076500Actual76500765003-month run rate93100
3Feb 1, 20267800079200Actual7920079200
4Mar 1, 20268500088100Actual8810088100
5Apr 1, 20268800084300Actual8430084300
6May 1, 20269000093600Actual9360093600
7Jun 1, 20269200095800Actual9580095800
8Jul 1, 20268600089900Actual8990089900
9Aug 1, 202684000Forecast8400093100
10Sep 1, 202695000Forecast9500093100
11Oct 1, 202698000Forecast9800093100
12Nov 1, 2026110000Forecast11000093100
13Dec 1, 2026125000Forecast12500093100

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.

=SUM(E2:E13)

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.

=COUNTIFS(A2:A13,"<="&I1,C2:C13,"")

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.

=SUMIFS(C2:C13,A2:A13,"<="&I1)+SUMIFS(B2:B13,A2:A13,">"&I1)

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.
=SUM(F2:F13)-SUM(B2:B13)

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.

=SUMIFS(C2:C13,A2:A13,"<="&I1)+SUMIFS(B2:B13,A2:A13,">"&I1)*SUMIFS(C2:C13,A2:A13,"<="&I1)/SUMIFS(B2:B13,A2:A13,"<="&I1)

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.

=TEXT(EDATE(I1,SEQUENCE(12)),"mmm yy")

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

Related guides

Updated