Forecast Template Pack — Module 06 · Catalyst Operations Partners

Download the file

A real .xlsx — five sheets, live formulas, named ranges, conditional formatting. Open in Excel, Numbers, or Google Sheets. The Summary tab opens first.

Forecast Model Starter — Q2 revenue example

5 sheets · 9 assumptions · 3 scenarios · 2-way sensitivity grid · ~13 KB

forecast-model-starter.xlsx

Download

The forecast anatomy, made physical

Module 06 teaches the four layers. This workbook enforces them — each layer is a tab, and every output traces back to the Assumptions sheet.

Layer 01

Assumptions

Every input with a value, source, and rationale. Empty source cells highlight amber.

Layer 02

Drivers

The math that turns assumptions into outputs. Lives inside the Scenarios output rows.

Layer 03

Scenarios

Downside / Base / Upside each vary specific assumptions — not all of them at once.

Layer 04

Outputs

Summary pulls from Scenarios by formula. Sensitivity stress-tests the driver math.

What's in the pack

Five tabs. Open them in order the first time and you'll see how the assumption register feeds the scenarios, and how both feed the summary.

Tab 01

Instructions

How the model works, the arithmetic rule, and how to adapt the driver formulas for operations, capacity, or cash forecasts.

Tab 02

Assumptions

Nine inputs — starting ARR, churn, expansion, pipeline, close rate, deal size, cycle, period, discount. Value, source, rationale, unit. Unsourced rows flag amber.

Tab 03

Scenarios

Downside / Base / Upside columns. Five varying inputs above, three computed outputs below, a total row, delta vs. base, and a one-sentence narrative for each world.

Tab 04

Summary

Executive one-view. The question, the base-case headline, the range, the three assumptions that move the number most, the narratives, the ask.

Tab 05

Sensitivity

Two-way grid — total revenue at five close rates × six pipeline levels. Cells more than 5% below base turn red; within ±5% stay tan; above +5% turn green.

The arithmetic rule, enforced by the file

Module 06's load-bearing constraint: do not trust Claude with the final math. This workbook makes that structural — there is no way to present a number from Summary that isn't wired, by formula, back through Scenarios to the Assumptions sheet.

How the model enforces it

Every output on the Summary tab is a cross-sheet reference — =Scenarios!D19, not a typed value. Every output on the Scenarios tab is a formula built from named ranges. Every named range points to a value cell on the Assumptions tab. Change an assumption, every downstream number recomputes. No hand-keyed outputs anywhere.

What the amber cells mean

Any row on the Assumptions tab with an empty Source cell turns the source cell amber. The rule: do not present this forecast until every source cell is filled. The starter file ships with one amber flag (discount rate / COGS impact) so you can see the pattern before you build your own.

What's wired in

Features that do work for you in the background. You don't have to know how they work to use them.

Source flag

Empty-source conditional formatting on the Assumptions tab. Rule: =LEN(TRIM(D6))=0 fills the cell amber. You see the gap at a glance; leadership sees a finished register.

Named

Workbook-level named ranges — start_arr, churn, expansion, new_pipe, close_rate, avg_deal, cycle, period_mo, discount. Scenarios and Sensitivity reference by name, so formulas read like the business: =start_arr*(1-(churn*period_mo/12)).

Red

Sensitivity: >5% below base. CellIsRule lessThan Scenarios!D{total}*0.95 fills red. The cells that would miss the Q2 plan.

Tan

Sensitivity: within ±5% of base. CellIsRule between 0.95× and 1.05×. The zone where the plan holds within normal variance.

Green

Sensitivity: >5% above base. CellIsRule greaterThan 1.05×. Upside cells — what has to happen to beat the plan, and by how much.

Formula

Summary headline is a formula, not a number. =Scenarios!D{total_row}. Change any assumption on Tab 02 and the executive headline on Tab 04 recomputes. Try it.

The driver formulas

For the revenue forecast as shipped. The structure of the workbook stays the same if you adapt it — only the driver formulas change. See the Instructions tab for operations and cash variants.

Output	Formula (base column)	What it's doing
New bookings	=D{pipe}D{close}period_mo	Pipeline × close rate × months in the forecast period.
Retained revenue	=start_arr(1-(D{churn}period_mo/12))	Starting ARR surviving the period's share of annual churn.
Expansion revenue	=start_arrD{exp}period_mo/12	Period's share of annualized expansion on the starting base.
Total period revenue	=SUM(D{bookings}:D{expansion})	The sum of the three output rows. The headline number.
Δ vs. Base (downside)	=C{total}-D{total}	Dollar gap below base. Negative number in red.
% of Base (upside)	=E{total}/D{total}-1	Upside as a percent of base. Answers "how much more than base?".
Summary · base case	=Scenarios!D{total_row}	Cross-sheet reference. Never a typed number. This is the arithmetic rule in one cell.
Sensitivity grid cell	=(pcrperiod_mo)+(start_arr(1-churnperiod_mo/12))+(start_arrexpansionperiod_mo/12)	Full driver math with two inputs varied (pipeline, close rate). Other inputs pulled from named ranges.

