How to build a 12-month rolling forecast with Claude

Written up from the video How to Build a Dynamic Rolling Forecast with Claude (CORRECTLY).

You can use Claude to build a dynamic 12-month rolling forecast on top of your actuals in a single session, provided you settle the assumptions before any formula is written. The build has two layers: a continuous monthly timeline that links actuals to the P&L and forecast months to the assumption sheets, and a rolling view driven by one start-date cell. Claude builds both. The assumptions, and whether they fit the strategy, stay with you.

In the video I build it for a fictional service business with five months of actuals, entirely through Claude in Cowork. The method matters more than the speed: give Claude strict rules and a clear sequence, and make it ask rather than guess.

What you give Claude

• The inputs model: the actuals, the detailed P&L and the assumption sheets for revenue, staff cost and operating cost.
• An open-questions note listing the assumptions you are not yet sure about.
• The strategic direction. In real life this comes from meetings with the leadership team, the sales pipeline and the operating plan. It is what the forecast has to be consistent with.

Step 1: set the standing rules

Before any building, give Claude the rules that hold for every step:

• Modelling discipline. Never hardcode a number that can be a formula. Keep inputs, calculations and outputs separate. Blue for inputs, black for formulas, green for links to other tabs. No circular references.
• Integrity. The actuals must tie to the detailed P&L to the dollar, the first forecast month must roll cleanly off the last actual month, and every subtotal must foot, proven by a checks block.
• Protect the source. Do not touch the actuals tabs. Build new tabs that link to them.
• Ask, do not guess. If any mapping, assumption, structure or data boundary is unclear, stop, set out the options and wait. Do not fabricate data to fill a gap.

Step 2: settle the assumptions first

This is the step most people skip, and it is the one that decides whether the forecast is worth anything. Work through the open questions with Claude before the model moves. Where there is real evidence in the actuals or the strategy note, Claude proposes a defensible answer with its reasoning. Where the answer is a judgement call, it says so and gives you two or three options with the trade-offs, and you decide.

In the video we settled six drivers this way: a single monthly churn provision on the recurring base (0.83% a month, 10% a year), four modest new retainer wins phased by month, a project run rate held at a mid-single-digit uplift, freelancer cost gliding from 24% to 20% of project revenue as delivery moves in-house, a phased hiring plan, and a 3% salary review with two one-off costs in the right months. Yours will differ. The point is that each one was reasoned, not guessed.

Step 3: update the input cells only

Claude writes the agreed values into the blue input cells and nothing else. Formulas, totals and actuals stay untouched. It then shows you the resulting monthly values for each driver so you can sanity-check them against the strategy before anything is built. If an agreed value has no obvious input cell, it stops and asks.

Step 4: build the forecast in two layers

Layer one, the working timeline. One continuous monthly P&L from the first actual month to the last forecast month, every line as a row. Actual months link to the detailed P&L. Forecast months link to the assumption sheets. Gross profit, EBITDA, EBIT, profit before tax and net profit are calculations.

Layer two, the rolling view. A single start-date cell, a dropdown restricted to valid months, drives twelve consecutive columns that re-label and repopulate themselves, plus a twelve-month total. Each cell pulls from the working timeline, so nothing is tied to a fixed month, and each column is marked actual or forecast.

One boundary rule matters here: if the start date plus twelve months runs past the end of the timeline, Claude must not invent months. It flags the problem and asks how to handle it. In the build, the dropdown was restricted to starts where a full window still fits.

Step 5: add the financial-year view

Finally, a fixed actual-plus-forecast view for the financial year, each P&L line by month with a full-year total, drawn from the working timeline and not driven by the rolling start date. This is the view most leadership teams ask for first.

The checks

The build is only finished when it proves itself:

• Zero formula errors across the workbook.
• The actual months reconcile to the detailed P&L to the dollar.
• The first forecast month rolls cleanly off the last actual month.
• The rolling window is tested at both edges, so it never runs off the end of the timeline.

Where your judgement comes in

The formulas are the easy part. What makes the forecast worth using is the sequence and the discipline: the assumptions settled first, and settled by you, in line with a strategic direction that only leadership can set. Claude brings the evidence and the options. You decide which assumption the business is prepared to stand behind, and you sign off the result.