@microsoft/forecast

---
name: forecast
description: Generates a weighted sales forecast from Dataverse opportunity data. Calculates committed, best-case, and pipeline views by rep and team; compares to quota; flags risks and upside. Use when user asks "what's my forecast", "quarterly forecast", "pipeline forecast", "where's my number", "forecast report", "am I going to hit quota", "sales projection", or "revenue forecast".
metadata:
  author: Dataverse
  version: 1.0.0
  category: sales-analytics
---

# Forecast

Sales forecasting requires aggregating pipeline data across reps, applying probability weighting, and identifying where the number is at risk or has upside. This skill automates that process from Dataverse opportunity records — producing a structured forecast with committed, best-case, and pipeline totals broken down by owner and by forecast category, with risk and upside annotations.

## Instructions

### Step 1: Define Forecast Parameters
Accept input from the user:
- **Period:** Current quarter (default), next quarter, current month, or custom date range
- **Scope:** Individual rep, team/manager rollup, or full organization
- **Owner filter:** Specific systemuserid, team, or all
- **Currency:** Use organization default

Calculate period boundaries:
- **Current quarter start/end** based on today's date and fiscal calendar
- **Period start:** `[quarter_start]T00:00:00Z`
- **Period end:** `[quarter_end]T23:59:59Z`

#### Step 2: Fetch Open Opportunities in Period
```
SELECT opportunityid, name, estimatedvalue, estimatedclosedate, closeprobability,
       salesstage, msdyn_forecastcategory, ownerid, customerid, accountid,
       budgetstatus, decisionmaker, need, purchasetimeframe, purchaseprocess,
       createdon, modifiedon, description
FROM opportunity
WHERE statecode = 0
AND estimatedclosedate >= '[period_start]'
AND estimatedclosedate <= '[period_end]'
ORDER BY ownerid, estimatedvalue DESC
```

Apply owner filter if specified.

#### Step 3: Fetch Already-Closed Deals in Period
**Won this period (actuals):**
```
SELECT opportunityid, name, estimatedvalue, actualclosedate, ownerid, customerid
FROM opportunity
WHERE statecode = 1
AND actualclosedate >= '[period_start]'
AND actualclosedate <= '[period_end]'
ORDER BY ownerid, actualclosedate DESC
```

**Lost this period (for win rate context):**
```
SELECT COUNT(opportunityid) as lost_count, SUM(estimatedvalue) as lost_value, ownerid
FROM opportunity
WHERE statecode = 2
AND actualclosedate >= '[period_start]'
AND actualclosedate <= '[period_end]'
GROUP BY ownerid
```

#### Step 4: Segment by Forecast Category
Group open opportunities by `msdyn_forecastcategory`:

**Forecast category values:**
| Code | Label | Description |
|------|-------|-------------|
| 100000001 | Pipeline | Early stage, uncertain |
| 100000002 | Best Case | Possible with favorable conditions |
| 100000003 | Committed | Rep has high confidence in close |
| 100000004 | Omitted | Excluded from forecast |
| 100000005 | Won | Already closed won (use for actuals) |
| 100000006 | Lost | Already closed lost |

For each category, calculate:
- Count of opportunities
- Total value (sum of estimatedvalue)
- Weighted value (sum of estimatedvalue × closeprobability / 100)

Run separate queries per category:

**Committed:**
```
SELECT COUNT(opportunityid) as count, SUM(estimatedvalue) as total,
       ownerid
FROM opportunity
WHERE statecode = 0
AND msdyn_forecastcategory = 100000003
AND estimatedclosedate >= '[period_start]'
AND estimatedclosedate <= '[period_end]'
GROUP BY ownerid
```

Repeat for Best Case (100000002) and Pipeline (100000001).

#### Step 5: Calculate Weighted Forecast
For each opportunity, compute:
- **Committed:** Use full estimatedvalue (rep has high confidence)
- **Best Case:** estimatedvalue × closeprobability / 100
- **Pipeline:** estimatedvalue × closeprobability / 100

**Total forecast by rep:**
- Forecast = Won (actuals) + Committed (full value) + Best Case (weighted)
- Upside = Pipeline weighted value above Committed + Best Case
- Gap to quota = Quota − Forecast (if quota data available)

**Note:** Quota data may be stored in `msdyn_quotas` or `msdyn_forecastconfiguration` if Sales Insights forecasting is enabled. Query if available:
```
SELECT msdyn_forecastdefinitionid, msdyn_forecastdefinitionname, msdyn_quotasource
FROM msdyn_forecastdefinition
WHERE statecode = 0
```

If quota is not in Dataverse, accept as a user-provided input.

#### Step 6: Check Forecast Accuracy Signals

For each deal in Committed category, validate:

**Red flags (may deflate number):**
- No activity in last 7 days (check activitypointer)
- Close date in current week but stage is Qualify or Develop
- Budget not confirmed (budgetstatus = 0 or 1)
- Decision maker not identified (decisionmaker = false/null)
- Close date has slipped before (compare createdon to estimatedclosedate gap)

