There are a lot of ways to forecast sales and they all vary in accuracy and complexity. In this article, I will cover the two methods of forecasting sales that I like. One of them is what I use when I need a quick number or am creating a dashboard in a CRM. The other is one I use when I am trying to get the most accurate number.
Let’s dive right in!
To forecast sales you must choose between speed and accuracy.
As a general rule, the quicker the forecast the less accurate it is. With the exception being if you have a tool that does all the hard stuff for you. If you are reading this… I’m going to assume you don’t have a tool.
As mentioned above, there are two primary methods that I like to use when forecasting sales. The ACCURATE method, weighted pipeline and the QUICK method, deal stage forecasting.
The weighted pipeline method is my go-to when I need an accurate forecast. It takes longer but takes more variables into account.
The deal stage pipeline method is my quick method to forecast sales. It’s less accurate but can be good enough depending on how strong your historical data is.
Because you are reading an article about how to forecast sales, let’s cover the more accurate method first.
How to forecast sales with the weighted pipeline method (The ACCURATE Method)
The weighted pipeline method of forecasting sales requires you to have data on the different types of deals, how long they take to close, and their close rates. This calculation is longer because it requires you to consider more variables.
Let’s dive into the information you will need.
This is the most important aspect of this calculation. You must segment the deals that the sales team in working into categories. Sometimes this is obvious, you may already have the pipeline divided by company size, industry, or solution. If you don't, here is how you do it.
First, calculate the close rate across all of the deals. Do this with a previous month or quarter that is long enough in the past that most of the deals that are going to close would have closed already.
Now that you have that number, categorize all of those deals by company size, industry, or solution being sold. Whichever categorization has the most impact on close rate should be how you categorize the deals for the weighted pipeline method.
In Q1 there were 100 deals in the pipeline and 32 of them closed over a 4 month period. Giving us a close rate of 32%.
Let's divide those deals and calculate close rate by industry and see what it looks like.
Notice when we organize the deals by industry there is not that large of a difference between the close rates. The most notable is Ecommerce at 30% but with only 4/13 it doesn't tell us much.
Let's try dividing those deals and calculate close rate by company size and see if that gives us better categories.
Notice the clear segmentation here. Under 100 employees clearly has the highest close rate with 101-500 11% less and 501+ another 7% lower. This is clearly a better way to categorize our deals for this analysis.
Time frame to close a deal in each category
Knowing your categories is a great first step but in order for us to create the most accurate sales forecast possible, we will need to know how long a deal in each category typically takes to close (we call this “sales cycle”).
This is a simple calculation. Take your historical deal data and look how long it took for a deal to go from “created” to “closed.” Most CRM systems allow you to run a report to tell you this but you can do this in excel if yours doesn't.
We take our closed deal data, categorize it by company size, and calculate the average amount of days it takes to close a deal in that category. See below
Percent of deals that close in each category
We calculated the close rate in our example of how to identify your own categories. If you already had your categories you will need to calculate that now.
Calculating the close rate is simple, but you must decide what to use as a starting point. I like “deal created” as a starting point for calculating the close rate.
Simply take how many deals closed and divide it by how many total deals there was in the pipeline during that time frame.
Note: Make sure you choose a time frame that was far enough back so you have an accurate idea of the close rate. If you choose a recent month or quarter you may be looking at a lower close rate because there are still active deals in the pipeline.
Example forecast calculation
Here is the scenario. It’s halfway through Q4 and the CEO wants to know where you think the sales team is going to land against their quota. Let’s use the example data from above to forecast Q4.
Using that historical data and some sample data, let's calculate a forecast.
Example Sales Forecast
If you would like the calculator seen above, fill out the form below.
As you can see in the spreadsheet above, we calculate the estimated sales with the specific close rate for each category. After calculating forcasted deals we multiply that by the average deal size and total all of the categories together to get our estimated revenue.
Note: This is estimating revenue from your existing pipeline. It does not take into account any new meetings that will be booked between the date of the analysis and the end of the quarter. If that would influence your sales forecast, take it into account.
How to forecast sales with the deal stage method (The QUICK Method)
This next method of sales forecasting is quick but lacks the number of variables necessary to be as accurate as the weighted pipeline method. That being said, depending on the accuracy of your historical data, this method will get you close.
Let’ take a look at the information you need for this calculation
To use the deal stage method you must assign a probability that a deal in each specific deal stage will close. You should have this built out in your CRM system but let’s use the following stages for this example.
Close rate by deal stage
Ah, more math… fun! The next data point you need is the probability that a deal will close thats in each deal stage. You can find this by looking at historical data and calculating how many deals were won vs. lost at each specific stage.
Here is some sample data we will be using.
That’s all the data you need for this! Simple, I know. Let’s run this forecast method with the same example data we used in the weighted pipeline example.
Active deals in the pipeline: 230
First, we must count how many deals are in each one of these stages.
Now let’s calculate the expected wins (how many will close).
Finally, let's put in the calculator to get our forecast.
If you would like the calculator seen above, fill out the form below.
The process of forecasting sales
Forecasting sales is an important part of business planning. It is also a very stressful part. Nothing is ever guaranteed and when forecasting sales and that’s ok. Your job is to give leadership the best possible guess that you can muster. There are a lot of ways to do it, some are better than others.
The two methods of forecasting sales we covered in this article are both respected ways to forecast sales. Give them a try, report your forecast to leadership, and get back to running an effective sales team.
Forecasting sales doesn’t make deals close. Effective leadership and sales process does, so focus most of your energy on that.