In this post, you’ll learn about the FORECAST Function, its syntax and the way of using the FORECAST Function in an excel spreadsheet.
FORECAST – refers to the calculation or estimation of future happenings.
What is FORECAST Function?
FORECAST Function is a statistical function. FORECAST Function calculates future values based on existing values using the linear regression. It can also predict numeric values like sales, inventory, expenses, measurements etc.
In financial modeling, the FORECAST Function calculates statistical values.
Remark:
At the starting of excel 2016, the FORECAST Function replaced into FORECAST.LINEAR Function.
Syntax
=FORECAST(x, known_y’s, known_x’s)
Argument:
- = – built-in function.
- FORECAST() – function name.
- x – x is a numeric value for which you want to find a new y-value.
- known_y’s – it is a range of data(dependent array y-value).
- known_x’s – it is a range of data(independent array x-value).
FORECAST Function calculates the relationship between a dependent variable(y value) and an independent variable(x value) also returns a predicted value based on linear regression between x and y value.
Note:
- #VALUE! Error – if x is not a numeric value.
- #N/A Error – if known_y’s and known_x’s are not with the same size and also if the cell is empty.
- #DIV/0! Error – if the value or variance of known_x’s is zero.
How to use the FORECAST Function in Excel?
FORECAST Function calculates the prediction of future value using the existing values.
Example:
The FORECAST Function calculates a new y-value by using a straight line equation;
where;
Step 1:
Open the workbook in your Microsoft Excel.
Step 2:
Enter the data, in the workbook.
In this example, periods refers to the month and the sales amount according to the month.
STEP 3:
In the new cell or the cell where you want to calculate the value, give the formula or the syntax. Start with ‘ =’ for every functions, FORECAST to initiate functions name, followed by an open parenthesis.
Now we are going to calculate the future value that is the 8th-month FORECAST value. The x value is 8th period which is placed in cell B10.
STEP 4:
After calculating keep a comma, then continue with the arguments of the syntax. Next argument is to calculate known_y’s.
known_y’s (dependent array) which is the sales amount, you can give the known values. Therefore, the values are from C3:C9.
STEP 5:
After calculating known_y’s keep a comma, then continue with the arguments of the syntax. Next argument is to calculate known_x’s.
Here known_x’s (independent value) is periods that is the month of the year 2021, you can give the known values. Hence the values are from B3:B9.
STEP 6:
Press enter to get the calculated results.
Therefore, the sales amount for the 8th period is 118.7571.
STEP 7:
To calculate the amount for the remaining months, you can just click and drag from the before value which you are calculated.
