How to Forecast Sales in Excel (5 Easy Ways)

The article will provide you with some methods on how to forecast sales in Excel. This is important while you consider predicting your business status. Forecasting your sales may concern you about whether you need to take a step for the improvement of your business.
We have sales information about a company for the first 6 months of the year in the dataset.

1. Using FORECAST Function to Forecast Sales in Excel

The simplest way to forecast sales in Excel would be to use the FORECAST function. Say, you want to forecast sales for the next 3 months. Let’s go through the process below.

Steps:

• First, make a column for forecasting sales and type the following formula in cell C13.

Here, the FORECAST function returns the forecasted sales of the 7th month by taking a linear relation between sales and periods.

• Press the ENTER button and you will see the forecasted sales for the 7th month.

• Use the Fill Handle to AutoFill the lower cells.

You can also use another FORECAST function known as the FORECAST.ETS function. It will return the forecasted sales, taking exponential change from the previous sales.

• Use the formula given below in cell C13.

• Then, press ENTER.

Here, the FORECAST.ETS function returns the forecasted sales for the next 3 months by taking an exponential relation between sales and periods.

Thus you can forecast sales by using Excel FORECAST and FORECAST.ETS functions.

Read More: How to Forecast Sales Using Historical Data in Excel

2. Using Excel Forecast Sheet Feature to Predict Sales

The best way to visualize forecast sales for the following months is to use the Forecast Sheet. Let’s go through the procedure below for a better understanding.

Steps:

• First, I define the months as numbers kept in the Period column.

• Then select the range C4:D10 and go to Data >> Forecast >> Forecast Sheet

• This operation will open the Create Forecast Worksheet Window. You will see it will create a Forecast Sheet for the next 4 months by default. In the window, we see 3 forecast lines which include Forecast(Sales), Lower Confidence Bound(Sales), and Upper Confidence Bound(Sales).

• You can change the Forecast Sheet options if you want. I brought the following changes to this sheet.
• I set the Confidence Interval to 90%, Forecast Start to 6 as I want to see the forecast of sales from the 6th month, and also set the Forecast End to 11 as I wish to forecast sales upto the 11th.
• After that, click on Create.

You will see the forecast of sales on a table along with a graph in a new sheet.

Thus you can visualize sales forecasts using the Forecast Sheet.

Read More: How to Forecast Sales Using Regression Analysis in Excel

3. Applying LINEST Function to Forecast Sales

Another way to forecast sales is to apply the LINEST Function. We know that the basic equation for a straight line is y = mx + c. By using this function, we will determine the value of slope (m) and the constant c from the given data of sales. We will determine the forecasted sales for the following 3 months from June. Let’s see the process below.

Steps:

• First, make some necessary columns in the dataset.

• Type the following formula in cell B13.

Here, the LINEST function returns the constant values of the linear regression between sales and periods.

• Hit ENTER and you will see the values of the constants of the equation.

Here, the LINEST function calculates the constant values m and c by using the least squares method that best fits the data.

• Now to find the trend values, type another formula in cell E5.

• Press ENTER to see the trend value for the first month.

• Use the Fill Handle to AutoFill the lower cells.

• To calculate the monthly deviation of the trend values from the sales values, type the following formula in cell F5, press ENTER and AutoFill the lower cells.

• Regarding the determination of the Seasonality Index, first, calculate the average of the sales with the following formula. We are assuming that the Seasonality Index becomes the same after every 6 months. So the Seasonality Index of the 1st month will be equal to the Seasonality Index of the 7th month.

• Then type the following formula in cell G5, press ENTER and AutoFill the lower cells.

• After that, you will need the trend values for the next 3 months. To find the values of trends, type the following formula in cell F13, press ENTER, and AutoFill the lower cells

• Later, just type the following formula in cell G13, press ENTER, and AutoFill the lower cells.

Thus you can forecast sales by using the LINEST function.

Read More: How to Forecast Sales Growth Rate in Excel

4. Inserting Mathematical Excel Formula to Forecast Sales

In this section, we will use a mathematical formula to determine sales forecasts. We are going to use the following formula for this purpose.

F = KS_Actual + (1 – K)S_Forecast

Where, F = Forecasted Sales
            K = Constant that Smooth the change in function
            S_Actual = Actual sales of the previous months
            S_Forecast = Forecasted sales of the previous months

Let’s go through the section for a better understanding.

Steps:

• We need to assume some things first. Let’s assume the forecasted sales of the 2nd month (S_Forecast) is 29,580 dollars.
• Also, we consider K equal to be 0.4. We will find the forecast for next month.

• Type the following formula in cell D6.

• Hit the ENTER button and you will see the forecasted sales for February.

• Use the Fill Handle to AutoFill the lower cells.

Thus you can forecast sales by using a mathematical formula.

Read More: How to Calculate Accuracy and Precision in Excel

5. Forecasting Sales in Excel with Moving Average Trendline

Another way to forecast sales in Excel is to use the moving average concept. We will be doing forecasts on account of averages of sales every 3 months. Let’s follow the description below.

Steps:

• First, make some necessary columns and type the following formula in cell E7.

Here, the AVERAGE function will return the average of the data in the range C5:C7.

• Press ENTER and you will see the average for the first 3 months.

• Use the Fill Handle to AutoFill the lower cells.

• Now, select the range C4:E10 and go to Insert >> Line Chart >> 2D Line

• After that, a chart will appear

• Click on the plus icon of the chart and then select Trendline >> More Option

• A dialog box will appear. Select Moving Averages and click OK.

• You will see the Format Trendline window at the right side. Select Moving Average and set the Period to 3.

• After that, you will see the forecasted sales by moving average.

• If you want to see the forecast for the next two months, go to Format Trendline again and then select any trendline option (I chose Exponential) and set the Forward Forecast to 2.

After that, you will see the forecasted sales for the next 2 months in the chart.

Thus, you can forecast sales by moving average concept and Trendline.

Read More: How to Do Budgeting and Forecasting in Excel

Download Practice Workbook

Conclusion

The bottom line is that you can learn some ideas about how to forecast sales in Excel after finishing this article. There are various ways to do this, but not every method will give you the best probability. In my opinion, using Forecast Sheet would be the best idea to forecast sales in Excel because it returns results according to the last data changes. If you have any other ideas or any feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming article.

Related Articles

• How to Forecast in Excel Based on Historical Data
• How to Forecast Growth Rate in Excel
• How to Forecast Revenue in Excel
• How to Forecast Revenue Growth in Excel

<< Go Back to Forecasting in Excel |Excel for Finance | Learn Excel