How to Create a Sales Forecasting Model — Webinar Recap

Sales forecasting, when done right, can help companies predict revenue, plan budgets, map out hiring plans and much more. Teams that manage to accurately model it often grow faster than companies that don't. The problem? Too few companies take the time to properly forecast their sales, or do so using inaccurate or incomplete data models. The good news is that there’s a better, simpler way to do this right.

On June 8th 2022, Christian Sassano, a Data Analyst on the Solutions Engineering team at Weld, went live to talk about better Sales Forecasting practices, and how to craft a forecasting model using Weld’s in-house methodology. The enthusiastic audience asked pointed, insightful questions and the session was all around engaging and informative. Here’s a recap of everything that was covered — the recording is a little further down if you want to tune in!

Heads up: While executing the model does require knowledge of SQL, there are still plenty of learnings from the session for those who don’t have this technical skill set.

The difference between a forecast and a plan

Christian launched the session by clearly defining what he sees as the difference between a forecast and a plan. While these two ideas intersect, and are often used interchangeably, it’s important to make the distinction. Here’s how he defines each one:

• A forecast is your best attempt to predict the future based on the data you have up to now.
• A plan is grounded in the forecast, but also incorporates your vision of the future.

Both of these concepts are important, and forecasting and strategic planning are each an essential part of a successful business, Christian elaborated:

“You have to have both things. You have to have a strong understanding of the present moment and you need to be grounded in reality. But you also need to be dreaming big, have a vision for the future, and believe in yourself and in your team that you can do things that you can’t currently do — and that you can do things that nobody else can do.”

– Christian Sassano, Solutions Engineer at Weld

It’s important to find a balance between both the reality of the current situation, and the aspirations of what’s to come. But to have that balance, there needs to be a comprehensive understanding of what the current reality really looks like — and that’s the ultimate goal of the methodology outlined in this webinar.

What to expect from your forecast

According to Christian, there are two core things you can expect from your forecast:

1. A clear view of your pace in relation to your forecast.
2. Up-to-date predictions based on your most recent data.

Measuring your performance against your forecast helps you understand whether you’re on track with your projection. And as your actual performance is measured, your forecast will adjust to reflect the variance and keep your predictions up-to-date based on your most recent data.

Ready to tune in? Watch the full-length playback below!

What you need to create your forecasting model

So, what information do you need to create an accurate sales forecast for your business? Christian shared the table and columns you’ll need to create your model. The column inputs are as follows:

• Prospect ID
• Date Qualified
• Date Won
• Is Won
• Days to Won

Ready to get started? Go ahead with Christian’s complete SQL sales forecasting model, made intentionally to be customizable to a variety of contexts. With the table above, you’ll have what you need to get started with the model.

The important thing to note here is that these inputs may have different meanings depending on your business model and sales funnel. In particular, ‘Date Qualified’ will be up to you to define, because how you qualify your customers is unique to your company’s specific context. In fact, you may even have more than one way of qualifying your customers. Christian encourages you to spend some time thinking this over, saying:

“You have to have some level of intent. For us at Weld that means when a meeting has been booked, if we see them as a potential customer, we create a deal, and we assign them to someone they’ll continue to interact with down the line. That deal created date tends to be what we use, because it implies that someone on our team is actively working to convert that person.”

– Christian Sassano, Solutions Engineer at Weld

Applying the methodology in a variety of contexts

Christian clarified that while this approach is typically used for sales teams, it can also be used in other contexts. In a commerce context, the model can be used to forecast sales using a first interaction with the web store or initial visit to the brick and mortar shop as the qualified date. Or, it can be used to forecast returning customers by using the first purchase as the qualified date, and the second purchase as the one date.

3 Key things the forecast model accounts for

Beyond the basic inputs, Christian clarified the three most important factors that his model accounts for — and that any strong one should. Here, we break each one down to give you a peek under the hood of what goes into the forecast.

1. The length of your sales funnel

Getting clear on the length of your sales funnel is essential for building an accurate forecast. You always want to look at your funnel from a cohort perspective, using the date prospects entered your sales funnel as your reference point. To do this, you’ll need a table with your Prospect ID and Days to Won inputs. With this table, you’ll be able to create two crucial visualizations of your sales funnel.

The first graph, with ‘days to won’ on the x axis and ‘number of customers’ on the y axis, shows the total number of customers won each day after entering your funnel. It may show trends like a bell curve (such as the graph above), bi- or multi-modal distribution, a more uniform distribution, or a skewed left or right distribution.

