Forecast Formula in Excel (Table of Content)
Introduction to Forecast Formula in Excel
The forecast formula is used to predict or calculate a future value which is based on the past data in financial modeling. It uses linear regression to predict the value. This is one of the Statistical in-built Function. It can be used as a worksheet function as well in a formula with other function. The user can use this function to calculate future growth, sales, etc. In Excel, 2013 and earlier versions have the Forecast function, which is now changed to FORECAST.LINEAR function. The forecast formula is still available in Excel 2016 and the latest version with backward compatibility.
Syntax:
FORECAST () – It will return future value which is based on the past data. There are three parameters – (x, known_y’s and known_x’s).
An argument in the FORECAST Function
x – It is a mandatory parameter for which the user wants to predict the value.
known_y’s – It is an optional parameter; it is the data range or array of dependent data which is known.
known_x’s – It’s an optional parameter; it is the data range or array of independent data known.
Equation for Forecast: “y=a + bx “
where: x and y are the samples means AVERAGE (known_x’s) and AVERAGE (known y’s).
How to Use Forecast Formula in Excel?
Excel Forecast Formula is very simple and easy. Let’s understand how to use the Excel Forecast Formula with few examples.
Example #1 – Basic Forecast Formula in Excel
There are some known Y and known X value, so a user wants to calculate the known Y 30 value for known X based on past data, which is known Y and known X. Let’s see how the Forecast Function can solve this problem.
Open MS Excel, go to Sheet1 where the user wants to calculate a Forecast value for 25.
Create one header for the Forecast result to show the function result in cell A11.
Click on cell B11 and apply Forecast Formula.
Now it will ask for x, which is the user wants to forecast the value; select cell A10.
Now it will ask for a known, which user already have in column A, select cell A2 to A9.
Now it will ask for a known, which user already have in column B, Select cell B2 to B9.
Press Enter key.
Summary of Example 1: As the user wants to Forecast the value of 25, which is coming after calculation 10.46332128 based on all given data. Which is available in cell B11 as the Forecast result.
Example #2 – Forecast Formula for Company Future Expenses
There is company data for their earnings and expenses data from the year 2004 to 2018, as shown in the below table, so a user wants to calculate the expense value in the year 2020. Let’s see how the Forecast Formula can solve this problem.
Open MS Excel, go to Sheet2, where the user wants to calculate the expense value for the year 2020.
Create one header for the Forecast result to show the function result in cell A11 and merge it to cell B11.
Click on cell C11 and apply Forecast Formula.
It will then ask for x, which is the user wants to forecast the value and select cell B10.
Now it will ask for known, which user already have in column B, select cell B2 to B9.
Now it will ask for known, which user already have in column B, select cell B2 to B9, write the formula in cell C11.
Press Enter key.
Summary of Example 2: As the user wants to Forecast the expense value in the year 2020, which is coming after calculation 33333.3333 based on all given data. Which is available in cell C11 as the Forecast result.
Example #3 – Data Calculation and Comparing with the Forecast.Linear Formula
Data Calculation for park visitor in ABC park and comparing with the Forecast.Linear Formula. There is an ABC Park where some data are given like park visitor from the year 2014 to 2018 as shown in the below table, so a user wants to calculate the next 3 years park visitor number each year.
Let’s see how the Forecast Formula can solve this problem. Open MS Excel, Go to Sheet3 where the user wants to calculate the next 3 years park visitor number in each year.
Create one header for the Forecast result to show the function result in cell D5 and merge it to cell D6.
Click on cell D7 and apply Forecast Formula.
It will then ask for x, which is the user wants to forecast the linear value and apply the formula in cell E7.
Press Enter key.
Summary of Example 3: As the user wants to calculate the next 3 years park visitor number in each year 2019 onwards, which is available in the D and E column as the FORECAST result.
Things to Remember
- The FORECAST function will return a numeric value based on provided known data known_ys and known_x’s. The length of known_y’s and known_x’s must be the same.
- If x is a no-numeric value, then the FORECAST formula will return an #VALUE!
- If there is empty or non-numeric or zero in the data known_y’s and known_x’s, then the FORECAST formula will return #DIV/0!
Recommended Articles
This is a guide to Forecast Formula in Excel. Here we discuss how to use Forecast Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