**Upside signals (may inflate number):**
- Deal in Best Case with strong qualification (BANT score ≥ 8/10)
- Deal with recent positive activity (meeting or call in last 3 days)
- Accelerated stage velocity (moved two stages in less than 2 weeks)

#### Step 7: Calculate Activity Recency for Each Deal
For each Committed and Best Case opportunity:
```
SELECT TOP 1 activityid, activitytypecode, actualend, subject
FROM activitypointer
WHERE regardingobjectid = '[opportunityid]'
AND statecode = 1
ORDER BY actualend DESC
```

Flag if last activity > 7 days ago for Committed deals.

#### Step 8: Generate Forecast Report

```
SALES FORECAST REPORT
Period: Q[n] [Year] ([Start Date] – [End Date])
Scope: [Rep Name / Team / Organization]
Generated: [Today's Date]
═══════════════════════════════════════════════════════════

PERIOD SUMMARY
───────────────────────────────────────────────────────────
Quota:            $[quota]
Won (Actuals):    $[won_value]    ([n] deals)
Committed:        $[committed]    ([n] deals)
Best Case:        $[best_case_w]  ([n] deals, weighted)
Pipeline:         $[pipeline_w]   ([n] deals, weighted)
─────────────────────────────────────────────
Total Forecast:   $[won + committed + best_case_weighted]
Upside Potential: $[pipeline_weighted]
Forecast vs Quota: [n]% | [Gap: $X above/below]

FORECAST BY REP
───────────────────────────────────────────────────────────
Rep Name          | Quota    | Won     | Commit  | Best    | Fcst    | vs Quota
[Rep 1]           | $[quota] | $[won]  | $[com]  | $[bc]   | $[tot]  | [+/-n]%
[Rep 2]           | $[quota] | $[won]  | $[com]  | $[bc]   | $[tot]  | [+/-n]%

COMMITTED DEALS ([n] deals, $[value])
───────────────────────────────────────────────────────────
🟢 [Deal Name] — $[Value] | [Stage] | Close: [Date] | Rep: [Name]
   Last activity: [n] days ago | BANT: [score]/10

⚠️  [Deal Name] — $[Value] | [Stage] | Close: [Date] | Rep: [Name]
   ⚠️ No activity in 14 days | Budget not confirmed

BEST CASE DEALS ([n] deals, $[weighted_value] weighted)
───────────────────────────────────────────────────────────
[Deal Name] — $[Value] ([prob]%) = $[weighted] | [Stage] | Close: [Date]
...

PIPELINE (Unweighted: $[value] | Weighted: $[weighted])
───────────────────────────────────────────────────────────
[n] deals in pipeline category for period
Top 3 by value: [Deal 1], [Deal 2], [Deal 3]

FORECAST RISKS
───────────────────────────────────────────────────────────
🔴 [Deal Name] — $[Value] | Committed but no activity in [n] days
🔴 [Deal Name] — $[Value] | Close date today, stage = Qualify
🟡 [Deal Name] — $[Value] | Budget unconfirmed, in Committed

UPSIDE OPPORTUNITIES
───────────────────────────────────────────────────────────
⬆️  [Deal Name] — $[Value] | Best Case, BANT 9/10, meeting 2 days ago
⬆️  [Deal Name] — $[Value] | Pipeline, strong qualification, close date pull-in possible

RECOMMENDED ACTIONS
───────────────────────────────────────────────────────────
1. [Rep Name] — call [Deal Name] today; no activity in [n] days, closes this week
2. Move [Deal Name] from Committed to Best Case — budget not confirmed
3. Accelerate [Deal Name] — strong signals, could be a pull-in to this quarter
═══════════════════════════════════════════════════════════
```

### Output Format
Deliver a three-part output:
1. **Executive summary** — one-line forecast vs quota
2. **Tabular rep rollup** — every rep's committed / best case / forecast vs quota
3. **Deal-level detail** — Committed and Best Case deals with health signals, risks, and upside

### Example Interaction

**User Input:**
"Generate the Q1 2026 forecast for the West team."

**Skill Output:**
```
SALES FORECAST — Q1 2026 | West Team
═══════════════════════════════════════════════════════════
Quota: $1,200,000
Won (Actuals): $380,000 (32% attainment)
Committed: $420,000 | Best Case: $180,000 (w) | Pipeline: $95,000 (w)
Total Forecast: $980,000 — 82% of quota
Gap to close: $220,000

FORECAST BY REP
─────────────────────────────────────────────────────
Sarah J.  | $400K quota | $150K won | $180K commit | Fcst: $370K | 93%
Mike P.   | $400K quota | $120K won | $140K commit | Fcst: $295K | 74% ⚠️
Lisa C.   | $400K quota | $110K won | $100K commit | Fcst: $245K | 61% 🔴

RISKS
🔴 Northwind ($85K, Committed) — no activity 12 days, close date March 3
🟡 Fabrikam ($42K, Committed) — budget unconfirmed

UPSIDE
⬆️ Alpine Ski ($65K, Best Case) — BANT 9/10, meeting yesterday
```

