Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
The FORECAST Function is categorized under Excel Statistical functions. It will calculate or predict a future value using existing values.
In financial modeling, the FORECAST function can be useful in calculating the statistical value of a forecast made. For example, if we know the past earnings and expenses, we can forecast the future amounts using the function.
=FORECAST(x, known_y’s, known_x’s)
The FORECAST function uses the following arguments:
X (required argument) – This is a numeric x-value for which we want to forecast a new y-value.
Known_y’s (required argument) – The dependent array or range of data.
Known_x’s (required argument) – This is the independent array or range of data that is known to us.
How to use the FORECAST Function in Excel?
As a worksheet function, FORECAST can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let’s consider an example:
Suppose we are given earnings data, which are the known x’s, and expenses, which are the known y’s. We can use the FORECAST function to predict an additional point along the straight line of best fit through a set of known x- and y-values. Using the data below:
Using earnings data for January 2019, we can predict the expenses for the same month using the FORECAST function.
The formula to use is:
We get the results below:
The FORECAST function will calculate a new y-value using the simple straight-line equation:
The values of x and y are the sample means (the averages) of the known x- and the known y-values.
A few notes about the function:
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.
#N/A! error – Occurs if:
The supplied values known_x’s and the supplied known_y’s arrays have different lengths.
One or both of the known_x’s or the known_y’s arrays are empty.
#DIV/0! error – Occurs if the variance of the supplied known_x’s is equal to zero.
#VALUE! error – Occurs if the given future value of x is non-numeric.
Thanks for reading CFI’s guide to this important Excel function. By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling and valuation analysis. To learn more, check out these additional CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.