---
name: forecast-builder
description: Build a bottom-up demand gen pipeline forecast with explicit assumptions, gap analysis, and scenario modeling
user_invocable: true
---

# Forecast Builder

Builds a pipeline forecast from the bottom up — shows the math, flags the weak assumptions, and produces a model Clay can present to leadership. No black boxes.

## Instructions

### Step 1: Collect inputs

Ask Clay for the following. If any are unknown, use the defaults listed — but flag every assumption explicitly in the output.

1. **Pipeline target** — quarterly or annual ($). This is the goal we're building toward.
2. **Marketing's pipeline contribution %** — what % of total pipeline is marketing responsible for generating? (e.g., 40%, 60%, 100% if self-serve). Default: 50%.
3. **Average deal size** — average new ARR per closed-won deal. Default: ask. Don't estimate without acknowledging it.
4. **Win rate** — % of opportunities that close. Default: 25%.
5. **Funnel conversion rates** — ask for as many as available:
   - MQL → SQL: default 20%
   - SQL → Opportunity: default 40%
   - Opportunity → Closed-Won: default 25%
   - Lead → MQL: default 30%
   - Visitor → Lead: default 2%
6. **Current monthly volume at top of funnel** — leads per month, or MQLs per month, or visitors per month — whatever level Clay has data for.
7. **Forecast period** — Q (13 weeks) or annual (52 weeks)?

Ask for these inputs before running the model. Don't guess at deal size or win rate — these are the two numbers that wreck forecasts.

---

### Step 2: Build the bottom-up model

**Work backwards from pipeline target to needed volume at each stage.**

Formula chain (quarterly):

```
Pipeline needed from marketing = Pipeline target × Marketing contribution %

Closed-won deals needed = Pipeline needed / Average deal size

Opportunities needed = Closed-won deals needed / Win rate (Opp → Close)

SQLs needed = Opportunities needed / SQL → Opp rate

MQLs needed = SQLs needed / MQL → SQL rate

Leads needed = MQLs needed / Lead → MQL rate

Visitors needed = Leads needed / Visitor → Lead rate
```

Then calculate the **current trajectory**:

```
Current quarterly run rate at [top of funnel stage] = Monthly volume × 3 months

Project downward through the same funnel:
  [Stage] = [Input] × [conversion rate]
  ...to pipeline
```

**Gap analysis:**

```
Pipeline gap = Pipeline target - Projected pipeline at current run rate
Volume gap at [top stage] = Needed volume - Current volume
```

---

### Step 3: Scenario modeling

Run three scenarios using the same model:

| | Base Case | Conservative (-20%) | Aggressive (+20%) |
|--|--|--|--|
| Input assumption | as provided | 20% lower volume | 20% higher volume |
| Pipeline projected | $X | $X | $X |
| Gap to target | $X | $X | $X |

For the conservative case, reduce top-of-funnel volume by 20%.
For the aggressive case, increase top-of-funnel volume OR improve the weakest funnel rate by 20%.

---

### Step 4: Produce the output

**Format:**

---

## Pipeline Forecast: [Period]

### Inputs & Assumptions

| Input | Value | Source | Confidence |
|-------|-------|--------|------------|
| Pipeline target | $X | Provided | — |
| Marketing contribution % | X% | [Provided / Assumed — flag if assumed] | [High/Medium/Low] |
| Average deal size | $X | [Provided / Assumed] | [flag] |
| Win rate (Opp → Close) | X% | [Provided / Assumed] | [flag] |
| MQL → SQL | X% | [Provided / Assumed] | [flag] |
| SQL → Opp | X% | [Provided / Assumed] | [flag] |
| Current monthly [stage] volume | N | Provided | — |

**Weakest assumptions (flag these explicitly):**
> "Win rate and average deal size have the most leverage on this model. A 5-point drop in win rate shifts the pipeline projection by $X. Validate these with sales before presenting."

---

### Bottom-Up Model

| Stage | Needed to Hit Target | Current Trajectory | Gap |
|-------|---------------------|--------------------|-----|
| Pipeline | $X | $X | $X |
| Closed-Won Deals | N | N | N |
| Opportunities | N | N | N |
| SQLs | N | N | N |
| MQLs | N | N | N |
| Leads | N | N | N |
| Visitors | N | N | N |

**Pipeline gap: $X ([X]% of target)**
**Top-of-funnel gap: [N] [stage] per month ([X]% increase needed)**

---

### Scenario Analysis

| Scenario | Pipeline Projected | vs. Target | What Changes |
|----------|-------------------|------------|--------------|
| Base Case | $X | +/- $X | Current run rate, current conversions |
| Conservative | $X | -$X | 20% lower volume (seasonality, budget cut, etc.) |
| Aggressive | $X | +$X | 20% volume increase OR conversion improvement |

---

### Leadership Narrative

[One paragraph Clay can present to leadership — plain English, no jargon, shows the math without showing all the work:]

> "To hit our $[target] pipeline goal this quarter, marketing needs to generate $[X] in sourced pipeline — [X]% of total. At our current run rate of [N] MQLs/month, we're on track for $[Y] — a $[Z] gap. Closing that gap requires either [X]% more volume at the top of the funnel, or improving [Stage] conversion from [X]% to [Y]%. The two assumptions with the most risk in this model are deal size ($[X]) and win rate ([X]%) — both should be confirmed with sales before this forecast is locked."

---

### What to Do With the Gap

If the gap is >20% of target:

> "A $[X] gap is material. Options: (1) Add budget/programs to increase top-of-funnel volume. (2) Improve mid-funnel conversion — if MQL→SQL is below 20%, fixing lead quality may close the gap faster than adding volume. (3) Reset the target with leadership if current programs can't support it. Pick one. Running all three simultaneously without prioritizing is how forecasts become fiction."

If the gap is <10%:

> "You're within striking distance. Focus on protecting mid-funnel conversion rates and maintaining current program velocity. Don't add new channels mid-quarter."

---

### Step 5: Flag model limitations

Always include this section:

**What this model does NOT account for:**
- Pipeline timing — not all MQLs generated this quarter will convert to pipeline this quarter. Account for lag (typically 30–90 days depending on sales cycle).
- Lead quality variation — conversion rates assume consistent lead quality. If top-of-funnel mix shifts (e.g., more inbound vs. outbound), rates will shift.
- Marketing-influenced vs. marketing-sourced pipeline — this model tracks sourced. If leadership uses influenced, define the methodology before presenting.
- Seasonality — if the forecast period includes Q4 end-of-year or summer slowdown, adjust volume expectations.