6 Powerful Forecast Functions in Excel | How to do Sales Forecast in Excel

Among four types of analytics (descriptive, diagnostic, predictive and prescriptive), predictive analytics is one of the important type of analytics to predict the future data. Forecasting can be done with all the statistical tools or analytics tool like R, Python, Alteryx. As a basic analytical tool, forecasting can be done also with the help of 6 powerful forecast functions in excel. So, we’ll discuss the sales forecasting in excel.

What is Forecasting?

As discussed earlier that forecasting is a part of predictive analytics. We are performing descriptive analytics on quantitative data to understand historical data. On the other hand, predictive analytics or forecasting is being used to get the quantitative future data. Forecasting technique is also used by many organizations in terms of predict future sales. Forecasting is being performed on the time series data where we have the past data per day or month or year. Mostly ARIMA (Auto Regressive Integrated Moving Average) technique is well accepted among the data analyst or statisticians to do time series forecasting. In forecasting, past trend of data is being used by the algorithm to predict the future trend.

There are two important concepts in the time series forecasting, those are trend and seasonality. Trend is the directional movement of time series data. If sales of a product is increasing month after month that means it is showing upward trend. On the other hand, if a product is experiencing high sales in few months in a year, it is considered as seasonal impact. As an example, air condition products are getting more sales in the summer.

Forecasting Methods

There are many types of forecasting methods are being used by data analyst.  Those are Moving Average, Simple Linear Regression, Multiple Linear Regression, Time series forecasting and so on. Excel forecast function is based on the linear regression methodology. Additionally, other variations and accuracy check methods have been included in it.

Why Forecasting is important?

Forecasting is an important method, not only for business but also for other purpose like weather forecast, economic forecast and so on. In this article, we’ll focus mainly on the sales forecasting. Sales forecast help marketers to manage the inventories at brand or SKU level, plan production, strategies future steps. Government is using forecasting to build the policies and economic decisions.

Basics of forecast method:

To explain forecast method in excel, will use FORECAST function. Basic forecast function in excel can be relate with simple linear regression formula. To apply Forecast function, we need to have three parameters or data points.

1. Independent variable as X where dependent variable Y is unknown
2. Known dependent variable as Y.
3. Known independent variable as X.

Basic forecast function understanding will be easier with simple linear regression equation.

Y= a + bX [Y is dependent variable; X is independent variable]

Let’s say for an example, we have historical sales data from Jan’20 to Dec’20 and we would like to predict sales from Jan’21 to Dec’21. So, month names are independent variables as Y, from Jan’20 to Dec’20. On the other hand, sales are dependent variables as X. As we would like to predict Jan’21 to Dec’21 sales, first discuss Jan’21 sales prediction based on past data.

As discussed, basic forecast function needs three data points to predict future data. So, to predict Jan’21 sales, X is Jan’21, known Ys are sales from Jan’20 to Dec’20 and known Xs are months from Jan’20 to Dec’20. Same way, if we would like to predict Y by using X, similar input needs to be included when linear regression equation is being used. Only slope (b) and intercept (a) have to calculate manually.

6 Powerful Forecast functions in Excel

Excel 2016 has an additional option as “Forecast sheet” which is available in data tab but 6 powerful forecast functions in excel gives you more control to the forecasting exercise. Excel 2016 has the full package of forecasting functions by which you can predict the future data where historical data can be linear, or seasonality exist in the data. Also, statistical accuracy of the forecasted data can be checked. So, lets deep dive into the forecast functions.

FORECAST function:

Forecast function is the old function in excel which was introduced before few years.

Syntax of FORECAST function:

=FORECAST (X, known_Ys, known_Xs)

Syntax arguments:

X: x is the independent variable where we need to predicted sales. Here it is month.

Known_ys: Known ys are the dependent values for a variable. In tis case, sales values from Jan’20 to Dec’20 are ys.

Known_xs: known xs are the past timeline as independent variables. In this case, it is the range from Jan’20 to Dec’20.

FORECAST.ETS Function:

It is one of forecast function of excel with the advantage of exponential smoothing. The objective of use this function to predict sales with seasonality. It is available with excel 2016 and later version.

