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