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.

how to forecast sales in excel


Download Practice Workbook


5 Ways to Forecast Sales in Excel

1. Using Excel FORECAST Function to Forecast Sales

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.
=FORECAST(B13,$C$5:$C$10,$D$5:$D$10)

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.

how to forecast sales in excel

  • 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.
=FORECAST.ETS(B13,$C$5:$C$10,$D$5:$D$10)
  • Then, press ENTER.

how to forecast sales in excel

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: FORECAST Function in Excel (with other Forecasting Functions)


2. Using Excel Forecast Sheet Feature to Forecast 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

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

how to forecast sales in excel

  • 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.

how to forecast sales in excel

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

how to forecast sales in excel

Thus you can visualize sales forecasts using the Forecast Sheet.

Read More: How to Forecast Sales Growth Rate in Excel (6 Methods)


3. Applying LINEST Function to Forecast Sales in Excel

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.

how to forecast sales in excel

  • Type the following formula in cell B13.
=LINEST(D5:D10,C5:C10,TRUE,FALSE)

how to forecast sales in excel

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.
=$B$13*C5+$C$13

how to forecast sales in excel

  • Press ENTER to see the Trend Value for the first month.

  • Use the Fill Handle to AutoFill the lower cells.

how to forecast sales in excel

  • 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.
=D5/E5

  • 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.
=AVERAGE(D5:D10)

how to forecast sales in excel

  • Then type the following formula in cell G5, press ENTER and AutoFill the lower cells.
=D5/$D$13

  • 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
=$B$13*E13+$C$13

how to forecast sales in excel

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

Thus you can forecast sales by using the LINEST Function.

Read More: Forecasting in Excel Using FORECAST Function & Auto Fill Handle Tool


4. Using Mathematical Formula to Forecast Sales in Excel

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 = KSActual + (1 – K)SForecast

Where, F = Forecasted Sales
            K = Constant that Smooth the change in function
            SActual = Actual sales of the previous months
            SForecast = 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 (SForecast) is 29,580 dollars.
  • Also, we consider K equal to be 4. We will find the forecast for next month.

how to forecast sales in excel

  • Type the following formula in cell D6.
=$B$14*C5+(1-$B$14)*D5

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

how to forecast sales in excel

  • Use the Fill Handle to AutoFill the lower cells.

Thus you can forecast sales by using a mathematical formula.

Read More: How to Calculate Forecast Accuracy Percentage in Excel (4 Easy Methods)


5. Utilizing Moving Average Concept to Forecast Sales in Excel with 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.
=AVERAGE(C5:C7)

how to forecast sales in excel

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.

how to forecast sales in excel

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

  • After that, a chart will appear

how to forecast sales in excel

  • 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.

how to forecast sales in excel

  • 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.

how to forecast sales in excel

  • 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.

how to forecast sales in excel

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

Read More: How to Forecast Revenue in Excel (6 Simple Methods)


Practice Section

Here, I’m giving you the dataset of this article so that you can practice on your own.


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

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo