Excel Sales Forecasting Guide: Step By Step Instructions To Building the Right F...

Download Now: Free Sales Plan Template

If you want to stay afloat in today's cutthroat business world, it's important to stay one step ahead of the competition. For this, you need to turn ‌sales forecasting into an art form.

This can be challenging for small and new businesses. Entrepreneurs are often optimistic about the future of their businesses. Yet, many are uncertain about the best way to predict their profit margins.

In this article, you'll learn what sales forecasting is all about and how to do sales forecasting in Excel.

Table of Contents

• A Refresher on Sales Forecasting
• Sales Forecasting in Excel
• How To Do Sales Forecasting in Excel With Exponential Smoothing
• How To Forecast Sales in Excel With Moving Average
• Limitations of Forecasting Sales in Excel

A Refresher on Sales Forecasting

As a refresher, sales forecasting is the process of predicting your future sales revenue by estimating how much your company will sell within a specific time period based on your past sales data.

Newly founded companies, however, will have to rely on other methods, such as market research or competitive analysis, to predict their future sales. Competitive analysis involves assessing your competitors' strengths and weaknesses, particularly the market's major players.

A sales forecast is instrumental in helping you plan ahead. It effectively provides a snapshot of your company's projected sales, which lets you make informed business decisions. It also allows you greater efficiency in managing many key internal functions, such as the organization of your resources, cash flow, and workforce.

Not to mention, with your sales forecast in hand, you can better respond to many emerging challenges and keep up with the rapid pace of today's market.

Sales Forecasting in Excel

There are several tools available to help you make accurate sales forecasts, but I think Microsoft Excel is by far one of the easiest to learn because it simplifies complex and repetitive data analyses.

You don't need to know how to program or do statistics to use Excel's forecasting function. The "conditional formatting" option lets you highlight specific values or change the appearance of cells that meet specific criteria, which I find helpful for highlighting certain trends and patterns within your forecasts.

Excel also helps you create (visually appealing) charts and graphs of your forecasts, which gives you a graphic representation of your forecasts

Another benefit of using Excel is the flexibility you get when performing tasks like formatting worksheets, entering formulas, and creating forecasting schedules. Excel spreadsheets let you customize your forecasts according to your specific set of requirements.

Excel also has built-in tools and functions, such as Anova, exponential smoothing, moving averages, Linest, Correl, Forecast, and Trend, all of which can be used for sales forecasting.

Now that I've talked up Excel, I'll go over how to do sales forecasting in Excel using two of the most common quantitative forecasting methods: exponential smoothing and moving averages. Let's dive into each.

How To Do Sales Forecasting in Excel With Exponential Smoothing

Exponential smoothing is a form of time series forecasting. This method smoothes out irregularities in your data by assigning more weight (importance) to more recent data and exponentially decreasing weights to older observations.

Newer sales data is given greater weight because it better reflects the current conditions/trends.

Historical data is important, but the weight each data point is given decreases exponentially. The "smoothing" refers to averaging the data to get rid of any fluctuations for more accurate sales predictions.

Given the quality and quantity of your historical data, exponential smoothing lets you make sales forecasts well into the next year, so it's useful for longer-term sales forecasts.

Below I'll go over a step-by-step guide to using Excel's exponential smoothing function for sales forecasting using version 16.90 of Microsoft Excel.

To run through this example, I'll use the basic sample data set below:

Step 1

Click the Data tab in the Excel header toolbar.

Step 2

Click Data Analysis, which should appear on the far right side of the header toolbar.

Note: The Data Analysis option is only available on the Excel desktop app. If you're on the desktop version and can't find the Data Analysis option, you'll need to add in Analysis ToolPak. Here's how to add it:

Step 2.5 (Optional)

1. Select Data from the header toolbar. If Analysis ToolPak isn't already installed, you should see an option on the far right of the toolbar that says Analysis Tools. Click on it.
2. Tick the checkbox for Analysis ToolPak in the popup window and click OK.

Done! You'll now see the Data Analysis option in the Data tab.

Step 3

Click Data Analysis, scroll to Exponential Smoothing, and click OK.

Step 4

In the Exponential Smoothing dialogue box popup, click on Input Range and select the data range you want to analyze (this should be the column that contains your sales data totals).

Step 5

Type "0.9" into the Damping factor text box. This is the α (alpha) smoothing constant. The value 0.9 is equal to 1- α.

Step 6

Click "Output Range" and select the cells where you want your forecast output to be. I recommend choosing the cells immediately next to your original sales data because it makes comparison and further analysis easier.

Step 7

Tick the Chart Output box at the bottom left of the dialogue box to get your forecast results in graph form.

Step 8

Click OK to run the analysis.

You'll have your forecasted results and your exponential smoothing graph visual. Here's what that looks like for my sample data:

Note: The #N/A next to January is there because there is no reference data before cell C2 to forecast the future value.

How To Forecast Sales in Excel With Moving Average

The moving average is a statistical method that helps businesses forecast long-term trends. It works by calculating the average of a set of values for a given time range. It's called "moving" because a new value is calculated for an upcoming time period.

Moving average is the most useful for forecasting long-term trends.

Now, I'll walk through a step-by-step breakdown of how to do sales forecasting in Excel using the moving average method using version 16.90 of Microsoft Excel.

I'll use the basic sample data set below to run through this example:

Step 1

Click the Data tab, then click Data Analysis on the far right side of the header toolbar.

Note: If you can't find the data analysis option, you will first need to load the Analysis ToolPak add-in (instructions here).

Step 2

Click Data Analysis, scroll to Moving Average, and click OK.

Step 3

In the Moving Average dialogue popup, click on Input Range and select the data range to forecast. This should be the column that contains your total sales amounts.

For my example, it's $B$2:$B$13.

Step 4

Input your preferred interval into the Interval box. The default and recommended interval is 3.

Step 5

In the Output Range box, select the cell immediately next to your first point of data.

Step 6

Tick the Chart Output box (to get a graph of your results) and click OK to run the analysis. Moving averages should now appear in the cells you selected. Based on my sample data, I get a three-month average.

Note: The #N/A next to January and February is there because our interval is three, so the analysis starts running from the third cell (March).

Limitations of Forecasting Sales in Excel

While Excel is a very simple and free-to-use tool to use for your sales forecasting needs, there are a few limitations I have to mention:

• Excel forecasting is a manual process, meaning you have to manually add in new data points as time goes on. It's cost-effective, but the time you spend working might be greater.
• To make sure your forecasts use the most accurate information, you have to manually re-run the equation every time you add in new data because Excel isn't an automated tool.
• Excel is a cost effective and easy tool if you're a smaller business, but it's not scalable. As you break into new markets, launch new products, and just generally increase your sales, the time you'll spend manually adding in new data points and re-running your forecasts can actually make you less effective with your time.
• There is the possibility for error since it's a more manual process. You'd want to make sure you have a good handle on the equations you use and are able to detect any errors in outputs.

All of that to say, Excel is still a cost-effective, high-quality, and helpful tool for sales forecasting, it can just get a bit tedious. If you're looking for an alternative to sales forecasting in Excel, I recommend considering a tool that is integrated with a CRM and comes with automation. HubSpot's Forecasting Software, for example, gives you a comprehensive view of your entire sales pipeline.

It's completely customizable, so you can pick and choose the pipelines and models for your forecasts that align with your business needs. Data is pulled from your CRM (so everything is current), and it's automated, so forecasts run on their own and you don't have to worry about running an equation or manual data entry. My favorite aspect is that you can view forecasts on a team-by-team level, which makes it easier to identify and correct any potential roadblocks.

Image Source

Customizable Forecasting is the Future

Microsoft Excel is an incredibly useful and flexible tool to track data sets and compute sales values for the future. The forecasts that you run will let you visualize your potential sales success and set you up for more effective and accurate sales planning.

Topics: Sales Forecasting

Don't forget to share this post!