HOW TO PREDICT THE FUTURE VALUES IN EXCEL USING FORECAST FUNCTION ?

Table of Contents

INTRODUCTION

Excel is a great application and perhaps the best among the various Spreadsheet based applications out there like GOOGLE SHEETS, CALC and more.

Excel have evolved into a very powerful tool for performing the tasks in various fields like finances, statistics, simple mathematics, regression and more.

The functions provided by Excel helps us to perform very complex calculations within fractions of the seconds.

Out of these functions the FORECAST FUNCTION group is the one we’ll be discussing in this article.

The FORECAST FUNCTIONS helps us to expect the output on the basis of the historical figures.

In this article, we’ll be discussing the old FORECAST FUNCTION or the new FORECAST.LINEAR FUNCTION.



PURPOSE OF FORECAST/FORECAST.LINEAR FUNCTION IN EXCEL

In simple words, the action of the FORECAST/FORECAST.LINEAR function can be described as

FORECAST/FORECAST.LINEAR functions helps us to calculate or predict any future value on the basis of the historical values i.e. the values which are present with us.

Suppose, we have the two variables such as the attendance of a particular class on different days up to Thursday.

Now, if we want to predict the attendance on the Friday, we could make use of the FORECAST.LINEAR/FORECAST FUNCTION.

The function will try to predict the attendance on the FRIDAY.

The function uses LINEAR REGRESSION to predict the future values.



PREREQUISITES TO LEARN FORECAST.LINEAR

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  • Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  •  Of course, Excel software.

Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?

SYNTAX: FORECAST/FORECAST.LINEAR FUNCTION

The Syntax for the function is

=FORECAST.LINEAR( X value against which the value is to be found , known_Y values, known_X values)
X will be the known variable against which we want to get the Y value

Known Y values are the known Y variables.

Known X values are the known X variable.

EXAMPLE 1: PREDICT THE SALES IN THE SECOND FORTNIGHT. THE SALES DATA FOR THE FIRST FORTNIGHT IS PROVIDED

DATA SAMPLE

For this example, we have been given the sales data for the first fifteen days for a store.

We’ll be trying to predict the sales on the next fifteen days on the basis of the data provided to us.

The following picture shows the data given to us.

EXCEL: IF FUNCTION EXAMPLE

SOLUTION

So, as it is evident from the topic name that we’ll be using FORECAST/FORECAST.LINEAR FUNCTION to predict the future sales for the store.

We have already discussed the syntax formula of the same in the previous section.

FOLLOW THE STEPS TO PREDICT THE FUTURE SALES OF THE STORE

  • Double click the cell against the date 16th of the given month where we want to get the predicted sales figure.
  • Enter the formula as =FORECAST.LINEAR(16th OF THE MONTH, ALL THE SALES UP TO 15th, ALL THE DATES from 1st to 15th).
  • For our example, this formula will become as =FORECAST.LINEAR(C19,$D$4:$D$18,$C$4:$C$18).

ENTER THE FORMULA

  • Click ENTER.
DRAG THE FORMULA THROUGH THE 30TH OF THE MONTH
  • The result will appear.
  • As , it is statistical formula, the result may appear in decimals which can be rounded to make the things look realistic using the ROUND FUNCTION.

The following picture shows the final result.

FINAL RESULT

EXPLANATION

Let us discuss the formula used.

The formula used is =FORECAST.LINEAR(C19,$D$4:$D$18,$C$4:$C$18).

The first argument is the date against which we want the predicted sales figure.

The second argument is all the SALES FIGURE from the 1st date of the month through the 15th date of the month.

The third argument is the Xs values i.e. the dates from 1st of the month though the 15th against the Ys value which we took as the second argument.


EXAMPLE 2: PREDICT THE RAINFALL FOR THE FUTURE YEARS 2022,2023 WHEN THE DATA FROM 2019 TO 2021 IS AVAILABLE.

DATA SAMPLE

Let us take one more example to try our hands on the newly learnt FORECAST/FORECAST.LINEAR FUNCTION.

This example shows the rainfall data of CITY X from Jan 2019 to Dec 2021. [ Month wise ]

We need to predict the monthly rainfall for 2022 and 2023.

The data is shown in the picture below.

EXAMPLE DATA


SOLUTION

We’ll be using LINEAR REGRESSION for this calculation and the best option is to use the FORECAST or FORECAST.LINEAR FUNCTION.

To calculate the future rainfall

FOLLOW THE STEPS TO CALCULATE THE FUTURE RAINFALL.

  • Double click the cell against the date Jan 22 against which we’ll find out the expected rainfall.
  • Enter the formula as =FORECAST.LINEAR(Jan 22 will be first value for which rainfall is to be found, all the rainfall data, all the months).
  • For our example, this formula will become as =FORECAST.LINEAR(E3,$C$4:$C$37,$B$4:$B$37).

ENTER THE FORMULA
  • Click ENTER.
RESULT
  • The result will appear.

We’ll make all the references of the historical data absolute so that we can drag down the formula for the further predictions.

As , it is statistical formula, the result may appear in decimals which can be rounded to make the things look realistic using the ROUND FUNCTION.

The following picture shows the final result.

FINAL RESULT

EXPLANATION

Let us discuss the formula used.

The formula used is =FORECAST.LINEAR(E3,$C$4:$C$37,$B$4:$B$37).

The first argument is the month against which we want to predict the rainfall.

The second argument contains the complete range having the historical rainfall.

The third argument is the Xs values i.e. the months against the Ys values which we took as the second argument.

FAQ

WHAT IS THE DIFFERENCE BETWEEN FORECAST FUNCTION AND FORECAST.LINEAR FUNCTION

There is absolutely no difference between FORECAST FUNCTION and FORECAST.LINEAR FUNCTION except that FORECAST.LINEAR function have replaced the FORECAST FUNCTION from EXCEL 2016 onwards.

There is no change in the number of parameters or any other type of change except the name.

That is the reason , FORECAST / FORECAST.LINEAR function names have been used together in this article.

Although FORECAST FUNCTION would still work [ for the compatibility purpose] , it is advisable to use FORECAST.LINEAR function if you are using it today.