Building a Sales Pipeline Forecasting Model with Excel

Image by Editor | Midjourney
 

Sales pipeline forecasting is a critical process for businesses to predict future revenue based on the status of deals in the sales funnel. This model helps to predict, analyze, and track future sales which helps to focus on sales.  In this article, we will show how to build a sales pipeline forecasting model in Excel.

Step 1: Prepare Your Data in Excel

To build a forecasting model you must ensure structured sales data. Your sales pipeline should include the following data.

• Deal ID: Unique identifier for each deal.
• Deal Value: Estimated revenue from the deal.
• Sales Stage: Status of the deal (e.g., Prospecting, Negotiation, Proposal Sent, Closed Won, Closed Lost).
• Probability of Closing: Estimated likelihood of deal success based on historical data.
• Expected Close Date: The projected date when the deal will be finalized.

Calculated Field:

• Expected Revenue Based on Conversion Rate: Forecast revenue considering probability and deal value.
• Cumulative Weighted Revenue: Running total of weighted revenue over time.
• Estimated Close Date Category: Categorization of deals based on timeframes.
• Win/Loss Status: Indicates whether a deal is won or lost.

You can also include the client’s name, sales rep, industry, conversion rate, different stages, etc.

Step 2: Calculate Forecasting Fields

2.1. Expected Revenue:

• Select cell F2 and insert the following formula.
• Drag the formula down to apply it to the rest of the cells.

Formula:

=B2*D2

This formula multiplies deal value with the probability of returning the expected revenue.

2.2. Cumulative Weighted Revenue:

• Select cell G2 and insert the following formula.
• Drag down the formula to apply it to the rest of the cells.

Formula:

=SUM($F$2:F2)

This formula ensures that each row sums up all previous values, creating a cumulative total.

3.2. Estimated Close Date Category:

• Select cell H2 and insert the following formula.
• Drag down the formula to apply it to the rest of the cells.

Formula:

=IF(E2<=TODAY()+30, "Short-Term", IF(E2<=TODAY()+90, "Medium-Term", "Long-Term"))

This formula checks if the Expected Close Date is within the following ranges.

• 30 days >> Short-Term
• 31-90 days >> Medium-Term
• More than 90 days >> Long-Term

4.2. Win/Loss Status Calculation:

• Select cell I2 and insert the following formula.
• Drag down the formula to apply it to the rest of the cells.

Formula:

=IF(C2="Closed Won", "Won", IF(C2="Closed Lost", "Lost", "Open"))

This formula checks the Sales Stage to ensure the Win/Loss status.

• “Won” if the deal is Closed Won.
• “Lost” if the deal is Closed Lost.
• Other status will be “Open”.

Step 3. Insert Pivot Table to Forecast Sales Revenue

To project revenue per month you can use the Pivot Table.

• Select your data range.
• Go to the Insert tab >> select PivotTable.
• Select New Worksheet >> click OK

In the Pivot Table Field List:

• Drag the Expected Close Date in the Rows section.
• Drag Expected Revenue in the Values section.
• Drag Win/Loss Status in the Columns section.
• Drag the Sales Stage in the Filters section.

Step 4: Visualize the Forecast

• Select the pivot table data.
• Go to the PivotTable Analyze tab >> select PivotChart.

Output:

Revenue Distribution:

• Total Expected Revenue: $30,568
• Lost: $9,688
• Open: $10,480
• Won: $10,400

Key Insights:

•   February has the highest Won Revenue ($10,400).
•   March & April have many open deals, indicating future closures.
•   June & July show low revenue, signaling a pipeline gap.
•   The chart separates Lost, Open, and Won deals.

Step 5: Analyze Trends & Make Adjustments

You can analyze the accuracy of your sales pipeline forecast, track key metrics, and adjust based on real data insights.

Key Insights to Track:

• Pipeline Bottlenecks:  Analyze Expected Close Dates and Win/Loss Status to identify slow-moving deals. Deals that remain open beyond 60 days may indicate a slowdown.
• Conversion Rate Trends: Monitor the percentage of deals that move from open to Closed Won over time. Compare win rates across different sales stages to identify weak points.
• Revenue Forecast Accuracy: Compare Expected Revenue with the Actual Revenue from Won Deals. If actual revenue consistently falls short, probability estimates may need adjustment.

To Improve Forecast Accuracy:

• You can update conversion rates regularly based on historical Win/Loss data to refine revenue predictions.
• You can use the Close Date Category (Short-, Medium-, Long-Term) to understand trends in different time frames.
• Closely monitor Win/Loss ratios to evaluate the effectiveness of sales strategies and adjust the pipeline accordingly.

Conclusion

By following the above steps, you can build a functional sales pipeline forecasting model in Excel to estimate future sales based on pipeline data, conversion probabilities, and deal values. This model will help you to predict future values and identify pipeline performance to optimize sales strategies. With regular updates and refinements, it can be a powerful tool for decision-making in any sales-driven organization. You can apply further customization in the model based on your specific requirements.