The second graph is cumulative, with ‘days to won’ on the x axis and ‘percentage won customers’ on the y axis. This will give you a representation of the percentage of customers who have converted as the number of days increases. Depending on your data, the graph may not have as smooth a curve as the one above — it could be a more gradual increase, and even balance out in some areas.

The length of your sales funnel won’t be represented by just one number. But, both of these graphs will help you give clear, quantifiable estimates, like:

• 50% of our customers are won within 30 days.
• 80% of sales take between 20 and 40 days to close.

Pro tip: If you have various types of customers with different sales funnels, you can account for this with an additional column for ‘Customer Type’ in your model’s inputs table. This way, you can run your forecast for each type of customer. A bi-modal distribution on your sales funnel graph could be an indication that you have multiple customer types.

2. Your overall win rate

The next key piece of information you’ll need to accurately forecast your sales is your overall win rate. When you look at your sales funnel from a cohort view, you’ll likely run into a common problem: your win rate will drop significantly as it approaches the current moment in time. This is because you’ll have several prospects in your funnel who have yet to convert.

“You have to give your prospects time to convert. And if you do this, every time you look at your win rate per month up to the current date, it will look like it just fell off a cliff.”

– Christian Sassano, Solutions Engineer at Weld

So how do you account for this, and come up with an accurate overall win rate? Well, the time from this “drop off point” to the current day should be around the same length as one sales funnel, as described in the previous step. Based on this, you can remove the most recent data that represents those prospects who are still mid-funnel. And from there, you can calculate your win rate based on the data that remains.

“We’re not talking about removing any data from our forecast — we’re only trying to come up with a clear estimate that can be used, and that can be defended.”

– Christian Sassano, Solutions Engineer at Weld

Ultimately, the goal is to have one single number for your overall win rate, and Christian’s out-of-the-box model simply takes the average of the remaining data. However, it’s adjustable to account for certain variables like seasonality so you can get an estimate that makes sense for your context.

3. The total volume of your pipeline

Finally, to produce your sales forecasting model, you’ll need to estimate the total volume you can expect in your pipeline in the future, or how many prospects you can expect to enter your funnel each day. This way, you can apply the same sales funnel length and win rate expectations you have of your current prospects onto your future prospects.

Christian’s model does this with a basic average volume, calculated from past results over a specific period of time. The time period selected will depend on your context, but could for example be:

• The 90 days leading up to the start of the forecast period.
• The same time period of your forecast the year prior.

“Whatever the case is, I do believe that you should give yourself a clear, singular line to work with. Simple estimates make clear assumptions.”

– Christian Sassano, Solutions Engineer at Weld

In other words, you’re looking for an estimate that’s based on the actual volume you’ve seen in the past — and again, this can (and should) be represented by a single number. While you might plan for growth, your volume estimate must be grounded in the actual volume you’ve seen, rather than your ambitions to increase your volume.

Seeing the bigger picture: how to use your forecast

As the session wrapped up, Christian walked through the various things your forecast can be used for. First and foremost, your forecast will help you identify whether you’re speeding up, slowing down, or right on track. But because the forecast accounts for your sales funnel, overall win rate, and total pipeline volume, it also helps you understand where any variance is coming from.

For example: You might notice that your win rate is lower than your projection. But before you double down on closing calls, you might also see that your volume has increased and your funnel length has expanded — and these factors are making it hard for your sales team to keep up and effectively close deals.

This is the kind of intel that can really inform your business strategy and help you make smart decisions about where to focus your teams’ efforts. And this brought the conversation back to the distinction between a forecast and a plan — your forecast should act as a baseline, while your plan should reflect your goals. So once you’ve created a forecast and monitored your results, you’ll be equipped with the insights to craft your plan.

Three assumptions your forecast helps you monitor:

1. Your top-funnel assumption: You’ll be able to see whether you’re bringing in the volume you’ve anticipated based on prior performance.
2. Your win rate assumption: You can compare the win rate you calculated with the actual win rate you’ve achieved over the time period.
3. Your funnel length assumption: Likewise, your sales funnel may differ from your forecast — just remember to ask yourself whether there’s a chance that changes in your customer base could be impacting this variance.

Now that you have a deeper understanding of the methodology, you’re ready to move ahead with Christian’s complete SQL sales forecasting model. It was intentionally made to be customizable, so you should be able to use it for your own unique forecasting purposes!

Curious to learn more about how Weld can bring your core data operations into one central place? Sign up free to get started.