Syntax of FORECAST.ETS function:

=FORECAST.ETS ( target_date, values, timeline, [seasonality], [data_completion], [aggregation])

Syntax arguments:

Target_date: Target date is independent variable as X. For timeline, target data format can be date or time or numeric vale.

Value: Historical dependent value as Y. In this example, it is sales data.

Timeline: Timeline is the historical time, as example Jan’20, Feb’20. It is the range of independent variable data as X. Data format can be date/ time format or numeric format.

Seasonality [optional input]: User can input the type of seasonality with this option. [0 as no seasonality, 1 as automatic seasonality detection, n as numeric value of season length]

Data_completion [optional input]: Sometimes we have the missing data in the data set and to fill up that many times average value of past data is being filled. If it is input as 0, missing data will be treated as zero. If input is 1, average value will be considered.

Aggregation [optional input]: This option is to input the aggregate type. Find the numeric input meaning below.

FORECAST.ETS.SEASONALITY function:

The objective of this function is to check the type of seasonality. Let’s say, same type of sales trend getting repeated after 6 months within a year, so this function will show the output as 6.

Syntax of FORECAST.ETS.SEASONALITY function:

=FORECAST.ETS.SEASONALITY (values, timeline, [data_completion], [aggregation])

Syntax arguments:

Value: Historical dependent value as Y. In this example, it is sales data.

Timeline: Timeline is the historical time as example Jan’20, Feb’20 and so on. It is like independent variable X. Data format can be date/ time format or numeric format.

Data_completion [optional input]: To treat missing data in the data set, average value of past data being captured. If input is 0, missing data will be as zero. If input is 1, average value of past data will be considered.

Aggregation [optional input]: This option is to input the aggregation. Find the input meaning of numeric values below.

FORECAST.LINEAR Function:

FORECAST.LINEAR function syntax is same as FORECAST function. It is based on linear regression method to predict future data.

Syntax of FORECAST.LINEAR function:

=FORECAST.LINEAR (x, known_ys, knownxs)

Syntax arguments:

It is same as FORECAST function.

X: x is the independent variable to predicted sales.

Known_ys: Known ys are the dependent variable as sales values from Jan’20 to Dec’20.

Known_xs: known xs are the past data as independent variables, from Jan’20 to Dec’20.

FORECAST.ETS.CONFINT function:

This function is designed to check the forecast accuracy in terms of confidence interval. Here we are using 95% confidence interval and FORECAST.ETS function to check the variance.

Syntax of FORECAST.ETS.CONFINT. function:

=FORECAST.ETS.CONFINT ( target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

Syntax arguments:

Target_date: Target date is independent variable (X). As timeline, data format can be date or time or numeric vale.

Value: Historical dependent Y value. Here, it is sales data.

Timeline: Timeline is the historical time as date. It is like independent variable X. Data format can be date/ time format or numeric format.

Confidence_level [optional input]: For this option, input value should be range from 0 to 1. If you want to incorporate 95% confidence level, 0.95 should be mentioned. By default, it is picking up95% confidence level.

Seasonality [optional input]: You can incorporate the type of seasonality. 0 as no seasonality, 1 as automatic seasonality detection, n as numeric value of season length.

Data_completion [optional input]: Sometimes we have the missing data in the data set and to fill up that many times average value of past data is being calculated. If input is 0, missing data will be treated as zero. If input is 1, average historical value will be considered.

Aggregation: [optional input]. This option is for the aggregation. Meaning of numeric values are mentioned below.

FORECAST.ETS.STAT function:

This function is very important for forecast function to check the statistical accuracy of the predicted value.

Syntax of FORECAST.ETS.STAT function:

=FORECAST.LINEAR.STAT (values, timeline, statistic type, [seasonality], [data_completion], [aggregate])

Syntax arguments:

Values, Timeline, Seasonality, data completion and aggregation will be same as FORECAST.ETS function. Only Statistic_type is additional input required to get the type of statistical accuracy.

Statistic_type: The meaning of all numeric input values are described below.

So, inbuilt 6 powerful forecast functions in excel can serve all the requirements of forecasting. So, try out these functions as per your requirements.

Please follow and like us: