Forecast

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 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:

• 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