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:
- Executive summary — one-line forecast vs quota
- Tabular rep rollup — every rep's committed / best case / forecast vs quota
- 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 valueestimatedclosedate(DATE) - Expected closecloseprobability(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:
- Determine current quarter boundaries
- Query open opportunities for current user
- Segment by forecast category
- Calculate weighted totals
- 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:
- Query all reps reporting to current user (or team)
- Aggregate opportunities by owner
- Compare each rep to quota
- 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:
- Calculate Q2 date boundaries
- Query opportunities with Q2 close dates
- Analyze coverage and stage distribution
- 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