Named ranges (workbook-level)

These are the handles the Scenarios and Sensitivity sheets use. If you're adapting the model, change the value cell on the Assumptions tab — the name stays the same, every downstream formula still resolves.

start_arr

Starting ARR at the period open. Locked at prior-quarter-end.

churn

Annual churn rate, as a decimal.

expansion

Annualized expansion rate on the existing base.

new_pipe

New pipeline dollars added per month.

close_rate

Qualified-pipeline-to-won conversion rate.

avg_deal

Weighted average deal size. Reserved for unit-level analysis.

cycle

Median sales cycle in days. Used for pipeline-to-close timing assumptions.

period_mo

Months in the forecast period. The workbook ships with 3 (one quarter).

discount

Discount rate / COGS impact. Ships unsourced — flagged amber until you fill it in.

The two prompts that build it and present it

This is what you paste into Claude when you want a forecast for your operation. Two prompts, run in order, bracketing the Excel work.

Prompt 01 — Structure (before you open Excel)

Structure prompt · run first

ROLE: You are a senior FP&A analyst supporting an operations consultant. You build defensible forecasts with explicit assumptions and scenario discipline.

CONTEXT: [The operation, the decision this forecast supports, the audience who will see it.]

QUESTION: [One sentence. Should we, can we, or will we be okay?]

RAW INPUTS: [Trailing data, known constants, estimates — paste it all, unorganized is fine.]

TASK:
1. Draft an assumption register — every input with value, source, and a one-sentence rationale. Flag any assumption that lacks a source.
2. Write the driver logic — the math that turns assumptions into outputs. Use plain formulas I can paste into Excel (no pseudocode).
3. Define three scenarios — base, upside, downside. For each, list which assumptions move and why. Each scenario must reflect a plausible world, not three versions of optimism.
4. Specify the summary outputs the decision-maker needs.

STANDARD: Do not compute final numbers in your response — I will run the math in Excel. Give me the structure, the formulas, and the logic. Flag every assumption that's not defensible yet. Your narrative should be something I can present to a leadership team without rewriting.

Prompt 02 — Presentation (after you run the numbers)

Presentation prompt · after the Excel work is done

ROLE: You are a senior FP&A analyst. You translate forecast models into executive presentations.

CONTEXT: [The decision, the audience, the venue — leadership meeting, board, client.]

INPUT: [Paste: assumption register · scenario outputs from Excel · scenario narratives.]

TASK: Produce a one-page executive brief in BLUF format (Module 05 structure), plus three presentation slides:
1. The base case output, with the key drivers
2. The three scenarios, side-by-side, with one-sentence narratives
3. The recommendation and the ask

STANDARD: Lead with the answer. Every number must be traceable to the assumption register. If an assumption is weak, say so — do not paper over it. Your draft should be something I can present without rewriting, just revising.

The "do not compute" instruction is the guardrail

Both prompts obey the same rule. The structure prompt ends with "Do not compute final numbers in your response — I will run the math in Excel." The presentation prompt insists that every number must be traceable to the assumption register. Together they keep Claude doing the work Claude is good at (structure, logic, narrative) and keep Excel doing the work Excel is good at (arithmetic).

What to do next

The fastest way to turn this starter into a forecast you'd actually present:

Open the file. Start on the Summary tab — that's what leadership sees. Then walk backward: Scenarios, Assumptions. Note the amber flag on the Assumptions tab.
Rewrite the nine assumptions for your operation. Your ARR, your churn, your pipeline, your cycle. Fill every Source cell or the row stays amber.
Rewrite the three scenario narratives. Each one must describe a plausible world in one sentence. If you can't say what has to be true for upside, that's the work.
Verify two output numbers by hand. Pick a scenario, recompute its total with a calculator. If the numbers don't match, find the formula that's wrong before you present.
Run Prompt 02 with your assumption register, scenario outputs, and narratives pasted in. You'll get a BLUF brief and three slides — revise, don't rewrite.

That's the whole Module 06 deliverable. The forecast is defensible because every number traces to a sourced assumption — and Claude never touched the arithmetic.