Sales forecasting is basically a process of projecting the sales figure of a company for a particular time period in the near future. It helps companies make informed business decisions and allocate internal resources at the right time. Sales forecasting can be easier for companies with couple of years of sales, as it uses educated guesses by taking into account the growth drivers, past sales performance, expectations. Although there are various techniques for projecting sales, we’ll go through the two most used techniques:
- Weighted Sales Forecast
- Regression Analysis
In this post weighted forecasting will be covered and in the next post regression analysis will be covered. We’ll do everything using an Excel sheet.
Weighted Sales Forecast
The easiest way to calculate the probability of a deal’s closure is to look at its presence in the sales pipeline. Say for example the sales pipeline is formed by the following sales stages:
- Lead
- Contact Initiated
- Needs Identified
- Proposal Sent
- In Negotiation
Whether a deal will finally be won or lost depends on the stages it has covered in the pipeline. So the probability of closing a deal that is present in the “Proposal Sent” stage is higher than a deal present in “Lead” stage. To implement this forecasting technique, you would need solid understanding of your sales conversions and figure out the probability of winning a deal from different sales stages. If your company is winning 3/4th of the deals present in “Proposal Sent” stage, then you have 75% probability of winning. This 75% would be considered as the weight of that stage and similarly you’d need to figure out the winning probability of each sales stage.
Weighted Forecasting with Excel
We’ve created an Excel sheet template for forecasting. All you need to do is plug in your current deal value and probability of closing a deal to get the final forecast value. Download the template by clicking on the image given below for better understanding.
Step 1:
In the column I and J you will see the following:
| Deal Stage | Closure Probablity |
| Lead | 15% |
| Contact Initiated | 25% |
| Needs Identified | 50% |
| Proposal Sent | 75% |
| In Negotiation | 90% |
Here you need to update the probability as per your business.
Step 2:
In the column E you’ll need to fill up the deal value for each each month and sales stage. Let’s assume the following about the deals whose expected close date falls in February:
- Deals with cumulative value of $50,000 are present in “Lead Generation” stage
- Deals with cumulative value of $40,000 are present in “Contact Initiated” stage
- Deals with cumulative value of $35,000 are present in “Needs Identified” stage
- Deals with cumulative value of $25,000 are present in “Proposal Sent” stage
- Deals with cumulative value of $20,000 are present in “In Negotiation” stage
Here is how it would look in the excel sheet:
The last column shows the weighted forecast by considering the probability of success. After entering the deal value for each month, you’ll get quarter wise weighted forecast value and a grand total for the entire year. That’s it!
In the next part of this post, we’ll discuss regression analysis.
CRM apps like TeamWave can help you easily find out the probability of winning from different sales stages. The image given below shows the deal advancement rate from one stage to the next stage.
Using simple mathematics (won deals/total deals in a stage) you can find out the final conversion percentage of deals from each sales stage.