Forecast Model Builder

---
name: forecast-model-builder
description: Use when building a revenue forecast model — bottoms-up rep capacity, funnel-based, or top-down market share. Triggers on "build a forecast", "annual plan", "quota model", "rep capacity model", "revenue forecast xlsx".
---

# Forecast Model Builder

Build a forecast that survives the "where does that number come from" question. The forecast that wins board approval is the one with a build the audience can poke at. Three cases, named driver, reconciliation to actuals.

## Required inputs

1. **Forecast horizon** — usually 12 months, monthly granularity
2. **Approach** — bottoms-up (rep capacity, funnel, accounts × ARPU) or top-down (market × share). Often you build both and reconcile.
3. **Last year actuals** — monthly revenue, segments, channels — for the reconciliation tab
4. **For rep-capacity build**: rep count by month, quota, ramp curve, attainment rate, attrition assumption
5. **For funnel build**: traffic, conversion to lead, lead-to-opp, win rate, cycle time, ACV
6. **For top-down**: market size, growth rate, share assumption with rationale
7. **Three cases** — base (planning number), upside (+15-25%), downside (-15-25%)

If the user only wants a single number with no case range, push back. A forecast without a downside is a plan, not a forecast.

## Approach

Use **openpyxl** with formulas. The forecast lives by being editable — analysts will drag scenarios sideways and stress-test inputs in the meeting.

For sensitivity tables, Excel's **Data Table** feature (one-input or two-input) is what you want — it's a live recompute, not a static grid. openpyxl can write the data-table reference; the user re-triggers the recalc on open.

## Workbook structure

1. **Cover** — case toggle (Base / Upside / Downside), forecast period, model owner, version
2. **Assumptions** — all drivers in named ranges. Each has THREE columns: Base, Upside, Downside. The active case pulls via INDEX/MATCH on the toggle.
3. **Last Year Actuals** — monthly actuals by segment/channel. The grounding truth. Do not skip this tab.
4. **Build** — the bottoms-up math. For rep-capacity: rows = reps (or rep classes), columns = months. `=Quota * Ramp_Pct[month] * Attainment`. Roll up to total bookings.
5. **Top-Down Check** (optional but recommended) — market × share, gives an envelope. If bottoms-up is 3x top-down, something is off — flag it.
6. **Forecast** — month-by-month forecast pulled from the active case. Side-by-side: prior year, base, upside, downside.
7. **Reconciliation** — bridge from last year to forecast. Categorize the delta: rep adds, productivity gains, pricing, segment expansion, churn improvement. Each line tied to an Assumption.
8. **Sensitivity** — Excel Data Table with two drivers (e.g., attainment × rep count) showing total bookings. The 3×3 grid investors love.
9. **Charts** — actuals vs forecast line, case bands, quarterly bookings stacked.

## Formula principles

- **Named ranges everywhere.** `Quota_AE`, `Ramp_M3`, `Win_Rate`. Don't reference `Assumptions!$C$14`.
- **Case toggle drives everything.** One cell on Cover (`Active_Case`). All driver lookups use `INDEX(BaseRange, MATCH(Active_Case, CaseList, 0))` or equivalent. Switch the toggle, the workbook flips.
- **Ramp curves as a lookup table**, not pasted percentages. `HLOOKUP` or `INDEX` against a Months-Since-Hire row.
- **Attainment as a distribution, not an average.** Sum of (rep × attainment band × probability) gives a more honest number than a flat 70%.
- **Don't multiply percentages of percentages without commenting.** `Traffic * CR * Lead_to_Opp * Win_Rate * ACV` — each is fine, but a 5-stage funnel where each stage is "about 30%" gives 0.24% — sanity check against actuals.

## Common pitfalls

- **No reconciliation to last year.** Numbers float, the board asks where the +60% comes from, you have no answer. The bridge tab is non-negotiable.
- **Cases that aren't materially different.** If upside is +5% and downside is -3%, you don't have cases — you have rounding. Spread should be ±15-25%.
- **Hardcoded ramp percentages in the build tab.** Move to a Ramp_Curve named range.
- **Forgetting attrition.** 12 reps at start ≠ 12 reps all year. Bake in 15-25% annual rep attrition.
- **Top-down only.** "TAM × share" without a bottoms-up check is a wish. Always cross-check.
- **Quarter-end stuffing in the forecast curve.** If actuals are lumpy in Q4, the forecast should be too — don't smooth into a flat line.

## Output

Generate the .xlsx and print: total bookings (base / upside / downside), the 3 most sensitive drivers (from the Sensitivity tab), and the bridge categories from the Reconciliation tab. End with one risk callout: "If [driver X] comes in 20% below assumption, the base case is $Y short — that's the swing factor to watch."