บทความ

How to leverage the exponential smoothing formula for forecasting

By Josh Bean, Director, marketing

Published January 16, 2020
Last modified January 16, 2020

When it comes to setting quotas, a lot of sales managers like to turn to a simple annual run rate. And while an ARR may be the quickest and easiest way to predict future sales results, we’ve seen that it’s far from the most precise metric.

By contrast, the exponential smoothing formula lies on the complete opposite end of the spectrum. Although it’s one of the most complicated sales forecasting methods, it is also arguably the most accurate. In this post, we’ll cut through all of the dense algebraic equations to explain what exponential smoothing is and how it’s used in sales forecasting. We’ll also learn the easy way to perform these calculations in Excel to create data-driven forecasts you can rely on.

What is exponential smoothing, and how do you apply it to sales?

Exponential smoothing is a way to analyze data taken from a specific period of time and assign less importance to the older data and more significance to the newer data. This method will produce “smoothed data” or data that’s had the noise removed to make it easier to see major patterns and predict upcoming trends.

In terms of sales forecasting, the exponential smoothing formula assigns more weight to your more recent sales performance data. For example, if you were using exponential smoothing to analyze 12-months’ worth of sales revenues, the formula would put more weight on last month’s earnings than on data that’s a year old. That’s one big difference between this technique and a simple run rate. The latter takes the results from one time period and projects it out to an entire year, assuming that those numbers will remain steady throughout the next 11 months or three quarters.

Exactly how much weight is placed on more recent data is decided by you and the probability-weighing factor, or “smoothing constant,” that you choose to use. It ranges from .1 to 1.0 and is represented by the alpha symbol (α) in the exponential smoothing formula:


 
The larger the value of the constant, the more weight that’s given to recent sales data.

If algebra was your least favorite subject in high school, then by this point, you’ve either broken into a cold sweat or your eyes have glazed over completely. Don’t worry—with Excel, you’re able to run an exponential smoothing forecast without doing any calculations.

How to use exponential smoothing in Excel

The good news is that you don’t need to be a math major to perform these calculations. In fact, if you have a newer version of Excel, you don’t even need to choose your own smoothing constant. While older versions of the software require you to enter a “damping factor,” Excel 2016 and 2019 will automatically determine that portion of the equation for you.

Nowadays, when it comes to smoothing your own sales data, all you need to know is how to use the FORECAST.ETS function.

Step one: Create a worksheet with your data

To get started, you’ll first need to organize your sales data in a standard chart, laid out in two columns representing the time period and the corresponding sales data.

For most sales managers, the time period being tracked will most likely be months, quarters, or years. The sales column will likely include dollar amounts, total units, or monthly recurring revenue (MRR).


 
For this example, we’ll use the last 12 months of growing MRR for a SaaS startup.

Step two: enter your target date

In a separate cell on the same spreadsheet, enter your target date—the future month, quarter, or year that you’re attempting to forecast. Keep in mind that predictions become less accurate as they project further into the future.


 
For this example, we are using the previous 12 months of sales data to predict the next month, February 2020.

Step three: Run the excel forecast.ets function

At this point, we can use Excel’s FORECAST.ETS function to predict the MRR for the target date.


 
To do so, create a separate “Sales Forecast” column. Click on a cell below this text, and type in “=FORECAST.ETS.” You’ll then be prompted to input a series of cell ranges for each of these elements:

    Target Date: This is the date you’re trying to forecast, February 2020. Enter cell D2.
     
    Values: This is the array of sales data you want to analyze for your forecast. Enter cells B2:B13, the column of MRR data.
     
    Timeline: This is the array of time periods you are analyzing for your forecast. Enter cells A2:A13, the column of past months.
     
    Seasonability: An optional feature that will look for seasonable trends in your data and take them into account if you enter a “1.” You could also manually specify the length of the seasonal pattern by entering a positive number, such as 12 for monthly data or 52 for weekly data (it will actually let you go as high as 8,760, the number of hours in a year). Most people will want to keep things simple, though, and just type “1” so that Excel detects seasonality automatically and determines what number to use for the forecast. There’s also the option of typing “0” if you don’t want the formula to account for seasonable trends at all.
     
    Data Completion: This optional feature will fill in any data gaps using linear interpolation. Enter “1” for data completion or “0” if you want missing values to be treated as zeroes.
     
    Aggregation: Another optional function that specifies how the algorithm should aggregate values with the same timestamp. This should not affect your forecast, so just enter the default “1.”

 
Here’s what the equation looks like with all of the appropriate cell ranges and numbers in their place. Once you fill out all of the values and press enter, you’ll be presented with the sales forecast calculated for your target date.


 
In our example, the predicted MRR for February 2020 is $27,725. If we change the target date, though, we can also easily see the projection for other months even further down the road, including $28,679 in March, $29,633 in April, and $30,587 in May.

If you’re a PC user working with the Windows version of Excel, you also have the ability to create a visual forecast worksheet that can make this data easier to process at a glance.


 
Simply highlight your data, go to the Data tab, click on the Forecast Sheet button, and then hit Create to get a helpful line graph that displays your actual sales data in blue alongside the forecasted sales in orange. If you have a Mac, the process will require a bit more manual work. Once you’ve calculated as many target dates as you want, highlight both your actual and forecasted data, go to the Insert tab, and select a 2-D Line Graph to create your chart.

In our example, the actual sales results from the past year are signified by the blue line, while the orange line represents what the exponential smoothing formula forecasts will happen over the next four months. It’s easy to get lost in spreadsheets, but once you understand how to work the formula, it’s surprisingly simple to calculate and chart sales forecast data in Excel.

Data-driven forecasting drives results

Sales forecasts affect everything, from financial planning to sales quotas. Simply put, they’re too important to calculate with imprecise metrics like run rates.

The exponential smoothing formula may seem complicated, but once you know how to use Excel to calculate and chart your sales forecasts, it’s easy to get a clear and accurate glimpse into the future.

Of course, predicting monthly revenues is not the only type of forecasting that matters to sales reps and managers. They also want to know which leads are most likely to pay off, which deals are closest to getting done, and how it’s going to affect their sales goals and their bottom line.

Those are the questions answered by Zendesk Sell’s Sales Forecasting feature, which takes your data and automatically calculates and populates sales forecasts with a micro-level focus. As an automated forecasting tool, the feature provides insights into every deal in your pipeline, including an Estimated Close Date and Win Likelihood for each one.

To learn more, take a look at this guide on how to further leverage data to create accurate sales forecasts.

Get a hold of your sales performance with better forecasting tools

Thanks to automatic forecasting tools like Excel and Zendesk, you don’t have to work through any complicated formulas to utilize exponential smoothing or calculate the probability of a certain deal going through.

Just follow the steps outlined in this guide to create effective sales forecasts with automatic tools. You’ll save time, and you’ll have accurate projections to guide your team’s sales performance.