Sales Funnel Conversion Model
Maps conversion rates at each stage of the sales funnel from initial lead to closed deal. Identifies where prospects drop off, quantifies stage-by-stage attrition, and predicts revenue from the current pipeline.
Sales - Sales Funnel Conversion Model.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- No visibility into where prospects drop off in the sales process
- Inability to predict revenue from the current pipeline with any accuracy
- Difficulty identifying which funnel stage needs improvement most urgently
- No way to model how improving a single stage conversion rate affects total revenue
- Sales forecasts disconnected from actual funnel performance data
Who This Is For
- Sales managers monitoring pipeline health
- Revenue operations teams building forecast models
- VP of Sales reporting to executive leadership
- Sales enablement teams targeting training investments
Inputs
- textStage Name
- #Leads Entering Stage
- %Conversion Rate to Next Stage
- #Average Days in Stage
- $Average Deal Size
Outputs
- Leads at Each Stage
- Drop-Off Count per Stage
- Overall Funnel Conversion Rate
- Predicted Closed Deals
- Predicted Revenue
- Biggest Drop-Off Stage
How Calculations Work
Starting from the top of the funnel, the model multiplies the number of leads entering each stage by the stage conversion rate to calculate how many advance. Drop-off is the difference. This cascades through all stages. Predicted closed deals equal the leads surviving all stages, multiplied by average deal size for predicted revenue. The stage with the largest absolute drop-off is flagged as the priority improvement area.
Example Use Case
Scenario: Four-stage funnel: 1,000 leads, 40% to qualified, 50% to proposal, 30% to negotiation, 60% to closed. Average deal size: $10K.
Result: Qualified: 400. Proposal: 200. Negotiation: 60. Closed: 36. Overall conversion: 3.6%. Predicted revenue: $360K. Biggest drop-off: Lead to Qualified (600 lost). Improving that stage by 10 points would add $90K in predicted revenue.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
How many funnel stages should I model?
Use your actual sales process stages. Typically 4-6 stages. Too few obscures where problems occur. Too many creates noise. Map it to your CRM pipeline stages.
Should I use historical or current conversion rates?
Use the trailing 3-6 month average for stability. Current month data alone is too volatile for reliable modeling.
How do I calculate the predicted revenue figure?
It multiplies the predicted number of closed deals (leads surviving all stages) by the average deal size. It is a probability-weighted forecast, not a guarantee.
What does the improvement impact analysis show?
It models what happens if you improve each stage's conversion rate by a set increment (default 5 percentage points) and shows the resulting change in closed deals and revenue.
Can I model different funnels for different products?
Yes. Create separate entries in INPUT for each product funnel or duplicate the template for each product line.
Download Sales Funnel Conversion Model
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.