offers Excel lesson videos made simple!

Sign up or log in to Magoosh Excel.

How to Use the Excel Forecast Function

Microsoft Excel comes with bunch of statistical tools which can enable you to analyze the data and extrapolate future trends and values, in an easy, accurate and quick manner. Excel’s forecast function being one of them. Knowing this function in a little detail can save you a lot of time. Using this special function, one can reliably forecast the future values, on the basis of the given data set, containing the recorded values of past.

The Forecast function is a predefined function in Excel which is categorized as a Statistical Function. It can be used as a worksheet function in Excel. Similar to all other worksheet functions, the Forecast function can also be entered as part of a formula, in a cell of a worksheet.

Lets dive into some details now.

Description

The FORECAST function returns the predicted value of the dependent variable (represented in the data by known_y’s) for the specific value of the independent variable (represented in the data by known_x’s) by using a method of best fit (least squares) linear regression.

If you assume that data pairs are plotted in a scatter graph plot having values of x, measured on the horizontal axis and values of y, measured on the vertical axis, Forecast function will returns the height (y), of the best fit regression line, for a specific value x.

Utility

Excel Forecast function offers a wide array of utility in various sectors such as marketing, finance, sales, economics etc, to establish and describe relationship between the given two variables, that is x (independent variable) and y (dependent variable).

It finds immense application in forecasting future sales volume, consumer demand rate, managing optimum inventory levels, understanding working capital requirements etc.

Syntax

FORECAST (x, known_y’s, known_x’s)

The Forecast function syntax takes in the following three arguments:

  1. x: Required. It represents the numeric value of x for which you have to forecast a new value y.
  2. known_y’s: Required. It represents an array of known y (dependent) values.
  3. known_x’s: Required. It represents an array of known x (independent) values.

Return Value

The Forecast function renders a numeric value, while there can be some discrepancy in the events like:

  • In the case, when x value is not a numeric value, the Forecast function will render the #VALUE! error.
  • If ‘known_y values’ and ‘known_x values ‘ data set contains, unequal number of elements, in such a case, the Forecast function will return the #N/A error.

Wow! that’s too much of detail. Time to see, how it actually it works.

Example

To see the things in action, just copy the given example data (in the following table), and paste it in cell A1 of a new Excel worksheet. Put the same formula in cell B11 to get the result. If you need to, you may increase or decrease the column widths to see all the data properly.

In this particular example:

  • A11 is the value x for which, a new value has to be forecasted in B11 as y.
  • B2:B10, corresponds to the array of all known y values.
  • A2:A10, correspond to the array of all known x values.

If same function is applied for subsequent cells, value of B12 will be 110 and B13 will be 120.

Some other examples are as follows:

  • =FORECAST( 8 , {2, 3, 4} , {4, 6, 8} ) Result: 16
  • =FORECAST( 9 , {4.5 , -0.89 } , {2 , -5} ) Result: 9.89
Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!