May 12, 2021

Written by

Collective[i] Team

  • Posted in
  • Sales Forecasting
  • Sales Forecasting Process

How to forecast sales using historical data

Sellers often wonder how to forecast sales growth, and there are many different methods that can be used. Utilizing historical data in forecasting can be extremely useful in determining accurate sales forecasts, but also has some limitations.

There are many types of sales forecasting techniques in use today. Let’s look at forecasting using historical demand data and examine the benefits and downsides to these methods.

How do you forecast sales using historical data?

Forecasting sales using historical data involves applying a chosen forecasting model to existing data sets to project future sales. There are many ways of collecting this data and deciding how to interpret it. Here are two of the most common approaches.

Units and revenue method

This is a fairly straightforward way to utilize historical data to come up with an estimate for future revenue. In order to figure out the dollar amount of future sales, forecasting tools analyze historical sales data to figure out the rate of change in sales over time. There are typically parameters for whatever length of time makes sense (monthly, quarterly, etc.) and an algorithm figures out the rate of change between those periods. That rate is then used to forecast the next period of sales.

While that is the basic method, it does not account for the many variables that can affect sales. Because of this, basing your sales forecast solely on the rate of change won’t give you an accurate picture. Looking at the same time period in the previous years and comparing it to the present year can help account for seasonal buying fluctuations, but relies on assumptions about business continuing as usual. And if 2020 has taught us anything, it’s that change is the only constant.

Weighted pipeline

How does historical data forecast revenue? In a weighted pipeline method, a forecasting tool uses that historical data to assign close probability rates to deals at different stages of the buying journey. Let’s say a sales team chooses to group opportunities into seven different categories depending on the most recent action a lead has taken. It starts with inquiring about a purchase, then moves along from initial conversations to demos, proposals, negotiations, and a final purchase — or not.

Using a sales forecasting tool, the sales team can then look at how the probability of a closed deal changes as leads move through these seven steps. Their pipeline might look something like this: 1. Lost lead: 0% close rate 2. Inquiring: 8% close rate 3. Interested: 15% close rate 4. Demo: 63% close rate 5. Proposal: 72% close rate 6. Bargaining: 92% close rate 7. Loyal customers: 99% close rate

Sellers can use historical data points to determine what each section of the pipeline is and what the actual close rate is for their company.

Let’s put this into practice with a look at two different customers in the above pipeline: Customer 1 and Customer 2. Customer 1 has gotten a demo and received a proposal for a deal worth $12,000. Customer 2 has sent a few emails back and forth with a seller about a potential deal worth $25,000.

Because Customer 1 fits into the “Demo” phase, he has a 72% chance of closing according to our model. Customer 2, in the “Inquiring” phase, has only an 8% likelihood of making it to a signed deal. Using the pipeline approach sales forecasting, the next step is to multiply the potential deal value by the close rate.

For Customer 1, that calculation would be:

  • $12,000 x.72 = $8,640

Customer 2:

  • $25,000 x .08 = $2,000

That gives this weighted pipeline a projected value of $10,640. Since a sales cycle is not static, projected values will constantly change as deals close, customers move along to different points in the pipeline, and some deals are lost.

What’s the best tool for forecasting using historical data?

You may be wondering what tools to use to do this forecasting. Excel is a common tool to use, but how do you forecast sales using historical data in Excel? There are many templates available to show you how to forecast in Excel based on historical data — or you can create your own custom spreadsheet to fit the methodology that you choose. Click here for a comprehensive look at using a sales forecast template.

Explore Collective[i]

On no, we ran into an issue submitting this form. Please ensure each field was filled out correctly and resubmit.

If this problem persists, please reach out to us and we will be more than happy to follow-up from there.

We’ll be in touch soon

In the meantime, explore Collective[i] and find answers to frequently asked questions.