July 8, 2021

Written by

Collective[i] Team

  • Posted in
  • Sales Forecasting
  • Sales Forecasting Methods

Sales forecasting formula

Every business, large or small, relies on sales forecasting at a basic operational level. Businesses have to know — roughly, at least — how much money is coming in to plan their spending and operations over the course of the year. And unfortunately, forecasting is not cut and dry. Sales forecasting is driven by many factors, and each one plays a role in complicating the results. Think about it: if sales forecasting was easy, wouldn’t we see a lot more successful businesses?

Many sales teams use a specific tech stack that helps them track leads and client relationships. In some instances, they also rely heavily on their sales team’s instincts. The historical data that’s mined from these places (and more) traditionally feeds into an old-school forecasting tool like a basic CRM or an Excel spreadsheet with a unique formula that mathematically spits out a target number.

Another tricky thing about sales forecasting is that one size does not fit all. Not every business operates the same way, nor does every sales team. For too long, sales forecasting methods have been agnostic of specific sellers’ target customers, their industries, and other factors that can impact projections. Because of realizations like these and glorious technological advancements, the old Excel sales forecasting method isn’t always as clear and concise as what a business might hope for.

To start, let’s look at the main sales forecasting formulas for Excel, and then we’ll explore modern methods that might provide better — and more agile — sales insights.

What is the forecasting formula in Excel?

For all the glitz and glamor in a lot of the tech stacks many sales departments employ, it’s not uncommon for some sellers to go back to old reliable when it comes to using a projected sales formula: Microsoft Excel. When it was released, Excel was the best program to make sense of large amounts of data, and offered enough opportunity for customization that businesses were able to crunch their unique data in order to get valuable outputs.

If sellers are looking for a sales forecasting formula, especially in Excel, odds are they have at least some quantitative data on hand. The most common (and often successful) method using Excel for predicting sales numbers is called exponential smoothing. It decreases the weight assigned to specific periods as time goes by. This is because, while all data is important, the formula is designed to place more weight on recent sales to give better insight into what is likely to happen in the near future.

This formula is called FORECAST.ETS, and can be applied by hitting “Insert Function” in Excel and finding FORECAST.ETS in the Formula Builder list.

The exponential smoothing forecasting formula uses two sets of data:

  • Period of time
  • Sales within that period of time

There are ways to adjust this. Instead of using the number of sales, sellers can provide a dollar amount associated with each sale. It’s also possible to provide a target date up to which you’d like to forecast. Other methods for personalizing the data include data completion if numbers are missing or incomplete, aggregation which determines how multiple sales within time periods are calculated, and predictable seasonality. Microsoft support provides more information for customizing forecasts.

Microsoft Support also supplies a sales forecasting template for free.

Limitations of sales forecast formulas

The above sales forecasting formula is considered reliable, but it does have limitations.

Historical quantitative data is an important piece of the puzzle, but it is only one input of a robust forecast. Often it is paired with some sort of qualitative data. That is: how do sales people feel about specific accounts? What are their gut instincts telling them? Unfortunately it’s harder to crunch “feelings” when they’re not in the form of numbers.

In order to get the best predictive forecast, quantitative data also has to be accurate. Many businesses rely on their sales teams to input notes and other information into a CRM, usually after a long day (or week) of sales prospecting and calls. This input may fall to the wayside, and why wouldn’t it? It’s mostly paperwork that feels like a tedious administrative task when a sales person is exhausted from being “on” all day. Unfortunately, incomplete data leads to inaccurate predictions.

Finally, the forecasting numbers that come from the formula above may be a good baseline in the moment, but situations change — often drastically — for better or worse. To create consistent goals for a sales team to shoot for, adjustments need to be made regularly, as circumstances change.

The easiest way to have a current forecast is to include more than just historic sales data. That’s why Collective[i] uses neural networks to include data like market intelligence, buyer information, and email data to build a clearer picture of the buying landscape for selling teams. We harness the power of AI to create unparalleled prescriptive probabilistic sales forecasts. Our network records interactions between sales team members and customers automatically to alleviate tedious note taking, uses real-time data from an ever-growing network of sales professionals, and gives regular — even daily — recommendations and insights to help close more deals.

Click here to see what else Collective[i] can do for your sales team, and let go of sales forecasting formulas.

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.