How to use FORECAST Function in Excel

Forecast function available in excel is the simplest ever forecasting function that we could have. This function predicts the selected iteration sequence but for that, we must have all the rest knowns sequences and rest known values. Forecast function simply uses Moving average forecast method to predict the next demand. If we do not have known X then we have entered the number manually giving 1 for the first value and so on.

Syntax:= FORECAST(x, known_y’s, known_x’s)

The FORECAST function syntax has the following arguments:

1. X (required argument) – This is a numeric x-value for which we want to forecast a new y-value.
2. Known_y’s (required argument) – The dependent array or range of data.
3. Known_x’s (required argument) – This is the independent array or range of data that is known to us.

Example: Let’s look at some Excel FORECAST function examples and explore how to use the FORECAST function as a worksheet function in Microsoft Excel:

Let’s take some sales data for 2019 that we’d like to forecast for 12 months of 2020. In a separate column I’ll insert my FORECAST function:

Notice the ‘x’ argument cell reference is relative, so that when I copy the formula down it picks up the next date and so on. Meanwhile the known_x and known_y references are absolute.

Syntax:  =FORECAST(B14,$C$2:$C$13,$B$2:$B$13)

Result: 176768.7238

Based on the Excel spreadsheet above, the following FORECAST examples would return:

Now we can see that forecast in excel has predicted some sales values for the year 2020 month wise which shows that sales will be get increased for the corresponding month

Chart Forecast Data

The above sales and forecasting data can be displayed in a dynamic line chart format so that it can be reached easily to the management.

In order to display the forecast graph select forecast column along with the month and sales data. So you will get the below forecasting chart as shown below.

In the above graph, we can see the difference where blue line shows the previous year sales and orange line shows the future forecasting sales which denote that growth will be get increased for the next 12 months compared to the previous year 2019

Note:

1. The length of the known_x’s array should be the same length as the known_y’s, and the variance of the known_x’s must not be zero.
2. #N/A! error – Occurs if:
   1. The supplied values known_x’s and the supplied known_y’s arrays have different lengths.
   2. One or both of the known_x’s or the known_y’s arrays are empty.

3. #DIV/0! error – Occurs if the variance of the supplied known_x’s is equal to zero.
4. #VALUE! error – Occurs if the given future value of x is non-numeric.
5. Advantages of using FORECAST in Excel:
   • Useful in predicting month wise and year wise sales
   • Commonly used in all FMCG, Financial and Investment companies to determine the growth of future sales.