Excel for Sales: Pipeline Tracking, Forecasting, and Reporting

Sales Pipeline Tracker Structure

Essential pipeline columns:

• Opportunity name and account
• Sales rep (owner)
• Deal value and stage
• Probability percentage
• Expected close date
• Weighted value (=DealValue * Probability)
• Days in stage

Weighted Pipeline Calculations

Calculate weighted pipeline by stage:

=SUMPRODUCT((Stage=“Proposal”)*(DealValue)*(Probability))

Total weighted pipeline for a rep:

=SUMIFS(WeightedValue, Rep, “John Smith”, CloseDate, “<=”&QuarterEnd)

Sales Forecasting

Simple forecast models in Excel:

• Commit: Sum of 90%+ probability deals
• Best case: Sum of 50%+ probability deals
• Pipeline: Sum of all weighted values

For trending, use FORECAST.LINEAR:

=FORECAST.LINEAR(NextMonth, HistoricalSales, MonthNumbers)

Commission Calculations

Tiered commission example:

=IF(Sales<=50000, Sales*0.05, IF(Sales<=100000, 2500+(Sales-50000)*0.08, 6500+(Sales-100000)*0.10))

Or use a lookup table with XLOOKUP for cleaner maintenance:

Create tiers: $0-50K at 5%, $50K-100K at 8%, $100K+ at 10%

Activity Tracking

Track leading indicators:

• Calls made per day/week
• Meetings scheduled and completed
• Proposals sent
• Conversion rates between stages

Calculate conversion: =COUNTIF(Stage,“Won”)/COUNTIF(Stage,“Proposal”)

Territory and Quota Tracking

Quota attainment: =(ActualSales/Quota)*100

Remaining to quota: =MAX(0, Quota-ActualSales)

Use conditional formatting to highlight reps below 80% attainment.

Official Resources

• COUNTIFS function — count with multiple criteria
• SUMIFS function — sum with multiple criteria