### Dataverse Tables Used
| Table | Purpose |
|-------|---------|
| `opportunity` | Pipeline data, categories, and values |
| `activitypointer` | Activity recency per deal |
| `account` | Account name for display |
| `systemuser` | Rep names and rollup |
| `msdyn_forecastdefinition` | Quota data (if Sales Insights enabled) |

### Key Fields Reference
**opportunity:**
- `estimatedvalue` (MONEY) - Deal value
- `estimatedclosedate` (DATE) - Expected close
- `closeprobability` (INT) - Win probability % (0-100)
- `msdyn_forecastcategory` (CHOICE) - Pipeline(100000001), Best Case(100000002), Committed(100000003), Omitted(100000004), Won(100000005), Lost(100000006)
- `salesstage` (CHOICE) - Qualify(0), Develop(1), Propose(2), Close(3)
- `statecode` (STATE) - Open(0), Won(1), Lost(2)
- `ownerid` (LOOKUP) - Assigned rep (systemuser)

### Configurable Parameters
- Forecast period (default: current quarter)
- Committed weighting method (full value vs probability-weighted)
- Activity recency alert threshold for Committed deals (default: 7 days)
- Minimum deal size to include in report (default: $0)
- Quota source (Dataverse table or user-provided input)
- Coverage ratio warning threshold (default: below 3x pipeline to quota)

## Examples

### Example 1: Personal Forecast

**User says:** "What's my forecast for this quarter?"

**Actions:**
1. Determine current quarter boundaries
2. Query open opportunities for current user
3. Segment by forecast category
4. Calculate weighted totals
5. Flag risks and upside

**Result:**
```
Q1 2026 FORECAST - Sarah Johnson

QUOTA: $400,000

ACTUALS (Won): $150,000 (38%)
COMMITTED: $180,000
BEST CASE: $65,000
PIPELINE: $120,000

WEIGHTED FORECAST: $370,000 (93%)
GAP TO QUOTA: $50,000

RISKS:
🟡 Northwind ($85K) - no activity 12 days
🟡 Fabrikam ($42K) - budget unconfirmed

UPSIDE:
⬆️ Alpine Ski ($65K) - BANT 9/10, strong momentum
```

### Example 2: Team Forecast Rollup

**User says:** "Show me the team forecast"

**Actions:**
1. Query all reps reporting to current user (or team)
2. Aggregate opportunities by owner
3. Compare each rep to quota
4. Generate team summary with risk indicators

**Result:**
```
Q1 2026 TEAM FORECAST

TEAM TOTAL: $1.2M quota | $910K forecast | 76%

BY REP:
| Rep      | Quota  | Won    | Commit | Fcst   | %   |
|----------|--------|--------|--------|--------|-----|
| Sarah J. | $400K  | $150K  | $180K  | $370K  | 93% |
| Mike P.  | $400K  | $120K  | $140K  | $295K  | 74% ⚠️|
| Lisa C.  | $400K  | $110K  | $100K  | $245K  | 61% 🔴|

AT RISK: Lisa C. needs $155K more to hit quota
COVERAGE: Mike P. has only 2.1x pipeline (below 3x threshold)
```

### Example 3: Next Quarter Planning

**User says:** "What's the pipeline look like for Q2?"

**Actions:**
1. Calculate Q2 date boundaries
2. Query opportunities with Q2 close dates
3. Analyze coverage and stage distribution
4. Generate forward-looking view

**Result:**
```
Q2 2026 PIPELINE PREVIEW

OPEN PIPELINE: $850,000 (32 deals)

BY CATEGORY:
Committed: $120K (early commits)
Best Case: $280K
Pipeline: $450K

STAGE DISTRIBUTION:
| Stage    | Count | Value  |
|----------|-------|--------|
| Qualify  | 12    | $180K  |
| Develop  | 14    | $420K  |
| Propose  | 6     | $250K  |

COVERAGE: 2.1x to Q2 quota ($400K)
⚠️ ALERT: Need $350K more qualified pipeline by April 1
```

## Troubleshooting

### Error: Quota data not available
**Cause:** msdyn_forecastdefinition table not configured or empty
**Solution:**
- Ask user to provide quota amount manually
- Display forecast totals without % to quota
- Note that Sales Insights forecasting not enabled

### Error: Forecast categories not populated
**Cause:** Reps not setting msdyn_forecastcategory on opportunities
**Solution:**
- Fall back to probability-based categorization
- >80% = Committed, 50-80% = Best Case, <50% = Pipeline
- Recommend enabling forecast category enforcement

### Error: Stale pipeline data
**Cause:** Many deals have old modifiedon dates
**Solution:**
- Flag deals not updated in 30+ days
- Recommend pipeline hygiene review
- Apply discount factor to stale deals in weighted calc